Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Sign Up Now!Log In
lloyduh 10/28/2013 1:15:10 PM

How to calculate sales order demand for a range of dates.

I have a requirement to calculate the sales order quantity demand for a range of dates. I've run into scenarios where I'm doubling values because records are appearing in both SOP10200 and SOP30300 for the same order. Here's what I think will handle the requirement:

1) Demand from Sales Orders: when querying SOP10200 (SOPTYPE=2), the demand quantity should be QUANTITY - QTYPRINV. This will give me the total demand for orders that are open but not yet invoiced, or the invoice has not been posted.

2) Demand from unposted Invoices: when querying SOP10200 (SOPTYPE=3), the demand quantity should be QUANTITY. This will give me the total demand for orders that have printed but not posted invoices.

3) Demand from posted Invoices: finally, query SOP30300 (SOPTYPE=3) for orders that have been invoiced.

The only problem we have encountered is that we've seen orders (SOPTYPE=2) in SOP30300 without an invoice. How does a sales order get to history without a matching invoice?

Any suggestions or thoughts?

Thanks.

 

Version: All
Section: Dynamics GP, SQL Scripts








Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables