pgsql/investigations/ar-debitmemo.sql

diff --git a/pgsql/investigations/ar-debitmemo.sql b/pgsql/investigations/ar-debitmemo.sql
index a732413..7b0c830 100644 (file)
--- a/pgsql/investigations/ar-debitmemo.sql
+++ b/pgsql/investigations/ar-debitmemo.sql
@@ -51,24 +51,10 @@ BEGIN
     IF _ar.aropen_journalnumber IS NULL THEN
         RAISE EXCEPTION 'not a valid open id..';
     END IF;
-    
-    -- verify dupes
-    SELECT count(aropen_id) INTO v_tmp FROM aropen WHERE
-        aropen_journalnumber = _ar.aropen_journalnumber;
-    
-    IF (v_tmp != 1 ) THEN
-        RAISE EXCEPTION 'duplicate aropen with same journal number % found ' , _ar.aropen_journalnumber;
-    END IF;
-        
-    --  
-    
-    -- looking for  (123 (discrep) ,135,153);
- 
-    -- alterntaive case - an invoice is to/from accnt id 150/149 (direct ship?);
 
     SELECT
           --araccnt_freight_accnt_id  
-            araccnt_ar_accnt_id   -- 153
+            araccnt_ar_accnt_id   -- 159
            -- araccnt_prepaid_accnt_id ,
            -- araccnt_deferred_accnt_id ,
            -- araccnt_discount_accnt_id ,
@@ -97,7 +83,6 @@ BEGIN
             salesaccnt_id ASC
         LIMIT 1;
 
-    
                     
     -- we now have the base amount..
     
@@ -105,105 +90,63 @@ BEGIN
     --  AR + Customer Deposit
     
     SELECT
-            gltrans_sequence,
-            gltrans_amount
+            gltrans_id,
+            gltrans_amount,
+            ROUND( _ar.aropen_amount / _ar.aropen_curr_rate, 3 ) - ROUND(gltrans_amount, 3),
+            v_diff
     INTO
-            v_gl_seq,
-            v_gl_ar_amount
+            v_gl_id,
+            v_gltrans_amount,
+            v_diff
     FROM
             gltrans
+    
     WHERE
-            gltrans_journalnumber = _ar.aropen_journalnumber
+            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;
-    
-    IF NOT FOUND THEN
-        -- if it's a reciept without any applications?
-        -- then nothing is posted to A/R
-        SELECT
-               gltrans_sequence,
-               gltrans_amount
-           INTO
-               v_gl_seq,
-               v_gl_ar_amount
-           FROM
-               gltrans
-           WHERE
-            
-                gltrans_journalnumber = _ar.aropen_journalnumber
-           AND
-               gltrans_accnt_id = v_credit_accnt_id
-               AND
-               gltrans_amount > 0;
-         IF NOT FOUND THEN
-    
-            RAISE EXCEPTION 'can not find sequence for journaln nubmer %, accnt_id = %',_ar.aropen_journalnumber,v_credit_accnt_id ;
-        END IF;
-    END IF;
-            
-    -- we should verify this apopen..
-            
+            gltrans_accnt_id = v_ar_accnt_id
+        AND
+            gltrans_source = 'A/R'
+        AND
+            gltrans_journalnumber = _ar.aropen_journalnumber;
             
-    SELECT
-            gltrans_id,
-            gltrans_misc_id,
-            gltrans_amount * -1.0,
-            ROUND(  _ar.aropen_amount / _ar.aropen_curr_rate, 3 ) - ROUND(-1.0 * gltrans_amount,3)
-        INTO
-            v_gl_id,
-            v_gl_misc_id,
-            v_gltrans_amount,
-            v_diff
-        FROM
-            gltrans
-        WHERE
-            gltrans_sequence = v_gl_seq 
-            AND
-            gltrans_accnt_id = v_credit_accnt_id
-            AND
-            -- within $1
-             ROUND(  _ar.aropen_amount / _ar.aropen_curr_rate, 2 ) > (ROUND(-1.0 * gltrans_amount,2) -1)
-             AND
-             ROUND( _ar.aropen_amount / _ar.aropen_curr_rate, 2 ) < (ROUND(-1.0 * gltrans_amount,2) +1)
-            AND
-            gltrans_source        = 'A/R'
-            AND
-            gltrans_doctype       = 'DM'
-        LIMIT 1;
 
     IF NOT FOUND THEN
-     
-        -- IF v_order_number IS NULL OR v_order_number = '' THEN
-        --    RAISE NOTICE 'skip aropen, as it points to a direct shipment?';
-        --    RETURN 'SKIP';
-       -- END IF;
-    
-       -- IF _ar.aropen_amount = 0.00 THEN
-        --    RAISE NOTICE 'skip aropen, zero dollar';
-        --    RETURN 'SKIP';
-        -- END IF;
-    
     
+        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=%   glseq=%, accnt_id=% value=%' ,i_aropen_id, v_gl_seq, v_credit_accnt_id, _ar.aropen_amount / _ar.aropen_curr_rate ;
+        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
+    IF v_diff != 0.00  THEN
     
-        v_new_rate :=    _ar.aropen_amount / v_gltrans_amount;
+        v_new_rate := _ar.aropen_amount / v_gltrans_amount;
         
-        RAISE NOTICE 'change currency rate from % to %', _ar.aropen_curr_rate  ,v_new_rate ;
+        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;
+        UPDATE 
+                aropen
+        SET
+                aropen_curr_rate  = v_new_rate
+        WHERE
+                aropen_id = i_aropen_id;
     END IF;