Urgent Global Customer


I

imma

Hi , I am a having a very urgent issue regarding the global customer, we are
using RMS with HQ, we have 6 stores and all the stores are connected to HQ in
all the stores customers is defined as global customers , the problem started
after i have merged the customer accounts, Below is the query i used

1) DECLARE @CustomerKeep INT

DECLARE @CustomerDelete INT

SET @CustomerKeep = (SELECT ID FROM Customer WHERE AccountNumber = 'X')

SET @CustomerDelete = (SELECT ID FROM Customer WHERE AccountNumber = 'Y')

UPDATE AccountReceivable
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE [Transaction]
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE Journal
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE [Order]
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE Payment
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE ShipTo
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE TransactionHold
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete
2)CREATE VIEW ARTemp AS SELECT CustomerID, SUM(Balance) AS Balance
FROM AccountReceivable
LEFT JOIN Customer ON AccountReceivable.CustomerID = Customer.ID
WHERE Customer.AccountNumber = 'X'
GROUP BY CustomerID

3) UPDATE Customer
SET AccountBalance = ARTemp.Balance
FROM ARTemp
INNER JOIN customer ON ARTemp.CustomerID = Customer.ID

4) Drop VIEW ARTemp

5) DECLARE @CustomerKeep INT

SET @CustomerKeep = (SELECT ID FROM Customer WHERE AccountNumber = 'X')


UPDATE Customer SET Customer.TotalSales = A.TotalSales, Customer.TotalVisits
= A.TotalVisits
FROM Customer
LEFT JOIN (SELECT CustomerID, COUNT([Transaction].TransactionNumber) AS
TotalVisits, SUM(Total) AS TotalSales
FROM [Transaction]
GROUP BY CustomerID) A ON Customer.ID = A.CustomerID
WHERE Customer.ID = @CustomerKeep

UPDATE Customer
SET Customer.TotalSavings = A.TotalSavings
FROM Customer
LEFT JOIN (SELECT CustomerID, SUM((FullPrice - Price)*Quantity) AS
TotalSavings
FROM TransactionEntry
LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber
GROUP BY CustomerID) A ON Customer.ID = A.CustomerID
WHERE Customer.ID = @CustomerKeep

UPDATE Customer
SET Customer.LastVisit = [Transaction].Time
FROM Customer
LEFT JOIN [Transaction] ON Customer.ID = [Transaction].CustomerID
WHERE [Transaction].Time IN (select max([Transaction].Time)
FROM [Transaction]
GROUP BY CustomerID) AND Customer.ID = @CustomerKeep


After the merging of the accounts the problem we encountered was that from
the stores when we want to receive payment some invoices are not showing,

Please Help me on this issue its really urgent

Regards
 
Ad

Advertisements

J

jennydat

What version are you using?
--
Jenny


imma said:
Hi , I am a having a very urgent issue regarding the global customer, we are
using RMS with HQ, we have 6 stores and all the stores are connected to HQ in
all the stores customers is defined as global customers , the problem started
after i have merged the customer accounts, Below is the query i used

1) DECLARE @CustomerKeep INT

DECLARE @CustomerDelete INT

SET @CustomerKeep = (SELECT ID FROM Customer WHERE AccountNumber = 'X')

SET @CustomerDelete = (SELECT ID FROM Customer WHERE AccountNumber = 'Y')

UPDATE AccountReceivable
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE [Transaction]
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE Journal
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE [Order]
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE Payment
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE ShipTo
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE TransactionHold
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete
2)CREATE VIEW ARTemp AS SELECT CustomerID, SUM(Balance) AS Balance
FROM AccountReceivable
LEFT JOIN Customer ON AccountReceivable.CustomerID = Customer.ID
WHERE Customer.AccountNumber = 'X'
GROUP BY CustomerID

3) UPDATE Customer
SET AccountBalance = ARTemp.Balance
FROM ARTemp
INNER JOIN customer ON ARTemp.CustomerID = Customer.ID

4) Drop VIEW ARTemp

5) DECLARE @CustomerKeep INT

SET @CustomerKeep = (SELECT ID FROM Customer WHERE AccountNumber = 'X')


