oe table

CREATE TABLE ``oe'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``ordnumber'' text,
        ``transdate'' date DEFAULT date('now'::text),
        ``vendor_id'' integer,
        ``customer_id'' integer,
        ``amount'' double precision,
        ``netamount'' double precision,
        ``reqdate'' date,
        ``taxincluded'' boolean,
        ``shippingpoint'' text,
        ``notes'' text,
        ``curr'' character(3)
);

NAME

Vend::Accounting::SQL-Ledger - SQL-Ledger Accounting Interface for Interchange

DESCRIPTION

This module is an attempt to create a set of callable routines that will allow the easy integration of the SQL-Ledger Accounting package with Interchange.

It handles the mapping of the Interchange variable names to the appropriate SQL-Ledger ones as well as parsing the html returned by the SQL-Ledger ``API''.

Background: SQL-Ledger Accounting ``www.sql-ledger.org'' is a multiuser, double entry, accounting system written in Perl and is licensed under the GNU General Public License.

The SQL-Ledger API: SQL-Ledger functions can be accessed from the command line by passing all the variables in one long string to the perl script. The variable=value pairs must be separated by an ampersand. See ``www.sql-ledger.org/misc/api.html'' for more details on the command line interface.

------------------------------------------------------------------

This module also happens to be the author's first perl module and probably his second or third perl program in addition to ``Hello World''. :)

So please go easy on me. -Daniel

Schema

CREATE SEQUENCE ``id'' start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;

CREATE TABLE ``makemodel'' (         ``id'' integer,
        ``parts_id'' integer,
        ``name'' text
); CREATE TABLE ``gl'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``source'' text,
        ``description'' text,
        ``transdate'' date DEFAULT date('now'::text)
);

CREATE TABLE ``chart'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``accno'' integer,
        ``description'' text,
        ``charttype'' character(1) DEFAULT 'A',
        ``gifi'' integer,
        ``category'' character(1),
        ``link'' text
);

CREATE TABLE ``defaults'' (         ``inventory_accno_id'' integer,
        ``income_accno_id'' integer,
        ``expense_accno_id'' integer,
        ``fxgain_accno_id'' integer,
        ``fxloss_accno_id'' integer,
        ``invnumber'' text,
        ``ordnumber'' text,
        ``yearend'' character varying(5),
        ``curr'' text,
        ``weightunit'' character varying(5),
        ``businessnumber'' text,
        ``version'' character varying(8)
);

CREATE TABLE ``acc_trans'' (         ``trans_id'' integer,
        ``chart_id'' integer,
        ``amount'' double precision,
        ``transdate'' date DEFAULT date('now'::text),
        ``source'' text,
        ``cleared'' boolean DEFAULT 'f',
        ``fx_transaction'' boolean DEFAULT 'f'
);

CREATE TABLE ``invoice'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``trans_id'' integer,
        ``parts_id'' integer,
        ``description'' text,
        ``qty'' real,
        ``allocated'' real,
        ``sellprice'' double precision,
        ``fxsellprice'' double precision,
        ``discount'' real,
        ``assemblyitem'' boolean DEFAULT 'f'
);

CREATE TABLE ``vendor'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``name'' character varying(35),
        ``addr1'' character varying(35),
        ``addr2'' character varying(35),
        ``addr3'' character varying(35),
        ``addr4'' character varying(35),
        ``contact'' character varying(35),
        ``phone'' character varying(20),
        ``fax'' character varying(20),
        ``email'' text,
        ``notes'' text,
        ``terms'' smallint DEFAULT 0,
        ``taxincluded'' boolean
);

