PostgreSQL /* DROP TRIGGER ly_movement_trigger ON adempiere.ly_movement CREATE TRIGGER ly_movement_trigger AFTER INSERT OR UPDATE OR DELETE ON adempiere.ly_movement FOR EACH ROW EXECUTE PROCEDURE adempiere.ly_movement_f(); */ CREATE OR REPLACE FUNCTION adempiere.ly_movement_f() RETURNS TRIGGER AS $$ DECLARE v_LY_Storage_ID NUMERIC(10,0) := 0; pinstance NUMERIC(10,0) := 0; pinstance_id NUMERIC(10,0) := 0; v_message VARCHAR(200) := NULL; ResultStr VARCHAR(200) := NULL; v_ID NUMERIC(10,0) := 0; v_UU VARCHAR(36) := NULL; v_Detail_ID NUMERIC(10,0) := 0; v_Detail_UU VARCHAR(36) := NULL; v_MovementQty NUMERIC(10,0) := 0; v_BeforeQty NUMERIC(10,0) := 0; v_StorageQty NUMERIC(10,0) := 0; BEGIN IF pinstance is null THEN pinstance_id:=1000000; ELSE pinstance_id:=pinstance; END IF; v_message :='程式開始..'; IF pinstance_id > 0 THEN BEGIN ResultStr := 'PInstanceNotFound'; UPDATE adempiere.ad_pinstance SET updated = NOW(), isprocessing = 'Y', result = 0, errormsg = ResultStr WHERE ad_pinstance_id = pinstance_id; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN ------ v_LY_Storage_ID:=0; SELECT LY_Storage_ID INTO v_LY_Storage_ID FROM LY_Storage WHERE M_Product_ID = OLD.M_Product_ID AND C_BPartner_ID = OLD.C_BPartner_ID; ------ IF COALESCE(v_LY_Storage_ID,0) > 0 THEN UPDATE LY_Storage SET StorageQty = StorageQty - OLD.MovementQ WHERE LY_Storage_ID = v_LY_Storage_ID; -- IF NOT FOUND THEN RETURN NULL; END IF; END IF; v_LY_Storage_ID:=0; SELECT LY_Storage_ID, StorageQty INTO v_ID , v_StorageQty FROM LY_Storage WHERE M_Product_ID = OLD.M_Product_ID AND C_BPartner_ID = OLD.C_BPartnerTo_ID FOR UPDATE; ------ IF COALESCE(v_ID,0) > 0 THEN UPDATE LY_Storage SET StorageQty = StorageQty - OLD.MovementQ WHERE LY_Storage_ID = v_ID; v_MovementQty:= NVL(OLD.MovementQty,0); v_BeforeQty := v_StorageQty; v_StorageQty := v_BeforeQty - v_MovementQty; SELECT adempiere.nextid('LY_StorageDetail', 'N') INTO v_Detail_ID; SELECT adempiere.generate_uuid() INTO v_Detail_UU; INSERT INTO adempiere.LY_StorageDetail ( LY_StorageDetail_ID, LY_StorageDetail_UU, LY_Storage_ID, C_BPartner_ID, M_Product_ID, BeforeQty, MovementQty, StorageQty, LY_Movement_ID, AD_Client_ID, AD_Org_ID, Created, Updated, CreatedBy, UpdatedBy )VALUES( v_Detail_ID, v_Detail_UU, v_ID, OLD.C_BPartner_ID, OLD.M_Product_ID, v_BeforeQty, v_MovementQty, v_StorageQty, OLD.LY_Movement_ID, OLD.AD_Client_ID, OLD.AD_Org_ID, NOW(), NOW(), OLD.UpdatedBy, OLD.UpdatedBy); ELSE SELECT adempiere.nextid('LY_Storage', 'N') INTO v_ID; SELECT adempiere.generate_uuid() INTO v_UU; INSERT INTO adempiere.LY_Storage ( LY_Storage_ID, LY_Storage_UU, C_BPartner_ID, M_Product_ID, StorageQty, AD_Client_ID, AD_Org_ID, Created, Updated, CreatedBy, UpdatedBy )VALUES( v_ID, v_UU, OLD.C_BPartner_ID, OLD.M_Product_ID, NVL(OLD.MovementQty,0) * -1, OLD.AD_Client_ID, OLD.AD_Org_ID, NOW(), NOW(), OLD.UpdatedBy, OLD.UpdatedBy); v_MovementQty:= NVL(OLD.MovementQty,0); v_BeforeQty := 0; v_StorageQty := v_BeforeQty - v_MovementQty; SELECT adempiere.nextid('LY_StorageDetail', 'N') INTO v_Detail_ID; SELECT adempiere.generate_uuid() INTO v_Detail_UU; INSERT INTO adempiere.LY_StorageDetail ( LY_StorageDetail_ID, LY_StorageDetail_UU, LY_Storage_ID,C_BPartner_ID, M_Product_ID, BeforeQty, MovementQty, StorageQty, LY_Movement_ID, AD_Client_ID, AD_Org_ID, Created, Updated, CreatedBy, UpdatedBy )VALUES( v_Detail_ID, v_Detail_UU, v_ID, OLD.C_BPartner_ID, OLD.M_Product_ID, v_BeforeQty, v_MovementQty, v_StorageQty, OLD.LY_Movement_ID, OLD.AD_Client_ID, OLD.AD_Org_ID, NOW(), NOW(), OLD.UpdatedBy, OLD.UpdatedBy); END IF; ------ ELSIF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN SELECT LY_Storage_ID, StorageQty INTO v_ID, v_StorageQty FROM LY_Storage WHERE M_Product_ID = OLD.M_Product_ID AND C_BPartner_ID = OLD.C_BPartner_ID; ------ IF COALESCE(v_ID,0) > 0 THEN UPDATE LY_Storage SET StorageQty = StorageQty + NEW.MovementQ WHERE LY_Storage_ID = v_ID; v_MovementQty:= NVL(NEW.MovementQty,0); v_BeforeQty := v_StorageQty; v_StorageQty := v_BeforeQty + v_MovementQty; SELECT adempiere.nextid('LY_StorageDetail', 'N') INTO v_Detail_ID; SELECT adempiere.generate_uuid() INTO v_Detail_UU; INSERT INTO adempiere.LY_StorageDetail ( LY_StorageDetail_ID, LY_StorageDetail_UU, v_ID, C_BPartner_ID, M_Product_ID, BeforeQty, MovementQty, StorageQty, LY_Movement_ID, AD_Client_ID,AD_Org_ID,Created,Updated,CreatedBy,UpdatedBy )VALUES( v_Detail_ID, v_Detail_UU, NEW.C_BPartner_ID,NEW.M_Product_ID, v_BeforeQty, v_MovementQty, v_StorageQty, NEW.LY_Movement_ID, NEW.AD_Client_ID, NEW.AD_Org_ID, NOW(), NOW(), NEW.UpdatedBy, NEW.UpdatedBy); ELSE -- SELECT ad_sequence_id FROM adempiere.ad_sequence WHERE name='LY_Storage'; -- SELECT * FROM adempiere.LY_Storage SELECT adempiere.nextid('LY_Storage', 'N') INTO v_ID; SELECT adempiere.generate_uuid() INTO v_UU; INSERT INTO adempiere.LY_Storage ( LY_Storage_ID, LY_Storage_UU, C_BPartner_ID, M_Product_ID, StorageQty, AD_Client_ID, AD_Org_ID, Created, Updated, CreatedBy, UpdatedBy )VALUES( v_ID, v_UU, NEW.C_BPartner_ID, NEW.M_Product_ID, NEW.MovementQty, NEW.AD_Client_ID, NEW.AD_Org_ID, NOW(), NOW(), NEW.UpdatedBy, NEW.UpdatedBy); v_MovementQty:= NVL(NEW.MovementQty,0); v_BeforeQty := v_StorageQty; v_StorageQty := v_BeforeQty + v_MovementQty; SELECT adempiere.nextid('LY_StorageDetail', 'N') INTO v_Detail_ID; SELECT adempiere.generate_uuid() INTO v_Detail_UU; INSERT INTO adempiere.LY_StorageDetail ( LY_StorageDetail_ID, LY_StorageDetail_UU, v_ID, C_BPartner_ID, M_Product_ID, BeforeQty, MovementQty, StorageQty, LY_Movement_ID, AD_Client_ID,AD_Org_ID,Created,Updated,CreatedBy,UpdatedBy )VALUES( v_Detail_ID, v_Detail_UU, NEW.C_BPartner_ID, NEW.M_Product_ID, v_BeforeQty, v_MovementQty, v_StorageQty, NEW.LY_Movement_ID, NEW.AD_Client_ID, NEW.AD_Org_ID, NOW(), NOW(), NEW.UpdatedBy, NEW.UpdatedBy); END IF; END IF; --- v_message :='程式完成::'; IF pinstance_id > 0 THEN BEGIN UPDATE adempiere.ad_pinstance SET updated = NOW(), isprocessing = 'N', result = 1, errormsg = v_message WHERE ad_pinstance_id = pinstance_id; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; RETURN NULL; EXCEPTION WHEN OTHERS THEN v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message; IF pinstance_id > 0 THEN BEGIN ResultStr := v_message; UPDATE adempiere.ad_pinstance SET updated = NOW(), isprocessing = 'N', result = 0, errormsg = ResultStr WHERE ad_pinstance_id = pinstance_id; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; END; $$ LANGUAGE plpgsql;

