SQL Server Error 515 - Cannot insert Null value occurs in GL.

Applies to: Dynamics SL 2011Microsoft Dynamics SL 2011 Service Pack 1Microsoft Dynamics SL 7.0 Service Pack 2

TechKnowledge Content


POTENTIAL CAUSES:


1. Occurs in General Ledger Post Transactions (01.520) if posting to an Account with an Inactive Account Status. See resolution 21476.


2. Occurs in General Ledger Release GL Batches (01.400). Fields are not fully populated when the batch is brought up in the Journal Transactions screen (01.010). See resolution 1532.


3. Occurs in Release GL Batches (01.400) with the Optional Info referring to 'Sub'. CpnyID in the GLTRAN table is not populated. See resolution 11929.


4. Occurs in Post Transactions (01.520) when there is an incorrect LedgerID in GLTRAN. See resolution 17549.


5. Occurs in Post Transactions (01.520) if the Account Type is incorrect. See resolution 21434.


6. Occurs when releasing a General Ledger Allocation batch in Solomon IV Version 4.21 Service Pack 1. It does not occur in subsequent versions.


7. Occurs releasing a batch inJournal Transactions (01.010)with the Optional Info referring to Sub when including Multi-company transactions in the detail area, but Intercompany relationships have not been established in Multi-Company Intercompany Account/Sub Maintenance (13.260)for the GL Module. See resolution27142.


Resolution 1532 - Sum the GLTRAN and BATCH records using Query Analyzer.

CORRECTION STEPS:


1. Make a BACKUP of the database, which can be restored in case an undesired data loss occurs.


2. Access the appropriate Application database using Query Analyzer.


3. Sum the Credit and Debit amounts of the GLTRAN record using the following statements:


SELECT SUM(CrAmt) from GLTRAN where BatNbr = 'XXXXXX' and Module = 'YY'


SELECT SUM(DrAmt) from GLTRAN where BatNbr = 'XXXXXX' and Module = 'YY'


SELECT SUM(CuryCrAmt) from GLTRAN where BatNbr = 'XXXXXX' and Module = 'YY'


SELECT SUM(CuryDrAmt) from GLTRAN where BatNbr = 'XXXXXX' and Module = 'YY'


(where XXXXXX = the applicable Batch Number and YY = the applicable Module)


4. Verify that the sum of the CrAmt and the sum of the DrAmt are equal to the Control Total of the Batch. Also, verify that the sum of the CuryCrAmt and the sum of the CuryDrAmt are equal to the Control Total of the Batch. To test this, use Initialize Mode to change the Module in General Ledger Journal Transactions (01.010) to the proper Module and bring up the Batch. The Control Total of the Batch should be the same as the sum of the Credits and sum of the Debits.


5. Sum the Credit and Debit amounts of the BATCH record using the following statements where XXXXXX = the applicable Batch Number and YY = the applicable Module: SELECT SUM(CtrlTot) from BATCH where BatNbr = 'XXXXXX' and Module = 'YY' SELECT SUM(CrTot) from BATCH where BatNbr = 'XXXXXX' and Module = 'YY' SELECT SUM(DrTot) from BATCH where BatNbr = 'XXXXXX' and Module = 'YY' SELECT SUM(CuryCtrlTot) from BATCH where BatNbr = 'XXXXXX' and Module = 'YY' SELECT SUM(CuryCrTot) from BATCH where BatNbr = 'XXXXXX' and Module = 'YY' SELECT SUM(CuryDrTot) from BATCH where BatNbr = 'XXXXXX' and Module = 'YY'


6. Verify that the sum of the CrTot and the sum of the DrTot are equal to the CrAmt and DrAmt of the GLTRAN table. Also, verify that the sum of the CuryCrTot and the sum of the CuryDrTot are equal to the CuryCrAmt and CuryDrAmt of the GLTRAN table.


7. If everything appears to be in balance, verify that there are no records missing from GLTRAN. Each transaction in the Batch should be released, the Posted column should be 'U' for Unposted, etc. Update any missing records.


NOTE: If the problem occurred during posting, run the General Ledger Post Transactions (01.520) process again. If still unsuccessful, refer to sub-resolution 3010.


Resolution 11929 - Verify the CompanyID in the BATCH and GLTRAN tables.

CORRECTION STEPS:


1. Make a BACKUP of the database, which can be restored in case an undesired data loss occurs.


2. Using Query Analyzer, run the following statement to verify that CpnyID in the BATCH table is correct:


SELECT * from BATCH where BatNbr = 'XXXXXX' and Module = 'GL'


(where XXXXXX is the appropriate Batch Number)


3. If the CpnyID is incorrect, execute the following statement:


UPDATE BATCH SET CpnyID = 'YYYY' where BatNbr = 'XXXXXX' and Module = 'GL'


(where XXXXXX is the appropriate Batch Number and YYYY is the appropriate Company ID)


4. Execute the following statement to verify that CpnyID in the GLTRAN table is correct:


SELECT * from GLTRAN where BatNbr = 'XXXXXX' and Module = 'GL'


(where XXXXXX is the appropriate Batch Number)


5. If the CpnyID is incorrect, execute the following statement:


UPDATE GLTRAN SET CpnyID = 'YYYY' where BatNbr = 'XXXXXX' and Module = 'GL'


(where XXXXXX is the appropriate Batch Number and YYYY is the appropriate Company ID)


Resolution 17549 - Correct the LedgerID field in the GLTRAN table using Query Analyzer.

