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,
    toTO_TIMESTAMP_dateTZ(c.investigationcompletedatetime::varchar || ' +0000'),
    (CASE
        WHEN regethresholddatetime IS NOT NULL THEN regethresholddatetime
        ELSE regz90thresholddatetime
    END) AS ResolutionDeadline,
    toTO_TIMESTAMP_dateTZ(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