Adding inventory adjustment stored procedures

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2018 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 52 insertions, 0 deletions

diff --git a/sql/modules/Inventory.sql b/sql/modules/Inventory.sql
new file mode 100644
index 00000000..fa4d6d0e
--- /dev/null
+++ b/sql/modules/Inventory.sql
@@ -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;