2 CREATE OR REPLACE FUNCTION aropen_accountcheck_all()
16 araging(current_date,TRUE)
18 araging_docnumber LOOP
20 SELECT aropen_accountcheck(_r.araging_docnumber) INTO v_gltrans_val;
22 IF _r.araging_total_val + v_gltrans_val <> 0 THEN
23 RAISE EXCEPTION 'araging_docnumber = % , araging_doctype = % , GL is = % , AR is = %',_r.araging_docnumber,_r.araging_doctype,v_gltrans_val,_r.araging_total_val;
32 LANGUAGE plpgsql VOLATILE
35 ALTER FUNCTION aropen_accountcheck_all()
39 CREATE OR REPLACE FUNCTION aropen_accountcheck(i_docnumber TEXT)
45 v_gltrans_related TEXT[];
46 v_cashrcpt_related INT[];
47 v_ar_accnt_id INTEGER;
48 v_cd_accnt_id INTEGER;
49 v_cashrcptitem_id INTEGER;
60 araccnt_custtype = '.*'
64 RAISE EXCEPTION 'Missing Account Receivable Account';
74 accnt_descrip = 'Customer Deposits'
78 RAISE EXCEPTION 'Missing Customer Deposits Account';
81 v_related:= ARRAY[]::TEXT[];
82 v_gltrans_related := ARRAY[]::TEXT[];
83 v_cashrcpt_related := ARRAY[]::INT[];
85 SELECT aropen_related(i_docnumber, v_related) INTO v_related;
87 FOR i IN 1 .. array_upper(v_related,1) LOOP
88 IF (TRUE = ((SELECT strpos(v_related[i]::TEXT, '::')) > 0)) THEN
90 SELECT split_part(v_related[i]::TEXT, '::', 2) INTO v_cashrcptitem_id;
92 SELECT array_append(v_cashrcpt_related, v_cashrcptitem_id) INTO v_cashrcpt_related;
94 SELECT array_append(v_gltrans_related, v_related[i]) INTO v_gltrans_related;
99 SUM(COALESCE(gltrans_amount,0))
105 gltrans_docnumber = ANY (v_gltrans_related)
107 gltrans_accnt_id IN (v_ar_accnt_id, v_cd_accnt_id)
115 SUM(COALESCE(gltrans_amount,0)) + v_result
121 gltrans_misc_id IN (SELECT
122 DISTINCT(cashrcpt_id)
128 cashrcptitem_cashrcpt_id = cashrcpt_id
130 cashrcptitem_id = ANY (v_cashrcpt_related))
132 gltrans_source = 'A/R'
134 gltrans_doctype = 'CR'
136 gltrans_accnt_id IN (v_ar_accnt_id, v_cd_accnt_id)
143 RAISE NOTICE 'GL IS %',v_result;
147 SUM(araging_total_val)
152 araging(current_date,TRUE)
154 araging_docnumber = ANY (v_gltrans_related);
155 RAISE NOTICE 'AR IS %',v_result;
163 LANGUAGE plpgsql VOLATILE
166 ALTER FUNCTION aropen_accountcheck(TEXT)
171 CREATE OR REPLACE FUNCTION aropen_related(i_docnumber TEXT, i_related TEXT[])
180 IF (NOT ARRAY[i_docnumber] <@ i_related) THEN
181 SELECT array_append(i_related, i_docnumber) INTO i_related;
184 SELECT split_part(i_docnumber, '::', 1) INTO i_docnumber;
191 (arapply_target_docnumber = i_docnumber)
193 (arapply_source_docnumber = i_docnumber) LOOP
195 IF (_r.arapply_source_docnumber IS NOT NULL) THEN
196 v_number = _r.arapply_source_docnumber;
199 IF (_r.arapply_source_aropen_id = -1 AND _r.arapply_reftype IS NOT NULL AND _r.arapply_ref_id IS NOT NULL) THEN
200 v_number = _r.arapply_source_docnumber || '::' || _r.arapply_ref_id;
203 IF (NOT ARRAY[v_number] <@ i_related) THEN
204 SELECT aropen_related(v_number, i_related) INTO i_related;
207 IF (_r.arapply_target_docnumber IS NOT NULL) THEN
208 v_number = _r.arapply_target_docnumber;
211 IF (_r.arapply_target_aropen_id = -1 AND _r.arapply_reftype IS NOT NULL AND _r.arapply_ref_id IS NOT NULL) THEN
212 v_number = _r.arapply_target_docnumber || '::' || _r.arapply_ref_id;
215 IF (NOT ARRAY[v_number] <@ i_related) THEN
216 SELECT aropen_related(v_number, i_related) INTO i_related;
226 LANGUAGE plpgsql VOLATILE
229 ALTER FUNCTION aropen_related(TEXT, TEXT[])