[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[5532] trunk/sql
- Subject: SF.net SVN: ledger-smb:[5532] trunk/sql
- From: ..hidden..
- Date: Thu, 10 Jan 2013 10:30:24 +0000
Revision: 5532
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5532&view=rev
Author: einhverfr
Date: 2013-01-10 10:30:23 +0000 (Thu, 10 Jan 2013)
Log Message:
-----------
Correcting load errors for sql/modules/Goods.sql for inventory reports
Modified Paths:
--------------
trunk/sql/Pg-database.sql
trunk/sql/modules/Fixes.sql
trunk/sql/modules/Goods.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2013-01-10 07:58:34 UTC (rev 5531)
+++ trunk/sql/Pg-database.sql 2013-01-10 10:30:23 UTC (rev 5532)
@@ -1770,10 +1770,11 @@
);
CREATE TABLE inventory_report_line (
- report_id int REFERENCES inventory_report(id),
+ adjust_id int REFERENCES inventory_report(id),
parts_id int REFERENCES parts(id),
counted numeric,
expected numeric,
+ variance numeric,
PRIMARY KEY (report_id, parts_id)
);
Modified: trunk/sql/modules/Fixes.sql
===================================================================
--- trunk/sql/modules/Fixes.sql 2013-01-10 07:58:34 UTC (rev 5531)
+++ trunk/sql/modules/Fixes.sql 2013-01-10 10:30:23 UTC (rev 5532)
@@ -145,3 +145,12 @@
REFERENCES acc_trans(entry_id);
COMMIT;
+
+BEGIN;
+
+ALTER TABLE inventory_report_line add adjust_id int not null;
+
+ alter table inventory_report_line add variance numeric not null;
+
+
+COMMIT;
Modified: trunk/sql/modules/Goods.sql
===================================================================
--- trunk/sql/modules/Goods.sql 2013-01-10 07:58:34 UTC (rev 5531)
+++ trunk/sql/modules/Goods.sql 2013-01-10 10:30:23 UTC (rev 5532)
@@ -192,15 +192,16 @@
FROM invoice i
JOIN (select id, transdate from ar WHERE APPROVED
union
- select id, transdate FROM ap WHERE APPROVED) a ON i.trans_id
+ select id, transdate FROM ap WHERE APPROVED) a ON i.trans_id = a.id
GROUP BY a.transdate, i.parts_id;
CREATE VIEW order_sum AS
-SELECT o.transdate,
- sum(oi.shipped) * case when oe_id_class = 1 THEN 1 ELSE -1 END as qty,
+SELECT oe.transdate,
+ sum(oi.ship * case when oe_class_id = 1 THEN 1 ELSE -1 END) as qty,
oi.parts_id
FROM orderitems oi
- JOIN oe ON oe.closed is false and oe_class_id in (1, 2);
+ JOIN oe ON oe.closed is false and oe_class_id in (1, 2)
+ GROUP BY oe.transdate, oi.parts_id;
CREATE OR REPLACE FUNCTION inventory__search_part
(in_parts_id int, in_partnumber text, in_counted_date date)
@@ -209,18 +210,20 @@
WITH RECURSIVE assembly_comp (a_id, parts_id, qty) AS (
SELECT id, parts_id, qty FROM assembly
UNION ALL
- SELECT ac.id, a.parts_id, ac.qty * a.qty
- FROM assembly a JOIN assembly_com ac ON a.id = ac.parts_id
-),
+ SELECT ac.a_id, a.parts_id, ac.qty * a.qty
+ FROM assembly a JOIN assembly_comp ac ON a.parts_id = ac.parts_id
+)
SELECT p.id, p.partnumber,
sum((coalesce(i.qty, 0) + coalesce(oi.qty, 0)) * a.qty )
FROM parts p
- LEFT JOIN assembly_comp a ON a.id = p.id
- LEFT JOIN invoice_sum i ON i.parts_id = o.id OR a.parts_id = i.parts_id
- LEFT JOIN order_sum oi ON i.parts OR a.parts_id = i.parts_id
+ LEFT JOIN assembly_comp a ON a.a_id = p.id
+ LEFT JOIN invoice_sum i ON i.parts_id = p.id OR a.parts_id = i.parts_id
+ LEFT JOIN order_sum oi ON oi.parts_id = p.id OR a.parts_id = i.parts_id
WHERE p.id = $1 OR p.partnumber = $2
OR (p.id IN (select parts_id FROM makemodel WHERE barcode = $2)
- AND NOT EXISTS (select id from parts where id = $2))
+ AND NOT EXISTS (select id from parts
+ where partnumber = $2 AND NOT obsolete
+ ))
and (i.transdate is null or i.transdate <= $3)
AND (oi.transdate IS NULL OR oi.transdate <= $3)
GROUP BY p.id, p.partnumber;
@@ -229,10 +232,10 @@
CREATE OR REPLACE FUNCTION inventory_adjust__save_line
(in_adjust_id int, in_parts_id int,
in_counted numeric, in_expected numeric, in_variance numeric)
-RETURNS inventory_adjustment_line
+RETURNS inventory_report_line
LANGUAGE SQL AS
$$
-INSERT INTO inventory_adjustment_line
+INSERT INTO inventory_report_line
(adjust_id, parts_id, counted, expected, variance)
VALUES ($1, $2, $3, $4, $5)
RETURNING *;
@@ -240,23 +243,23 @@
CREATE OR REPLACE FUNCTION inventory_adjust__save_info
(in_transdate date, in_source text)
-RETURNS inventory_adjustment_info
+RETURNS inventory_report
LANGUAGE SQL AS
$$
-INSERT INTO inventory_adjustment_info(transdate, source)
+INSERT INTO inventory_report(transdate, source)
VALUES ($1, $2)
RETURNING *;
$$;
-CREATE OR REPLACE FUNCTION inventory_adjust__approve(in_id)
-RETURNS inventory_adjustment_line language plpgsql as
+CREATE OR REPLACE FUNCTION inventory_adjust__approve(in_id int)
+RETURNS inventory_report_line language plpgsql as
$$
-DECLARE inv inventory_adjustment_info;
+DECLARE inv inventory_report;
t_ar ar;
t_ap ap;
BEGIN
-SELECT * INTO inv FROM inventory_adjustment_info where id = in_id;
+SELECT * INTO inv FROM inventory_report where id = in_id;
INSERT INTO ar (entity_credit_account, invnumber, invoice, approved,
amount, netamount, transdate)
@@ -265,7 +268,7 @@
SELECT * INTO t_ar FROM ar WHERE id = currval('id');
-UPDATE inventory_adjustment_info
+UPDATE inventory_report
set ar_trans_id = t_ar.id,
ar_invnumber = t_ar.invnumber
WHERE id = in_id;
@@ -274,7 +277,7 @@
discount)
SELECT t_ar.id, p.id, p.description, l.variance * -1, p.sellprice, 3, 1
FROM parts p
- JOIN inventory_adjust_line l ON p.id = l.parts_id
+ JOIN inventory_report_line l ON p.id = l.parts_id
WHERE l.adjust_id = in_id;
INSERT INTO ap (entity_credit_account, invnumber, invoice, approved, amount,
@@ -283,12 +286,12 @@
't', 'f', sum(l.variance * p.sellprice), sum(l.variance * p.sellprice),
inv.transdate
FROM parts p
- JOIN inventory_adjust_line l ON p.id = l.parts_id
+ JOIN inventory_report_line l ON p.id = l.parts_id
WHERE l.adjust_id = in_id;
SELECT * INTO t_ap FROM ap WHERE id = currval('id');
-UPDATE inventory_adjustment_info
+UPDATE inventory_report
set ap_trans_id = t_ap.id,
ap_invnumber = t_ap.invnumber
WHERE id = in_id;
@@ -297,48 +300,50 @@
discount, transdate)
SELECT t_ap.id, p.id, p.description, l.variance * -1, p.sellprice, 3, 0
FROM parts p
- JOIN inventory_adjust_line l ON p.id = l.parts_id
+ JOIN inventory_report_line l ON p.id = l.parts_id
WHERE l.adjust_id = in_id;
INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, approved)
SELECT t_ap.id, p.expense_accno_id, sum(l.variance * -1 * p.lastcost),
inv.transdate, true
FROM parts p
- JOIN inventory_adjust_line l ON p.id = l.parts_id
+ JOIN inventory_report_line l ON p.id = l.parts_id
WHERE l.adjust_id = in_id
GROUP BY p.expense_accno_id
UNION
SELECT t_ap.id, eca.ar_ap_accno_id, sum(l.variance * -1 * p.lastcost),
inv.transdate, true
FROM parts p
- JOIN inventory_adjust_line l ON p.id = l.parts_id
+ JOIN inventory_report_line l ON p.id = l.parts_id
JOIN entity_credit_account eca on eca_id = t_ap.entity_credit_account
WHERE l.adjust_id = in_id
GROUP BY eca.ar_ap_accno_id;
-SELECT * INTO inv FROM inventory_adjustment_info where id = in_id;
+SELECT * INTO inv FROM inventory_report where id = in_id;
RETURN inv;
END;
$$;
-CREATE OR REPLACE FUNCTION inventory_adjust__delete(in_id)
+CREATE OR REPLACE FUNCTION inventory_adjust__delete(in_id int)
RETURNS BOOL LANGUAGE PLPGSQL AS
$$
-DECLARE inv inventory_adjustemnt_info;
+DECLARE inv inventory_report;
BEGIN
-SELECT * INTO inv FROM inventory_adjustment_info where id = in_id;
+SELECT * INTO inv FROM inventory_report where id = in_id;
IF NOT FOUND THEN
RETURN FALSE;
ELSIF inv.ap_trans_id IS NOT NULL OR inv.ar_trans_id IS NOT NULL THEN
RAISE EXCEPTION 'Set is Already Approved!';
END IF;
-DELETE FROM inventory_adjustment_line where adjust_id = in_id;
-DELETE FROM inventory_adjustment_info where id = in_id;
+DELETE FROM inventory_report_line where adjust_id = in_id;
+DELETE FROM inventory_report where id = in_id;
RETURN TRUE;
+
+END;
$$;
COMMIT;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.