CREATE OR REPLACE PACKAGE BODY PKG_COMMON_TRANS_SMS
AS
  --
PROCEDURE PR_UPDATE_MO_TARIFF_ID(
    GET_PART_DATE  IN VARCHAR2,
    TARIFF_ID      IN TPA_O_NUMBER_VARR,
    TRANSACTION_ID IN VARCHAR2)
AS
  --
  SQLSTRING_CUR VARCHAR2(500);
  --
BEGIN
  SQLSTRING_CUR := 'UPDATE CGW_SMS_MO_TRANS_LOG_OND PARTITION (PRT_SMS_MO_TRANS_LOG_' || GET_PART_DATE || ') SET  CMTL_MT_TARIFF_ID=:1 WHERE CMTL_TRANSACTION_ID=:2';
  EXECUTE IMMEDIATE SQLSTRING_CUR USING TARIFF_ID , TRANSACTION_ID;
END PR_UPDATE_MO_TARIFF_ID;
--
--
PROCEDURE PR_SMS_MO_CHANNEL_SMPP_INSERT(
    MO_DETAILS_SMS_CP IN TP_O_MO_DETAILS_SMS_CP )
AS
  --
BEGIN
  --
  PKG_COMMON_TRANS_SMS.PR_DELVER_DATA_ENQ( MO_DETAILS_SMS_CP => MO_DETAILS_SMS_CP);
  --
END PR_SMS_MO_CHANNEL_SMPP_INSERT;
--
PROCEDURE PR_SMS_MO_HISTORY_INSERT(
    MO_DETAILS_SMS_CP IN TP_O_MO_DETAILS_SMS_CP )
AS
BEGIN
  INSERT
  INTO CGW_SMS_MO_TRANS_LOG_HIST
    (
      CMH_CHRONO ,
      CMH_MOBILE_NO ,
      CMH_SHORT_CODE ,
      CMH_SUFFIX ,
      CMH_MESSAGE_RECEIVED ,
      CMH_REQ_RECV_DTIME ,
      CMH_PROCESSED_DTIME ,
      CMH_MO_CHARGE_APPLIED ,
      CMH_MO_TARIFF_ID ,
      CMH_MO_PROCESSED_STATUS_CODE ,
      CMH_MO_PROCESSED_STATUS_MESG ,
      CMH_CP_ID
    )
    VALUES
    (
      SEQ_MO_HISTORY_LOG.NEXTVAL ,
      MO_DETAILS_SMS_CP.MSISDN ,
      MO_DETAILS_SMS_CP.SHORT_CODE ,
      MO_DETAILS_SMS_CP.SUFFIX ,
      MO_DETAILS_SMS_CP.MESSAGE_RECEIVED ,
      SYSDATE ,
      --MO_DETAILS_SMS_CP.REQ_RECV_DTIME          ,
      SYSDATE ,
      MO_DETAILS_SMS_CP.MO_CHARGE_APPLIED ,
      MO_DETAILS_SMS_CP.MO_TARIFF_ID ,
      MO_DETAILS_SMS_CP.MO_PROCESSED_STATUS_CODE ,
      MO_DETAILS_SMS_CP.MO_PROCESSED_STATUS_MESG ,
      MO_DETAILS_SMS_CP.CP_ID
    ) ;
  COMMIT;
END PR_SMS_MO_HISTORY_INSERT;
--
PROCEDURE PR_SMS_MO_TRANS_INSERT
  (
    MO_DETAILS_SMS_CP IN TP_O_MO_DETAILS_SMS_CP
  )
AS
BEGIN
  INSERT
  INTO CGW_SMS_MO_TRANS_LOG_OND
    (
      CMTL_TRANSACTION_ID ,
      CMTL_SHORT_CODE ,
      CMTL_MSISDN ,
      CMTL_CP_ID ,
      CMTL_MO_REQ_RECV_DTIME ,
      CMTL_MO_CHARGE_APPLIED ,
      CMTL_MO_TARIFF_ID ,
      CMTL_MO_PROC_DTIME ,
      CMTL_MO_KEYWORD_ID ,
      CMTL_MO_MESSAGE_BODY ,
      CMTL_MT_CHARGE_APPLIED ,
      CMTL_MT_TARIFF_ID
    )
    VALUES
    (
      MO_DETAILS_SMS_CP.TRANSACTION_ID ,
      MO_DETAILS_SMS_CP.SHORT_CODE ,
      MO_DETAILS_SMS_CP.MSISDN ,
      MO_DETAILS_SMS_CP.CP_ID ,
      SYSDATE ,
      MO_DETAILS_SMS_CP.MO_CHARGE_APPLIED ,
      MO_DETAILS_SMS_CP.MO_TARIFF_ID ,
      SYSDATE ,
      MO_DETAILS_SMS_CP.KEYWORD_ID ,
      MO_DETAILS_SMS_CP.MESSAGE_RECEIVED ,
      MO_DETAILS_SMS_CP.MT_CHARGING_APPLICABLE ,
      MO_DETAILS_SMS_CP.MT_TARIFF_ID
    ) ;
  COMMIT;
END PR_SMS_MO_TRANS_INSERT;
--
PROCEDURE PR_SMS_MT_BROADCAST_INSERT
  (
    OBJ_CP_MT_DETAILS IN TP_O_MT_DETAILS_SMS ,
    MT_CHARGECODE     IN NUMBER ,
    PROCESS_DATE      IN DATE ,
    SUBMIT_DATE       IN DATE ,
    STATUS_CODE       IN NUMBER ,
    STATUS_MESG       IN VARCHAR2
  )
AS
BEGIN
  INSERT
  INTO CGW_SMS_MT_TRANS_LOG_BC
    (
      CSBH_MOBILE_NO ,
      CSBH_SHORT_CODE ,
      CBSH_CP_ID ,
      CSBH_MT_RECEIVED_DTIME ,
      CSBH_MT_MESSAGE_TYPE ,
      CSBH_MT_MESSAGE_HEADER ,
      CSBH_MT_MESSAGE_BODY ,
      CSBH_MT_PROCESSED_STATUS_CODE ,
      CSBH_MT_PROCESSED_STATUS_MESG ,
      CSBH_MT_SUBMIT_DTIME ,
      CSBH_MT_SUBMIT_STATUS_CODE ,
      CSBH_MT_SUBMIT_STATUS_MESG ,
      CSBH_TRANSACTION_ID ,
      CSBH_MESSAGE_ID_INT ,
      CSBH_MESSAGE_ID_EXT
    )
    VALUES
    (
      OBJ_CP_MT_DETAILS.MSISDN ,
      OBJ_CP_MT_DETAILS.SHORT_CODE ,
      OBJ_CP_MT_DETAILS.CP_ID ,
      SYSDATE ,
      OBJ_CP_MT_DETAILS.MESSAGE_TYPE ,
      OBJ_CP_MT_DETAILS.MESSAGE_HEADER ,
      OBJ_CP_MT_DETAILS.MESSAGE_BODY ,
      OBJ_CP_MT_DETAILS.MT_PROCESSED_STATUS_CODE ,
      OBJ_CP_MT_DETAILS.MT_PROCESSED_STATUS_MESG ,
      SUBMIT_DATE ,
      STATUS_CODE ,
      STATUS_MESG ,
      OBJ_CP_MT_DETAILS.TRANSACTION_ID ,
      OBJ_CP_MT_DETAILS.MESSAGE_ID ,
      OBJ_CP_MT_DETAILS.CP_MESSAGE_ID
    ) ;
  COMMIT;
