summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-12-21 01:07:57 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-12-21 01:07:57 +0000
commit27e466198957d4d5f6ed3b08bf8f8de191389e3f (patch)
treebc55ec1beec9767cb1c8ed03ee31eaeb3e8d1571 /sql
parent5ead0207f91dfecac4cafa8eb95cf7c50866d71f (diff)
More batch, vendor, and payment reversal fixes
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1987 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql')
-rw-r--r--sql/modules/Company.sql15
-rw-r--r--sql/modules/Payment.sql47
2 files changed, 53 insertions, 9 deletions
diff --git a/sql/modules/Company.sql b/sql/modules/Company.sql
index ff2795af..bcb0599e 100644
--- a/sql/modules/Company.sql
+++ b/sql/modules/Company.sql
@@ -97,6 +97,21 @@ BEGIN
END;
$$ LANGUAGE PLPGSQL;
+CREATE OR REPLACE FUNCTION entity_credit_get_id_by_meta_number
+(in_meta_number text, in_account_class int)
+returns int AS
+$$
+DECLARE out_credit_id int;
+BEGIN
+ SELECT id INTO out_credit_id
+ FROM entity_credit_account
+ WHERE meta_number = in_meta_number
+ AND entity_class = in_account_class;
+
+ RETURN out_credit_id;
+END;
+$$ LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION entity_list_contact_class()
RETURNS SETOF contact_class AS
$$
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 6c667828..f898528b 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -551,8 +551,10 @@ $$ This function return vendor or customer info, its under construction $$;
CREATE TYPE payment_record AS (
amount numeric,
meta_number text,
+ credit_id int,
company_paid text,
accounts text[],
+ source text,
date_paid date
);
@@ -565,11 +567,11 @@ DECLARE
out_row payment_record;
BEGIN
FOR out_row IN
- select sum(CASE WHEN c.entity_class = 1 then a.amount * -1
- ELSE a.amount END), c.meta_number,
- co.legal_name,
+ select sum(CASE WHEN c.entity_class = 1 then a.amount
+ ELSE a.amount * -1 END), c.meta_number,
+ c.id, co.legal_name,
compound_array(ARRAY[ARRAY[ch.id::text, ch.accno,
- ch.description]]), a.transdate
+ ch.description]]), a.source, a.transdate
FROM entity_credit_account c
JOIN ( select entity_credit_account, id
FROM ar WHERE in_account_class = 2
@@ -586,7 +588,9 @@ BEGIN
OR in_date_from IS NULL)
AND (a.transdate <= in_date_to OR in_date_to IS NULL)
AND (source = in_source OR in_source IS NULL)
- GROUP BY c.meta_number, co.legal_name, a.transdate
+ GROUP BY c.meta_number, c.id, co.legal_name, a.transdate,
+ a.source
+ ORDER BY a.transdate, c.meta_number, a.source
LOOP
RETURN NEXT out_row;
END LOOP;
@@ -595,12 +599,18 @@ $$ language plpgsql;
CREATE OR REPLACE FUNCTION payment__reverse
(in_source text, in_date_paid date, in_credit_id int, in_cash_accno text,
- in_date_reversed date, in_account_class int)
+ in_date_reversed date, in_account_class int, in_batch_id int)
RETURNS INT
AS $$
DECLARE
pay_row record;
+ t_voucher_id int;
+ t_voucher_inserted bool;
BEGIN
+ IF in_batch_id IS NOT NULL THEN
+ t_voucher_id := nextval('voucher_id_seq');
+ t_voucher_inserted := FALSE;
+ END IF;
FOR pay_row IN
SELECT a.*, c.ar_ap_account_id
FROM acc_trans a
@@ -618,15 +628,34 @@ BEGIN
AND in_credit_id = c.id
AND in_cash_accno = ch.accno
LOOP
+ IF in_batch_id IS NOT NULL
+ AND t_voucher_inserted IS NOT TRUE
+ THEN
+ INSERT INTO voucher
+ (id, trans_id, batch_id, batch_class)
+ VALUES
+ (t_voucher_id, pay_row.trans_id, in_batch_id,
+ CASE WHEN in_account_class = 1 THEN 4
+ WHEN in_account_class = 2 THEN 7
+ END);
+
+ t_voucher_inserted := TRUE;
+ END IF;
+
INSERT INTO acc_trans
- (trans_id, chart_id, amount, transdate, source, memo)
+ (trans_id, chart_id, amount, transdate, source, memo, approved,
+ voucher_id)
VALUES
(pay_row.trans_id, pay_row.chart_id, pay_row.amount * -1,
in_date_reversed, in_source, 'Reversing ' ||
- COALESCE(in_source, '')),
+ COALESCE(in_source, ''),
+ case when in_batch_id is not null then false
+ else true end, t_voucher_id),
(pay_row.trans_id, pay_row.ar_ap_account_id, pay_row.amount,
in_date_reversed, in_source, 'Reversing ' ||
- COALESCE(in_source, ''));
+ COALESCE(in_source, ''),
+ case when in_batch_id is not null then false
+ else true end, t_voucher_id);
IF in_account_class = 1 THEN
UPDATE ap SET paid = amount -
(SELECT sum(a.amount)