> 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;
------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