END PR_SMS_MT_BROADCAST_INSERT;
--
PROCEDURE PR_SMS_MT_INVALID_INSERT
  (
    OBJ_CP_MT_DETAILS IN TP_O_MT_DETAILS_SMS ,
    STATUS_CODE       IN NUMBER ,
    STATUS_MESG       IN VARCHAR2 ,
    ACTION            IN NUMBER
  )
AS
BEGIN
  INSERT
  INTO CGW_SMS_MT_TRANS_LOG_INVLD
    (
      CIMH_CP_TRANSACTION_ID ,
      CIMH_MOBILE_NO ,
      CIMH_SHORT_CODE ,
      CIMH_CP_ID ,
      CIMH_MT_ACTION_TAKEN ,
      CIMH_MT_MESSAGE_TYPE ,
      CIMH_MT_MESSAGE_HEADER ,
      CIMH_MT_MESSAGE_BODY ,
      CIMH_MT_RECVD_DATETIME ,
      CIMH_MT_PROCESSED_DATETIME ,
      CIMH_MT_SUBMIT_STATUS_CODE ,
      CIMH_MT_SUBMIT_STATUS_MESG ,
      CIMH_MESSAGE_ID
    )
    VALUES
    (
      OBJ_CP_MT_DETAILS.TRANSACTION_ID ,
      OBJ_CP_MT_DETAILS.MSISDN ,
      OBJ_CP_MT_DETAILS.SHORT_CODE ,
      OBJ_CP_MT_DETAILS.CP_ID ,
      ACTION ,
      OBJ_CP_MT_DETAILS.MESSAGE_TYPE ,
      OBJ_CP_MT_DETAILS.MESSAGE_HEADER ,
      OBJ_CP_MT_DETAILS.MESSAGE_BODY ,
      SYSDATE ,
      --OBJ_CP_MT_DETAILS.REQ_RECV_DTIME,
      SYSDATE ,
      STATUS_CODE ,
      STATUS_MESG ,
      OBJ_CP_MT_DETAILS.CP_MESSAGE_ID
    ) ;
  COMMIT;
END PR_SMS_MT_INVALID_INSERT;
--
PROCEDURE PR_SMS_MT_TRANS_INSERT
  (
    OBJ_CP_MT_DETAILS IN TP_O_MT_DETAILS_SMS ,
    MT_CHARGECODE     IN NUMBER ,
    PROCESS_DATE      IN DATE ,
    SUBMIT_DATE       IN DATE ,
    STATUS_CODE       IN NUMBER ,
    STATUS_MESG       IN VARCHAR2 ,
    N_MT_COUNTER      IN NUMBER ,
    MT_COUNTER_LIMIT  IN NUMBER
  )
AS
BEGIN
  --
  INSERT
  INTO CGW_SMS_MT_TRANS_LOG_OND
    (
      CMTL_TRANSACTION_ID ,
      CMTL_MESSAGE_ID_EXT ,
      CMTL_MESSAGE_ID_INT ,
      CMTL_REQ_RECV_DTIME ,
      CMTL_SHORT_CODE ,
      CMTL_MSISDN ,
      CMTL_CP_ID ,
      CMTL_MT_TARIFF_ID ,
      CMTL_MT_PRICE ,
      --    CMTL_MT_CHARGING_APPLICABLE   ,
      --    CMTL_MT_CHARGE_APPLIED        ,
      CMTL_MT_MESSAGE_TYPE ,
      CMTL_MT_MESSAGE_HEADER ,
      CMTL_MT_MESSAGE_BODY ,
      CMTL_MT_PROCESSED_DTIME ,
      CMTL_MT_PROCESSED_STATUS_CODE ,
      CMTL_MT_PROCESSED_STATUS_MESG ,
      CMTL_MT_SUBMIT_DTIME ,
      CMTL_MT_SUBMIT_STATUS_CODE ,
      CMTL_MT_SUBMIT_STATUS_MESG
    )
    VALUES
    (
      OBJ_CP_MT_DETAILS.TRANSACTION_ID ,
      OBJ_CP_MT_DETAILS.CP_MESSAGE_ID ,
      OBJ_CP_MT_DETAILS.MESSAGE_ID ,
      SYSDATE ,
      OBJ_CP_MT_DETAILS.SHORT_CODE ,
      OBJ_CP_MT_DETAILS.MSISDN ,
      OBJ_CP_MT_DETAILS.CP_ID ,
      MT_CHARGECODE ,
      OBJ_CP_MT_DETAILS.PRICE ,
      --    MT_CHARGE_APPLIED                          ,
      --    MT_CHARGE_APPLIED                          ,
      OBJ_CP_MT_DETAILS.MESSAGE_TYPE ,
      OBJ_CP_MT_DETAILS.MESSAGE_HEADER ,
      OBJ_CP_MT_DETAILS.MESSAGE_BODY ,
      PROCESS_DATE ,
      OBJ_CP_MT_DETAILS.MT_PROCESSED_STATUS_CODE ,
      OBJ_CP_MT_DETAILS.MT_PROCESSED_STATUS_MESG ,
      SUBMIT_DATE ,
      STATUS_CODE ,
      STATUS_MESG
    ) ;
  --
  COMMIT;
END PR_SMS_MT_TRANS_INSERT;
--
PROCEDURE PR_SMS_MT_TRANS_INSERT_Q_FULL
  (
    OBJ_CP_MT_DETAILS IN TP_O_MT_DETAILS_SMS ,
    MT_CHARGECODE     IN NUMBER ,
    PROCESS_DATE      IN DATE ,
    SUBMIT_DATE       IN DATE ,
    STATUS_CODE       IN NUMBER ,
    STATUS_MESG       IN VARCHAR2 ,
    MT_REL_ID         IN NUMBER
  )
AS
BEGIN
  --
  INSERT
  INTO CGW_SMS_MT_TRANS_LOG_OND
    (
      CMTL_TRANSACTION_ID ,
      CMTL_MESSAGE_ID_EXT ,
      CMTL_MESSAGE_ID_INT ,
      CMTL_REQ_RECV_DTIME ,
      CMTL_SHORT_CODE ,
      CMTL_MSISDN ,
      CMTL_CP_ID ,
      CMTL_MT_TARIFF_ID ,
      CMTL_MT_PRICE ,
      --    CMTL_MT_CHARGING_APPLICABLE   ,
      --    CMTL_MT_CHARGE_APPLIED        ,
      CMTL_MT_MESSAGE_TYPE ,
      CMTL_MT_MESSAGE_HEADER ,
      CMTL_MT_MESSAGE_BODY ,
      CMTL_MT_PROCESSED_DTIME ,
      CMTL_MT_PROCESSED_STATUS_CODE ,
      CMTL_MT_PROCESSED_STATUS_MESG ,
      CMTL_MT_SUBMIT_DTIME ,
      CMTL_MT_SUBMIT_STATUS_CODE ,
      CMTL_MT_SUBMIT_STATUS_MESG
    )
    VALUES
    (
      OBJ_CP_MT_DETAILS.TRANSACTION_ID ,
      OBJ_CP_MT_DETAILS.CP_MESSAGE_ID ,
      OBJ_CP_MT_DETAILS.MESSAGE_ID ,
      SYSDATE ,
      OBJ_CP_MT_DETAILS.SHORT_CODE ,
      OBJ_CP_MT_DETAILS.MSISDN ,
      OBJ_CP_MT_DETAILS.CP_ID ,
      MT_CHARGECODE ,
      OBJ_CP_MT_DETAILS.PRICE ,
      --    MT_CHARGE_APPLIED                          ,
      --    MT_CHARGE_APPLIED                          ,
      OBJ_CP_MT_DETAILS.MESSAGE_TYPE ,
      OBJ_CP_MT_DETAILS.MESSAGE_HEADER ,
      OBJ_CP_MT_DETAILS.MESSAGE_BODY ,
      PROCESS_DATE ,
      OBJ_CP_MT_DETAILS.MT_PROCESSED_STATUS_CODE ,
      OBJ_CP_MT_DETAILS.MT_PROCESSED_STATUS_MESG ,
      SUBMIT_DATE ,
      STATUS_CODE ,
      STATUS_MESG
    ) ;
  --
  COMMIT;
