From: Alan Knowles Date: Wed, 25 Feb 2015 10:10:26 +0000 (+0800) Subject: Attribute changed sql/accnt.sql X-Git-Url: http://git.roojs.org/?p=Pman.Xtuple;a=commitdiff_plain;h=4f7430120d8deb7dacab6b3d6b53c1c0ed5006d0 Attribute changed sql/accnt.sql sql/accnt.sql Attribute changed sql/bankaccnt.sql sql/bankaccnt.sql Attribute changed sql/checkhead.sql sql/checkhead.sql Attribute changed sql/cntct.sql sql/cntct.sql Attribute changed sql/cobapply.sql sql/cobapply.sql Attribute changed sql/cohead.sql sql/cohead.sql Attribute changed sql/custinfo.sql sql/custinfo.sql Attribute changed sql/dragon_report.sql sql/dragon_report.sql Attribute changed sql/expense.sql sql/expense.sql Attribute changed sql/invadj.sql sql/invadj.sql Attribute changed sql/invdetail.sql sql/invdetail.sql Attribute changed sql/invhist_transfer.sql sql/invhist_transfer.sql Attribute changed sql/location.sql sql/location.sql Attribute changed sql/recvgrp.sql sql/recvgrp.sql Attribute changed sql/shiphead.sql sql/shiphead.sql Attribute changed sql/vendaddrinfo.sql sql/vendaddrinfo.sql Attribute changed sql/vendinfo.sql sql/vendinfo.sql --- diff --git a/sql/accnt.sql b/sql/accnt.sql new file mode 100644 index 00000000..2ada2360 --- /dev/null +++ b/sql/accnt.sql @@ -0,0 +1,8 @@ + +-- kingdee code for the account --- +ALTER TABLE accnt ADD COLUMN accnt_code_alt TEXT DEFAULT ''; + +-- chinese name for the account --- +ALTER TABLE accnt ADD COLUMN accnt_descrip_alt TEXT DEFAULT ''; + +CREATE INDEX accnt_code_descrip_alt ON accnt USING btree (accnt_code_alt, accnt_descrip_alt); diff --git a/sql/bankaccnt.sql b/sql/bankaccnt.sql new file mode 100644 index 00000000..e1e78e4c --- /dev/null +++ b/sql/bankaccnt.sql @@ -0,0 +1,2 @@ +ALTER TABLE bankaccnt ADD CONSTRAINT bankaccnt_accnt_id_fk + FOREIGN KEY (bankaccnt_accnt_id) REFERENCES accnt (accnt_id); \ No newline at end of file diff --git a/sql/checkhead.sql b/sql/checkhead.sql new file mode 100644 index 00000000..1393ddcc --- /dev/null +++ b/sql/checkhead.sql @@ -0,0 +1,2 @@ +ALTER TABLE checkhead add column checkhead_voided DATE; + diff --git a/sql/cntct.sql b/sql/cntct.sql new file mode 100644 index 00000000..48483b42 --- /dev/null +++ b/sql/cntct.sql @@ -0,0 +1 @@ +ALTER TABLE cntct ADD COLUMN cntct_id_card TEXT NOT NULL DEFAULT ''; \ No newline at end of file diff --git a/sql/cobapply.sql b/sql/cobapply.sql new file mode 100644 index 00000000..30073d6b --- /dev/null +++ b/sql/cobapply.sql @@ -0,0 +1,50 @@ +-- Sequence: cobapply_id_seq + +-- DROP SEQUENCE cobapply_id_seq; + +CREATE SEQUENCE cobapply_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 2147483647 + START 1 + CACHE 1; +ALTER TABLE cobapply_id_seq + OWNER TO admin; +GRANT ALL ON TABLE cobapply_id_seq TO admin; +GRANT ALL ON TABLE cobapply_id_seq TO xtrole; + + +-- cob apply - planned credit memo applications for a bill ; + + +CREATE TABLE cobapply +( + cobapply_id integer NOT NULL DEFAULT nextval(('cobapply_id_seq'::text)::regclass), + cobapply_cobmisc_id integer, + cobapply_aropen_id integer, + cobapply_applied boolean, + + CONSTRAINT cobapply_pkey PRIMARY KEY (cobapply_id ), + CONSTRAINT cobapply_cobmisc_id_fkey FOREIGN KEY (cobapply_cobmisc_id) + REFERENCES cobmisc (cobmisc_id) + ON UPDATE CASCADE ON DELETE NO ACTION, + CONSTRAINT cobapply_aropen_id_fkey FOREIGN KEY (cobapply_aropen_id) + REFERENCES aropen (aropen_id) + ON UPDATE CASCADE ON DELETE NO ACTION +) +WITH ( + OIDS=FALSE +); + +CREATE INDEX cobapply_cobmisc_id_ix ON cobapply USING btree (cobapply_cobmisc_id); +CREATE INDEX cobapply_aropen_id_ix ON cobapply USING btree (cobapply_aropen_id); +CREATE INDEX cobapply_applied_ix ON cobapply USING btree (cobapply_applied); + +ALTER TABLE cobapply + OWNER TO admin; +GRANT ALL ON TABLE cobapply TO admin; +GRANT ALL ON TABLE cobapply TO xtrole; +COMMENT ON TABLE cobapply + IS 'Planned credit memo applies to bills'; + + \ No newline at end of file diff --git a/sql/cohead.sql b/sql/cohead.sql new file mode 100644 index 00000000..cb2e5c92 --- /dev/null +++ b/sql/cohead.sql @@ -0,0 +1,15 @@ +-- add a 'displayed' sales person. + + +alter table cohead add column cohead_display_salesrep_id INTEGER; + +ALTER TABLE cohead add column cohead_pretax_discount numeric(16,4) NOT NULL DEFAULT 0; + +ALTER TABLE cohead add column cohead_posttax_discount numeric(16,4) NOT NULL DEFAULT 0; + +ALTER TABLE cohead ADD CONSTRAINT cohead_display_salesrep_fkey FOREIGN KEY (cohead_display_salesrep_id) + REFERENCES salesrep(salesrep_id) MATCH SIMPLE + ON UPDATE CASCADE ON DELETE NO ACTION; + + + diff --git a/sql/custinfo.sql b/sql/custinfo.sql new file mode 100644 index 00000000..1ef84c34 --- /dev/null +++ b/sql/custinfo.sql @@ -0,0 +1,15 @@ +ALTER TABLE custinfo ADD COLUMN cust_passwd CHARACTER VARYING(64) NOT NULL DEFAULT ''; + +CREATE INDEX cust_passwd_ix ON custinfo USING btree (cust_passwd); + +ALTER TABLE custinfo ADD COLUMN cust_login_email CHARACTER VARYING(256) NOT NULL DEFAULT ''; + +CREATE INDEX cust_login_email_ix ON custinfo USING btree (cust_login_email); + +ALTER TABLE custinfo ADD COLUMN cust_dob TEXT NOT NULL DEFAULT ''; + +CREATE INDEX cust_dob_ix ON custinfo USING btree (cust_dob); + +ALTER TABLE custinfo ADD COLUMN cust_subscribed BOOLEAN NOT NULL DEFAULT TRUE; + +CREATE INDEX cust_subscribed_ix ON custinfo USING btree (cust_subscribed); \ No newline at end of file diff --git a/sql/dragon_report.sql b/sql/dragon_report.sql new file mode 100644 index 00000000..edf6dcff --- /dev/null +++ b/sql/dragon_report.sql @@ -0,0 +1,38 @@ +-- +-- first go at dragon reports + + +CREATE SEQUENCE dragon_report_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 2147483647 + START 1 + CACHE 1; +ALTER TABLE recvgrp_id_seq + OWNER TO admin; +GRANT ALL ON TABLE dragon_report_id_seq TO admin; +GRANT ALL ON TABLE dragon_report_id_seq TO xtrole; + +CREATE TABLE dragon_report +( + + id integer NOT NULL DEFAULT nextval(('dragon_report_id_seq'::text)::regclass), + name text NOT NULL, + query text, + + + CONSTRAINT dragon_report_pkey PRIMARY KEY (id) +) +WITH ( + OIDS=FALSE +); + + +ALTER TABLE dragon_report + OWNER TO admin; +GRANT ALL ON TABLE dragon_report TO admin; +GRANT ALL ON TABLE dragon_report TO xtrole; +COMMENT ON TABLE dragon_report + IS 'first go at dragon reports'; + + \ No newline at end of file diff --git a/sql/expense.sql b/sql/expense.sql new file mode 100644 index 00000000..559cc564 --- /dev/null +++ b/sql/expense.sql @@ -0,0 +1,131 @@ + +CREATE SEQUENCE expense_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 2147483647 + START 1 + CACHE 1; +ALTER TABLE expense_id_seq + OWNER TO admin; +GRANT ALL ON TABLE expense_id_seq TO admin; +GRANT ALL ON TABLE expense_id_seq TO xtrole; + + +CREATE SEQUENCE expitem_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 2147483647 + START 1 + CACHE 1; +ALTER TABLE expitem_id_seq + OWNER TO admin; +GRANT ALL ON TABLE expitem_id_seq TO admin; +GRANT ALL ON TABLE expitem_id_seq TO xtrole; + + + +CREATE TABLE expense +( + expense_id INTEGER NOT NULL DEFAULT nextval(('expense_id_seq'::text)::regclass), + + + expense_accnt_id INTEGER DEFAULT NULL, + expense_emp_id INTEGER NOT NULL, + + expense_number TEXT NOT NULL, + + expense_trandate date DEFAULT NULL, + expense_created date DEFAULT NULL, + expense_modified date DEFAULT NULL, + expense_duedate date DEFAULT NULL, + + expense_memo TEXT, + expense_comments TEXT, + expense_status TEXT, + + expense_advance decimal(12,3) DEFAULT '0.000', + expense_amount decimal(12,3) DEFAULT '0.000', + + expense_tax decimal(12,3) DEFAULT '0.000', + expense_total decimal(12,3) DEFAULT '0.000', + expense_posted boolean default false, + + CONSTRAINT expense_accnt_id_fkey FOREIGN KEY (expense_accnt_id) + REFERENCES accnt (accnt_id) MATCH SIMPLE, + CONSTRAINT expense_emp_id_fkey FOREIGN KEY (expense_emp_id) + REFERENCES emp (emp_id), + CONSTRAINT expense_id_pkey PRIMARY KEY (expense_id) +) +WITH ( + OIDS=FALSE +); + +ALTER TABLE expense + OWNER TO admin; + +GRANT ALL ON TABLE expense TO admin; +GRANT ALL ON TABLE expense TO xtrole; +COMMENT ON TABLE expense + IS 'Expense Reports'; + + CREATE INDEX expense_number_ix ON expense USING btree (expense_number); + CREATE INDEX expense_date_ix ON expense USING btree (expense_trandate,expense_created, expense_modified, expense_duedate ); + + + + +CREATE TABLE expitem +( + expitem_id INTEGER NOT NULL DEFAULT nextval(('expitem_id_seq'::text)::regclass), + + expitem_expense_id INTEGER NOT NULL, + expitem_curr_id INTEGER NOT NULL, + expitem_expcat_id INTEGER NOT NULL, + + + expitem_row INTEGER NOT NULL, + + + expitem_amount decimal(12,3) DEFAULT '0.000', + expitem_amount_fc decimal(12,3) DEFAULT '0.000', + expitem_tax decimal(12,3) DEFAULT '0.000', + expitem_total decimal(12,3) DEFAULT '0.000', + --grossAmt decimal(12,3) DEFAULT '0.000', + -- expense cat? + + expitem_date date DEFAULT NULL, + --exchangeRate decimal(12,5) DEFAULT NULL, + + + expitem_is_billable INTEGER DEFAULT 0, + + expitem_memo text DEFAULT '', + + + -- tax code ??? + --receipt int(4) DEFAULT '0', + CONSTRAINT expitem_expense_id_fkey FOREIGN KEY (expitem_expense_id) + REFERENCES expense (expense_id), + + CONSTRAINT expitem_curr_id_fkey FOREIGN KEY (expitem_curr_id) + REFERENCES curr_symbol (curr_id), + + CONSTRAINT expitem_expcat_id_fkey FOREIGN KEY (expitem_expcat_id) + REFERENCES expcat (expcat_id), + + -- unique row / expid.... + + CONSTRAINT expitem_id_pkey PRIMARY KEY (expitem_id) +) +WITH ( + OIDS=FALSE +); + +ALTER TABLE expitem + OWNER TO admin; + +GRANT ALL ON TABLE expitem TO admin; +GRANT ALL ON TABLE expitem TO xtrole; +COMMENT ON TABLE expitem + IS 'Expense Report Items'; + diff --git a/sql/invadj.sql b/sql/invadj.sql new file mode 100644 index 00000000..b4cd5164 --- /dev/null +++ b/sql/invadj.sql @@ -0,0 +1,85 @@ +-- Sequence: accnt_accnt_id_seq + +--DROP table invadj; + + +CREATE SEQUENCE invadj_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 2147483647 + START 1 + CACHE 1; +ALTER TABLE invadj_id_seq + OWNER TO admin; +GRANT ALL ON TABLE invadj_id_seq TO admin; +GRANT ALL ON TABLE invadj_id_seq TO xtrole; + + +CREATE TABLE invadj +( + invadj_id integer NOT NULL DEFAULT nextval(('invadj_id_seq'::text)::regclass), + invadj_transdate date, + invadj_location_id integer, + invadj_itemsite_id integer, + invadj_qty_by integer, + invadj_posted boolean, + + CONSTRAINT invadj_pkey PRIMARY KEY (invadj_id ), + CONSTRAINT invadj_location_fkey FOREIGN KEY (invadj_location_id) + REFERENCES location (location_id) + ON UPDATE CASCADE ON DELETE NO ACTION, + + CONSTRAINT invadj_itemsite_fkey FOREIGN KEY (invadj_itemsite_id) + REFERENCES itemsite (itemsite_id) + ON UPDATE CASCADE ON DELETE NO ACTION +) +WITH ( + OIDS=FALSE +); + +ALTER TABLE invadj ADD COLUMN invadj_comments text; +ALTER TABLE invadj ADD COLUMN invadj_voids_id INTEGER NOT NULL DEFAULT 0; +ALTER TABLE invadj ADD COLUMN invadj_invdetail_id INTEGER; +ALTER TABLE invadj ADD COLUMN invadj_voided_by_id INTEGER NOT NULL DEFAULT 0; +ALTER TABLE invadj ADD COLUMN invadj_invadjgrp_id INTEGER DEFAULT NULL; + + +CREATE INDEX invadj_location_id_ix ON invadj USING btree (invadj_location_id); +CREATE INDEX invadj_transdate_ix ON invadj USING btree (invadj_transdate); +CREATE INDEX invadj_itemsite_ix ON invadj USING btree (invadj_itemsite_id); +CREATE INDEX invadj_posted_ix ON invadj USING btree (invadj_posted); + +CREATE INDEX invadj_voided_by_id_ix ON invadj USING btree (invadj_voided_by_id); +CREATE INDEX invadj_invdetail_id_ix ON invadj USING btree (invadj_invdetail_id); +CREATE INDEX invadj_voids_id_ix ON invadj USING btree (invadj_voids_id); + +CREATE INDEX invadj_invadjgrp_id_ix ON invadj USING btree (invadj_invadjgrp_id); + + + +ALTER TABLE invadj + OWNER TO admin; +GRANT ALL ON TABLE invadj TO admin; +GRANT ALL ON TABLE invadj TO xtrole; +COMMENT ON TABLE invadj + IS 'Inventory Adjustment Draft'; + + + + + + + +-- sync: +-- DELETE FROM invadj; +-- SELECT invadj_sync_invdetail(invdetail_id) FROM invdetailview where invhist_transtype = 'AD' ORDER BY invhist_transdate ASC; + + +--- FIX the fact i forgot to update invdetail id.. + +-- UPDATE invadj +-- SET invadj_invdetail_id = (SELECT invdetail_id FROM invdetailview WHERE invhist_ordnumber = 'INVADJ-' || invadj_id LIMIT 1) +-- WHERE invadj_posted and invadj_invdetail_id IS NULL; +-- +-- + diff --git a/sql/invdetail.sql b/sql/invdetail.sql new file mode 100644 index 00000000..73fe36f6 --- /dev/null +++ b/sql/invdetail.sql @@ -0,0 +1,2 @@ +ALTER TABLE invdetail ADD CONSTRAINT invdetail_invhist_fk + FOREIGN KEY (invdetail_invhist_id) REFERENCES invhist (invhist_id); \ No newline at end of file diff --git a/sql/invhist_transfer.sql b/sql/invhist_transfer.sql new file mode 100644 index 00000000..ffb77fcd --- /dev/null +++ b/sql/invhist_transfer.sql @@ -0,0 +1,4 @@ + +-- change for delivery note +ALTER TABLE invhist_transfer ADD COLUMN invhist_transfer_delivery_note TEXT; +CREATE INDEX invhist_transfer_delivery_note_ix ON invhist_transfer USING btree (invhist_transfer_delivery_note ); diff --git a/sql/location.sql b/sql/location.sql new file mode 100644 index 00000000..17e800f0 --- /dev/null +++ b/sql/location.sql @@ -0,0 +1 @@ +ALTER TABLE location ADD COLUMN location_is_default INT DEFAULT 0; \ No newline at end of file diff --git a/sql/recvgrp.sql b/sql/recvgrp.sql new file mode 100644 index 00000000..dee9a6f8 --- /dev/null +++ b/sql/recvgrp.sql @@ -0,0 +1,4 @@ +-- change for delivery note +ALTER TABLE recvgrp ADD COLUMN recvgrp_receipt_number TEXT; +CREATE INDEX recvgrp_receipt_number_ix ON recvgrp USING btree (recvgrp_receipt_number); + diff --git a/sql/shiphead.sql b/sql/shiphead.sql new file mode 100644 index 00000000..094e06ad --- /dev/null +++ b/sql/shiphead.sql @@ -0,0 +1,4 @@ +-- change for delivery note +ALTER TABLE shiphead ADD COLUMN shiphead_delivery_note TEXT; +CREATE INDEX shiphead_delivery_note_ix ON shiphead USING btree (shiphead_delivery_note); + diff --git a/sql/vendaddrinfo.sql b/sql/vendaddrinfo.sql new file mode 100644 index 00000000..1858235d --- /dev/null +++ b/sql/vendaddrinfo.sql @@ -0,0 +1,2 @@ +ALTER TABLE vendaddrinfo ADD CONSTRAINT vendaddr_vend_id_fk + FOREIGN KEY (vendaddr_vend_id) REFERENCES vendinfo (vend_id); \ No newline at end of file diff --git a/sql/vendinfo.sql b/sql/vendinfo.sql new file mode 100644 index 00000000..6639bc77 --- /dev/null +++ b/sql/vendinfo.sql @@ -0,0 +1,2 @@ +ALTER TABLE vendinfo ADD CONSTRAINT vend_terms_fk + FOREIGN KEY (vend_terms_id) REFERENCES terms (terms_id); \ No newline at end of file