---
title: "PK83548_PROVIDER_CORR_HIST stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "SAIF"
last_reviewed: "2026-07-24"
library: SAIF.Business.Provider.PaymentPreference
reference_type: package
---

# PK83548_PROVIDER_CORR_HIST

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.Provider.PaymentPreference` |
| **Calling classes** | `clsProviderEFT` |

## Purpose

`PK83548_PROVIDER_CORR_HIST` resolves the provider primary tax-ID mailing address and inserts provider correspondence-history rows for EFT authorization-letter changes and EOB paperless-delivery changes.

## Procedures / Methods

### FETCH_TIN_ADDRESS

| Field | Value |
| --- | --- |
| **Returns** | `TIN_Address` record containing the mailing-address fields copied into history |
| **Called by** | `PK83548_PROVIDER_CORR_HIST.EFT_CORR_HIST`; `PK83548_PROVIDER_CORR_HIST.EOB_CORR_HIST` |

**Description:** Joins `PROVIDER_TAX_ID` to `PROVIDER`, filters to the provider primary tax ID row that is effective on the supplied date, fetches the first matching address record, and returns it to the insert procedures.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_provider_seq_num` | IN | `PROVIDER_TAX_ID.PROVIDER_SEQ_NUM%TYPE` | — | Provider identifier used to locate the primary tax ID row. |
| `in_eff_date` | IN | `PROVIDER_TAX_ID.EFF_DATE%TYPE` | — | Effective date used to evaluate the `PROVIDER_TAX_ID` row window. |
| `in_exp_date` | IN | `PROVIDER_TAX_ID.EXP_DATE%TYPE` | — | Expiration-date argument accepted by the function signature; the body lookup filters only on `in_eff_date`. |

### EFT_CORR_HIST

| Field | Value |
| --- | --- |
| **Returns** | No return value; inserts one `PROVIDER_CORR_HIST` row or raises an exception |
| **Called by** | `clsProviderEFT.CreateHistory` |

**Description:** Calls `FETCH_TIN_ADDRESS`, then inserts one EFT correspondence-history row with provider/location/user keys, correspondence date, EFT effective and expiration dates, resolved address fields, normalized foreign-address flag, and audit values.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_PROVIDER_LOCATION_SEQ_NUM` | IN | `PROVIDER_EFT_INTERFACE.PROVIDER_LOCATION_SEQ_NUM%TYPE` | — | Provider-location identifier written to history. |
| `in_PROVIDER_SEQ_NUM` | IN | `PROVIDER_EFT_INTERFACE.PROVIDER_SEQ_NUM%TYPE` | — | Provider identifier used for history and tax ID lookup. |
| `in_EXT_USER_TYPE_SEQ_NUM` | IN | `PROVIDER_EFT_INTERFACE.EXT_USER_TYPE_SEQ_NUM%TYPE` | — | External-user-type identifier written to history. |
| `in_CORRESPONDENCE_DATE` | IN | `PROVIDER_EFT_INTERFACE.UPDATE_DATE%TYPE` | — | Correspondence date stored in `PCH.CORRESPONDENCE_DATE`. |
| `in_EFF_DATE` | IN | `PROVIDER_EFT_INTERFACE.EFF_DATE%TYPE` | — | EFT effective date written to `PCH.EFT_EFF_DATE` and used by `FETCH_TIN_ADDRESS`. |
| `in_EXP_DATE` | IN | `PROVIDER_EFT_INTERFACE.EXP_DATE%TYPE` | — | EFT expiration date written to `PCH.EFT_EXP_DATE`. |
| `in_UPDATE_USER` | IN | `PROVIDER_EFT_INTERFACE.UPDATE_USER%TYPE` | — | Audit user written to `PCH.CREATE_USER`. |

### EOB_CORR_HIST

| Field | Value |
| --- | --- |
| **Returns** | No return value; inserts one `PROVIDER_CORR_HIST` row or raises an exception |
| **Called by** | No caller was identified in `Business\SAIF.Business.Provider.PaymentPreference` source; the member is present in the Oracle package source. |

**Description:** Calls `FETCH_TIN_ADDRESS` with `sysdate`, then inserts one EOB correspondence-history row with provider/location/user keys, `PAPERLESS_EOB_FLAG`, `EOB_CHANGE_LTR_FLAG = 'Y'`, resolved address fields, normalized foreign-address flag, and audit values.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_PROVIDER_LOCATION_SEQ_NUM` | IN | `PROVIDER_CORR_HIST.PROVIDER_LOCATION_SEQ_NUM%TYPE` | — | Provider-location identifier written to history. |
| `in_PROVIDER_SEQ_NUM` | IN | `PROVIDER_CORR_HIST.PROVIDER_SEQ_NUM%TYPE` | — | Provider identifier used for history and tax ID lookup. |
| `in_EXT_USER_TYPE_SEQ_NUM` | IN | `PROVIDER_CORR_HIST.EXT_USER_TYPE_SEQ_NUM%TYPE` | — | External-user-type identifier written to history. |
| `in_PAPERLESS_EOB_FLAG` | IN | `PROVIDER_CORR_HIST.PAPERLESS_EOB_FLAG%TYPE` | — | Paperless EOB flag written to history. |
| `in_UPDATE_USER` | IN | `PROVIDER_CORR_HIST.CREATE_USER%TYPE` | — | Audit user written to `PCH.CREATE_USER`. |

