Reg Protection Action View
- Devan Robertson
Name | RegProgectionView |
|---|---|
Type | View |
Description | Includes all records found in the Reg Protection Table with additional fields that are handy when analyzing regulatory protection actions. |
Data Category | Event Field/Column Name | Data Type | Description | Applicability | Sample Value | Notes |
|---|---|---|---|---|---|---|
Key | TenantId | VARCHAR | ALWAYS | qvo-stg |
| |
Key | Tenant | VARCHAR |
| ALWAYS | Default |
|
Key | ClientId | VARCHAR | ALWAYS | Default |
| |
Key | Client | VARCHAR |
| ALWAYS | Default |
|
Claim Details | ClaimType | VARCHAR | CONDITIONAL | Card-Pinless |
| |
Claim Details | ClaimCategory | VARCHAR | CONDITIONAL | Fraud |
| |
Claim Details | ClaimReason | VARCHAR | CONDITIONAL | Stolen |
| |
Claim Details | ClaimStatus | VARCHAR(64) | ALWAYS | RESOLVED-PAID |
| |
Claim Details | ContactDate | DATE | CONDITIONAL | 2023-09-09 |
| |
Key | ClaimId | VARCHAR(32) | ALWAYS | 2306060014C |
| |
Key | DisputeId | VARCHAR(32) |
| ALWAYS | 2306060014D |
|
Dispute Details | DisputeAmount | NUMBER | ALWAYS | 123.45 |
| |
Action Details | Regulation | VARCHAR | The applicable rule or regulation that the protection action was applied to maintain compliance with. | ALWAYS | 'Reg E Provisional Credit', 'Reg E Resolution', 'Reg Z Acknowledgement', 'Reg Z Withholding', 'Reg Z Resolution' |
|
Action Details | Performed On | TIMESTAMP_NTZ(9) | Datetime that the regulatory protection action was applied to the dispute. | ALWAYS | 2023-09-09 21:00:00 |
|
Dispute Details | PCDocRequirementSatisfiedDate | TIMESTAMP_NTZ(9) | CONDITIONAL | 2023-09-09 21:00:00 |
| |
Dispute Details | RegE10Outcome | VARCHAR(64) | CONDITIONAL |
DenyProvisionalCredit |
| |
Dispute Details | PCDenyReason | VARCHAR(64) | CONDITIONAL |
Missing Documentation |
|
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.
SELECT
r.tenantid,
t.name,
r.clientid,
cl.commonname,
c.claimtype,
c.reasontype,
c.reason,
c.status,
c.customercontactdate,
r.claimid,
r.disputeid,
d.disputeamount,
(CASE
WHEN r.regprotectiondecision = 'RegE10' THEN 'Reg E Provisional Credit'
WHEN r.regprotectiondecision = 'RegE90' THEN 'Reg E Resolution'
WHEN r.regprotectiondecision = 'RegE45' THEN 'Reg E Resolution'
WHEN r.regprotectiondecision = 'RegZ30' THEN 'Reg Z Acknowledgement'
WHEN r.regprotectiondecision = 'RegZ90' THEN 'Reg Z Resolution'
WHEN r.regprotectiondecision = 'RegZPC' THEN 'Reg Z Withholding'
else CONCAT('ERROR - ', r.regprotectiondecision)
END) AS Regulation,
TO_TIMESTAMP_TZ(r.regprotectiondecisiondatetime::varchar || ' +0000'),
TO_TIMESTAMP_TZ(r.pcdocrequirementsatisfieddatetime::varchar || ' +0000'),
r.status ,
CAMEL_CASE_TO_STRING(r.rege10processingoutcome),
r.pcdenyreason
FROM regprotection as r
JOIN dispute AS d ON d.tenantid = r.tenantid AND d.disputeid = r.disputeid
JOIN tenant AS t ON r.tenantid = t.tenantid
JOIN claim AS c ON r.tenantid = c.tenantid and r.claimid = c.claimid
JOIN client AS cl ON r.tenantid = cl.tenantid and r.clientid = cl.clientid
ORDER BY r.regprotectiondecisiondatetime DESC
Change Log
Date | Change Summary |
|---|---|
12/11/2024 |
|