Error: EFT Processing Do Standard Debit encountered unexpected error: ORA-29273: HTTP request failed - during EFT Processing

An EFT Job failed during processing with the following error:
 
    Calling procedure:    UDEFTPRC - EFT Processing on 11/20/2017 at 22:01:51
      Parameter = Enter the EFT processing vendor  Value = SAGE
      Parameter = Query Name                       Value = 
      Parameter = Activity Types                   Value = 
      Parameter = Activity                         Value = 
      Parameter = Pull Cycle                       Value = 20
      Parameter = Current Month                    Value = Current
      Parameter = Batch Owner                      Value = 
      Parameter = Payment Source                   Value = 
      Parameter = Report Only?                     Value = N
      Parameter = Run EFT Detail Report?           Value = Y
      Parameter = Break Group 1                    Value = 
      Parameter = Break Group 2                    Value = 
      Parameter = Break Group 3                    Value = 
      Parameter = Output Destination               Value = FILE
      Parameter = Print Format                     Value = pdf
      Parameter = Printer Name                     Value = dummyptr
      Parameter = Copies                           Value = 1
      Parameter = Filename                         Value = /export/home/app/ta/mpbn/output/membership
      Process id is EFT - 00060
      Creating subdirectory /export/home/app/ta/mpbn/output/upload/eft00060/
    EFT Processing Do Standard Debit encountered unexpected error:  ORA-29273: HTTP request failed
ORA-06512: at "PBDS.EFT_WEB", line 227
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure.  Contact system administrator.
    EFT Processing Do Standard Debit encountered unexpected error:  ORA-29273: HTTP request failed
ORA-06512: at "PBDS.EFT_WEB", line 227
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure.  Contact system administrator.

The errors continue to duplicate for many pages...
 
    EFT Processing Do Standard Debit encountered unexpected error:  ORA-29273: HTTP request failed
ORA-06512: at "PBDS.EFT_WEB", line 227
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure.  Contact system administrator.
Processing completed.  2061 debits were attempted.
      Running report EFT Processing Summary Report...
      Running report EFT Processing Detail Report...
      Running report EFT Summary Report...
      Running report EFT Detail Report...
    Completed procedure:  UDEFTPRC - EFT Processing on 11/20/2017 at 22:12:19
    Operation rescheduled with new run date 12/20/2017 22:00:00.
  Waiting for a request...
The errors occur due to a network connection problem between the TA server and SAGE.  Here are the next steps to correct the unprocessed Pledge Transactions:

1.  From the Queue log, get the:
  • The Pull Cycle value from the Pull Cycle parameter of the EFT job.
  • The Process_ID (i.e. 'EFT - 00060').
2.  Run the following query to locate all the Pledge Transactions that were not processed, and will need to be fixed in order to be pulled by a subsequent EFT job:

The following variables are needed to make the Select statement work:
Pull_Cycle = taken from the Pull Cycle parameter of the EFT job from the Queue log
Effective_Date = found in the EFT Processing Summary Report (filename:  udeftsum.pdf)  The value can look like '11/20/17'.
The value in the report may also be blank, for example, due to the Current Month parameter = "CURRENT" or whatever value is found in the Queue log for the EFT job.
Process_ID = taken from Queue log or Report
 
