The municipal code is 12 digits, called OSC, and is issued by Treasury (010201000000).
The New York municipal data uses an account code like: A8012. The first letters indentify the Fund Codes. For Expendutres, the first 4 digits identify the function code and the last digit identifies the object code category. For Revenues and Fund Equity the digits identify the Function Codes. Currently we import Revenues, Expenditures, Fund Equity and Liabilities.
To import the AFR for a single year:
Begin by updating the MunIDs
Update MunIDs
1. UPDATE aa_ny_mu_afr SET ImpMunID = NULL;
2. UPDATE aa_ny_mu_afr JOIN m_municipalities ON MunLocalCode = MUNICIPAL_CODE SET ImpMunID = MunID WHERE MunModID = 1 AND MunState = 'NY';
3. Check for missing MunIDs: SELECT * FROM aa_ny_mu_afr ORDER BY ImpMunID;
Next update the AndIDs:
1. UPDATE aa_ny_mu_afr SET ImpAndID = NULL;
2. UPDATE aa_ny_mu_afr JOIN m_annualdata ON ImpMunID = AndMunID AND AndYear = CALENDAR_YEAR SET ImpAndID = AndID WHERE AndScenario = 'Baseline';
3. Check for missing AndIDs: SELECT * FROM aa_ny_mu_afr ORDER BY ImpAndID;
Insert any missing AndID records:
1. INSERT INTO m_annualdata (AndMunID, AndYear, AndDataStatus, AndScenario, AndSource, AndDateAdded, AndDateModified) SELECT DISTINCT ImpMunID, CALENDAR_YEAR, 'Audited', 'Baseline', 'State Download', NOW(), NOW() FROM aa_ny_mu_afr WHERE ImpAndID IS NULL AND ImpMunID IS NOT NULL;
2. Then run the Update And’s again if necessary
Update Fund Codes
In Excel =IF(ISNUMBER(VALUE(((RIGHT(O2,LEN(O2)-1))))),LEFT(O2,1),LEFT(O2,2)) when Account Number is in column “O”.
Updated Object Codes:
In Excel Column “H (ObjectCode)” =IF(R2="EXPENDITURES",RIGHT(O2,1),"")
1. UPDATE aa_ny_mu_afr SET ImpObjectCode = RIGHT(ACCOUNT_NUM,1) WHERE ImpSection = 'Expenditures';
Insert the temp data into the Finanacial Data Details table.
1. INSERT INTO m_financialdatadetail (FddAndID, FddFutID, FddFtsID, FddSscID, FddObjectCode, FddAmount, FddComment, FddDateAdded, FddDateModified) SELECT ImpAndID, ImpFutID, ImpFtsID, ImpSscID, ImpObjectCode, ImpAmount, ImpDescription, NOW(), NOW() FROM aa_ny_mu_afr WHERE ImpSscID IS NOT NULL AND ImpAmount <> '' AND ImpAmount <> 0;
2. Run MU Update Indicator Score New Algorithm
Run the Long-term Debt update script:
1. UPDATE m_annualdata JOIN (SELECT AndID, ROUND(SUM(FddAmount),0) AS TotalGALTD FROM m_financialdatadetail JOIN m_annualdata ON AndID = FddAndID JOIN m_municipalities ON MunID = AndMunID JOIN m_finsubcategories ON SscID = FddSscID JOIN m_fincategories ON SucID = SscSucID JOIN c_fundtypestatecodes ON FtsID = FddFtsID WHERE MunModID = 1 AND MunState = 'NY' AND SucSection = 'SOP' AND SscSucID = 36 AND FtsOperatingExp = 'Yes' AND (FddSscID <> 4611 AND FddSscID <> 4615 AND FddSscID <> 4583) GROUP BY AndID) AS GALTD ON m_annualdata.AndID = GALTD.AndID SET AndGeneralLongTermDebt = GALTD.TotalGALTD;
This is an new version for Long-term Debt:
2. SELECT AndYear, AndID, ROUND(SUM(FddAmount),0) AS TotalGALTD FROM m_financialdatadetail JOIN m_annualdata ON AndID = FddAndID JOIN m_municipalities ON MunID = AndMunID JOIN m_finsubcategories ON SscID = FddSscID JOIN m_fincategories ON SucID = SscSucID JOIN c_fundtypestatecodes ON FtsID = FddFtsID JOIN m_fundtype ON FutID = FtsFutID WHERE MunModID = 1 AND MunState = 'NY' AND SucSection = 'SOP' AND (SscLineNumber >= 620 AND SscLineNumber <= 629) AND AndMunID = 32904 AND FutType = 'Governmental' GROUP BY AndID ORDER BY AndYear DESC