Which table contains Unprocessed sales of specific product

[sagharfrancis]

Hi,

I am searching for a table that contains the unprocessed sales information of the specific product.

I searched many tables of Inventory and Sales But didn't find any column named with unprocessed sales

[Mike.Sheen]

The underlying sales orders are stored in a table, SO_Main. These have a status of 0 for unprocessed orders. Lines of items (products) are stored in the SO_Lines table and are linked to a sales order via the SO_History table. SO_history links to SO_Main via the InvoiceID column, and SO_History links to SO_Lines via the InvoiceHistoryID column.

If you open the Inventory maintenance form, there are two places you can see unprocessed sales for a particular product:

1. The Order -> Unprocessed sales tab

This tab has it's data fed to it from the stored procedure usp_JIWA_Inventory_BuildGrid_OnUnprocessedSalesOrders - if you examine the queries in that stored procedure you'll see the root of the unprocessed sales information

2. The S.O.H. -> Warehouse Summary tab

This tab has it's data fed to it from the stored procedure usp_JIWA_Warehouse_SOH_Summary

You can identify which queries are used by using a profile trace whilst using the application. Often data is lazy loaded, so the data is not read until the tab is pressed - as is the case with the above two tabs - so you could start a profile trace, press the tab and then in the profile trace it will show the SQL query(s) issued.

[sagharfrancis]

Thanks for the reply.

How do I get these unprocessed items in code specific to that warehouse?

Do I need to query the SO_main, So_Lines, and history table or call the sp?

I run sp but it's returning XML. Is it possible to get data in tabular form without creating a new sp.

[Mike.Sheen]

Thanks for the reply.

How do I get these unprocessed items in code specific to that warehouse?

Do I need to query the SO_main, So_Lines, and history table or call the sp?

I run sp but it's returning XML. Is it possible to get data in tabular form without creating a new sp.

I directed you at the stored proc so you could look at the query it uses in there. Disregard the fact it's returning XML and look at the query.

You want to filter by SO_Main.IN_LogicalID to limit which warehouses to include.

To save you some time, here's a query that I think will satisfy your requirements:

Code: Select all

SELECT SO_Main.InvoiceID, SO_Main.InvoiceNo, SO_Main.InvoiceInitDate,
      SO_Lines.LineNum, SO_Lines.InventoryID, SO_Lines.PartNo, SO_Lines.Description, SO_Lines.QuantityOrdered, SO_Lines.QuantityThisDel, SO_Lines.ItemPrice, SO_Lines.CurrentLineTotal
FROM SO_Main
JOIN SO_History ON SO_History.InvoiceID = SO_History.InvoiceID AND SO_History.HistoryNo = SO_Main.CurrentHistoryNo
JOIN SO_Lines ON SO_Lines.InvoiceHistoryID = SO_History.InvoiceHistoryID
WHERE SO_Main.Status = 0
AND IN_LogicalID = 'ZZZZZZZZZ0000000000'
ORDER BY SO_Main.InvoiceNo, SO_Lines.LineNum