CREATE OR REPLACE FUNCTION payment_post
(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 var_payment_id int;
DECLARE var_gl_id int;
DECLARE var_entry_id int[];
DECLARE out_count int;
BEGIN
-- 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/Setti
ELSE -- and it is very us
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 t
-- 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
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;