+-- another idea to solve the big ar problem
+
+-- see if we can match each ar transaction type against invoices.
+
+
+
+CREATE OR REPLACE FUNCTION x_ar_verify_creditmemo(i_aropen_id integer)
+ RETURNS TEXT
+AS $BODY$
+DECLARE
+ _ar RECORD;
+
+ v_sales_accnt_id INTEGER;
+ v_ar_accnt_id INTEGER;
+ v_credit_accnt_id INTEGER;
+ v_tmp INTEGER;
+
+ v_gl_id INTEGER;
+ v_gl_misc_id INTEGER;
+ v_new_rate NUMERIC;
+ v_gltrans_amount NUMERIC;
+ v_diff NUMERIC;
+ v_order_number TEXT;
+
+ v_c_amount NUMERIC;
+ v_c_rate NUMERIC;
+ v_c_base_amount NUMERIC;
+
+ v_gl_seq INTEGER;
+ v_gl_ar_amount NUMERIC;
+
+----
+ v_asset_accnt_id INTEGER;
+ v_resultbool BOOLEAN;
+ v_count INTEGER := 0;
+ v_skip_count INTEGER := 0;
+ v_gltrans_sequence INTEGER;
+ v_item_number TEXT;
+
+
+
+BEGIN
+
+ SELECT * INTO _ar FROM aropen where aropen_id = i_aropen_id;
+
+
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'not a valid open id..';
+ END IF;
+
+ IF _ar.aropen_journalnumber IS NULL THEN
+ RAISE EXCEPTION 'not a valid open id..';
+ END IF;
+
+ SELECT
+ --araccnt_freight_accnt_id
+ araccnt_ar_accnt_id -- 159
+ -- araccnt_prepaid_accnt_id ,
+ -- araccnt_deferred_accnt_id ,
+ -- araccnt_discount_accnt_id ,
+
+ INTO
+ v_ar_accnt_id
+ FROM
+ araccnt
+ ORDER BY
+ araccnt_id ASC
+ LIMIT 1;
+
+
+ -- we now have the base amount..
+
+ --- Bank receipt should be exactly this amount.
+ -- AR + Customer Deposit
+
+ SELECT
+ gltrans_id,
+ gltrans_amount,
+ ROUND( _ar.aropen_amount / _ar.aropen_curr_rate, 3 ) - ROUND(gltrans_amount, 3),
+ v_diff
+ INTO
+ v_gl_id,
+ v_gltrans_amount,
+ v_diff
+ FROM
+ gltrans
+
+ WHERE
+ CASE WHEN _ar.aropen_applyto IS NULL OR _ar.aropen_applyto = '' THEN
+ gltrans_docnumber = _ar.aropen_docnumber
+ AND
+ gltrans_doctype = 'CM'
+ ELSE
+ gltrans_docnumber = _ar.aropen_applyto
+ AND
+ gltrans_doctype = 'IN'
+ END
+ AND
+ gltrans_accnt_id = v_ar_accnt_id
+ AND
+ gltrans_source = 'A/R'
+ AND
+ gltrans_journalnumber = _ar.aropen_journalnumber;
+
+
+ IF NOT FOUND THEN
+
+ IF _ar.aropen_amount = 0.00 THEN
+ RAISE NOTICE 'skip aropen, zero dollar';
+ RETURN 'SKIP';
+ END IF;
+
+ RAISE EXCEPTION 'aropen is missing gltrans in A/R account aropen_id=%' ,i_aropen_id;
+ END IF;
+
+ IF (v_gl_misc_id IS NULL) THEN
+-- UPDATE gltrans SET gltrans_misc_id = i_aropen_id WHERE gltrans_id = v_gl_id;
+ END IF;
+
+ -- if currency rate is off...
+ IF v_diff != 0.00 THEN
+
+ v_new_rate := _ar.aropen_amount / v_gltrans_amount;
+
+ RAISE NOTICE 'aropen_id % change currency rate from % to %', i_aropen_id, _ar.aropen_curr_rate ,v_new_rate ;
+
+ UPDATE
+ aropen
+ SET
+ aropen_curr_rate = v_new_rate
+ WHERE
+ aropen_id = i_aropen_id;
+ END IF;
+
+
+ RAISE NOTICE 'VERIFIED % diff=%',i_aropen_id, v_diff;
+ RETURN 'DONE' || i_aropen_id;
+END
+
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+
+ALTER FUNCTION x_ar_verify_creditmemo(integer)
+ OWNER TO admin;
+
+
+
+
+SELECT x_ar_verify_creditmemo(aropen_id) FROM aropen where aropen_doctype = 'C' order by aropen_id asc;
+
+
+
+
+