Call Sotore Procedure in ESQL C

[Former Member]

I have a Sybase store procedure P_TEST_EX with 36 input parameters and 5 output parameters, details see follow:

declare @al_channel_id  NUMERIC(2)
declare @as_user_id  VARCHAR(8)
declare @as_machine_name  VARCHAR(40)
declare @al_api_code  NUMERIC(4)
declare @al_comp_code  NUMERIC(4)
declare @as_account  VARCHAR(35)
declare @as_msg_type  VARCHAR(4)
declare @as_reference  VARCHAR(255)
declare @al_pos  NUMERIC(1)
declare @adt_transmission_date  DATETIME
declare @as_trx_amt  VARCHAR(18)
declare @as_settl_amt  VARCHAR(18)
declare @as_replacement_amt  VARCHAR(18)
declare @as_trace_num  VARCHAR(6)
declare @as_settl_rate  VARCHAR(18)
declare @as_local_time  VARCHAR(12)
declare @as_local_date  VARCHAR(12)
declare @as_settl_date  VARCHAR(12)
declare @as_expiry_date  VARCHAR(4)
declare @as_capture_date  VARCHAR(4)
declare @as_iid  VARCHAR(13)
declare @as_track2  VARCHAR(39)
declare @as_card  VARCHAR(20)
declare @as_ret_ref_no  VARCHAR(14)
declare @as_acceptor_term_id  VARCHAR(16)
declare @as_acceptor_id_code  VARCHAR(15)
declare @as_acceptor_name  VARCHAR(40)
declare @as_trx_currency  VARCHAR(3)
declare @as_settl_currency  VARCHAR(3)
declare @as_terminal_data  VARCHAR(19)
declare @as_pos_response_code  VARCHAR(23)
declare @as_pos_settl_data  VARCHAR(15)
declare @as_trx_type  VARCHAR(1)
declare @as_process_code  VARCHAR(6)
declare @al_interface_code  NUMERIC(4,0)
declare @odec_avail  NUMERIC(20,6)
declare @odec_cur_bal  NUMERIC(20,6)
declare @ol_error  NUMERIC(6,0)
declare @os_err_msg  VARCHAR(1000)
declare @ol_trx_code  NUMERIC(12)
declare @os_auth_code  VARCHAR(10)

When I call the store procedure in ESQL C like this:

/*variables delaration */
EXEC SQL BEGIN DECLARE SECTION;
char        null_str_val[2]="\\0";
int         null_int_val=0;
short       null_ind=-1;
/*input value */
CS_DECIMAL  al_channel_id={2,0,0};
char        as_user_id[9]="XXX_USR";
char        as_machine_name[9]="XXX_SVR";
CS_DECIMAL  al_api_code={4,0,0};
CS_DECIMAL  al_comp_code={4,0,0};
char        as_account[36]="\\0";
char        as_msg_type[5]="0200";
CS_DECIMAL  al_trans_nb={6,0,0};                short al_trans_nb_ind=-1;
char        as_reference[256]="\\0";
CS_DECIMAL  al_pos={1,0,0};
CS_DATETIME adt_transmission_date;
char        as_trx_amt[19];                     short as_trx_amt_ind = -1;
char        as_settl_amt[19];                   short as_settl_amt_ind = -1;
char        as_replacement_amt[19];             short as_replacement_amt_ind = -1;
char        as_trace_num[7];
char        as_settl_rate[19];                  short as_settl_rate_ind = -1;
char        as_local_time[13];
char        as_local_date[13];
char        as_settl_date[13];                  short as_settl_date_ind = -1;
char        as_expiry_date[5];                  short as_expiry_date_ind = -1;
char        as_capture_date[5];
char        as_iid[14];
char        as_track2[40];
char        as_card[21];
char        as_ret_ref_no[15];
char        as_acceptor_term_id[17];
char        as_acceptor_id_code[16];            short as_acceptor_id_code_ind = -1;
char        as_acceptor_name[41];
char        as_trx_currency[4];                 short as_trx_currency_ind=-1;
char        as_settl_currency[4];               short as_settl_currency_ind=-1;
char        as_terminal_data[20];
char        as_pos_response_code[24];           short as_pos_response_code_ind=-1;
char        as_pos_settl_data[16];              short as_pos_settl_data_ind=-1;
char        as_trx_type[2];
char        as_process_code[7];
CS_DECIMAL  al_interface_code={4,0,0};
CS_DECIMAL  al_rev_standin={1,0,0};
/*return values*/
CS_DECIMAL  odec_avail={20,6,0};                short odec_avail_ind=-1;
CS_DECIMAL  odec_cur_bal={20,6,0};              short odec_cur_bal_ind=-1;
CS_DECIMAL  ol_error={6,0,0};                   short ol_error_ind=-1;
char        os_err_msg[1001]="\\0";              short os_err_msg_ind=-1;
CS_DECIMAL  ol_trx_code={12,0,0};               short ol_trx_code_ind=-1;
char        os_auth_code[7]="\\0";               short os_auth_code_ind=-1;
EXEC SQL END DECLARE SECTION;

