Refactored Payment.sql::payment_bulk_post()

[Erik Huelsmann <..hidden..>]

Hi Chris, others,


In my pursuit of posting the correct FX effects (gains/losses) when
creating payments, I'm looking at payment_bulk_post().

However, the function is too obfuscated to look at. Before I continue,
I'd like to factor out all the EXECUTE statements, so that the new
function becomes an posted below. I haven't tested yet, because I
don't know how to use the batch posting process, but it seems to load
very nicely.

Comments?


Bye,

Erik.



CREATE OR REPLACE FUNCTION payment_bulk_post
(in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
	in_ar_ap_accno text, in_cash_accno text,
	in_payment_date date, in_account_class int,
        in_exchangerate numeric, in_curr text)
RETURNS int AS
$$
DECLARE
	out_count int;
	t_voucher_id int;
	t_trans_id int;
	t_amount numeric;
        t_ar_ap_id int;
	t_cash_id int;
        t_currs text[];
        t_exchangerate numeric;
BEGIN
	IF in_batch_id IS NULL THEN
		-- t_voucher_id := NULL;
		RAISE EXCEPTION 'Bulk Post Must be from Batch!';
	ELSE
		INSERT INTO voucher (batch_id, batch_class, trans_id)
		values (in_batch_id,
                (SELECT batch_class_id FROM batch WHERE id = in_batch_id),
                in_transactions[1][1]);

		t_voucher_id := currval('voucher_id_seq');
	END IF;

	SELECT string_to_array(value, ':') into t_currs
          from defaults
         where setting_key = 'curr';

        IF (in_curr IS NULL OR in_curr = t_currs[0]) THEN
                t_exchangerate := 1;
        ELSE
                t_exchangerate := in_exchangerate;
        END IF;

	select id into t_ar_ap_id from chart where accno = in_ar_ap_accno;
	select id into t_cash_id from chart where accno = in_cash_accno;


        -- Set up the temp table with the transactions and amounts
from IN_TRANSACTIONS
	CREATE TEMPORARY TABLE bulk_payments_in (id int, amount numeric);
	FOR out_count IN
			array_lower(in_transactions, 1) ..
			array_upper(in_transactions, 1)
	LOOP
           INSERT INTO bulk_payments_in(id, amount)
	   VALUES (in_transactions[out_count][1], in_transactions[out_count][2]);
	END LOOP;

        -- Insert the credit side of the transactions
        INSERT INTO acc_trans
	    (trans_id, chart_id, amount, approved, voucher_id, transdate, source)
        SELECT id,
               CASE WHEN in_account_class = 1 THEN t_cash_id
	            WHEN in_account_class = 2 THEN t_ar_ap_id
		    ELSE -1
                END,
		amount * t_exchangerate,
		CASE WHEN t_voucher_id IS NULL THEN true ELSE false END,
		t_voucher_id, in_payment_date, in_source
		FROM bulk_payments_in  where amount <> 0;


        -- Insert the debit side of the transactions
        INSERT INTO acc_trans
	    (trans_id, chart_id, amount, approved, voucher_id, transdate, source)
        SELECT id,
               CASE WHEN in_account_class = 1 THEN t_ar_ap_id
	            WHEN in_account_class = 2 THEN t_cash_id
		    ELSE -1
                END,
		amount * -1 * t_exchangerate,
		CASE WHEN t_voucher_id IS NULL THEN true ELSE false END,
		t_voucher_id, in_payment_date, in_source
		FROM bulk_payments_in  where amount <> 0;

	DROP TABLE bulk_payments_in;
	perform unlock_all();
	return out_count;
END;
$$ language plpgsql;