summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-01-17 01:51:21 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2008-01-17 01:51:21 +0000
commit7fde4d6119cccd548d46adcee7c88b9d1e6a756f (patch)
treeef504df7d0025a0ef39c3fea9a608d39784fe8f8
parentde145349db9f7170d5b79581c951b15d5967b129 (diff)
Adding new payment posting routine for single payments from David Mora
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2049 4979c152-3d1c-0410-bac9-87ea11338e46
-rw-r--r--sql/modules/Payment.sql129
1 files changed, 89 insertions, 40 deletions
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 1f68d293..e5b02f3c 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -384,52 +384,101 @@ sub-array, the first element is the (integer) transaction id, and the second
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;
-
--- 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.
---$$;
+-- I HAVE TO MAKE A COMMENT ON THIS FUNCTION
-- Move this to the projects module when we start on that. CT