/* code to set all input parameters are omitted */

 EXEC SQL exec :retcode = P_TEST_EX
    (   @al_channel_id      = :al_channel_id,
        @as_user_id         = :as_user_id,
        @as_machine_name    = :as_machine_name,
        @al_api_code        = :al_api_code,
        @al_comp_code       = :al_comp_code,
        @as_account         = :as_account,
        @as_msg_type        = :as_msg_type,
        @as_reference       = :as_reference,
        @al_pos             = :al_pos,
        @adt_transmission_date = :adt_transmission_date,
        @as_trx_amt         = :as_trx_amt           :as_trx_amt_ind,
        @as_settl_amt       = :as_settl_amt         :as_settl_amt_ind,
        @as_replacement_amt = :as_replacement_amt   :as_replacement_amt_ind,
        @as_trace_num       = :as_trace_num,
        @as_settl_rate      = :as_settl_rate        :as_settl_rate_ind,
        @as_local_time      = :as_local_time,
        @as_local_date      = :as_local_date,
        @as_settl_date      = :as_settl_date        :as_settl_date_ind,
        @as_expiry_date     = :as_expiry_date       :as_expiry_date_ind,
        @as_capture_date    = :as_capture_date,
        @as_iid             = :as_iid,
        @as_track2          = :as_track2,
        @as_card            = :as_card,
        @as_ret_ref_no      = :as_ret_ref_no,
        @as_acceptor_term_id= :as_acceptor_term_id,
        @as_acceptor_id_code= :as_acceptor_id_code  :as_acceptor_id_code_ind,
        @as_acceptor_name   = :as_acceptor_name,
        @as_trx_currency    = :as_trx_currency      :as_trx_currency_ind,
        @as_settl_currency  = :as_settl_currency    :as_settl_currency_ind,
        @as_terminal_data   = :as_terminal_data,
        @as_pos_response_code=:as_pos_response_code :as_pos_response_code_ind,
        @as_pos_settl_data  = :as_pos_settl_data    :as_pos_settl_data_ind,
        @as_trx_type        = :as_trx_type,
        @as_process_code    = :as_process_code,
        @al_interface_code  = :al_interface_code,
        @odec_avail         = :odec_avail:odec_avail_ind,
        @odec_cur_bal       = :odec_cur_bal:odec_cur_bal_ind,
        @ol_error           = :ol_error:ol_error_ind,
        @os_err_msg         = :os_err_msg:os_err_msg_ind,
        @ol_trx_code        = :ol_trx_code:ol_trx_code_ind,
        @os_auth_code       = :os_auth_code:os_auth_code_ind);

After the store procedure call, there is no sql error and return code=0, but all the output fields stay as NULL. In other words, the output fields were not overwritten by the store procedure. What did I do wrong here, please help.

Thanks

[MarkCulp]

I think you need to show us the actual code in your stored procedure. What you have given here are declarations of variables (within your stored procedure?) and not the parameter list to the SP.

[VolkerBarth]

That reminds me of that older product suggestion🙂

[Breck_Carter]

This forum is for questions about Sybase SQL Anywhere, not Sybase ASE or Microsoft SQL Server.

