Share
ReportIn the PO of the HQ I found that each entry is duplicated 5 times , though it is ok in the SO . Does anybody know why this happened and how to fix it .
*This post is locked for comments
You’re offline. This is a read only version of the page.
In the PO of the HQ I found that each entry is duplicated 5 times , though it is ok in the SO . Does anybody know why this happened and how to fix it .
*This post is locked for comments
To fix this, run the following stored procedure below:
/****** Object: StoredProcedure [dbo].[CheckUploadedDeliveries] Script Date: 08/15/2014 17:16:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Archelle pagapulan
-- Create date: July 4, 2014
-- Description: Delete/Corrects multiple uploaded deliveries
-- =============================================
CREATE PROCEDURE [dbo].[CheckUploadedDeliveries]
AS
BEGIN
SELECT
store.StoreCode, CASE(tr.POType) WHEN 1 THEN cast(tr.WorksheetID as varchar) ELSE 'N/A' END as ReferenceWorksheet,
case(tr.POType) WHEN 1 then 'MO - PurchaseOrder IN' WHEN 2 THEN 'Store - InventoryTransfer IN' WHEN 3 THEN 'Store - Inventory Transfer OUT'
WHEN 4 THEN 'Store - PurchaseOrder IN' END as POType,
tr.PONumber
,case(refStore.StoreCode) when null then 'N/A' ELSE isnull(refStore.StoreCode, 'N/A') end as ReferenceStore, tr.OtherPOID as ReferenceID,
tr.myCount as TotalCount,
'delete from PurchaseOrderEntry where PurchaseOrderID IN(select ID from PurchaseOrder where StoreID = ' + cast(tr.storeid as varchar) + '
and ponumber = ''' + tr.ponumber + '''
and potype = ' + cast(tr.potype as varchar) + ' and WorksheetID = ' + cast(tr.WorksheetID as varchar) + ' and OtherPOID = ' + cast(tr.OtherPOID as varchar) +
' and OtherStoreID = ' + CAST(tr.OtherStoreID AS varchar) + ' and ID
!= (select max(ID) FROM PurchaseOrder where StoreID = ' + cast(tr.storeid as varchar) + ' and ponumber = ''' + tr.ponumber + '''
and potype = ' + cast(tr.potype as varchar) + ' and WorksheetID = ' + cast(tr.WorksheetID as varchar) + ' and OtherPOID = ' + cast(tr.OtherPOID as varchar) +
' and OtherStoreID = ' + CAST(tr.OtherStoreID AS varchar) + ')) AND StoreID = ' + cast(tr.storeid as varchar)
AS SamePOEntryFirst,
'delete from PurchaseOrder where StoreID = ' + cast(tr.storeid as varchar) + ' and ponumber = ''' + tr.ponumber + '''
and potype = ' + cast(tr.potype as varchar) + ' and WorksheetID = ' + cast(tr.WorksheetID as varchar) + ' and OtherPOID = ' + cast(tr.OtherPOID as varchar) +
' and OtherStoreID = ' + CAST(tr.OtherStoreID AS varchar) + ' and ID != (select max(ID) FROM PurchaseOrder
where StoreID = ' + cast(tr.storeid as varchar) + ' and ponumber = ''' + tr.ponumber + '''
and potype = ' + cast(tr.potype as varchar) + ' and WorksheetID = ' + cast(tr.WorksheetID as varchar) + ' and OtherPOID = ' + cast(tr.OtherPOID as varchar) +
' and OtherStoreID = ' + CAST(tr.OtherStoreID AS varchar) + ')' AS deleteSamePONext,
'select * from PurchaseOrder where StoreID = ' + cast(tr.storeid as varchar) + ' and ponumber = ''' + tr.ponumber + '''
and potype = ' + cast(tr.potype as varchar) + ' and WorksheetID = ' + cast(tr.WorksheetID as varchar) + ' and OtherPOID = ' + cast(tr.OtherPOID as varchar) +
' and OtherStoreID = ' + CAST(tr.OtherStoreID AS varchar) as 'ViewDeliveries'
from (
select storeid, ponumber, potype, WorksheetID, OtherPOID, OtherStoreID, count(*) as myCount
from PurchaseOrder
group by storeid, ponumber, potype, WorksheetID,OtherPOID, OtherStoreID
having count(*) > 1 ) as tr left join store on store.ID = tr.StoreID
left join Store refStore on refStore.ID = tr.OtherStoreID
END
GO
Execute the procedure. Ex
DECLARE @return_value int
EXEC @return_value = [dbo].[CheckUploadedDeliveries]
SELECT 'Return Value' = @return_value
GO
Ok so let me discuss to you what happened.
IN HQ
First, HQ created new items in store. Without downloading the items to affected stores, they created PurchaseOrders through PO planner (worksheet 340). After creating PurchaseOrders, they also created request data upload (worksheet 401) to upload store data.
The store
When store tries to synchronized, first the Worksheet 340 (order by worksheet style thats y 340 first before 401). PurchaseOrder entries were not found in store because this are all new items therefore, po processing was skipped but unfortunately, when you query the po, purchaseOrder were literally created but no entries. Next, request data upload is processing. The inserted PurchaseOrder with no entries were uploaded to hq. Thus create a new purchaseorder for the store.
In HQ
Merchandisers detect that purchaseorders created were skipped through viewing worksheet history, then issued Worksheet itemupdate (250) to download/update new items to stores.
In store
When store tries to synchronize again, it downloads first the items. Then process the purchaseorders created. Since items were already exist in store, it downloads the purchaseorders with its entries.
In HQ.
Creates worksheet 401 again.
when sync again,
It uploads all store data consisting new downloaded POs thus create double entry for po being inserted.
So to answer ur question in short, Download all the items first before creating purchaseorders to avoid duplicate entries in HQ.
Hi , actually i had somebody fix it using SQL query , if you are interested i could give you his email and you contact him . My mail is wkholi@gmail.com
Hello.. i am also having the same issue, if you could help, it would be grateful. Detailed sales report in HQ for a particular store has duplicate entries. But in SO the entries are fine. There has not been any restore of late. The only change is we added a new store to HQ.
Could you please help with this as it is tripling the sales figure?
I made only few pos , 2 or 3 and it is in both
Sorry for the delay , I was away for the past couple of weeks .
yes I see each item duplicated 5 times in the PO but the data in the reports is correct , and lately I have found it in an interstore transfer where it was repeated twice .
It would be easy enough to blow away the duplicate entries from the database using a sql statement, but I'm not sure why they're being uploaded in the first place, so it likely wouldn't fix the problem for future POs. Does the issue occur in every new PO you make? If you open Headquarters Administrator and run
select * from purchaseorderentry where purchaseorderid = (select MAX(id) from purchaseorder where potype = '1')
do you see each entry appearing 5 times?
- i might have made a restore before , I am not sure , but I am sure that I have not restored 4 times as I have only in HQ each item duplicated 4 times .
- Detailed sales Sales reports are fine .
Do you know of a fix for that ?
Have you restored from a backup in the recent past? Also, are all of the items strictly duplicates, or do you have any seemingly random items added to the PO as well? Finally, do you see any similar behavior in your detailed sales reports, with either duplicate sales appearing or items showing up on transactions they weren't sold on?
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,432
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