END PR_SMS_MT_TRANS_INSERT_Q_FULL;
--
PROCEDURE PR_SMSG_MT_QUEUE_INSERT
  (
    TRANS_ID       VARCHAR2 ,
    SHORT_CODE     VARCHAR2 ,
    MSISDN         VARCHAR2 ,
    MESSAGE_TYPE   NUMBER ,
    MESSAGE_HEADER VARCHAR2 ,
    MESSAGE        VARCHAR2 ,
    DELV_RECPT_REQ NUMBER ,
    REFUND_FLAG    NUMBER
  )
AS
BEGIN
  PR_SMSG_MT_ENQUEUE ( TRANS_ID => TRANS_ID , SHORT_CODE => SHORT_CODE , SENDER_NAME => '' , MSISDN => MSISDN , MESSAGE_TYPE => MESSAGE_TYPE , MESSAGE_HEADER => MESSAGE_HEADER , MESSAGE => MESSAGE , DELV_RECPT_REQ => DELV_RECPT_REQ , REFUND_FLAG => REFUND_FLAG) ;
END PR_SMSG_MT_QUEUE_INSERT;
--
PROCEDURE PR_UP_OND_MT_DLV_CNT
  (
    TRANS_ID VARCHAR2 ,
    MSISDN     IN VARCHAR2 ,
    PART_DATE  IN VARCHAR2 ,
    MT_COUNTER IN NUMBER ,
    N_MT_COUNTER OUT NUMBER
  )
AS
  TRANSACTION_STATUS NUMBER;
  UPDATE_QUERY       VARCHAR2 ( 500 ) ;
BEGIN
  N_MT_COUNTER         := 0;
  IF ( MT_COUNTER      <= 1 ) THEN
    TRANSACTION_STATUS := - 1;
  ELSE
    TRANSACTION_STATUS := 1;
  END IF;
  N_MT_COUNTER := MT_COUNTER - 1;
  --
  UPDATE_QUERY := 'UPDATE CGW_VALIDATE_TRANSACTION PARTITION (PRT_VALIDATE_TRANS_' || PART_DATE || ') SET CVT_TRANSACTION_STATUS = :1, CVT_MESSAGE_COUNTER = :2 WHERE CVT_TRANSACTION_ID = :3 AND CVT_MSISDN = :4';
  EXECUTE IMMEDIATE UPDATE_QUERY USING TRANSACTION_STATUS ,
  N_MT_COUNTER ,
  TRANS_ID ,
  MSISDN;
  COMMIT;
END PR_UP_OND_MT_DLV_CNT;
--
PROCEDURE PR_UP_WEB_MT_DLV_CNT
  (
    TRANS_ID VARCHAR2 ,
    MSISDN    IN VARCHAR2 ,
    PART_DATE IN VARCHAR2
  )
AS
  UPDATE_QUERY VARCHAR2
  (
    500
  )
  ;
BEGIN
  UPDATE_QUERY := 'UPDATE CGW_VALIDATE_TRANSACTION_WEB PARTITION (PRT_WEB_TRANS_LOG_' || PART_DATE || ') SET CVTW_TRANSACTION_STATUS = -1  WHERE  CVTW_TRANSACTION_ID = :1 AND CVTW_MSISDN = :2';
  EXECUTE IMMEDIATE UPDATE_QUERY USING TRANS_ID ,
  MSISDN;
  COMMIT;
END PR_UP_WEB_MT_DLV_CNT;
--
PROCEDURE PR_ENQ_MT_SMS_BC_CP
  (
    TRANS_ID    VARCHAR2 ,
    MESSAGE_ID  VARCHAR2,
    CP_ID       NUMBER,
    SHORT_CODE  VARCHAR2,
    SUFFIX      VARCHAR2 ,
    SENDER_NAME VARCHAR2,
    MSISDNS TPA_O_VARCHAR_TAB,
    MESSAGE_TYPE   NUMBER ,
    MESSAGE_HEADER VARCHAR2 ,
    MESSAGE        VARCHAR2 ,
    DELV_RECPT_REQ NUMBER ,
    ACCESS_CHANNEL NUMBER ,
    CHARGE_FLAG    CHAR ,
    PRIORITY       NUMBER,
    MAX_MT         NUMBER,
    QUEUE_TYPE     VARCHAR2,
    STATUS_CODE OUT NUMBER,
    SUCCESS_CNT OUT NUMBER,
    TOTAL_MT_SENT_COUNT OUT NUMBER
  )
AS
  TMP_MT_SENT_CNT NUMBER
  (
    8,0
  )
  ;
  TMP_LAST_UPDATE_DTIME DATE;
  TMP_MSISDN_ARR TPA_O_VARCHAR_TAB;
  DATE_DIFF  NUMBER(2);
  MSISDN_CNT NUMBER;
  FAILED_CNT NUMBER;
  --
  CURSOR REF_CURSOR
  IS
    SELECT * FROM TABLE(CAST (MSISDNS AS TPA_O_VARCHAR_TAB));
  --
  QUEUE_NAME VARCHAR2(50);
  PAYLOAD TP_Q_MT_SMS_AL_CP;
  PAYLOAD_ARRAY TPA_Q_MT_SMS_AL_CP;
  --
  ENQUEUE_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
  RECIPIENT_ARRAY DBMS_AQ.AQ$_RECIPIENT_LIST_T;
  MSG_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
  MESSAGE_PROPERTIES_ARRAY DBMS_AQ.MESSAGE_PROPERTIES_ARRAY_T;
  --
  MSGID_ARRAY DBMS_AQ.MSGID_ARRAY_T;
  ERROR_ARRAY DBMS_AQ.ERROR_ARRAY_T;
  --
  --ENQUEUED NUMBER;
  --