CREATE TABLE ``customer'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``name'' character varying(35),
        ``addr1'' character varying(35),
        ``addr2'' character varying(35),
        ``addr3'' character varying(35),
        ``addr4'' character varying(35),
        ``contact'' character varying(35),
        ``phone'' character varying(20),
        ``fax'' character varying(20),
        ``email'' text,
        ``notes'' text,
        ``discount'' real,
        ``taxincluded'' boolean,
        ``creditlimit'' double precision DEFAULT 0,
        ``terms'' smallint DEFAULT 0,
        ``shiptoname'' character varying(35),
        ``shiptoaddr1'' character varying(35),
        ``shiptoaddr2'' character varying(35),
        ``shiptoaddr3'' character varying(35),
        ``shiptoaddr4'' character varying(35),
        ``shiptocontact'' character varying(20),
        ``shiptophone'' character varying(20),
        ``shiptofax'' character varying(20),
        ``shiptoemail'' text
);

CREATE TABLE ``parts'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``partnumber'' text,
        ``description'' text,
        ``bin'' text,
        ``unit'' character varying(5),
        ``listprice'' double precision,
        ``sellprice'' double precision,
        ``lastcost'' double precision,
        ``priceupdate'' date DEFAULT date('now'::text),
        ``weight'' real,
        ``onhand'' real DEFAULT 0,
        ``notes'' text,
        ``makemodel'' boolean DEFAULT 'f',
        ``assembly'' boolean DEFAULT 'f',
        ``alternate'' boolean DEFAULT 'f',
        ``rop'' real,
        ``inventory_accno_id'' integer,
        ``income_accno_id'' integer,
        ``expense_accno_id'' integer,
        ``obsolete'' boolean DEFAULT 'f'
);

CREATE TABLE ``assembly'' (         ``id'' integer,
        ``parts_id'' integer,
        ``qty'' double precision
);

CREATE TABLE ``ar'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``invnumber'' text,
        ``ordnumber'' text,
        ``transdate'' date DEFAULT date('now'::text),
        ``customer_id'' integer,
        ``taxincluded'' boolean,
        ``amount'' double precision,
        ``netamount'' double precision,
        ``paid'' double precision,
        ``datepaid'' date,
        ``duedate'' date,
        ``invoice'' boolean DEFAULT 'f',
        ``shippingpoint'' text,
        ``terms'' smallint DEFAULT 0,
        ``notes'' text,
        ``curr'' character(3)
);

CREATE TABLE ``ap'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``invnumber'' text,
        ``transdate'' date DEFAULT date('now'::text),
        ``vendor_id'' integer,
        ``taxincluded'' boolean,
        ``amount'' double precision,
        ``netamount'' double precision,
        ``paid'' double precision,
        ``datepaid'' date,
        ``duedate'' date,
        ``invoice'' boolean DEFAULT 'f',
        ``ordnumber'' text,
        ``curr'' character(3)
);

CREATE TABLE ``partstax'' (         ``parts_id'' integer,
        ``chart_id'' integer
);

CREATE TABLE ``tax'' (         ``chart_id'' integer,
        ``rate'' double precision,
        ``taxnumber'' text
);

CREATE TABLE ``customertax'' (         ``customer_id'' integer,
        ``chart_id'' integer
);

CREATE TABLE ``vendortax'' (         ``vendor_id'' integer,
        ``chart_id'' integer
);

CREATE TABLE ``oe'' (         ``id'' integer DEFAULT nextval('id'::text),
        ``ordnumber'' text,
        ``transdate'' date DEFAULT date('now'::text),
        ``vendor_id'' integer,
        ``customer_id'' integer,
        ``amount'' double precision,
        ``netamount'' double precision,
        ``reqdate'' date,
        ``taxincluded'' boolean,
        ``shippingpoint'' text,
        ``notes'' text,
        ``curr'' character(3)
);

CREATE TABLE ``orderitems'' (         ``trans_id'' integer,
        ``parts_id'' integer,
        ``description'' text,
        ``qty'' real,
        ``sellprice'' double precision,
        ``discount'' real
);

CREATE TABLE ``exchangerate'' (         ``curr'' character(3),
        ``transdate'' date,
        ``buy'' double precision,
        ``sell'' double precision
);