SF.net SVN: ledger-smb: [2018] trunk/sql

[..hidden..]

Revision: 2018
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2018&view=rev
Author:   einhverfr
Date:     2007-12-28 21:38:34 -0800 (Fri, 28 Dec 2007)

Log Message:
-----------
Adding inventory adjustment stored procedures

Modified Paths:
--------------
    trunk/sql/Pg-database.sql

Added Paths:
-----------
    trunk/sql/modules/Inventory.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2007-12-29 02:17:13 UTC (rev 2017)
+++ trunk/sql/Pg-database.sql	2007-12-29 05:38:34 UTC (rev 2018)
@@ -47,8 +47,6 @@
 
 COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
 
-
-
 -- USERS stuff --
 CREATE TABLE users (
     id serial UNIQUE, 
@@ -477,8 +475,28 @@
     primary_contact int references person(id),
     ar_ap_account_id int references chart(id),
     cash_account_id int references chart(id),
-    PRIMARY KEY(entity_id, meta_number)
+    PRIMARY KEY(entity_id, meta_number, entity_class),
 );
+
+CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u 
+ON entity_credit_account(meta_number)
+WHERE entity_class = 2;
+
+COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
+$$This index is used to ensure that AR accounts are not reused.$$;
+
+-- THe following credit accounts are used for inventory adjustments.
+INSERT INTO entity (name, entity_class) values ('Inventory Entity', 1);
+
+INSERT INTO company (legal_name, entity_id) 
+values ('Inventory Entity', currval('entity_id_seq');
+
+INSERT INTO entity_credit_account (entity_id, meta_number, entity_class)
+VALUES 
+(currval('entity_id_seq'), '00000', 1),
+(currval('entity_id_seq'), '00000', 2);
+
+
 -- notes are from entity_note
 -- ssn, iban and bic are from entity_credit_account
 -- 

Added: trunk/sql/modules/Inventory.sql
===================================================================
--- trunk/sql/modules/Inventory.sql	                        (rev 0)
+++ trunk/sql/modules/Inventory.sql	2007-12-29 05:38:34 UTC (rev 2018)
@@ -0,0 +1,52 @@
+CREATE OR REPLACE FUNCTION inventory_get_item_at_day
+(in_transdate date, in_partnumber text)
+RETURNS parts AS
+$$
+DECLARE out_row parts%ROWTYPE;
+BEGIN
+        SELECT p.id, p.partnumber, p.description, p.unit, p.listprice, 
+		p.sellprice, p.lastcost, p.priceupdate, p.weight, 
+                p.onhand - sum(coalesce(i.qty, 0))
+                AS onhand, p.notes, p.makemodel, p.assembly, p.alternate, 
+		p.rop, p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
+		p.bin, p.obsolete, p.bom, p.image, p.microfiche, p.partsgroup_id, 
+		p.project_id, p.avgcost
+	INTO out_row;
+        FROM parts p
+        LEFT JOIN invoice i ON (i.parts_id = p.id
+                AND i.trans_id IN 
+                        (select id FROM ar WHERE transdate > in_trans_date
+                        UNION 
+                        SELECT id FROM ap WHERE transdate > in_trans_date))
+        WHERE p.partnumber = in_partnumber
+                AND assembly IS FALSE AND obsolete IS NOT TRUE
+        GROUP BY p.id, p.partnumber, p.description, p.unit, p.listprice,
+                p.sellprice, p.lastcost, p.priceupdate, p.weight,
+                p.onhand, p.notes, p.makemodel, p.assembly, p.alternate,
+                p.rop, p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
+                p.bin, p.obsolete, p.bom, p.image, p.microfiche, p.partsgroup_id,
+                p.project_id, p.avgcost;
+
+	RETURN out_row;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION inventory_create_report(in_transdate date) RETURNS int
+AS
+$$
+BEGIN
+	INSERT INTO inventory_report(entry_date) values (in_transdate);
+	RETURN currval('inventory_report_id_seq');
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION inventory_report__add_line
+(in_report_id int, in_parts_id int, in_onhand int, in_counted int)
+RETURNS int AS
+$$
+BEGIN
+	INSERT INTO inventory_report_line(report_id, parts_id, onhand, counted)
+	VALUES (in_report_id, in_parts_id, in_onhand, in_counted);
+
+	RETURN currval('inventory_report_line_id_seq');
+$$ LANGUAGE plpgsql;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.