Open Forum

Expand all | Collapse all

Duplicate Invoices

  • 1.  Duplicate Invoices

    SILVER CONTRIBUTOR
    Posted 29 days ago
    I'm doing an audit and I'm looking for the best way to search for any duplicate invoices or payments.  What tables would be best to use?

    ------------------------------
    Diane Huffman
    Internal Auditor
    Wyandotte Tribe of Oklahoma
    Wyandotte OK
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Duplicate Invoices

    GOLD CONTRIBUTOR
    Posted 29 days ago
    Hi Diane,

    Here are a couple of scripts for SOP and RM documents that you can review:

    SOP:
    SELECT [SOPTYPE],
    [SOPNUMBE],
    COUNT(*) AS COUNT
    FROM
    (
    SELECT [SOPTYPE],
    [SOPNUMBE]
    FROM [SOP10100] [W]
    UNION ALL
    SELECT [SOPTYPE],
    [SOPNUMBE]
    FROM [SOP30200] [H]
    ) [C]
    GROUP BY [SOPTYPE],
    [SOPNUMBE]
    HAVING COUNT(*) > 1;


    RM:
    SELECT [RMDTYPAL],
    [DOCNUMBR],
    COUNT(*) AS COUNT
    FROM
    (
    SELECT [RMDTYPAL],
    [RMDNUMWK] AS [DOCNUMBR]
    FROM [RM10301] [W]
    UNION ALL
    SELECT [RMDTYPAL],
    [DOCNUMBR]
    FROM [RM10201] [W]
    UNION ALL
    SELECT [RMDTYPAL],
    [DOCNUMBR]
    FROM [RM20101] [O]
    UNION ALL
    SELECT [RMDTYPAL],
    [DOCNUMBR]
    FROM [RM30101] [H]
    ) [C]
    GROUP BY [RMDTYPAL],
    [DOCNUMBR]
    HAVING COUNT(*) > 1;

    ------------------------------
    John Kirsch
    GP Product Lead
    Dynamic Consulting
    Green Bay WI
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Duplicate Invoices

    SILVER CONTRIBUTOR
    Posted 29 days ago

    Thank you John,  I pulled those tables and it appears we don't use them as there are no files in them.  I had some luck with the PM tables.

     




    Academy - Online Interactive Learning from Experts


  • 4.  RE: Duplicate Invoices

    TOP CONTRIBUTOR
    Posted 29 days ago
    For Payables, you can use this query:

    SELECT [Vendor ID], [Document Number]
    FROM PayablesTransactions
    GROUP BY [Document Number], [Vendor ID]
    HAVING COUNT([Document Number]) > 1 and COUNT([Vendor ID])>1
    ORDER BY [Vendor ID], [Document Number]


    ------------------------------
    Charles Allen
    Senior Managing Consultant
    BKD Technologies
    Houston, TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Duplicate Invoices

    SILVER CONTRIBUTOR
    Posted 29 days ago

    Thank you Charles, this is very helpful.  I found quite a few transactions to test.

     




    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role