sync

diff --git a/pgsql/invadj_post.sql b/pgsql/invadj_post.sql
new file mode 100644 (file)
index 0000000..bd54d99
--- /dev/null
+++ b/pgsql/invadj_post.sql
@@ -0,0 +1,193 @@
+
+-- create dummy itemlocs
+
+  
+-- create dummy itemlocs
+
+CREATE OR REPLACE FUNCTION invadj_post(int)
+  RETURNS  INTEGER  AS
+$BODY$
+DECLARE
+  
+    i_invadj_id ALIAS FOR $1;
+
+    r_invadj RECORD;
+    r_dist RECORD;
+    
+    v_invdetail_id INTEGER;
+  
+    v_series INTEGER;
+    v_result INTEGER;
+    v_result_bool BOOLEAN;
+BEGIN
+  
+  
+    SELECT
+            *
+        INTO
+            r_invadj
+        FROM
+            invadj
+        WHERE
+            invadj_id = i_invadj_id;
+    
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'could not find invadj record';
+    END IF;
+    
+    IF r_invadj.invadj_posted  THEN
+        RAISE EXCEPTION 'invadj already posted';
+    END IF;
+  
+  
+    IF   r_invadj.invadj_voids_id > 0 THEN 
+        
+        SELECT
+                invadj_voided_by_id
+            INTO
+                v_result
+            FROM
+                invadj
+            WHERE
+                invadj_id = r_invadj.invadj_voids_id;
+                
+                
+                
+        IF NOT FOUND THEN
+            RAISE EXCEPTION 'voids id is invalid';
+        END IF;
+        IF v_result > 0 THEN
+            RAISE EXCEPTION 'voids id points to an already voided record';
+        END IF;
+            
+    END IF;
+  
+  
+    SELECT invAdjustment(
+                    r_invadj.invadj_itemsite_id,
+                    r_invadj.invadj_qty_by,
+                    'INVADJ-' || r_invadj.invadj_id,
+                    r_invadj.invadj_comments,
+                    r_invadj.invadj_transdate
+                    -- uses std cost?
+                ) INTO v_series;
+            
+    IF v_series < 1 THEN
+        RAISE EXCEPTION  'invAdjustment post failed with %', v_series;
+    END IF;
+        
+    SELECT
+                itemlocdist_id, itemlocdist_reqlotserial,
+                itemlocdist_distlotserial, itemlocdist_qty,
+                
+                itemsite_loccntrl, itemsite_controlmethod,
+                itemsite_perishable, itemsite_warrpurc,
+                
+                COALESCE(itemsite_lsseq_id,-1) AS itemsite_lsseq_id,
+                COALESCE(itemlocdist_source_id,-1) AS itemlocdist_source_id
+            INTO
+                r_dist
+            FROM
+                itemlocdist, itemsite
+            WHERE
+                (
+                    (itemlocdist_itemsite_id=itemsite_id) AND (itemlocdist_series=v_series )
+                )
+            ORDER BY itemlocdist_id;
+            
+            
+    IF NOT FOUND THEN
+         RAISE EXCEPTION 'invAdjustment failed to create itemlocdist';
+    END IF;
+        
+        
+        
+    INSERT INTO itemlocdist (
+       itemlocdist_itemlocdist_id,  itemlocdist_source_type,
+       itemlocdist_source_id,  itemlocdist_qty,
+       itemlocdist_ls_id, itemlocdist_expiration
+   )
+       SELECT
+       itemlocdist_id,       'L',
+       r_invadj.invadj_location_id,       r_invadj.invadj_qty_by,
+       itemlocdist_ls_id, endOfTime()
+       FROM itemlocdist WHERE (itemlocdist_id=r_dist.itemlocdist_id);
+
+        
+    SELECT distributeToLocations(r_dist.itemlocdist_id) INTO v_result;
+    
+    IF NOT FOUND OR v_result < 0 THEN
+        RAISE EXCEPTION 'distributeToLocations failed  ';
+    END IF;
+          
+    
+    SELECT postItemlocseries(v_series) INTO v_result_bool ;
+    
+    IF NOT v_result_bool  THEN
+        RAISE EXCEPTION 'postItemlocseries failed  ';
+    END IF;
+        
+    -- finally - if the record was a void - make refernce in the voided data..    
+    IF  r_invadj.invadj_voids_id > 0 THEN
+    
+        UPDATE invadj SET
+            invadj_voided_by_id = r_invadj.invadj_id
+            WHERE
+            invadj_id = r_invadj.invadj_voids_id ;
+            
+            
+    END IF;
+  
+    SELECT
+            invdetail_id
+        INTO
+            v_invdetail_id
+        FROM
+            invdetailview
+        WHERE
+            invhist_ordnumber = 'INVADJ-' || r_invadj.invadj_id
+        LIMIT 1;
+    
+  
+    UPDATE invadj SET
+            invadj_posted = true,
+            invadj_invdetail_id = v_invdetail_id
+            WHERE
+            
+            invadj_id = r_invadj.invadj_id;
+  
+    
+    
+    
+    -- we need to update the invhist id..
+    
+    IF  r_invadj.invadj_voids_id > 0 OR  r_invadj.invadj_voided_by_id > 0  THEN
+        -- try running the void flag code.
+        PERFORM invfifo_invadj_void_flag(v_invdetail_id);
+    END IF;
+    
+    
+    
+   RETURN v_result;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION  invadj_post(int)
+  OWNER TO admin;
+
+
+
+-- sync:
+--DELETE FROM invadj;
+--SELECT invadj_sync_invdetail(invdetail_id) FROM invdetailview where invhist_transtype = 'AD' ORDER BY invhist_transdate ASC;
+
+
+--- FIX the fact i forgot to update invdetail id..
+
+--UPDATE invadj
+--    SET invadj_invdetail_id = (SELECT invdetail_id FROM invdetailview WHERE invhist_ordnumber = 'INVADJ-' || invadj_id LIMIT 1)
+--    WHERE invadj_posted  and invadj_invdetail_id IS NULL;
+--
+--
+