BEGIN
  --
  STATUS_CODE           := 0;
  SUCCESS_CNT           := 0;
  TMP_MT_SENT_CNT       := 0;
  TOTAL_MT_SENT_COUNT   := TMP_MT_SENT_CNT;
  TMP_LAST_UPDATE_DTIME := SYSDATE;
  --
  ENQUEUE_OPTIONS.VISIBILITY := DBMS_AQ.IMMEDIATE;
  --
  --
  BEGIN
    --
    SELECT BSD_UPDATED_DTIME,
      BSD_MT_SENT_CNT
    INTO TMP_LAST_UPDATE_DTIME,
      TMP_MT_SENT_CNT
    FROM CGW_BC_SMS_DETAIL
    WHERE BSD_CP_ID   =CP_ID
    AND BSD_SHORT_CODE=SHORT_CODE;
    --
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    --
    INSERT
    INTO CGW_BC_SMS_DETAIL VALUES
      (
        CP_ID ,
        SHORT_CODE ,
        TMP_MT_SENT_CNT,
        TMP_LAST_UPDATE_DTIME
      );
    --
    COMMIT;
    --
    --
  END;
  --
  TOTAL_MT_SENT_COUNT := TMP_MT_SENT_CNT;
  MSISDN_CNT          := MSISDNS.COUNT;
  --
  UPDATE CGW_BC_SMS_DETAIL
  SET BSD_MT_SENT_CNT = BSD_MT_SENT_CNT + MSISDN_CNT,
    BSD_UPDATED_DTIME = SYSDATE
  WHERE BSD_CP_ID     =CP_ID
  AND BSD_SHORT_CODE  =SHORT_CODE;
  COMMIT;
  --
  --
  DATE_DIFF := TRUNC(SYSDATE) - TRUNC(TMP_LAST_UPDATE_DTIME);
  --
  --
  IF MAX_MT       > 0 THEN
    IF DATE_DIFF <= 0 AND TMP_MT_SENT_CNT >= MAX_MT THEN
      --
      UPDATE CGW_BC_SMS_DETAIL
      SET BSD_MT_SENT_CNT = BSD_MT_SENT_CNT - MSISDN_CNT,
        BSD_UPDATED_DTIME = SYSDATE
      WHERE BSD_CP_ID     =CP_ID
      AND BSD_SHORT_CODE  =SHORT_CODE;
      COMMIT;
      --
      STATUS_CODE := 1777;
      RETURN;
    ELSIF DATE_DIFF > 0 THEN
      --
      UPDATE CGW_BC_SMS_DETAIL
      SET BSD_MT_SENT_CNT = MSISDN_CNT,
        BSD_UPDATED_DTIME = SYSDATE
      WHERE BSD_CP_ID     =CP_ID
      AND BSD_SHORT_CODE  =SHORT_CODE;
      COMMIT;
      --
      TMP_MT_SENT_CNT := 0;
    END IF;
  END IF;
  --
  QUEUE_NAME := 'Q_MT_SMS_AL_BC_CP';
  --
  RECIPIENT_ARRAY (1)           := SYS.AQ$_AGENT ( 'INSTANCE1', QUEUE_NAME, NULL);
  MSG_PROPERTIES.RECIPIENT_LIST := RECIPIENT_ARRAY;
  --
  PAYLOAD_ARRAY            := TPA_Q_MT_SMS_AL_CP();
  MESSAGE_PROPERTIES_ARRAY := DBMS_AQ.MESSAGE_PROPERTIES_ARRAY_T();
  --
  --
  --
  OPEN REF_CURSOR;
  --
  LOOP
    FETCH REF_CURSOR BULK COLLECT INTO TMP_MSISDN_ARR ;
    --
    EXIT
  WHEN TMP_MSISDN_ARR.COUNT = 0;
    --
    FOR I IN TMP_MSISDN_ARR.FIRST..TMP_MSISDN_ARR.LAST
    LOOP
      EXIT
    WHEN MAX_MT > 0 AND TMP_MT_SENT_CNT >= MAX_MT ;
      --
      --TRANS_ID:= 'B' || TO_CHAR(SYSTIMESTAMP,'MMDDHH24MISSFF3') || TMP_MSISDN_ARR(I) || SHORT_CODE || 'M';
      PAYLOAD := TP_Q_MT_SMS_AL_CP ( CMQS_TRANSACTION_ID => TRANS_ID ,CMQS_SUBMIT_ID => MESSAGE_ID , CMQS_CPM_ID => CP_ID , CMQS_SOURCE => SHORT_CODE , CMQS_SUFFIX => '' , CMQS_SENDER_NAME => SENDER_NAME , CMQS_DESTINATION => TMP_MSISDN_ARR(I) , CMQS_REQ_RECV_DTIME => SYSDATE , CMQS_MESSAGE_TYPE => MESSAGE_TYPE, CMQS_MESSAGE => MESSAGE , CMQS_MESSAGE_VALIDITY => '' , CMQS_MESSAGE_DLVDTIME => '' , CMQS_MT_TYPE => PKG_ENUM_TYPES.EN_MT_TRANSACTIONS.MT_BROADCAST, CMQS_SCHEDULED_DTIME => SYSDATE, CMQS_CHARGE_FLAG => CHARGE_FLAG, CMQS_DELV_RECPT_REQD => 1 , CMQS_CHANNEL_ID => ACCESS_CHANNEL, CMQS_RETRY_COUNTER => 0) ;
      PAYLOAD_ARRAY.EXTEND;
      PAYLOAD_ARRAY(PAYLOAD_ARRAY.LAST) := PAYLOAD;
      --
      --
      MESSAGE_PROPERTIES_ARRAY.EXTEND;
      MESSAGE_PROPERTIES_ARRAY(MESSAGE_PROPERTIES_ARRAY.LAST) := MSG_PROPERTIES;
      --
      TMP_MT_SENT_CNT := TMP_MT_SENT_CNT + 1;
    END LOOP;
    --
    --
    SUCCESS_CNT := SUCCESS_CNT + DBMS_AQ.ENQUEUE_ARRAY( QUEUE_NAME => QUEUE_NAME, ENQUEUE_OPTIONS => ENQUEUE_OPTIONS, ARRAY_SIZE => PAYLOAD_ARRAY.COUNT, MESSAGE_PROPERTIES_ARRAY => MESSAGE_PROPERTIES_ARRAY, PAYLOAD_ARRAY => PAYLOAD_ARRAY, MSGID_ARRAY => MSGID_ARRAY, ERROR_ARRAY => ERROR_ARRAY);
    --
    FAILED_CNT := MSISDN_CNT - SUCCESS_CNT;
    --
    UPDATE CGW_BC_SMS_DETAIL
    SET BSD_MT_SENT_CNT = BSD_MT_SENT_CNT - FAILED_CNT,
      BSD_UPDATED_DTIME = SYSDATE
    WHERE BSD_CP_ID     =CP_ID
    AND BSD_SHORT_CODE  =SHORT_CODE;
    --
    COMMIT;
    --
    TOTAL_MT_SENT_COUNT := TMP_MT_SENT_CNT;
    STATUS_CODE         := 1000;
    --
  END LOOP;
  --
  CLOSE REF_CURSOR;
  --
END PR_ENQ_MT_SMS_BC_CP;
--
PROCEDURE PR_ENQ_MT_SMS_CP(
    TRANS_ID       VARCHAR2 ,
    MESSAGE_ID     VARCHAR2 ,
    CP_ID          NUMBER ,
    SHORT_CODE     VARCHAR2 ,
    SUFFIX         VARCHAR2 ,
    MSISDN         VARCHAR2 ,
    MESSAGE_TYPE   NUMBER ,
    MESSAGE_HEADER VARCHAR2 ,
    MESSAGE        VARCHAR2 ,
    DELV_RECPT_REQ NUMBER ,
    REFUND_FLAG    NUMBER ,
    ACCESS_CHANNEL NUMBER ,
    CHARGE_FLAG    CHAR ,
    PRIORITY       NUMBER,
    QUEUE_TYPE     VARCHAR2)
AS
  QUEUE_NAME VARCHAR2(50);
  --
  ENQUEUE_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
  MSG_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
  PAYLOAD TP_Q_MT_SMS_AL_CP;
  MSG_ID RAW ( 16 ) ;
  RECIPIENT_ARRAY DBMS_AQ.AQ$_RECIPIENT_LIST_T;
  --
  MT_TYPE NUMBER;