SELECT T.*
FROM   TRANSACTIONS T, ACCOUNT_ACTIVITIES AA, ACCOUNTS A, WEB_MERCHANTS WM
WHERE  ('' IS NULL OR (T.ACCOUNT_ID, T.GIFT_DATE, T.GIFT_SEQ, T.ADJUSTMENT_SEQ) IN
      (SELECT ACCOUNT_ID, GIFT_DATE, GIFT_SEQ, ADJUSTMENT_SEQ
                      FROM   QUERY_RETRIEVED
                      WHERE  QUERY_NAME = ''))
      AND A.ACCOUNT_ID = T.ACCOUNT_ID
      AND NVL(A.STS, '') = 'A'
      AND AA.ACCOUNT_ID = T.ACCOUNT_ID
      AND AA.ACTIVITY_TYPE = T.ACTIVITY_TYPE
      AND T.TRANSACTION_TYPE = 'PL'
      AND T.SOFT_CREDIT_TYPE IS NULL
      AND T.STS = 'A'
      AND T.PAYMENT_METHOD = 'EF'
      AND T.EFT_ALIAS <> '*No Alias*'
      AND NVL(T.BANK_APPROVAL_STATUS, 'NE') = 'PE'     --only records that are orphaned as PEnding.
      AND NVL(T.SCHEDULE_START_DATE, TO_DATE(UD_EFT.EFFEC_DATE('{Pull_Cycle}', '{Effective_Date}'), 'DD-MON-RR')) <=
      TO_DATE(UD_EFT.EFFEC_DATE('{Pull_Cycle}', '{Effective_Date}'), 'DD-MON-RR')
      AND T.MERCHANT_ID = WM.MERCHANT_ID
      AND WM.WEB_VENDOR = 'SAGE'
      AND INSTR(WM.SERVICES, 'UDEFT') > 0
      AND T.PLEDGE_NUMBER IN (SELECT PLEDGE_NUMBER
                         FROM   EFT_LOG
                         WHERE  PROCESS_ID = '{Process_ID}')
      AND (TO_DATE(UD_EFT.EFFEC_DATE('{Pull_Cycle}', '{Effective_Date}'), 'DD-MON-RR') -
      NVL(T.DATE_LAST_PULLED, TO_DATE('01-JAN-0001', 'DD-MON-YYYY')) > 25 AND
      (T.PULL_CYCLE = '{Pull_Cycle}' OR '' IS NOT NULL))
      AND ((T.PLEDGE_STATUS IN ('UF', 'PF')) OR
      (T.PLEDGE_STATUS = 'FF' AND EXISTS
       (SELECT 'x'
            FROM   CLASSIFICATIONS C
            WHERE  C.ACCOUNT_ID = T.ACCOUNT_ID
                 AND C.CLASSIFICATION_CODE = SVN.GET('SG_AUTORENEW_CLASS_CODE')
                 AND C.CLASSIFICATION_VALUE = 'EFT'
                 AND C.STS = 'A'
                 AND NVL(C.END_DATE, SYSDATE) >= SYSDATE)))
      AND (T.GIFT_DATE = UD_EFT.MOST_RECENT_PLEDGE(T.ACCOUNT_ID) OR T.PLEDGE_STATUS IN ('UF', 'PF'));

3.  Get a count of the number of records returned in Step #2, and export the results to a spreadsheet.
4.  Get a count of the number of entries in the EFT Error log, and compare results to Step #3:
 
SELECT COUNT( * )
FROM   EFT_LOG_ERRORS e
WHERE  PROCESS_ID = '{Process_ID}'
      AND ERROR_LEVEL = 'E'
      AND ERROR_MESSAGE LIKE '%ORA-29273%';

5.  If the count between Step #3 and #4 is different, where there are more errors than Transactions records, use the following query to retrieve the records in question:
 
a.  Create a Temp table for the Transactions Records:
 
CREATE TABLE TMP_{Your initials}_{Case #}
TABLESPACE PBDS_DATA_TEMP AS ( {insert entire Select statement from Step #2} );

b.  Run the following query to locate the Transactions specified in the EFT Error Log, but not in the 
 
SELECT *
FROM   TRANSACTIONS T
WHERE  (T.ACCOUNT_ID, T.GIFT_DATE, T.GIFT_SEQ, T.ADJUSTMENT_SEQ) IN
      (SELECT E.ACCOUNT_ID, E.GIFT_DATE, E.GIFT_SEQ, E.ADJUSTMENT_SEQ
       FROM   EFT_LOG E, EFT_LOG_ERRORS EL
       WHERE  E.PROCESS_ID = EL.PROCESS_ID
            AND E.PROCESS_ID = '{Process_ID}'
            AND E.LONG_RECORD_SEQ = EL.ERROR_RECORD_SEQ
       MINUS
       SELECT T.ACCOUNT_ID, T.GIFT_DATE, T.GIFT_SEQ, T.ADJUSTMENT_SEQ
       FROM   {Temp Tablename})
ORDER BY T.ACCOUNT_ID, T.GIFT_DATE, T.GIFT_SEQ, T.ADJUSTMENT_SEQ;

Export the results to a spreadsheet and send to the client for review.  Let them determine why those Transactions would not be processed.

6.  Also, send the spreadsheet from Step #3 to the client/MS and confirm that the client wants those Transactions reset, to be processed during the next EFT job.  If so, use the attached Datafix as a template, update it with the correct values and run it.

Note:
  • If the client intends to run EFT again, Support suggests running EFT in Report Mode, to verify that the generated reports and record counts match the Transactions updated by the datafix. 
  • If the datafix is run during the following month, remember to mention to the client to use the EFT Current Month parameter = CURRENT - 1, or all the next month's Transactions will be accidentally processed.
          

 
  FXxxxxxxxx_Prod_v3.0 - Template.sql

Was this article helpful?