7.6.300 Booking Report

[generico]

Has any written a bookings report against the so called "new" bookings table? If so what tables did you use? I am having no luck. Thanks

 

[dgillz]

Is there anything wrong with the bookings report that exists in 7.6.300?

Also, what "new" table are you talking about?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[generico]

Dgillz,

Thanks for the response, the problem I am having I have limited information. I would like to have customer PO #, item description and have it roll up to an individual sales person. I thought new tables were generated by initalizing the bookings tables.

 

[crystalreporting]

You are correct - new 'bookings' tables were introduced in 7.6.300. The big question is... Pervasive or SQL? If SQL, you can get to the tables, if Pervasive, Exact forgot to include them in the DDF's. This has been logged as a bug.. (and covered in an earlier post here).

Peter Shirley

http://www.linkcorp.com

 

[generico]

CR,

I am using SQL. Thanks

 

[PhilPPhillips]

Generico, I haven't upgraded to 7.6.300 yet (still on 7.6.100a), so I don't know the limitations, if any, that the booking tables possess. I was flabbergasted when I found out Macola had no orders reports or even a reasonable way to get that information. So I created our order reports (or bookings as Macola calls them) years ago using a view I created that links (by a union query) the open and shipped orders from the various tables (oeordhdr, oeordlin, oehdrhst, oelinhst). I've used this view for all of my "booking" reports, some that are very complicated. Here is the view I created if you want to try it out. You may have to add fields as necessary.

CREATE VIEW dbo.orders
AS
SELECT OEORDLIN_SQL.[ord_no] ORDERNO,
OEORDLIN_SQL.[item_no] ITEM_NO,
OEORDLIN_SQL.[item_desc_1] DESCRIPTION_1,
OEORDLIN_SQL.[qty_ordered] QTY_ORDERED,
OEORDLIN_SQL.[unit_price] UNIT_PRICE,
OEORDLIN_SQL.[unit_cost] UNIT_COST,
OEORDHDR_SQL.[bill_to_name] BILL_TO_NAME,
OEORDLIN_SQL.[prod_cat] ITEM_PROD_CAT,
OEORDHDR_SQL.[ord_dt] ORDER_DATE,
OEORDHDR_SQL.[slspsn_no] SALESMAN_NO_1,
OEORDLIN_SQL.[request_dt] REQUEST_DATE,
OEORDHDR_SQL.[cus_no] ORDER_CUSTOMER_NO,
OEORDHDR_SQL.[ord_type] ORDER_TYPE,
OEORDLIN_SQL.[line_seq_no] SEQUENCE_NO,
ARCUSFIL_SQL.[cus_type_cd] CUS_TP,
OEORDLIN_SQL.[ord_no] ORDER_NO,
OEORDLIN_SQL.[discount_pct] DISCOUNT_PCT,
ARCUSFIL_SQL.[state] STATE,
OEORDLIN_SQL.[vend_no] VEND_NO,
OEORDHDR_SQL.[bill_to_addr_3] BILL_TO_ADDR_3,
OEORDHDR_SQL.[mfg_loc] mfg_loc
FROM { oj ([data_42].[dbo].[OEORDHDR_SQL] OEORDHDR_SQL INNER
JOIN
[data_42].[dbo].[OEORDLIN_SQL] OEORDLIN_SQL ON
OEORDHDR_SQL.[ord_type] = OEORDLIN_SQL.[ord_type] AND
OEORDHDR_SQL.[ord_no] = OEORDLIN_SQL.[ord_no])
INNER JOIN
[data_42].[dbo].[ARCUSFIL_SQL] ARCUSFIL_SQL ON
OEORDHDR_SQL.[cus_no] = ARCUSFIL_SQL.[cus_no] }
UNION
SELECT OELINHST_SQL.[ord_no], OELINHST_SQL.[item_no],
OELINHST_SQL.[item_desc_1],
OELINHST_SQL.[qty_ordered] - OELINHST_SQL.[qty_bkord],
OELINHST_SQL.[unit_price], OELINHST_SQL.[unit_cost],
OEHDRHST_SQL.[bill_to_name], OELINHST_SQL.[prod_cat],
OEHDRHST_SQL.[ord_dt], OEHDRHST_SQL.[slspsn_no],
OELINHST_SQL.[request_dt], OEHDRHST_SQL.[cus_no],
OEHDRHST_SQL.[orig_ord_type],
OELINHST_SQL.[line_seq_no], ARCUSFIL_SQL.[cus_type_cd],
OEHDRHST_SQL.[inv_no], OELINHST_SQL.[discount_pct],
ARCUSFIL_SQL.[state], OELINHST_SQL.[vend_no],
OEHDRHST_SQL.[bill_to_addr_3],
OEHDRHST_SQL.[mfg_loc]
FROM { oj ([data_42].[dbo].[OEHDRHST_SQL] OEHDRHST_SQL INNER
JOIN
[data_42].[dbo].[OELINHST_SQL] OELINHST_SQL ON
OEHDRHST_SQL.[ord_type] = OELINHST_SQL.[ord_type] AND
OEHDRHST_SQL.[ord_no] = OELINHST_SQL.[ord_no] AND
OEHDRHST_SQL.[inv_no] = OELINHST_SQL.[inv_no])
INNER JOIN
[data_42].[dbo].[ARCUSFIL_SQL] ARCUSFIL_SQL ON
OEHDRHST_SQL.[cus_no] = ARCUSFIL_SQL.[cus_no] }

 

[generico]

Phil,

This looks great. I will try it out and let you know now. thanks a lot!!!

 

[MacolaHelp]

Exact NJ says the new ddfs will be included in 300 service pack b. Their tables aren't exactly like the peak tables from whence came the new bookings reports, power inquiry view, etc. If the view above works for SQL, it should also work in the pervasive database manager if you change the syntax to pervasive's flavor.

For my btrieve sites that use bookings, I too, needed to do some fancy footwork and formulas to link all the proper tables in crystal, but I have had a number of them running successfully for years.

Good luck!

 

[crystalreporting]

For anyone that still wants the table name it's OEPDSHDR.

Peter Shirley

http://www.linkcorp.com

 

[NEmacGuy]

One of the better booking reports I have seen / worked on was running off of the Order Header and Line Audit files.
Could break out new orders added, changed and deleted.
I would admit the files are a bit messy to work with, and they also tend to grow large but can generate good data.
Many clients have issue with bookings $ changing for prior dates when you add / change / delete lines on an order today.