BEGIN
  --
  IF QUEUE_TYPE = 'SUBS' THEN
    QUEUE_NAME := 'Q_MT_SMS_AL_SUBS_CP';
    MT_TYPE    := PKG_ENUM_TYPES.EN_MT_TRANSACTIONS.MT_SUBSCRIPTION;
  ELSE
    QUEUE_NAME := 'Q_MT_SMS_AL_OND_CP';
    MT_TYPE    := PKG_ENUM_TYPES.EN_MT_TRANSACTIONS.MT_ONDEMAND;
  END IF;
  --
  PAYLOAD := TP_Q_MT_SMS_AL_CP ( CMQS_TRANSACTION_ID => TRANS_ID ,CMQS_SUBMIT_ID => MESSAGE_ID , CMQS_CPM_ID => CP_ID , CMQS_SOURCE => SHORT_CODE , CMQS_SUFFIX => SUFFIX , CMQS_SENDER_NAME => '' , CMQS_DESTINATION => MSISDN , CMQS_REQ_RECV_DTIME => SYSDATE , CMQS_MESSAGE_TYPE => MESSAGE_TYPE, CMQS_MESSAGE => MESSAGE , CMQS_MESSAGE_VALIDITY => '' , CMQS_MESSAGE_DLVDTIME => '' , CMQS_MT_TYPE => MT_TYPE, CMQS_SCHEDULED_DTIME => SYSDATE, CMQS_CHARGE_FLAG => CHARGE_FLAG, CMQS_DELV_RECPT_REQD => 1 , CMQS_CHANNEL_ID => ACCESS_CHANNEL, CMQS_RETRY_COUNTER => 0) ;
  --
  --
  ENQUEUE_OPTIONS.VISIBILITY    := DBMS_AQ.IMMEDIATE;
  RECIPIENT_ARRAY ( 1 )         := SYS.AQ$_AGENT ( 'INSTANCE1' , QUEUE_NAME , NULL ) ;
  MSG_PROPERTIES.RECIPIENT_LIST := RECIPIENT_ARRAY;
  MSG_PROPERTIES.PRIORITY       := PRIORITY;
  --
  DBMS_AQ.ENQUEUE ( QUEUE_NAME => QUEUE_NAME , ENQUEUE_OPTIONS => ENQUEUE_OPTIONS , MESSAGE_PROPERTIES => MSG_PROPERTIES , PAYLOAD => PAYLOAD , MSGID => MSG_ID ) ;
  COMMIT;
  --
END;
--
--
PROCEDURE PR_MO_SMSG_ENQ(
    SHORT_CODE   VARCHAR2,
    MSISDN       VARCHAR2,
    MESSAGE_TYPE NUMBER,
    MESSAGE      VARCHAR2,
    TRANS_ID     VARCHAR2,
    EXIT_STATUS OUT NUMBER )
AS
  PAYLOAD TP_Q_MO_SMSG_CP;
  ENQUEUE_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
  MSG_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
  MSG_ID RAW (16);
  RECIPIENT_ARRAY DBMS_AQ.AQ$_RECIPIENT_LIST_T;
BEGIN
  EXIT_STATUS                   := 0;
  PAYLOAD                       := TP_Q_MO_SMSG_CP (TRANS_ID, SHORT_CODE, MSISDN, MESSAGE_TYPE, MESSAGE);
  ENQUEUE_OPTIONS.VISIBILITY    := DBMS_AQ.IMMEDIATE;
  RECIPIENT_ARRAY (1)           := SYS.AQ$_AGENT ('INSTANCE1', 'Q_MO_SMSG_CP', NULL);
  MSG_PROPERTIES.RECIPIENT_LIST := RECIPIENT_ARRAY;
  DBMS_AQ.ENQUEUE (QUEUE_NAME => 'Q_MO_SMSG_CP', ENQUEUE_OPTIONS => ENQUEUE_OPTIONS, MESSAGE_PROPERTIES => MSG_PROPERTIES, PAYLOAD => PAYLOAD, MSGID => MSG_ID);
  COMMIT;
  IF (MSG_ID     = 0) THEN
    EXIT_STATUS := -1;
    RETURN;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  EXIT_STATUS := -1;
END PR_MO_SMSG_ENQ;
--
PROCEDURE PR_SMSG_MT_ENQUEUE(
    TRANS_ID       VARCHAR2,
    SHORT_CODE     VARCHAR2,
    SENDER_NAME    VARCHAR2,
    MSISDN         VARCHAR2,
    MESSAGE_TYPE   NUMBER,
    MESSAGE_HEADER VARCHAR2,
    MESSAGE        VARCHAR2,
    DELV_RECPT_REQ NUMBER ,
    REFUND_FLAG    NUMBER)
AS
  FINAL_MESSAGE VARCHAR2 (2800);
  ENQUEUE_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
  MSG_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
  PAYLOAD TP_Q_MT_SMSG_CP;
  MSG_ID RAW (16);
  RECIPIENT_ARRAY DBMS_AQ.AQ$_RECIPIENT_LIST_T;
  MAX_RETRY_COUNTER NUMBER;
  FINAL_SENDER_NAME VARCHAR2(32);
BEGIN
  MAX_RETRY_COUNTER := 4;
  FINAL_SENDER_NAME := SENDER_NAME;
  --
  IF MESSAGE_TYPE  = 245 THEN
    FINAL_MESSAGE := MESSAGE_HEADER || MESSAGE;
  ELSE
    FINAL_MESSAGE := MESSAGE;
  END IF;
  IF SENDER_NAME      IS NULL THEN
    FINAL_SENDER_NAME := SHORT_CODE;
  END IF;
  --
  --
  PAYLOAD := TP_Q_MT_SMSG_CP (CMSQ_TRASACTION_ID => TRANS_ID, CMSQ_SHORT_CODE => SHORT_CODE, CMSQ_SENDER_NAME => FINAL_SENDER_NAME, CMSQ_MSISDN => MSISDN, CMSQ_MESSAGE_TYPE => MESSAGE_TYPE, CMSQ_MESSAGE => FINAL_MESSAGE, CMSQ_MESSAGE_VALIDITY => '', CMSQ_MESSAGE_DLV_TIME => '', CMSQ_DELV_RECPT_REQ => DELV_RECPT_REQ, CMSQ_REFUND_FLAG => REFUND_FLAG, CMSQ_RETRY_CNT => MAX_RETRY_COUNTER);
  --
  ENQUEUE_OPTIONS.VISIBILITY    := DBMS_AQ.IMMEDIATE;
  RECIPIENT_ARRAY (1)           := SYS.AQ$_AGENT ('I' || SHORT_CODE, 'Q_MT_SMSG_CP', NULL);
  MSG_PROPERTIES.RECIPIENT_LIST := RECIPIENT_ARRAY;
  MSG_PROPERTIES.PRIORITY       := PKG_COMMON_TRANS.FN_GET_GATEWAY_ENQ_PRIORITY(TRANS_ID => TRANS_ID, SHORT_CODE => SHORT_CODE);
  --
  DBMS_AQ.ENQUEUE (QUEUE_NAME => 'Q_MT_SMSG_CP', ENQUEUE_OPTIONS => ENQUEUE_OPTIONS, MESSAGE_PROPERTIES => MSG_PROPERTIES, PAYLOAD => PAYLOAD, MSGID => MSG_ID);
  --
  COMMIT;
END PR_SMSG_MT_ENQUEUE;
--
PROCEDURE PR_SMSG_MT_ENQUEUE(
    OBJ_TP_Q_MT_SMSG_CP TP_Q_MT_SMSG_CP)
AS
  ENQUEUE_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
  MSG_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
  PAYLOAD TP_Q_MT_SMSG_CP := OBJ_TP_Q_MT_SMSG_CP;
  MSG_ID RAW (16);
  RECIPIENT_ARRAY DBMS_AQ.AQ$_RECIPIENT_LIST_T;
