Unified Operations & Dynamics AX Forum

Expand all | Collapse all

Shipped no invoiced

  • 1.  Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted 21 days ago
    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
    ------------------------------
    AXUG Summit - Post


  • 2.  RE: Shipped no invoiced

    TOP CONTRIBUTOR
    Posted 21 days ago
    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
    ------------------------------

    AXUG Summit - Post


  • 3.  RE: Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted 20 days ago
    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


     



    AXUG Summit - Post


  • 4.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted 20 days ago
    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
    ------------------------------

    AXUG Summit - Post


  • 5.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted 20 days ago
    ​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
    ------------------------------

    AXUG Summit - Post


  • 6.  RE: Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted 20 days ago
    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



    AXUG Summit - Post


  • 7.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted 20 days ago
    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
    ------------------------------

    AXUG Summit - Post


  • 8.  RE: Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted 20 days ago

    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

     




    AXUG Summit - Post


  • 9.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted 19 days ago
    ​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
    ------------------------------

    AXUG Summit - Post


  • 10.  RE: Shipped no invoiced

    SILVER CONTRIBUTOR
    Posted 17 days ago
    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.


     



    AXUG Summit - Post


  • 11.  RE: Shipped no invoiced

    GOLD CONTRIBUTOR
    Posted 17 days ago
    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
    ------------------------------

    AXUG Summit - Post


  • 12.  RE: Shipped no invoiced

    TOP CONTRIBUTOR
    Posted 19 days ago
    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
    ------------------------------

    AXUG Summit - Post


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