[Former Member]

Mark: Here is the actual store procedure, can you please tell me where did I do wrong in ESQL C code? Thanks,

CREATE PROCEDURE P_TEST_EX  
@al_channel_id          NUMERIC(2),
@as_user_id             VARCHAR(8),
@as_machine_name        VARCHAR(40),
@al_api_code            NUMERIC(4),
@al_comp_code           NUMERIC(4),
@as_account             VARCHAR(35),
@as_msg_type            VARCHAR(4),
@as_reference           VARCHAR(255),  
@al_pos                 NUMERIC(1), 
@adt_transmission_date  DATETIME,
@as_trx_amt             VARCHAR(18),
@as_settl_amt           VARCHAR(18),
@as_replacement_amt     VARCHAR(18),
@as_trace_num           VARCHAR(6),
@as_settl_rate          VARCHAR(18),
@as_local_time          VARCHAR(12),
@as_local_date          VARCHAR(12),
@as_settl_date          VARCHAR(12),
@as_expiry_date         VARCHAR(4),
@as_capture_date        VARCHAR(4),
@as_iid                 VARCHAR(13),
@as_track2              VARCHAR(39),
@as_card                VARCHAR(20),

@as_ret_ref_no          VARCHAR(14),
@as_acceptor_term_id    VARCHAR(16),
@as_acceptor_id_code    VARCHAR(15),
@as_acceptor_name       VARCHAR(40),
@as_trx_currency        VARCHAR(3),
@as_settl_currency      VARCHAR(3),
@as_terminal_data       VARCHAR(19),
@as_pos_response_code   VARCHAR(23),
@as_pos_settl_data      VARCHAR(15),
@as_trx_type            VARCHAR(1),
@as_process_code        VARCHAR(6),
@al_interface_code      NUMERIC(4,0),
@odec_avail             NUMERIC(20,6) OUTPUT,
@odec_cur_bal           NUMERIC(20,6) OUTPUT,
@ol_error               NUMERIC(6,0) OUTPUT,
@os_err_msg             VARCHAR(1000) OUTPUT,
@ol_trx_code            NUMERIC(12)  OUTPUT,
@os_auth_code           VARCHAR (6) OUTPUT

as  
BEGIN

SET NOCOUNT ON        
DECLARE @ls_message1 VARCHAR(4000)  
DECLARE @ll_count        NUMERIC(2)
DECLARE @adv_error       INT   
DECLARE @adv_sqlerrm        VARCHAR(256)   
DECLARE @ol_access          NUMERIC(1)   
DECLARE @ln_id              NUMERIC(12)
DECLARE @ll_msg_type        NUMERIC(4)
DECLARE @ll_msg_code        NUMERIC(3)
DECLARE @ll_auth_code       NUMERIC(6)
DECLARE @ll_max_code        NUMERIC(8)
DECLARE @ll_branch_code     NUMERIC(4) 
DECLARE @ll_teller_code     NUMERIC(4)
DECLARE @ll_trx_type        NUMERIC(3)
DECLARE @ll_split_count     NUMERIC(1)
DECLARE @ll_commit_ctr      NUMERIC(1)
DECLARE @ll_usetime         NUMERIC(1)
DECLARE @ll_time_out        NUMERIC(4)
DECLARE @ls_sms_numbers     VARCHAR(100)
DECLARE @ls_err_msg         VARCHAR(1000)
DECLARE @ldt_start_date     DATETIME
DECLARE @ldt_end_date       DATETIME
DECLARE @ll_timeout_occured NUMERIC(1)
DECLARE @ll_atm_trx_no      NUMERIC(12,0)
DECLARE @ldec_amount        NUMERIC(20,6)
DECLARE @ldt_datetime       DATETIME
DECLARE @ll_error           NUMERIC(6)

