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	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	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	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	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	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	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	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	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 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 with source reference back to this document.

Source Code¶

PL/SQL package specification

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;
 /

PL/SQL package body

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;

Sources¶

Business\SAIF.Business.Provider.PaymentPreference\clsProviderEFT.vb

PK83548_PROVIDER_CORR_HIST¶

Overview¶

Purpose¶

Procedures / Methods¶

FETCH_TIN_ADDRESS¶

Parameters¶

EFT_CORR_HIST¶

Parameters¶

EOB_CORR_HIST¶

Parameters¶

Business Rules¶

Operations¶

Source Code¶

Related Documentation¶

Sources¶