/*
DROP TRIGGER ly_movement_trigger ON adempiere.ly_movement


CREATE TRIGGER ly_movement_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON adempiere.ly_movement
  FOR EACH ROW
  EXECUTE PROCEDURE adempiere.ly_movement_f();
*/

CREATE OR REPLACE FUNCTION adempiere.ly_movement_f() RETURNS TRIGGER AS $$
DECLARE
   v_LY_Storage_ID NUMERIC(10,0) := 0;
   pinstance       NUMERIC(10,0) := 0;
   pinstance_id    NUMERIC(10,0) := 0;
   v_message       VARCHAR(200)  := NULL;
   ResultStr       VARCHAR(200)  := NULL;
   v_ID            NUMERIC(10,0) := 0;
   v_UU            VARCHAR(36)   := NULL;
   v_Detail_ID     NUMERIC(10,0) := 0;
   v_Detail_UU     VARCHAR(36)   := NULL;
   v_MovementQty   NUMERIC(10,0) := 0;
   v_BeforeQty     NUMERIC(10,0) := 0;
   v_StorageQty    NUMERIC(10,0) := 0;
BEGIN
 
   IF pinstance is null THEN
      pinstance_id:=1000000;
   ELSE
      pinstance_id:=pinstance;
   END IF;
   v_message :='程式開始..';
   IF pinstance_id > 0 THEN
      BEGIN
         ResultStr := 'PInstanceNotFound';
         UPDATE adempiere.ad_pinstance
            SET updated      = NOW(),
                isprocessing = 'Y',
                result       = 0,
                errormsg     = ResultStr
          WHERE ad_pinstance_id = pinstance_id;
      EXCEPTION
           WHEN OTHERS THEN NULL;
      END;
   END IF;

   IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN
