web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Community site session details

Community site session details

Session Id :
Microsoft Dynamics RMS (Archived)

Duplicate entries in HQ PO

(0) ShareShare
ReportReport
Posted on by

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)
  • Suggested answer
    archelle16 Profile Picture
    1,743 on at

    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

  • Suggested answer
    archelle16 Profile Picture
    1,743 on at

    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.

  • Community Member Profile Picture
    on at

    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

  • Bee Profile Picture
    20 on at

    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?

  • Community Member Profile Picture
    on at

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

  • Community Member Profile Picture
    on at

    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 .

  • Spencer McCandless Profile Picture
    2,087 on at

    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?

  • Community Member Profile Picture
    on at

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

  • Spencer McCandless Profile Picture
    2,087 on at

    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans