| Page Properties | ||||||
|---|---|---|---|---|---|---|
|
...
Data Category | Event Field/Column Name | Data Type | Description | Applicability | Sample Value | Notes | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Key | TenantId | VARCHAR(32) |
| ALWAYS | qvo-stg | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Key | Tenant | VARCHAR(64) |
| ALWAYS | Default | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Key | ClientId | VARCHAR(64) |
| ALWAYS | Default | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Key | Client | VARCHAR(64) |
| ALWAYS | DefaultKey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | ClaimIdClaimType | VARCHAR(3264) |
| ALWAYSCONDITIONAL | 2306060014C | Key | ClaimTypeCard-Pinless | |||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | ClaimCategory | VARCHAR(64) |
| CONDITIONAL | Card-PinlessFraud | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Account Claim Details | CustomerIdClaimReason | VARCHAR(64) |
| CONDITIONAL | 123456Stolen | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Account Compliance Details | IsEmployeeClaim | BOOLEAN | RegulatoryCoverage | VARCHAR |
| CONDITIONALfalse | Reg E | |||||||||||||||||||||||||||||||||||||||||||||||||||||
Account Claim Details | AccountNumberClaimChannel | VARCHAR(6432) |
| Account DetailsALWAYS | IsEnrolledInAutopayOnline Banking | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
BOOLEANKey | CONDITIONAL | TRUE | Account Details | NextPaymentDate | DATE | CONDITIONAL | 2023-09-09 | Account Details | StatementCycleDate | DATE | CONDITIONAL | 2023-09-09 | Account Details | AccountOpenDateClaimId | VARCHAR(32) |
| ALWAYS | 2306060014C | ||||||||||||||||||||||||||||||||||||||||||
Claim Details | CreateDate | DATE |
| CONDITIONAL | 2023-09-09 | Available in 24.01.03. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
Account Claim Details | AccountType | VARCHAR(32)CustomerContactDate | DATE | Card Details | CardBIN | VARCHAR(8
| CONDITIONAL | “Deposit”, “Credit Card” | Available in 24.01.03. | Card Details | CardType | VARCHAR(16) | CONDITIONAL | Debit |
| CONDITIONAL | 2023-09-09 | |||||||||||||||||||||||||||||||||||||||||||
Claim Details | ClaimAmount | NUMBER(20,2) |
| CONDITIONAL | 412356, 41235678 | Card Details | CardNumberLast4 | VARCHAR(4) | Last 4 digits of the Card Number. | CONDITIONAL | 5555 | 123.45 | ||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | ReasonTypeClaimState | VARCHAR(64) |
| CONDITIONAL | FraudALWAYS | OPEN | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | ReasonClaimStatus | VARCHAR(64) |
| CONDITIONALALWAYSStolen | RESOLVED-PAID | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Card Details | ChannelOriginBIN | VARCHAR(32) | ALWAYS | Online Banking | Claim Details | ClaimAmount | NUMBER(20,28) |
| CONDITIONAL | 123.45 | Claim 412356, 41235678 | |||||||||||||||||||||||||||||||||||||||||||||||||
Card Details | TotalClaimAmountCardType | NUMBERVARCHAR(20,216) |
| CONDITIONAL | 123.45Debit | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | CustomerContactDate | DATEDecision | VARCHAR(64) |
| CONDITIONAL2023-09-09 | Paid, Denied | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | CustomerDiscoveryDateDiscoveryDate | DATE |
| CONDITIONAL | 2023-09-09 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | IsPotentialAbuse | BOOLEANInvestigationCompleteDateTime | TIMESTAMP_NTZ(9) |
| CONDITIONAL | FALSE | Claim 2023-09-09 21:00:00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||
Compliance Details | StatusInvestigationDate | VARCHARTIMESTAMP_NTZ(649) |
| ALWAYSCONDITIONAL | RESOLVED-PAID | Claim Details | ProvisionalCreditDateTime2023-09-09 21:00:00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||
Compliance Details | ResolutionDeadline | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | PCDecisionResolvedDate | VARCHARTIMESTAMP_NTZ(169) | CONDITIONAL | Grant | Claim Details | Decision | VARCHAR(64) |
| CONDITIONAL | Paid, Denied | Claim Details | HasIdentifiedSuspect2023-09-09 21:00:00 | ||||||||||||||||||||||||||||||||||||||||||||||||
Risk Details | PotentialAbuse | BOOLEAN |
| ALWAYSCONDITIONAL | TRUE, FALSE | Available in 24.01.03. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Risk Details | PCDecisionFromIIDPreSubmission | VARCHAR(16)EmployeeClaim | BOOLEAN |
| ALWAYS | AutoPay, Review | Available in 24.01.03. |
| CONDITIONAL | false | ||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | PCDecisionReasonPCDecision | VARCHAR(25516) |
| CONDITIONAL | Available in 24.01.03.Grant | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | ResolvedDateTimePCDate | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | InvestigationCompleteDateTime | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | Claim Details | ReclassificationCount | Reclassifications | NUMBER(3,0) |
| CONDITIONAL | 1 | ||||||||||||||||||||||||||||||||||||||||||||||||
Claim Details | RequiredDocumentCountRequiredDocuments | NUMBER(3,0) |
| CONDITIONAL | 1 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Claim DetailsMetadata | TimeZoneCreatedBy | VARCHAR(4864) | ALWAYS | America/Los_Angeles | Claim Details | WasPCManuallyWithheld | BOOLEAN |
| ALWAYS | TRUE, FALSEThe username of the user that created the claim. | CONDITIONAL | Available in 24.01.03. | ||||||||||||||||||||||||||||||||||||||||||||||||
Compliance Dispute Details | RegE10DateTimeDisputeCount | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | Compliance Details | PCDocRequirementSatisifedDateTime | TIMESTAMP_NTZ(9NUMBER(3,0) | Compliance Details | RegE10ThresholdDateTime | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 | Compliance Details | RegE45DateTime | TIMESTAMP_NTZ(9
| CONDITIONAL | 2023-09-09 21:00:00 | Available in 24.01.03. |
| CONDITIONAL | 2 | ||||||||||||||||||||||||||||||||||||||
Dispute Details | DisputeAmount | NUMBER(20,2) |
| Compliance Details | RegEThresholdDateTime | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 | Compliance Details | RegZ30DateTime | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | Compliance Details | RegE90DateTime | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 | Compliance Details | RegZ90DateTime | TIMESTAMP_NTZ(9) |
| CONDITIONAL | 2023-09-09 21:00:00 | Compliance Details | RegZ30ThresholdDateTime | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 |
| CONDITIONAL | 123.59 | |||||||||||||||||||||||||||
Dispute Details | MerchantCreditAmount | NUMBER(20,2) | TIMESTAMP_NTZ(9
| CONDITIONAL | 2023-09-09 21:00:00 | Compliance Details | RegZ90ThresholdDateTime | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 | Compliance Details | RegZPCThresholdDateTime |
| CONDITIONAL | 123.59 | |||||||||||||||||||||||||||||||||||||||||||||
Dispute Details | RecoveredAmount | NUMBER(20,2) |
| CONDITIONAL | 2023-09-09 21:00:00 | Available in 24.01.03. | Compliance Details | RegZPCDateTime | TIMESTAMP_NTZ(9123.59 | |||||||||||||||||||||||||||||||||||||||||||||||||||
Dispute Details | LossAmount | NUMBER(20,2) |
| CONDITIONAL | 2023-09-09 21:00:00 | Available in 24.01.03. | Compliance Details | IsFCRA | BOOLEAN | Indicates whether the FCRA Override was applied to the claim. | CONDITIONAL | FALSE | ||||||||||||||||||||||||||||||||||||||||||||||||
Metadata | CreatedbyUserId | VARCHAR(64) | The username of the user that created the claim. | CONDITIONAL | Available in 24.01.03. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
Metadata | SaveDateTime | TIMESTAMP_NTZ(9) | ALWAYS | 2023-09-09 21:00:00 | Metadata | CreateDateTime | TIMESTAMP_NTZ(9) |
| ALWAYS | 2023-09-09 21:00:00CONDITIONAL | 123.59 |
SQL
For greater transparency, and to provide clients with a “jumping off” point for their own queries, the SQL used to generated this view is provided below.
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT
c.tenantid,
t.name,
c.clientid,
cl.legalname,
c.claimtype,
c.reasontype,
c.reason,
(CASE
WHEN c.rege45datetime IS NOT NULL THEN 'Reg E'
WHEN c.rege90datetime IS NOT NULL THEN 'Reg E'
WHEN c.regz90datetime IS NOT NULL THEN 'Reg Z'
WHEN c.isfcra = TRUE THEN 'FCRA'
ELSE 'No Coverage'
END) AS RegulatoryCoverage,
c.channelorigin,
c.claimid,
TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
c.customercontactdate,
c.totalclaimamount,
cs.state,
c.status,
c.cardbin,
c.cardtype,
c.decision,
c.customerdiscoverydate,
TO_TIMESTAMP_TZ(c.investigationcompletedatetime::varchar || ' +0000'),
(CASE
WHEN regethresholddatetime IS NOT NULL THEN regethresholddatetime
ELSE regz90thresholddatetime
END) AS ResolutionDeadline,
TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000'),
c.ispotentialabuse,
c.isemployeeclaim,
c.pcdecision,
TO_TIMESTAMP_TZ(c.provisionalcreditdatetime::varchar || ' +0000'),
c.reclassificationcount,
c.requireddocumentcount,
(CASE
WHEN c.createdbyuserid= 'System' or c.createdbyuserid is NULL THEN 'System' -- QPS-9986
ELSE CONCAT(LEFT(u.firstname, 1), '. ', u.lastname)
END) AS CreatedBy,
(SELECT COUNT(*) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeCount,
(SELECT SUM(d.disputeamount) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeAmount,
IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid = c.tenantid AND mc.claimid = c.claimid), 0) AS MerchantCreditAmount,
IFNULL((SELECT SUM(d.merchanttotal) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND (d.status = 'Resolved-Paid' OR d.status = 'Resolved-Denied')), 0) * -1 AS RecoveredAmount,
IFNULL((SELECT SUM(d.losstotal) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid), 0) * -1 AS LossAmount,
to_date(t.golivedatetime) as TenantProductionDate
FROM claim AS c
JOIN tenant AS t ON c.tenantid = t.tenantid
JOIN client AS cl ON c.tenantid = cl.tenantid AND c.clientid = cl.clientid
LEFT JOIN claim_state AS cs ON cs.status = c.status
LEFT JOIN userprofile as u ON u.tenantid = c.tenantid AND u.userid = c.createdbyuserid
ORDER BY
c.createdatetime DESC |
Change Log
Date | Change Summary |
|---|---|
11/26/2024 | Added SQL |
12/11/2024 | Converted timestamps from NTZ to TZ format for compatibility |