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

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



    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

    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 :
    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]
    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
    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

    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

    Academy - Online Interactive Learning from Experts

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