CFPB Not Accepting Leading Zeros or Hyphens in HMDA Respondent ID

It has come to our attention that the CFPB is still making changes to the 2017 HMDA submission portal. We have just heard (1/9/18) that they changed the formatting of the Respondent ID field. It is now required that all institutions remove leading zeros and the hyphen for those reporters that use their Tax ID as Respondent ID. The hyphen is still required for the Tax ID field. This represents a huge hurdle for the industry because all HMDA submission tools are designed to make that field 10 digits.

This was not mentioned in the 2017 FIG and did not flag as an issue when institutions uploaded test files to the HMDA Platform. Now a recent change causes a syntactical edit, S025, when leading zeros or hyphens are present in the Respondent ID field. Your submission file will not be accepted if the S025 edit flags.

RATA offers two simple methods to solve this problem:

1. (Recommended) Apply a hotfix in the form of a SQL script to your Comply 17 system. Once this fix is applied you will keep your IDs exactly the same, zero filled and with the hyphen if applicable. When you go through the submission wizard Comply will automatically strip the unwanted characters as it creates the submission file.

Here are the steps to run the hotfix for the Respondent ID:

1. Have an administrative user log in to Comply (or have someone log in as the csa user)
2. Go to the System ribbon
3. Click the Query Runner button
4. Copy everything below that is under the SCRIPT line and paste it into the Query Runner window
5. Click the Execute button
6. Restart Comply
7. When you go through the submission wizard the Respondent ID field will be formatted correctly in the submission file

2. Create the submission file without applying the hot fix, then follow these steps to manually correct the submission file prior to submission:

1. Once you've gone through the submission wizard to create your LAR in .txt format, open that submission file in Notepad
2. Click 'Edit > Replace'
3. In the 'Find what' box type in your full 10-digit Respondent ID with the leading zeros and/or hyphens
4. In the 'Replace with' box type in the Respondent ID with no leading zeros and no hyphens
5. Click the 'Replace All' button
6. Close the Replace window
7. Click 'File > Save'
8. Now you will be able to upload the submission .txt file to the HMDA Platform without receiving the S025 edit

Nothing is changing for 2017 CRA submissions. If you have any questions please feel free to contact us.

SCRIPT

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'ssp_Submission_HMDA_V2_Transmittal') AND type in (N'P', N'PC'))
DROP PROCEDURE ssp_Submission_HMDA_V2_Transmittal
GO

CREATE PROCEDURE ssp_Submission_HMDA_V2_Transmittal
@fkReportEntity AS int
AS
BEGIN

DECLARE @intAppCount AS int
SELECT @intAppCount = COUNT(*)
FROM #FilteredApps

SELECT
'1' AS RecordID,
CASE
WHEN ISNUMERIC(REPLACE(LTRIM(RTRIM(chrRespondentID)), '-', '')) = 1 THEN CAST(CAST(REPLACE(LTRIM(RTRIM(chrRespondentID)), '-', '') AS bigint) AS varchar(10))
ELSE LTRIM(RTRIM(chrRespondentID))
END AS RespondentID,
fkAgency AS AgencyCode,
GETDATE() AS Timestamp,
fkReportYear AS ActivityYear,
LTRIM(RTRIM(chrRespondentTaxID)) AS TaxID,
@intAppCount AS TotalLineEntries,
LTRIM(RTRIM(chrRespondName)) AS RespondentName,
LTRIM(RTRIM(chrRespondAddress)) AS RespondentAddress,
LTRIM(RTRIM(chrRespondCity)) AS RespondentCity,
UPPER(LTRIM(RTRIM(chrRespondState))) AS RespondentState,
LTRIM(RTRIM(chrRespondZip)) AS RespondentZIPCode,
LTRIM(RTRIM(chrParentName)) AS ParentName,
LTRIM(RTRIM(chrParentAddress)) AS ParentAddress,
LTRIM(RTRIM(chrParentCity)) AS ParentCity,
UPPER(LTRIM(RTRIM(chrParentState))) AS ParentState,
LTRIM(RTRIM(chrParentZip)) AS ParentZIPCode,
LTRIM(RTRIM(chrContactName)) AS ContactPersonsName,
LTRIM(RTRIM(chrContactPhone)) AS ContactPersonsPhoneNumber,
LTRIM(RTRIM(chrContactFax)) AS ContactPersonsFacsimileNumber,
LTRIM(RTRIM(chrContactEMail)) AS ContactPersonsEmailAddress
FROM tbl_Report_Entity WITH (NOLOCK)
WHERE pkReportEntity = @fkReportEntity

