Adding a field to OEORDLIN_SQL table 4

[tjay3001]

Will manually adding a field to the OEORDLIN_SQL table via enterprise manager cause any problems with Macola processes such as MRP or anything else that touches that table? There will not be any data in that field, just need that field name to be there in order to create a macola view table.

 

[dgillz]

Please explain what "create a Macola view table" means.

I don't know what you are trying to accomplish but there is no need to add another field to the table, there are already several user defined fields available.

I believe you should thoroughly exhaust every alternative before you change a Macola table. So please explain what you are trying to do and we will give you some alternatives.


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

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[tjay3001]

My ultimate goal is to be able to create a crystal report with all orders both open and shipped. To do this I was trying to create a "macola view" wich allows me to see both open and shipped data in one table. My problem is that in order to see a field in the view table, that field must be present in both actual tables. For example trying to link oeordlin and oelinhst I want to see inv_no so that I can properly link my header and line tables so that my crystal report doesn't show dups due to orders with multiple invoices. The problem with that is that the field inv_no does not exist in the oeordlin table. This is why I would like to add it if it is not going to create any problems.

 

[dgillz]

There is already a table that does this, it is the OEINQORD_SQL table. The field ord_fg is populated with an O for open, H for history or B for both, meaning the order has a backorder.

So query the order from here and based on the value of that flag, write formulas to get the qty ordered, shipped, etc from either the open or history order files.

Trust me, you do not want to modify the macola tables.

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

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[kscheeler]

Following is a stored procedure I use for Crystal in doing some Bookings Reports. A union all query would work too but I think this runs faster.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE usp_BookingsReport

@StartDt int,
@EndDt int

as

select h.ord_type,
h.bill_to_name,
h.job_no,
h.ord_no,
h.ord_dt,
h.oe_po_no,
h.cus_no,
h.slspsn_no,
h.inv_no,
h.inv_dt,
h.orig_ord_type
into #ordhdr
from oehdrhst_sql h
where h.ord_dt between @StartDt and @EndDt

insert into #ordhdr
select h.ord_type,
h.bill_to_name,
h.job_no,
h.ord_no,
h.ord_dt,
h.oe_po_no,
h.cus_no,
h.slspsn_no,
h.inv_no,
h.inv_dt,
h.ord_type as orig_ord_type
from oeordhdr_sql h
where h.ord_dt between @StartDt and @EndDt


select l.ord_type,
l.ord_no,
l.item_no,
l.loc,
l.request_dt,
l.qty_ordered,
l.qty_to_ship,
l.unit_price,
l.discount_pct,
l.uom,
l.uom_ratio,
l.line_no,
l.inv_no
into #ordlin
from oelinhst_sql l inner join #ordhdr h on
l.ord_type = h.ord_type and l.ord_no = h.ord_no and l.inv_no = h.inv_no

insert into #ordlin
select l.ord_type,
l.ord_no,
l.item_no,
l.loc,
l.request_dt,
l.qty_ordered,
l.qty_to_ship,
l.unit_price,
l.discount_pct,
l.uom,
l.uom_ratio,
l.line_no,
0 as inv_no
from oeordlin_sql l inner join #ordhdr h on
l.ord_type = h.ord_type and l.ord_no = h.ord_no
where h.inv_no = 0

select h.*, l.*
from #ordhdr h inner join #ordlin l on
h.ord_type = l.ord_type and h.ord_no = l.ord_no and h.inv_no = l.inv_no
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Kevin Scheeler

 

[crystalreporting]

A bookings report was added to 7.6.300 - the table that supplies this report is OEPDSHDR (Order/Hist Header Combined).

Peter Shirley

http://www.srhconsulting.com

 

[abaldwin]

Dont even THINK about adding a field. You will have so many problems you wont even be able to think straight.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[randva]

Kevin:

Thank you for posting that procedure. Do you by chance have a similar one for Btrieve?

Thank you,

 

[stravis]

Andy is right about adding a field. Very bad idea. To be more specific, some processes in Macola perform a "schema validation check" prior to processing. What this does is look at tables to determine the number of columns and sometimes it checks for existence of certain fields, if this information does not match the hard coded values that Macola thinks exist then the process will fail. Not all tables are checked in every process so don't even think that you can see if you can slip one by with a little testing. Never do the following with a Macola database.

1. Add or Remove Columns from a table
2. Change the name of a column, table, or index
3. Change the datatype or length of a column
4. Add a unique index to a table (non-unique indexes to speed queries are fine)
5. Remove or modify existing Macola indexes.
6. Use filler fields to store data (Any time an update is performed to Macola these fields could be potentially dropped along with all data contained in the field.)
7. Setup any form of MS SQL replication that adds fields to a table.
8. Use SQL Triggers to before business logic validation that cancels transactions. (this is a method used to perform validation of data being entered in to a database and preventing the entry of data if certain conditions are not met. An example would be to create a SQL Trigger that prevents the creation or update of a customer record if user_def_fld_1 is not equal to "A", "B", or "C".)

Database changes that can be made by professional (someone who has a clue about SQL and database design):

1. Add new tables to the Macola databases
2. Create views that query existing Macola databases
3. Create stored procedures that select, insert, or modify data in to any table in the Macola database.
4. Create SQL Triggers that perform additional SQL tasks. These triggers should never throw an error and should never rollback an update to the Macola tables. This is one that should only be performed by experienced SQL developers.

I know this is a little off topic but I thought I should put it out there so that it is clear what type of changes can be made. If you are still in the Btrieve/Pervasive.SQL world all of the rules above apply except those that specifically mention SQL.

Scott Travis

http://www.infoSpring.net