## Business Rules

Rules extracted from this package's PL/SQL logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PBR-001](../business-rules.md#pbr-001-only-the-provider-primary-tax-id-row-supplies-the-correspondence-address) | Only the provider primary tax ID row supplies the correspondence address. | `FETCH_TIN_ADDRESS` joins `PROVIDER_TAX_ID` to `PROVIDER` on the provider primary tax ID. | Correspondence rows copy address data only from the provider primary tax-ID record. |
| [PBR-002](../business-rules.md#pbr-002-the-tax-id-row-must-be-effective-on-the-supplied-eft-effective-date) | The tax ID row must be effective on the supplied EFT effective date. | `FETCH_TIN_ADDRESS` filters to rows where `EFF_DATE <= in_eff_date` and `EXP_DATE` is null or >= `in_eff_date`. | Expired or not-yet-effective tax ID rows are excluded from history insertion. |
| [PBR-003](../business-rules.md#pbr-003-fetch-tin-address-returns-only-the-first-matching-tax-id-address-row) | Fetch TIN address returns only the first matching tax ID address row. | The fetch loop exits when `%ROWCOUNT > 1`, leaving the first fetched record as the return value. | At most one `TIN_Address` record is returned to the insert procedures. |
| [PBR-004](../business-rules.md#pbr-004-eft-correspondence-history-inserts-are-always-marked-as-authorization-letter-events) | EFT correspondence history inserts are always marked as authorization letter events. | `EFT_CORR_HIST` inserts `PCH.EFT_AUTH_LTR_FLAG = 'Y'`. | Stored history rows identify the event as an EFT authorization letter. |
| [PBR-005](../business-rules.md#pbr-005-foreign-address-history-values-are-normalized-to-y-or-n-during-insert) | Foreign address history values are normalized to Y or N during insert. | Both insert statements use `case when TIN_Address_rec.FOREIGN_ADDRESS_FLAG is null then `'N'` else `'Y'` end`. | `PROVIDER_CORR_HIST.FOREIGN_ADDRESS_FLAG` is always stored as `Y` or `N`. |
| [PBR-006](../business-rules.md#pbr-006-correspondence-history-rows-always-stamp-create-date-create-number-and-create-user) | Correspondence history rows always stamp create date create number and create user. | Both insert statements set `CREATE_DATE = sysdate`, `CREATE_NUM = 0`, and `CREATE_USER = in_UPDATE_USER`. | Each inserted history row receives consistent audit values from the package. |
| [PBR-007](../business-rules.md#pbr-007-tax-id-lookup-failures-are-raised-instead-of-silently-ignored) | Tax ID lookup failures are raised instead of silently ignored. | `FETCH_TIN_ADDRESS` re-raises `NO_DATA_FOUND` and raises the package-local `v_error` for other failures. | Calling procedures do not continue after a failed address lookup. |
| [PBR-008](../business-rules.md#pbr-008-correspondence-history-insert-failures-are-re-raised-as-the-package-local-exception) | Correspondence history insert failures are re-raised as the package local exception. | `EFT_CORR_HIST` and `EOB_CORR_HIST` catch `WHEN OTHERS`, capture `SQLERRM`, and raise `v_error`. | Insert failures stop normal package completion instead of being swallowed. |

> These rules are documented in [Business rules](../business-rules.md) with one section per rule.

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| Fetch TIN correspondence address | Reads the provider primary tax-ID address that is effective on the supplied date and returns the first matching address record. | Called internally by `EFT_CORR_HIST` and `EOB_CORR_HIST`. |
| Insert EFT correspondence history | Inserts one `PROVIDER_CORR_HIST` row with `EFT_AUTH_LTR_FLAG = 'Y'`, EFT dates, normalized foreign-address flag, and audit fields. | `clsProviderEFT.CreateHistory` executes `PK83548_PROVIDER_CORR_HIST.EFT_CORR_HIST`. |
| Insert EOB correspondence history | Inserts one `PROVIDER_CORR_HIST` row with `PAPERLESS_EOB_FLAG`, `EOB_CHANGE_LTR_FLAG = 'Y'`, normalized foreign-address flag, and audit fields. | The package member exists in Oracle source even though no caller was identified in this library's VB source. |

> These operations are also listed in [Operations and processes](../operations-processes.md) with source reference back to this document.

## Source Code

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

```sql
PACKAGE PK83548_PROVIDER_CORR_HIST
AS

TYPE ref_cursor IS REF CURSOR;

TYPE TIN_Address IS RECORD(
      address_line1   PROVIDER_CORR_HIST.ADDRESS_LINE1%TYPE,
      address_line2   PROVIDER_CORR_HIST.ADDRESS_LINE2%TYPE,
      address_line3   PROVIDER_CORR_HIST.ADDRESS_LINE3%TYPE,
      address_line4   PROVIDER_CORR_HIST.ADDRESS_LINE4%TYPE,
      CITY_NAME       PROVIDER_CORR_HIST.CITY_NAME%TYPE,
      STATE_CODE    PROVIDER_CORR_HIST.STATE_CODE%TYPE,
      ZIP_CODE        PROVIDER_CORR_HIST.ZIP_CODE%TYPE,
      zip_code_ext    PROVIDER_CORR_HIST.ZIP_CODE_EXT%TYPE,
      FOREIGN_ADDRESS_FLAG PROVIDER_CORR_HIST.FOREIGN_ADDRESS_FLAG%type,
      COUNTRY_CODE PROVIDER_CORR_HIST.country_code%type
      );

TYPE get_tin_address_cur IS REF CURSOR RETURN TIN_Address ;

   FUNCTION FETCH_TIN_ADDRESS (
      in_provider_seq_num   IN PROVIDER_TAX_ID.PROVIDER_SEQ_NUM%TYPE,
      in_eff_date           IN PROVIDER_TAX_ID.EFF_DATE%TYPE,
      in_exp_date           IN PROVIDER_TAX_ID.EXP_DATE%TYPE)
      RETURN TIN_Address;

   PROCEDURE EFT_CORR_HIST (
      in_PROVIDER_LOCATION_SEQ_NUM   IN PROVIDER_EFT_INTERFACE.PROVIDER_LOCATION_SEQ_NUM%TYPE,
      in_PROVIDER_SEQ_NUM            IN PROVIDER_EFT_INTERFACE.PROVIDER_SEQ_NUM%TYPE,
      in_EXT_USER_TYPE_SEQ_NUM       IN PROVIDER_EFT_INTERFACE.EXT_USER_TYPE_SEQ_NUM%TYPE,
      in_CORRESPONDENCE_DATE         IN PROVIDER_EFT_INTERFACE.UPDATE_DATE%TYPE,
      in_EFF_DATE                    IN PROVIDER_EFT_INTERFACE.EFF_DATE%TYPE,
      in_EXP_DATE                    IN PROVIDER_EFT_INTERFACE.EXP_DATE%TYPE,
      in_UPDATE_USER                 IN PROVIDER_EFT_INTERFACE.UPDATE_USER%TYPE);

   PROCEDURE EOB_CORR_HIST(
   in_PROVIDER_LOCATION_SEQ_NUM in PROVIDER_CORR_HIST.PROVIDER_LOCATION_SEQ_NUM%TYPE
        ,in_PROVIDER_SEQ_NUM in PROVIDER_CORR_HIST.PROVIDER_SEQ_NUM%TYPE
        , in_EXT_USER_TYPE_SEQ_NUM in PROVIDER_CORR_HIST.EXT_USER_TYPE_SEQ_NUM%TYPE
        , in_PAPERLESS_EOB_FLAG          in PROVIDER_CORR_HIST.PAPERLESS_EOB_FLAG%TYPE
        , in_UPDATE_USER in PROVIDER_CORR_HIST.CREATE_USER%TYPE  );

END PK83548_PROVIDER_CORR_HIST;
 /
```

</details>

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

```sql
PACKAGE BODY PK83548_PROVIDER_CORR_HIST
/*****************************************************************
*Package Title  :
*Name           : PK83548_PROVIDER_CORR_HIST
*****************************************************************
*Description:
*   Insert history records intto the Provider_eob_interface and  PROVIDER_EFT_INTERFACE tables.
*
*PROGRAMMER     ANALYST                 DATE       MOD #
*   julfle                  julfle               08/27/2013
*******************************************************************/

AS
   FUNCTION FETCH_TIN_ADDRESS (
      in_provider_seq_num   IN PROVIDER_TAX_ID.PROVIDER_SEQ_NUM%TYPE,
      in_eff_date           IN PROVIDER_TAX_ID.EFF_DATE%TYPE,
      in_exp_date           IN PROVIDER_TAX_ID.EXP_DATE%TYPE)
      RETURN TIN_Address
   AS
      TIN_Address_cursor   get_tin_address_cur;
      TIN_Address_rec      TIN_Address;
      v_error              EXCEPTION;
      outresult            VARCHAR2 (400);
      v_row                NUMBER := 1;
   BEGIN
      OPEN TIN_Address_cursor FOR
         SELECT address_line1,
                address_line2,
                address_line3,
                address_line4,
                CITY_NAME,
                STATE_CODE,
                ZIP_CODE,
                zip_code_ext,
                PROVIDER_TAX_ID.FOREIGN_ADDRESS_FLAG,
                PROVIDER_TAX_ID.COUNTRY_CODE
           FROM    PROVIDER_TAX_ID
                JOIN
                   Provider
                ON (    provider_tax_id.PROVIDER_SEQ_NUM =
                           provider.PROVIDER_SEQ_NUM
                    AND provider.PRIMARY_TAX_ID = provider_tax_id.TAX_ID)
          WHERE     PROVIDER_TAX_ID.PROVIDER_SEQ_NUM = in_provider_seq_num
                AND PROVIDER_TAX_ID.EFF_DATE <= in_eff_date
                AND (   PROVIDER_TAX_ID.EXP_DATE IS NULL
                     OR PROVIDER_TAX_ID.EXP_DATE >= in_eff_date);

      LOOP
         FETCH TIN_Address_cursor INTO TIN_Address_rec;

         EXIT WHEN    TIN_Address_cursor%NOTFOUND
                   OR TIN_Address_cursor%ROWCOUNT > v_row;
      END LOOP;

      CLOSE TIN_Address_cursor;

      RETURN TIN_Address_rec;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         outResult := 'TaxId not Found';
         RAISE NO_DATA_FOUND;
      WHEN OTHERS
      THEN
         outResult := 'select TaxId ' + SUBSTR (SQLERRM, 1, 160);
         RAISE v_error;
   END;

   PROCEDURE EFT_CORR_HIST (
      in_PROVIDER_LOCATION_SEQ_NUM   IN PROVIDER_EFT_INTERFACE.PROVIDER_LOCATION_SEQ_NUM%TYPE,
      in_PROVIDER_SEQ_NUM            IN PROVIDER_EFT_INTERFACE.PROVIDER_SEQ_NUM%TYPE,
      in_EXT_USER_TYPE_SEQ_NUM       IN PROVIDER_EFT_INTERFACE.EXT_USER_TYPE_SEQ_NUM%TYPE,
      in_CORRESPONDENCE_DATE         IN PROVIDER_EFT_INTERFACE.UPDATE_DATE%TYPE,
      in_EFF_DATE                    IN PROVIDER_EFT_INTERFACE.EFF_DATE%TYPE,
      in_EXP_DATE                    IN PROVIDER_EFT_INTERFACE.EXP_DATE%TYPE,
      in_UPDATE_USER                 IN PROVIDER_EFT_INTERFACE.UPDATE_USER%TYPE)

   IS
      v_error           EXCEPTION;
      outresult         VARCHAR2 (400);
      TIN_Address_rec   TIN_Address;

   BEGIN
         TIN_Address_rec :=
            FETCH_TIN_ADDRESS (in_PROVIDER_SEQ_NUM, in_EFF_DATE, in_EXP_DATE);

         BEGIN
            INSERT
              INTO PROVIDER_CORR_HIST PCH (PCH.PROVIDER_CORR_HIST_SEQ_NUM,
                                           PCH.PROVIDER_SEQ_NUM,
                                           PCH.PROVIDER_LOCATION_SEQ_NUM,
                                           PCH.EXT_USER_TYPE_SEQ_NUM,
                                           PCH.CORRESPONDENCE_DATE,
                                           PCH.EFT_AUTH_LTR_FLAG,
                                           PCH.EFT_EFF_DATE,
                                           PCH.EFT_EXP_DATE,
                                           PCH.ADDRESS_LINE1,
                                           PCH.ADDRESS_LINE2,
                                           PCH.ADDRESS_LINE3,
                                           PCH.ADDRESS_LINE4,
                                           PCH.CITY_NAME,
                                           PCH.COUNTRY_CODE,
                                           PCH.FOREIGN_ADDRESS_FLAG,
                                           PCH.STATE_CODE,
                                           PCH.ZIP_CODE,
                                           PCH.ZIP_CODE_EXT,
                                           PCH.CREATE_DATE,
                                           PCH.CREATE_NUM,
                                           PCH.CREATE_USER)
            VALUES (PROVIDER_CORR_HIST_SEQ.NEXTVAL,
                    in_PROVIDER_SEQ_NUM,
                    in_PROVIDER_LOCATION_SEQ_NUM,
                    in_EXT_USER_TYPE_SEQ_NUM,
                    in_CORRESPONDENCE_DATE,
                    'Y',
                    in_EFF_DATE,
                    in_EXP_DATE,
                    TIN_Address_rec.ADDRESS_LINE1,
                    TIN_Address_rec.ADDRESS_LINE2,
                    TIN_Address_rec.ADDRESS_LINE3,
                    TIN_Address_rec.ADDRESS_LINE4,
                    TIN_Address_rec.CITY_NAME,
                    TIN_Address_rec.COUNTRY_CODE,
                    case when TIN_Address_rec.FOREIGN_ADDRESS_FLAG is null then 'N' else 'Y' End ,
                    TIN_Address_rec.STATE_CODE,
                    TIN_Address_rec.ZIP_CODE,
                    TIN_Address_rec.ZIP_CODE_EXT,
                    sysdate,
                    0,
                    in_UPDATE_USER);
         EXCEPTION
            WHEN OTHERS
            THEN
               outResult := 'select TaxId ' + SUBSTR (SQLERRM, 1, 160);
               RAISE v_error;
         END;
   END;

   PROCEDURE EOB_CORR_HIST (
      in_PROVIDER_LOCATION_SEQ_NUM   IN PROVIDER_CORR_HIST.PROVIDER_LOCATION_SEQ_NUM%TYPE,
      in_PROVIDER_SEQ_NUM            IN PROVIDER_CORR_HIST.PROVIDER_SEQ_NUM%TYPE,
      in_EXT_USER_TYPE_SEQ_NUM       IN PROVIDER_CORR_HIST.EXT_USER_TYPE_SEQ_NUM%TYPE,
      in_PAPERLESS_EOB_FLAG          IN PROVIDER_CORR_HIST.PAPERLESS_EOB_FLAG%TYPE,
      in_UPDATE_USER                 IN PROVIDER_CORR_HIST.CREATE_USER%TYPE)
   IS
      v_error           EXCEPTION;
      outresult         VARCHAR2 (400);
      TIN_Address_rec   TIN_Address;
   BEGIN
      TIN_Address_rec :=
         FETCH_TIN_ADDRESS (in_PROVIDER_SEQ_NUM,
                            sysdate,
                            sysdate);

      BEGIN
         INSERT INTO PROVIDER_CORR_HIST PCH (PCH.PROVIDER_CORR_HIST_SEQ_NUM,
                                             PCH.PROVIDER_SEQ_NUM,
                                             PCH.PROVIDER_LOCATION_SEQ_NUM,
                                             PCH.EXT_USER_TYPE_SEQ_NUM,
                                             PCH.CORRESPONDENCE_DATE,
                                             PCH.PAPERLESS_EOB_FLAG,
                                             PCH.EOB_CHANGE_LTR_FLAG,
                                             PCH.ADDRESS_LINE1,
                                             PCH.ADDRESS_LINE2,
                                             PCH.ADDRESS_LINE3,
                                             PCH.ADDRESS_LINE4,
                                             PCH.CITY_NAME,
                                             PCH.COUNTRY_CODE,
                                             PCH.FOREIGN_ADDRESS_FLAG,
                                             PCH.STATE_CODE,
                                             PCH.ZIP_CODE,
                                             PCH.ZIP_CODE_EXT,
                                             PCH.CREATE_DATE,
                                             PCH.CREATE_NUM,
                                             PCH.CREATE_USER)
              VALUES (PROVIDER_CORR_HIST_SEQ.NEXTVAL,
                      in_PROVIDER_SEQ_NUM,
                      in_PROVIDER_LOCATION_SEQ_NUM,
                      in_EXT_USER_TYPE_SEQ_NUM,
                      sysdate,
                      in_PAPERLESS_EOB_FLAG,
                      'Y',
                      TIN_Address_rec.ADDRESS_LINE1,
                      TIN_Address_rec.ADDRESS_LINE2,
                      TIN_Address_rec.ADDRESS_LINE3,
                      TIN_Address_rec.ADDRESS_LINE4,
                      TIN_Address_rec.CITY_NAME,
                      TIN_Address_rec.COUNTRY_CODE,
                      case when TIN_Address_rec.FOREIGN_ADDRESS_FLAG is null then 'N' else 'Y' End ,
                      TIN_Address_rec.STATE_CODE,
                      TIN_Address_rec.ZIP_CODE,
                      TIN_Address_rec.ZIP_CODE_EXT,
                      sysdate,
                      0,
                      in_UPDATE_USER);
      EXCEPTION
         WHEN OTHERS
         THEN
            outResult := 'select TaxId ' + SUBSTR (SQLERRM, 1, 160);
            RAISE v_error;
      END;
   END;
END PK83548_PROVIDER_CORR_HIST;
```

</details>

## Related Documentation
- [Procedures](index.md)
- [Index](../index.md)
- [Overview](../overview.md)
- [Architecture](../architecture.md)
- [Dependencies](../dependencies.md)
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [Database tables](../database-tables.md)
- [SQL catalog](../sql/index.md)
- [API reference](../api/index.md)

## Sources
- `Business\SAIF.Business.Provider.PaymentPreference\clsProviderEFT.vb`