Open Forum

Expand all | Collapse all

Received Not Invoiced Full GL Line Item Distributions

  • 1.  Received Not Invoiced Full GL Line Item Distributions

    Posted 3 days ago
    Hi,

    Does anyone have a SQL view or report that shows Received Not Invoiced with full line item GL distribution accounts?

    Thanks.

    Jeff

    ------------------------------
    Jeffrey Johnson
    System Administrator
    Gillette Children's Specialty Healthcare
    Saint Paul MN
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Received Not Invoiced Full GL Line Item Distributions

    GPUG ALL STAR
    Posted 3 days ago
    Hi Jeff,
    Funny you're asking this, as I just had a customer having some troubles running this report.
    This might help : https://community.dynamics.com/gp/b/dynamicsgp/posts/received-not-invoice-report-is-not-tying-to-the-accrued-purchases-account-on-the-general-ledger-trial-balance
    This could help to start:
    select * from POP10500 where QTYSHPPD > QTYMATCH and POPTYPE = 1

    Besides of that, the built-in SQL view "ReceivingsLineItems" should be able to provide a fair amount of data, you would just have to link it to the Accounts view..

    Something like this : 

    SELECT ReceivingsLineItems.[POP Receipt Number] ,
    ReceivingsLineItems.[PO Number] ,
    ReceivingsLineItems.[POP Type] ,
    ReceivingsLineItems.[Item Number] ,
    ReceivingsLineItems.[Item Description] ,
    ReceivingsLineItems.[QTY Shipped] ,
    ReceivingsLineItems.[QTY Invoiced] ,
    ReceivingsLineItems.[Unit Cost] ,
    ReceivingsLineItems.[Extended Cost] ,
    ReceivingsLineItems.[Vendor ID] ,
    ReceivingsLineItems.[Vendor Name] ,
    ReceivingsLineItems.[Created Date] ,
    Accounts.[Account Number] ,
    Accounts.[Account Description] ,
    ReceivingsLineItems.[Posted User ID]
    FROM
    ReceivingsLineItems INNER JOIN Accounts
    ON ReceivingsLineItems.[Inventory Index] = Accounts.[Account Index]
    WHERE ReceivingsLineItems.[PO Number] LIKE 'PO002020%'
    ORDER BY ReceivingsLineItems.[POP Receipt Number];



    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Received Not Invoiced Full GL Line Item Distributions

    TOP CONTRIBUTOR
    Posted 3 days ago
    As Beat says if you are trying to reconcile up to the RNI Report it can be a challenge.
    I might use the existing RNI smartlist view.  (which you can modify easily to add the IV Index as a full account number)

    Or create your own something like this:

    SELECT *
    FROM [TWO].[dbo].[slbReceivingLines]
    where [QTYSHPPD] > [QTYMATCH] and [POPTYPE] <> 2

    The reason I would use POPTYPE <> 2 is to capture open Returns which are deducts from the RNI as well as the Invoices. Even Shipment/Invoice types can have a Receipt QTY larger than the Invoiced QTY.

    As Beat said you will need to join the accounts if you don't do it in slb and use smartlist.
    and you will need to recalculate the Extended Cost as the value in the View will be for the entire line QTY. Not the extended value of the remaining Quantity.
    Watch for Returns as deducts.

    ------------------------------
    Thaddeus Suter
    Retus, Inc
    HELOTES TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


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