Fields
Data Category | Event Field/Column Name | Data Type | Description | Applicability | Sample Value | Notes |
|---|---|---|---|---|---|---|
Key | TenantId | VARCHAR | Unique ID associated with each Quavo client. | ALWAYS | qvo-stg | |
Key | Tenant | VARCHAR | A tenant is a Quavo customer that uses QFD. Each tenant is assigned a unique Tenant ID. | ALWAYS | Default | |
Key | ClientId | VARCHAR | A unique ID representing a single client. | ALWAYS | Default | |
Key | Client | VARCHAR | A client is a Quavo customer that uses QFD through a relationship with a Quavo tenant. Each client is assigned a unique Client ID. | ALWAYS | Default | |
Claim Details | ClaimType | VARCHAR | See Transaction Type. | CONDITIONAL | Card-Pinless | |
Claim Details | ClaimCategory | VARCHAR | General categorization of the claim. For a more precise description, see Claim Reason. | CONDITIONAL | Fraud | |
Claim Details | ClaimReason | VARCHAR | Identifies the specific reason associated with the claim. | CONDITIONAL | Stolen | |
Compliance Details | RegulatoryCoverage | VARCHAR | Identifies the specific reason associated with the claim. | CONDITIONAL | Reg E | |
Claim Details | ClaimChannel | VARCHAR | The Claim Channel defines the intake channel where the claim originated. This channel can include various avenues such as online banking, back office users, contact centers, or external 3rd party contact centers. | ALWAYS | Online Banking | |
Key | ClaimId | VARCHAR | Unique ID associated with a claim. Format: YYMMDD<Ordinal Number>"C" Example: 2405230003C | ALWAYS | 2306060014C | |
Claim Details | CreateDate | DATE | The datetime that a record was created. | CONDITIONAL | 2023-09-09 | |
Claim Details | ClaimAmount | NUMBER( | The sum dispute amount of all posted disputes on a claim. | CONDITIONAL | 123.45 | |
Claim Details | ClaimStatus | VARCHAR | Represents the current stage or step of a particular process or lifecycle, such as a claim or dispute. | ALWAYS | RESOLVED-PAID | |
Claim Details | ResolvedDate | TIMESTAMP_NTZ(9) | Datetime that a claim or dispute is set to a "Resolved" status.. Also referred to as RESOLVEDDATETIME. | CONDITIONAL | 2023-09-09 21:00:00 | |
Processing Details | SystemActions | NUMBER | Count of actions taken by the system on a claim. | ALWAYS | 8 | |
Processing Details | UserActions | NUMBER | Count of actions taken by a user on a claim. | ALWAYS | 2 | |
Processing Details | TotalActions | NUMBER | Count of all actions taken on a claim. | ALWAYS | 10 | |
Processing Details | ProcessingTime | NUMBER | Difference between Create Date and Resolved Date, in days. | ALWAYS | 14 |
SQL
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,
c.claimamount,
c.status,
(SELECT COUNT(*) FROM completedactions AS a WHERE a.tenantid = c.tenantid AND a.claimid = c.claimid AND a.addedbyid = 'System' AND a.flowtype NOT IN ('RequestReopenScreenFlow', 'RequestCaseWithdrawn', 'LaunchLetterHub', 'IntakeScreenFlow') AND a.flowaction != 'ResendPreviousCorrespondence') AS SystemActions,
(SELECT COUNT(*) FROM completedactions AS a WHERE a.tenantid = c.tenantid AND a.claimid = c.claimid AND a.addedbyid != 'System' AND a.flowtype NOT IN ('RequestReopenScreenFlow', 'RequestCaseWithdrawn', 'LaunchLetterHub', 'IntakeScreenFlow') AND a.flowaction != 'ResendPreviousCorrespondence') AS UserActions,
SystemActions + UserActions AS TotalActions,
(CASE
WHEN SystemActions = TotalActions then 'Processed Straight Through'
ELSE 'Manual Processing Required'
END) AS Processing,
TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000'),
TO_TIMESTAMP_TZ(c.resolveddatetime::varchar || ' +0000'),
DATEDIFF(d, c.createdatetime, c.resolveddatetime) AS ProcessingTime
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
WHERE
c.status IN ('Resolved-Paid', 'Resolved-Denied')
ORDER BY
c.resolveddatetime DESC
Change Log
Date | Change Summary |
|---|---|
12/19/2024 | New |