Added on January 11, 2018
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:
2. Create the submission file without applying the hot fix, then follow these steps to manually correct the submission file prior to submission:
Nothing is changing for 2017 CRA submissions. If you have any questions please feel free to contact us.
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
Schedule a free online demonstration to see what Comply HMDA/CRA can do for you!
Free HMDA/CRA Demo Contact Us