SELECT @ls_message1 = 'P_API_TEST_EX(@al_channel_id=' +
             CASE WHEN @al_channel_id IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR,@al_channel_id) END + ', @as_user_id ='+
             CASE WHEN @as_user_id IS NULL THEN 'NULL' ELSE @as_user_id END  + ', @as_machine_name ='+
             CASE WHEN @as_machine_name IS NULL THEN 'NULL' ELSE @as_machine_name END + ', @al_api_code =' + 
             CASE WHEN @al_api_code IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR,@al_api_code) END + ', @al_comp_code ='+
             CASE WHEN @al_comp_code IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR,@al_comp_code) END + ', @as_account ='+                
             CASE WHEN @as_account IS NULL THEN 'NULL' ELSE @as_account END + ', @as_msg_type ='+
             CASE WHEN @as_msg_type IS NULL THEN 'NULL' ELSE @as_msg_type END + ', @as_reference ='+
             CASE WHEN @as_reference IS NULL THEN 'NULL' ELSE @as_reference END + ', @al_pos ='+                 
             CASE WHEN @al_pos IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR,@al_pos) END + ', @adt_transmission_date ='+
             CASE WHEN @adt_transmission_date IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR,@adt_transmission_date) END + ', @as_trx_amt ='+
             CASE WHEN @as_trx_amt IS NULL THEN 'NULL' ELSE @as_trx_amt END + ', @as_settl_amt ='+                                  
             CASE WHEN @as_settl_amt IS NULL THEN 'NULL' ELSE @as_settl_amt END + ', @as_replacement_amt ='+   
             CASE WHEN @as_replacement_amt IS NULL THEN 'NULL' ELSE @as_replacement_amt END + ', @as_trace_num ='+   
             CASE WHEN @as_trace_num IS NULL THEN 'NULL' ELSE @as_trace_num END + ', @as_settl_rate ='+   
             CASE WHEN @as_settl_rate IS NULL THEN 'NULL' ELSE @as_settl_rate END + ', @as_local_time ='+   
             CASE WHEN @as_local_time IS NULL THEN 'NULL' ELSE @as_local_time END + ', @as_local_date ='+   
             CASE WHEN @as_local_date IS NULL THEN 'NULL' ELSE @as_local_date END + ', @as_settl_date ='+   
             CASE WHEN @as_settl_date IS NULL THEN 'NULL' ELSE @as_settl_date END + ', @as_expiry_date ='+   
             CASE WHEN @as_expiry_date IS NULL THEN 'NULL' ELSE @as_expiry_date END + ', @as_capture_date ='+   
             CASE WHEN @as_capture_date IS NULL THEN 'NULL' ELSE @as_capture_date END + ', @as_iid ='+   
             CASE WHEN @as_iid IS NULL THEN 'NULL' ELSE @as_iid END + ', @as_track2 ='+   
             CASE WHEN @as_track2 IS NULL THEN 'NULL' ELSE @as_track2 END + ', @as_card ='+   
             CASE WHEN @as_card IS NULL THEN 'NULL' ELSE @as_card END + ', @as_ret_ref_no ='+   
             CASE WHEN @as_ret_ref_no IS NULL THEN 'NULL' ELSE @as_ret_ref_no END + ', @as_acceptor_term_id ='+   
             CASE WHEN @as_acceptor_term_id IS NULL THEN 'NULL' ELSE @as_acceptor_term_id END + ', @as_acceptor_id_code ='+   
             CASE WHEN @as_acceptor_id_code IS NULL THEN 'NULL' ELSE @as_acceptor_id_code END + ', @as_acceptor_name ='+   
             CASE WHEN @as_acceptor_name IS NULL THEN 'NULL' ELSE @as_acceptor_name END + ', @as_trx_currency ='+   
             CASE WHEN @as_trx_currency IS NULL THEN 'NULL' ELSE @as_trx_currency END + ', @as_settl_currency ='+           
             CASE WHEN @as_settl_currency IS NULL THEN 'NULL' ELSE @as_settl_currency END + ', @as_terminal_data ='+   
             CASE WHEN @as_terminal_data IS NULL THEN 'NULL' ELSE @as_terminal_data END + ', @as_pos_response_code ='+   
             CASE WHEN @as_pos_response_code IS NULL THEN 'NULL' ELSE @as_pos_response_code END + ', @as_pos_settl_data ='+ 
             CASE WHEN @as_pos_settl_data IS NULL THEN 'NULL' ELSE @as_pos_settl_data END + ', @as_trx_type ='+   
             CASE WHEN @as_trx_type IS NULL THEN 'NULL' ELSE @as_trx_type END + ', @as_process_code ='+   
             CASE WHEN @as_process_code IS NULL THEN 'NULL' ELSE @as_process_code END + ', @al_interface_code ='+   
             CASE WHEN @al_interface_code IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR,@al_interface_code) END + ')'

