- begin;
- CREATE SEQUENCE id;
- -- Central DB structure
- -- This is the central database stuff which is used across all datasets
- -- in the ledger-smb.conf it is called 'ledgersmb' by default, but obviously
- -- can be named anything.
- -- BEGIN new entity management
- CREATE TABLE entity_class (
- id serial primary key,
- class text check (class ~ '[[:alnum:]_]') NOT NULL,
- active boolean not null default TRUE);
-
- COMMENT ON TABLE entity_class IS $$ Defines the class type such as vendor, customer, contact, employee $$;
- COMMENT ON COLUMN entity_class.id IS $$ The first 7 values are reserved and permanent $$;
- CREATE index entity_class_idx ON entity_class(lower(class));
- CREATE TABLE entity (
- id serial UNIQUE,
- name text check (name ~ '[[:alnum:]_]'),
- entity_class integer references entity_class(id) not null ,
- created date not null default current_date,
- PRIMARY KEY(name,entity_class));
-
- COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$;
- COMMENT ON COLUMN entity.name IS $$ This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com $$;
- ALTER TABLE entity ADD FOREIGN KEY (entity_class) REFERENCES entity_class(id);
- INSERT INTO entity_class (id,class) VALUES (1,'Vendor');
- INSERT INTO entity_class (id,class) VALUES (2,'Customer');
- INSERT INTO entity_class (id,class) VALUES (3,'Employee');
- INSERT INTO entity_class (id,class) VALUES (4,'Contact');
- INSERT INTO entity_class (id,class) VALUES (5,'Lead');
- INSERT INTO entity_class (id,class) VALUES (6,'Referral');
- SELECT setval('entity_class_id_seq',7);
- CREATE TABLE entity_class_to_entity (
- entity_class_id integer not null references entity_class(id) ON DELETE CASCADE,
- entity_id integer not null references entity(id) ON DELETE CASCADE,
- PRIMARY KEY(entity_class_id,entity_id)
- );
- COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
- -- USERS stuff --
- CREATE TABLE users (
- id serial UNIQUE,
- username varchar(30) primary key,
- entity_id int not null references entity(id) on delete cascade
- );
- COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$;
- -- Session tracking table
- CREATE TABLE session(
- session_id serial PRIMARY KEY,
- token VARCHAR(32) CHECK(length(token) = 32),
- last_used TIMESTAMP default now(),
- ttl int default 3600 not null,
- users_id INTEGER NOT NULL references users(id),
- transaction_id INTEGER NOT NULL
- );
- --
- CREATE TABLE transactions (
- id int PRIMARY KEY,
- table_name text,
- locked_by int references "session" (session_id) ON DELETE SET NULL
- );
- COMMENT on TABLE transactions IS
- $$ This table tracks basic transactions across AR, AP, and GL related tables.
- It provies a referential integrity enforcement mechanism for the financial data
- and also some common features such as discretionary (and pessimistic) locking
- for long batch workflows. $$;
- CREATE FUNCTION lock_record (int, int) returns bool as
- $$
- declare
- locked int;
- begin
- SELECT locked_by into locked from transactions where id = $1;
- IF NOT FOUND THEN
- RETURN FALSE;
- ELSEIF locked_by is not null AND locked_by <> $2 THEN
- RETURN FALSE;
- END IF;
- UPDATE transactions set locked_by = $2 where id = $1;
- RETURN TRUE;
- end;
- $$ language plpgsql;
- COMMENT ON column transactions.locked_by IS
- $$ This should only be used in pessimistic locking measures as required by large
- batch work flows. $$;
- -- LOCATION AND COUNTRY
- CREATE TABLE country (
- id serial PRIMARY KEY,
- name text check (name ~ '[[:alnum:]_]') NOT NULL,
- short_name text check (short_name ~ '[[:alnum:]_]') NOT NULL,
- itu text);
-
- COMMENT ON COLUMN country.itu IS $$ The ITU Telecommunication Standardization Sector code for calling internationally. For example, the US is 1, Great Britain is 44 $$;
- CREATE UNIQUE INDEX country_name_idx on country(lower(name));
- CREATE TABLE location_class (
- id serial UNIQUE,
- class text check (class ~ '[[:alnum:]_]') not null,
- authoritative boolean not null,
- PRIMARY KEY (class,authoritative));
-
- CREATE UNIQUE INDEX lower_class_unique ON location_class(lower(class));
- INSERT INTO location_class(id,class,authoritative) VALUES ('1','Billing',TRUE);
- INSERT INTO location_class(id,class,authoritative) VALUES ('2','Sales',TRUE);
- INSERT INTO location_class(id,class,authoritative) VALUES ('3','Shipping',TRUE);
- SELECT SETVAL('location_class_id_seq',4);
-
- CREATE TABLE location (
- id serial PRIMARY KEY,
- line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL,
- line_two text,
- line_three text,
- city text check (city ~ '[[:alnum:]_]') NOT NULL,
- state text check(state ~ '[[:alnum:]_]') NOT NULL,
- country_id integer not null REFERENCES country(id),
- mail_code text not null check (mail_code ~ '[[:alnum:]_]'),
- created date not null,
- inactive_date timestamp default null,
- active boolean not null default TRUE
- );
- CREATE INDEX location_unique_class_idx ON location (id,location_class);
-
- CREATE TABLE company (
- id serial UNIQUE,
- entity_id integer not null references entity(id),
- legal_name text check (legal_name ~ '[[:alnum:]_]'),
- tax_id text,
- created date default current_date not null,
- PRIMARY KEY (entity_id,legal_name));
-
- COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$;
- CREATE TABLE company_to_location (
- location_id integer references location(id) not null,
- location_class integer not null references location_class(id),
- company_id integer not null references company(id) ON DELETE CASCADE,
- PRIMARY KEY(location_id,company_id));
- CREATE TABLE salutation (
- id serial unique,
- salutation text primary key);
- INSERT INTO salutation (id,salutation) VALUES ('1','Dr.');
- INSERT INTO salutation (id,salutation) VALUES ('2','Miss.');
- INSERT INTO salutation (id,salutation) VALUES ('3','Mr.');
- INSERT INTO salutation (id,salutation) VALUES ('4','Mrs.');
- INSERT INTO salutation (id,salutation) VALUES ('5','Ms.');
- INSERT INTO salutation (id,salutation) VALUES ('6','Sir.');
- SELECT SETVAL('salutation_id_seq',7);
- CREATE TABLE person (
- id serial PRIMARY KEY,
- entity_id integer references entity(id) not null,
- salutation_id integer references salutation(id),
- first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL,
- middle_name text,
- last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL,
- created date not null default current_date
- );
-
- COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$;
- create table entity_employee (
-
- person_id integer references person(id) not null,
- entity_id integer references entity(id) not null unique,
- startdate date not null default current_date,
- enddate date,
- role varchar(20),
- ssn text,
- sales bool default 'f',
- manager_id integer references entity(id),
- employeenumber varchar(32),
- dob date,
- PRIMARY KEY (person_id, entity_id)
- );
- CREATE TABLE person_to_location (
- location_id integer not null references location(id),
- location_class integer not null references location_class(id),
- person_id integer not null references person(id) ON DELETE CASCADE,
- PRIMARY KEY (location_id,person_id));
- CREATE TABLE person_to_company (
- location_id integer references location(id) not null,
- person_id integer not null references person(id) ON DELETE CASCADE,
- company_id integer not null references company(id) ON DELETE CASCADE,
- PRIMARY KEY (location_id,person_id));
- CREATE TABLE entity_other_name (
- entity_id integer not null references entity(id) ON DELETE CASCADE,
- other_name text check (other_name ~ '[[:alnum:]_]'),
- PRIMARY KEY (other_name, entity_id));
-
- COMMENT ON TABLE entity_other_name IS $$ Similar to company_other_name, a person may be jd, Joshua Drake, linuxpoet... all are the same person. $$;
- CREATE TABLE person_to_entity (
- person_id integer not null references person(id) ON DELETE CASCADE,
- entity_id integer not null check (entity_id != person_id) references entity(id) ON DELETE CASCADE,
- related_how text,
- created date not null default current_date,
- PRIMARY KEY (person_id,entity_id));
-
- CREATE TABLE company_to_entity (
- company_id integer not null references company(id) ON DELETE CASCADE,
- entity_id integer check (company_id != entity_id) not null references entity(id) ON DELETE CASCADE,
- related_how text,
- created date not null default current_date,
- PRIMARY KEY (company_id,entity_id));
-
- CREATE TABLE contact_class (
- id serial UNIQUE,
- class text check (class ~ '[[:alnum:]_]') NOT NULL,
- PRIMARY KEY (class));
-
- CREATE UNIQUE INDEX contact_class_class_idx ON contact_class(lower(class));
- INSERT INTO contact_class (id,class) values (1,'Primary Phone');
- INSERT INTO contact_class (id,class) values (2,'Secondary Phone');
- INSERT INTO contact_class (id,class) values (3,'Cell Phone');
- INSERT INTO contact_class (id,class) values (4,'AIM');
- INSERT INTO contact_class (id,class) values (5,'Yahoo');
- INSERT INTO contact_class (id,class) values (6,'Gtalk');
- INSERT INTO contact_class (id,class) values (7,'MSN');
- INSERT INTO contact_class (id,class) values (8,'IRC');
- INSERT INTO contact_class (id,class) values (9,'Fax');
- INSERT INTO contact_class (id,class) values (10,'Generic Jabber');
- INSERT INTO contact_class (id,class) values (11,'Home Phone');
- INSERT INTO contact_class (id,class) values (12,'Email');
- SELECT SETVAL('contact_class_id_seq',12);
- CREATE TABLE person_to_contact (
- person_id integer not null references person(id) ON DELETE CASCADE,
- contact_class_id integer references contact_class(id) not null,
- contact text check(contact ~ '[[:alnum:]_]') not null,
- PRIMARY KEY (person_id,contact_class_id,contact));
-
- COMMENT ON TABLE person_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single individual $$;
-
- CREATE TABLE company_to_contact (
- company_id integer not null references company(id) ON DELETE CASCADE,
- contact_class_id integer references contact_class(id) not null,
- contact text check(contact ~ '[[:alnum:]_]') not null,
- PRIMARY KEY (company_id,contact_class_id,contact));
- COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$;
-
- -- Begin rocking notes interface
- CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'));
- INSERT INTO note_class(id,class) VALUES (1,'Entity');
- INSERT INTO note_class(id,class) VALUES (2,'Invoice');
- CREATE UNIQUE INDEX note_class_idx ON note_class(lower(class));
- CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id),
- note text not null, vector tsvector not null,
- created timestamp not null default now(),
- ref_key integer not null);
- CREATE TABLE entity_note(entity_id int references entity(id)) INHERITS (note);
- ALTER TABLE entity_note ADD CHECK (note_class = 1);
- ALTER TABLE entity_note ADD FOREIGN KEY (ref_key) REFERENCES entity(id) ON DELETE CASCADE;
- CREATE INDEX entity_note_id_idx ON entity_note(id);
- CREATE UNIQUE INDEX entity_note_class_idx ON note_class(lower(class));
- CREATE INDEX entity_note_vectors_idx ON entity_note USING gist(vector);
- CREATE TABLE invoice_note() INHERITS (note);
- CREATE INDEX invoice_note_id_idx ON invoice_note(id);
- CREATE UNIQUE INDEX invoice_note_class_idx ON note_class(lower(class));
- CREATE INDEX invoice_note_vectors_idx ON invoice_note USING gist(vector);
- -- END entity
- --
- CREATE TABLE makemodel (
- parts_id int PRIMARY KEY,
- make text,
- model text
- );
- --
- CREATE TABLE gl (
- id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
- reference text,
- description text,
- transdate date DEFAULT current_date,
- person_id integer references person(id),
- notes text,
- approved bool default true,
- department_id int default 0
- );
- --
- CREATE TABLE chart (
- id serial PRIMARY KEY,
- accno text NOT NULL,
- description text,
- charttype char(1) DEFAULT 'A',
- category char(1),
- link text,
- gifi_accno text,
- contra bool DEFAULT 'f'
- );
- --
- CREATE TABLE gifi (
- accno text PRIMARY KEY,
- description text
- );
- --
- CREATE TABLE defaults (
- setting_key text primary key,
- value text
- );
- /*
- inventory_accno_id int,
- income_accno_id int,
- expense_accno_id int,
- fxgain_accno_id int,
- fxloss_accno_id int,
- */
- \COPY defaults FROM stdin WITH DELIMITER |
- sinumber|1
- sonumber|1
- yearend|1
- businessnumber|1
- version|1.2.0
- closedto|\N
- revtrans|1
- ponumber|1
- sqnumber|1
- rfqnumber|1
- audittrail|0
- vinumber|1
- employeenumber|1
- partnumber|1
- customernumber|1
- vendornumber|1
- glnumber|1
- projectnumber|1
- \.
- -- */
- CREATE TABLE acc_trans (
- trans_id int NOT NULL REFERENCES transactions(id),
- chart_id int NOT NULL REFERENCES chart (id),
- amount NUMERIC,
- transdate date DEFAULT current_date,
- source text,
- cleared bool DEFAULT 'f',
- fx_transaction bool DEFAULT 'f',
- project_id int,
- memo text,
- invoice_id int,
- approved bool default true,
- entry_id SERIAL PRIMARY KEY
- );
- --
- CREATE TABLE invoice (
- id serial PRIMARY KEY,
- trans_id int,
- parts_id int,
- description text,
- qty integer,
- allocated integer,
- sellprice NUMERIC,
- fxsellprice NUMERIC,
- discount numeric,
- assemblyitem bool DEFAULT 'f',
- unit varchar(5),
- project_id int,
- deliverydate date,
- serialnumber text,
- notes text
- );
- -- Added for Entity but can't be added due to order
- ALTER TABLE invoice_note ADD FOREIGN KEY (ref_key) REFERENCES invoice(id);
- --
- --
- -- pricegroup added here due to references
- CREATE TABLE pricegroup (
- id serial PRIMARY KEY,
- pricegroup text
- );
- CREATE TABLE entity_credit_account (
- id serial not null unique,
- entity_id int not null references entity(id) ON DELETE CASCADE,
- entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
- discount numeric,
- discount_terms int default 0,
- taxincluded bool default 'f',
- creditlimit NUMERIC default 0,
- terms int2 default 0,
- meta_number varchar(32),
- cc text,
- bcc text,
- business_id int,
- language_code varchar(6),
- pricegroup_id int references pricegroup(id),
- curr char(3),
- startdate date DEFAULT CURRENT_DATE,
- enddate date,
- threshold numeric default 0,
- PRIMARY KEY(entity_id, meta_number)
- );
- -- notes are from entity_note
- -- ssn, iban and bic are from entity_credit_account
- --
- -- The view below is broken. Disabling for now.
- /*
- create view employee as
- SELECT
- ente.entity_id,
- 3,
- u.username,
- ente.startdate,
- ente.enddate,
- en.note,
- ente.ssn,
- eca.iban,
- eca.bic,
- ente.manager_id,
- ente.employeenumber,
- ente.dob
- FROM
- entity_employee ente
- JOIN
- entity_credit_account eca on (eca.entity_id = ente.entity_id)
- JOIN
- entity_note en on (en.entity_id = ente.entity_id)
- JOIN
- users u on (u.entity_id = ente.entity_id);
- */
- CREATE TABLE entity_bank_account (
- id serial not null,
- entity_id int not null references entity(id) ON DELETE CASCADE,
- bic varchar,
- iban varchar,
- UNIQUE (id),
- PRIMARY KEY (entity_id, bic, iban)
- );
- CREATE VIEW customer AS
- SELECT
- c.id,
- emd.entity_id,
- emd.entity_class,
- emd.discount,
- emd.taxincluded,
- emd.creditlimit,
- emd.terms,
- emd.meta_number as customernumber,
- emd.cc,
- emd.bcc,
- emd.business_id,
- emd.language_code,
- emd.pricegroup_id,
- emd.curr,
- emd.startdate,
- emd.enddate,
- eba.bic,
- eba.iban,
- ein.note as invoice_notes
- FROM entity_credit_account emd
- join entity_bank_account eba on emd.entity_id = eba.entity_id
- Left join entity_note ein on ein.ref_key = emd.entity_id
- join company c on c.entity_id = emd.entity_id
- where emd.entity_class = 2;
-
- CREATE VIEW vendor AS
- SELECT
- c.id,
- emd.entity_id,
- emd.entity_class,
- emd.discount,
- emd.taxincluded,
- emd.creditlimit,
- emd.terms,
- emd.meta_number as vendornumber,
- emd.cc,
- emd.bcc,
- emd.business_id,
- emd.language_code,
- emd.pricegroup_id,
- emd.curr,
- emd.startdate,
- emd.enddate,
- eba.bic,
- eba.iban,
- ein.note as
- invoice_notes
- FROM entity_credit_account emd
- join entity_bank_account eba on emd.entity_id = eba.entity_id
- left join entity_note ein on ein.ref_key = emd.entity_id
- join company c on c.entity_id = emd.entity_id
- where emd.entity_class = 1;
- COMMENT ON TABLE entity_credit_account IS $$ This is a metadata table for ALL entities in LSMB; it deprecates the use of customer and vendor specific tables (which were nearly identical and largely redundant), and replaces it with a single point of metadata. $$;
- COMMENT ON COLUMN entity_credit_account.entity_id IS $$ This is the relationship between entities and their metadata. $$;
- COMMENT ON COLUMN entity_credit_account.entity_class IS $$ A reference to entity_class, requiring that entity_credit_account only apply to vendors and customers, using the entity_class table as the Point Of Truth. $$;
- ALTER TABLE company ADD COLUMN sic_code varchar;
- --
- --
- -- COMMENT ON TABLE employee IS $$ Is a metadata table specific to employees $$;
- CREATE TABLE parts (
- id serial PRIMARY KEY,
- partnumber text,
- description text,
- unit varchar(5),
- listprice NUMERIC,
- sellprice NUMERIC,
- lastcost NUMERIC,
- priceupdate date DEFAULT current_date,
- weight numeric,
- onhand numeric DEFAULT 0,
- notes text,
- makemodel bool DEFAULT 'f',
- assembly bool DEFAULT 'f',
- alternate bool DEFAULT 'f',
- rop numeric, -- SC: ReOrder Point
- inventory_accno_id int,
- income_accno_id int,
- expense_accno_id int,
- bin text,
- obsolete bool DEFAULT 'f',
- bom bool DEFAULT 'f',
- image text,
- drawing text,
- microfiche text,
- partsgroup_id int,
- project_id int,
- avgcost NUMERIC
- );
- CREATE UNIQUE INDEX parts_partnumber_index_u ON parts (partnumber)
- WHERE obsolete is false;
- --
- CREATE TABLE assembly (
- id int,
- parts_id int,
- qty numeric,
- bom bool,
- adj bool,
- PRIMARY KEY (id, parts_id)
- );
- --
- CREATE TABLE ar (
- id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
- invnumber text,
- transdate date DEFAULT current_date,
- entity_id int REFERENCES entity(id),
- taxincluded bool,
- amount NUMERIC,
- netamount NUMERIC,
- paid NUMERIC,
- datepaid date,
- duedate date,
- invoice bool DEFAULT 'f',
- shippingpoint text,
- terms int2 DEFAULT 0,
- notes text,
- curr char(3),
- ordnumber text,
- person_id integer references entity_employee(entity_id),
- till varchar(20),
- quonumber text,
- intnotes text,
- department_id int default 0,
- shipvia text,
- language_code varchar(6),
- ponumber text,
- on_hold bool default false,
- reverse bool default false,
- approved bool default true
- );
- COMMENT ON COLUMN ar.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
- --
- CREATE TABLE ap (
- id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
- invnumber text,
- transdate date DEFAULT current_date,
- entity_id int REFERENCES entity(id),
- taxincluded bool DEFAULT 'f',
- amount NUMERIC,
- netamount NUMERIC,
- paid NUMERIC,
- datepaid date,
- duedate date,
- invoice bool DEFAULT 'f',
- ordnumber text,
- curr char(3),
- notes text,
- person_id integer references entity_employee(entity_id),
- till varchar(20),
- quonumber text,
- intnotes text,
- department_id int DEFAULT 0,
- shipvia text,
- language_code varchar(6),
- ponumber text,
- shippingpoint text,
- on_hold bool default false,
- approved bool default true,
- reverse bool default false,
- terms int2 DEFAULT 0
- );
- COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
- --
- CREATE TABLE taxmodule (
- taxmodule_id serial PRIMARY KEY,
- taxmodulename text NOT NULL
- );
- --
- CREATE TABLE taxcategory (
- taxcategory_id serial PRIMARY KEY,
- taxcategoryname text NOT NULL,
- taxmodule_id int NOT NULL,
- FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id)
- );
- --
- CREATE TABLE partstax (
- parts_id int,
- chart_id int,
- taxcategory_id int,
- PRIMARY KEY (parts_id, chart_id),
- FOREIGN KEY (parts_id) REFERENCES parts (id) on delete cascade,
- FOREIGN KEY (chart_id) REFERENCES chart (id),
- FOREIGN KEY (taxcategory_id) REFERENCES taxcategory (taxcategory_id)
- );
- --
- CREATE TABLE tax (
- chart_id int PRIMARY KEY,
- rate numeric,
- taxnumber text,
- validto date,
- pass integer DEFAULT 0 NOT NULL,
- taxmodule_id int DEFAULT 1 NOT NULL,
- FOREIGN KEY (chart_id) REFERENCES chart (id),
- FOREIGN KEY (taxmodule_id) REFERENCES taxmodule (taxmodule_id)
- );
- --
- CREATE TABLE customertax (
- customer_id int references entity_credit_account(id) on delete cascade,
- chart_id int,
- PRIMARY KEY (customer_id, chart_id)
- );
- --
- CREATE TABLE vendortax (
- vendor_id int references entity_credit_account(id) on delete cascade,
- chart_id int,
- PRIMARY KEY (vendor_id, chart_id)
- );
- --
- CREATE TABLE oe_class (
- id smallint unique check(id IN (1,2,3,4)),
- oe_class text primary key);
-
- INSERT INTO oe_class(id,oe_class) values (1,'Sales Order');
- INSERT INTO oe_class(id,oe_class) values (2,'Purchase Order');
- INSERT INTO oe_class(id,oe_class) values (3,'Quotation');
- INSERT INTO oe_class(id,oe_class) values (4,'RFQ');
- COMMENT ON TABLE oe_class IS $$ This could probably be done better. But I need to remove the customer_id/vendor_id relationship and instead rely on a classification $$;
- CREATE TABLE oe (
- id serial PRIMARY KEY,
- ordnumber text,
- transdate date default current_date,
- entity_id integer references entity(id),
- amount NUMERIC,
- netamount NUMERIC,
- reqdate date,
- taxincluded bool,
- shippingpoint text,
- notes text,
- curr char(3),
- person_id integer references person(id),
- closed bool default 'f',
- quotation bool default 'f',
- quonumber text,
- intnotes text,
- department_id int default 0,
- shipvia text,
- language_code varchar(6),
- ponumber text,
- terms int2 DEFAULT 0,
- oe_class_id int references oe_class(id) NOT NULL
- );
- --
- CREATE TABLE orderitems (
- id serial PRIMARY KEY,
- trans_id int,
- parts_id int,
- description text,
- qty numeric,
- sellprice NUMERIC,
- discount numeric,
- unit varchar(5),
- project_id int,
- reqdate date,
- ship numeric,
- serialnumber text,
- notes text
- );
- --
- CREATE TABLE exchangerate (
- curr char(3),
- transdate date,
- buy numeric,
- sell numeric,
- PRIMARY KEY (curr, transdate)
- );
- --
- -- batch stuff
- CREATE TABLE batch_class (
- id serial unique,
- class varchar primary key
- );
- insert into batch_class (id,class) values (1,'ap');
- insert into batch_class (id,class) values (2,'ar');
- insert into batch_class (id,class) values (3,'payment');
- insert into batch_class (id,class) values (4,'payment_reversal');
- insert into batch_class (id,class) values (5,'gl');
- insert into batch_class (id,class) values (6,'receipt');
- SELECT SETVAL('batch_class_id_seq',6);
- CREATE TABLE batch (
- id serial primary key,
- batch_class_id integer references batch_class(id) not null,
- description text,
- approved_on date default null,
- approved_by int references entity_employee(entity_id),
- created_by int references entity_employee(entity_id),
- locked_by int references session(session_id),
- created_on date default now()
- );
- COMMENT ON COLUMN batch.batch_class_id IS
- $$ Note that this field is largely used for sorting the vouchers. A given batch is NOT restricted to this type.$$;
- CREATE TABLE voucher (
- trans_id int REFERENCES transactions(id) NOT NULL,
- batch_id int references batch(id) not null,
- id serial NOT NULL,
- batch_class int references batch_class not null,
- PRIMARY KEY (batch_class, batch_id, trans_id)
- );
- COMMENT ON COLUMN voucher.batch_class IS $$ This is the authoritative class of the
- voucher. $$;
- COMMENT ON COLUMN voucher.id IS $$ This is simply a surrogate key for easy reference.$$;
- --
- create table shipto (
- trans_id int,
- shiptoname varchar(64),
- shiptoaddress1 varchar(32),
- shiptoaddress2 varchar(32),
- shiptocity varchar(32),
- shiptostate varchar(32),
- shiptozipcode varchar(10),
- shiptocountry varchar(32),
- shiptocontact varchar(64),
- shiptophone varchar(20),
- shiptofax varchar(20),
- shiptoemail text,
- entry_id SERIAL PRIMARY KEY
- );
- -- SHIPTO really needs to be pushed into entities too
- --
- --
- CREATE TABLE project (
- id serial PRIMARY KEY,
- projectnumber text,
- description text,
- startdate date,
- enddate date,
- parts_id int,
- production numeric default 0,
- completed numeric default 0,
- customer_id int
- );
- --
- CREATE TABLE partsgroup (
- id serial PRIMARY KEY,
- partsgroup text
- );
- --
- CREATE TABLE status (
- trans_id int PRIMARY KEY,
- formname text,
- printed bool default 'f',
- emailed bool default 'f',
- spoolfile text
- );
- --
- CREATE TABLE department (
- id serial PRIMARY KEY,
- description text,
- role char(1) default 'P'
- );
- --
- -- department transaction table
- CREATE TABLE dpt_trans (
- trans_id int PRIMARY KEY,
- department_id int
- );
- --
- -- business table
- CREATE TABLE business (
- id serial PRIMARY KEY,
- description text,
- discount numeric
- );
- --
- -- SIC
- CREATE TABLE sic (
- code varchar(6) PRIMARY KEY,
- sictype char(1),
- description text
- );
- --
- CREATE TABLE warehouse (
- id serial PRIMARY KEY,
- description text
- );
- --
- CREATE TABLE inventory (
- entity_id integer references entity_employee(entity_id),
- warehouse_id int,
- parts_id int,
- trans_id int,
- orderitems_id int,
- qty numeric,
- shippingdate date,
- entry_id SERIAL PRIMARY KEY
- );
- --
- CREATE TABLE yearend (
- trans_id int PRIMARY KEY,
- transdate date
- );
- --
- CREATE TABLE partsvendor (
- entity_id int not null references entity_credit_account(id) on delete cascade,
- parts_id int,
- partnumber text,
- leadtime int2,
- lastcost NUMERIC,
- curr char(3),
- entry_id SERIAL PRIMARY KEY
- );
- --
- CREATE TABLE partscustomer (
- parts_id int,
- customer_id int not null references entity_credit_account(id) on delete cascade,
- pricegroup_id int,
- pricebreak numeric,
- sellprice NUMERIC,
- validfrom date,
- validto date,
- curr char(3),
- entry_id SERIAL PRIMARY KEY
- );
- -- How does partscustomer.customer_id relate here?
- --
- CREATE TABLE language (
- code varchar(6) PRIMARY KEY,
- description text
- );
- --
- CREATE TABLE audittrail (
- trans_id int,
- tablename text,
- reference text,
- formname text,
- action text,
- transdate timestamp default current_timestamp,
- person_id integer references person(id) not null,
- entry_id BIGSERIAL PRIMARY KEY
- );
- --
- CREATE TABLE translation (
- trans_id int,
- language_code varchar(6),
- description text,
- PRIMARY KEY (trans_id, language_code)
- );
- --
- CREATE TABLE user_preference (
- id int PRIMARY KEY REFERENCES users(id),
- language varchar(6) REFERENCES language(code),
- stylesheet text default 'ledgersmb.css' not null,
- printer text,
- dateformat text default 'yyyy-mm-dd' not null,
- numberformat text default '1000.00' not null
- );
- -- user_preference is here due to a dependency on language.code
- COMMENT ON TABLE user_preference IS
- $$ This table sets the basic preferences for formats, languages, printers, and user-selected stylesheets.$$;
- CREATE TABLE recurring (
- id int DEFAULT nextval ( 'id' ) PRIMARY KEY,
- reference text,
- startdate date,
- nextdate date,
- enddate date,
- repeat int2,
- unit varchar(6),
- howmany int,
- payment bool default 'f'
- );
- --
- CREATE TABLE recurringemail (
- id int PRIMARY KEY,
- formname text,
- format text,
- message text
- );
- --
- CREATE TABLE recurringprint (
- id int PRIMARY KEY,
- formname text,
- format text,
- printer text
- );
- --
- CREATE TABLE jcitems (
- id serial PRIMARY KEY,
- project_id int,
- parts_id int,
- description text,
- qty numeric,
- allocated numeric,
- sellprice NUMERIC,
- fxsellprice NUMERIC,
- serialnumber text,
- checkedin timestamp with time zone,
- checkedout timestamp with time zone,
- person_id integer references person(id) not null,
- notes text
- );
- insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
- insert into transactions (id, table_name) SELECT id, 'ar' FROM ap;
- INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
- CREATE OR REPLACE FUNCTION track_global_sequence() RETURNS TRIGGER AS
- $$
- BEGIN
- IF tg_op = 'INSERT' THEN
- INSERT INTO transactions (id, table_name)
- VALUES (new.id, TG_RELNAME);
- ELSEIF tg_op = 'UPDATE' THEN
- IF new.id = old.id THEN
- return new;
- ELSE
- UPDATE transactions SET id = new.id WHERE id = old.id;
- END IF;
- ELSE
- DELETE FROM transactions WHERE id = old_id;
- END IF;
- RETURN new;
- END;
- $$ LANGUAGE PLPGSQL;
- CREATE TRIGGER ap_track_global_sequence before insert or update or delete on ap
- for each row execute procedure track_global_sequence();
- CREATE TRIGGER ar_track_global_sequence before insert or update or delete on ar
- for each row execute procedure track_global_sequence();
- CREATE TRIGGER gl_track_global_sequence before insert or update or delete on gl
- for each row execute procedure track_global_sequence();
- CREATE TABLE custom_table_catalog (
- table_id SERIAL PRIMARY KEY,
- extends TEXT,
- table_name TEXT
- );
- CREATE TABLE custom_field_catalog (
- field_id SERIAL PRIMARY KEY,
- table_id INT REFERENCES custom_table_catalog,
- field_name TEXT
- );
- INSERT INTO taxmodule (
- taxmodule_id, taxmodulename
- ) VALUES (
- 1, 'Simple'
- );
- create index acc_trans_trans_id_key on acc_trans (trans_id);
- create index acc_trans_chart_id_key on acc_trans (chart_id);
- create index acc_trans_transdate_key on acc_trans (transdate);
- create index acc_trans_source_key on acc_trans (lower(source));
- --
- create index ap_id_key on ap (id);
- create index ap_transdate_key on ap (transdate);
- create index ap_invnumber_key on ap (invnumber);
- create index ap_ordnumber_key on ap (ordnumber);
- create index ap_quonumber_key on ap (quonumber);
- --
- create index ar_id_key on ar (id);
- create index ar_transdate_key on ar (transdate);
- create index ar_invnumber_key on ar (invnumber);
- create index ar_ordnumber_key on ar (ordnumber);
- create index ar_quonumber_key on ar (quonumber);
- --
- create index assembly_id_key on assembly (id);
- --
- create index chart_id_key on chart (id);
- create unique index chart_accno_key on chart (accno);
- create index chart_category_key on chart (category);
- create index chart_link_key on chart (link);
- create index chart_gifi_accno_key on chart (gifi_accno);
- --
- create index customer_customer_id_key on customertax (customer_id);
- --
- create index exchangerate_ct_key on exchangerate (curr, transdate);
- --
- create unique index gifi_accno_key on gifi (accno);
- --
- create index gl_id_key on gl (id);
- create index gl_transdate_key on gl (transdate);
- create index gl_reference_key on gl (reference);
- create index gl_description_key on gl (lower(description));
- --
- create index invoice_id_key on invoice (id);
- create index invoice_trans_id_key on invoice (trans_id);
- --
- create index makemodel_parts_id_key on makemodel (parts_id);
- create index makemodel_make_key on makemodel (lower(make));
- create index makemodel_model_key on makemodel (lower(model));
- --
- create index oe_id_key on oe (id);
- create index oe_transdate_key on oe (transdate);
- create index oe_ordnumber_key on oe (ordnumber);
- create index orderitems_trans_id_key on orderitems (trans_id);
- create index orderitems_id_key on orderitems (id);
- --
- create index parts_id_key on parts (id);
- create index parts_partnumber_key on parts (lower(partnumber));
- create index parts_description_key on parts (lower(description));
- create index partstax_parts_id_key on partstax (parts_id);
- --
- --
- create index shipto_trans_id_key on shipto (trans_id);
- --
- create index project_id_key on project (id);
- create unique index projectnumber_key on project (projectnumber);
- --
- create index partsgroup_id_key on partsgroup (id);
- create unique index partsgroup_key on partsgroup (partsgroup);
- --
- create index status_trans_id_key on status (trans_id);
- --
- create index department_id_key on department (id);
- --
- create index partsvendor_parts_id_key on partsvendor (parts_id);
- --
- create index pricegroup_pricegroup_key on pricegroup (pricegroup);
- create index pricegroup_id_key on pricegroup (id);
- --
- create index audittrail_trans_id_key on audittrail (trans_id);
- --
- create index translation_trans_id_key on translation (trans_id);
- --
- create unique index language_code_key on language (code);
- --
- create index jcitems_id_key on jcitems (id);
- -- Popular some entity data
- INSERT INTO country(short_name,name) VALUES ('AC','Ascension Island');
- INSERT INTO country(short_name,name) VALUES ('AD','Andorra');
- INSERT INTO country(short_name,name) VALUES ('AE','United Arab Emirates');
- INSERT INTO country(short_name,name) VALUES ('AF','Afghanistan');
- INSERT INTO country(short_name,name) VALUES ('AG','Antigua and Barbuda');
- INSERT INTO country(short_name,name) VALUES ('AI','Anguilla');
- INSERT INTO country(short_name,name) VALUES ('AL','Albania');
- INSERT INTO country(short_name,name) VALUES ('AM','Armenia');
- INSERT INTO country(short_name,name) VALUES ('AN','Netherlands Antilles');
- INSERT INTO country(short_name,name) VALUES ('AO','Angola');
- INSERT INTO country(short_name,name) VALUES ('AQ','Antarctica');
- INSERT INTO country(short_name,name) VALUES ('AR','Argentina');
- INSERT INTO country(short_name,name) VALUES ('AS','American Samoa');
- INSERT INTO country(short_name,name) VALUES ('AT','Austria');
- INSERT INTO country(short_name,name) VALUES ('AU','Australia');
- INSERT INTO country(short_name,name) VALUES ('AW','Aruba');
- INSERT INTO country(short_name,name) VALUES ('AX','Aland Islands');
- INSERT INTO country(short_name,name) VALUES ('AZ','Azerbaijan');
- INSERT INTO country(short_name,name) VALUES ('BA','Bosnia and Herzegovina');
- INSERT INTO country(short_name,name) VALUES ('BB','Barbados');
- INSERT INTO country(short_name,name) VALUES ('BD','Bangladesh');
- INSERT INTO country(short_name,name) VALUES ('BE','Belgium');
- INSERT INTO country(short_name,name) VALUES ('BF','Burkina Faso');
- INSERT INTO country(short_name,name) VALUES ('BG','Bulgaria');
- INSERT INTO country(short_name,name) VALUES ('BH','Bahrain');
- INSERT INTO country(short_name,name) VALUES ('BI','Burundi');
- INSERT INTO country(short_name,name) VALUES ('BJ','Benin');
- INSERT INTO country(short_name,name) VALUES ('BM','Bermuda');
- INSERT INTO country(short_name,name) VALUES ('BN','Brunei Darussalam');
- INSERT INTO country(short_name,name) VALUES ('BO','Bolivia');
- INSERT INTO country(short_name,name) VALUES ('BR','Brazil');
- INSERT INTO country(short_name,name) VALUES ('BS','Bahamas');
- INSERT INTO country(short_name,name) VALUES ('BT','Bhutan');
- INSERT INTO country(short_name,name) VALUES ('BV','Bouvet Island');
- INSERT INTO country(short_name,name) VALUES ('BW','Botswana');
- INSERT INTO country(short_name,name) VALUES ('BY','Belarus');
- INSERT INTO country(short_name,name) VALUES ('BZ','Belize');
- INSERT INTO country(short_name,name) VALUES ('CA','Canada');
- INSERT INTO country(short_name,name) VALUES ('CC','Cocos (Keeling) Islands');
- INSERT INTO country(short_name,name) VALUES ('CD','Congo, Democratic Republic');
- INSERT INTO country(short_name,name) VALUES ('CF','Central African Republic');
- INSERT INTO country(short_name,name) VALUES ('CG','Congo');
- INSERT INTO country(short_name,name) VALUES ('CH','Switzerland');
- INSERT INTO country(short_name,name) VALUES ('CI','Cote D\'Ivoire (Ivory Coast)');
- INSERT INTO country(short_name,name) VALUES ('CK','Cook Islands');
- INSERT INTO country(short_name,name) VALUES ('CL','Chile');
- INSERT INTO country(short_name,name) VALUES ('CM','Cameroon');
- INSERT INTO country(short_name,name) VALUES ('CN','China');
- INSERT INTO country(short_name,name) VALUES ('CO','Colombia');
- INSERT INTO country(short_name,name) VALUES ('CR','Costa Rica');
- INSERT INTO country(short_name,name) VALUES ('CS','Czechoslovakia (former)');
- INSERT INTO country(short_name,name) VALUES ('CU','Cuba');
- INSERT INTO country(short_name,name) VALUES ('CV','Cape Verde');
- INSERT INTO country(short_name,name) VALUES ('CX','Christmas Island');
- INSERT INTO country(short_name,name) VALUES ('CY','Cyprus');
- INSERT INTO country(short_name,name) VALUES ('CZ','Czech Republic');
- INSERT INTO country(short_name,name) VALUES ('DE','Germany');
- INSERT INTO country(short_name,name) VALUES ('DJ','Djibouti');
- INSERT INTO country(short_name,name) VALUES ('DK','Denmark');
- INSERT INTO country(short_name,name) VALUES ('DM','Dominica');
- INSERT INTO country(short_name,name) VALUES ('DO','Dominican Republic');
- INSERT INTO country(short_name,name) VALUES ('DZ','Algeria');
- INSERT INTO country(short_name,name) VALUES ('EC','Ecuador');
- INSERT INTO country(short_name,name) VALUES ('EE','Estonia');
- INSERT INTO country(short_name,name) VALUES ('EG','Egypt');
- INSERT INTO country(short_name,name) VALUES ('EH','Western Sahara');
- INSERT INTO country(short_name,name) VALUES ('ER','Eritrea');
- INSERT INTO country(short_name,name) VALUES ('ES','Spain');
- INSERT INTO country(short_name,name) VALUES ('ET','Ethiopia');
- INSERT INTO country(short_name,name) VALUES ('FI','Finland');
- INSERT INTO country(short_name,name) VALUES ('FJ','Fiji');
- INSERT INTO country(short_name,name) VALUES ('FK','Falkland Islands (Malvinas)');
- INSERT INTO country(short_name,name) VALUES ('FM','Micronesia');
- INSERT INTO country(short_name,name) VALUES ('FO','Faroe Islands');
- INSERT INTO country(short_name,name) VALUES ('FR','France');
- INSERT INTO country(short_name,name) VALUES ('FX','France, Metropolitan');
- INSERT INTO country(short_name,name) VALUES ('GA','Gabon');
- INSERT INTO country(short_name,name) VALUES ('GB','Great Britain (UK)');
- INSERT INTO country(short_name,name) VALUES ('GD','Grenada');
- INSERT INTO country(short_name,name) VALUES ('GE','Georgia');
- INSERT INTO country(short_name,name) VALUES ('GF','French Guiana');
- INSERT INTO country(short_name,name) VALUES ('GH','Ghana');
- INSERT INTO country(short_name,name) VALUES ('GI','Gibraltar');
- INSERT INTO country(short_name,name) VALUES ('GL','Greenland');
- INSERT INTO country(short_name,name) VALUES ('GM','Gambia');
- INSERT INTO country(short_name,name) VALUES ('GN','Guinea');
- INSERT INTO country(short_name,name) VALUES ('GP','Guadeloupe');
- INSERT INTO country(short_name,name) VALUES ('GQ','Equatorial Guinea');
- INSERT INTO country(short_name,name) VALUES ('GR','Greece');
- INSERT INTO country(short_name,name) VALUES ('GS','S. Georgia and S. Sandwich Isls.');
- INSERT INTO country(short_name,name) VALUES ('GT','Guatemala');
- INSERT INTO country(short_name,name) VALUES ('GU','Guam');
- INSERT INTO country(short_name,name) VALUES ('GW','Guinea-Bissau');
- INSERT INTO country(short_name,name) VALUES ('GY','Guyana');
- INSERT INTO country(short_name,name) VALUES ('HK','Hong Kong');
- INSERT INTO country(short_name,name) VALUES ('HM','Heard and McDonald Islands');
- INSERT INTO country(short_name,name) VALUES ('HN','Honduras');
- INSERT INTO country(short_name,name) VALUES ('HR','Croatia (Hrvatska)');
- INSERT INTO country(short_name,name) VALUES ('HT','Haiti');
- INSERT INTO country(short_name,name) VALUES ('HU','Hungary');
- INSERT INTO country(short_name,name) VALUES ('ID','Indonesia');
- INSERT INTO country(short_name,name) VALUES ('IE','Ireland');
- INSERT INTO country(short_name,name) VALUES ('IL','Israel');
- INSERT INTO country(short_name,name) VALUES ('IM','Isle of Man');
- INSERT INTO country(short_name,name) VALUES ('IN','India');
- INSERT INTO country(short_name,name) VALUES ('IO','British Indian Ocean Territory');
- INSERT INTO country(short_name,name) VALUES ('IQ','Iraq');
- INSERT INTO country(short_name,name) VALUES ('IR','Iran');
- INSERT INTO country(short_name,name) VALUES ('IS','Iceland');
- INSERT INTO country(short_name,name) VALUES ('IT','Italy');
- INSERT INTO country(short_name,name) VALUES ('JE','Jersey');
- INSERT INTO country(short_name,name) VALUES ('JM','Jamaica');
- INSERT INTO country(short_name,name) VALUES ('JO','Jordan');
- INSERT INTO country(short_name,name) VALUES ('JP','Japan');
- INSERT INTO country(short_name,name) VALUES ('KE','Kenya');
- INSERT INTO country(short_name,name) VALUES ('KG','Kyrgyzstan');
- INSERT INTO country(short_name,name) VALUES ('KH','Cambodia');
- INSERT INTO country(short_name,name) VALUES ('KI','Kiribati');
- INSERT INTO country(short_name,name) VALUES ('KM','Comoros');
- INSERT INTO country(short_name,name) VALUES ('KN','Saint Kitts and Nevis');
- INSERT INTO country(short_name,name) VALUES ('KP','Korea (North)');
- INSERT INTO country(short_name,name) VALUES ('KR','Korea (South)');
- INSERT INTO country(short_name,name) VALUES ('KW','Kuwait');
- INSERT INTO country(short_name,name) VALUES ('KY','Cayman Islands');
- INSERT INTO country(short_name,name) VALUES ('KZ','Kazakhstan');
- INSERT INTO country(short_name,name) VALUES ('LA','Laos');
- INSERT INTO country(short_name,name) VALUES ('LB','Lebanon');
- INSERT INTO country(short_name,name) VALUES ('LC','Saint Lucia');
- INSERT INTO country(short_name,name) VALUES ('LI','Liechtenstein');
- INSERT INTO country(short_name,name) VALUES ('LK','Sri Lanka');
- INSERT INTO country(short_name,name) VALUES ('LR','Liberia');
- INSERT INTO country(short_name,name) VALUES ('LS','Lesotho');
- INSERT INTO country(short_name,name) VALUES ('LT','Lithuania');
- INSERT INTO country(short_name,name) VALUES ('LU','Luxembourg');
- INSERT INTO country(short_name,name) VALUES ('LV','Latvia');
- INSERT INTO country(short_name,name) VALUES ('LY','Libya');
- INSERT INTO country(short_name,name) VALUES ('MA','Morocco');
- INSERT INTO country(short_name,name) VALUES ('MC','Monaco');
- INSERT INTO country(short_name,name) VALUES ('MD','Moldova');
- INSERT INTO country(short_name,name) VALUES ('MG','Madagascar');
- INSERT INTO country(short_name,name) VALUES ('MH','Marshall Islands');
- INSERT INTO country(short_name,name) VALUES ('MK','F.Y.R.O.M. (Macedonia)');
- INSERT INTO country(short_name,name) VALUES ('ML','Mali');
- INSERT INTO country(short_name,name) VALUES ('MM','Myanmar');
- INSERT INTO country(short_name,name) VALUES ('MN','Mongolia');
- INSERT INTO country(short_name,name) VALUES ('MO','Macau');
- INSERT INTO country(short_name,name) VALUES ('MP','Northern Mariana Islands');
- INSERT INTO country(short_name,name) VALUES ('MQ','Martinique');
- INSERT INTO country(short_name,name) VALUES ('MR','Mauritania');
- INSERT INTO country(short_name,name) VALUES ('MS','Montserrat');
- INSERT INTO country(short_name,name) VALUES ('MT','Malta');
- INSERT INTO country(short_name,name) VALUES ('MU','Mauritius');
- INSERT INTO country(short_name,name) VALUES ('MV','Maldives');
- INSERT INTO country(short_name,name) VALUES ('MW','Malawi');
- INSERT INTO country(short_name,name) VALUES ('MX','Mexico');
- INSERT INTO country(short_name,name) VALUES ('MY','Malaysia');
- INSERT INTO country(short_name,name) VALUES ('MZ','Mozambique');
- INSERT INTO country(short_name,name) VALUES ('NA','Namibia');
- INSERT INTO country(short_name,name) VALUES ('NC','New Caledonia');
- INSERT INTO country(short_name,name) VALUES ('NE','Niger');
- INSERT INTO country(short_name,name) VALUES ('NF','Norfolk Island');
- INSERT INTO country(short_name,name) VALUES ('NG','Nigeria');
- INSERT INTO country(short_name,name) VALUES ('NI','Nicaragua');
- INSERT INTO country(short_name,name) VALUES ('NL','Netherlands');
- INSERT INTO country(short_name,name) VALUES ('NO','Norway');
- INSERT INTO country(short_name,name) VALUES ('NP','Nepal');
- INSERT INTO country(short_name,name) VALUES ('NR','Nauru');
- INSERT INTO country(short_name,name) VALUES ('NT','Neutral Zone');
- INSERT INTO country(short_name,name) VALUES ('NU','Niue');
- INSERT INTO country(short_name,name) VALUES ('NZ','New Zealand (Aotearoa)');
- INSERT INTO country(short_name,name) VALUES ('OM','Oman');
- INSERT INTO country(short_name,name) VALUES ('PA','Panama');
- INSERT INTO country(short_name,name) VALUES ('PE','Peru');
- INSERT INTO country(short_name,name) VALUES ('PF','French Polynesia');
- INSERT INTO country(short_name,name) VALUES ('PG','Papua New Guinea');
- INSERT INTO country(short_name,name) VALUES ('PH','Philippines');
- INSERT INTO country(short_name,name) VALUES ('PK','Pakistan');
- INSERT INTO country(short_name,name) VALUES ('PL','Poland');
- INSERT INTO country(short_name,name) VALUES ('PM','St. Pierre and Miquelon');
- INSERT INTO country(short_name,name) VALUES ('PN','Pitcairn');
- INSERT INTO country(short_name,name) VALUES ('PR','Puerto Rico');
- INSERT INTO country(short_name,name) VALUES ('PS','Palestinian Territory, Occupied');
- INSERT INTO country(short_name,name) VALUES ('PT','Portugal');
- INSERT INTO country(short_name,name) VALUES ('PW','Palau');
- INSERT INTO country(short_name,name) VALUES ('PY','Paraguay');
- INSERT INTO country(short_name,name) VALUES ('QA','Qatar');
- INSERT INTO country(short_name,name) VALUES ('RE','Reunion');
- INSERT INTO country(short_name,name) VALUES ('RO','Romania');
- INSERT INTO country(short_name,name) VALUES ('RS','Serbia');
- INSERT INTO country(short_name,name) VALUES ('RU','Russian Federation');
- INSERT INTO country(short_name,name) VALUES ('RW','Rwanda');
- INSERT INTO country(short_name,name) VALUES ('SA','Saudi Arabia');
- INSERT INTO country(short_name,name) VALUES ('SB','Solomon Islands');
- INSERT INTO country(short_name,name) VALUES ('SC','Seychelles');
- INSERT INTO country(short_name,name) VALUES ('SD','Sudan');
- INSERT INTO country(short_name,name) VALUES ('SE','Sweden');
- INSERT INTO country(short_name,name) VALUES ('SG','Singapore');
- INSERT INTO country(short_name,name) VALUES ('SH','St. Helena');
- INSERT INTO country(short_name,name) VALUES ('SI','Slovenia');
- INSERT INTO country(short_name,name) VALUES ('SJ','Svalbard & Jan Mayen Islands');
- INSERT INTO country(short_name,name) VALUES ('SK','Slovak Republic');
- INSERT INTO country(short_name,name) VALUES ('SL','Sierra Leone');
- INSERT INTO country(short_name,name) VALUES ('SM','San Marino');
- INSERT INTO country(short_name,name) VALUES ('SN','Senegal');
- INSERT INTO country(short_name,name) VALUES ('SO','Somalia');
- INSERT INTO country(short_name,name) VALUES ('SR','Suriname');
- INSERT INTO country(short_name,name) VALUES ('ST','Sao Tome and Principe');
- INSERT INTO country(short_name,name) VALUES ('SU','USSR (former)');
- INSERT INTO country(short_name,name) VALUES ('SV','El Salvador');
- INSERT INTO country(short_name,name) VALUES ('SY','Syria');
- INSERT INTO country(short_name,name) VALUES ('SZ','Swaziland');
- INSERT INTO country(short_name,name) VALUES ('TC','Turks and Caicos Islands');
- INSERT INTO country(short_name,name) VALUES ('TD','Chad');
- INSERT INTO country(short_name,name) VALUES ('TF','French Southern Territories');
- INSERT INTO country(short_name,name) VALUES ('TG','Togo');
- INSERT INTO country(short_name,name) VALUES ('TH','Thailand');
- INSERT INTO country(short_name,name) VALUES ('TJ','Tajikistan');
- INSERT INTO country(short_name,name) VALUES ('TK','Tokelau');
- INSERT INTO country(short_name,name) VALUES ('TM','Turkmenistan');
- INSERT INTO country(short_name,name) VALUES ('TN','Tunisia');
- INSERT INTO country(short_name,name) VALUES ('TO','Tonga');
- INSERT INTO country(short_name,name) VALUES ('TP','East Timor');
- INSERT INTO country(short_name,name) VALUES ('TR','Turkey');
- INSERT INTO country(short_name,name) VALUES ('TT','Trinidad and Tobago');
- INSERT INTO country(short_name,name) VALUES ('TV','Tuvalu');
- INSERT INTO country(short_name,name) VALUES ('TW','Taiwan');
- INSERT INTO country(short_name,name) VALUES ('TZ','Tanzania');
- INSERT INTO country(short_name,name) VALUES ('UA','Ukraine');
- INSERT INTO country(short_name,name) VALUES ('UG','Uganda');
- INSERT INTO country(short_name,name) VALUES ('UK','United Kingdom');
- INSERT INTO country(short_name,name) VALUES ('UM','US Minor Outlying Islands');
- INSERT INTO country(short_name,name) VALUES ('US','United States');
- INSERT INTO country(short_name,name) VALUES ('UY','Uruguay');
- INSERT INTO country(short_name,name) VALUES ('UZ','Uzbekistan');
- INSERT INTO country(short_name,name) VALUES ('VA','Vatican City State (Holy See)');
- INSERT INTO country(short_name,name) VALUES ('VC','Saint Vincent & the Grenadines');
- INSERT INTO country(short_name,name) VALUES ('VE','Venezuela');
- INSERT INTO country(short_name,name) VALUES ('VG','British Virgin Islands');
- INSERT INTO country(short_name,name) VALUES ('VI','Virgin Islands (U.S.)');
- INSERT INTO country(short_name,name) VALUES ('VN','Viet Nam');
- INSERT INTO country(short_name,name) VALUES ('VU','Vanuatu');
- INSERT INTO country(short_name,name) VALUES ('WF','Wallis and Futuna Islands');
- INSERT INTO country(short_name,name) VALUES ('WS','Samoa');
- INSERT INTO country(short_name,name) VALUES ('YE','Yemen');
- INSERT INTO country(short_name,name) VALUES ('YT','Mayotte');
- INSERT INTO country(short_name,name) VALUES ('YU','Yugoslavia (former)');
- INSERT INTO country(short_name,name) VALUES ('ZA','South Africa');
- INSERT INTO country(short_name,name) VALUES ('ZM','Zambia');
- INSERT INTO country(short_name,name) VALUES ('ZR','Zaire');
- INSERT INTO country(short_name,name) VALUES ('ZW','Zimbabwe');
- --
- CREATE FUNCTION del_yearend() RETURNS TRIGGER AS '
- begin
- delete from yearend where trans_id = old.id;
- return NULL;
- end;
- ' language 'plpgsql';
- -- end function
- --
- CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
- -- end trigger
- --
- CREATE FUNCTION del_department() RETURNS TRIGGER AS '
- begin
- delete from dpt_trans where trans_id = old.id;
- return NULL;
- end;
- ' language 'plpgsql';
- -- end function
- --
- CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department();
- -- end trigger
- CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department();
- -- end trigger
- CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department();
- -- end trigger
- CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
- -- end trigger
- --
- CREATE FUNCTION del_exchangerate() RETURNS TRIGGER AS '
- declare
- t_transdate date;
- t_curr char(3);
- t_id int;
- d_curr text;
- begin
- select into d_curr substr(value,1,3) from defaults where setting_key = ''curr'';
-
- if TG_RELNAME = ''ar'' then
- select into t_curr, t_transdate curr, transdate from ar where id = old.id;
- end if;
- if TG_RELNAME = ''ap'' then
- select into t_curr, t_transdate curr, transdate from ap where id = old.id;
- end if;
- if TG_RELNAME = ''oe'' then
- select into t_curr, t_transdate curr, transdate from oe where id = old.id;
- end if;
- if d_curr != t_curr then
- select into t_id a.id from acc_trans ac
- join ar a on (a.id = ac.trans_id)
- where a.curr = t_curr
- and ac.transdate = t_transdate
- except select a.id from ar a where a.id = old.id
-
- union
-
- select a.id from acc_trans ac
- join ap a on (a.id = ac.trans_id)
- where a.curr = t_curr
- and ac.transdate = t_transdate
-
- except select a.id from ap a where a.id = old.id
-
- union
-
- select o.id from oe o
- where o.curr = t_curr
- and o.transdate = t_transdate
-
- except select o.id from oe o where o.id = old.id;
- if not found then
- delete from exchangerate where curr = t_curr and transdate = t_transdate;
- end if;
- end if;
- return old;
- end;
- ' language 'plpgsql';
- -- end function
- --
- CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
- -- end trigger
- --
- CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
- -- end trigger
- --
- CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
- -- end trigger
- --
- CREATE FUNCTION check_inventory() RETURNS TRIGGER AS '
- declare
- itemid int;
- row_data inventory%rowtype;
- begin
- if not old.quotation then
- for row_data in select * from inventory where trans_id = old.id loop
- select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
- if itemid is null then
- delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
- end if;
- end loop;
- end if;
- return old;
- end;
- ' language 'plpgsql';
- -- end function
- --
- CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory();
- -- end trigger
- --
- --
- CREATE FUNCTION check_department() RETURNS TRIGGER AS '
- declare
- dpt_id int;
- begin
-
- if new.department_id = 0 then
- delete from dpt_trans where trans_id = new.id;
- return NULL;
- end if;
- select into dpt_id trans_id from dpt_trans where trans_id = new.id;
-
- if dpt_id > 0 then
- update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
- else
- insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
- end if;
- return NULL;
- end;
- ' language 'plpgsql';
- -- end function
- --
- CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department();
- -- end trigger
- CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department();
- -- end trigger
- CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department();
- -- end trigger
- CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
- -- end trigger
- --
- CREATE FUNCTION del_recurring() RETURNS TRIGGER AS '
- BEGIN
- DELETE FROM recurring WHERE id = old.id;
- DELETE FROM recurringemail WHERE id = old.id;
- DELETE FROM recurringprint WHERE id = old.id;
- RETURN NULL;
- END;
- ' language 'plpgsql';
- --end function
- CREATE TRIGGER del_recurring AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_recurring();
- -- end trigger
- CREATE TRIGGER del_recurring AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_recurring();
- -- end trigger
- CREATE TRIGGER del_recurring AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_recurring();
- -- end trigger
- --
- CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
- DECLARE
- v_cost float;
- v_qty float;
- v_parts_id alias for $1;
- BEGIN
- SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
- FROM invoice i
- JOIN ap a ON (a.id = i.trans_id)
- WHERE i.parts_id = v_parts_id;
-
- IF v_cost IS NULL THEN
- v_cost := 0;
- END IF;
- IF NOT v_qty IS NULL THEN
- IF v_qty = 0 THEN
- v_cost := 0;
- ELSE
- v_cost := v_cost/v_qty;
- END IF;
- END IF;
- RETURN v_cost;
- END;
- ' language 'plpgsql';
- -- end function
- --
- CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
- DECLARE
- v_cost float;
- v_parts_id alias for $1;
- BEGIN
- SELECT INTO v_cost sellprice FROM invoice i
- JOIN ap a ON (a.id = i.trans_id)
- WHERE i.parts_id = v_parts_id
- ORDER BY a.transdate desc, a.id desc
- LIMIT 1;
- IF v_cost IS NULL THEN
- v_cost := 0;
- END IF;
- RETURN v_cost;
- END;
- ' language plpgsql;
- -- end function
- --
- CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
- AS
- '
- BEGIN
- IF NEW.onhand >= NEW.rop THEN
- NOTIFY parts_short;
- END IF;
- RETURN NEW;
- END;
- ' LANGUAGE PLPGSQL;
- -- end function
- CREATE TRIGGER parts_short AFTER UPDATE ON parts
- FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
- -- end function
- CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
- RETURNS BOOL AS
- '
- DECLARE
- table_name ALIAS FOR $1;
- new_field_name ALIAS FOR $2;
- field_datatype ALIAS FOR $3;
- BEGIN
- EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
- WHERE extends = '''''' || table_name || '''''' '';
- IF NOT FOUND THEN
- BEGIN
- INSERT INTO custom_table_catalog (extends)
- VALUES (table_name);
- EXECUTE ''CREATE TABLE custom_''||table_name ||
- '' (row_id INT PRIMARY KEY)'';
- EXCEPTION WHEN duplicate_table THEN
- -- do nothing
- END;
- END IF;
- EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
- VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
- WHERE extends = ''''''|| table_name || ''''''))'';
- EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
- || new_field_name || '' '' || field_datatype;
- RETURN TRUE;
- END;
- ' LANGUAGE PLPGSQL;
- -- end function
- CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR)
- RETURNS BOOL AS
- '
- DECLARE
- table_name ALIAS FOR $1;
- custom_field_name ALIAS FOR $2;
- BEGIN
- DELETE FROM custom_field_catalog
- WHERE field_name = custom_field_name AND
- table_id = (SELECT table_id FROM custom_table_catalog
- WHERE extends = table_name);
- EXECUTE ''ALTER TABLE custom_'' || table_name ||
- '' DROP COLUMN '' || custom_field_name;
- RETURN TRUE;
- END;
- ' LANGUAGE PLPGSQL;
- -- end function
- CREATE TABLE menu_node (
- id serial NOT NULL,
- label character varying NOT NULL,
- parent integer,
- "position" integer NOT NULL
- );
- ALTER TABLE public.menu_node OWNER TO ledgersmb;
- --
- -- Name: menu_node_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb
- --
- SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_node', 'id'), 193, true);
- --
- -- Data for Name: menu_node; Type: TABLE DATA; Schema: public; Owner: ledgersmb
- --
- COPY menu_node (id, label, parent, "position") FROM stdin;
- 0 Top-level \N 0
- 1 AR 0 1
- 2 Add Transaction 1 1
- 3 Sales Invoice 1 2
- 5 Transactions 4 1
- 6 Outstanding 4 2
- 7 AR Aging 4 3
- 9 Taxable Sales 4 4
- 10 Non-Taxable 4 5
- 12 Add Customer 11 1
- 13 Reports 11 2
- 14 Search 13 1
- 15 History 13 2
- 16 Point of Sale 0 2
- 17 Sale 16 1
- 18 Open 16 2
- 19 Receipts 16 3
- 20 Close Till 16 4
- 21 AP 0 3
- 22 Add Transaction 21 1
- 23 Vendor Invoice 21 2
- 25 Transactions 24 1
- 26 Outstanding 24 2
- 27 AP Aging 24 3
- 28 Taxable 24 4
- 29 Non-taxable 24 5
- 31 Add Vendor 30 1
- 32 Reports 30 2
- 33 Search 32 1
- 34 History 32 2
- 35 Cash 0 4
- 36 Receipt 35 1
- 38 Payment 35 3
- 37 Receipts 35 2
- 39 Payments 35 4
- 40 Transfer 35 5
- 42 Receipts 41 1
- 43 Payments 41 2
- 44 Reconciliation 41 3
- 41 Reports 35 7
- 45 Reconciliation 35 6
- 46 HR 0 5
- 47 Employees 46 1
- 48 Add Employee 47 1
- 49 Search 47 2
- 50 Order Entry 0 6
- 51 Sales Order 50 1
- 52 Purchase Order 50 2
- 53 Reports 50 3
- 54 Sales Orders 53 1
- 55 Purchase Orders 53 2
- 57 Sales Orders 56 1
- 58 Purchase Orders 56 2
- 56 Generate 50 4
- 60 Consolidate 50 5
- 61 Sales Orders 60 1
- 62 Purchase Orders 60 2
- 63 Shipping 0 7
- 64 Ship 63 1
- 65 Receive 63 2
- 66 Transfer 63 3
- 67 Quotations 0 8
- 68 Quotation 67 1
- 69 RFQ 67 2
- 70 Reports 67 3
- 71 Quotations 70 1
- 72 RFQs 70 2
- 73 General Journal 0 9
- 74 Journal Entry 73 1
- 75 Adjust Till 73 2
- 76 Reports 73 3
- 77 Goods and Services 0 10
- 78 Add Part 77 1
- 79 Add Service 77 2
- 80 Add Assembly 77 3
- 81 Add Overhead 77 4
- 82 Add Group 77 5
- 83 Add Pricegroup 77 6
- 84 Stock Assembly 77 7
- 85 Reports 77 8
- 86 All Items 85 1
- 87 Parts 85 2
- 88 Requirements 85 3
- 89 Services 85 4
- 90 Labor 85 5
- 91 Groups 85 6
- 92 Pricegroups 85 7
- 93 Assembly 85 8
- 94 Components 85 9
- 95 Translations 77 9
- 96 Description 95 1
- 97 Partsgroup 95 2
- 98 Projects 0 11
- 99 Add Project 98 1
- 100 Add Timecard 98 2
- 101 Generate 98 3
- 102 Sales Orders 101 1
- 103 Reports 98 4
- 104 Search 103 1
- 105 Transactions 103 2
- 106 Time Cards 103 3
- 107 Translations 98 5
- 108 Description 107 1
- 109 Reports 0 12
- 110 Chart of Accounts 109 1
- 111 Trial Balance 109 2
- 112 Income Statement 109 3
- 113 Balance Sheet 109 4
- 114 Inventory Activity 109 5
- 115 Recurring Transactions 0 13
- 116 Batch Printing 0 14
- 117 Sales Invoices 116 1
- 118 Sales Orders 116 2
- 119 Checks 116 3
- 120 Work Orders 116 4
- 121 Quotations 116 5
- 122 Packing Lists 116 6
- 123 Pick Lists 116 7
- 124 Purchase Orders 116 8
- 125 Bin Lists 116 9
- 126 RFQs 116 10
- 127 Time Cards 116 11
- 128 System 0 15
- 129 Audit Control 128 1
- 130 Taxes 128 2
- 131 Defaults 128 3
- 132 Yearend 128 4
- 133 Backup 128 5
- 134 Send to File 133 1
- 135 Send to Email 133 2
- 136 Chart of Accounts 128 6
- 137 Add Accounts 136 1
- 138 List Accounts 136 2
- 139 Add GIFI 136 3
- 140 List GIFI 136 4
- 141 Warehouses 128 7
- 142 Add Warehouse 141 1
- 143 List Warehouse 141 2
- 144 Departments 128 8
- 145 Add Department 144 1
- 146 List Departments 144 2
- 147 Type of Business 128 9
- 148 Add Business 147 1
- 149 List Businesses 147 2
- 150 Language 128 10
- 151 Add Language 150 1
- 152 List Languages 150 2
- 153 SIC 128 11
- 154 Add SIC 153 1
- 155 List SIC 153 2
- 156 HTML Templates 128 12
- 157 Income Statement 156 1
- 158 Balance Sheet 156 2
- 159 Invoice 156 3
- 160 AR Transaction 156 4
- 161 AP Transaction 156 5
- 162 Packing List 156 6
- 163 Pick List 156 7
- 164 Sales Order 156 8
- 165 Work Order 156 9
- 166 Purchase Order 156 10
- 167 Bin List 156 11
- 168 Statement 156 12
- 169 Quotation 156 13
- 170 RFQ 156 14
- 171 Timecard 156 15
- 172 LaTeX Templates 128 13
- 173 Invoice 172 1
- 174 AR Transaction 172 2
- 175 AP Transaction 172 3
- 176 Packing List 172 4
- 177 Pick List 172 5
- 178 Sales Order 172 6
- 179 Work Order 172 7
- 180 Purchase Order 172 8
- 181 Bin List 172 9
- 182 Statement 172 10
- 183 Check 172 11
- 184 Receipt 172 12
- 185 Quotation 172 13
- 186 RFQ 172 14
- 187 Timecard 172 15
- 188 Text Templates 128 14
- 189 POS Invoice 188 1
- 190 Stylesheet 0 16
- 191 Preferences 0 17
- 192 New Window 0 18
- 193 Logout 0 19
- 11 Customers 1 6
- 4 Reports 1 5
- 194 Credit Note 1 3
- 195 Credit Invoice 1 4
- 24 Reports 21 5
- 30 Vendors 21 6
- 196 Debit Note 21 3
- 197 Debit Invoice 21 4
- \.
- --
- -- Name: menu_node_parent_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
- --
- ALTER TABLE ONLY menu_node
- ADD CONSTRAINT menu_node_parent_key UNIQUE (parent, "position");
- --
- -- Name: menu_node_pkey; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
- --
- ALTER TABLE ONLY menu_node
- ADD CONSTRAINT menu_node_pkey PRIMARY KEY (id);
- --
- -- Name: menu_node_parent_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ledgersmb
- --
- ALTER TABLE ONLY menu_node
- ADD CONSTRAINT menu_node_parent_fkey FOREIGN KEY (parent) REFERENCES menu_node(id);
- CREATE TABLE menu_attribute (
- node_id integer NOT NULL,
- attribute character varying NOT NULL,
- value character varying NOT NULL,
- id serial NOT NULL
- );
- --
- -- Name: menu_attribute_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb
- --
- SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 551, true);
- --
- -- Data for Name: menu_attribute; Type: TABLE DATA; Schema: public; Owner: ledgersmb
- --
- COPY menu_attribute (node_id, attribute, value, id) FROM stdin;
- 1 menu 1 1
- 2 module ar.pl 2
- 2 action add 3
- 3 action add 4
- 3 module is.pl 5
- 3 type invoice 6
- 4 menu 1 7
- 5 module ar.pl 8
- 5 action search 9
- 5 nextsub transactions 10
- 6 module ar.pl 12
- 6 action search 13
- 6 nextsub transactions 14
- 7 module rp.pl 15
- 7 action report 16
- 7 report ar_aging 17
- 9 module rp.pl 21
- 9 action report 22
- 9 report tax_collected 23
- 10 module rp.pl 24
- 10 action report 25
- 10 report nontaxable_sales 26
- 11 menu 1 27
- 12 module ct.pl 28
- 12 action add 29
- 12 db customer 30
- 13 menu 1 31
- 14 module ct.pl 32
- 14 db customer 34
- 15 module ct.pl 35
- 15 action add 36
- 15 db customer 37
- 14 action history 33
- 16 menu 1 38
- 17 module ps.pl 39
- 17 action add 40
- 17 nextsub openinvoices 41
- 18 action openinvoices 42
- 18 module ps.pl 43
- 19 module ps.pl 44
- 19 action receipts 46
- 20 module rc.pl 47
- 20 action till_closing 48
- 20 pos true 49
- 21 menu 1 50
- 22 action add 52
- 22 module ap.pl 51
- 23 action add 53
- 23 type invoice 55
- 23 module ir.pl 54
- 24 menu 1 56
- 25 action search 58
- 25 nextsub transactions 59
- 25 module ap.pl 57
- 26 action search 61
- 26 nextsub transactions 62
- 26 module ap.pl 60
- 27 module rp.pl 63
- 27 action report 64
- 28 module rp.pl 66
- 28 action report 67
- 28 report tax_collected 68
- 27 report tax_paid 65
- 29 module rp.pl 69
- 29 action report 70
- 29 report report 71
- 30 menu 1 72
- 31 module ct.pl 73
- 31 action add 74
- 31 db vendor 75
- 32 menu 1 76
- 33 module ct.pl 77
- 33 action history 79
- 33 db vendor 78
- 34 module ct.pl 80
- 34 action add 81
- 34 db vendor 82
- 35 menu 1 83
- 36 module payment.pl 84
- 36 action payment 85
- 36 type receipt 86
- 37 module cp.pl 87
- 38 module cp.pl 90
- 38 action payment 91
- 37 type receipt 89
- 37 action payments 88
- 38 type check 92
- 39 module cp.pl 93
- 39 type check 95
- 39 action payments 94
- 40 module gl.pl 96
- 40 action add 97
- 40 transfer 1 98
- 41 menu 1 99
- 42 module rp.pl 100
- 42 action report 101
- 42 report receipts 102
- 43 module rp.pl 103
- 43 action report 104
- 43 report payments 105
- 45 module rc.pl 106
- 45 action reconciliation 107
- 44 module rc.pl 108
- 44 action reconciliation 109
- 44 report 1 110
- 46 menu 1 111
- 47 menu 1 112
- 48 module hr.pl 113
- 48 action add 114
- 48 db employee 115
- 49 module hr.pl 116
- 49 db employee 118
- 49 action search 117
- 50 menu 1 119
- 51 module oe.pl 120
- 51 action add 121
- 51 type sales_order 122
- 52 module oe.pl 123
- 52 action add 124
- 52 type purchase_order 125
- 53 menu 1 126
- 54 module oe.pl 127
- 54 type sales_order 129
- 54 action search 128
- 55 module oe.pl 130
- 55 type purchase_order 132
- 55 action search 131
- 56 menu 1 133
- 57 module oe.pl 134
- 57 action search 136
- 58 module oe.pl 137
- 58 action search 139
- 57 type generate_sales_order 135
- 58 type generate_purchase_order 138
- 60 menu 1 550
- 61 module oe.pl 140
- 61 action search 141
- 62 module oe.pl 143
- 62 action search 144
- 62 type consolidate_purchase_order 145
- 61 type consolidate_sales_order 142
- 63 menu 1 146
- 64 module oe.pl 147
- 64 action search 148
- 65 module oe.pl 150
- 65 action search 151
- 65 type consolidate_sales_order 152
- 64 type receive_order 149
- 66 module oe.pl 153
- 66 action search_transfer 154
- 67 menu 1 155
- 68 module oe.pl 156
- 68 action add 157
- 69 module oe.pl 159
- 69 action add 160
- 68 type sales_quotation 158
- 69 type request_quotation 161
- 70 menu 1 162
- 71 module oe.pl 163
- 71 type sales_quotation 165
- 71 action search 164
- 72 module oe.pl 166
- 72 action search 168
- 72 type request_quotation 167
- 73 menu 1 169
- 74 module gl.pl 170
- 74 action add 171
- 75 module gl.pl 172
- 75 action add_pos_adjust 174
- 75 rowcount 3 175
- 75 pos_adjust 1 176
- 75 reference Adjusting Till: (Till) Source: (Source) 177
- 75 descripton Adjusting till due to data entry error 178
- 76 module gl.pl 180
- 76 action search 181
- 77 menu 1 182
- 78 module ic.pl 183
- 78 action add 184
- 78 item part 185
- 79 module ic.pl 186
- 79 action add 187
- 79 item service 188
- 80 module ic.pl 189
- 80 action add 190
- 81 module ic.pl 192
- 81 action add 193
- 81 item part 194
- 80 item labor 191
- 82 action add 195
- 82 module pe.pl 196
- 83 action add 198
- 83 module pe.pl 199
- 83 type partsgroup 200
- 82 type pricegroup 197
- 84 module ic.pl 202
- 84 action stock_assembly 203
- 85 menu 1 204
- 86 module ic.pl 205
- 87 action search 206
- 86 action search 207
- 87 module ic.pl 208
- 86 searchitems all 209
- 88 module ic.pl 211
- 88 action requirements 212
- 89 action search 213
- 89 module ic.pl 214
- 89 searchitems service 215
- 87 searchitems part 210
- 90 action search 216
- 90 module ic.pl 217
- 90 searchitems labor 218
- 91 module pe.pl 221
- 91 type pricegroup 222
- 91 action search 220
- 92 module pe.pl 224
- 92 type partsgroup 225
- 92 action search 223
- 93 action search 226
- 93 module ic.pl 227
- 93 searchitems assembly 228
- 94 action search 229
- 94 module ic.pl 230
- 94 searchitems component 231
- 95 menu 1 232
- 96 module pe.pl 233
- 96 action translation 234
- 96 translation description 235
- 97 module pe.pl 236
- 97 action translation 237
- 97 translation partsgroup 238
- 98 menu 1 239
- 99 module pe.pl 240
- 99 action add 241
- 99 type project 242
- 100 module jc.pl 243
- 100 action add 244
- 99 project project 245
- 100 project project 246
- 100 type timecard 247
- 101 menu 1 248
- 102 module pe.pl 249
- 102 action project_sales_order 250
- 103 menu 1 255
- 104 module pe.pl 256
- 104 type project 258
- 104 action search 257
- 105 action report 260
- 105 report projects 261
- 105 module rp.pl 262
- 106 module jc.pl 263
- 106 action search 264
- 106 type timecard 265
- 106 project project 266
- 107 menu 1 268
- 108 module pe.pl 269
- 108 action translation 270
- 108 translation project 271
- 109 menu 1 272
- 110 module ca.pl 273
- 110 action chart_of_accounts 274
- 111 action report 275
- 111 module rp.pl 276
- 111 report trial_balance 277
- 112 action report 278
- 112 module rp.pl 279
- 112 report income_statement 280
- 113 action report 281
- 113 module rp.pl 282
- 113 report balance_sheet 283
- 114 action report 284
- 114 module rp.pl 285
- 114 report inv_activity 286
- 115 action recurring_transactions 287
- 115 module am.pl 288
- 116 menu 1 289
- 119 module bp.pl 290
- 119 action search 291
- 119 type check 292
- 119 vc vendor 293
- 117 module bp.pl 294
- 117 action search 295
- 117 vc customer 297
- 118 module bp.pl 298
- 118 action search 299
- 118 vc customer 300
- 118 type invoice 301
- 117 type sales_order 296
- 120 module bp.pl 302
- 120 action search 303
- 120 vc customer 304
- 121 module bp.pl 306
- 121 action search 307
- 121 vc customer 308
- 122 module bp.pl 310
- 122 action search 311
- 122 vc customer 312
- 120 type work_order 305
- 121 type sales_quotation 309
- 122 type packing_list 313
- 123 module bp.pl 314
- 123 action search 315
- 123 vc customer 316
- 123 type pick_list 317
- 124 module bp.pl 318
- 124 action search 319
- 124 vc vendor 321
- 124 type purchase_order 320
- 125 module bp.pl 322
- 125 action search 323
- 125 vc vendor 325
- 126 module bp.pl 326
- 126 action search 327
- 126 vc vendor 329
- 127 module bp.pl 330
- 127 action search 331
- 127 type timecard 332
- 125 type bin_list 324
- 126 type request_quotation 328
- 127 vc employee 333
- 128 menu 1 334
- 129 module am.pl 337
- 130 module am.pl 338
- 131 module am.pl 339
- 129 action audit_control 340
- 130 taxes audit_control 341
- 131 action defaults 342
- 130 action taxes 343
- 132 module am.pl 346
- 132 action yearend 347
- 133 menu 1 348
- 134 module am.pl 349
- 135 module am.pl 350
- 134 action backup 351
- 135 action backup 352
- 134 media file 353
- 135 media email 354
- 137 module am.pl 355
- 138 module am.pl 356
- 139 module am.pl 357
- 140 module am.pl 358
- 137 action add_account 359
- 138 action list_account 360
- 139 action add_gifi 361
- 140 action list_gifi 362
- 141 menu 1 363
- 142 module am.pl 364
- 143 module am.pl 365
- 142 action add_warehouse 366
- 143 action list_warehouse 367
- 145 module am.pl 368
- 146 module am.pl 369
- 145 action add_department 370
- 146 action list_department 371
- 147 menu 1 372
- 148 module am.pl 373
- 149 module am.pl 374
- 148 action add_business 375
- 149 action list_business 376
- 150 menu 1 377
- 151 module am.pl 378
- 152 module am.pl 379
- 151 action add_language 380
- 152 action list_language 381
- 153 menu 1 382
- 154 module am.pl 383
- 155 module am.pl 384
- 154 action add_sic 385
- 155 action list_sic 386
- 156 menu 1 387
- 157 module am.pl 388
- 158 module am.pl 389
- 159 module am.pl 390
- 160 module am.pl 391
- 161 module am.pl 392
- 162 module am.pl 393
- 163 module am.pl 394
- 164 module am.pl 395
- 165 module am.pl 396
- 166 module am.pl 397
- 167 module am.pl 398
- 168 module am.pl 399
- 169 module am.pl 400
- 170 module am.pl 401
- 171 module am.pl 402
- 157 action list_templates 403
- 158 action list_templates 404
- 159 action list_templates 405
- 160 action list_templates 406
- 161 action list_templates 407
- 162 action list_templates 408
- 163 action list_templates 409
- 164 action list_templates 410
- 165 action list_templates 411
- 166 action list_templates 412
- 167 action list_templates 413
- 168 action list_templates 414
- 169 action list_templates 415
- 170 action list_templates 416
- 171 action list_templates 417
- 157 template income_statement 418
- 158 template balance_sheet 419
- 159 template invoice 420
- 160 template ar_transaction 421
- 161 template ap_transaction 422
- 162 template packing_list 423
- 163 template pick_list 424
- 164 template sales_order 425
- 165 template work_order 426
- 166 template purchase_order 427
- 167 template bin_list 428
- 168 template statement 429
- 169 template quotation 430
- 170 template rfq 431
- 171 template timecard 432
- 157 format HTML 433
- 158 format HTML 434
- 159 format HTML 435
- 160 format HTML 436
- 161 format HTML 437
- 162 format HTML 438
- 163 format HTML 439
- 164 format HTML 440
- 165 format HTML 441
- 166 format HTML 442
- 167 format HTML 443
- 168 format HTML 444
- 169 format HTML 445
- 170 format HTML 446
- 171 format HTML 447
- 172 menu 1 448
- 173 action list_templates 449
- 174 action list_templates 450
- 175 action list_templates 451
- 176 action list_templates 452
- 177 action list_templates 453
- 178 action list_templates 454
- 179 action list_templates 455
- 180 action list_templates 456
- 181 action list_templates 457
- 182 action list_templates 458
- 183 action list_templates 459
- 184 action list_templates 460
- 185 action list_templates 461
- 186 action list_templates 462
- 187 action list_templates 463
- 173 module am.pl 464
- 174 module am.pl 465
- 175 module am.pl 466
- 176 module am.pl 467
- 177 module am.pl 468
- 178 module am.pl 469
- 179 module am.pl 470
- 180 module am.pl 471
- 181 module am.pl 472
- 182 module am.pl 473
- 183 module am.pl 474
- 184 module am.pl 475
- 185 module am.pl 476
- 186 module am.pl 477
- 187 module am.pl 478
- 173 format LATEX 479
- 174 format LATEX 480
- 175 format LATEX 481
- 176 format LATEX 482
- 177 format LATEX 483
- 178 format LATEX 484
- 179 format LATEX 485
- 180 format LATEX 486
- 181 format LATEX 487
- 182 format LATEX 488
- 183 format LATEX 489
- 184 format LATEX 490
- 185 format LATEX 491
- 186 format LATEX 492
- 187 format LATEX 493
- 173 template invoice 506
- 174 template ar_transaction 507
- 175 template ap_transaction 508
- 176 template packing_list 509
- 177 template pick_list 510
- 178 template sales_order 511
- 179 template work_order 512
- 180 template purchase_order 513
- 181 template bin_list 514
- 182 template statement 515
- 185 template quotation 518
- 186 template rfq 519
- 187 template timecard 520
- 183 template check 516
- 184 template receipt 517
- 188 menu 1 521
- 189 module am.pl 522
- 189 action list_templates 523
- 189 template pos_invoice 524
- 189 format TEXT 525
- 190 action display_stylesheet 526
- 190 module am.pl 527
- 191 module am.pl 528
- 191 action config 529
- 193 module login.pl 532
- 193 action logout 533
- 193 target _top 534
- 192 menu 1 530
- 192 new 1 531
- 0 menu 1 535
- 136 menu 1 536
- 144 menu 1 537
- 194 module ar.pl 538
- 194 action add 539
- 195 action add 540
- 195 module is.pl 541
- 196 action add 543
- 196 module ap.pl 544
- 197 action add 545
- 197 module ir.pl 547
- 196 type debit_note 549
- 194 type credit_note 548
- 195 type credit_invoice 542
- 197 type debit_invoice 546
- 36 account_class 1 551
- \.
- --
- -- Name: menu_attribute_id_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
- --
- ALTER TABLE ONLY menu_attribute
- ADD CONSTRAINT menu_attribute_id_key UNIQUE (id);
- --
- -- Name: menu_attribute_pkey; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace:
- --
- ALTER TABLE ONLY menu_attribute
- ADD CONSTRAINT menu_attribute_pkey PRIMARY KEY (node_id, attribute);
- --
- -- Name: menu_attribute_node_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ledgersmb
- --
- ALTER TABLE ONLY menu_attribute
- ADD CONSTRAINT menu_attribute_node_id_fkey FOREIGN KEY (node_id) REFERENCES menu_node(id);
- --
- -- PostgreSQL database dump complete
- --
- --
- CREATE TABLE menu_acl (
- id serial NOT NULL,
- role_name character varying,
- acl_type character varying,
- node_id integer,
- CONSTRAINT menu_acl_acl_type_check CHECK ((((acl_type)::text = 'allow'::text) OR ((acl_type)::text = 'deny'::text)))
- );
- ALTER TABLE ONLY menu_acl
- ADD CONSTRAINT menu_acl_pkey PRIMARY KEY (id);
- ALTER TABLE ONLY menu_acl
- ADD CONSTRAINT menu_acl_node_id_fkey FOREIGN KEY (node_id) REFERENCES menu_node(id);
- --
- -- PostgreSQL database dump complete
- --
- CREATE TYPE menu_item AS (
- position int,
- id int,
- level int,
- label varchar,
- path varchar,
- args varchar[]
- );
- CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS
- $$
- DECLARE
- item menu_item;
- arg menu_attribute%ROWTYPE;
-
- BEGIN
- FOR item IN
- SELECT n.position, n.id, c.level, n.label, c.path, '{}'
- FROM connectby('menu_node', 'id', 'parent', 'position', '0',
- 0, ',')
- c(id integer, parent integer, "level" integer,
- path text, list_order integer)
- JOIN menu_node n USING(id)
- LOOP
- FOR arg IN
- SELECT *
- FROM menu_attribute
- WHERE node_id = item.id
- LOOP
- item.args := item.args ||
- (arg.attribute || '=' || arg.value)::varchar;
- END LOOP;
- RETURN NEXT item;
- END LOOP;
- END;
- $$ language plpgsql;
- CREATE OR REPLACE FUNCTION menu_children(in_parent_id int) RETURNS SETOF menu_item
- AS $$
- declare
- item menu_item;
- arg menu_attribute%ROWTYPE;
- begin
- FOR item IN
- SELECT n.position, n.id, c.level, n.label, c.path, '{}'
- FROM connectby('menu_node', 'id', 'parent', 'position',
- in_parent_id, 1, ',')
- c(id integer, parent integer, "level" integer,
- path text, list_order integer)
- JOIN menu_node n USING(id)
- LOOP
- FOR arg IN
- SELECT *
- FROM menu_attribute
- WHERE node_id = item.id
- LOOP
- item.args := item.args ||
- (arg.attribute || '=' || arg.value)::varchar;
- END LOOP;
- return next item;
- end loop;
- end;
- $$ language plpgsql;
- COMMENT ON FUNCTION menu_children(int) IS $$ This function returns all menu items which are children of in_parent_id (the only input parameter. $$;
- CREATE OR REPLACE FUNCTION
- menu_insert(in_parent_id int, in_position int, in_label text)
- returns int
- AS $$
- DECLARE
- new_id int;
- BEGIN
- UPDATE menu_node
- SET position = position * -1
- WHERE parent = in_parent_id
- AND position >= in_position;
- INSERT INTO menu_node (parent, position, label)
- VALUES (in_parent_id, in_position, in_label);
- SELECT INTO new_id currval('menu_node_id_seq');
- UPDATE menu_node
- SET position = (position * -1) + 1
- WHERE parent = in_parent_id
- AND position < 0;
- RETURN new_id;
- END;
- $$ language plpgsql;
- comment on function menu_insert(int, int, text) is $$
- This function inserts menu items at arbitrary positions. The arguments are, in
- order: parent, position, label. The return value is the id number of the menu
- item created. $$;
- CREATE VIEW menu_friendly AS
- SELECT t."level", t.path, t.list_order, (repeat(' '::text, (2 * t."level")) || (n.label)::text) AS label, n.id, n."position" FROM (connectby('menu_node'::text, 'id'::text, 'parent'::text, 'position'::text, '0'::text, 0, ','::text) t(id integer, parent integer, "level" integer, path text, list_order integer) JOIN menu_node n USING (id));
- ALTER TABLE public.menu_friendly OWNER TO ledgersmb;
- --
- -- PostgreSQL database dump complete
- --
- CREATE AGGREGATE as_array (
- BASETYPE = ANYELEMENT,
- STYPE = ANYARRAY,
- SFUNC = ARRAY_APPEND,
- INITCOND = '{}'
- );
- CREATE AGGREGATE compound_array (
- BASETYPE = ANYARRAY,
- STYPE = ANYARRAY,
- SFUNC = ARRAY_CAT,
- INITCOND = '{}'
- );
- CREATE TABLE pending_reports (
- id bigserial primary key not null,
- report_id int,
- scn int,
- their_balance INT,
- our_balance INT,
- errorcode INT,
- entered_by int references entity(id) not null,
- corrections INT NOT NULL DEFAULT 0,
- clear_time TIMESTAMP NOT NULL,
- insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
- ledger_id int REFERENCES acc_trans(entry_id),
- overlook boolean not null default 'f'
- );
- CREATE TABLE report_corrections (
- id serial primary key not null,
- correction_id int not null default 1,
- entry_in int references pending_reports(id) not null,
- entered_by int not null,
- reason text not null,
- insert_time timestamptz not null default now()
- );
- CREATE INDEX company_name_gist__idx ON company USING gist(legal_name gist_trgm_ops);
- CREATE INDEX location_address_one_gist__idx ON location USING gist(line_one gist_trgm_ops);
- CREATE INDEX location_address_two_gist__idx ON location USING gist(line_two gist_trgm_ops);
- CREATE INDEX location_address_three_gist__idx ON location USING gist(line_three gist_trgm_ops);
-
- CREATE INDEX location_city_prov_gist_idx ON location USING gist(city gist_trgm_ops);
- CREATE INDEX entity_name_gist_idx ON entity USING gist(name gist_trgm_ops);
- commit;
|