SELECT
    c.tenantid,
    t.name,
    c.clientid,
    cl.legalnamecommonname,
    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.channelorigincardtype,
    c.claimidchannelorigin AS ClaimChannel,
    TO_TIMESTAMP_TZ(c.createdatetime::varchar || ' +0000')claimid AS ClaimID,
    c.customercontactdatetotalclaimamount AS TotalClaimAmount,
    c.totalclaimamountcustomerdiscoverydate AS DiscoveryDate,
    cs.statec.customercontactdate AS ContactDate,
    TO_TIMESTAMP_TZ(c.status,
    c.cardbincreatedatetime::varchar || ' +0000') AS CreateDate,
    c.cardtype,
    c.decision,
    c.customerdiscoverydate,
    TO_TIMESTAMP_TZ(c.investigationcompletedatetime TO_TIMESTAMP_TZ((SELECT MAX(executedon) FROM accounting AS a WHERE a.tenantid = c.tenantid AND a.claimid = c.claimid AND a.reason IN ('Provisional Credit'))::varchar || ' +0000') AS PCDate,
    (CASE
DATEDIFF(d, c.createdatetime, PCDate) AS PCDays,
    TO_TIMESTAMP_TZ((SELECT MIN(executedondatetime) FROM disputestatusmaintenance WHENAS regethresholddatetimea ISWHERE NOTa.tenantid NULL= THENc.tenantid regethresholddatetimeAND a.claimid = c.claimid)::varchar || ' +0000') AS DisputeStatusDate,
ELSE regz90thresholddatetime   DATEDIFF(d, c.createdatetime, ENDDisputeStatusDate) AS ResolutionDeadlineDisputeStatusDays,
    TO_TIMESTAMP_TZ(c.resolveddatetimeinvestigationcompletedatetime::varchar || ' +0000') AS InvestigationDate,
    DATEDIFF(d, c.ispotentialabusecustomercontactdate,     c.isemployeeclaim,investigationcompletedatetime)     c.pcdecisionAS ResolutionDays,
    TO_TIMESTAMP_TZ(c.provisionalcreditdatetimeresolveddatetime::varchar || ' +0000') AS ResolvedDate,
    c.reclassificationcountispotentialabuse,
    c.requireddocumentcountisemployeeclaim,
    (CASE
        WHEN c.createdbyuseridstatus = 'System' or c.createdbyuserid is NULLResolved-Denied' THEN 'System'  -- QPS-9986
        ELSE CONCAT(LEFT(u.firstname, 1), '. ', u.lastname)
    Denied' ELSE 'Paid' END) AS CreatedByDecision,
    (SELECT COUNT(*) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated') AS DisputeCountc.pcdecision AS PCDecision,
    (CASE WHEN c.waspcmanuallywithheld = TRUE THEN TRUE END) AS PCWithheld,
    (SELECT SUM(d.disputeamount) FROM dispute AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid AND d.status != 'Resolved-PostedDisputeCreated'CASE WHEN c.pcdecision = 'AutoPay' THEN TRUE END) AS DisputeAmountAutoPay,
    IFNULL((SELECT SUM(mc.appliedamount) FROM merchantcredit AS mc WHERE mc.tenantid =CASE WHEN c.tenantidreclassificationcount AND> mc.claimid0 = c.claimid), 0THEN TRUE END) AS MerchantCreditAmountReclassified,
    IFNULL(CASE WHEN EXISTS (SELECT SUM(d.merchanttotal)1 FROM disputereopenrequest 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) THEN TRUE END) AS ReopenRequested,
    (CASE WHEN c.requireddocumentcount > 0 THEN TRUE END) AS RequiredDocuments,
    IFNULL(CASE WHEN EXISTS (SELECT SUM(d.losstotal)1 FROM disputecorrespondence AS d WHERE d.tenantid = c.tenantid AND d.claimid = c.claimid), 0)AND *purpose -1= AS LossAmount,
    to_date(t.golivedatetime) as TenantProductionDate'Additional Information') THEN TRUE END) AS AdditionalInfoRequested,
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 =WHERE c.status LEFT JOIN userprofile as u ON u.tenantid = c.tenantid AND u.userid = c.createdbyuserid
IN ('Resolved-Paid', 'Resolved-Denied')
ORDER BY
    c.createdatetime DESC;