Best Method to Insert Records Into OESLSHST

[vanessa03]

Malcola 7.6.300b & SQL2000
I am very new to SQL & SQL Programming, so keep that in mind. I would like to insert records in OESLSHST from an Excel file, which I have uploaded into a SQL Table. These records will be our Sales Budget. The fields are layed out as follows:
Salesman,Cust,Shipto,Prod Cat, Item#,JanQty,JanAmt,FebQty,FebAmt,MarQty,MarAmt,etc...

What would be the best method to insert a month at a time into the OESLSHST table? Also how would you get the next identity number?

Should I post this in the SQL programming board? Thanks.

 

[maccrystal]

The OESLSHST table is populated by the OE\Processes\Post Sales Hst trx. This function insert records based on the invoices generated during the time period. You should not attempt to modify the standard Progression tables.

You should create a seperate table in the Database to upload the sales budget records. Use a T-SQL script to add the table (e.g ASI_OESalesBudget) and create the keys you need. You could then write a Excel VBS script to upload the Excel info into the SQL budget table. Make sure you duplicate the field attributes for the Customer No, Item, SHip to, etc... attributes. The Identity number (GL4Identity) is a SQL auto increment field that is automatically set as records are added to the table.

Do your experimentation is a "TEST COMPANY" before attempting this in your "LIVE" database!!

Sample Script to create table:

-- Sample Script
-- Create a Sale budget table
CREATE TABLE ASI_OESLSBudget_sql (
SLSPSN_NO char(3) NOT NULL,
CUS_NO char(12) NOT NULL,
SHIP_TO char(15) NOT NULL,
PROD_CAT char(3) NULL,
Item_NO char(15) NULL,
Jan_BudgetQty [decimal](14, 2) NULL ,
Jan_BudgetDolLars [decimal](14, 2) NULL ,
-- Add others month fields below.
Dec_BudgetQty [decimal](14, 2) NULL ,
Dec_BudgetDolLars [decimal](14, 2) NULL ,
A4IDENTITY numeric(9,0) Identity (1,1) not null
)
Go

The OESLSHST table and the Sales Budget table could be linked for reporting purposes.

http://www.appstratinc.com

 

[vanessa03]

Thanks for the fast reply. For the past 4 years I have been using the sales history load to load my budget(using invoice date of 10 years out). I then post in to the OESLSHST file. It's just a tedious process, usually taking a couple of days to enter. I was looking for a faster way to add to the file.

 

[dgillz]

Its pretty easy to write a MS Access query to update or append records into the OESLSHST table, however as has been pointed out, this is not what Macola meant this table to do.

So I would create my own table and append records into it rather than stepping on one of Macola's tables.

But for reporting purposes, if the data is already in excel, there is no need to put it somewhere else, just make this spreadsheet one of the data sources for the crystal report, with the rest of the data coming from macola tables.

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

http://www.gainfocus.biz

 

[kscheeler]

I agree with everyone that the OESLSHST table wasn't designed for that purpose. I would also create my own table in the SQL datbase. I would do that rather than excel because it will make it easier to integrate with existing crystal reports because you wouldn't have to create a seperate data source for it. You also could set it up in Excel first and use Enterprise Manager to import the data into your own table in SQL. You'll get the best of both worlds because the table can be designed the way YOU need it to be.

Kevin Scheeler