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