Account, Subaccount, and/or Company ID don't appear in PV list in Microsoft Dynamics SL
This article provides a solution to an issue where account, Subaccount, and/or Company ID doesn't appear in PV list in Microsoft Dynamics SL.
Applies to: Microsoft Dynamics SL 2011
Original KB number: 871313
The POTENTIAL CAUSES information that is listed below is provided to help identify possible scenarios for when this issue would occur. It should be noted, however, that in most cases this issue is encountered as a result of either Potential Cause 1, 2, or 3 listed below.
- The ACCTXREF or SUBXREF table isn't populated in the System database. See Resolution 9436 and Resolution 10.
- The COMPANY table is incorrectly populated in the System database. See Resolution 11857.
- The Account Validation feature is inappropriately enabled in General Ledger GL Setup (01.950.00). If the option is needed and enabled, see Resolution 7067 to set up the correct Account/Subaccount combinations. If the option isn't needed, but has been enabled, see Resolution 15227. For more information about the use of this feature, see Resolution 8401.
- The Valid Combos Required option isn't selected in Shared Information Flexkey Definition (21.320.00). See Resolution 4077.
- If the Multi-Company module is being used, the Activate Multi-Company with Inter-Company Processing option in General Ledger GL Setup (01.950.00) isn't selected. See Resolution 7822.
- A record doesn't exist in the INTERCOMPANY table in the System database. See Resolution 12255.
- It can occur if the ACCTXREF, ACCTSUB, or SUBXREF tables are located in the application database and in the system database. See resolution 9.
- It can occur if the system views point to the wrong system database. See Resolution 10.
- It can occur if the triggers are associated with the wrong application database. See Resolution 11.
To resolve the potential issues, use the appropriate resolution.
Remove the ACCTXREF, ACCTSUB, and SUBXREF tables from the application database. These tables should only be located in the Microsoft Dynamics SL System database or in the Microsoft Business Solutions - Solomon System database. To remove them, follow these steps:
Make a backup copy of the Microsoft Dynamics SL Application and of the Microsoft Dynamics SL System databases.
Using Query Analyzer, run the following statements against the Microsoft Dynamics SL Application database:
drop table acctxref drop table acctsub drop table subxref
If you receive Server Msg 3701 when you run these statements, that is OK. The message just indicates that the table wasn't in the database.
Update the system views. To do it, follow these steps:
- Use the appropriate step:
- In Microsoft Dynamics SL 7.0, select Start, select Control Panel, select Administrative Tools, and then select Microsoft Dynamics SL Database Maintenance.
- In Microsoft Dynamics SL 6.5 and in earlier versions, select Start, point to All Programs, point to Microsoft Dynamics SL, and then select Database Maintenance.
- In the Destination SQL Server Name box, type the name of your SQL Server.
- In the Login ID box, type sa.
- In the Password box, type the system administrator password.
- Select Connect.
- On the Update Databases tab, select the system database in the System Database Name box.
- In the Databases box, select each of the application databases.
- Select Update Views.
- Select OK in the following confirmation message:
98290000 - Updating Views of System Database is Complete.
- Select Close.
Run the rebuild_triggers.sql script. For more information, see Various SQL Server error messages occur in multiple screens in Microsoft Dynamics SL.
Select the Valid Combos Required box in Shared Information Flexkey Definition (21.320.00).
When Valid Combos Required is selected (True), Microsoft Dynamics SL populates the PV List from the SUBACCT table. For example, the Subaccount must be entered in Subaccount Maintenance (01.270.00).
When Valid Combos Required isn't selected (False), Microsoft Dynamics SL populates the PV List from the FLEXKEY table for the Subaccount Segment. The Segment IDs are entered in Flexkey Table Maintenance (21.330.00).
- Access Shared Information Flexkey Definition (21.320.00).
- Enter SUBACCOUNT in Field Type and tab off the field.
- Select the Valid Combos Req Box.
- Save the changes and exit the screen.
Run Generate Combinations in General Ledger Acct/Sub Maintenance (01.320.02).
- Access Acct/Sub Maintenance (01.320.00) and review the Account/Subaccount Combinations. If an Account/Subaccount doesn't exist, continue with the Correction Steps.
- In Account/Subaccount Maintenance, select Generate Combinations to display Acct/Sub Maintenance Worksheet (01.320.01) to add existing Account/Subaccount combinations.
- The Account/Subaccount Maintenance Worksheet can be used to generate Account/Subaccount combinations. Select Generate Combos to display Generate Acct/Sub Combinations (01.320.02).
- The Generate Acct/Sub Combinations can be used to copy existing Accounts and Subaccounts to build new Account/Subaccount combinations.
Verify the Activate Multi-Company with Inter-Company Processing option is enabled in General Ledger GL Setup (01.950.00) and Multi-Company Setup (13.950.00).
- Access GL Setup.
- Verify the Activate Multi-Company with Inter-Company Processing option is selected.
- Access Multi-Company Setup.
- Verify the Activate Multi-Company with Inter-Company Processing option is selected.
Explain the function of the General Ledger Account Validation feature.
- Account Validation, which is enabled in General Ledger Setup(01.950.00), is a new feature introduced with Solomon IV Version 4.x. The purpose of this feature is to validate Account and Subaccount combinations during data entry (if Validate Account/Subaccount at Posting isn't selected) or when Post Transactions (01.520.00) is run (if Validate Account/Subaccount at Posting is selected).
- If Multi-Company is used, the combinations are validated by Company ID. The Account and Subaccount combinations by Company ID are set up in Account/Subaccount Maintenance (01.320.00). Entries made here populate the ACCTSUB table with the combinations for each Company.
- It isn't necessary to enter data in Account/Subaccount Maintenance if Account Validation isn't being used.
Save changes in General Ledger Chart of Accounts Maintenance (01.260.00) or Subaccount Maintenance (01.270.00) to populate the ACCTXREF or SUBXREF table(s) in the System database.
The ACCTXREF table, in the System database, stores the Account numbers for each Company that appears in the Master Account List or Possible Values List in any data entry screens containing an Account field. The SUBXREF table, in the System database, stores the Subaccount numbers for each Company that appears in the Subaccount List or Possible Values List in any data entry screens containing a Subaccount field.
- Access General Ledger Chart of Accounts Maintenance (01.260.00) or Subaccount Maintenance (01.270.00) and write down the Description that appears for the first Account or Subaccount in the list.
- Make a small change to the Description field and save the changes.
- Change the Description field back to the original value and save the changes.
- This process will populate the ACCTXREF or SUBXREF table(s) with all of the possible Accounts/Subaccounts for the Company that appears in Chart of Accounts Maintenance or Subaccount Maintenance.
- If Account Validation is enabled in the database, possible values in the Master Account List that appears in data entry screens may be restricted by Company ID. If Account Validation isn't enabled, all the accounts that appear in Chart of Accounts Maintenance should appear in the Master Account Lists in data entry screens for each Company in the database.
Verify the COMPANY table in the System database is populated with the correct Company information using SQL Query Analyzer.
Make a BACKUP of the database, which can be restored in case an undesired data loss occurs.
Using SQL Query Analyzer, access the appropriate System database and execute the following statement:
SELECT CpnyCOA, CpnySub, * from COMPANY
Any blank or incorrect values in the CpnyCOA and CpnySub fields. These fields hold the Company ID of the primary Chart of Accounts. If any erroneous items are located, proceed with the Correction Steps.
Execute the following statement:
UPDATE COMPANY SET CpnyCOA = 'XXXX', CpnySub = 'xxxx' where CpnyCoa = 'YYYY' and CpnySub = 'yyyy'
Where XXXX and xxxx = the MASTER Company ID and YYYY and yyyy = current value
Update the INTERCOMPANY table in the System database using SQL Query Analyzer.
In cases where a record doesn't exist in the INTERCOMPANY table in the System database with To Company and From Company equal to the Company ID, and Module equal to ZZ, the following statement may be used:
INSERT INTO INTERCOMPANY (FromAcct, FromCompany, FromSub, Module, Screen, ToAcct, ToCompany, ToSub, User1, User2, User3, User4) VALUES (' ', '<CPNYID>',' ', 'ZZ',' ',' ', '<CPNYID>',' ',' ',' ', '0.00', '0.00')
Where CPNYID = Master Company ID
For example, the following statement would be used for the DemoPremier0060 database:
INSERT INTO INTERCOMPANY (FromAcct, FromCompany, FromSub, Module, Screen, ToAcct, ToCompany, ToSub, User1, User2, User3, User4) VALUES (' ', '0060',' ', 'ZZ',' ',' ', '0060',' ',' ',' ', '0.00', '0.00')
Access General Ledger GL Setup (01.950.00) and deselect the Validate Account/Subaccount option. Save the changes.
If Validate Account/Subaccount is selected in GL Setup and Account/Subaccount combinations have not been set up in Acct/Sub Maintenance, Account and Subaccount PV lists will be blank. For more information about the Account Validation feature, see Resolution 8401.