I am trying to recreate the credit holds status report from Macola 7.7.3 in Order Entry / Reports-Graphs / Order Status / Orders on Credit Hold. I am having trouble recreating this report in Crystal I believe due to the inter-play between "header table(s) (e.g. "OEORDHDR_SQL", "OEHDRHIST_SQL") and line item / history tables (e.g. "OEORDLIN_SQL", "OELINHIST_SQL"). As stated, I want to recreate the Macola report in Crystal because it is garbled and terrible format. I need all orders with current status of "C" (credit holds). From what I can tell, the only tables with "status" as a field type with a "string of one character" are the OEORDHDR_SQL and OEHDRHST_SQL tables. The problem I am having is what tables to pull the other information from because I am not getting the result that ties to the Macola report as of a point in time. Any help here would be greatly appreciated. I have attached a couple of snips of the crystal report fields and tables

Regards,

Jeff Miesbauer

If the oeordhdr_sql.status field has a value of "H", the order is on credit hold. The oehdrhst_sql and oelinhst_sql should not be included in this report if all you want is orders on credit hold. These tables contain data about orders that have already been invoiced AND posted, so by definition there were not on credit hold.

This statement puzzles me "I am not getting the result that ties to the Macola report as of a point in time".

You will not be able to run a report that shows the orders on credit hold as of say, 12/31/15 or any arbitrary time in the past, at least not easily. Nor is this what the canned report does. Is this what you are attempting to do?

Also, how are you linking the oeordhdr_sql and oeordlin_sql tables?

Thank you for the courtesy of your response. I have not used Crystal Reports in over 10 years and am relearning to become an intermediate user as a goal.

First off, according to our Macola version 7.7.300, credit holds are a status of "C" not "H" (see attachment). Status "H" is cancelled order according to this so I am confused with your response on that.

Listed below are the fields we want on the report to get credit holds and I did mean only getting credit holds as of "now" not a retroactive point in time. If I should NOT use tables OEHDRHST_SQL and OELINHST_SQL tables, please help me with what tables I should use if I want the following fields
1> ord_type
2> ord_no
3> status (issue with "H" vs "C" needs clarification)
4> ord_dt
5> entered_dt
6> ship_to_name
7> shipping_dt
8> slspsn_name
9> prod_cat_desc
10> item_desc_1
11> qty_ordered
12> sls_amt (or ordered amount?)

My thoughts / questions:
- Should items 1> - 7> above come from OEORDHDR_SQL table?
- What is relevance of using OEORDLIN_SQL table?; the only fields from list above that are relevant to my objective are "ord_type" and "ord_no".
- I apparently need to incorporate the ARSLMFIL_SQL because item 8> above is needed for this report
- Also need table IMCATFIL_SQL for 9> above as we want the product information on this report
- Regarding the sales / order amount, I am a bit confused about this because I can only get sls_amt off the OELINHST_SQL table which you are indicating not to use. What field/table gets me the order amount on credit hold?

I hope this provides more clarity to my objective and I really appreciate your technical assistance as I am quickly learning how to navigate through Macola tables and fields. As I mentioned, the credit hold report in Macola is a garbled mess and I want to make a better one.

Regards,

Jeff