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	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	JOIN	BANK_ACCOUNT_SEQ_NUM, BANK_ACCOUNT_STATUS_CODE, PRENOTE_REQUIRED_FLAG, PRENOTE_SUCCESS_DATE
DEBIT_CARD_ENROLLMENT_REQUEST	SELECT	CARDHOLDER_ID_NUM, CARD_TERM_DATE

Sample 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

Business rules

• 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.
• 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.
• 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.

Sources

• Business\SAIF.Business\EFTServices.vb