Skip to content

Reporting - Overview

The reporting team is creating a reporting database to combine and query hub/blue data.

For the initial phase, we will provide procs as an interface for their ETL process.

PayCenter Report ICD

Financial Institutions

Get Organizations

Database Boyar_Zelle
Object [zelle_reporting].[spGetOrganizations]
Param @modifiedStartAt DATETIME2 = NULL utc
Param @modifiedEndAt DATETIME2 = NULL utc
# name type length nullable desc
1 ExternalOrganizationId varchar 30 No EWS Org Id
2 PayCenterMemberFIId bigint 8 No Common FI identifier. By convention equal to CompanyId
3 Name varchar 50 No Org name
4 OrganizationNetworkType varchar 15 No ENUM[InNetwork, OutOfNetwork, Visa, MasterCard, NetworkOperator]
5 DefaultRoutingNumber varchar 15 Yes
6 TCHRoutingNumber varchar 15 Yes
7 MembershipStatus varchar 8 Yes ENUM[Active, Inactive, Restricted]
8 OrganizationCreatedAt datetime2 6 No Created UTC
9 OrganizationModifiedAt datetime2 6 Yes Modified UTC
10 MembershipCreatedAt datetime2 6 No PayCenter membership created UTC
11 MembershipModifiedAt datetime2 6 Yes PayCenter membership modified UTC
12 DefaultRoutingNumberCreatedAt datetime2 6 No Created UTC
13 DefaultRoutingNumberModifiedAt datetime2 6 Yes Modified UTC
14 CustomerIdType varchar 11 No Enum[LegalEntityId, CoreCustomerId, LegalEntityIdCoreCustomerId]

Get Core Profiles

Get core profile configuration by RTN. Multiple profiles for an RTN are possible.

Database Boyar_Mono
Object [enterprise_integration_reporting].[spGetCoreProfiles]
Param @modifiedStartAt DATETIME2 = NULL utc
Param @modifiedEndAt DATETIME2 = NULL utc
id name type length nullable desc
1 PayCenterMemberFIId bigint 8 No Common FI identifier. By convention equal to CompanyId
2 RoutingNumber varchar 15 No
3 IsDefaultRoutingNumber int 4 No
4 CoreName varchar 200 No The core name as provided by /cores endpoint
5 IsEnabled bit 1 No
6 CoreProfileCreatedAt datetime2 6 No
7 CoreProfileModifiedAt datetime2 6 Yes

Tokens

Get Tokens

Database Boyar_Zelle
Object [zelle_reporting].[spGetTokens]
Param @modifiedStartAt DATETIME2 = NULL utc
Param @modifiedEndAt DATETIME2 = NULL utc
Param @zelleOrganizationId VARCHAR(30) = NULL
# name type length nullable desc
1 ExternalOrganizationId varchar 30 No EWS Org Id
2 PayCenterMemberFIId bigint 8 No Common FI identifier. By convention equal to CompanyId
3 TokenIdentifier varchar 255 No Email or phone
4 TokenType varchar 6 No ENUM[Email, Mobile, ZelleTag]
5 ExternalCustomerId varchar 20 No Zelle customer id
6 TokenId bigint 8 No Token PK
7 FirstName varchar 100 No First name
8 LastName varchar 100 Yes Last name
9 ExternalPaymentProfileId varchar 20 no EWS PaymentProfile Id
10 PaymentProfileId bigint 8 no PaymentProfile PK
11 AccountNumber varchar 20 Yes Financial account
12 ExternalIdentifier varchar 40 No User identifier from DI
13 ExternalIdentifierType varchar 12 No ENUM[None, TaxId, DriverLic, ResidenceCard, MltryId, PassPort, Oth, NetTellerId]
14 CoreCustomerId varchar 64 Yes Member Id or CIF number for the given User
15 TokenCreatedAt datetime2 6 No Enrolled UTC
16 TokenModifiedAt datetime2 6 Yes Last modified UTC
17 PaymentProfileCreatedAt datetime2 6 No Created UTC
18 PaymentProfileModifiedAt datetime2 6 Yes Last modified UTC
19 CustomerCreatedAt datetime2 6 No Enrolled UTC
20 CustomerModifiedAt datetime2 6 Yes Last modified UTC
21 TokenStatus varchar 11 No ENUM[Active, Inactive, Restricted]
22 DeactivatedAt datetime2 6 Yes UTC of (Inactive, Restricted)

Payments

Get Payments

Gets payments from the PaymentRepo service.

