Re: Refactored Payment.sql::payment_bulk_post()

[Erik Huelsmann <..hidden..>]

> So, if I understand correctly, you're referring to this PostgreSQL
> FAQ: http://wiki.postgresql.org/wiki/FAQ#Why_do_I_get_.22relation_with_OID_.23.23.23.23.23_does_not_exist.22_errors_when_accessing_temporary_tables_in_PL.2FPgSQL_functions.3F

If what the FAQ says is true, the function below could replace the
current one -- though still untested.


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;

        IF in_curr <> t_currs[0] THEN
           INSERT INTO acc_trans
              (trans_id, chart_id, amount, approved, voucher_id,
transdate, source)
           SELECT id,
                  CASE WHEN fx_diff < 0 THEN t_fxloss_id
                       WHEN fx_diff > 0 THEN t_fxgain_id
                       ELSE -1
                  END,
                  fx_diff,
                  CASE WHEN t_voucher_id IS NULL THEN true ELSE false END,
                  t_voucher_id, in_payment_date, in_source
             -- ### BUG: since there's no guarantee in_curr agrees
with the transaction's currency,
             --       we can't use in_curr's current rate as a basis
for FX gain / loss calculations
             FROM (SELECT id, amount * (t_exchangerate - fxrate) as fx_diff
                   FROM bulk_payments_in bp
                   JOIN (SELECT ap.id as id, ex.sell as fxrate
                           FROM ap
                           JOIN exchangerate ON (ap.curr = exchangerate.curr
                                                 AND ap.transdate =
exchangerate.transdate)
                         UNION
                         SELECT ar.id as id, ex.buy as fxrate
                           FROM ar
                           JOIN exchangerate ON (ar.curr = exchangerate.curr
                                                 AND ar.transdate =
exchangerate.transdate)
                          ) aa
                        ON bp.id = aa.id
        END IF;

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