Data structure changes

_as_MPBM_accountExtension

• iUDF column is added  to the table
  • Based on the provided mapping from NT the IUDF is looked up in the scoa view : _as_MPBM_V_A1ScheduleMapping
• Mtsf column is added to the table
  • Based on the provided mapping from NT the Mstsf is looked up in the scoa view : _as_MPBM_V_A1ScheduleMapping
    
    

_as_MPBM_accountExtensionAllversions

• Accounttype colum is added to the table
  • This column will increase the speed of all the reports/extracts and MPBM
• ucBSACOSAItemCombi column is added to the table
  
  • This column will increase the speed of all the reports/extracts and MPBM
• ucOSAFunctionCombi column is added to the table
  
  • This column will increase the speed of all the reports/extracts and MPBM

_as_MPBM_SegmentModelAllVersions

• SCOAFile
• AccountNumber
• AccountNumberPrefix
• ShortCode
• SCOAAccount
• ShortDesc0
• ShortDesc1
• ShortDesc2
• ShortDesc3 
• ShortDesc4
• ShortDescription
  • These columns increases the sql speed of reports and MPBM

Report changes

Sql's

• _asf_GetBalancesAllaccounts.sql
  • The script is updated for 6.3
  • Performance is improved
• _ASf_GLBalancesPerAccount.sql
  • The script is updated for 6.3
  • Performance is improved
• _as_sp_UpdateAccountType.sql
  
  • The script is updated for 6.3
  • Performance is improved
  • The following types are added:
    
    • 3100 - Property Rates by Usage [Revenue - Non-exchange Revenue]
    • 3200 - Service Charges [Revenue - Non-exchange Revenue]
• Reporting - Create static tables.sql
  • The script is updated for 6.3
• _as_SP_SetSCOAActiveTable.sql
  • The script is updated for 6.3
• _as_MPBM_AccountExtensionAllVersionExtended.sql
  • The script is updated for 6.3
  • Performance is improved
• _as_GLAccountsFullMPBM.sql
  • Has become depreciated
• Sagebudget.SQl
  • Performance is improved
• _as_MPBM_Virements.SQL
  • Performance is improved
• DataExtract Commitment.sql
  • Performance is improved
• mSCOA DataExtract Budget.sql
  • Performance is improved
  • Added Full data sql to get the following:
    • Actuals
    • Actuals running
    • MPBM budgets
    • Sage budgets
    • Calculated next budget type
    • Virements
    • Commitment

To call the sp with full data:

EXEC 
		_AS_SP_DataExtract
			 @StartYear = 2018	
			,@PrintStartYear = 2019	
			,@DataType ='Original Budget'			---Select 'Draft' Union all Select 'Original' union all Select 'Adjusted' union all Select 'Actuals' union all Select 'Virements'
			,@IncludeVirements =0
			,@Actuals =0
			,@IncludeCommitments =0								
			,@ReportType = 'Full system' 			--Select 'Full data' Union all select 'Duplicate check' union all Select 'DataExtract'
			,@ScoaVersion = 'mSCOA_V6.2'
			,@IncludeData ='AA-AR-BB-BN-V-C-SB'

• mSCOA BS .sql
  • Performance is improved
• mSCOA OSB.sql
  • Performance is improved
• Data extract - Actuals Movement only.sql
  • Performance is improved
• mSCOA BSAC.sql
  • Performance is improved
• mSCOA OSA.sql
  • Performance is improved
• Data extract - Actuals .sql
  • Performance is improved
• IDP extract.sql
  • Performance is improved
• _AS_MPBM_RME.sql
  • Performance is improved
• _asf_GetAgeingTransactions.sql
  • Performance is improved
• mSCOA Ageing SP.sql
  • Performance is improved

Reports

Trial balance pack

• • Trial balance pack is updated for 6.3
  • Trial balance pack used budgets from  the MPBM database
  • Trial balance pack shows Budgets vs Actuals

Report pack

MPBM changes

Arrears changes

Contract and retention changes

Performance and management changes

SCOA changes

• Updated the new (missing) 6.3 BSAC/OSAC and function mapping (see attachement Excel for overview)
• Added NT A 6.2 schedule mapping to the database. Mapping can be found in the view : SCOA.DBO._as_MPBM_V_A1ScheduleMapping