SELECT @ol_error  = 0    
SELECT @os_err_msg  = ''    
SELECT @ll_msg_type    = CONVERT(NUMERIC,@as_msg_type)
SELECT @ldt_start_date = CONVERT(DATETIME, (CONVERT(VARCHAR,GETDATE(),103) || ' ' || CONVERT(VARCHAR,GETDATE(),108)), 103)
SELECT @ldec_amount  = CASE WHEN @as_trx_amt IS NULL THEN 0 ELSE CONVERT(NUMERIC,@as_trx_amt)/1000 END
SELECT @as_account   = CASE WHEN @as_account IS NULL THEN '' ELSE @as_account END

--SELECT @as_settl_amt = CONVERT(VARCHAR, CONVERT(NUMERIC,@as_settl_amt)/1000)
--SELECT @as_replacement_amt = CONVERT(VARCHAR, CONVERT(NUMERIC,@as_replacement_amt)/1000)
--SELECT @as_settl_rate = CONVERT(VARCHAR, CONVERT(NUMERIC,@as_settl_rate)/1000)

-- get the interface parametrization 
SELECT @ll_split_count = SPLITCOUNT,
   @ls_sms_numbers = SMSNUMBER,
   @ll_time_out    = ATMTIMEOUT,
   @ll_commit_ctr  = COMMITCTR,
   @ll_usetime     = USETIME
FROM ATM_PARAM 
WHERE COMP_CODE = @al_comp_code 
AND   INT_CODE  = @al_interface_code
SELECT @adv_error = @@ERROR , @ll_count  = @@ROWCOUNT  
 IF @adv_error != 0 OR @ll_count <> 1 
BEGIN  
    SELECT @ol_error = -89
    SELECT @adv_sqlerrm = description FROM master..sysmessages WHERE error = @adv_error   
    SELECT @os_err_msg = 'P_API_WITHDRAWAL_ADDREF_EX, error while selecting from ATM_PARM ' || @adv_sqlerrm  
    BEGIN TRANSACTION
    SAVE TRANSACTION WITHDR
    GOTO ERROR
END

-- get Authorization code to insert it in ATM TRX
EXEC P_GET_ATM_AUTHCODE @ol_error out, @os_err_msg out, @ll_auth_code out
SELECT @os_auth_code =  CONVERT(VARCHAR,@ll_auth_code)
IF @ol_error < 0 
BEGIN
    SELECT @ol_error = -89
    BEGIN TRANSACTION
    SAVE TRANSACTION WITHDR
    GOTO ERROR
END
EXEC  P_ATM_INSERT_ATM_TRX1 @al_comp_code, @adt_transmission_date, @as_trx_amt, @as_settl_amt, @as_trace_num, @as_settl_rate, 
                         @as_local_time, @as_local_date, @as_settl_date, @as_expiry_date, @as_capture_date, @as_iid, @as_track2, 
                         @as_card, @as_ret_ref_no, @as_acceptor_term_id, @as_acceptor_id_code, @as_acceptor_name, @as_trx_currency,
                         @as_settl_currency, @as_terminal_data, @as_pos_response_code, @as_pos_settl_data, @as_replacement_amt, 
                         NULL, '88', @as_trx_type, @os_auth_code, @as_account, NULL, NULL, NULL, @ll_msg_type, NULL, NULL, NULL, NULL, 
                         NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, @al_interface_code, 
                         @ldt_start_date, @ldt_start_date, 1, @ll_atm_trx_no out, @ol_error out, @os_err_msg out