BEGIN
  --
  ENQUEUE_OPTIONS.VISIBILITY    := DBMS_AQ.IMMEDIATE;
  RECIPIENT_ARRAY (1)           := SYS.AQ$_AGENT ('I' || OBJ_TP_Q_MT_SMSG_CP.CMSQ_SHORT_CODE, 'Q_MT_SMSG_CP', NULL);
  MSG_PROPERTIES.RECIPIENT_LIST := RECIPIENT_ARRAY;
  MSG_PROPERTIES.PRIORITY       := PKG_COMMON_TRANS.FN_GET_GATEWAY_ENQ_PRIORITY(TRANS_ID => OBJ_TP_Q_MT_SMSG_CP.CMSQ_TRASACTION_ID, SHORT_CODE => OBJ_TP_Q_MT_SMSG_CP.CMSQ_SHORT_CODE);
  --
  DBMS_AQ.ENQUEUE (QUEUE_NAME => 'Q_MT_SMSG_CP', ENQUEUE_OPTIONS => ENQUEUE_OPTIONS, MESSAGE_PROPERTIES => MSG_PROPERTIES, PAYLOAD => PAYLOAD, MSGID => MSG_ID);
  --
  COMMIT;
END;
--
PROCEDURE PR_SMSG_MT_ENQUEUE_SCHED(
    TRANS_ID        VARCHAR2,
    SHORT_CODE      VARCHAR2,
    SENDER_NAME     VARCHAR2,
    MSISDN          VARCHAR2,
    MESSAGE_TYPE    NUMBER,
    MESSAGE_HEADER  VARCHAR2,
    MESSAGE         VARCHAR2,
    DELV_RECPT_REQ  NUMBER,
    REFUND_FLAG     NUMBER,
    SCHEDULED_DTIME DATE)
AS
  FINAL_MESSAGE VARCHAR2 (2800);
  ENQUEUE_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
  MSG_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
  PAYLOAD TP_Q_MT_SMSG_CP;
  MSG_ID RAW (16);
  RECIPIENT_ARRAY DBMS_AQ.AQ$_RECIPIENT_LIST_T;
  MAX_RETRY_COUNTER NUMBER;
  FINAL_SENDER_NAME VARCHAR2 (32);
BEGIN
  MAX_RETRY_COUNTER := 4;
  FINAL_SENDER_NAME := SENDER_NAME;
  --
  IF MESSAGE_TYPE  = 245 THEN
    FINAL_MESSAGE := MESSAGE_HEADER || MESSAGE;
  ELSE
    FINAL_MESSAGE := MESSAGE;
  END IF;
  --
  IF SENDER_NAME      IS NULL THEN
    FINAL_SENDER_NAME := SHORT_CODE;
  END IF;
  --
  --
  PAYLOAD := TP_Q_MT_SMSG_CP (CMSQ_TRASACTION_ID => TRANS_ID, CMSQ_SHORT_CODE => SHORT_CODE, CMSQ_SENDER_NAME => FINAL_SENDER_NAME, CMSQ_MSISDN => MSISDN, CMSQ_MESSAGE_TYPE => MESSAGE_TYPE, CMSQ_MESSAGE => FINAL_MESSAGE, CMSQ_MESSAGE_VALIDITY => '', CMSQ_MESSAGE_DLV_TIME => '', CMSQ_DELV_RECPT_REQ => DELV_RECPT_REQ, CMSQ_REFUND_FLAG => REFUND_FLAG, CMSQ_RETRY_CNT => MAX_RETRY_COUNTER);
  --
  --
  ENQUEUE_OPTIONS.VISIBILITY    := DBMS_AQ.IMMEDIATE;
  RECIPIENT_ARRAY (1)           := SYS.AQ$_AGENT ('INSTANCE1', 'Q_MT_SMSG_DELAY_CP', NULL);
  MSG_PROPERTIES.RECIPIENT_LIST := RECIPIENT_ARRAY;
  MSG_PROPERTIES.PRIORITY       := PKG_COMMON_TRANS.FN_GET_GATEWAY_ENQ_PRIORITY(TRANS_ID => TRANS_ID, SHORT_CODE => SHORT_CODE);
  --
  IF SCHEDULED_DTIME              IS NOT NULL THEN
    IF (SCHEDULED_DTIME                               - SYSDATE) > 0 THEN
      MSG_PROPERTIES.DELAY        := (SCHEDULED_DTIME - SYSDATE) * 24 * 60 * 60;
    END IF;
  END IF;
  --
  --
  DBMS_AQ.ENQUEUE (QUEUE_NAME => 'Q_MT_SMSG_DELAY_CP', ENQUEUE_OPTIONS => ENQUEUE_OPTIONS, MESSAGE_PROPERTIES => MSG_PROPERTIES, PAYLOAD => PAYLOAD, MSGID => MSG_ID);
  --
  --
  COMMIT;
END PR_SMSG_MT_ENQUEUE_SCHED;
--
PROCEDURE PR_SMS_MT_SMPP_SUBMIT_INSERT(
    IP_ADDRESS                IN VARCHAR2 ,
    PORT_NO                   IN NUMBER ,
    USER_NAME                 IN VARCHAR2 ,
    SERVICE_TYPE              IN VARCHAR2 ,
    SOURCE_ADDR_TON           IN NUMBER ,
    SOURCE_ADDR_NPI           IN NUMBER ,
    IN_SOURCE_ADDR            IN VARCHAR2 ,
    DEST_ADDR_TON             IN NUMBER ,
    DEST_ADDR_NPI             IN NUMBER ,
    DEST_ADDR                 IN VARCHAR2 ,
    ESM_CLASS                 IN NUMBER ,
    PROTOCOL_ID               IN VARCHAR2 ,
    PRIORITY_FLAG             IN NUMBER ,
    SCHEDULE_DELIVERY_TIME    IN VARCHAR2 ,
    VALIDITY_PERIOD           IN VARCHAR2 ,
    REGISTERED_DELIVERY       IN NUMBER ,
    REPLACE_IF_PRESENT_FLAG   IN NUMBER ,
    DATA_CODING               IN NUMBER ,
    SM_DEFAULT_MSG_ID         IN NUMBER ,
    SM_LENGTH                 IN NUMBER ,
    SHORT_MESSAGE_CONV        IN VARCHAR2 ,
    USER_MESSAGE_REFERENCE    IN NUMBER ,
    SOURCE_PORT               IN NUMBER ,
    SOURCE_ADDR_SUBUNIT       IN NUMBER ,
    DEST_PORT                 IN NUMBER ,
    DEST_ADDR_SUBUNIT         IN NUMBER ,
    SAR_MSG_REF_NUM           IN NUMBER ,
    SAR_TOTAL_SEGMENTS        IN NUMBER ,
    SAR_SEGMENT_SEQNUM        IN NUMBER ,
    MORE_MESSAGES_TO_SEND     IN NUMBER ,
    PAYLOAD_TYPE              IN NUMBER ,
    MESSAGE_PAYLOAD           IN VARCHAR2 ,
    PRIVACY_INDICATOR         IN NUMBER ,
    CALLBACK_NUM              IN VARCHAR2 ,
    CALLBACK_NUM_PRES_IND     IN NUMBER ,
    CALLBACK_NUM_ATAG         IN VARCHAR2 ,
    SOURCE_SUBADDRESS         IN VARCHAR2 ,
    DEST_SUBADDRESS           IN VARCHAR2 ,
    USER_RESPONSE_CODE        IN NUMBER ,
    DISPLAY_TIME              IN NUMBER ,
    SMS_SIGNAL                IN NUMBER ,
    MS_VALIDITY               IN NUMBER ,
    MS_MSG_WAIT_FACILITIES    IN NUMBER ,
    NUMBER_OF_MESSAGES        IN NUMBER ,
    ALERT_ON_MESSAGE_DELIVERY IN VARCHAR2 ,
    LANGUAGE_INDICATOR        IN NUMBER ,
    ITS_REPLY_TYPE            IN NUMBER ,
    ITS_SESSION_INFO          IN VARCHAR2 ,
    USSD_SERVICE_OP           IN NUMBER ,
    SHORT_MESSAGE             IN VARCHAR2 ,
    PROTOCOL_TYPE             IN VARCHAR2,
    SMPP_ID                   IN NUMBER)