------
       v_LY_Storage_ID:=0;
       SELECT LY_Storage_ID INTO v_LY_Storage_ID
         FROM LY_Storage
        WHERE M_Product_ID  = OLD.M_Product_ID
          AND C_BPartner_ID = OLD.C_BPartner_ID;
------
      IF COALESCE(v_LY_Storage_ID,0) > 0 THEN
         UPDATE LY_Storage SET StorageQty = StorageQty - OLD.MovementQ WHERE LY_Storage_ID = v_LY_Storage_ID;
            -- IF NOT FOUND THEN RETURN NULL; END IF;
      END IF;

      v_LY_Storage_ID:=0;
      SELECT LY_Storage_ID, StorageQty INTO v_ID , v_StorageQty
        FROM LY_Storage
       WHERE M_Product_ID  = OLD.M_Product_ID
         AND C_BPartner_ID = OLD.C_BPartnerTo_ID
         FOR UPDATE;
------
      IF COALESCE(v_ID,0) > 0 THEN
         UPDATE LY_Storage SET StorageQty = StorageQty - OLD.MovementQ WHERE LY_Storage_ID = v_ID;

         v_MovementQty:= NVL(OLD.MovementQty,0);
         v_BeforeQty  := v_StorageQty;
         v_StorageQty := v_BeforeQty - v_MovementQty;
         SELECT adempiere.nextid('LY_StorageDetail', 'N') INTO v_Detail_ID;
         SELECT adempiere.generate_uuid()                 INTO v_Detail_UU;
         INSERT INTO adempiere.LY_StorageDetail (
                     LY_StorageDetail_ID, LY_StorageDetail_UU, LY_Storage_ID, C_BPartner_ID, M_Product_ID,
                     BeforeQty, MovementQty, StorageQty, LY_Movement_ID,
                     AD_Client_ID, AD_Org_ID, Created, Updated, CreatedBy, UpdatedBy
            )VALUES( v_Detail_ID, v_Detail_UU, v_ID, OLD.C_BPartner_ID, OLD.M_Product_ID,
                     v_BeforeQty, v_MovementQty, v_StorageQty, OLD.LY_Movement_ID,
                     OLD.AD_Client_ID, OLD.AD_Org_ID, NOW(), NOW(), OLD.UpdatedBy, OLD.UpdatedBy);


      ELSE
         SELECT adempiere.nextid('LY_Storage', 'N') INTO v_ID;
         SELECT adempiere.generate_uuid()           INTO v_UU;
         INSERT INTO adempiere.LY_Storage (
                     LY_Storage_ID, LY_Storage_UU, C_BPartner_ID, M_Product_ID, StorageQty,
                     AD_Client_ID, AD_Org_ID, Created, Updated, CreatedBy, UpdatedBy
            )VALUES( v_ID, v_UU, OLD.C_BPartner_ID, OLD.M_Product_ID, NVL(OLD.MovementQty,0) * -1,
                     OLD.AD_Client_ID, OLD.AD_Org_ID, NOW(), NOW(), OLD.UpdatedBy, OLD.UpdatedBy);

         v_MovementQty:= NVL(OLD.MovementQty,0);
         v_BeforeQty  := 0;
         v_StorageQty := v_BeforeQty - v_MovementQty;
         SELECT adempiere.nextid('LY_StorageDetail', 'N') INTO v_Detail_ID;
         SELECT adempiere.generate_uuid()                 INTO v_Detail_UU;
         INSERT INTO adempiere.LY_StorageDetail (
                     LY_StorageDetail_ID, LY_StorageDetail_UU, LY_Storage_ID,C_BPartner_ID, M_Product_ID,
                     BeforeQty, MovementQty, StorageQty, LY_Movement_ID,
                     AD_Client_ID, AD_Org_ID, Created, Updated, CreatedBy, UpdatedBy
            )VALUES( v_Detail_ID, v_Detail_UU, v_ID, OLD.C_BPartner_ID, OLD.M_Product_ID,
                     v_BeforeQty, v_MovementQty, v_StorageQty, OLD.LY_Movement_ID,
                     OLD.AD_Client_ID, OLD.AD_Org_ID, NOW(), NOW(), OLD.UpdatedBy, OLD.UpdatedBy);
      END IF;