IF @ol_error < 0 
BEGIN
    SELECT @ol_error = -1
    BEGIN TRANSACTION
    SAVE TRANSACTION WITHDR
    GOTO ERROR
END 
COMMIT

IF @ll_time_out = 5 set lock wait 5
IF @ll_time_out = 10 set lock wait 10
IF @ll_time_out = 15 set lock wait 15
IF @ll_time_out = 20 set lock wait 20
IF @ll_time_out = 25 set lock wait 25
IF @ll_time_out = 30 set lock wait 30
IF @ll_time_out = 35 set lock wait 35
IF @ll_time_out = 40 set lock wait 40

BEGIN TRANSACTION
SAVE TRANSACTION WITHDR

--Check Access  
EXEC P_API_CHECK_ACCESS @al_api_code,@as_user_id,@as_machine_name,@ol_access OUTPUT     
IF @ol_access = 0 
    BEGIN
        SELECT @ol_error = -99 
        SELECT @os_err_msg = 'P_API_WITHDRAWAL_ADDREF_EX, Access Denied' 
        GOTO ERROR 
    END

--get transaction branch code, teller code and transaction type
IF @as_trx_type not in ('A','P','B')
BEGIN
    SELECT @ol_error = -1 
    SELECT @os_err_msg = 'P_API_WITHDRAWAL_ADDREF_EX, Invalid Parmeter @as_trx_type ' || CONVERT(VARCHAR,@as_trx_type)
    GOTO ERROR 
END

IF @as_process_code = '090000' AND @al_pos = 1 SELECT @ll_msg_code = 207
ELSE IF @as_process_code = '010000' AND @al_pos = 1 SELECT @ll_msg_code = 208
ELSE IF @as_process_code = '000000' AND @al_pos = 1 SELECT @ll_msg_code = 200
ELSE IF @as_process_code = '580000' AND @al_pos = 1 
    BEGIN
        SELECT @ol_error = -126 -- Invalid Transaction  
        SELECT @os_err_msg = 'P_API_WITHDRAWAL_ADDREF_EX, Refund transaction denied' 
        GOTO ERROR 
    END 
ELSE IF @as_process_code = '310000' SELECT @ll_msg_code = 201
ELSE SELECT @ll_msg_code = 200

SELECT @as_iid = SUBSTRING(@as_iid,3,LEN(@as_iid))
EXEC P_GET_ATM_PARAM @al_comp_code, @al_pos, @as_iid, @as_acceptor_term_id, @ll_msg_code, @ll_branch_code out, @ll_teller_code out, @ll_trx_type out, @ol_error out, @os_err_msg out
IF @ol_error < 0 
BEGIN
    SELECT @ol_error = -89
    GOTO ERROR
END

--call Withdrawal API

SELECT @ldt_datetime  = CASE WHEN @ll_usetime = 1 THEN GETDATE() ELSE @adt_transmission_date END
SELECT @as_account = SUBSTRING(@as_account,3, LEN(@as_account))
EXEC P_TEST_EX_S @al_comp_code, @ll_branch_code, @ll_teller_code, @ll_trx_type, @as_card, @as_account, @ldec_amount, @as_trx_currency, @ldt_datetime, @as_reference, @al_pos, '', '', @ll_split_count, @ll_commit_ctr, @odec_avail out, @odec_cur_bal out, @ol_error out, @ol_trx_code out, @os_err_msg out 
IF @ol_error < 0 
BEGIN
    GOTO ERROR
END

SELECT @ldt_end_date = CONVERT(DATETIME, (CONVERT(VARCHAR,GETDATE(),103) || ' ' || CONVERT(VARCHAR,GETDATE(),108)), 103)

UPDATE ATM_TRX
SET  STATUS = '00',
     CTS_TRS_BR = @ll_branch_code,
     CTS_TRS_NO = @ol_trx_code, -- here for other apis
     --TO_ACC-- here for other apis
     END_DATE = @ldt_end_date,
     TIME_OUT = 0
  WHERE COMP_CODE = @al_comp_code
  AND   TRX_NO    = @ll_atm_trx_no
  SELECT @adv_error = @@ERROR , @ll_count  = @@ROWCOUNT  
 IF @adv_error != 0 
    BEGIN  
        SELECT @ol_error = - 1 
        SELECT @adv_sqlerrm = description FROM master..sysmessages WHERE error = @adv_error   
        SELECT @os_err_msg = 'P_API_WITHDRAWAL_ADDREF_EX, error while updating ATM_TRX ' || @adv_sqlerrm  
        GOTO ERROR
    END

