Duplicate entries in HQ PO

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

archelle16 1,743 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

archelle16 1,743 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Bee 20 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I made only few pos , 2 or 3 and it is in both

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

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 .

Was this reply helpful? Yes No
Spencer McCandless 2,087 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

- 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 ?

Was this reply helpful? Yes No
Spencer McCandless 2,087 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No