diff --git a/pgsql/invadj_sync_invdetail.sql b/pgsql/invadj_sync_invdetail.sql
new file mode 100644 (file)
index 0000000..8c2a58d
--- /dev/null
+++ b/pgsql/invadj_sync_invdetail.sql
@@ -0,0 +1,84 @@
+
+CREATE OR REPLACE FUNCTION invadj_sync_invdetail(int)
+  RETURNS  int  AS
+$BODY$
+DECLARE
+  
+  i_invdetail_id ALIAS FOR $1;
+
+  r_invfifo RECORD;
+  v_id INTEGER;
+   
+    
+BEGIN
+
+    SELECT
+            *
+        INTO
+            r_invfifo
+        FROM
+            invdetailview
+        WHERE
+            invdetail_id = i_invdetail_id;
+    
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'could not find invdetail record';
+    END IF;
+    
+    IF r_invfifo.invhist_transtype != 'AD' THEN
+        RAISE EXCEPTION 'record is not an adjustment';
+    END IF;
+    
+    SELECT
+            invadj_id
+        INTO
+            v_id
+        FROM
+            invadj
+        WHERE
+            invadj_invdetail_id = r_invfifo.invdetail_id;
+            
+    IF FOUND THEN
+        RETURN v_id;
+    END IF;
+    
+    INSERT INTO
+        invadj
+        (
+            
+            invadj_transdate  , invadj_location_id ,
+            invadj_itemsite_id , invadj_qty_by ,
+            
+            invadj_posted , invadj_comments,
+            invadj_voids_id, invadj_invdetail_id,
+            invadj_voided_by_id
+        )
+            VALUES
+        (
+            r_invfifo.invhist_transdate::date, r_invfifo.invdetail_location_id,
+            r_invfifo.invhist_itemsite_id, r_invfifo.invdetail_qty,
+            
+            true, r_invfifo.invhist_ordnumber || '/' || r_invfifo.invhist_ordnumber  || ' : '|| r_invfifo.invhist_comments,
+            0, r_invfifo.invdetail_id,
+            0
+            
+        );
+        
+    SELECT
+            invadj_id
+        INTO
+            v_id
+        FROM
+            invadj
+        WHERE
+            invadj_invdetail_id = r_invfifo.invdetail_id;
+          
+
+   RETURN v_id;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION  invadj_sync_invdetail(int)
+  OWNER TO admin;
+