ERROR:

IF @ol_error < 0 
BEGIN       
  ROLLBACK TRANSACTION WITHDR      
  IF LTRIM(RTRIM(@ls_sms_numbers)) <> '' AND LTRIM(RTRIM(@ls_sms_numbers)) IS NOT NULL AND @ol_error NOT IN (0, -1, -63, -64, -644, -120, -121, -124, -125, -126, -127) 
      BEGIN
          print 'sms'
          EXEC  P_ATM_SEND_SMS @ls_sms_numbers, 'Transaction Declined (88)', @os_err_msg, @ll_max_code out, @ll_error out, @ls_err_msg out
          IF @ol_error < 0 
          BEGIN
            SELECT @ll_error = - 1 
          END
      END
  SELECT @ldt_end_date = CONVERT(DATETIME, (CONVERT(VARCHAR,GETDATE(),103) || ' ' || CONVERT(VARCHAR,GETDATE(),108)), 103)
  UPDATE ATM_TRX
  SET    STATUS = CASE WHEN @ol_error = -63 THEN '14'--INVALID_CARD_NUMBER
                       WHEN @ol_error = -64 THEN '62'--RESTRICTED_CARD
                       WHEN @ol_error = -644 THEN '54'--EXPIRED_CARD
                       WHEN @ol_error = -120 THEN '51'--INSUFFICIENT_FUNDS
                       WHEN @ol_error = -121 THEN '61'--EXCEEDS_AMT_LIMIT
                       WHEN @ol_error = -124 THEN '76'--INELIGIBLE_ACCOUNT
                       WHEN @ol_error = -125 THEN '62'--RESTRICTED_CARD
                       WHEN @ol_error = -126 THEN '12'--INVALID_TRANSACTION
                       WHEN @ol_error = -127 THEN '78'--REFER_TO_ISSUER
                       WHEN @ol_error = -89 THEN '89'--DB_PROBLEM
                      ELSE '88' END ,--SYSTEM_ERROR
         CTS_TRS_BR = @ll_branch_code,
         --TO_ACC-- here for other apis
         END_DATE = @ldt_end_date,
         TIME_OUT = 0
  WHERE COMP_CODE = @al_comp_code
  AND   TRX_NO = @ll_atm_trx_no
  SELECT @adv_error = @@ERROR , @ll_count  = @@ROWCOUNT  
 IF @adv_error != 0 
    BEGIN  
        SELECT @ol_error = - 1 
        SELECT @adv_sqlerrm = description FROM master..sysmessages WHERE error = @adv_error   
        SELECT @os_err_msg = 'P_API_WITHDRAWAL_ADDREF_EX, error while updating ATM_TRX ' || @adv_sqlerrm  
    END       
  EXEC  P_IM_REQUEST_LOG_EXT 'STARTED P_API_WITHDRAWAL_ADDREF_EX ',@ls_message1,@al_channel_id,@as_user_id,@as_machine_name,@al_api_code,@ln_id OUTPUT
  EXEC  P_IM_REQUEST_LOG_DET_EXT @ln_id,'E',@os_err_msg 
  COMMIT
  SET NOCOUNT OFF 
END

ELSE 
BEGIN
   EXEC  P_IM_REQUEST_LOG_EXT 'STARTED P_API_WITHDRAWAL_ADDREF_EX ',@ls_message1,@al_channel_id,@as_user_id,@as_machine_name,@al_api_code,@ln_id OUTPUT
   EXEC  P_IM_REQUEST_LOG_DET_EXT @ln_id,'I','P_API_WITHDRAWAL_ADDREF_EX FINISHED SUCCESSFULLY'                        
   COMMIT
   SET NOCOUNT OFF 
END

END

--Jian