[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [2049] trunk/sql/modules/Payment.sql
- Subject: SF.net SVN: ledger-smb: [2049] trunk/sql/modules/Payment.sql
- From: ..hidden..
- Date: Wed, 16 Jan 2008 17:51:27 -0800
Revision: 2049
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2049&view=rev
Author: einhverfr
Date: 2008-01-16 17:51:21 -0800 (Wed, 16 Jan 2008)
Log Message:
-----------
Adding new payment posting routine for single payments from David Mora
Modified Paths:
--------------
trunk/sql/modules/Payment.sql
Modified: trunk/sql/modules/Payment.sql
===================================================================
--- trunk/sql/modules/Payment.sql 2008-01-15 19:52:19 UTC (rev 2048)
+++ trunk/sql/modules/Payment.sql 2008-01-17 01:51:21 UTC (rev 2049)
@@ -384,54 +384,103 @@
is the amount for that transaction. If the total of the amounts do not add up
to in_total, then an error is generated. $$;
+
CREATE OR REPLACE FUNCTION payment_post
-(in_trans_id int, in_batch_id int, in_source text, in_amount numeric,
- in_ar_ap_accno text, in_cash_accno text, in_approved bool,
- in_payment_date date, in_account_class int)
+(in_payment_date date,
+ in_account_class int,
+ in_person_id int,
+ in_currency char(3),
+ in_notes text,
+ in_department int,
+ in_gl_description text,
+ in_cash_accno int[],
+ in_cash_amount int[],
+ in_cash_approved bool[],
+ in_cash_source text[],
+ in_accno int[],
+ in_amount int[],
+ in_approved bool[],
+ in_source text[],
+ in_transaction_id int[],
+ in_type int[],
+ in_approved bool)
RETURNS INT AS
$$
-DECLARE out_entry_id int;
+DECLARE var_payment_id int;
+DECLARE var_gl_id int;
+DECLARE var_entry_id int[];
+DECLARE out_count int;
+
BEGIN
- INSERT INTO acc_trans (chart_id, amount,
- trans_id, transdate, approved, source)
- VALUES ((SELECT id FROM chart WHERE accno = in_ar_ap_accno),
- CASE WHEN in_account_class = 1 THEN in_amount * -1
- ELSE amount
- END,
- in_trans_id, in_payment_date, in_approved, in_source);
-
- INSERT INTO acc_trans (chart_id, amount,
- trans_id, transdate, approved, source)
- VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno),
- CASE WHEN in_account_class = 2 THEN in_amount * -1
- ELSE amount
- END,
- in_trans_id, in_payment_date, coalesce(in_approved, true),
- in_source);
-
- SELECT currval('acc_trans_entry_id_seq') INTO out_entry_id;
- RETURN out_entry_id;
+ -- FIRST WE HAVE TO INSERT THE PAYMENT
+ -- THE ID IS GENERATED BY payment_id_seq
+ --
+ INSERT INTO payment (reference, payment_class, payment_date,
+ person_id, currency, notes, department_id)
+ VALUES ((CASE WHEN in_account_class = 1 THEN
+ setting_increment('rcptnumber') -- I FOUND THIS ON sql/modules/Settings.sql
+ ELSE -- and it is very usefull
+ setting_increment('paynumber')
+ END),
+ in_account_class, in_payment_date, in_person_id,
+ in_currency, in_notes, in_department);
+ SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table
+ -- SECOND WE HAVE TO MAKE THE GL TO HOLD THE TRANSACTIONS
+ -- THE ID IS GENERATED BY gl_id_seq
+ --
+ INSERT INTO gl (reference, description, transdate,
+ person_id, notes, approved, department_id)
+ VALUES (setting_increment('glnumber'),
+ in_gl_description, in_payment_date, in_person_id,
+ in_notes, in_department, coalesce(in_approved, true));
+ SELECT currval('id') INTO var_gl_id; -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT
+ -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS...
+ --
+ -- FIRST WE SHOULD INSERT THE CASH ACCOUNTS
+ --
+ -- WE SHOULD HAVE THE DATA STORED AS (ACCNO, AMOUNT), SO
+ FOR out_count IN
+ array_lower(in_cash_accno, 1) ..
+ array_upper(in_cash_accno, 1)
+ LOOP
+ INSERT INTO acc_trans (chart_id, amount,
+ trans_id, transdate, approved, source)
+ VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno[out_count]),
+ CASE WHEN in_account_class = 2 THEN in_cash_amount[out_count] * -1
+ ELSE in_cash_amount[out_count]
+ END,
+ var_gl_id, in_payment_date, coalesce(in_cash_approved[1], true),
+ in_cash_source[out_count]);
+ --SELECT currval('acc_trans_entry_id_seq') INTO var_entry_id[out_count];--WE'LL NEED THIS FOR THE PAYMENT_LINK
+ END LOOP;
+ --
+ -- NOW LETS HANDLE THE AR/AP/OVERPAYMENT ACCOUNT
+ --
+ FOR var_count IN
+ array_lower(in_accno, 1) ..
+ array_upper(in_accno, 1)
+ LOOP
+ INSERT INTO acc_trans (chart_id, amount,
+ trans_id, transdate, approved, source)
+ VALUES ((SELECT id FROM chart WHERE accno = in_accno[out_count]),
+ CASE WHEN in_account_class = 2 THEN in_amount[out_count] * -1
+ ELSE in_amount[out_count]
+ END,
+ var_gl_id, in_payment_date, coalesce(in_approved[1], true),
+ in_source[out_count]);
+ --
+ -- WE WILL INSERT THE LINK INTO PAYMENT_LINKS NOW
+ --
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval(acc_trans_entry_id_seq),
+ in_transaction_id[out_count], in_type[var_count]);
+ END LOOP;
+ return 0;
END;
$$ LANGUAGE PLPGSQL;
+-- I HAVE TO MAKE A COMMENT ON THIS FUNCTION
--- COMMENT ON FUNCTION payment_post
--- (in_trans_id int, in_source text, in_amount numeric, in_ar_ap_accno text,
--- in_cash_accno text, in_approved bool, in_payment_date date,
--- in_account_class int)
---IS $$
---This function takes the following arguments (prefaced with in_ in the db):
---trans_id: Id for ar/ap transaction.
---source: text for source documnet identifier (for example, check number)
---amount: numeric for the amount of the transaction
---ar_ap_accno: AR/AP account number
---cash_accno: Cash Account number, i.e. the account where the payment will be
---held
---approved: False, for a voucher.
---This function posts the payment or saves the payment voucher.
---$$;
-
-
-- Move this to the projects module when we start on that. CT
CREATE OR REPLACE FUNCTION project_list_open(in_date date)
RETURNS SETOF project AS
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.