---
title: "SQL-035: EFT procedure family"
summary: Oracle EFT authorization helper calls used by EFTServices.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business
reference_type: package
---

# SQL-035: EFT procedure family

`EFTServices` uses the `pk82350_eft` Oracle package to look up person, provider, beneficiary, and debit-card authorization state without duplicating the EFT eligibility logic in VB code.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-035 |
| **Operation type** | Read |
| **Execution method** | Stored procedure |
| **Database** | Oracle |
| **Schema** | `WCIS_DBA` |
| **Command type** | StoredProcedure |
| **Business object** | `EFTServices` |
| **Criteria property** | `N/A` |
| **Lifecycle method** | `GetAuthorizationSequenceNumber`, `GetProviderAuthSeq`, `PersonHasActiveDebitCardAuth`, `PersonHasActiveAuth`, `PersonHasDebitCardEnrollment` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:inclaimnum` | Input | String | 8 | Optional claim number overload for `pk82350_eft.isauthorizedforeft`. |
| `:inclaimid` | Input | Decimal | N/A | Optional claim ID overload for `pk82350_eft.isauthorizedforeft`. |
| `:inpersonid` | Input | String / Number | N/A | Person identifier used by the worker and card-enrollment helpers. |
| `:incustomertypecode` | Input | Varchar2 | 5 | Customer type code passed to the EFT package. |
| `:inauthtypecode` | Input | Varchar2 | 5 | Authorization type code such as `CLDC` or `PRDD`. |
| `:indate` | Input | Date | N/A | Effective date passed to package functions that evaluate authorization timing. |
| `:inIncludePending` | Input | Varchar2 | 5 | `TRUE` to include pending authorizations; `FALSE` to require active/prenoted rows. |
| `:inprovidernum` | Input | Decimal | N/A | Provider number passed to `pk82350_eft.isproviderauthforeft`. |
| `:inbeneid` | Input | Decimal | N/A | Beneficiary identifier passed to `pk82350_eft.isbeneficiaryauthforeft`. |
| `:PARMOUT` | ReturnValue | Number | N/A | Returned authorization sequence number or count, depending on the called function. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PAYMENT_AUTHORIZATION](../database-tables.md#payment-authorization) | SELECT | `CUSTOMER_SECOND_ID`, `CUSTOMER_ID`, `CUSTOMER_TYPE_CODE`, `AUTHORIZATION_TYPE_CODE`, `AUTH_DATE`, `REVOKE_DATE`, `BANK_ACCOUNT_SEQ_NUM`, `PAYMENT_AUTHORIZATION_SEQ_NUM` |
| [BANK_ACCOUNT](../database-tables.md#bank-account) | JOIN | `BANK_ACCOUNT_SEQ_NUM`, `BANK_ACCOUNT_STATUS_CODE`, `PRENOTE_REQUIRED_FLAG`, `PRENOTE_SUCCESS_DATE` |
| [DEBIT_CARD_ENROLLMENT_REQUEST](../database-tables.md#debit-card-enrollment-request) | SELECT | `CARDHOLDER_ID_NUM`, `CARD_TERM_DATE` |

## Sample SQL

```sql
-- EFTServices worker/person authorization lookup
BEGIN
  :PARMOUT := pk82350_eft.isauthorizedforeft(
    :inclaimnum,
    :inpersonid,
    :incustomertypecode,
    :inauthtypecode,
    :indate,
    :inIncludePending
  );
END;

-- EFTServices provider authorization lookup
BEGIN
  :PARMOUT := pk82350_eft.isproviderauthforeft(
    :inprovidernum,
    :incustomertypecode,
    :inauthtypecode,
    :indate,
    :inIncludePending
  );
END;

-- EFTServices beneficiary authorization lookup
BEGIN
  :PARMOUT := pk82350_eft.isbeneficiaryauthforeft(
    :inbeneid,
    :inauthtypecode,
    :indate
  );
END;

-- EFTServices count helpers
BEGIN
  :PARMOUT := pk82350_eft.ActiveAuthCountByPerson(
    :inpersonid,
    :incustomertypecode,
    :inauthtypecode,
    :indate,
    :inIncludePending
  );
END;

BEGIN
  :PARMOUT := pk82350_eft.CardEnrollmentCountByPerson(:inpersonid);
END;
```

## Operations and processes
- [EFT authorization support](../operations/eft-authorization-support.md)

## Business rules
- [PROC-RULE-016: Pending-inclusive authorization checks bypass bank-account status and prenote gating.](../business-rules.md#proc-rule-016-pending-inclusive-authorization-checks-bypass-bank-account-status-and-prenote-gating)
- [PROC-RULE-017: Non-pending authorization checks require an active bank account and successful prenote when prenote is required.](../business-rules.md#proc-rule-017-non-pending-authorization-checks-require-an-active-bank-account-and-successful-prenote-when-prenote-is-required)
- [PROC-RULE-018: Beneficiary EFT authorizations must be effective, unreversed, and backed by an active bank account.](../business-rules.md#proc-rule-018-beneficiary-eft-authorizations-must-be-effective-unreversed-and-backed-by-an-active-bank-account)
- [PROC-RULE-019: Provider EFT authorization checks use the same pending-versus-active gating as person authorization checks.](../business-rules.md#proc-rule-019-provider-eft-authorization-checks-use-the-same-pending-versus-active-gating-as-person-authorization-checks)
- [PROC-RULE-020: Active-authorization counts honor the same pending and prenote rules as sequence lookups.](../business-rules.md#proc-rule-020-active-authorization-counts-honor-the-same-pending-and-prenote-rules-as-sequence-lookups)
- [PROC-RULE-021: Only non-terminated debit card enrollments count as active enrollments.](../business-rules.md#proc-rule-021-only-non-terminated-debit-card-enrollments-count-as-active-enrollments)

## Sources
- `Business\SAIF.Business\EFTServices.vb`