---
title: "pk82350_eft stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "SAIF"
last_reviewed: "2026-07-24"
source_schema: "`WCIS_DBA`"
source_name: "pk82350_eft"
source_type: "`Package`"
oracle_status: "found"
library: SAIF.Business
reference_type: package
---

# pk82350_eft

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | `Package` |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business` |
| **Calling classes** | `ClaimServices, EFTServices` |

## Purpose

EFT-authorization package that validates authorization, beneficiary enrollment, provider authorization, and card-enrollment counts.

## Procedures / Methods

### isauthorizedforeft

| Field | Value |
| --- | --- |
| **Returns** | A `NUMBER` payment-authorization sequence when a matching row exists, or the package default negative code when no authorization matches. |
| **Called by** | `ClaimServices.GetAuthorizationSequenceNumber`, `EFTServices.GetAuthorizationSequenceNumber` |

**Description:** Delegates person authorization lookups to the shared EFT helper logic, using either the claim-number or claim-ID overload.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inclaimnum` | IN | VARCHAR2 | — | SAIF claim number used by the package. |
| `inpersonid` | IN | NUMBER | — | Person identifier used by the package. |
| `incustomertypecode` | IN | VARCHAR2 | — | Customer type code used by the package. |
| `inauthtypecode` | IN | VARCHAR2 | — | Authorization type code used by the package. |
| `indate` | IN | DATE | SYSDATE | Effective date used by the package. |
| `inIncludePending` | IN | VARCHAR2 | 'FALSE' | TRUE to include pending authorizations. |
| `inclaimid` | IN | NUMBER | — | Claim identifier used by the package. |

### isbeneficiaryauthforeft

| Field | Value |
| --- | --- |
| **Returns** | A `NUMBER` payment-authorization sequence when a beneficiary authorization exists, or `-222` when no row matches. |
| **Called by** | `ClaimServices.HasActiveBeneEFTAuth` |

**Description:** Checks beneficiary EFT authorization state against active bank-account rows.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inbeneid` | IN | NUMBER | — | Beneficiary identifier used by the package. |
| `inauthtypecode` | IN | VARCHAR2 | — | Authorization type code used by the package. |
| `indate` | IN | DATE | SYSDATE | Effective date used by the package. |

### isproviderauthforeft

| Field | Value |
| --- | --- |
| **Returns** | A `NUMBER` payment-authorization sequence when a provider authorization exists, or the package default negative code when no row matches. |
| **Called by** | `EFTServices.GetProviderAuthSeq` |

**Description:** Checks provider EFT authorization state using the same pending-versus-active logic used for person authorizations.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inprovidernum` | IN | NUMBER | — | Provider number used by the package. |
| `incustomertypecode` | IN | VARCHAR2 | 'PROV' | Customer type code used by the package. |
| `inauthtypecode` | IN | VARCHAR2 | 'PRDD' | Authorization type code used by the package. |
| `indate` | IN | DATE | SYSDATE | Effective date used by the package. |
| `inIncludePending` | IN | VARCHAR2 | — | TRUE to include pending authorizations. |

### ActiveAuthCountByPerson

| Field | Value |
| --- | --- |
| **Returns** | A `NUMBER` count of matching person authorizations. |
| **Called by** | `EFTServices.PersonHasActiveDebitCardAuth`, `EFTServices.PersonHasActiveAuth` |

**Description:** Counts active or pending person authorizations for the requested customer and authorization type.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inpersonid` | IN | VARCHAR2 | — | Person identifier used by the package. |
| `incustomertypecode` | IN | VARCHAR2 | — | Customer type code used by the package. |
| `inauthtypecode` | IN | VARCHAR2 | 'CLDC' | Authorization type code used by the package. |
| `indate` | IN | DATE | SYSDATE | Effective date used by the package. |
| `inIncludePending` | IN | VARCHAR2 | 'FALSE' | TRUE to include pending authorizations. |

### CardEnrollmentCountByPerson

| Field | Value |
| --- | --- |
| **Returns** | A `NUMBER` count of non-terminated debit-card enrollment rows. |
| **Called by** | `EFTServices.PersonHasDebitCardEnrollment` |

**Description:** Counts active debit-card enrollment requests for the supplied cardholder.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inpersonid` | IN | VARCHAR2 | — | Person identifier used by the package. |

## Business Rules

