pgsql/investigations/ar-arapply.sql

diff --git a/pgsql/investigations/ar-arapply.sql b/pgsql/investigations/ar-arapply.sql
index e69de29..a0249bd 100644 (file)
--- a/pgsql/investigations/ar-arapply.sql
+++ b/pgsql/investigations/ar-arapply.sql
@@ -0,0 +1,150 @@
+-- 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_arapply(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 * -1.0,
+            ROUND( _ar.aropen_amount / _ar.aropen_curr_rate, 3 ) - ROUND(gltrans_amount * -1.0, 3),
+            v_diff
+    INTO
+            v_gl_id,
+            v_gltrans_amount,
+            v_diff
+    FROM
+            gltrans
+    
+    WHERE
+            gltrans_docnumber  = _ar.aropen_docnumber
+        AND
+            gltrans_doctype = 'DM'
+        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_arapply(integer)
+  OWNER TO admin;
+
+
+    
+    
+SELECT x_ar_verify_arapply(arapply_id) FROM arapply order by arapply_id asc;
+
+
+
+
+