Debtor Child sales orders do not appear in the Parent / Head Office account - 7.00.175 SR10 and prior
- Beth Blok (Unlicensed)
- Mike Sheen
Problem
Not all of the child / branch debtor transactions appear in the Parent / Head Office account. This can happen when sales orders for the child / branch debtor were created before the child / branch debtor was linked to the parent / head office account
Solution
SQL skills are required
A working knowledge of SQL Management Studio is required to complete this task.
In Service Release 11 for 07.00.175, when attempting to link a child debtor to a head office account AND unprocessed sales orders exist for the child/branch debtor a warning is displayed.
Warning. 'Debtor account XXXX has unprocessed sales orders xxxx-S01. The link cannot be made until the sales order(s) is processed'
Note: Processed sales order are correctly linked to the parent account
Run the following script in SQL to update the relevant records
-- This script updates the records in the DB_Trans table linking the child / branch transactions to the parent / office account
UPDATE DB_Trans SET DebtorID = (SELECT ParentDebtorID FROM DB_Main WHERE DB_Main.DebtorID = DB_Trans.DebtorID)
-- This script updates the sales order records in the SO_Main table linking the child / branch sales orders to the parent / head office account
UPDATE SO_Main SET ParentDebtorID = (SELECT TOP 1 ParentDebtorID FROM DB_Main WHERE DB_Main.DebtorID =SO_Main.DebtorID) WHERE SO_Main.Status = 0 AND ParentDebtorID <> (SELECT TOP 1 ParentDebtorID FROM DB_Main WHERE DB_Main.DebtorID =SO_Main.DebtorID)