------
   ELSIF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
       SELECT LY_Storage_ID, StorageQty INTO v_ID, v_StorageQty
         FROM LY_Storage
        WHERE M_Product_ID = OLD.M_Product_ID
          AND C_BPartner_ID = OLD.C_BPartner_ID;
------    
      IF COALESCE(v_ID,0) > 0 THEN
         UPDATE LY_Storage SET StorageQty = StorageQty + NEW.MovementQ WHERE LY_Storage_ID = v_ID;

         v_MovementQty:= NVL(NEW.MovementQty,0);
         v_BeforeQty  := v_StorageQty;
         v_StorageQty := v_BeforeQty + v_MovementQty;
         SELECT adempiere.nextid('LY_StorageDetail', 'N') INTO v_Detail_ID;
         SELECT adempiere.generate_uuid()                 INTO v_Detail_UU;
         INSERT INTO adempiere.LY_StorageDetail (
                     LY_StorageDetail_ID, LY_StorageDetail_UU, v_ID, C_BPartner_ID, M_Product_ID,
                     BeforeQty, MovementQty, StorageQty, LY_Movement_ID,
                     AD_Client_ID,AD_Org_ID,Created,Updated,CreatedBy,UpdatedBy
            )VALUES( v_Detail_ID, v_Detail_UU, NEW.C_BPartner_ID,NEW.M_Product_ID,
                     v_BeforeQty, v_MovementQty, v_StorageQty, NEW.LY_Movement_ID,
                     NEW.AD_Client_ID, NEW.AD_Org_ID, NOW(), NOW(), NEW.UpdatedBy, NEW.UpdatedBy);
      ELSE
      -- SELECT ad_sequence_id FROM adempiere.ad_sequence WHERE name='LY_Storage';
      -- SELECT * FROM adempiere.LY_Storage
         SELECT adempiere.nextid('LY_Storage', 'N') INTO v_ID;
         SELECT adempiere.generate_uuid()           INTO v_UU;
         INSERT INTO adempiere.LY_Storage (
                     LY_Storage_ID, LY_Storage_UU, C_BPartner_ID, M_Product_ID, StorageQty,
                     AD_Client_ID, AD_Org_ID, Created, Updated, CreatedBy, UpdatedBy
            )VALUES( v_ID, v_UU, NEW.C_BPartner_ID, NEW.M_Product_ID, NEW.MovementQty,
                     NEW.AD_Client_ID, NEW.AD_Org_ID, NOW(), NOW(), NEW.UpdatedBy, NEW.UpdatedBy);

         v_MovementQty:= NVL(NEW.MovementQty,0);
         v_BeforeQty  := v_StorageQty;
         v_StorageQty := v_BeforeQty + v_MovementQty;
         SELECT adempiere.nextid('LY_StorageDetail', 'N') INTO v_Detail_ID;
         SELECT adempiere.generate_uuid()                 INTO v_Detail_UU;
         INSERT INTO adempiere.LY_StorageDetail (
                     LY_StorageDetail_ID, LY_StorageDetail_UU, v_ID, C_BPartner_ID, M_Product_ID,
                     BeforeQty, MovementQty, StorageQty, LY_Movement_ID,
                     AD_Client_ID,AD_Org_ID,Created,Updated,CreatedBy,UpdatedBy
            )VALUES( v_Detail_ID, v_Detail_UU, NEW.C_BPartner_ID, NEW.M_Product_ID,
                     v_BeforeQty, v_MovementQty, v_StorageQty, NEW.LY_Movement_ID,
                     NEW.AD_Client_ID, NEW.AD_Org_ID, NOW(), NOW(), NEW.UpdatedBy, NEW.UpdatedBy);
                   
      END IF;
   END IF;
---
   v_message :='程式完成::';
   IF pinstance_id > 0 THEN
      BEGIN
         UPDATE adempiere.ad_pinstance
            SET updated      = NOW(),
                isprocessing = 'N',
                result       = 1,
                errormsg     = v_message
          WHERE ad_pinstance_id = pinstance_id;
      EXCEPTION
            WHEN OTHERS THEN NULL;
      END;
   END IF;

   RETURN NULL;
 
EXCEPTION WHEN OTHERS THEN
   v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
   IF pinstance_id > 0 THEN
      BEGIN
         ResultStr := v_message;
         UPDATE adempiere.ad_pinstance
            SET updated      = NOW(),
                isprocessing = 'N',
                result       = 0,
                errormsg     = ResultStr
          WHERE ad_pinstance_id = pinstance_id;
      EXCEPTION
         WHEN OTHERS THEN NULL;
      END;
   END IF;
END;
$$ LANGUAGE plpgsql;