Database Schema Enhancements
Database Schema Update
To improve the overall performance of MYOB Acumatica Professional Services features and customer reports, database schema changes were introduced in CR9 and completed in CR12. In CR9, only the PMTran table was converted from an extension table to an extension using the custom fields approach. In CR12, all standard Acumatica tables used in the PS Edition are now extended with custom fields.
This change should result in less complex queries, leading to improved application performance.
Refer to the upgrade notes for guidance on moving data from extension tables to custom fields and remapping the existing generic inquiries and API endpoints to use the new custom fields.
DB Schema Changes Phase 2
The following fields and DAC changes have been introduced:
| Previous Extension Table | Previous DB Column Name | Current Table | New DB Column Name |
| MYPEAddress | IsOther | Address | UsrMYPExIsOther |
| Comments | UsrMYPExComments | ||
| IsPostal | UsrMYPExIsPostal | ||
| IsBusiness | UsrMYPExIsBusiness | ||
| IsResidential | UsrMYPExIsResidential | ||
| IsRegisteredOffice | UsrMYPExIsRegisteredOffice | ||
| LinkedAddressID | UsrMYPExLinkedAddressID | ||
| ExtRefNbr | UsrMYPExExtRefNbr | ||
| FamilyGroupID | UsrMYPExFamilyGroupID | ||
| LinkedFamilyGroupAddressID | UsrMYPExLinkedFamilyGroupAddressID | ||
| MYPEARInvoice | PartnerID | ARInvoice | UsrMYPExPartnerID |
| ManagerID | UsrMYPExManagerID | ||
| HasParagraphLines | UsrMYPExHasParagraphLines | ||
| MYPEContact | DateOfDeath | Contact | UsrMYPExDateOfDeath |
| Shared | UsrMYPExShared | ||
| MYPEPMProject | PartnerID | Contract | UsrMYPExPartnerID |
| ManagerID | UsrMYPExManagerID | ||
| ServiceLineID | UsrMYPExServiceLineID | ||
| IsParentProject | UsrMYPExIsParentProject | ||
| ParentContractID | UsrMYPExParentContractID | ||
| BillSeparately | UsrMYPExBillSeparately | ||
| HoldBilling | UsrMYPExHoldBilling | ||
| RolloverRule | UsrMYPExRolloverRule | ||
| IsProjectRolledOver | UsrMYPExIsProjectRolledOver | ||
| RolledFromProject | UsrMYPExRolledFromProject | ||
| MYPECRActivity | PartnerID | CRActivity | UsrMYPExPartnerID |
| ManagerID | UsrMYPExManagerID | ||
| WorkTypeID | UsrMYPExWorkTypeID | ||
| WorkflowStageID | UsrMYPExWorkflowStageID | ||
| EmployeeAllocationID | UsrMYPExEmployeeAllocationID | ||
| Customer | UsrMYPEEntityTypeID | Customer | UsrMYPExEntityTypeID |
| UsrMYPEJurisdiction | UsrMYPExJurisdiction | ||
| UsrParentBAccountID | UsrMYPExParentBAccountID | ||
| UsrUseFamilyGroupAddresses | UsrMYPExUseFamilyGroupAddresses | ||
| UsrTradingAs | UsrMYPExTradingAs | ||
| MYPECustomerClass | Jurisdiction | CustomerClass | UsrMYPExJurisdiction |
| ParentBAccountID | UsrMYPExParentBAccountID | ||
| MYPEEPApprovalExt | EmployeeIsApprover | EPApproval | UsrMYPExEmployeeIsApprover |
| MYPEEPEmployee | IsManager | EPEmployee | UsrMYPExIsManager |
| IsPartner | UsrMYPExIsPartner | ||
| ProductivityPct | UsrMYPExProductivityPct | ||
| Office | UsrMYPExOffice | ||
| Team | UsrMYPExTeam | ||
| EmployeeLevel | UsrMYPExEmployeeLevel | ||
| MYPEEPEmployeeClassExt | ProductivityPct | EPEmployeeClass | UsrMYPExProductivityPct |
| MYPEPMProforma | PartnerID | PMProforma | UsrMYPExPartnerID |
| ManagerID | UsrMYPExManagerID | ||
| HasParagraphLines | UsrMYPExHasParagraphLines | ||
| WIPAdjustmentRefNbr | UsrMYPExWIPAdjustmentRefNbr | ||
| MYPEPMTask | WorkTypeID | PMTask | UsrMYPExWorkTypeID |
| WorkflowStageID | UsrMYPExWorkflowStageID | ||
| PartnerID | UsrMYPExPartnerID | ||
| ManagerID | UsrMYPExManagerID | ||
| ClientID | UsrMYPExClientID | ||
| FinPeriodID | UsrMYPExFinPeriodID | ||
| ServiceLineID | UsrMYPExServiceLineID | ||
| Billable | UsrMYPExBillable | ||
| MYPEPMTimeActivityExt | TimeCardRefNbr | PMTimeActivity | UsrMYPExTimeCardRefNbr |
| Units | UsrMYPExUnits | ||
| BillingRate | UsrMYPExBillingRate | ||
| IsNewLineCorrect | UsrMYPExIsNewLineCorrect | ||
| IsPublicHoliday | UsrMYPExIsPublicHoliday | ||
| BillingQty | UsrMYPExBillingQty | ||
| BillingAmount | UsrMYPExBillingAmount | ||
| LeaveRequestRefNbr | UsrMYPExLeaveRequestRefNbr | ||
| TaskActivityNoteID | UsrMYPExTaskActivityNoteID | ||
| WorkflowStageID | UsrMYPExWorkflowStageID | ||
| EmployeeAllocationID | UsrMYPExEmployeeAllocationID | ||
| MYPEUserPreferencesExt | CalendarDefaultView | UserPreferences | UsrMYPExCalendarDefaultView |
| CalendarTimeInterval | UsrMYPExCalendarTimeInterval | ||
| MYPEBAccount | UsrMYPEEntityTypeID | BAccount | UsrMYPExEntityTypeID |
| UsrMYPEManagerID | UsrMYPExManagerID | ||
| UsrMYPEPartnerID | UsrMYPExPartnerID | ||
| UsrMYPEFamilyGroupID | UsrMYPExFamilyGroupID |
Screen performance enhancements
The Project Billing, WIP Allocation, and Invoice Release processes have been refactored to reduce queries and data handling, leading to faster processing.
Additionally, Project and Client screens have been optimized for improved navigation speed when viewing or accessing records.Updated API Endpoints and Generic Inquiries
The ADVPS Web Service endpoint has been updated with new field mappings. All generic inquiries, reports, dashboards, and API endpoints need to be updated to replace extension table fields with new fields.
To facilitate these updates, two maintenance screens have been added:
Generic Inquiry Upgrade (MYPE4010)