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

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.

Source Code¶

PL/SQL package specification

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;

PL/SQL package/procedure body

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;

Sources¶

Business\SAIF.Business\ClaimServices.vb
Business\SAIF.Business\EFTServices.vb

pk82350_eft¶

Overview¶

Purpose¶

Procedures / Methods¶

isauthorizedforeft¶

Parameters¶

isbeneficiaryauthforeft¶

Parameters¶

isproviderauthforeft¶

Parameters¶

ActiveAuthCountByPerson¶

Parameters¶

CardEnrollmentCountByPerson¶

Parameters¶

Business Rules¶

Operations¶

Source Code¶

Related Documentation¶

Sources¶