SF.net SVN: ledger-smb:[2759] addons/1.3

[..hidden..]

Revision: 2759
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2759&view=rev
Author:   einhverfr
Date:     2009-08-24 17:40:19 +0000 (Mon, 24 Aug 2009)

Log Message:
-----------
template transaction work from last week committing now

Added Paths:
-----------
    addons/1.3/templatetrans/
    addons/1.3/templatetrans/tags/
    addons/1.3/templatetrans/trunk/
    addons/1.3/templatetrans/trunk/sql/
    addons/1.3/templatetrans/trunk/sql/modules/
    addons/1.3/templatetrans/trunk/sql/modules/trans-functions.sql
    addons/1.3/templatetrans/trunk/sql/modules/trans-tables.sql

Added: addons/1.3/templatetrans/trunk/sql/modules/trans-functions.sql
===================================================================
--- addons/1.3/templatetrans/trunk/sql/modules/trans-functions.sql	                        (rev 0)
+++ addons/1.3/templatetrans/trunk/sql/modules/trans-functions.sql	2009-08-24 17:40:19 UTC (rev 2759)
@@ -0,0 +1,49 @@
+-- Many of these will have to be rewritten to work with 1.4
+
+CREATE OR REPLACE FUNCTION journal__add(
+in_source text,
+in_description text,
+in_entry_type int,
+in_transaction_date,
+in_approved bool,
+in_department_id int, 
+in_is_template bool
+) RETURNS journal AS 
+$$
+DECLARE retval journal;
+BEGIN
+	INSERT INTO journal (source, description, entry_type, transaction_date,
+			approved, department_id, is_template)
+	VALUES (in_source, in_description, in_entry_type, in_transaction_date,
+			coalesce(in_approved, false), in_department_id, 
+			coalesce(in_is_template, false));
+
+	SELECT * INTO retval FROM journal WHERE id = currval('journal_id_seq');
+	RETURN retval;
+END;
+$$ language plpgsql; 
+
+CREATE OR REPLACE FUNCTION journal__add_line(
+in_account_id, in_journal_id, in_amount numeric, in_cleared bool, in_memo text
+) RETURNS journal_line AS $$
+DECLARE retval journal_line;
+BEGIN
+	INSERT INTO journal_line(account_id, journal_id, amount, cleared, memo)
+	VALUES (in_account_id, in_journal_id, in_amount, 
+		coalesce(in_cleared, false), in_memo);
+	SELECT * INTO retval FROM journal_line where line_id = currval('journal_line_line_id_seq');
+	return retval;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION journal__validate_entry(in_id) RETURNS bool AS
+$$
+	SELECT sum(amount) = 0 FROM journal_line WHERE id = in_id;
+$$ language sql;
+
+CREATE OR REPLACE FUNCTION journal__make_invoice(
+);
+
+CREATE OR REPLACE FUNCTION journal__list_templates(
+);
+-- orders with inventory not supported yet.

Added: addons/1.3/templatetrans/trunk/sql/modules/trans-tables.sql
===================================================================
--- addons/1.3/templatetrans/trunk/sql/modules/trans-tables.sql	                        (rev 0)
+++ addons/1.3/templatetrans/trunk/sql/modules/trans-tables.sql	2009-08-24 17:40:19 UTC (rev 2759)
@@ -0,0 +1,99 @@
+-- based on 1.4 table structure.  This will allow less rewrite
+-- and easier QA on 1.3.
+
+
+CREATE journal_entry_type (
+id serial,
+type text primary key
+);
+
+create table journal (
+source text not null,  -- invoice number or source document number
+id serial not null,
+description text not null,
+locked_by int references session(id) on delete set null, -- used for
+entry_type int references journal_entry_type(id),
+transaction_date date not null default now(),
+approved bool default false, -- this way we do vouchers by default,
+department_id int references department(id) not null, 
+is_template bool default false,
+primary_key(id), -- no other set of fields guaranteed to be unique
+check (is_template is false or approved is false)
+);
+
+COMMENT ON TABLE journal IS $$journal replaces gl and transactions and also is the base table for all transactions$$;
+
+create table journal_line (
+account_id int references account(id)  not null,
+journal_id int references journal(id) not null,
+amount numeric not null check (amount <> 'NaN'),
+cleared bool not null default false,
+memo text,
+entered_by int references user(entity_id),
+line_id serial not null,
+primary key (entry_id) -- required to be a surrogate key because it is possible to have a single payment which pays two identical bills.
+);
+
+create table payment_map (
+line_id int references journal_line(line_id),
+journal_id int references journal(id) not null,
+primary key(line_id)
+);
+
+COMMENT ON TABLE payment_map IS $$payment_map provides the map between 
+journal_lines and the paid journal entry.  This is almost backward of how SL 
+and LSMB 1.3 do it but it allows for better tracking of individual payments. $$;
+
+create table forex_line (
+line_id int references journal_line(line_id),
+currency char(3), -- should we look at making a separate currency
+table?  Maybe attaching to country?
+primary key (line_id)
+);
+
+create table order_type (
+id serial not null unique,
+label text primary key
+); -- PO, RFQ, Customer RFQ, Sales Order, Quotation, Vendor Quotation,
+
+create table orders ( -- an invoice is treated as an order with a corresponding journal entry.
+credit_account int references entity_credit_account(id) not null,
+id serial not null,
+include_tax bool default false not null,
+terms int not null default 0,
+language_id int references language(id)
+order_number text not null
+);
+
+create table invoice ( -- journal.source would hold the invoice number
+order_id int references orders(id),  -- in the case of what we
+currently do as AR/AP transactions, this would be null
+journal_id int references journal(id),
+on_hold bool default false,
+reverse bool default false,
+credit_account int references entity_credit_account(id) not null,
+language_id int references language(id),
+PRIMARY KEY  (journal_id)
+);
+
+CREATE TABLE order_line ( -- replaces invoice and order_item
+entry_id serial not null unique,
+parts_id int references parts(id),
+quantity numeric not null check (quantity <> 'NaN'),
+lot_price numeric not null check (quantity <> 'NaN'),
+allocated numeric not null check (allocated <> 'NaN' AND
+abs(allocated) < abs(quantity))
+allocated_price numeric not null check(allocated_price <> 'NaN' AND
+abs(allocated_price) < abs(lot_price), -- tracking allocated prices
+separately so that we can handle purchase quantities in intelligeable
+ways.
+lot_size numeric not null check (lot_size <> 'NaN'),
+lot_qty numeric not null check (lot_qtty <> 'NaN'),
+displayed_sku text not null,
+displayed_description text not null
+);
+
+CREATE TABLE forex_order_line (
+entry_id int not null primary key references order_line(entry_id),
+fx_lot_price numeric not null ot null check (fx_lot_price <> 'NaN')
+);


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