SF.net SVN: ledger-smb:[6594] trunk/sql/modules/Payment.sql

[..hidden..]

Revision: 6594
          http://sourceforge.net/p/ledger-smb/code/6594
Author:   einhverfr
Date:     2014-01-22 07:57:15 +0000 (Wed, 22 Jan 2014)
Log Message:
-----------
Moved payment__reverse_overpayment to overpayment__reverse, and rewrote it to be more robust.

Modified Paths:
--------------
    trunk/sql/modules/Payment.sql

Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql	2014-01-22 06:53:16 UTC (rev 6593)
+++ trunk/sql/modules/Payment.sql	2014-01-22 07:57:15 UTC (rev 6594)
@@ -1486,49 +1486,7 @@
 SELECT * FROM gl WHERE id = (select id from payment where id = $1);
 $$;
 
-CREATE OR REPLACE FUNCTION payment__reverse_overpayment
-(in_payment_id int, in_batch_id int) 
-RETURNS voucher LANGUAGE PLPGSQL AS
-$$
-DECLARE retval voucher;
-        t_batch_class int;
-        t_gl_id int;
-      in_account_class int;
-BEGIN
-    SELECT entity_class INTO in_account_class 
-      FROM entity_credit_account
-     WHERE id = (select entity_credit_id FROM overpayments 
-                  WHERE payment_id = in_payment_id);
 
-    IF in_account_class = 1 THEN
-       t_batch_class := 4;
-    ELSIF in_account_class = 2 THEN
-       t_batch_class := 7;
-    ELSE
-       RAISE EXCEPTION 'bad account class';
-    END IF;
-
-    SELECT gl_id INTO t_gl_id FROM payment WHERE payment_id = in_payment_id;
-
-    INSERT INTO voucher(batch_id, trans_id, batch_class)
-    SELECT in_batch_id, t_gl_id, t_batch_class
-    RETURNING * INTO retval;
-
-    INSERT INTO acc_trans(trans_id, chart_id, amount, transdate, source, 
-                          fx_transaction, memo, approved, voucher_id)
-    SELECT a.trans_id, a.chart_id, a.amount, b.default_date, 
-           'reversing ' || a.source, a.fx_transaction, a.memo, false, retval.id
-      FROM acc_trans a
-      JOIN batch b ON (b.id = in_batch_id)
- LEFT JOIN payment_links pl ON pl.entry_id = a.entry_id
-     WHERE trans_id = t_gl_id or pl.entry_id is not null;
-
-    UPDATE overpayment set available = 0 WHERE payment_id = in_payment_id;
-
-    RETURN retval;
-END;
-$$;
-
 DROP TYPE IF EXISTS overpayment_list_item CASCADE;
 CREATE TYPE overpayment_list_item AS (
   payment_id int,
@@ -1566,6 +1524,51 @@
        ($5 IS NULL OR e.name @@ plainto_tsquery($5));
 $$;
 
+CREATE OR REPLACE FUNCTION overpayment__reverse 
+(in_id int, in_transdate date, in_batch_id int, in_account_class int,
+in_cash_accno text, in_exchangerate numeric, in_curr char(3))
+returns bool LANGUAGE PLPGSQL AS
+$$
+declare t_id int;
+BEGIN
+
+-- reverse overpayment gl
+
+INSERT INTO gl (transdate, reference, description, approved)
+SELECT transdate, reference || '-reversal', 'reversal of ' || description, '0'
+  FROM gl WHERE id = in_id;
+
+t_id := curval('id');
+
+INSERT INTO voucher (batch_id, trans_id, batch_class)
+VALUES (in_batch_id, t_id, CASE WHEN in_account_class == 1 THEN 4 ELSE 7 END);
+
+INSERT INTO acc_trans (transdate, trans_id, chart_id, amount)
+SELECT in_transdate, t_id, chart_id, amount * -1
+  FROM acc_trans
+ WHERE trans_id = in_id;
+
+-- reverse overpayment usage
+SELECT payment__reverse(ac.source, ac.transdate, eca.id, in_cash_accno,
+        in_transdate, eca.entity_class, in_batch_id, null, 
+        in_exchangerate, in_currency)
+  FROM acc_trans ac
+  JOIN (select id, entity_credit_account FROM ar UNION
+        select id, entity_credit_account from ap) a ON a.id = ac.trans_id
+  JOIN entity_credit_account eca ON a.entity_credit_account = eca.id
+  JOIN payment_links pl ON pl.entry_id = a.entry_id
+  JOIN overpayment op ON op.id = pl.payment_id
+  JOIN payment p ON p.id = o.payment_id
+ WHERE p.gl_id = in_id
+GROUP BY ac.source, ac.transdate, eca.id, eca.entity_class;
+
+UPDATE overpayment set available = 0 
+ WHERE payment_id = (select id from payment where gl_id = in_id);
+
+RETURN TRUE;
+END;
+$$;
+
 update defaults set value = 'yes' where setting_key = 'module_load_ok';
 
 COMMIT;

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


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits