SELECT
                           C.eCPClaim_Key     AS CLAIM_KEY ,
                           C.eCPClaim_insured_id      AS INMATE_ID ,
                           C.eCPClaim_insured_last_name AS LAST_NAME ,
                           C.eCPClaim_insured_middle_initial AS MIDDLE_INITIAL ,
                           C.eCPClaim_insured_first_name    AS FIRST_NAME ,
                           C.eCPClaim_insured_dob     AS INMATE_DOB ,
                           (
                            SELECT eCPClaim_Custom_Value1 FROM tbl_eCPClaim_Custom WHERE eCPClaim_Key = C.eCPClaim_Key
                            AND eCPClaim_Custom_Field_Config_ID = 'text2' LIMIT 1
                           ) AS YREG_DOC_ID ,
                           C.eCPClaim_allowed_amount AS MEDICARE_ALLOWABLE ,
                           C.eCPClaim_payer_billable_amount AS BILLABLE_AMOUNT ,
                           C.eCPClaim_type AS CLAIM_TYPE ,
                           (
                             SELECT eCPClaim_Custom_Value1
                             FROM tbl_eCPClaim_Custom
                             WHERE eCPClaim_Key = C.eCPClaim_Key
                             AND eCPClaim_Custom_Field_Config_ID = 'dropdown2'  LIMIT 1
                           ) AS JLSFE_CLAIM_TYPE ,
                           (
                               SELECT MIN(S.eCPService_Date_From) FROM  tbl_eCPServiceLine S
                               WHERE eCPClaim_Key = C.eCPClaim_Key
                           )AS DATE_OF_SERVICE_FROM ,
                           (
                              SELECT MAX(S.eCPService_Date_To) FROM  tbl_eCPServiceLine S
                              WHERE eCPClaim_Key = C.eCPClaim_Key
                           ) AS DATE_OF_SERVICE_TO ,
                           (
                             SELECT eCPClaim_Custom_Value1 FROM tbl_eCPClaim_Custom WHERE eCPClaim_Key = C.eCPClaim_Key
                             AND eCPClaim_Custom_Field_Config_ID = 'text3' LIMIT 1
                           ) AS CLAIM_COUNTER

where C.eCPClaim_Key  IN (
                            SELECT RES.eCPClaim_Key FROM tbl_eCPRemit TR, tbl_eCPClaimResponseSummary RES
                            WHERE TR.eCPRemit_Key = RES.ecpclaimresponsesummary_response_key
                             AND TR.eCPResponseBatch_Key='1809701372'
                             AND RES.ecpclaimresponsesummary_response_type=205
                             AND  TR.eCPRemit_Claim_Status_Code != '4'
                          )