CORRECTION STEPS:


1. Make a BACKUP of the database, which can be restored in case an undesired data loss occurs.


2. Using Query Analyzer, access the appropriate application database.


3. Run the following query:


SELECT * from GLTRAN where BatNbr = 'YYYYYY' and Module = 'MM'


(where 'YYYYYY' = the appropriate GL batch number and 'MM' = the appropriate Module)


4. If LedgerID is incorrect, run the following query:


UPDATE GLTRAN SET LedgerID = 'XXXXXXXXXXX' where BatNbr = 'YYYYYY' and LedgerID = 'xxxxxxxxxx' and Module = 'MM'


(where 'XXXXXXXXXXX' = the correct LedgerID, 'YYYYYY' = the appropriate GL batch number, 'xxxxxxxxxx' = the incorrect LedgerID, and 'MM' = the appropriate Module)


5. Access General Ledger Post Transactions (01.520), and post the batch.


Resolution 21434 - Update the AcctType field in the ACCOUNTand ACCTXREF tables using Query Analyzer

1. Access General Ledger GL Setup (01.950) - Chart of Acct Order.


2. Make note of the Chart of Acct Order that is selected. The values for the AcctType field in the ACCOUNT and ACCTXREF tables that correspond with the COA Order will consist of a numeric character and an alphanumeric character. They are often as follows:


Assets 1A Liabilities 2L Income 3I Expenses 4E


CORRECTION STEPS:


1. Make a BACKUP of the database, which can be restored in case an undesired data loss occurs.


2. Open Query Analyzer, log into the appropriate server, and choose the Application database.


3. Execute the following SQL statement to verify the value in the AcctType field in the ACCOUNT table:


SELECT AcctType, * from ACCOUNT where Acct = 'XXXXXX'


(where 'XXXXXX' = the affected Account number)


4. Run the following statement if AcctType is not populated or is incorrect:


UPDATE ACCOUNT SET AcctType = 'YY' where Acct = 'XXXXXX'


(where 'YY' = a value based on the order selected in the GL Setup | Chart of Acct Order tab as noted in Verification Step 2.)


5. Choose the System database, and execute the following statement to check the AcctType field in ACCTXREF:


SELECT AcctType, * from ACCTXREF where Acct = 'XXXXXX' and CpnyID = 'yyyy'


(where 'XXXXXX' = the affected Account number and 'yyyy' = the appropriate Company ID)


6. Execute the following statement if AcctType is not populated or is incorrect:


UPDATE ACCTXREF SET AcctType = 'YY' where Acct = 'XXXXXX' and CpnyID = 'yyyy'


Resolution 21476 - Verify that the Accounts have an Active Account Status in General Ledger Chart of Accounts Maintenance ( 01.260 ).

CORRECTION STEPS:


1. Go to General Ledger Chart of Accounts Maintenance (01.260).


2. Select the appropriate Account Number.


3. Verify the Status of the account is Active.


Resolution 27142 - Create From/To relationships for all Account/Subaccount combinations that will be used for each Company in Multi-Company Inter-Company Acct/Sub Maintenance (13.260).

NOTES:


1. The following fields are used in Inter-Company Acct/Sub Maintenance (13.260) to designate how different Account/Subaccounts are to be used for Inter-Company transactions.


>>FROM - use to enter Due From Company information.


Company ID - Unique key used to identify each Company in the system; used when entering Company information on transactions.


Module - Two character code for module. If the same Account and Subaccount is to be used for all modules select ALL, otherwise select the specific module from the drop down box.


Screen - Seven character code for screen. If the same Account and Subaccount is to be used for all screens in the modules enter ALL, otherwise enter the specific screen number, i.e. 03010.


Account - Account the Inter-Company transaction is Due From.


Sub - Subaccount the Inter-Company transaction is Due From.


>>TO - use to enter Due To Company information.


Company ID - Unique key used to identify each Company in the system; used when entering Company information on transactions.


Account - Account the Inter-Company transaction is Due To.


Sub - Subaccount the Inter-Company transaction is Due To.


2. Different Account or Subaccounts may not be used for the same Inter-Company relationship and the same source data screen.


Sub-Resolution 3010 - Update the BATCH or GLTRAN record using Query Analyzer to correct the CuryID value.

VERIFICATION STEPS:


1. Log into the appropriate Application database using Query Analyzer and run the following statement:


SELECT CuryID, * from GLTRAN where BatNbr = 'XXXXXX' and Module = 'YY'


(where XXXXXX = the Batch Number and YY = the Module of the Batch)


2. Compare the GLTRAN CuryID values to the CuryID in the BATCH record using the following SQL statement:


SELECT CuryID from BATCH where BatNbr = 'XXXXXX' and Module = 'YY' (where XXXXXX = the Batch Number and YY = the Module of the Batch)


NOTE: This should return one and only one record.


3. If the value differs from the GLTRANs or if a null value appears, proceed with the Correction Steps.


CORRECTION STEPS:


1. Make a BACKUP of the database, which can be restored in case an undesired data loss occurs.


2. Update the BATCH or GLTRAN record with the following statement:


UPDATE BATCH (GLTRAN) SET CuryID = 'XXXX' where BatNbr = 'YYYYYY' and Module = 'ZZZZ' and CuryID = 'xxxx' (where XXXX = the correct CuryID, YYYYYY = the Batch Number, ZZ = the Module, and xxxx = the incorrect CuryID)




This article was TechKnowledge Document ID:126851