Here are all of the data structures involved in this view.
Query Ran: select * from assemblycanbuild
CREATE OR REPLACE VIEW assemblycanbuild AS
SELECT assembliesbatchid,
CASE
WHEN min(
CASE
WHEN (stock::double precision - prioruse - quantity::double precision) >= 0::double precision THEN 100000000::double precision
WHEN COALESCE(qtyperunit, 0::double precision) = 0::double precision OR (stock::double precision - prioruse) < 0::double precision THEN 0::double precision
ELSE trunc((stock::double precision - prioruse) / qtyperunit)
END) = 100000000::double precision THEN 'All'::character varying
ELSE min(
CASE
WHEN COALESCE(qtyperunit, 0::double precision) = 0::double precision OR (stock::double precision - prioruse) < 0::double precision THEN 0::double precision
ELSE trunc((stock::double precision - prioruse) / qtyperunit)
END)::character varying
END AS canbuild
FROM assembliesstockbatchpriorexpected
WHERE quantity <> 0
GROUP BY assembliesbatchid;
CREATE OR REPLACE VIEW assembliesstockbatchpriorexpected AS
SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.units, a.quantity, a.stock, a.prioruse, COALESCE(sum(
CASE
WHEN COALESCE(e.promisedby::timestamp without time zone::timestamp with time zone, e.requestedby::timestamp without time zone::timestamp with time zone,
CASE
WHEN e.deliverywks IS NULL THEN f.issuedate
ELSE NULL::date
END::timestamp without time zone::timestamp with time zone,
CASE
WHEN e.deliverywks <> -1 THEN (f.issuedate + e.deliverywks * 7)::timestamp without time zone::timestamp with time zone
ELSE a.duedate + '1 day'::interval
END) <= a.duedate THEN COALESCE(e.quantity, 0) - COALESCE(e.deliveredsum, 0)
ELSE NULL::integer
END), 0::bigint) AS expectedbefore, a.qtyperunit
FROM assembliesstockbatchprioruse a
LEFT JOIN (pos f
JOIN poparts e ON f.poid = e.poid AND f.postatusid >= 20 AND f.postatusid <= 59 AND f.isrfq = false
JOIN manufacturerpartpn g ON g.pnid = e.pnid) ON e.partid = a.partid
GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.prioruse, a.units, a.qtyperunit;
CREATE OR REPLACE VIEW assembliesstockbatchprioruse AS
SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.units, a.quantity, a.stock, COALESCE(sum(- b.quantity)::double precision, 0::double precision) AS prioruse, a.qtyperunit, a.leadfree
FROM assembliesstockbatch a
LEFT JOIN (allocatedassemblies b
JOIN assembliesbatch c ON b.assembliesbatchid = c.assembliesbatchid AND (c.assembliesbatchstatusid = 1 OR c.assembliesbatchstatusid = 2 OR c.assembliesbatchstatusid = 4 OR c.assembliesbatchstatusid = 7)
JOIN assemblies q ON q.assemblyid = c.assemblyid) ON a.partid = b.partid AND COALESCE(a.ownerid, 1) = 1 AND a.leadfree = q.leadfree AND (a.duedate > c.duedate OR a.duedate = c.duedate AND a.assembliesbatchid > c.assembliesbatchid)
GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.units, a.qtyperunit, a.leadfree;
CREATE OR REPLACE VIEW assembliesstockbatch AS
SELECT d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname, c.ownerid, e.partid, d.units, - e.quantity AS quantity, COALESCE(c.stock, 0::bigint) AS stock, max(b.quantity) AS qtyperunit, a.leadfree
FROM assemblies a
JOIN assembliesbatch d ON d.assemblyid = a.assemblyid
JOIN allocatedassemblies e ON e.assembliesbatchid = d.assembliesbatchid
LEFT JOIN partsassembly b ON b.assemblyid = a.assemblyid AND e.partid = b.partid
LEFT JOIN stockperowner_lead_ab() c(partid, ownerid, stock, leadstateid) ON c.partid = e.partid AND c.ownerid = 1 AND leadcompcheck_ab(a.leadfree, c.leadstateid)
LEFT JOIN stocklog f ON f.refid = d.batchid AND f.transtypeid = 3 AND f.partid = e.partid
WHERE (d.assembliesbatchstatusid = ANY (ARRAY[1, 2, 4, 7])) AND COALESCE(f.commited, false) = false
GROUP BY d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname, c.ownerid, e.partid, COALESCE(c.stock, 0::bigint), d.units, e.quantity, a.leadfree;
CREATE OR REPLACE FUNCTION stockperowner_lead_ab()
RETURNS SETOF stockperowner AS
$BODY$
declare
row stockperowner;
begin
for row in select partid,ownerid,sum(stock),2 from stockperowner
where leadstateid in (2,3,4)
group by partid,ownerid
Loop
return next row;
end loop;
for row in select partid,ownerid,sum(stock),1 from stockperowner
where leadstateid in (1,3,4)
group by partid,ownerid
Loop
return next row;
end loop;
return;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE VIEW stockperowner AS
SELECT a.partid, a.ownerid, sum(a.stock) AS stock, b.leadstateid
FROM stock a
JOIN manufacturerpartpn b ON a.pnid = b.pnid
WHERE b.compatibilitygradeid <= 400
GROUP BY a.partid, a.ownerid, b.leadstateid;
CREATE OR REPLACE FUNCTION leadcompcheck_ab(assmstat boolean, leadstateid integer)
RETURNS boolean AS
$BODY$
begin
if assmstat and leadstateid in (1,3,4) then
return true;
elsif not assmstat and leadstateid in (2,3,4) then
return true;
else
return false;
end if;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TABLE pos
(
poid integer NOT NULL DEFAULT nextval(('public.pos_poid_seq'::text)::regclass),
supplierid integer,
poref citext NOT NULL,
postatusid integer,
isrfq boolean,
posupplierref citext,
issuedate date,
confirmationdate date,
confirmationref citext,
promiseddeliverydate date,
deliverydate date,
comments text,
userid integer,
currencyid integer DEFAULT 1,
exchange double precision,
printedcomment text,
ownerid integer,
suppliercontactid integer,
readydate date,
courierid integer,
couriercontact citext,
courierdate date,
shipmentdoc citext,
suppliercourier boolean,
suppliercourierdetails citext,
fob boolean,
fobmfgname integer,
attachments text,
paymentorder integer,
paymentdelivery integer,
paymentcredit integer,
creditdays integer,
currentplus boolean,
problems text,
clonedfrompoid integer,
followupcontactid integer,
lastmodifieddate timestamp without time zone,
filegenerated boolean NOT NULL DEFAULT false,
revision integer DEFAULT 0,
CONSTRAINT pos_pkey PRIMARY KEY (poid),
CONSTRAINT pos_courierid_fkey FOREIGN KEY (courierid)
REFERENCES couriers (courierid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_currencyid_fkey FOREIGN KEY (currencyid)
REFERENCES currencies (currencyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_followupcontactid_fkey FOREIGN KEY (followupcontactid)
REFERENCES organizationcontacts (organizationcontactid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT pos_postatusid_fkey FOREIGN KEY (postatusid)
REFERENCES postatus (postatusid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_suppliercontactid_fkey FOREIGN KEY (suppliercontactid)
REFERENCES organizationcontacts (organizationcontactid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT pos_supplierid_fkey FOREIGN KEY (supplierid)
REFERENCES organizations (organizationid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;
ALTER TABLE pos OWNER TO postgres;
-- Index: courieridpo
-- DROP INDEX courieridpo;
CREATE INDEX courieridpo
ON pos
USING btree
(courierid);
-- Index: ix_b010e4db_b3da_4618_8328_f47d77c917a9_
-- DROP INDEX ix_b010e4db_b3da_4618_8328_f47d77c917a9_;
CREATE INDEX ix_b010e4db_b3da_4618_8328_f47d77c917a9_
ON pos
USING btree
(currencyid);
-- Index: ix_isrfqpo
-- DROP INDEX ix_isrfqpo;
CREATE INDEX ix_isrfqpo
ON pos
USING btree
(isrfq);
-- Index: ix_pospoid
-- DROP INDEX ix_pospoid;
CREATE UNIQUE INDEX ix_pospoid
ON pos
USING btree
(poid);
-- Index: owneridpo
-- DROP INDEX owneridpo;
CREATE INDEX owneridpo
ON pos
USING btree
(ownerid);
-- Index: postatusidpo
-- DROP INDEX postatusidpo;
CREATE INDEX postatusidpo
ON pos
USING btree
(postatusid);
-- Index: supplieridpo
-- DROP INDEX supplieridpo;
CREATE INDEX supplieridpo
ON pos
USING btree
(supplierid);
-- Index: useridpo
-- DROP INDEX useridpo;
CREATE INDEX useridpo
ON pos
USING btree
(userid);
CREATE TABLE poparts
(
popartid integer NOT NULL DEFAULT nextval(('public.poparts_popartid_seq'::text)::regclass),
poid integer,
partid integer,
pnid integer,
quantity integer,
supplierquantity integer,
unitprice double precision,
requestedby date,
promisedby date,
deliveredby date,
deliverywks integer,
comments citext,
currencyid integer,
statusrequest boolean,
nobid boolean,
invoiceno citext,
paymentsatus integer,
purchaseagreemet boolean,
deliveredsum integer DEFAULT 0,
fkpoitemstatusid integer,
bestprice double precision,
bestpricecomments citext,
linenumber integer,
intotal boolean NOT NULL DEFAULT true,
mpqqty integer,
lastmodifieddate timestamp without time zone,
CONSTRAINT poparts_pkey PRIMARY KEY (popartid),
CONSTRAINT poparts_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT poparts_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT poparts_poid_fkey FOREIGN KEY (poid)
REFERENCES pos (poid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE poparts OWNER TO postgres;
-- Index: currencyidpp
-- DROP INDEX currencyidpp;
CREATE INDEX currencyidpp
ON poparts
USING btree
(currencyid);
-- Index: ix_manufacturerpartpnpoparts
-- DROP INDEX ix_manufacturerpartpnpoparts;
CREATE INDEX ix_manufacturerpartpnpoparts
ON poparts
USING btree
(pnid);
-- Index: ix_partspoparts
-- DROP INDEX ix_partspoparts;
CREATE INDEX ix_partspoparts
ON poparts
USING btree
(partid);
-- Index: ix_pospoparts
-- DROP INDEX ix_pospoparts;
CREATE INDEX ix_pospoparts
ON poparts
USING btree
(poid);
-- Index: popartid
-- DROP INDEX popartid;
CREATE INDEX popartid
ON poparts
USING btree
(popartid);
CREATE TABLE manufacturerpartpn
(
pnid integer NOT NULL DEFAULT nextval(('public.manufacturerpartpn_pnid_seq'::text)::regclass),
partid integer,
manufacturerid integer,
manufacturerpn citext,
manufacturerdatasheet text,
mpq integer,
unitid integer,
comments citext,
compatibilitygradeid integer,
pnstatusid integer,
lifecycleid integer DEFAULT 100,
translatempq boolean NOT NULL DEFAULT false,
leadstateid integer,
parentid integer,
CONSTRAINT manufacturerpartpn_pkey PRIMARY KEY (pnid),
CONSTRAINT manufacturerpartpn_compatibilitygradeid_fkey FOREIGN KEY (compatibilitygradeid)
REFERENCES partcompatibility (compatibilitygradeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_lifecycleid_fkey FOREIGN KEY (lifecycleid)
REFERENCES partlifecycle (lifecycleid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_manufacturerid_fkey FOREIGN KEY (manufacturerid)
REFERENCES organizations (organizationid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT manufacturerpartpn_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_unitid_fkey FOREIGN KEY (unitid)
REFERENCES units (unitid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE manufacturerpartpn OWNER TO postgres;
-- Index: ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_
-- DROP INDEX ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_;
CREATE INDEX ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_
ON manufacturerpartpn
USING btree
(compatibilitygradeid);
-- Index: ix_manufacturerpartpnpnid
-- DROP INDEX ix_manufacturerpartpnpnid;
CREATE UNIQUE INDEX ix_manufacturerpartpnpnid
ON manufacturerpartpn
USING btree
(pnid);
-- Index: ix_manufacturersmanufacturerpartpn
-- DROP INDEX ix_manufacturersmanufacturerpartpn;
CREATE INDEX ix_manufacturersmanufacturerpartpn
ON manufacturerpartpn
USING btree
(manufacturerid);
-- Index: ix_partlifecyclemanufacturerpartpn
-- DROP INDEX ix_partlifecyclemanufacturerpartpn;
CREATE INDEX ix_partlifecyclemanufacturerpartpn
ON manufacturerpartpn
USING btree
(lifecycleid);
-- Index: ix_partsmanufacturerpartpn
-- DROP INDEX ix_partsmanufacturerpartpn;
CREATE INDEX ix_partsmanufacturerpartpn
ON manufacturerpartpn
USING btree
(partid);
-- Index: ix_unitsmanufacturerpartpn
-- DROP INDEX ix_unitsmanufacturerpartpn;
CREATE INDEX ix_unitsmanufacturerpartpn
ON manufacturerpartpn
USING btree
(unitid);
-- Index: mpplsi
-- DROP INDEX mpplsi;
CREATE INDEX mpplsi
ON manufacturerpartpn
USING btree
(leadstateid);
CREATE TABLE allocatedassemblies
(
allocatedassembliesid integer NOT NULL DEFAULT nextval(('public.allocatedassemblies_allocatedassembliesid_seq'::text)::regclass),
assembliesbatchid integer,
partid integer,
ownerid integer,
quantity integer,
commitdate timestamp without time zone,
userid integer,
comments citext,
CONSTRAINT pk_allocatedassemblies PRIMARY KEY (allocatedassembliesid),
CONSTRAINT fk_allocatedassemblies_assembliesbatchid FOREIGN KEY (assembliesbatchid)
REFERENCES assembliesbatch (assembliesbatchid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_allocatedassemblies_partid FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE allocatedassemblies OWNER TO postgres;
-- Index: fki_allocatedassemblies_assembliesbatchid
-- DROP INDEX fki_allocatedassemblies_assembliesbatchid;
CREATE INDEX fki_allocatedassemblies_assembliesbatchid
ON allocatedassemblies
USING btree
(assembliesbatchid);
-- Index: fki_allocatedassemblies_partid
-- DROP INDEX fki_allocatedassemblies_partid;
CREATE INDEX fki_allocatedassemblies_partid
ON allocatedassemblies
USING btree
(partid);
CREATE TABLE assembliesbatch
(
assembliesbatchid integer NOT NULL DEFAULT nextval(('public.assembliesbatch_assembliesbatchid_seq'::text)::regclass),
batchid integer,
assemblyid integer,
units integer,
comments citext,
lastmodified timestamp without time zone,
ab_options citext,
buildprice double precision,
duedate timestamp with time zone DEFAULT (('now'::text)::date + '49 days'::interval),
customerid integer,
allocatedunits integer,
canbuild citext,
entrydate timestamp without time zone DEFAULT ('now'::text)::date,
assembliesbatchstatusid integer DEFAULT 1,
customername citext,
currentsort integer,
bomprice double precision,
originalunits integer,
quotationitemid integer,
CONSTRAINT assembliesbatch_pkey PRIMARY KEY (assembliesbatchid),
CONSTRAINT assembliesbatch_assembliesbatchstatus_id FOREIGN KEY (assembliesbatchstatusid)
REFERENCES assembliesbatchstatus (assembliesbatchstatusid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_assemblyid_fkey FOREIGN KEY (assemblyid)
REFERENCES assemblies (assemblyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_batchid_fkey FOREIGN KEY (batchid)
REFERENCES batches (batchid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_quotationitemid_fkey FOREIGN KEY (quotationitemid)
REFERENCES sales.quotationitems (quotationitemid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE assembliesbatch OWNER TO postgres;
-- Index: fki_assembliesbatch_assembliesbatchstatus_id
-- DROP INDEX fki_assembliesbatch_assembliesbatchstatus_id;
CREATE INDEX fki_assembliesbatch_assembliesbatchstatus_id
ON assembliesbatch
USING btree
(assembliesbatchstatusid);
-- Index: ix_080c8ff0_5017_42a2_a174_28095b85106e_
-- DROP INDEX ix_080c8ff0_5017_42a2_a174_28095b85106e_;
CREATE INDEX ix_080c8ff0_5017_42a2_a174_28095b85106e_
ON assembliesbatch
USING btree
(assemblyid);
CREATE TABLE assemblies
(
assemblyid integer NOT NULL DEFAULT nextval(('public.assemblies_assemblyid_seq'::text)::regclass),
assemblyname citext NOT NULL,
assemblytypeid integer DEFAULT 100,
productid integer,
leadfree boolean NOT NULL DEFAULT true,
CONSTRAINT assemblies_pkey PRIMARY KEY (assemblyid),
CONSTRAINT assemblies_assemblytypeid_fkey FOREIGN KEY (assemblytypeid)
REFERENCES assemblytype (assemblytypeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assemblies_productid_fkey FOREIGN KEY (productid)
REFERENCES products (productid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT uix_assemblies_assemblyname UNIQUE (assemblyname)
)
WITH OIDS;
ALTER TABLE assemblies OWNER TO postgres;
-- Index: ix_assemblytypeassemblies
-- DROP INDEX ix_assemblytypeassemblies;
CREATE INDEX ix_assemblytypeassemblies
ON assemblies
USING btree
(assemblytypeid);
-- Index: ix_leadfree
-- DROP INDEX ix_leadfree;
CREATE INDEX ix_leadfree
ON assemblies
USING btree
(leadfree);
-- Index: ix_relationship58
-- DROP INDEX ix_relationship58;
CREATE INDEX ix_relationship58
ON assemblies
USING btree
(productid);
-- Index: uix_assemblies_assemblyname
-- DROP INDEX uix_assemblies_assemblyname;
CREATE UNIQUE INDEX uix_assemblies_assemblyname
ON assemblies
USING btree
(assemblyname);
CREATE TABLE partsassembly
(
partsassemblyid integer NOT NULL DEFAULT nextval(('public.partsassembly_partsassemblyid_seq'::text)::regclass),
partid integer NOT NULL,
assemblyid integer NOT NULL,
quantity double precision,
unitid integer,
CONSTRAINT partsassembly_pkey PRIMARY KEY (partsassemblyid),
CONSTRAINT partsassembly_assemblyid_fkey FOREIGN KEY (assemblyid)
REFERENCES assemblies (assemblyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT partsassembly_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT partsassembly_unitid_fkey FOREIGN KEY (unitid)
REFERENCES units (unitid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE partsassembly OWNER TO postgres;
-- Index: assemblyidpa
-- DROP INDEX assemblyidpa;
CREATE INDEX assemblyidpa
ON partsassembly
USING btree
(assemblyid);
-- Index: idx_u_assidpartid
-- DROP INDEX idx_u_assidpartid;
CREATE UNIQUE INDEX idx_u_assidpartid
ON partsassembly
USING btree
(partid, assemblyid);
-- Index: ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_
-- DROP INDEX ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_;
CREATE INDEX ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_
ON partsassembly
USING btree
(partid);
-- Index: ix_5b3dd218_7383_402a_90e2_12458dd570ea_
-- DROP INDEX ix_5b3dd218_7383_402a_90e2_12458dd570ea_;
CREATE INDEX ix_5b3dd218_7383_402a_90e2_12458dd570ea_
ON partsassembly
USING btree
(assemblyid);
-- Index: ix_unitspartsassembly
-- DROP INDEX ix_unitspartsassembly;
CREATE INDEX ix_unitspartsassembly
ON partsassembly
USING btree
(unitid);
-- Index: partidpa
-- DROP INDEX partidpa;
CREATE INDEX partidpa
ON partsassembly
USING btree
(partid);
-- Index: partsassemblyid
-- DROP INDEX partsassemblyid;
CREATE INDEX partsassemblyid
ON partsassembly
USING btree
(partsassemblyid);
CREATE TABLE stocklog
(
stocklogid integer NOT NULL DEFAULT nextval(('public.stocklog_stocklogid_seq'::text)::regclass),
partid integer,
pnid integer,
ownerid integer,
quantity integer,
transtypeid integer,
out_deleted boolean,
refid integer,
poid integer,
commited boolean,
commitdate timestamp without time zone,
userid integer,
comments citext,
stocklocationid integer,
scanned boolean NOT NULL DEFAULT false,
scanneddate timestamp without time zone,
CONSTRAINT stocklog_pkey PRIMARY KEY (stocklogid),
CONSTRAINT stocklog_ownerid_fkey FOREIGN KEY (ownerid)
REFERENCES owners (ownerid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_stocklocationid_fkey FOREIGN KEY (stocklocationid)
REFERENCES stocklocations (stocklocationid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT stocklog_transtypeid_fkey FOREIGN KEY (transtypeid)
REFERENCES transtypes (transtypeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE stocklog OWNER TO postgres;
-- Index: ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_
-- DROP INDEX ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_;
CREATE INDEX ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_
ON stocklog
USING btree
(ownerid);
-- Index: ix_manufacturerpartpnstocklog
-- DROP INDEX ix_manufacturerpartpnstocklog;
CREATE INDEX ix_manufacturerpartpnstocklog
ON stocklog
USING btree
(pnid);
-- Index: ix_partsstocklog
-- DROP INDEX ix_partsstocklog;
CREATE INDEX ix_partsstocklog
ON stocklog
USING btree
(partid);
-- Index: ix_transtypesstocklog
-- DROP INDEX ix_transtypesstocklog;
CREATE INDEX ix_transtypesstocklog
ON stocklog
USING btree
(transtypeid);
-- Index: owneridsl
-- DROP INDEX owneridsl;
CREATE INDEX owneridsl
ON stocklog
USING btree
(ownerid);
-- Index: partidsl
-- DROP INDEX partidsl;
CREATE INDEX partidsl
ON stocklog
USING btree
(partid);
-- Index: poidsl
-- DROP INDEX poidsl;
CREATE INDEX poidsl
ON stocklog
USING btree
(poid);
-- Index: referenceidsl
-- DROP INDEX referenceidsl;
CREATE INDEX referenceidsl
ON stocklog
USING btree
(refid);
-- Index: stocklogid
-- DROP INDEX stocklogid;
CREATE INDEX stocklogid
ON stocklog
USING btree
(stocklogid);
-- Index: targetidsl
-- DROP INDEX targetidsl;
CREATE INDEX targetidsl
ON stocklog
USING btree
(transtypeid);
-- Index: useridsl
-- DROP INDEX useridsl;
CREATE INDEX useridsl
ON stocklog
USING btree
(userid);
REATE TABLE stock
(
stockid integer NOT NULL DEFAULT nextval(('public.stock_stockid_seq'::text)::regclass),
partid integer,
pnid integer,
ownerid integer,
stock integer NOT NULL DEFAULT 0,
stocklocationid integer,
batchid integer,
CONSTRAINT stock_pkey PRIMARY KEY (stockid),
CONSTRAINT stock_batchid_fkey FOREIGN KEY (batchid)
REFERENCES batches (batchid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT stock_ownerid_fkey FOREIGN KEY (ownerid)
REFERENCES owners (ownerid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_stocklocationid_fkey FOREIGN KEY (stocklocationid)
REFERENCES stocklocations (stocklocationid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE stock OWNER TO postgres;
-- Index: ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_
-- DROP INDEX ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_;
CREATE INDEX ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_
ON stock
USING btree
(ownerid);
-- Index: ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_
-- DROP INDEX ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_;
CREATE INDEX ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_
ON stock
USING btree
(partid);
-- Index: ix_manufacturerpartpnstock
-- DROP INDEX ix_manufacturerpartpnstock;
CREATE INDEX ix_manufacturerpartpnstock
ON stock
USING btree
(pnid);
-- Index: ownerids
-- DROP INDEX ownerids;
CREATE INDEX ownerids
ON stock
USING btree
(ownerid);
-- Index: partids
-- DROP INDEX partids;
CREATE INDEX partids
ON stock
USING btree
(partid);
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
