D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Shipped no invoiced

  • 1.  Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted Jun 04, 2019 11:26 AM
    Hi All,
    Can you please let me know which tables or view can be used to create this report , Shipped not invoiced under account receivables, I know we have it under the interface but wanted to create a power BI report

    ------------------------------
    Ray Soliman
    ERP Manager
    Argus Machine Co. Ltd.
    Edmonton AB
    ------------------------------


  • 2.  RE: Shipped no invoiced

    TOP CONTRIBUTOR
    Posted Jun 04, 2019 12:05 PM
    Hi Ray,
    Try InventTrans. This will have the sales order line information. The issue column shows the SO line status. Issue status of "Deducted" is a quantity this is shipped but not invoiced. Issue status = "Sold" means it has been invoiced.

    Thanks
    Drew

    ------------------------------
    Andrew Lencsak
    Solution Architect
    DXC Technology
    Atlanta GA
    ------------------------------



  • 3.  RE: Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted Jun 04, 2019 11:20 PM
    Hi Andrew,
    There are no issue status column on this table and how to select only sales orders as this is all inventory item transactions


     





  • 4.  RE: Shipped no invoiced

    TOP CONTRIBUTOR
    Posted Jun 05, 2019 03:08 AM
    Hi Ray,

    The field name is StatusIssue (InventTrans.StatusIssue). To select only transactions related to Sales orders, you need to use table InventTransOrigin. There is a complicated explanation about InventTransOrigin here, but the relationship can be described thus:

    SELECT INVENTTRANSORIGIN.REFERENCECATEGORY, INVENTTRANS.ITEMID, INVENTTRANS.QTY
    FROM INVENTTRANS
    INNER JOIN INVENTTRANSORIGIN ON INVENTTRANS.INVENTTRANSORIGIN = INVENTTRANSORIGIN.RECID
    AND INVENTTRANS.PARTITION = INVENTTRANSORIGIN.PARTITION AND INVENTTRANS.DATAAREAID = INVENTTRANSORIGIN.DATAAREAID

    The ReferenceCategory field tells you what the transaction is linked to. ReferenceCategory = 1 means it is linked to a Sales order.

    ------------------------------
    Guy Terry
    Application Consultant
    Annata
    Portsmouth
    ------------------------------



  • 5.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted Jun 05, 2019 12:00 PM
    ​I like the approaches suggested so far, but an alternate approach would be to open: Sales and marketing > Inquiries > Order status > Open sales order lines.  Add the field, "Invoice remainder (RemainInventFinancial" from the order lines table to the grid.  You can then filter the grid by the invoice remainder quantity being not equal to zero.  RemainInventFinancial is the quantity on the sales order line in the inventory unit of measure that has been shipped, but not invoiced, so this filter gives you exactly the sales order lines you want.  You can export the grid to Excel if you want a report.  This may cover your requirements without writing any code, if the requirements are not very complicated.

    ------------------------------
    Kevin McLean
    Strategic Solutions NW, LLC
    Beaverton OR
    ------------------------------



  • 6.  RE: Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted Jun 05, 2019 12:04 PM
    Hi Kevin,
    I am looking to connect to this view from power BI  to show same fields as the SSRS report do you know 
    Which table and columns I would use





  • 7.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted Jun 05, 2019 12:09 PM
    SalesLine.RemainInventFinancial is shipped and not invoiced in the inventory unit of measure.
    SalesLine.RemainSalesFinancial is shipped and not invoiced in the sales order unit of measure.​

    ------------------------------
    Kevin McLean
    Strategic Solutions NW, LLC
    Beaverton OR
    ------------------------------



  • 8.  RE: Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted Jun 05, 2019 02:15 PM

    Thanks Kevin for the reply, the issue that we are having now is that the line total column is on the originating currency ( we have CAD and US) so the total of the excel sheet match the ssrs report

     

    Argus Machine

    Ray Soliman

    |

    Manager ERP

    Tel: 1 (780) 434-9451x1021

    Email: ray.soliman@argusmachine.com

    |

    Web: www.argusmachine.com

     






  • 9.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted Jun 06, 2019 01:15 PM
    ​Sorry - I can't help much with that.  Is it possible to put in an exchange rate for 1 USD = 1 USD, and then always join the exchange rate table for the report?  I think AX will tell you that you don't need an exchange rate for a currency to itself, but I do not know if the existence of that exchange rate causes problems.

    ------------------------------
    Kevin McLean
    Strategic Solutions NW, LLC
    Beaverton OR
    ------------------------------



  • 10.  RE: Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted Jun 07, 2019 04:31 PM
    Hi,
    The report look good but now the issue the exchange rate so basically we need the exchange rate and originated currency and function currency.


     





  • 11.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted Jun 08, 2019 10:27 AM
    Ray
    do you own Atlas?  If so I have created this report in Atlas, if not I would encourage you to look at acquiring it - it is a live interface to AX through Excel and does not need any mapping unlike BI.  It also uses AX security. (D365).  All of my clients use this.
    www.insightsoftware.com


    ------------------------------
    Deb Salyer
    DS Erp Inc
    Sarasota FL
    941-351-2990
    deb@dserpinc.com
    ------------------------------



  • 12.  RE: Shipped no invoiced

    TOP CONTRIBUTOR
    Posted Jun 06, 2019 02:25 PM
    Hi Ray,
    If you are on D365FO I would look into configuring reporting currency for this so you can manage both the ledger currency and reporting currency for the client then expose your BI report to this:
    https://community.dynamics.com/ax/b/dynamics365tour/archive/2018/11/15/managing-the-reporting-currency

    ------------------------------
    Andrew Lencsak
    Solution Architect
    DXC Technology
    Atlanta GA
    ------------------------------



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