1-- 2CREATE SEQUENCE id start 10000; 3SELECT nextval('id'); 4-- 5CREATE SEQUENCE invoiceid; 6SELECT nextval('invoiceid'); 7-- 8CREATE SEQUENCE orderitemsid; 9SELECT nextval('orderitemsid'); 10-- 11CREATE SEQUENCE jcitemsid; 12SELECT nextval('jcitemsid'); 13-- 14CREATE SEQUENCE addressid; 15SELECT nextval('addressid'); 16-- 17CREATE SEQUENCE assemblyid; 18SELECT nextval('assemblyid'); 19-- 20CREATE SEQUENCE inventoryid; 21SELECT nextval('inventoryid'); 22-- 23CREATE SEQUENCE contactid; 24SELECT nextval('contactid'); 25-- 26CREATE SEQUENCE referenceid; 27SELECT nextval('referenceid'); 28-- 29CREATE SEQUENCE archiveid; 30SELECT nextval('archiveid'); 31-- 32CREATE TABLE makemodel ( 33 parts_id int, 34 make text, 35 model text 36); 37-- 38CREATE TABLE gl ( 39 id int DEFAULT nextval('id'), 40 reference text, 41 description text, 42 transdate date DEFAULT current_date, 43 employee_id int, 44 notes text, 45 department_id int DEFAULT 0, 46 approved bool DEFAULT 't', 47 curr char(3), 48 exchangerate float 49); 50-- 51CREATE TABLE chart ( 52 id int DEFAULT nextval('id'), 53 accno text NOT NULL, 54 description text, 55 charttype char(1) DEFAULT 'A', 56 category char(1), 57 link text, 58 gifi_accno text, 59 contra bool DEFAULT 'f', 60 closed bool DEFAULT 'f' 61); 62-- 63CREATE TABLE gifi ( 64 accno text, 65 description text 66); 67-- 68CREATE TABLE defaults ( 69 fldname text, 70 fldvalue text 71); 72-- 73INSERT INTO defaults (fldname, fldvalue) VALUES ('version', '3.2.3'); 74-- 75CREATE TABLE acc_trans ( 76 trans_id int, 77 chart_id int, 78 amount float, 79 transdate date DEFAULT current_date, 80 source text, 81 approved bool DEFAULT 't', 82 fx_transaction bool DEFAULT 'f', 83 project_id int, 84 memo text, 85 id int, 86 cleared date, 87 vr_id int 88); 89-- 90CREATE TABLE invoice ( 91 id int DEFAULT nextval('invoiceid') primary key, 92 trans_id int, 93 parts_id int, 94 description text, 95 qty float, 96 allocated float, 97 sellprice float, 98 fxsellprice float, 99 discount float4, 100 assemblyitem bool DEFAULT 'f', 101 unit varchar(5), 102 project_id int, 103 deliverydate date, 104 serialnumber text, 105 itemnotes text, 106 lineitemdetail bool, 107 ordernumber text, 108 ponumber text, 109 cost float, 110 vendor text, 111 vendor_id int, 112 kititem bool DEFAULT 'f' 113); 114-- 115CREATE TABLE customer ( 116 id int DEFAULT nextval('id') primary key, 117 name varchar(64), 118 contact varchar(64), 119 phone varchar(20), 120 fax varchar(20), 121 email text, 122 notes text, 123 terms int2 DEFAULT 0, 124 taxincluded bool DEFAULT 'f', 125 customernumber varchar(32), 126 cc text, 127 bcc text, 128 business_id int, 129 taxnumber varchar(32), 130 sic_code varchar(6), 131 discount float4, 132 creditlimit float DEFAULT 0, 133 employee_id int, 134 language_code varchar(6), 135 pricegroup_id int, 136 curr char(3), 137 startdate date, 138 enddate date, 139 arap_accno_id int, 140 payment_accno_id int, 141 discount_accno_id int, 142 cashdiscount float4, 143 discountterms int2, 144 threshold float, 145 paymentmethod_id int, 146 remittancevoucher bool, 147 prepayment_accno_id int 148); 149-- 150CREATE TABLE parts ( 151 id int DEFAULT nextval('id'), 152 partnumber text, 153 description text, 154 unit varchar(5), 155 listprice float, 156 sellprice float, 157 lastcost float, 158 priceupdate date DEFAULT current_date, 159 weight float, 160 onhand float DEFAULT 0, 161 notes text, 162 makemodel bool DEFAULT 'f', 163 assembly bool DEFAULT 'f', 164 alternate bool DEFAULT 'f', 165 rop float, 166 inventory_accno_id int, 167 income_accno_id int, 168 expense_accno_id int, 169 bin text, 170 obsolete bool DEFAULT 'f', 171 bom bool DEFAULT 'f', 172 image text, 173 drawing text, 174 microfiche text, 175 partsgroup_id int, 176 project_id int, 177 avgcost float, 178 tariff_hscode text, 179 countryorigin text, 180 barcode text, 181 toolnumber text, 182 lot text, 183 expires date, 184 checkinventory bool default 'f' 185); 186-- 187CREATE TABLE assembly ( 188 id int DEFAULT nextval('assemblyid'), 189 parts_id int, 190 qty float, 191 bom bool, 192 adj bool, 193 aid int 194); 195-- 196CREATE TABLE ar ( 197 id int DEFAULT nextval('id'), 198 invnumber text, 199 transdate date DEFAULT current_date, 200 customer_id int, 201 taxincluded bool, 202 amount float, 203 netamount float, 204 paid float, 205 datepaid date, 206 duedate date, 207 invoice bool DEFAULT 'f', 208 shippingpoint text, 209 terms int2 DEFAULT 0, 210 notes text, 211 curr char(3), 212 ordnumber text, 213 employee_id int, 214 till varchar(20), 215 quonumber text, 216 intnotes text, 217 department_id int DEFAULT 0, 218 shipvia text, 219 language_code varchar(6), 220 ponumber text, 221 approved bool DEFAULT 't', 222 cashdiscount float4, 223 discountterms int2, 224 waybill text, 225 warehouse_id int, 226 description text, 227 onhold bool DEFAULT 'f', 228 exchangerate float, 229 dcn text, 230 bank_id int, 231 paymentmethod_id int 232); 233-- 234CREATE TABLE ap ( 235 id int DEFAULT nextval('id'), 236 invnumber text, 237 transdate date DEFAULT current_date, 238 vendor_id int, 239 taxincluded bool DEFAULT 'f', 240 amount float, 241 netamount float, 242 paid float, 243 datepaid date, 244 duedate date, 245 invoice bool DEFAULT 'f', 246 ordnumber text, 247 curr char(3), 248 notes text, 249 employee_id int, 250 till varchar(20), 251 quonumber text, 252 intnotes text, 253 department_id int DEFAULT 0, 254 shipvia text, 255 language_code varchar(6), 256 ponumber text, 257 shippingpoint text, 258 terms int2 DEFAULT 0, 259 approved bool DEFAULT 't', 260 cashdiscount float4, 261 discountterms int2, 262 waybill text, 263 warehouse_id int, 264 description text, 265 onhold bool DEFAULT 'f', 266 exchangerate float, 267 dcn text, 268 bank_id int, 269 paymentmethod_id int 270); 271-- 272CREATE TABLE partstax ( 273 parts_id int, 274 chart_id int 275); 276-- 277CREATE TABLE tax ( 278 chart_id int, 279 rate float, 280 taxnumber text, 281 validto date 282); 283-- 284CREATE TABLE customertax ( 285 customer_id int, 286 chart_id int 287); 288-- 289CREATE TABLE vendortax ( 290 vendor_id int, 291 chart_id int 292); 293-- 294CREATE TABLE oe ( 295 id int DEFAULT nextval('id'), 296 ordnumber text, 297 transdate date DEFAULT current_date, 298 vendor_id int, 299 customer_id int, 300 amount float, 301 netamount float, 302 reqdate date, 303 taxincluded bool, 304 shippingpoint text, 305 notes text, 306 curr char(3), 307 employee_id int, 308 closed bool DEFAULT 'f', 309 quotation bool DEFAULT 'f', 310 quonumber text, 311 intnotes text, 312 department_id int DEFAULT 0, 313 shipvia text, 314 language_code varchar(6), 315 ponumber text, 316 terms int2 DEFAULT 0, 317 waybill text, 318 warehouse_id int, 319 description text, 320 aa_id int, 321 exchangerate float, 322 backorder bool DEFAULT 'f' 323); 324-- 325CREATE TABLE orderitems ( 326 id int DEFAULT nextval('orderitemsid'), 327 trans_id int, 328 parts_id int, 329 description text, 330 qty float, 331 sellprice float, 332 discount float4, 333 unit varchar(5), 334 project_id int, 335 reqdate date, 336 ship float, 337 serialnumber text, 338 itemnotes text, 339 lineitemdetail bool, 340 ordernumber text, 341 ponumber text, 342 cost float, 343 vendor text, 344 vendor_id int 345); 346-- 347CREATE TABLE exchangerate ( 348 curr char(3), 349 transdate date, 350 exchangerate float 351); 352-- 353CREATE TABLE employee ( 354 id int primary key DEFAULT nextval('id'), 355 login text, 356 name varchar(64), 357 workphone varchar(20), 358 workfax varchar(20), 359 workmobile varchar(20), 360 homephone varchar(20), 361 homemobile varchar(20), 362 startdate date DEFAULT current_date, 363 enddate date, 364 notes text, 365 sales bool DEFAULT 'f', 366 email text, 367 ssn varchar(20), 368 employeenumber varchar(32), 369 dob date, 370 payperiod int2, 371 apid int, 372 paymentid int, 373 paymentmethod_id int, 374 acsrole_id int, 375 acs text 376); 377-- 378CREATE TABLE shipto ( 379 trans_id int, 380 shiptoname varchar(64), 381 shiptoaddress1 varchar(32), 382 shiptoaddress2 varchar(32), 383 shiptocity varchar(32), 384 shiptostate varchar(32), 385 shiptozipcode varchar(10), 386 shiptocountry varchar(32), 387 shiptocontact varchar(64), 388 shiptophone varchar(20), 389 shiptofax varchar(20), 390 shiptoemail text, 391 shiptorecurring bool DEFAULT 'f' 392); 393-- 394CREATE TABLE vendor ( 395 id int DEFAULT nextval('id') primary key, 396 name varchar(64), 397 contact varchar(64), 398 phone varchar(20), 399 fax varchar(20), 400 email text, 401 notes text, 402 terms int2 DEFAULT 0, 403 taxincluded bool DEFAULT 'f', 404 vendornumber varchar(32), 405 cc text, 406 bcc text, 407 gifi_accno varchar(30), 408 business_id int, 409 taxnumber varchar(32), 410 sic_code varchar(6), 411 discount float4, 412 creditlimit float DEFAULT 0, 413 employee_id int, 414 language_code varchar(6), 415 pricegroup_id int, 416 curr char(3), 417 startdate date, 418 enddate date, 419 arap_accno_id int, 420 payment_accno_id int, 421 discount_accno_id int, 422 cashdiscount float4, 423 discountterms int2, 424 threshold float, 425 paymentmethod_id int, 426 remittancevoucher bool, 427 prepayment_accno_id int 428); 429-- 430CREATE TABLE project ( 431 id int DEFAULT nextval('id'), 432 projectnumber text, 433 description text, 434 startdate date, 435 enddate date, 436 parts_id int, 437 production float DEFAULT 0, 438 completed float DEFAULT 0, 439 customer_id int 440); 441-- 442CREATE TABLE partsgroup ( 443 id int DEFAULT nextval('id'), 444 partsgroup text, 445 pos bool DEFAULT 't', 446 code text, 447 image text 448); 449-- 450CREATE TABLE status ( 451 trans_id int, 452 formname text, 453 printed bool DEFAULT 'f', 454 emailed bool DEFAULT 'f', 455 spoolfile text 456); 457-- 458CREATE TABLE department ( 459 id int DEFAULT nextval('id'), 460 description text, 461 role char(1) DEFAULT 'P', 462 rn int 463); 464-- 465-- department transaction table 466CREATE TABLE dpt_trans ( 467 trans_id int, 468 department_id int 469); 470-- 471-- business table 472CREATE TABLE business ( 473 id int DEFAULT nextval('id'), 474 description text, 475 discount float4, 476 rn int 477); 478-- 479-- SIC 480CREATE TABLE sic ( 481 code varchar(6), 482 sictype char(1), 483 description text 484); 485-- 486CREATE TABLE warehouse ( 487 id int DEFAULT nextval('id'), 488 description text, 489 rn int 490); 491-- 492CREATE TABLE inventory ( 493 id int DEFAULT nextval('inventoryid'), 494 warehouse_id int, 495 parts_id int, 496 trans_id int, 497 orderitems_id int, 498 qty float, 499 shippingdate date, 500 employee_id int 501); 502-- 503CREATE TABLE yearend ( 504 trans_id int, 505 transdate date 506); 507-- 508CREATE TABLE partsvendor ( 509 vendor_id int, 510 parts_id int, 511 partnumber text, 512 leadtime int2, 513 lastcost float, 514 curr char(3) 515); 516-- 517CREATE TABLE pricegroup ( 518 id int DEFAULT nextval('id'), 519 pricegroup text 520); 521-- 522CREATE TABLE partscustomer ( 523 parts_id int, 524 customer_id int, 525 pricegroup_id int, 526 pricebreak float, 527 sellprice float, 528 validfrom date, 529 validto date, 530 curr char(3) 531); 532-- 533CREATE TABLE language ( 534 code varchar(6), 535 description text 536); 537-- 538CREATE TABLE audittrail ( 539 trans_id int, 540 tablename text, 541 reference text, 542 formname text, 543 action text, 544 transdate timestamp DEFAULT current_timestamp, 545 employee_id int 546); 547-- 548CREATE TABLE translation ( 549 trans_id int, 550 language_code varchar(6), 551 description text 552); 553-- 554CREATE TABLE recurring ( 555 id int, 556 reference text, 557 startdate date, 558 nextdate date, 559 enddate date, 560 repeat int2, 561 unit varchar(6), 562 howmany int, 563 payment bool DEFAULT 'f', 564 description text 565); 566-- 567CREATE TABLE recurringemail ( 568 id int, 569 formname text, 570 format text, 571 message text 572); 573-- 574CREATE TABLE recurringprint ( 575 id int, 576 formname text, 577 format text, 578 printer text 579); 580-- 581CREATE TABLE jcitems ( 582 id int DEFAULT nextval('jcitemsid'), 583 project_id int, 584 parts_id int, 585 description text, 586 qty float, 587 allocated float, 588 sellprice float, 589 fxsellprice float, 590 serialnumber text, 591 checkedin timestamp with time zone, 592 checkedout timestamp with time zone, 593 employee_id int, 594 notes text 595); 596-- 597CREATE TABLE cargo ( 598 id int not null, 599 trans_id int not null, 600 package text, 601 netweight float, 602 grossweight float, 603 volume float 604); 605-- 606CREATE TABLE br ( 607 id int DEFAULT nextval('id') primary key, 608 batchnumber text, 609 description text, 610 batch text, 611 transdate date DEFAULT current_date, 612 apprdate date, 613 amount float, 614 managerid int, 615 employee_id int 616); 617-- 618CREATE TABLE vr ( 619 br_id int references br (id) on delete cascade, 620 trans_id int not null, 621 id int not null DEFAULT nextval('id'), 622 vouchernumber text 623); 624-- 625CREATE TABLE semaphore ( 626 id int, 627 login text, 628 module text, 629 expires varchar(12) 630); 631-- 632CREATE TABLE address ( 633 id int DEFAULT nextval('addressid') primary key, 634 trans_id int, 635 address1 varchar(32), 636 address2 varchar(32), 637 city varchar(32), 638 state varchar(32), 639 zipcode varchar(10), 640 country varchar(32) 641); 642-- 643CREATE TABLE contact ( 644 id int default nextval('contactid') primary key, 645 trans_id int not null, 646 salutation varchar(32), 647 firstname varchar(32), 648 lastname varchar(32), 649 contacttitle varchar(32), 650 occupation varchar(32), 651 phone varchar(20), 652 fax varchar(20), 653 mobile varchar(20), 654 email text, 655 gender char(1) default 'M', 656 parent_id int, 657 typeofcontact varchar(20) 658); 659-- 660CREATE TABLE paymentmethod ( 661 id int primary key default nextval('id'), 662 description text, 663 fee float, 664 rn int, 665 roundchange float4 666); 667-- 668CREATE TABLE bank ( 669 id int, 670 name varchar(64), 671 iban varchar(34), 672 bic varchar(11), 673 address_id int default nextval('addressid'), 674 dcn text, 675 rvc text, 676 membernumber text, 677 clearingnumber text 678); 679-- 680CREATE TABLE payment ( 681 id int not null, 682 trans_id int not null, 683 exchangerate float default 1, 684 paymentmethod_id int 685); 686-- 687CREATE TABLE curr ( 688 rn int2, 689 curr char(3) primary key, 690 prec int2 691); 692-- 693CREATE TABLE report ( 694 reportid int primary key default nextval('id'), 695 reportcode text, 696 reportdescription text, 697 login text 698); 699-- 700CREATE TABLE reportvars ( 701 reportid int not null, 702 reportvariable text, 703 reportvalue text 704); 705-- 706CREATE TABLE employeededuction ( 707 id int, 708 employee_id int, 709 deduction_id int, 710 exempt float, 711 maximum float 712); 713-- 714CREATE TABLE pay_trans ( 715 trans_id int, 716 id int, 717 glid int, 718 qty float, 719 amount float 720); 721-- 722CREATE TABLE deduction ( 723 id int default nextval('id') primary key, 724 description text, 725 employee_accno_id int, 726 employeepays float4, 727 employer_accno_id int, 728 employerpays float4, 729 fromage int2, 730 toage int2, 731 agedob bool, 732 basedon int 733); 734-- 735CREATE TABLE deduct ( 736 trans_id int, 737 deduction_id int, 738 withholding bool, 739 percent float4 740); 741-- 742CREATE TABLE deductionrate ( 743 rn int2, 744 trans_id int, 745 rate float, 746 amount float, 747 above float, 748 below float 749); 750-- 751CREATE TABLE wage ( 752 id int default nextval('id') primary key, 753 description text, 754 amount float, 755 defer int, 756 exempt bool default 'f', 757 chart_id int 758); 759-- 760CREATE TABLE payrate ( 761 trans_id int, 762 id int, 763 rate float, 764 above float 765); 766-- 767CREATE TABLE employeewage ( 768 id int, 769 employee_id int, 770 wage_id int 771); 772-- 773CREATE TABLE reference ( 774 id int default nextval('referenceid') primary key, 775 code text, 776 trans_id int, 777 description text, 778 archive_id int, 779 login text, 780 formname text, 781 folder text 782); 783-- 784CREATE TABLE acsrole ( 785 id int default nextval('id') primary key, 786 description text, 787 acs text, 788 rn int2 789); 790-- 791CREATE TABLE archive ( 792 id int default nextval('archiveid') primary key, 793 filename text 794); 795-- 796CREATE TABLE archivedata ( 797 archive_id int references archive (id) on delete cascade, 798 bt text, 799 rn int 800); 801-- 802CREATE TABLE mimetype ( 803 extension varchar(32) primary key, 804 contenttype varchar(64) 805); 806-- 807