AS
  --
BEGIN
  --
  INSERT
  INTO SMPPSERVER_SUBMIT_DATA
    (
      MESSAGE_ID ,
      IP_ADDRESS ,
      PORT_NO ,
      USER_NAME ,
      SERVICE_TYPE ,
      SOURCE_ADDR_TON ,
      SOURCE_ADDR_NPI ,
      SOURCE_ADDR ,
      DEST_ADDR_TON ,
      DEST_ADDR_NPI ,
      DEST_ADDR ,
      ESM_CLASS ,
      PROTOCOL_ID ,
      PRIORITY_FLAG ,
      SCHEDULE_DELIVERY_TIME ,
      VALIDITY_PERIOD ,
      REGISTERED_DELIVERY ,
      REPLACE_IF_PRESENT_FLAG ,
      DATA_CODING ,
      SM_DEFAULT_MSG_ID ,
      SM_LENGTH ,
      SHORT_MESSAGE ,
      USER_MESSAGE_REFERENCE ,
      SOURCE_PORT ,
      SOURCE_ADDR_SUBUNIT ,
      DEST_PORT ,
      DEST_ADDR_SUBUNIT ,
      SAR_MSG_REF_NUM ,
      SAR_TOTAL_SEGMENTS ,
      SAR_SEGMENT_SEQNUM ,
      MORE_MESSAGES_TO_SEND ,
      PAYLOAD_TYPE ,
      MESSAGE_PAYLOAD ,
      PRIVACY_INDICATOR ,
      CALLBACK_NUM ,
      CALLBACK_NUM_PRES_IND ,
      CALLBACK_NUM_ATAG ,
      SOURCE_SUBADDRESS ,
      DEST_SUBADDRESS ,
      USER_RESPONSE_CODE ,
      DISPLAY_TIME ,
      SMS_SIGNAL ,
      MS_VALIDITY ,
      MS_MSG_WAIT_FACILITIES ,
      NUMBER_OF_MESSAGES ,
      ALERT_ON_MESSAGE_DELIVERY,
      LANGUAGE_INDICATOR ,
      ITS_REPLY_TYPE ,
      ITS_SESSION_INFO ,
      USSD_SERVICE_OP ,
      SHORT_MESSAGE_CONV ,
      PROTOCOL_TYPE
    )
    VALUES
    (
      SMPP_ID ,
      IP_ADDRESS ,
      PORT_NO ,
      USER_NAME ,
      SERVICE_TYPE ,
      SOURCE_ADDR_TON ,
      SOURCE_ADDR_NPI ,
      IN_SOURCE_ADDR ,
      DEST_ADDR_TON ,
      DEST_ADDR_NPI ,
      DEST_ADDR ,
      ESM_CLASS ,
      PROTOCOL_ID ,
      PRIORITY_FLAG ,
      SCHEDULE_DELIVERY_TIME ,
      VALIDITY_PERIOD ,
      REGISTERED_DELIVERY ,
      REPLACE_IF_PRESENT_FLAG ,
      DATA_CODING ,
      SM_DEFAULT_MSG_ID ,
      SM_LENGTH ,
      SHORT_MESSAGE ,
      USER_MESSAGE_REFERENCE ,
      SOURCE_PORT ,
      SOURCE_ADDR_SUBUNIT ,
      DEST_PORT ,
      DEST_ADDR_SUBUNIT ,
      SAR_MSG_REF_NUM ,
      SAR_TOTAL_SEGMENTS ,
      SAR_SEGMENT_SEQNUM ,
      MORE_MESSAGES_TO_SEND ,
      PAYLOAD_TYPE ,
      MESSAGE_PAYLOAD ,
      PRIVACY_INDICATOR ,
      CALLBACK_NUM ,
      CALLBACK_NUM_PRES_IND ,
      CALLBACK_NUM_ATAG ,
      SOURCE_SUBADDRESS ,
      DEST_SUBADDRESS ,
      USER_RESPONSE_CODE ,
      DISPLAY_TIME ,
      SMS_SIGNAL ,
      MS_VALIDITY ,
      MS_MSG_WAIT_FACILITIES ,
      NUMBER_OF_MESSAGES ,
      ALERT_ON_MESSAGE_DELIVERY,
      LANGUAGE_INDICATOR ,
      ITS_REPLY_TYPE ,
      ITS_SESSION_INFO ,
      USSD_SERVICE_OP ,
      SHORT_MESSAGE_CONV ,
      PROTOCOL_TYPE
    );
  --
  COMMIT;
  --
END PR_SMS_MT_SMPP_SUBMIT_INSERT;
--
PROCEDURE PR_VALIDATE_TRANS_INSERT_MT
  (
    OBJ_CP_MT_DETAILS IN TP_O_MT_DETAILS_SMS
  )
AS
BEGIN
  PKG_COMMON_TRANS.PR_VALIDATE_TRANS_INSERT_MT ( TRANSACTION_ID => OBJ_CP_MT_DETAILS.TRANSACTION_ID, MESSAGE_ID_INT => OBJ_CP_MT_DETAILS.MESSAGE_ID, MESSAGE_ID_EXT => OBJ_CP_MT_DETAILS.CP_MESSAGE_ID, REQ_RECV_DTIME => OBJ_CP_MT_DETAILS.REQ_RECV_DTIME, CP_ID => OBJ_CP_MT_DETAILS.CP_ID, SHORT_CODE => OBJ_CP_MT_DETAILS.SHORT_CODE, MSISDN => OBJ_CP_MT_DETAILS.MSISDN, CHARGE_FLAG => OBJ_CP_MT_DETAILS.CHARGE_FLAG, MT_TARIFF_ID => OBJ_CP_MT_DETAILS.TARIFF_ID, PRICE => OBJ_CP_MT_DETAILS.PRICE, MT_TYPE => OBJ_CP_MT_DETAILS.MT_TYPE, SERVICE_TYPE => PKG_ENUM_TYPES.EN_SERVICES.SMS, NEXT_MT_RESET_DATE => OBJ_CP_MT_DETAILS.NEXT_MT_COUNTER_RESET_DATE);
END PR_VALIDATE_TRANS_INSERT_MT;
--
PROCEDURE PR_DELVER_DATA_ENQ
  (
    MO_DETAILS_SMS_CP IN TP_O_MO_DETAILS_SMS_CP
  )
AS
  --
  QUEUE_NAME VARCHAR2
  (
    40
  )
  ;
  ENQUEUE_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
  MSG_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
  PAYLOAD TP_Q_MO_SMS_CHNL_SMPP_CP;
  MSG_ID RAW (16);
  RECIPIENT_ARRAY DBMS_AQ.AQ$_RECIPIENT_LIST_T;
  --
  MO_QUEUE_TYPE NUMBER:=2;
  --