UPDATE Customer SET Customer.TotalSales = A.TotalSales, Customer.TotalVisits
= A.TotalVisits
FROM Customer
LEFT JOIN (SELECT CustomerID, COUNT([Transaction].TransactionNumber) AS
TotalVisits, SUM(Total) AS TotalSales
FROM [Transaction]
GROUP BY CustomerID) A ON Customer.ID = A.CustomerID
WHERE Customer.ID = @CustomerKeep

UPDATE Customer
SET Customer.TotalSavings = A.TotalSavings
FROM Customer
LEFT JOIN (SELECT CustomerID, SUM((FullPrice - Price)*Quantity) AS
TotalSavings
FROM TransactionEntry
LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber
GROUP BY CustomerID) A ON Customer.ID = A.CustomerID
WHERE Customer.ID = @CustomerKeep

UPDATE Customer
SET Customer.LastVisit = [Transaction].Time
FROM Customer
LEFT JOIN [Transaction] ON Customer.ID = [Transaction].CustomerID
WHERE [Transaction].Time IN (select max([Transaction].Time)
FROM [Transaction]
GROUP BY CustomerID) AND Customer.ID = @CustomerKeep


After the merging of the accounts the problem we encountered was that from
the stores when we want to receive payment some invoices are not showing,

Please Help me on this issue its really urgent

Regards
 
Ad

Advertisements

I

imma

We are using 2.0


jennydat said:
What version are you using?
--
Jenny


imma said:
Hi , I am a having a very urgent issue regarding the global customer, we are
using RMS with HQ, we have 6 stores and all the stores are connected to HQ in
all the stores customers is defined as global customers , the problem started
after i have merged the customer accounts, Below is the query i used

1) DECLARE @CustomerKeep INT

DECLARE @CustomerDelete INT

SET @CustomerKeep = (SELECT ID FROM Customer WHERE AccountNumber = 'X')

SET @CustomerDelete = (SELECT ID FROM Customer WHERE AccountNumber = 'Y')

UPDATE AccountReceivable
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE [Transaction]
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE Journal
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE [Order]
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE Payment
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE ShipTo
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete

UPDATE TransactionHold
SET CustomerID = @CustomerKeep
WHERE CustomerID = @CustomerDelete
2)CREATE VIEW ARTemp AS SELECT CustomerID, SUM(Balance) AS Balance
FROM AccountReceivable
LEFT JOIN Customer ON AccountReceivable.CustomerID = Customer.ID
WHERE Customer.AccountNumber = 'X'
GROUP BY CustomerID

3) UPDATE Customer
SET AccountBalance = ARTemp.Balance
FROM ARTemp
INNER JOIN customer ON ARTemp.CustomerID = Customer.ID

4) Drop VIEW ARTemp

5) DECLARE @CustomerKeep INT

SET @CustomerKeep = (SELECT ID FROM Customer WHERE AccountNumber = 'X')


UPDATE Customer SET Customer.TotalSales = A.TotalSales, Customer.TotalVisits
= A.TotalVisits
FROM Customer
LEFT JOIN (SELECT CustomerID, COUNT([Transaction].TransactionNumber) AS
TotalVisits, SUM(Total) AS TotalSales
FROM [Transaction]
GROUP BY CustomerID) A ON Customer.ID = A.CustomerID
WHERE Customer.ID = @CustomerKeep

UPDATE Customer
SET Customer.TotalSavings = A.TotalSavings
FROM Customer
LEFT JOIN (SELECT CustomerID, SUM((FullPrice - Price)*Quantity) AS
TotalSavings
FROM TransactionEntry
LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber
GROUP BY CustomerID) A ON Customer.ID = A.CustomerID
WHERE Customer.ID = @CustomerKeep

UPDATE Customer
SET Customer.LastVisit = [Transaction].Time
FROM Customer
LEFT JOIN [Transaction] ON Customer.ID = [Transaction].CustomerID
WHERE [Transaction].Time IN (select max([Transaction].Time)
FROM [Transaction]
GROUP BY CustomerID) AND Customer.ID = @CustomerKeep


After the merging of the accounts the problem we encountered was that from
the stores when we want to receive payment some invoices are not showing,

Please Help me on this issue its really urgent

Regards
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Global Customer ( Very Urgent ) 1
Global Customers 1
Global Customers 3
Global Customers Question 3
Delete Global Customers 1
Global Customer Edit 0
Global customer import 7
Delete Global Customers 1

Top