Re: EDI

In article <6ng23h$d97$1_at_uuneo.neosoft.com>,   "Matthew MacFarland" <matthew_mac&*(^%farland_at_dril-quip.com> wrote:
> Does any know of the best way to output invoices from a custom Oracle
> database to EDI?

Not sure if this is the *best* way, but ...

We have defined a table called EXPORT with a field called TEXT which is a very long varchar2. We insert records into this table by concatenating the data into a long string and putting this string into the TEXT field. This is done in a PL/SQL stored procedure.

e.g.

INSERT INTO EXPORT (text, line_number)
VALUES (LPAD(v_invoice_number,10) || RPAD(v_billing_address,80) || ...,

        export_sequence.nextval);

The stored procedure is executed at the beginning of a SQL*Plus script that then uses the SPOOL command to store the contents of a SELECT TEXT FROM EXPORT ORDER BY LINE_NUMBER command into a file. This file is now suitable for import into our mapping software.

Two things that make this work: the INSERT command makes use of the LPAD and RPAD functions so that the fields are fixed length in the string, which is what the EDI mapping software expects. And there is an additional LINE_NUMBER field in the EXPORT table which is used to make sure that the records are spooled in the correct order.

Howard Parks
1 Peter 4:10

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 02 1998 - 00:00:00 CDT