BEGIN
  --
  QUEUE_NAME := 'Q_MO_SMS_CHNL_SMPP_CP';
  --
  ENQUEUE_OPTIONS.VISIBILITY    := DBMS_AQ.IMMEDIATE;
  RECIPIENT_ARRAY (1)           := SYS.AQ$_AGENT ('SMPPSRVR'||MO_DETAILS_SMS_CP.SMPP_SERVER_NO, QUEUE_NAME, NULL);
  MSG_PROPERTIES.RECIPIENT_LIST := RECIPIENT_ARRAY;
  --
  BEGIN
    --
    IF MO_QUEUE_TYPE=1 THEN
      --
      PAYLOAD                     := NEW TP_Q_MO_SMS_CHNL_SMPP_CP( '','', 0, 0, NULL, '1214', '', 0, NULL, 0, 'HI Test', 'HI test', 'ok', 'ok', 1234, 0, 0, 0, '', '', '172.20.23.12', '', 0, 0,NULL, NULL, NULL, '', '', NULL, '', '', 0, '', 1, 0, 0, 0, 0, 0, '','', 0,'', 0,'', 0, '','', '', 1, 1, '', 0, 0, 0, 0,'', '',0,0, 0, 0, 0, 0 );
      PAYLOAD.MESSAGE_ID          :=1;
      PAYLOAD.USER_NAME           :=MO_DETAILS_SMS_CP.SMPP_USER_NAME;
      PAYLOAD.SOURCE_ADDR_TON     :=MO_DETAILS_SMS_CP.SMPP_SOURCE_ADDR_TON;
      PAYLOAD.SOURCE_ADDR_NPI     :=MO_DETAILS_SMS_CP.SMPP_SOURCE_ADDR_NPI;
      PAYLOAD.SOURCE_ADDR         :=MO_DETAILS_SMS_CP.MSISDN;
      PAYLOAD.DEST_ADDR_TON       :=MO_DETAILS_SMS_CP.SMPP_DEST_ADDR_TON;
      PAYLOAD.DEST_ADDR_NPI       :=MO_DETAILS_SMS_CP.SMPP_DEST_ADDR_NPI;
      PAYLOAD.DEST_ADDR           :=MO_DETAILS_SMS_CP.SHORT_CODE;
      PAYLOAD.ESM_CLASS           :=MO_DETAILS_SMS_CP.SMPP_ESM_CLASS;
      PAYLOAD.DATA_CODING         :=MO_DETAILS_SMS_CP.SMPP_DCS;
      PAYLOAD.SM_LENGTH           :=MO_DETAILS_SMS_CP.SMPP_SM_LENGTH;
      PAYLOAD.SHORT_MESSAGE       :=MO_DETAILS_SMS_CP.MESSAGE_RECEIVED;
      PAYLOAD.RECEIPTED_MESSAGE_ID:=MO_DETAILS_SMS_CP.TRANSACTION_ID;
      PAYLOAD.PROTOCOL_TYPE       :='U';
      PAYLOAD.MSG_STAT            :='R';
      PAYLOAD.RECVD_DATE          :=SYSDATE;
      PAYLOAD.REQ_RECV_DTIME      :=SYSDATE;
      PAYLOAD.LAST_PROCESSED_DATE :=SYSDATE;
      --
      --PAYLOAD.VALIDITY_PERIOD          :=''       ;
      --PAYLOAD.SUBMIT_DATE              := ''     ;
      --PAYLOAD.DONE_DATE                :=''       ;
      --PAYLOAD.SCHEDULE_DELIVERY_TIME   :='';
      --
      DBMS_AQ.ENQUEUE (QUEUE_NAME => QUEUE_NAME, ENQUEUE_OPTIONS => ENQUEUE_OPTIONS, MESSAGE_PROPERTIES => MSG_PROPERTIES, PAYLOAD => PAYLOAD, MSGID => MSG_ID);
      --
      -- EXCEPTION
      -- WHEN OTHERS THEN
      --  PR_CDC_ERROR_INSERT ( APPLN_DESC => 'PKG_SMPP_SERVER.PR_DELVER_DATA_ENQ', ERR_MSG => SQLERRM ) ;
    ELSE
      IF ((MO_DETAILS_SMS_CP.SMPP_DCS = 0 OR MO_DETAILS_SMS_CP.SMPP_DCS = 3) AND MO_DETAILS_SMS_CP.SMPP_SM_LENGTH <= 160) THEN
        INSERT
        INTO SMPPSERVER_DELIVER_DATA
          (
            MESSAGE_ID ,
            USER_NAME ,
            SOURCE_ADDR_TON ,
            SOURCE_ADDR_NPI ,
            SOURCE_ADDR ,
            DEST_ADDR_TON ,
            DEST_ADDR_NPI ,
            DEST_ADDR ,
            ESM_CLASS ,
            DATA_CODING ,
            SM_LENGTH ,
            SHORT_MESSAGE ,
            PROTOCOL_TYPE ,
            MSG_STAT ,
            RECEIPTED_MESSAGE_ID,
            SERVER_NO
          )
          VALUES
          (
            SMPPSERVER_DELIVER_DATA_SEQ.NEXTVAL ,
            MO_DETAILS_SMS_CP.SMPP_USER_NAME ,
            MO_DETAILS_SMS_CP.SMPP_SOURCE_ADDR_TON,
            MO_DETAILS_SMS_CP.SMPP_SOURCE_ADDR_NPI,
            MO_DETAILS_SMS_CP.MSISDN ,
            MO_DETAILS_SMS_CP.SMPP_DEST_ADDR_TON ,
            MO_DETAILS_SMS_CP.SMPP_DEST_ADDR_NPI ,
            MO_DETAILS_SMS_CP.SHORT_CODE ,
            MO_DETAILS_SMS_CP.SMPP_ESM_CLASS ,
            MO_DETAILS_SMS_CP.SMPP_DCS ,
            MO_DETAILS_SMS_CP.SMPP_SM_LENGTH ,
            MO_DETAILS_SMS_CP.MESSAGE_RECEIVED ,
            'U' ,
            'R' ,
            MO_DETAILS_SMS_CP.TRANSACTION_ID ,
            MO_DETAILS_SMS_CP.SMPP_SERVER_NO
          );
      ELSIF ((MO_DETAILS_SMS_CP.SMPP_DCS = 8 OR MO_DETAILS_SMS_CP.SMPP_DCS = 4) AND MO_DETAILS_SMS_CP.SMPP_SM_LENGTH <= 70) THEN
        INSERT
        INTO SMPPSERVER_DELIVER_DATA
          (
            MESSAGE_ID ,
            USER_NAME ,
            SOURCE_ADDR_TON ,
            SOURCE_ADDR_NPI ,
            SOURCE_ADDR ,
            DEST_ADDR_TON ,
            DEST_ADDR_NPI ,
            DEST_ADDR ,
            ESM_CLASS ,
            DATA_CODING ,
            SM_LENGTH ,
            SHORT_MESSAGE ,
            PROTOCOL_TYPE ,
            MSG_STAT ,
            RECEIPTED_MESSAGE_ID,
            SERVER_NO
          )
          VALUES
          (
            SMPPSERVER_DELIVER_DATA_SEQ.NEXTVAL ,
            MO_DETAILS_SMS_CP.SMPP_USER_NAME ,
            MO_DETAILS_SMS_CP.SMPP_SOURCE_ADDR_TON,
            MO_DETAILS_SMS_CP.SMPP_SOURCE_ADDR_NPI,
            MO_DETAILS_SMS_CP.MSISDN ,
            MO_DETAILS_SMS_CP.SMPP_DEST_ADDR_TON ,
            MO_DETAILS_SMS_CP.SMPP_DEST_ADDR_NPI ,
            MO_DETAILS_SMS_CP.SHORT_CODE ,
            MO_DETAILS_SMS_CP.SMPP_ESM_CLASS ,
            MO_DETAILS_SMS_CP.SMPP_DCS ,
            MO_DETAILS_SMS_CP.SMPP_SM_LENGTH ,
            MO_DETAILS_SMS_CP.MESSAGE_RECEIVED ,
            'U' ,
            'R' ,
            MO_DETAILS_SMS_CP.TRANSACTION_ID ,
            MO_DETAILS_SMS_CP.SMPP_SERVER_NO
          );
      END IF;
    END IF;
    COMMIT;
    --
  END;
END PR_DELVER_DATA_ENQ;
--
END PKG_COMMON_TRANS_SMS;