How to identify Open and Close Order in Navasion4

Dynamics 365 Business Central/NAV User Forum
1

Hi All

I hope you all are doing well on personal as well as professional front. You can say I am not even at the bigneer stage of Navision. Actually, we are building the Data Warehouse and we are pulling the data from NAVISION Site.

I need a logic for OPEN and CLOSE order. How to find out in NAVISION4 that perticular Order is OPEN or CLOSED. Our’s is manufacturing Domain.

Also, if possiable please provide the table’s in the NAVASION4 where order status infromation is stored.

I hope my question is making sense to you all…

Thanks

Saurabh Deshpande

2

A production order is set to status finished, a sales order is no longer in the sales table - where part shipments are involved a periodic deletion routine will need to be run.

3

Hi Adam

Thanks for quick reply. I naver seen the user interface of NAVISION also not having idea about the NAVISION routine.

what I can see is the NAVISION, SQL Server Database table on my screen. I am directly interacting with NAVISION Database (Not through interface). So I can write SQL query to get the data.

Now, I have to write the SQL statment to find the OPEN and CLOSED orders. So, my problem is which are tables I have to use in this query, what will be the condition I have to put there.

As per my knowledge following tables we have to use.

SALES HEADER, SALES LINE, SALES INVOICE HEADER, SALES INVOICE LINE.

Also, Is the logic for getiing the OPEN and CLOSE order is genric?, Or Will it vary from business to business and site to site?

Sorry, adam if I am asking too basic questions to you.

Thanks

4

Hi Saurabh,

NAV 4.0 standard has two status: Open and Released. But this can be customized to anything.

To look for Open Sales Order you need to go to Sales Header table and filter the document type to 1, this is for Order type in NAV and filter the status to 0.

Not all closed document will be deleted from Sales Header table. If the user does the posting (Ship and/or Invoice) from Sales Order screen in Navision, and the document is fully invoiced and fully shipped then the document will be deleted from the Sales Header Table. Because there is another way to post an Invoice in Navision, by creating an Sales Invoice and use Get Shipment Line function. If the user does this then the document will not be deleted from Sales Header. But once the shipment has been posted, the status will be Released. If this the case, you need to go deeper to the Sales Line. Check “Qty. Shipped (Base)” and “Qty. Invoiced (Base)” in Sales Line table and compare those two fields to “Quantity (Base)”. If ( “Qty. Shipped (Base)” = “Quantity (Base)” ) AND ( “Qty. Invoiced (Base)” = “Quantity (Base)” ) for all the lines corresponding to the header THEN Sales Order is Fully Invoiced and Fully Shipped.

Sales Invoice Header is where we put all posted invoices.

I hope it’ll help

Regards,

-TR-