Storing Chart of Account Strings Mapping

This collection of steps is used to store a customer’s account stings in the “aa_mi_bsa_import” table with the IS’s mapped to theor local codes.

Truncate table “aa_mi_bsa_import”.

If a Trial Balance with Account Strings has already been imported into the Financial Data Details Table

INSERT INTO aa_mi_bsa_import (ImpMunID, ImpFtsID, ImpFutID, ImpSscID, ImpSection, ImpAccountString) SELECT DISTINCT AndMunID, FddFtsID, FddFutID, FddSscID, SucSection, FddAccountString FROM m_financialdatadetail JOIN m_finsubcategories ON SscID = FddSscID JOIN m_fincategories ON SucID = SscSucID JOIN m_annualdata ON AndID = FddAndID WHERE FddAndID = 54331.

This gets the Local Unit’s ID number for the Fund Code: UPDATE aa_mi_bsa_import JOIN c_lufundcodes ON LfcCode = LEFT(ImpAccountString,3) SET ImpLfcID = LfcID WHERE LfcMunID = ImpMunID

The next 2 get the Revenue and Expense LccID’s.

UPDATE aa_mi_bsa_import JOIN c_lucostcenters ON LccSscID = ImpSscID SET ImpFddLccID = LccID WHERE LccSection ='Revenues' AND LccMunID = ImpMunID AND LccCode = RIGHT(LEFT(ImpAccountString,11),3)

UPDATE aa_mi_bsa_import JOIN c_lucostcenters ON LccSscID = ImpSscID SET ImpFddLccID = LccID WHERE LccSection ='Expense' AND LccMunID = ImpMunID AND LccCode = RIGHT(LEFT(ImpAccountString,7),3)

Once all the Local Unit codes are stored in “aa_mi_bsa_import’ then import this data into the Account String table:

INSERT INTO c_luaccountstrings (LasMunID, LasSection, LasLfcID, LasLccID, LasLocID, LasAccountString) SELECT ImpMunID, ImpSection, ImpLfcID, ImpFddLccID, '' AS ObjectCodeID, ImpAccountString FROM aa_mi_bsa_import.