Good Morning!
I am setting up a bookings BAQ to pull from the bookings tables. I see in details and releases there are dates, and they are significantly different. Can anyone explain the differences between the dates? If I want to filter for bookings in a date range, which do I use, the release or the detail table?
Thank you!
Nate
Detail refers to changes in the OrderLine
Release rfers to changes in the OrderRelease…
So both?
I think I get it. So I had to run a filter on the date range in detail or the date range in release.
Something like this?
select
[SubQuery1].[BookOrd_Company] as [BookOrd_Company],
[SubQuery1].[BookOrd_OrderNum] as [BookOrd_OrderNum],
[SubQuery1].[BookOrd_CustNum] as [BookOrd_CustNum],
[SubQuery1].[BookDtl_OrderLine] as [BookDtl_OrderLine],
[SubQuery1].[BookDtl_BookTime] as [BookDtl_BookTime],
[SubQuery1].[BookDtl_RecType] as [BookDtl_RecType],
[SubQuery1].[BookDtl_BookType] as [BookDtl_BookType],
[SubQuery1].[BookDtl_OurBookQty] as [BookDtl_OurBookQty],
[SubQuery1].[BookDtl_BookValue] as [BookDtl_BookValue],
[SubQuery1].[BookDtl_BookDate] as [BookDtl_BookDate],
[SubQuery1].[BookRel_OrderRelNum] as [BookRel_OrderRelNum],
[SubQuery1].[BookRel_BookDate] as [BookRel_BookDate]
from (select
[BookOrd].[Company] as [BookOrd_Company],
[BookOrd].[OrderNum] as [BookOrd_OrderNum],
[BookOrd].[CustNum] as [BookOrd_CustNum],
[BookDtl].[OrderLine] as [BookDtl_OrderLine],
[BookDtl].[BookTime] as [BookDtl_BookTime],
[BookDtl].[RecType] as [BookDtl_RecType],
[BookDtl].[BookType] as [BookDtl_BookType],
[BookDtl].[OurBookQty] as [BookDtl_OurBookQty],
[BookDtl].[BookValue] as [BookDtl_BookValue],
[BookDtl].[BookDate] as [BookDtl_BookDate],
[BookRel].[OrderRelNum] as [BookRel_OrderRelNum],
[BookRel].[BookDate] as [BookRel_BookDate]
from Erp.BookOrd as BookOrd
inner join Erp.BookDtl as BookDtl on
BookOrd.Company = BookDtl.Company
and BookOrd.OrderNum = BookDtl.OrderNum
left outer join Erp.BookRel as BookRel on
BookDtl.Company = BookRel.Company
and BookDtl.OrderNum = BookRel.OrderNum
and BookDtl.OrderLine = BookRel.OrderLine) as SubQuery1
where ((SubQuery1.BookDtl_BookDate >= @StartDate and SubQuery1.BookDtl_BookDate <= @EndDate ) or (SubQuery1.BookRel_BookDate >= @StartDate and SubQuery1.BookRel_BookDate <= @EndDate ))
Oh I see! Book Qty and Value are only at the line level. Do I even need the release table?
I don’t think so. Bookings are a value thing while releases are date and location thing. Changing a release many impact sales/revenue but that’s not bookings. The value of the sales order remains unchanged.
That makes things much more simple! Here is the bookings BAQ I am using to pull booking value totals for a range of dates. Does anyone see any obvious problems?
[BookOrd].[Company] as [BookOrd_Company],
[BookOrd].[OrderNum] as [BookOrd_OrderNum],
[BookOrd].[CustNum] as [BookOrd_CustNum],
[BookDtl].[OrderLine] as [BookDtl_OrderLine],
[BookDtl].[BookTime] as [BookDtl_BookTime],
[BookDtl].[RecType] as [BookDtl_RecType],
[BookDtl].[BookType] as [BookDtl_BookType],
[BookDtl].[OurBookQty] as [BookDtl_OurBookQty],
[BookDtl].[BookValue] as [BookDtl_BookValue],
[BookDtl].[BookDate] as [BookDtl_BookDate]
from Erp.BookOrd as BookOrd
inner join Erp.BookDtl as BookDtl on
BookOrd.Company = BookDtl.Company
and BookOrd.OrderNum = BookDtl.OrderNum
and ( BookDtl.BookDate >= @StartDate and BookDtl.BookDate <= @EndDate )
Looks good. Warning about the Bookings tables:
Records are written when one clicks Ready for Processing.
If shipments have occurred, at least in 10.2.500 and lower, the algorithm will subtract closed line items. Shipped items should still be considered as bookings. 
Reported this about four years ago.
Book date is always based on the sytstem date. Enter that sales order that arrived late on the last day of the month on the next day, that booking goes into the next month.
You cannot add, change, or delete these records - legitimately.
Wow