-- Sequence: accnt_accnt_id_seq

--DROP table invadj;

 
CREATE SEQUENCE invadj_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE invadj_id_seq
  OWNER TO admin;
GRANT ALL ON TABLE invadj_id_seq TO admin;
GRANT ALL ON TABLE invadj_id_seq TO xtrole;


CREATE TABLE invadj
(
  invadj_id integer NOT NULL DEFAULT nextval(('invadj_id_seq'::text)::regclass),
  invadj_transdate  date,
  invadj_location_id integer,
  invadj_itemsite_id integer,
  invadj_qty_by integer,
  invadj_posted boolean,
  
    CONSTRAINT invadj_pkey PRIMARY KEY (invadj_id ),
    CONSTRAINT invadj_location_fkey FOREIGN KEY (invadj_location_id)
        REFERENCES location (location_id) 
      ON UPDATE CASCADE ON DELETE NO ACTION,
      
    CONSTRAINT invadj_itemsite_fkey FOREIGN KEY (invadj_itemsite_id)
      REFERENCES itemsite (itemsite_id) 
      ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

ALTER TABLE invadj ADD COLUMN   invadj_comments text;
ALTER TABLE invadj ADD COLUMN   invadj_voids_id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE invadj ADD COLUMN   invadj_invdetail_id INTEGER;
ALTER TABLE invadj ADD COLUMN   invadj_voided_by_id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE invadj ADD COLUMN   invadj_invadjgrp_id INTEGER DEFAULT NULL;
 

CREATE INDEX invadj_location_id_ix  ON invadj  USING btree  (invadj_location_id);
CREATE INDEX invadj_transdate_ix  ON invadj  USING btree  (invadj_transdate);
CREATE INDEX invadj_itemsite_ix  ON invadj  USING btree  (invadj_itemsite_id);
CREATE INDEX invadj_posted_ix  ON invadj USING btree  (invadj_posted);

CREATE INDEX invadj_voided_by_id_ix  ON invadj USING btree  (invadj_voided_by_id);
CREATE INDEX invadj_invdetail_id_ix  ON invadj USING btree  (invadj_invdetail_id);
CREATE INDEX invadj_voids_id_ix  ON invadj USING btree  (invadj_voids_id);

CREATE INDEX invadj_invadjgrp_id_ix  ON invadj USING btree  (invadj_invadjgrp_id);
 


ALTER TABLE invadj
  OWNER TO admin;
GRANT ALL ON TABLE invadj TO admin;
GRANT ALL ON TABLE invadj TO xtrole;
COMMENT ON TABLE invadj
  IS 'Inventory Adjustment Draft';
  
  
  
  


-- create dummy itemlocs

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;


  
-- 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;
--
--