END
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'ssp_Submission_HMDA_V2_LAR') AND type in (N'P', N'PC'))
DROP PROCEDURE ssp_Submission_HMDA_V2_LAR
GO

CREATE PROCEDURE ssp_Submission_HMDA_V2_LAR
@fkReportEntity AS int
AS
BEGIN

DECLARE @chrRespondentID varchar(10)
DECLARE @fkAgency int
SELECT @chrRespondentID = CASE
WHEN ISNUMERIC(REPLACE(LTRIM(RTRIM(chrRespondentID)), '-', '')) = 1 THEN CAST(CAST(REPLACE(LTRIM(RTRIM(chrRespondentID)), '-', '') AS bigint) AS varchar(10))
ELSE LTRIM(RTRIM(chrRespondentID))
END,
@fkAgency = fkAgency
FROM tbl_Report_Entity WITH (NOLOCK)
WHERE pkReportEntity = @fkReportEntity

SELECT
'2' AS RecordID,
@chrRespondentID AS RespondentID,
@fkAgency AS AgencyCode,
LTRIM(RTRIM(chrAppNo)) AS LoanApplicationNumber,
CASE WHEN dbo.udfVariantIsNA(varApplicationDate) = 1 THEN 'NA'
ELSE CONVERT(varchar, dbo.udfVariantGetDate(varApplicationDate), 112)
END AS DateApplicationReceived,
LTRIM(RTRIM(chrLoanType)) AS LoanType,
fkPropertyType AS PropertyType,
fkLoanPurpose AS LoanPurpose,
fkOccupancy AS OwnerOccupancy,
CONVERT(int, ROUND((ISNULL(monLoanAmount, 0) / 1000), 0)) AS LoanAmount,
fkPreapproved AS Preapprovals,
fkActionType AS TypeOfActionTaken,
CONVERT(varchar, dteActionDate, 112) AS DateOfAction,
LTRIM(RTRIM(chrMSA)) AS MSA,
LTRIM(RTRIM(chrStateCode)) AS StateCode,
LTRIM(RTRIM(chrCountyCode)) AS CountyCode,
LTRIM(RTRIM(chrCensusTract)) AS CensusTract,
fkAppEthn AS ApplicantEthnicity,
fkCoAppEthn AS CoApplicantEthnicity,
fkAppRace1 AS ApplicantRace1,
fkAppRace2 AS ApplicantRace2,
fkAppRace3 AS ApplicantRace3,
fkAppRace4 AS ApplicantRace4,
fkAppRace5 AS ApplicantRace5,
fkCoAppRace1 AS CoApplicantRace1,
fkCoAppRace2 AS CoApplicantRace2,
fkCoAppRace3 AS CoApplicantRace3,
fkCoAppRace4 AS CoApplicantRace4,
fkCoAppRace5 AS CoApplicantRace5,
fkAppGender AS ApplicantSex,
fkCoAppGender AS CoApplicantSex,
CASE WHEN dbo.udfVariantIsNA(varTotalIncome) = 1 THEN 'NA'
WHEN varTotalIncome IS NULL OR dbo.udfVariantIsNumeric(varTotalIncome) = 0 THEN ''
ELSE CONVERT(varchar, CONVERT(int, ROUND((ISNULL(dbo.udfVariantGetNumeric(varTotalIncome), 0) / 1000), 0)))
END AS ApplicantIncome,
fkPurchaserType AS TypeOfPurchaser,
LTRIM(RTRIM(fkDenialReason1)) AS DenialReason1,
LTRIM(RTRIM(fkDenialReason2)) AS DenialReason2,
LTRIM(RTRIM(fkDenialReason3)) AS DenialReason3,
CASE WHEN dbo.udfVariantIsNA(varRateSpread) = 1 THEN 'NA'
WHEN varRateSpread IS NULL OR dbo.udfVariantIsNumeric(varRateSpread) = 0 THEN ''
WHEN dbo.udfVariantGetNumeric(varRateSpread) < 0 THEN ''
ELSE dbo.udfPad(CONVERT(varchar, CONVERT(decimal(7,2), dbo.udfVariantGetNumeric(varRateSpread))), 5, '0', 1)
END AS RateSpread,
fkHOEPA AS HOEPAStatus,
fkLienStatus AS LienStatus
FROM tbl_HMDA WITH (NOLOCK)
INNER JOIN #FilteredApps
ON pkHMDAGuid = fkApplicationID
LEFT JOIN tbl_GeoCode
ON fkGeoCode = pkGeoCode
ORDER BY chrAppNo

END
GO