Debtor Child sales orders do not appear in the Parent / Head Office account - 7.00.175 SR10 and prior

Owned by Beth Blok (Unlicensed)
Last updated: Nov 21, 2022 by Mike SheenVersion comment
1 min read

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)