Bookings

I am attempting to create a bookings query based on BookOrd and BookDtl. I have only one record for this year, but several for years past. Am I missing something?

select
[BookOrd].[Company] as [BookOrd_Company],
[BookOrd].[CustNum] as [BookOrd_CustNum],
[BookOrd].[PONum] as [BookOrd_PONum],
[BookDtl].[OrderNum] as [BookDtl_OrderNum],
[BookDtl].[OrderLine] as [BookDtl_OrderLine],
[BookDtl].[BookDate] as [BookDtl_BookDate],
[BookDtl].[RecType] as [BookDtl_RecType],
[BookDtl].[BookType] as [BookDtl_BookType],
[BookDtl].[ProdCode] as [BookDtl_ProdCode],
[BookDtl].[BookValue] as [BookDtl_BookValue]
from Erp.BookOrd as BookOrd
inner join Erp.BookDtl as BookDtl on
BookOrd.Company = BookDtl.Company
and BookOrd.OrderNum = BookDtl.OrderNum
where BookDtl.BookDate >= @StartDate and BookDtl.BookDate <= @EndDate

did someone turn off the flag in the company settings that says that you want to track bookings? If that flag is off, then nothing gets captured in the bookings table.

Dang - yes it was unchecked. I don’t suppose there is a way to update the bookings to date???

Nope… i don’t believe that there is. It captures the changes as they happen. sorry… :frowning:

So I think when you turn the check box on, if you make any change to a previously entered order it will make a bookings entry for the first time as if it was new (found this out the hard way). So, when you turn on the check box I think you will want to trigger this on all of your orders that will have any activity in the future or it may skew your bookings entries, e.g., you check the box in company config today, the price on an order changed from 100,000 to 100,001 tomorrow and it shows a new bookings entry as of tomorrow for 100,001.

Then you have the timing issue as all these dates will be on the same day. We sometimes need to update the date anyways to backdate bookings when we have a delay in processing. We added a UD field which defaults to the booking date but then we can update it if we need to backdate. We use this field to report on. To make it update-able, we had to run concurrent records on a UD table and then do an update-able dashboard on that UD Table, which kicks the change back to bookdtl since you can’t do an update-able BAQ on bookdtl.

It is pretty messy, but it works. The biggest challenge for you would be triggering the bookdtl record on orders that are closed if you need them. For open stuff, you could just drop the price a penny the bring it back up.

Thanks. For now, I can use the orders table because we don’t have many change orders, but at least from May forward, I should be able to use the bookings tables.

I would still be careful about the first booking entry on orders already created though. If you have a huge order entered before May, then have a change order for $100 in June, that will get booked at the entire order amount rather than just the change amount since no previous bookings entry exists. So your bookings entries will be pretty unreliable until your current backlog gets processed. Not sure how quick that is, but if your orders linger for a while then it may be an issue.

Got it. Thanks.

Heed Mike’s warnings. The BookDtl table is a wild beast and does things you might not expect:

  • BookDate: Like Mike said, it’s always the System Date so no back-dating.
  • Ready To Process: The booking records are not written until this is checked.
  • Shipments: When you make a change, the system tries to be intelligent with respect to shipments. If I have ten items at $10/each and half of them ship 5. Now you reduce the price for the remaining 5. The system calculates the remaining books, subtracts it and then adds back in the new booking value with the remaining quantity. One problem. It does this on all lines, including shipped lines. zero * Price and boom! - a shipped line gets completely debooked.

It would really be nice to be able to add adjustment records into the table (BookTyp = ‘ADJ’ to add to New, Del, and Chg). This way, you could move the date or fix unwanted debookings.

Our work-around is we use a UD table to add adjustments and have a BAQ that does a UNION with the BookDtl and our adjustment table and use that for any booking reporting.

Mark W.

I MAY have spoken too soon… just accidentally found two programs that APPEAR (from the help file) to do what you want.
PLEASE do not run these in your production environment UNTIL you have verified that they do what you want!!!

The two programs are called “Fix Book Release records” and “Fix Book Detail records”.
AGAIN… TEST and make sure BEFORE running this in live! Even Better, you might contact Technical Support to find out if this is recommended.

Thanks - I’ll take a look in my test environment first.

I tried in my test environment and it did not work as advertised. No new entries for recent years. Oddly, the interface has no filtering options - just run it. I’ll report back after I speak with support.

I found this in the instructions - only applies to SaaS environment and support confirmed this.

Use Fix Book Release Records to run the rebuild process to fix and update the Book records for the release of a sales order for the current company within a multi-tenant SaaS environment.

I saw that in the help too… but I thought (hoped) that it would not matter if you were onsite. I have put a query out Internally at Epicor to see if anyone knows why.

We are having this same problem.

I need to direct someone who has permissions to the EXACT location of this little check box and I do not know where to tell them to go. :roll_eyes:

Where, please, is the check box in the system settings to fix this problem?

Please and thank you!!!

Company Configuration/Modules/Sales/Order - Book Sales Orders Checkbox

1 Like

you rock!