Rules extracted from this procedure's verified logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PROC-RULE-016](../business-rules.md#proc-rule-016-pending-inclusive-authorization-checks-bypass-bank-account-status-and-prenote-gating) | Pending-inclusive authorization checks bypass bank-account status and prenote gating. | `inIncludePending = 'TRUE'`. | Set `v_pending_flag = 1` before calling the shared authorization lookup. |
| [PROC-RULE-017](../business-rules.md#proc-rule-017-non-pending-authorization-checks-require-an-active-bank-account-and-successful-prenote-when-prenote-is-required) | Non-pending authorization checks require an active bank account and successful prenote when prenote is required. | Shared `isauthforeft` path runs with `v_pending_flag = 0`. | Match only `bank_account_status_code IN ('NEW','VALID')` rows where prenote is not required or `prenote_success_date` is populated. |
| [PROC-RULE-018](../business-rules.md#proc-rule-018-beneficiary-eft-authorizations-must-be-effective-unreversed-and-backed-by-an-active-bank-account) | Beneficiary EFT authorizations must be effective, unreversed, and backed by an active bank account. | `trunc(auth_date) <= indate`, `revoke_date IS NULL`, and `bank_account_status_code IN ('NEW','VALID')`. | Return the matching `payment_authorization_seq_num`; otherwise keep the default `-222`. |
| [PROC-RULE-019](../business-rules.md#proc-rule-019-provider-eft-authorization-checks-use-the-same-pending-versus-active-gating-as-person-authorization-checks) | Provider EFT authorization checks use the same pending-versus-active gating as person authorization checks. | `inIncludePending = 'TRUE'` or the default non-pending path. | Either include pending rows or require `NEW`/`VALID` status plus a successful prenote when required. |
| [PROC-RULE-020](../business-rules.md#proc-rule-020-active-authorization-counts-honor-the-same-pending-and-prenote-rules-as-sequence-lookups) | Active-authorization counts honor the same pending and prenote rules as sequence lookups. | `inIncludePending = 'TRUE'` or the default non-pending path. | Count all matching authorizations or only active/prenoted authorizations for the person and auth type. |
| [PROC-RULE-021](../business-rules.md#proc-rule-021-only-non-terminated-debit-card-enrollments-count-as-active-enrollments) | Only non-terminated debit card enrollments count as active enrollments. | `card_term_date IS NULL`. | Include the enrollment row in the returned count. |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| OP-015 | Delegates to the shared `isauthforeft` query path to return a matching payment authorization sequence. | Claim/EFT authorization helpers call `pk82350_eft.isauthorizedforeft`. |
| OP-016 | Queries beneficiary payment authorizations and returns the matching authorization sequence number. | Claim/EFT beneficiary authorization checks call `pk82350_eft.isbeneficiaryauthforeft`. |
| OP-017 | Queries provider payment authorizations and returns the matching authorization sequence number. | `EFTServices.GetProviderAuthSeq` calls `pk82350_eft.isproviderauthforeft`. |
| OP-018 | Counts active payment authorizations for a person and authorization type. | `EFTServices.PersonHasActiveDebitCardAuth` and `PersonHasActiveAuth` call `pk82350_eft.ActiveAuthCountByPerson`. |
| OP-019 | Counts active debit-card enrollment rows for the cardholder. | `EFTServices.PersonHasDebitCardEnrollment` calls `pk82350_eft.CardEnrollmentCountByPerson`. |

> These operations are also listed in [operations-processes.md](../operations-processes.md).

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
FUNCTION isauthorizedforeft (
      inclaimnum       IN   VARCHAR2,
      inpersonid       IN   NUMBER,
      incustomertypecode IN VARCHAR2,
      inauthtypecode   IN   VARCHAR2,
      indate           IN   DATE := SYSDATE,
      inIncludePending IN   VARCHAR2 := 'FALSE'
   )
      RETURN NUMBER;

FUNCTION isauthorizedforeft (
      inclaimid        IN   NUMBER,
      inpersonid       IN   NUMBER,
      incustomertypecode IN VARCHAR2,
      inauthtypecode   IN   VARCHAR2,
      indate           IN   DATE := SYSDATE,
      inIncludePending IN   VARCHAR2 := 'FALSE'
   )
      RETURN NUMBER;

FUNCTION isbeneficiaryauthforeft (
      inbeneid         IN   NUMBER,
      inauthtypecode   IN   VARCHAR2,
      indate           IN   DATE := SYSDATE
   )
      RETURN NUMBER;

FUNCTION isproviderauthforeft (
      inprovidernum      IN   NUMBER,
      incustomertypecode IN   VARCHAR2 := 'PROV',
      inauthtypecode     IN   VARCHAR2 := 'PRDD',
      indate             IN   DATE     := SYSDATE,
      inIncludePending   IN   VARCHAR2
   )
      RETURN NUMBER;

FUNCTION ActiveAuthCountByPerson (
      inpersonid       IN   VARCHAR2,
      incustomertypecode IN VARCHAR2,
      inauthtypecode   IN   VARCHAR2 := 'CLDC',
      indate           IN   DATE     := SYSDATE,
      inIncludePending IN   VARCHAR2 := 'FALSE'
   )
      RETURN NUMBER;

FUNCTION CardEnrollmentCountByPerson (
      inpersonid       IN   VARCHAR2
   )
      RETURN NUMBER;
```

</details>

<details>
<summary>PL/SQL package/procedure body</summary>

```sql
FUNCTION isauthforeft (
      inpersonid         IN   NUMBER,
      incustomertypecode IN   VARCHAR2,
      inauthtypecode     IN   VARCHAR2,
      indate             IN   DATE,
      inIncludePending   IN   VARCHAR2
   )
      RETURN NUMBER
   AS
      v_paymentauthorizationseqnum   NUMBER := 0;
      v_return                       NUMBER := -10;
      v_pending_flag                 NUMBER := 0;

      CURSOR geteftauth
      IS
         SELECT payment_authorization_seq_num
           FROM payment_authorization
           JOIN bank_account using (bank_account_seq_num)
        WHERE customer_second_id = TO_CHAR (inpersonid)
            AND customer_type_code = incustomertypecode
            AND authorization_type_code = inauthtypecode
            AND trunc(auth_date) <= indate
            AND revoke_date IS NULL
            AND (v_pending_flag = 1
             OR (v_pending_flag = 0
                 AND bank_account_status_code in ('NEW','VALID')
                 AND (prenote_required_flag = 'N'
                   OR prenote_required_flag = 'Y' and prenote_success_date is not null)
                 ));
      BEGIN

      IF inIncludePending = 'TRUE'
      THEN
         v_pending_flag := 1;
      END IF;

      OPEN geteftauth;

      FETCH geteftauth
       INTO v_paymentauthorizationseqnum;

      IF geteftauth%FOUND
      THEN
         v_return := v_paymentauthorizationseqnum;
      END IF;

      CLOSE geteftauth;

      RETURN v_return;
   END;

FUNCTION isauthorizedforeft (
      inclaimnum       IN   VARCHAR2,
      inpersonid       IN   NUMBER,
      incustomertypecode IN VARCHAR2,
      inauthtypecode   IN   VARCHAR2,
      indate           IN   DATE := SYSDATE,
      inIncludePending IN   VARCHAR2 := 'FALSE'
   )
      RETURN NUMBER
   AS
      -- create and initialize local variables
      v_return   NUMBER := -40;
   BEGIN
      v_return :=
                isauthforeft (inpersonid, incustomertypecode, inauthtypecode, indate, inIncludePending);
      RETURN v_return;
   END;

FUNCTION isauthorizedforeft (
      inclaimid        IN   NUMBER,
      inpersonid       IN   NUMBER,
      incustomertypecode IN VARCHAR2,
      inauthtypecode   IN   VARCHAR2,
      indate           IN   DATE := SYSDATE,
      inIncludePending IN   VARCHAR2 := 'FALSE'
   )
      RETURN NUMBER
   AS
      -- create and initialize local variables
      v_return     NUMBER       := -50;

   BEGIN
      v_return :=
             isauthforeft (inpersonid, incustomertypecode, inauthtypecode, indate, inIncludePending);
      RETURN v_return;
   END;

FUNCTION isbeneficiaryauthforeft (
      inbeneid         IN   NUMBER,
      inauthtypecode   IN   VARCHAR2,
      indate           IN   DATE := SYSDATE
   )
      RETURN NUMBER
   AS
      v_paymentauthorizationseqnum   NUMBER := 0;
      v_return                       NUMBER := -222;

      CURSOR geteftauth
      IS
         SELECT payment_authorization_seq_num
           FROM payment_authorization
           JOIN bank_account using (bank_account_seq_num)
          WHERE customer_second_id = TO_CHAR (inbeneid)
            AND authorization_type_code = inauthtypecode
            AND trunc(auth_date) <= indate
            AND revoke_date IS NULL
            AND bank_account_status_code in ('NEW','VALID');
   BEGIN

      OPEN geteftauth;

      FETCH geteftauth
       INTO v_paymentauthorizationseqnum;

      IF geteftauth%FOUND
      THEN
         v_return := v_paymentauthorizationseqnum;
      END IF;

      CLOSE geteftauth;

      RETURN v_return;
   END;

FUNCTION isproviderauthforeft (
      inprovidernum      IN   NUMBER,
      incustomertypecode IN   VARCHAR2 := 'PROV',
      inauthtypecode     IN   VARCHAR2 := 'PRDD',
      indate             IN   DATE     := SYSDATE,
      inIncludePending   IN   VARCHAR2
   )
      RETURN NUMBER
   AS
      -- create and initialize local variables
      v_paymentauthorizationseqnum   NUMBER := 0;
      v_return                       NUMBER := -55;
      v_pending_flag                 NUMBER := 0;

      CURSOR geteftauth
      IS
         SELECT payment_authorization_seq_num
           FROM payment_authorization
           JOIN bank_account using (bank_account_seq_num)
          WHERE customer_id = TO_CHAR(inprovidernum)
            AND customer_type_code = incustomertypecode
            AND authorization_type_code = inauthtypecode
            AND trunc(auth_date) <= indate
            AND revoke_date IS NULL
            AND (v_pending_flag = 1
             OR (v_pending_flag = 0
                 AND bank_account_status_code in ('NEW','VALID')
                 AND (prenote_required_flag = 'N'
                   OR prenote_required_flag = 'Y' and prenote_success_date is not null)
                 ));
   BEGIN
      IF inIncludePending = 'TRUE'
      THEN
         v_pending_flag := 1;
      END IF;

      OPEN geteftauth;

      FETCH geteftauth
       INTO v_paymentauthorizationseqnum;

      IF geteftauth%FOUND
      THEN
         v_return := v_paymentauthorizationseqnum;
      END IF;

      CLOSE geteftauth;

      RETURN v_return;
   END;

FUNCTION ActiveAuthCountByPerson (
      inpersonid       IN   VARCHAR2,
      incustomertypecode IN VARCHAR2,
      inauthtypecode   IN   VARCHAR2 := 'CLDC',
      indate           IN   DATE     := SYSDATE,
      inIncludePending IN   VARCHAR2 := 'FALSE'
   )
      RETURN NUMBER
   AS
      -- create and initialize local variables
      v_return                       NUMBER := 0;
      v_pending_flag                 NUMBER := 0;

      CURSOR getauthcount
      IS
         SELECT count(*)
           FROM payment_authorization pa
           JOIN bank_account using (bank_account_seq_num)
          WHERE customer_second_id = inpersonid
            AND customer_type_code = incustomertypecode
            AND authorization_type_code = inauthtypecode
            AND trunc(auth_date) <= indate
            AND revoke_date IS NULL
            AND (v_pending_flag = 1
             OR (v_pending_flag = 0
                 AND bank_account_status_code in ('NEW','VALID')
                 AND (prenote_required_flag = 'N'
                   OR prenote_required_flag = 'Y' and prenote_success_date is not null)
                 ));
   BEGIN

      IF inIncludePending = 'TRUE'
      THEN
         v_pending_flag := 1;
      END IF;

      -- get authorization count for person and auth type code
      OPEN getauthcount;

      FETCH getauthcount
       INTO v_return;

      CLOSE getauthcount;

      RETURN v_return;
   END;

FUNCTION CardEnrollmentCountByPerson (
      inpersonid       IN   VARCHAR2
   )
      RETURN NUMBER
   AS
      -- create and initialize local variables
      v_return                       NUMBER := 0;

      CURSOR getcardcount
      IS
         SELECT count(*)
           FROM debit_card_enrollment_request
          WHERE cardholder_id_num = inpersonid
            AND card_term_date is null;
   BEGIN
      -- get card enrollment count for person
      OPEN getcardcount;

      FETCH getcardcount
       INTO v_return;

      CLOSE getcardcount;

      RETURN v_return;
   END;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

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