Database Boyar_PaymentRepo
Object [zelle_reporting].[spGetPayments]
Param @zellePaymentId VARCHAR = NULL
Param @modifiedStartAt DATETIME2 = NULL utc
Param @modifiedEndAt DATETIME2 = NULL utc
Param @zelleOrgID VARCHAR(5) = NULL

Note

The *Start/*EndAt fields are the inclusive range in UTC range. See desc regarding the source field.

name type length nullable desc
PaymentId bigint 8 No Source system id
ZellePaymentId varchar 12 No Org Id + unique string
Amount decimal 9 No
InitiatedAt datetime2 6 No UTC Date and time the actor initiated the payment
ModifiedAt datetime2 6 No The latest of Created/Modified of any entity tables in the query in UTC.
Memo nvarchar 400 Yes
ZelleProductType varchar 30 Yes ENUM[p2p, b2c] (retail, wholesale)
SenderZelleCustomerId varchar 128 yes
SenderFirstName varchar 128 Yes
SenderLastName varchar 128 Yes
SenderBusinessName varchar 64 Yes
SenderExternalEntityIdentifier varchar 500 Yes
SenderExternalEntityIdentifierType varchar 30 Yes ENUM[None, TaxId, DriverLic, ResidenceCard, MltryId, PassPort, Oth, NetTellerId]
SenderTokenIdentifier varchar 500 Yes
SenderTokenIdentifierType varchar 30 Yes ENUM[Email, Mobile, ZelleTag]
SenderOrgId varchar 5 No
SenderPayCenterMemberFIId bigint 8 No Common FI identifier. By convention equal to CompanyId
SenderAccountNumber varchar 50 Yes
SenderRoutingNumber varchar 20 Yes
SenderAccountType varchar 30 Yes ENUM[Checking, Savings]
RecipientZelleCustomerId varchar 128 yes
RecipientFirstName varchar 128 Yes
RecipientLastName varchar 128 Yes
RecipientBusinessName varchar 64 Yes
RecipientExternalEntityIdentifier varchar 500 Yes
RecipientExternalEntityIdentifierType varchar 30 Yes ENUM[None, TaxId, DriverLic, ResidenceCard, MltryId, PassPort, Oth, NetTellerId]
RecipientTokenIdentifier varchar 500 Yes
RecipientTokenIdentifierType varchar 30 Yes ENUM[Email, Mobile, ZelleTag]
IsRecipientKnown bit 1 Yes
RecipientOrgId varchar 5 Yes
RecipientPayCenterMemberFIId bigint 8 No Common FI identifier. By convention equal to CompanyId
RecipientNetworkType varchar 30 Yes ENUM[InNetwork, OutOfNetwork, Visa, MasterCard, NetworkOperator]
RecipientAccountNumber varchar 50 Yes
RecipientRoutingNumber varchar 20 Yes
RecipientAccountType varchar 30 Yes ENUM[Checking, Savings]
PaymentMode varchar 30 No ENUM[Expedited, Standard]
AcceptanceMode varchar 30 Yes ENUM[Auto, Manual]
IsImmediateDebit bit 1 No Blue only supports immediate debit.

Get Outbound Payments

Provides the status of the Add Payment call to EWS

Database Boyar_EVENTREPOSITORY
Object [reporting].[spGetOutboundPayments]
Param @transactionStartAt DATETIME2 = NULL utc
Param @transactionEndAt DATETIME2 = NULL utc
Param @zellePaymentId VARCHAR = NULL
Param @AddPaymentAPIResponseSuccess BIT = 0
name type length nullable desc
PaymentOutboundSendId bigint 8 No Source System Id
TransactionIdFk uniqueidentifier 16 No Correlation
jXLogTrackingId uniqueidentifier 16 No Correlation
BusCorrelId varchar 500 No Correlation
PaymentId varchar 50 No
CreatedOn datetime2 7 No utc
AddPaymentAPIResponseStatus varchar 30 Yes Value of 'success' when successful

Get PmtHubCrXferAdd Responses

Given a date range and optional payment id, provides the jx error codes returned to the DI.

Database Boyar_EVENTREPOSITORY
Object [reporting].[spGetPmtHubCrXferAddResponses]
Param @transactionStartAt DATETIME2 = NULL utc
Param @transactionEndAt DATETIME2 = NULL utc
Param @zellePaymentId VARCHAR = NULL
Param @tranStatusId TINYINT = 1 1 = ERROR
2 = RECEIVED
3 = SUCCESS
4 = RESUBMITTED
5 = INPROGRESS
name type length nullable desc
PaymentOutboundSendId bigint 8 No Source System Id
TransactionIdFk uniqueidentifier 16 No Correlation
jXLogTrackingId uniqueidentifier 16 No
BusCorrelId varchar 500 No
ZellePaymentId varchar 50 No
CreatedOn datetime2 7 No utc
TransDate datetime2 7 No utc
ErrCode varchar 30 Yes FaultMsgRec
ErrCat varchar 30 Yes FaultMsgRec
ErrDesc varchar 500 Yes FaultMsgRec

Payment Activity

Get Payment Activities

Database Boyar_PaymentRepo
Object [zelle_reporting].[spGetPaymentActivities]
Param @zellePaymentId VARCHAR = NULL
Param @modifiedStartAt DATETIME2 = NULL
Param @modifiedEndAt DATETIME2 = NULL
Param @statusEffectiveStartAt DATETIME2 = NULL
Param @statusEffectiveEndAt DATETIME2 = NULL
Param @zelleOrgID VARCHAR(5) = NULL

Note

The *Start/*EndAt fields are the inclusive range in UTC range. See desc regarding the source field.

name type length nullable desc
PaymentActivityId bigint 8 No Activity source system id
PaymentId bigint 8 No Payment source system id
ZellePaymentId varchar 12 No Org Id + unique string
Direction varchar 30 No ENUM[Inbound, Outbound]
InitiatedAt datetime2 6 No UTC Date and time the actor initiated the payment
ModifiedAt datetime2 6 No The latest of Created/Modified of any entity tables in the query in UTC.
StatusEffectiveAt datetime2 6 No
Status varchar 30 No ENUM[Created, Pending, PendingProfile, Sent, Failed, Expired, PendingAcceptance, Accepted, PendingProcessing, Delivered, CompletedOnOnUsNetwork, Denied, Settled, AcceptWithoutPosting]
FailureReasonCode varchar 30 Yes ENUM[ProfileInactive, UnableToAccept, NameMatchFailed, Other, Declined, ExpiredAccepting, CancelledBySender, PaymentNotAllowed, AccountUnavailable, AchReturn, CannotProcessExpeditedPayment, NotificationToUnknownRecipient, NoResponseFromReceiving, FailedByNetworkOperator, FailedByDebitNetwork, NotificationNotDelivered, PaymentMadeViaAnotherNetwork]
DeniedReasonCode varchar 30 Yes ENUM[IssueWithCreditAccount, FraudRiskAction, FailCreditException, DeniedByDebitNetwork, CancelledBySender, PaymentNotAllowed, DeniedByPaymentNetwork]
IpAddress varbinary 16 Yes Ipv6 Mapped to varbinary via IPAddress.GetAddressBytes()
MobileDeviceIdentifier varchar 16 Yes The mobile device identifier often referred as the International Mobile Equipment Identity (IMEI).
SenderZelleOrgId varchar 5 Yes The zelle organization id which refers to the sender.
RecipientZelleOrgId varchar 5 Yes The zelle organization id which refers to the recipient.

Note

Ipv4 addresses are converted to Ipv6.

Payment Requests

Get Payment Requests

Gets payment requests from the PaymentRepo service.

Database Boyar_PaymentRepo
Object [zelle_reporting].[spGetPaymentRequests]
Param @zellePaymentRequestId VARCHAR = NULL
Param @modifiedStartAt DATETIME2 = NULL utc
Param @modifiedEndAt DATETIME2 = NULL utc
Param @zelleOrgID VARCHAR(5) = NULL

Note

The *Start/*EndAt fields are the inclusive range in UTC range. See desc regarding the source field.

name type length nullable desc
PaymentRequestId bigint 8 No Source system id
ZellePaymentRequestId varchar 13 No Org Id + unique string
Amount decimal 9 No
InitiatedAt datetime2 6 No UTC Date and time the actor initiated the request
ModifiedAt datetime2 6 No The latest of Created/Modified of any entity tables in the query in UTC.
Memo nvarchar 400 Yes
PaymentBatchId varchar 10 Yes Id of the Batch containing the corresponding Payment Request.
RequestorZelleCustomerId varchar 128 yes
RequestorCoreCustomerId varchar 64 yes
RequestorFirstName varchar 128 Yes
RequestorLastName varchar 128 Yes
RequestorBusinessName varchar 64 Yes
RequestorExternalEntityIdentifier varchar 500 Yes
RequestorExternalEntityIdentifierType varchar 30 Yes ENUM[None, TaxId, DriverLic, ResidenceCard, MltryId, PassPort, Oth, NetTellerId]
RequestorTokenIdentifier varchar 500 Yes
RequestorTokenIdentifierType varchar 30 Yes ENUM[Email, Mobile, ZelleTag]
RequestorOrgId varchar 5 No
RequestorPayCenterMemberFIId bigint 8 No Common FI identifier. By convention equal to CompanyId
ResponderZelleCustomerId varchar 128 yes
ResponderCoreCustomerId varchar 64 yes
ResponderFirstName varchar 128 Yes
ResponderLastName varchar 128 Yes
ResponderBusinessName varchar 64 Yes
ResponderExternalEntityIdentifier varchar 500 Yes
ResponderExternalEntityIdentifierType varchar 30 Yes ENUM[None, TaxId, DriverLic, ResidenceCard, MltryId, PassPort, Oth, NetTellerId]
ResponderTokenIdentifier varchar 500 No
ResponderTokenIdentifierType varchar 30 No ENUM[Email, Mobile, ZelleTag]
ResponderOrgId varchar 5 Yes
ResponderPayCenterMemberFIId bigint 8 No Common FI identifier. By convention equal to CompanyId

Payment Request Activity

Get Payment Request Activities

Database Boyar_PaymentRepo
Object [zelle_reporting].[spGetPaymentRequestActivities]
Param @zellePaymentRequestId VARCHAR = NULL
Param @zellePaymentId VARCHAR = NULL
Param @modifiedStartAt DATETIME2 = NULL
Param @modifiedEndAt DATETIME2 = NULL
Param @statusEffectiveStartAt DATETIME2 = NULL
Param @statusEffectiveEndAt DATETIME2 = NULL
Param @zelleOrgID VARCHAR(5) = NULL

Note

The *Start/*EndAt fields are the inclusive range in UTC range. See desc regarding the source field.

name type length nullable desc
PaymentRequestActivityId bigint 8 No Activity source system id
PaymentRequestId bigint 8 No Payment source system id
ZellePaymentRequestId varchar 13 No Org Id + unique string
PaymentId bigint 8 Yes Payment source system id
ZellePaymentId varchar 12 Yes Org Id + unique string
Direction varchar 30 No ENUM[Inbound, Outbound]
InitiatedAt datetime2 6 No UTC Date and time the actor initiated the request
ModifiedAt datetime2 6 No The latest of Created/Modified of any entity tables in the query in UTC.
StatusEffectiveAt datetime2 6 No
Status varchar 30 No ENUM[Pending, Active, Inactive]
DeactivationReason varchar 30 Yes ENUM[NotAllowed, PaymentMadeViaAnotherNetwork, Other, Completed, Paid, Cancelled, Declined, Expired]
DeactivationMemo varchar 400 Yes Memo provided by the organization
IpAddress varbinary 16 Yes Ipv6 Mapped to varbinary via IPAddress.GetAddressBytes()
MobileDeviceIdentifier varchar 16 Yes The mobile device identifier often referred as the International Mobile Equipment Identity (IMEI).
RequestorZelleOrgId varchar 5 Yes The zelle organization id which refers to the requestor.
ResponderZelleOrgId varchar 5 Yes The zelle organization id which refers to the responder.

Note

Ipv4 addresses are converted to Ipv6.

MNO Validation

Get MNO Identity Match Validation

Provides the logs for ENS NotMobPhoneValidate calls to validate identity.

Database Boyar_EVENTREPOSITORY
Object [reporting].[spGetMNOIdentityMatchValidationLog]
Param @createdStartAt DATETIME2 required utc
Param @createdEndAt DATETIME2 required utc
Param @Token nvarchar(255) = NULL
Param @OrgId nvarchar(10) = NULL
name type length nullable desc
MNOIdentityMatchValidationLogId bigint 8 No PK
TransactionIdFk uniqueidentifier 16 No Correlation
jXLogTrackingId uniqueidentifier 16 No Correlation
BusCorrelId nvarchar 500 No Correlation
OrgId nvarchar 5 No
Token nvarchar 255 No
CreatedAt datetime2 7 No utc
CallType nvarchar 30 No
IsSuccess bit 1 No
FirstName nvarchar 100 Yes First name
LastName nvarchar 100 Yes Last name
FirstNameMatchType nvarchar 30 Yes ENUM[Exact, HighPart, Part, NoMatch, NotAvail]
LastNameMatchType nvarchar 30 Yes ENUM[Exact, HighPart, Part, NoMatch, NotAvail]
EntityConfidenceScore tinyint 1 Yes Represents a percent.
A higher confidence score means a better likelihood that the transcription is accurate.

Get MNO Line Type Validation

Provides the logs for ENS NotMobPhoneValidate calls to validate token line type.

Database Boyar_EVENTREPOSITORY
Object [reporting].[spGetMNOLineTypeValidationLog]
Param @createdStartAt DATETIME2 required utc
Param @createdEndAt DATETIME2 required utc
Param @Token nvarchar(255) = NULL
Param @OrgId nvarchar(10) = NULL
name type length nullable desc
MNOLineTypeValidationLogId bigint 8 No PK
TransactionIdFk uniqueidentifier 16 No Correlation
jXLogTrackingId uniqueidentifier 16 No Correlation
BusCorrelId nvarchar 500 No Correlation
OrgId nvarchar 5 No
Token nvarchar 255 No
CreatedAt datetime2 7 No utc
CallType nvarchar 30 No
IsSuccess bit 1 No
PhoneTypeCarrierName nvarchar 100 Yes
PhoneTypeNetworkCode nvarchar 3 Yes MNC1
PhoneLineType nvarchar 30 Yes ENUM[Land, Mob, FixVoip, NonFixVoip, TollFree, Prem, ShareCost, UAN, VoiceMail, Pager, Unknown]

Limits

Get User-Specific Limits at the FI Level

Provide user-specific limit configurations at the financial institution level.

Database Boyar_PaymentRepo
Object [zelle_reporting].[spGetUserLimitsAtFILevel]
Param @payCenterMemberFIId BIGINT = NULL
Param @networkProduct Varchar(4) = NULL
Param @network Varchar(1) = NULL
name type length nullable desc
GraduatedRule varchar 50 No Graduated Rule Reference
LimitId bigint 8 No PK of [Boyar_PaymentRepo].[limits].[Limit] table
Network varchar 256 Yes ENUM[InNetwork, OutOfNetwork, Visa, MasterCard, NetworkOperator]
PayCenterMemberFIId varchar 256 Yes Common FI identifier. By convention equal to CompanyId
Direction varchar 8 No ENUM[Inbound, Outbound]
CustomerId varchar 256 Yes ExternalCustomerId of the Zelle customer
PerTransactionRange decimal 9 Yes Amount limit configuration for a single transaction
DailyRange decimal 9 Yes Amount limit configuration for daily transactions
WeeklyRange decimal 9 Yes Amount limit configuration for weekly transactions
MonthlyRange decimal 9 Yes Amount limit configuration for monthly transactions
DailyVelocity int 4 Yes Velocity limit configuration for daily transactions
WeeklyVelocity int 4 Yes Velocity limit configuration for weekly transactions
MonthlyVelocity int 4 Yes Velocity limit configuration for monthly transactions

Risk Score

Get Zelle Risk Score

Database Boyar_Zelle
Object [zelle_reporting].[spGetZelleRiskScore]
Param @modifiedStartAt DATETIME2 = NULL utc
Param @modifiedEndAt DATETIME2 = NULL utc
# name type length nullable desc
1 ZelleRiskScoreId bigint 8 No Source system id
2 Status bit 1 No
3 StatusCode varchar 64 Yes
4 Identifier varchar 255 Yes
5 Score varchar 255 Yes
6 IdentifierStatusCode varchar 64 Yes
7 ReasonCode nvarchar 200 Yes
8 Attribute nvarchar 200 Yes
9 CreatedById varchar 30 No
10 CreatedAt datetime2 2 No Created UTC
11 ModifiedById varchar 30 Yes
12 ModifiedAt datetime2 2 Yes Modified UTC
13 OriginatorCustomerId bigint 8 Yes PK of [Boyar].[Zelle].[dbo].[Customer]
14 TargetTokenId bigint 8 Yes PK of [Boyar].[Zelle].[dbo].[Token]
15 T.Identifier varchar 255 No Email or Mobile
16 TargetPaymentProfileId bigint 8 Yes PK of [Boyar].[Zelle].[dbo].[PaymentProfile]
16 ExternalPaymentId varchar 13 Yes Org Id + unique string
17 ExternalPaymentRequestId varchar 13 Yes Org Id + unique string

  1. MNCs are standard 2-3 digit carrier identifiers used by cellular networks to indicate the network to which a mobile subscriber has signed up with. See docs