Anyone know the logic for Querying Shipped not billed with SQL in BC?
What are the tables/fields I should be checking for Shipped not billed?
Please advise.
Anyone know where I can find these fields - in page inspection it says Sales Header but they are not in the table
Shipped Not Invoiced (5751, Boolean)
Completely Shipped (5752, Boolean)
Those are FlowFields. They exist in the BC Table object definition but not in the underlying SQL table. They are a calculated value. In the case of these 2 fields, the calculation is on related Sales Line records. To get those field values in SQL you would need to perform those calculations.
I want to share what I have found after looking at the codes:
There are two fields to check in Sales Line
-
Qty_ Shipped Not Invoiced <> 0
-
Outstanding Quantity = 0
That would only give you lines that were completely shipped but not invoiced. if you also want to see lines that were not completely shipped, then drop the second field.
The simplest place to start is to create a trace on the SQL server. Then run the page that shows the flow field you are chasing. Then look at the TSQL in the trace and you will find the subquery (APPLY) that you need.