#20-03-2011 Nabil update two triggers PurchaseDT_AU and PurchaseDT_AI create last cost in itemtrans Grant All on *.* to root@"%"; ###################################################################################################### #Purchases Tables Drop Trigger if exists Purchase_BD; Drop Trigger if exists Purchase_BD; Delimiter $$ CREATE Trigger Purchase_BD Before Delete on Purchases For Each Row Begin Delete From PurchasesDt WHERE PurchasesDt.Purchase_id = old.Purchase_id; END $$ Delimiter; ###################################################################################################### # PurchaseDT Table Drop Trigger if exists PurchaseDT_AI; Delimiter $$ CREATE Trigger PurchaseDT_AI After Insert on PurchasesDT For Each Row Begin UPDATE Purchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem + 1 where Purchases.Purchase_ID = new.Purchase_ID; if new.Journal_Type = 'PI' then Set @Sign = 1; UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity where Purchases.Purchase_ID = new.Purchase_ID; else if new.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; Set @ItemUnit = 0; Select Item_Code, Item_Unit,Item_LastDatePurchase into @ItemCode, @ItemUnit ,@LastPurchaseDate From Items WHERE Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; #Ayman 03.12.2009 Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then Set @Price = new.PurchaseDt_Price + @Sign*new.PurchaseDT_Charges/new.PurchaseDt_Quantity - new.PurchaseDt_Price*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountAmount/new.PurchaseDt_Quantity; Set @PriceBase1 = new.PurchaseDt_PriceBase1 + @Sign*new.PurchaseDT_ChargesBase1/new.PurchaseDt_Quantity - new.PurchaseDt_PriceBase1*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase1/new.PurchaseDt_Quantity; Set @PriceBase2 = new.PurchaseDt_PriceBase2 + @Sign*new.PurchaseDT_ChargesBase2/new.PurchaseDt_Quantity - new.PurchaseDt_PriceBase2*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase2/new.PurchaseDt_Quantity; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,@Sign*new.PurchaseDt_Quantity,@Price,ifnull(@PriceBase1,0),ifnull(@PriceBase2,0),new.Synchronized,ifnull(@PriceBase1,0),ifnull(@PriceBase2,0),new.Item_UnitCoef,new.Item_Unit2Qty, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo ); else Set @Price=((new.PurchaseDt_Price/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_Charges/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_Price/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountAmount/(@ItemUnit*new.PurchaseDt_Quantity))); Set @PriceBase1=((new.PurchaseDt_PriceBase1/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase1/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_PriceBase1/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase1/(@ItemUnit*new.PurchaseDt_Quantity))); Set @PriceBase2=((new.PurchaseDt_PriceBase2/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase2/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_PriceBase2/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase2/(@ItemUnit*new.PurchaseDt_Quantity))); if @Price is null then set @Price = 0;end if; if @PriceBase1 is null then set @PriceBase1 = 0;end if; if @PriceBase2 is null then set @PriceBase2 = 0;end if; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo) #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*@ItemUnit),@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2); Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code)) ,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo); end if; if new.RelatedDt_Id <> 0 and new.journal_type = 'PI' then Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO'; end if; # select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = new.Purchase_id; # select module_freezone into @module from module limit 1; # if @module then # update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn + @sign * new.PurchaseDt_Quantity # where Purchase_invoiceNo = @PInvoice and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code; # if row_count() = 0 then # insert into itemCustom # set Purchase_invoiceNo = @PInvoice, Item_code = new.Item_Code, Warehouse_code = new.Warehouse_Code, # ItemCust_QtyIn = @sign * new.PurchaseDt_Quantity; # end if; # end if; # if ((@LastPurchaseDate <=new.Purchase_Date) or (@LastPurchaseDate is null)) AND (new.journal_type = 'PI') AND (@price>0) then # update items set Item_LastDatePurchase= cast(new.Purchase_Date as date), # item_lastcostprice=if(currency_code=new.currency_code,@price, # convertcurFunc(new.currency_code,currency_code,new.Purchase_Date,0,0,'','',@price)), # item_lastcostpriceBase1=@PriceBase1,item_lastcostpriceBase2=@PriceBase2 # where item_code=new.item_code; # end if; set @VarDate:=null; select ItemTrans_LastDatePurchase into @VarDate from ItemsTrans where item_code=new.Item_Code; if @VarDate is null then insert into ItemsTrans (Item_code,ItemTrans_LastDatePurchase, itemTrans_lastcostprice,itemTrans_lastcostpriceBase1,itemTrans_lastcostpricebase2,ItemTrans_LastPricePurchase) values (new.Item_Code,new.Purchase_date,@Price,@PriceBase1,@PriceBase2,new.PurchaseDt_Price); else if new.Purchase_date>=@VarDate then update ItemsTrans set ItemTrans_LastDatePurchase=new.Purchase_date, itemTrans_lastcostprice=@Price, itemTrans_lastcostpriceBase1=@PriceBase1, itemTrans_lastcostpriceBase2=@PriceBase2, ItemTrans_LastPricePurchase=new.PurchaseDt_Price where item_code=new.Item_Code; end if; end if; END $$ Delimiter; Drop Trigger if exists PurchaseDT_AU; Delimiter $$ CREATE Trigger PurchaseDT_AU After Update on PurchasesDT For Each Row Begin if new.Journal_Type = 'PI' then Set @Sign = 1; UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity where Purchases.Purchase_ID = old.Purchase_ID; UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity where Purchases.Purchase_ID = new.Purchase_ID; else if new.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; Set @ItemUnit = 0; Select Item_Code, Item_Unit,item_LastDatePurchase into @ItemCode, @ItemUnit ,@LastPurchaseDate From Items WHERE Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; #toni habshi 3.09.2008 #delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type = old.Journal_Type; #Ayman 03.12.2009 Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then Set @Price = new.PurchaseDt_Price + @Sign*new.PurchaseDT_Charges/new.PurchaseDt_Quantity - new.PurchaseDt_Price*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountAmount/new.PurchaseDt_Quantity; Set @PriceBase1 = new.PurchaseDt_PriceBase1 + @Sign*new.PurchaseDT_ChargesBase1/new.PurchaseDt_Quantity - new.PurchaseDt_PriceBase1*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase1/new.PurchaseDt_Quantity; Set @PriceBase2 = new.PurchaseDt_PriceBase2 + @Sign*new.PurchaseDT_ChargesBase2/new.PurchaseDt_Quantity - new.PurchaseDt_PriceBase2*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase2/new.PurchaseDt_Quantity; #Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,@Sign*new.PurchaseDt_Quantity,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty); update `StkTransaction` set Transaction_Date=new.Purchase_Date ,Item_Code=new.Item_Code , Warehouse_Code=new.Warehouse_Code , Project_Code=new.Project_Code ,Costcent_Code=new.Costcent_Code ,Currency_Code=new.Currency_Code ,QuantityEntered=@Sign*new.PurchaseDt_Quantity ,Price=@Price ,PriceBase1=@PriceBase1 ,PriceBase2=@PriceBase2 ,Synchronized=new.Synchronized ,CostBase1=@PriceBase1 ,CostBase2=@PriceBase2 ,UnitCoef=new.Item_UnitCoef ,Unit2Qty=new.Item_Unit2Qty ,Item_ExpireDate = new.PurchaseDt_ExpireDate ,Item_BatchNo = new.PurchaseDt_BatchNo where Transaction_ID=new.Purchasedt_id and Transaction_Type=new.Journal_Type; else Set @Price = ((new.PurchaseDt_Price/@ItemUnit) +(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_Charges/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_Price/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountAmount/(@ItemUnit*new.PurchaseDt_Quantity))); Set @PriceBase1 = ((new.PurchaseDt_PriceBase1/@ItemUnit) + (if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase1/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_PriceBase1/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase1/(@ItemUnit*new.PurchaseDt_Quantity))); Set @PriceBase2 = ((new.PurchaseDt_PriceBase2/@ItemUnit) +(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase2/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_PriceBase2/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase2/(@ItemUnit*new.PurchaseDt_Quantity))); if @Price is null then set @Price = 0;end if; if @PriceBase1 is null then set @PriceBase1 = 0;end if; if @PriceBase2 is null then set @PriceBase2 = 0;end if; #Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2) #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code)) #,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2); update `StkTransaction` set Transaction_Date=new.Purchase_Date ,Item_Code=new.Item_Code , Warehouse_Code=new.Warehouse_Code , Project_Code=new.Project_Code ,Costcent_Code=new.Costcent_Code ,Currency_Code=new.Currency_Code ,QuantityEntered=(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code)) ,Price=@Price ,PriceBase1=@PriceBase1 ,PriceBase2=@PriceBase2 ,Synchronized=new.Synchronized ,CostBase1=@PriceBase1 ,CostBase2=@PriceBase2 ,Item_ExpireDate = new.PurchaseDt_ExpireDate ,Item_BatchNo = new.PurchaseDt_BatchNo where Transaction_ID=new.Purchasedt_id and Transaction_Type=new.Journal_Type; end if; if new.RelatedDt_Id <> 0 and new.journal_type = 'PI' then Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO'; Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO'; end if; # select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = old.Purchase_id; # select module_freezone into @module from module limit 1; # if @module then # update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn - @sign * old.PurchaseDt_Quantity # where Purchase_invoiceNo = @PInvoice and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code; # if row_count() = 0 then # Insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = old.Item_Code, # Warehouse_code = old.Warehouse_Code, ItemCust_QtyIn = (-1)*@sign * old.PurchaseDt_Quantity; # end if; # end if; # select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = new.Purchase_id; # select module_freezone into @module from module limit 1; # if @module then # update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn + @sign * new.PurchaseDt_Quantity # where Purchase_invoiceNo = @PInvoice and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code; # if row_count() = 0 then # insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = new.Item_Code, # Warehouse_code = new.Warehouse_Code, ItemCust_QtyIn = @sign * new.PurchaseDt_Quantity; # end if; # end if; # if ((@LastPurchaseDate <=new.Purchase_Date) or (@LastPurchaseDate is null)) AND (new.journal_type = 'PI') AND (@price>0) then # update items set Item_LastDatePurchase= cast(new.Purchase_Date as date), # item_lastcostprice=if(currency_code=new.currency_code,@price, # convertcurFunc(new.currency_code,currency_code,new.Purchase_Date,0,0,'','',@price)), # item_lastcostpriceBase1=@PriceBase1,item_lastcostpriceBase2=@PriceBase2 # where item_code=new.item_code; # end if; set @VarDate:=null; delete from itemstrans where item_code=old.Item_Code and ItemTrans_LastDatePurchase=old.Purchase_date; select ItemTrans_LastDatePurchase into @VarDate from ItemsTrans where item_code=new.Item_Code; if @VarDate is null then insert into ItemsTrans (Item_code,ItemTrans_LastDatePurchase, itemTrans_lastcostprice,itemTrans_lastcostpriceBase1,itemTrans_lastcostpriceBase2,ItemTrans_LastPricePurchase) values (new.Item_Code,new.Purchase_date,@Price,@PriceBase1,@PriceBase2,new.PurchaseDt_Price); else if new.Purchase_date>=@VarDate then update ItemsTrans set ItemTrans_LastDatePurchase=new.Purchase_date, itemTrans_lastcostprice=@Price, itemTrans_lastcostpriceBase1=@PriceBase1, itemTrans_lastcostpriceBase2=@PriceBase2, ItemTrans_LastPricePurchase=new.PurchaseDt_Price where item_code=new.Item_Code; end if; end if; END $$ Delimiter; Drop Trigger if exists PurchaseDt_BD; Delimiter $$ CREATE Trigger PurchaseDt_BD Before Delete on Purchasesdt For Each Row Begin Declare exit handler for 1442 begin end; if old.Journal_Type = 'PI' then Set @Sign = 1; else if old.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; Set @ItemUnit = 0; Select Item_Code, Item_Unit into @ItemCode, @ItemUnit From Items WHERE Item_Code = old.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; Delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type = old.Journal_Type; if old.RelatedDt_Id <> 0 and old.journal_type = 'PI' then Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO'; end if; # select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = old.Purchase_id; # select module_freezone into @module from module limit 1; # if @module then # update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn - @sign * old.PurchaseDt_Quantity # where Purchase_invoiceNo = @PInvoice and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code; # if row_count() = 0 then # insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = old.Item_Code, # Warehouse_code = old.Warehouse_Code, ItemCust_QtyIn = (-1)*@sign * old.PurchaseDt_Quantity; # end if; # end if; if old.Journal_Type = 'PI' then UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity where Purchases.Purchase_ID = old.Purchase_ID; end if; UPDATE Purchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem - 1 where Purchases.Purchase_ID = old.Purchase_ID; delete from itemstrans where item_code=old.Item_Code and ItemTrans_LastDatePurchase=old.Purchase_date; END $$ Delimiter; ######################################################################################################################## # PorderDT Table Drop Trigger if exists PorderDt_AI; Delimiter $$ CREATE Trigger PorderDt_AI After Insert on PorderDt For Each Row Begin Update Porder set Porder_TotalQuantity = Porder_TotalQuantity + new.PorderDt_Quantity where Porder.Porder_ID = new.Porder_ID; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityPOrdered,Price,PriceBase1,PriceBase2,Synchronized,UnitCoef,Unit2Qty) Values (new.POrderdt_id,new.POrder_Date,'PO',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.POrderDt_Quantity,new.Porderdt_Price,new.Porderdt_PriceBase1,new.ProderDt_PriceBase2,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty); END $$ Delimiter; Drop Trigger if exists PorderDt_AU; Delimiter $$ CREATE Trigger PorderDt_AU After Update on PorderDt For Each Row Begin Update Porder set Porder_TotalQuantity = Porder_TotalQuantity - old.PorderDt_Quantity where Porder.Porder_ID = old.Porder_ID; Update Porder set Porder_TotalQuantity = Porder_TotalQuantity + new.PorderDt_Quantity where Porder.Porder_ID = new.Porder_ID; Delete from stkTransaction where Transaction_ID = old.Porderdt_Id and Transaction_Type = 'PO'; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityPOrdered,Price,PriceBase1,PriceBase2,Synchronized,UnitCoef,Unit2Qty) Values (new.POrderdt_id,new.POrder_Date,'PO',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.POrderDt_Quantity,new.Porderdt_Price,new.Porderdt_PriceBase1,new.ProderDt_PriceBase2,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty); END $$ Delimiter; Drop Trigger if exists PorderDt_BD; Delimiter $$ CREATE Trigger PorderDt_BD Before Delete on PorderDt For Each Row Begin Declare exit handler for 1442 begin end; Delete from stkTransaction where Transaction_ID = old.Porderdt_Id and Transaction_Type = 'PO'; Update Porder set Porder_TotalQuantity = Porder_TotalQuantity - old.PorderDt_Quantity where Porder.Porder_ID = old.Porder_ID; END $$ Delimiter; ######################################################################################################################## # Porder Table Drop Trigger if exists Porder_BD; Delimiter $$ CREATE Trigger Porder_BD Before Delete on Porder For Each Row Begin Delete From PorderDt WHERE PorderDt.Porder_ID = old.Porder_ID; END $$ Delimiter; ###################################################################################################### # AdjustmentDT Table Drop Trigger if exists AdjustmentDT_BI; Delimiter $$ CREATE Trigger AdjustmentDT_BI Before Insert on AdjustmentDt For Each Row Begin if new.AdjustmentDt_ID is not null then set new.Currency_Code = (Select Currency_Code from Adjustment where Adjustment_ID = new.Adjustment_ID); end if; END $$ Delimiter; Drop Trigger if exists AdjustmentDT_AI; Delimiter $$ CREATE Trigger AdjustmentDT_AI After Insert on adjustmentdt For Each Row Begin Select Item_LastDatePurchase into @LastPurchaseDate From Items WHERE Item_Code = new.Item_Code; if new.AdjustmentDt_Quantity > 0 then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityEntered,Currency_Code,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Adjustmentdt_id,new.Adjustment_Date,'AI',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.AdjustmentDt_Quantity,new.Currency_Code,new.AdjustmentDt_Price,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.AdjustmentDt_QtyUnit,ifnull(new.AdjustmentDt_PricePerUnit,0),new.Synchronized,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.AdjustmentDt_ExpireDate, new.AdjustmentDt_BatchNo); /* Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) Values (new.AdjustmentdT_ID,new.AdjustmentdT_ID,'AI',new.Size_Measure,new.Color_Code,new.AdjustmentDt_Quantity); */ else Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityDelivered,Currency_Code,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Adjustmentdt_id,new.Adjustment_Date,'AR',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,abs(new.AdjustmentDt_Quantity),new.Currency_Code,new.AdjustmentDt_Price,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.AdjustmentDt_QtyUnit,ifnull(new.AdjustmentDt_PricePerUnit,0),new.Synchronized,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.AdjustmentDt_ExpireDate, new.AdjustmentDt_BatchNo); /*Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) Values (new.AdjustmentdT_ID,new.AdjustmentdT_ID,'AR',new.Size_Measure,new.Color_Code,new.AdjustmentDt_Quantity); */ end if; #if ((@LastPurchaseDate <=new.Adjustment_Date) or (@LastPurchaseDate is null) ) AND (new.AdjustmentDt_Price>0) then # update items set Item_LastDatePurchase= cast(new.Adjustment_Date as date), # item_lastcostprice=if(currency_code=new.currency_code,new.AdjustmentDt_Price, # convertcurFunc(new.currency_code,currency_code,new.Adjustment_Date,0,0,'','',new.AdjustmentDt_Price)), # item_lastcostpriceBase1=new.AdjustmentDt_PriceBase1,item_lastcostpriceBase2=new.AdjustmentDt_PriceBase2 # where item_code=new.item_code; #end if; END $$ Delimiter; Drop Trigger if exists AdjustmentDT_BU; Delimiter $$ CREATE Trigger AdjustmentDT_BU Before Update on AdjustmentDt For Each Row Begin if new.AdjustmentDt_ID is not null then set new.Currency_Code = (Select Currency_Code from Adjustment where Adjustment_ID = new.Adjustment_ID); end if; end $$ Delimiter; Drop Trigger if exists AdjustmentDT_AU; Delimiter $$ CREATE Trigger AdjustmentDT_AU After Update on adjustmentdt For Each Row Begin Delete from stkTransaction where Transaction_ID = old.Adjustmentdt_Id and (Transaction_Type = 'AI' or Transaction_Type = 'AR'); #Delete from StkTransactionSizes where Transactiondt_id = old.AdjustmentDt_ID and (Transaction_Type = 'AI' or Transaction_Type = 'AR'); if new.AdjustmentDT_Quantity > 0 then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityEntered,Currency_Code,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Adjustmentdt_id,new.Adjustment_Date,'AI',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.AdjustmentDt_Quantity,new.Currency_Code,new.AdjustmentDt_Price,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.AdjustmentDt_QtyUnit,ifnull(new.AdjustmentDt_PricePerUnit,0),new.Synchronized,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.AdjustmentDt_ExpireDate, new.AdjustmentDt_BatchNo); #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.AdjustmentdT_ID,new.AdjustmentdT_ID,'AI',new.Size_Measure,new.Color_Code,new.AdjustmentDt_Quantity); else Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityDelivered,Currency_Code,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Adjustmentdt_id,new.Adjustment_Date,'AR',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,Abs(new.AdjustmentDt_Quantity),new.Currency_Code,new.AdjustmentDt_Price,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.AdjustmentDt_QtyUnit,ifnull(new.AdjustmentDt_PricePerUnit,0),new.Synchronized,new.AdjustmentDt_PriceBase1,new.AdjustmentDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.AdjustmentDt_ExpireDate, new.AdjustmentDt_BatchNo); #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.AdjustmentdT_ID,new.AdjustmentdT_ID,'AR',new.Size_Measure,new.Color_Code,new.AdjustmentDt_Quantity); end if; # if ((@LastPurchaseDate <=new.Adjustment_Date) or (@LastPurchaseDate is null) ) AND (new.AdjustmentDt_Price>0) then # update items set Item_LastDatePurchase= cast(new.Adjustment_Date as date), # item_lastcostprice=if(currency_code=new.currency_code,new.AdjustmentDt_Price, # convertcurFunc(new.currency_code,currency_code,new.Adjustment_Date,0,0,'','',new.AdjustmentDt_Price)), # item_lastcostpriceBase1=new.AdjustmentDt_PriceBase1,item_lastcostpriceBase2=new.AdjustmentDt_PriceBase2 # where item_code=new.item_code; #end if; END $$ Delimiter; Drop Trigger if exists AdjustmentDT_BD; Delimiter $$ CREATE Trigger AdjustmentDT_BD Before Delete on adjustmentdt For Each Row Begin Delete from stkTransaction where Transaction_ID = old.Adjustmentdt_Id and (Transaction_Type = 'AI' or Transaction_Type = 'AR'); #Delete from StkTransactionSizes where Transactiondt_id = old.AdjustmentDt_ID and (Transaction_Type = 'AI' or Transaction_Type = 'AR'); END $$ Delimiter; ######################################################################################################################## # Adjustment Table Drop Trigger if exists Adjustment_BD; Delimiter $$ CREATE Trigger Adjustment_BD Before Delete on Adjustment For Each Row Begin Delete From AdjustmentDt WHERE AdjustmentDt.Adjustment_ID = old.Adjustment_ID; END $$ Delimiter; ######################################################################################################################## # DeliveryDT Table Drop Trigger if Exists DeliveryDt_AI; DELIMITER $$ CREATE TRIGGER DeliveryDt_AI AFTER INSERT ON deliverydt FOR EACH ROW BEGIN Declare vCheck TINYINT(1); select ifnull(Module_ConfirmDR,0) into @module_confirmDR from module limit 1; set vCheck = 0; if @module_confirmDR = 1 and new.Journal_Type = 'DR' and new.confirmDR = 1 then set vCheck = 1; end if; if @module_confirmDR = 0 and new.Journal_Type = 'DR' then set vCheck = 1; end if; if new.Journal_Type = 'DI' then set @Sign = 1; set vCheck = 1; UPDATE Deliveries SET Delivery_TotalQuantity = Delivery_TotalQuantity + new.DeliveryDt_Quantity where Deliveries.Delivery_ID = new.Delivery_ID; else if new.Journal_Type = 'DR' then set @Sign = -1; end if; end if; if new.Journal_Type = 'DI' then if new.RelatedDt_Id <> 0 then Update `StkTransaction` Set QuantitySordered = QuantitySordered - (new.DeliveryDt_Quantity) where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'SO'; end if; end if; if vCheck = 1 then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityDelivered,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo ) Values (new.Deliverydt_id,new.Delivery_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.DeliveryDt_Quantity),new.DeliveryDt_Price,new.DeliveryDt_PriceBase1,new.DeliveryDt_PriceBase2,new.DeliveryDt_QtyUnit,ifnull(new.DeliveryDt_PricePerUnit,0),new.Synchronized,new.DeliveryDt_CostBase1,new.DeliveryDt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.deliverydt_priceTTC,new.deliverydt_priceTTCBase1,new.deliverydt_priceTTCBase2, new.DeliveryDt_ExpireDate, new.DeliveryDt_BatchNo); Select Module_Delivery into @Module From Module limit 1; if not @Module then Update `StkTransaction` set QuantitySold = @Sign*new.DeliveryDt_Quantity,Synchronized = new.Synchronized where `StkTransaction`.Transaction_ID = new.DeliveryDt_ID and Transaction_Type = new.Journal_Type; if row_Count() = 0 then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantitySold,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo) Values (new.Deliverydt_id,new.Delivery_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.DeliveryDt_Quantity),new.DeliveryDt_Price,new.DeliveryDt_PriceBase1,new.DeliveryDt_PriceBase2,new.DeliveryDt_QtyUnit,ifnull(new.DeliveryDt_PricePerUnit,0),new.Synchronized,new.DeliveryDt_CostBase1,new.DeliveryDt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.deliverydt_priceTTC,new.deliverydt_priceTTCBase1,new.deliverydt_priceTTCBase2, new.DeliveryDt_ExpireDate, new.DeliveryDt_BatchNo); end if; end if; select module_freezone into @module from module limit 1; if @module = 1 then update itemCustom set ItemCust_QtyOut = ItemCust_QtyOut + (@sign * new.Deliverydt_Quantity) where Purchase_invoiceNo = new.Purchase_invoiceNo and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code; if row_count() = 0 then insert into itemCustom set Purchase_invoiceNo = new.Purchase_invoiceNo, Item_code = new.Item_Code, Warehouse_code = new.Warehouse_Code, ItemCust_QtyOut = @sign * new.Deliverydt_Quantity; end if; end if; end if; END $$ DELIMITER; Drop Trigger if Exists DeliveryDt_AU; DELIMITER $$ CREATE TRIGGER DeliveryDt_AU AFTER UPDATE ON deliverydt FOR EACH ROW BEGIN Declare vCheck TINYINT(1); select ifnull(Module_ConfirmDR,0) into @module_confirmDR from module limit 1; set vCheck = 0; if @module_confirmDR = 1 and new.Journal_Type = 'DR' and new.confirmDR = 1 then set vCheck = 1; end if; if @module_confirmDR = 0 and new.Journal_Type = 'DR' then set vCheck = 1; end if; if new.Journal_Type = 'DI' then set @Sign = 1; set vCheck = 1; UPDATE Deliveries SET Delivery_TotalQuantity = Delivery_TotalQuantity - old.DeliveryDt_Quantity where Deliveries.Delivery_ID = old.Delivery_ID; UPDATE Deliveries SET Delivery_TotalQuantity = Delivery_TotalQuantity + new.DeliveryDt_Quantity where Deliveries.Delivery_ID = new.Delivery_ID; else if new.Journal_Type = 'DR' then set @Sign = -1; end if; end if; if old.Journal_Type = 'DI' then if old.RelatedDt_Id <> 0 then Update `StkTransaction` Set QuantitySordered = QuantitySordered + (old.DeliveryDt_Quantity) where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'SO'; Update `StkTransaction` Set QuantitySordered = QuantitySordered - (new.DeliveryDt_Quantity) where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'SO'; end if; end if; if vCheck = 1 then Delete from StkTransaction where Transaction_ID = old.DeliveryDt_ID and Transaction_Type = old.Journal_Type; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityDelivered,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo) Values (new.Deliverydt_id,new.Delivery_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.DeliveryDt_Quantity),new.DeliveryDt_Price,new.DeliveryDt_PriceBase1,new.DeliveryDt_PriceBase2,new.DeliveryDt_QtyUnit,ifnull(new.DeliveryDt_PricePerUnit,0),new.Synchronized,new.DeliveryDt_CostBase1,new.DeliveryDt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.deliverydt_priceTTC,new.deliverydt_priceTTCBase1,new.deliverydt_priceTTCBase2, new.DeliveryDt_ExpireDate, new.DeliveryDt_BatchNo); Select Module_Delivery into @Module From Module limit 1; if not @Module then Update `StkTransaction` set QuantitySold = @Sign*new.DeliveryDt_Quantity,Synchronized = new.Synchronized where `StkTransaction`.Transaction_ID = new.DeliveryDt_ID and Transaction_Type = new.Journal_Type; if row_Count() = 0 then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantitySold,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo) Values (new.Deliverydt_id,new.Delivery_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.DeliveryDt_Quantity),new.DeliveryDt_Price,new.DeliveryDt_PriceBase1,new.DeliveryDt_PriceBase2,new.DeliveryDt_QtyUnit,ifnull(new.DeliveryDt_PricePerUnit,0),new.Synchronized,new.DeliveryDt_CostBase1,new.DeliveryDt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.deliverydt_priceTTC,new.deliverydt_priceTTCBase1,new.deliverydt_priceTTCBase2, new.DeliveryDt_ExpireDate, new.DeliveryDt_BatchNo); end if; end if; select module_freezone into @module from module limit 1; if @module then update itemCustom set ItemCust_QtyOut = ItemCust_QtyOut + @sign * old.Deliverydt_Quantity where Purchase_invoiceNo = old.Purchase_invoiceNo and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code; if row_count() = 0 then insert into itemCustom set Purchase_invoiceNo = old.Purchase_invoiceNo, Item_code = old.Item_Code, Warehouse_code = old.Warehouse_Code, ItemCust_QtyOut = (-1)*@sign * old.Deliverydt_Quantity; end if; end if; end if; END $$ DELIMITER; Drop Trigger if Exists DeliveryDt_BD; DELIMITER $$ CREATE TRIGGER DeliveryDt_BD BEFORE DELETE ON deliverydt FOR EACH ROW BEGIN Declare exit handler for 1442 begin end; if old.Journal_Type = 'DI' then set @sign = 1; else if old.Journal_Type = 'DR' then set @sign = -1; end if; end if; if old.Journal_Type = 'DI' then if old.RelatedDt_Id <> 0 then Update `StkTransaction` Set QuantitySordered = QuantitySordered + (old.DeliveryDt_Quantity) where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'SO'; end if; end if; Delete from StkTransaction where Transaction_ID = old.DeliveryDt_ID and Transaction_Type = old.Journal_Type; select module_freezone into @module from module limit 1; if @module then update itemCustom set ItemCust_QtyOut = ItemCust_QtyOut - @sign * old.Deliverydt_Quantity where Purchase_invoiceNo = old.Purchase_invoiceNo and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code; if row_count() = 0 then insert into itemCustom set Purchase_invoiceNo = old.Purchase_invoiceNo, Item_code = old.Item_Code, Warehouse_code = old.Warehouse_Code, ItemCust_QtyOut = (-1)*@sign * old.Deliverydt_Quantity; end if; end if; if old.Journal_Type = 'DI' then UPDATE Deliveries SET Delivery_TotalQuantity = Delivery_TotalQuantity - old.DeliveryDt_Quantity where Deliveries.Delivery_ID = old.Delivery_ID; end if; END $$ Delimiter; ######################################################################################################################## # Deliveries Table Drop Trigger if exists Deliveries_BD; Delimiter $$ CREATE Trigger Deliveries_BD Before Delete on Deliveries For Each Row Begin Delete From DeliveryDt WHERE DeliveryDt.Delivery_ID = old.Delivery_ID; update purchases set purchase_delivered = false where purchase_id = old.purchase_id; END $$ Delimiter; ###################################################################################################### # SalesDt Table Drop Trigger if Exists SalesDt_AI; DELIMITER $$ CREATE TRIGGER SalesDt_AI AFTER INSERT ON SalesDt FOR EACH ROW BEGIN UPDATE Sales SET Sale_TotalNoOfItem = Sale_TotalNoOfItem + 1 where Sales.Sale_id = new.Sale_id; if new.Journal_Type = 'SI' then set @Sign = 1; UPDATE Sales SET Sale_TotalQuantity = Sale_TotalQuantity + new.SaleDt_Quantity where Sales.Sale_ID = new.Sale_ID; else if new.Journal_Type = 'SR' then set @Sign = -1; end if; end if; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code, QuantitySold,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo) Values (new.Saledt_id,new.Sale_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(@Sign*new.SaleDt_Quantity),new.SaleDt_Price,new.SaleDt_PriceBase1,new.SaleDt_PriceBase2,new.SaleDt_QtyUnit,ifnull(new.SaleDt_PricePerUnit,0),new.Synchronized,new.SaleDt_PriceBase1,new.SaleDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.saledt_priceTTC,new.saledt_priceTTCBase1,new.saledt_priceTTCBase2, new.SaleDt_ExpireDate, new.SaleDt_BatchNo); END $$ DELIMITER; Drop Trigger if Exists SalesDt_AU; DELIMITER $$ CREATE TRIGGER SalesDt_AU AFTER UPDATE ON SalesDt FOR EACH ROW BEGIN if new.Journal_Type = 'SI' then set @Sign = 1; UPDATE Sales SET Sale_TotalQuantity = Sale_TotalQuantity - old.SaleDt_Quantity where Sales.Sale_ID = old.Sale_ID; UPDATE Sales SET Sale_TotalQuantity = Sale_TotalQuantity + new.SaleDt_Quantity where Sales.Sale_ID = old.Sale_ID; else if new.Journal_Type = 'SR' then set @Sign = -1; end if; end if; Delete from stkTransaction where Transaction_ID = old.Saledt_ID and Transaction_Type = old.Journal_Type; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code, QuantitySold,Price,PriceBase1,PriceBase2,QtyUnit,PricePerUnit,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_CodePacked,PriceTTC,PriceTTCBase1,PriceTTCBase2, Item_ExpireDate, Item_BatchNo) Values (new.Saledt_id,new.Sale_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(@Sign*new.SaleDt_Quantity),new.SaleDt_Price,new.SaleDt_PriceBase1,new.SaleDt_PriceBase2,new.SaleDt_QtyUnit,ifnull(new.SaleDt_PricePerUnit,0),new.Synchronized,new.SaleDt_PriceBase1,new.SaleDt_PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked,new.saledt_priceTTC,new.saledt_priceTTCBase1,new.saledt_priceTTCBase2, new.SaleDt_ExpireDate, new.SaleDt_BatchNo); END $$ DELIMITER; Drop Trigger if Exists SalesDt_BD; DELIMITER $$ CREATE TRIGGER SalesDt_BD BEFORE DELETE ON SalesDt FOR EACH ROW BEGIN Declare exit handler for 1442 begin end; if old.Journal_Type = 'SI' then set @Sign = 1; else if old.Journal_Type = 'SR' then set @Sign = -1; end if; end if; Delete from stkTransaction where Transaction_ID = old.Saledt_ID and Transaction_Type = old.Journal_Type; if old.Journal_Type = 'SI' then UPDATE Sales SET Sale_TotalQuantity = Sale_TotalQuantity - old.SaleDt_Quantity where Sales.Sale_ID = old.Sale_ID; end if; UPDATE Sales SET Sale_TotalNoOfItem = Sale_TotalNoOfItem - 1 where Sales.Sale_id = old.Sale_id; END $$ Delimiter; ######################################################################################################################## # Sales Table Drop Trigger if exists Sales_BD; Delimiter $$ CREATE Trigger Sales_BD Before Delete on Sales For Each Row Begin Delete From SalesDt WHERE SalesDt.Sale_ID = old.Sale_ID; END $$ Delimiter; ######################################################################################################################## # ProductionDt Table MODIFIED BY FADY SEE BELOW #Drop Trigger if Exists ProductionDt_AI; #DELIMITER $$ #CREATE TRIGGER ProductionDt_AI AFTER INSERT ON ProductionDt #FOR EACH ROW BEGIN # Select Production_Reversed into @Reversed From Production where Production_ID = new.Production_ID; # if @Reversed = 1 then # set @Sign = -1; # Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) # Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.ProductionDt_Quantity),new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty); # else # set @Sign = -1; # Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) # Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.ProductionDt_Quantity),new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty); # end if; # # Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) # Values (new.ProductiondT_ID,new.ProductiondT_ID,'PDt',new.Size_Measure,new.Color_Code,@Sign * new.ProductionDt_Quantity); #END $$ #DELIMITER;# #Drop Trigger if Exists ProductionDt_BU; #Drop Trigger if Exists ProductionDt_AU; #DELIMITER $$ #CREATE TRIGGER ProductionDt_AU AFTER UPDATE ON ProductionDt #FOR EACH ROW BEGIN # Delete from StkTransaction where Transaction_ID = old.Productiondt_id and Transaction_Type = 'PDt'; # Select Production_Reversed into @Reversed From Production where Production_ID = new.Production_ID; # if @Reversed = 1 then # set @Sign = 1; # Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) # Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.ProductionDt_Quantity,new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty); # else # set @Sign = -1; # Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) # Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.ProductionDt_Quantity,new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty); # end if; # Delete from StkTransactionSizes where Transactiondt_id = old.ProductionDt_ID and Transaction_Type = 'PDt'; # Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) # Values (new.ProductiondT_ID,new.ProductiondT_ID,'PDt',new.Size_Measure,new.Color_Code,@Sign * new.ProductionDt_Quantity); #END $$ #DELIMITER; Drop Trigger if Exists ProductionDt_AI; DELIMITER $$ CREATE TRIGGER ProductionDt_AI AFTER INSERT ON ProductionDt FOR EACH ROW BEGIN Select Production_Reversed into @Reversed From Production where Production_ID = new.Production_ID; # if new.item_special=0 then if (ifnull(new.item_special,0)=0) then if @Reversed = 1 then set @Sign = -1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.ProductionDt_Quantity),new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.ProductionDt_ExpireDate, new.ProductionDt_BatchNo); else set @Sign = -1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.ProductionDt_Quantity),new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.ProductionDt_ExpireDate, new.ProductionDt_BatchNo); end if; end if; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.ProductiondT_ID,new.ProductiondT_ID,'PDt',new.Size_Measure,new.Color_Code,@Sign * new.ProductionDt_Quantity); END $$ DELIMITER; Drop Trigger if Exists ProductionDt_BU; Drop Trigger if Exists ProductionDt_AU; DELIMITER $$ CREATE TRIGGER ProductionDt_AU AFTER UPDATE ON ProductionDt FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.Productiondt_id and Transaction_Type = 'PDt'; Select Production_Reversed into @Reversed From Production where Production_ID = new.Production_ID; #if new.item_special=0 then if (ifnull(new.item_special,0)=0) then if @Reversed = 1 then set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.ProductionDt_Quantity,new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.ProductionDt_ExpireDate, new.ProductionDt_BatchNo); else set @Sign = -1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Productiondt_id,new.Production_Date,'PDt',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.ProductionDt_Quantity,new.ProductionDt_Cost,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Synchronized,new.Productiondt_CostBase1,new.Productiondt_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty, new.ProductionDt_ExpireDate, new.ProductionDt_BatchNo); end if; end if; #Delete from StkTransactionSizes where Transactiondt_id = old.ProductionDt_ID and Transaction_Type = 'PDt'; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.ProductiondT_ID,new.ProductiondT_ID,'PDt',new.Size_Measure,new.Color_Code,@Sign * new.ProductionDt_Quantity); END $$ DELIMITER; Drop Trigger if Exists ProductionDt_BD; DELIMITER $$ CREATE TRIGGER ProductionDt_BD BEFORE DELETE ON ProductionDt FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.Productiondt_id and Transaction_Type = 'PDt'; #Delete from StkTransactionSizes where Transactiondt_id = old.ProductionDt_ID and Transaction_Type = 'PDt'; END $$ Delimiter; ###################################################################################################### # Production Table Drop Trigger if Exists Production_AI; DELIMITER $$ CREATE TRIGGER Production_AI AFTER INSERT ON Production FOR EACH ROW BEGIN Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; #Ayman 05.12.2009 Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then if new.Production_Reversed = 1 then set @Sign =-1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Item_UnitCoef,new.Item_Unit2Qty, new.Production_ExpireDate, new.Production_BatchNo); else set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Item_UnitCoef,new.Item_Unit2Qty, new.Production_ExpireDate, new.Production_BatchNo); end if; else if new.Production_Reversed = 1 then set @Sign =-1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity), new.Production_ExpireDate, new.Production_BatchNo); else set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity), new.Production_ExpireDate, new.Production_BatchNo); end if; end if; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.Production_ID,new.Production_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.Production_Quantity); END $$ DELIMITER; Drop Trigger if Exists Production_BU; Drop Trigger if Exists Production_AU; DELIMITER $$ CREATE TRIGGER Production_AU AFTER UPDATE ON Production FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.Production_id and Transaction_Type = 'PD'; Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; #Ayman 05.12.2009 Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then if new.Production_Reversed = 1 then set @Sign =-1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Item_UnitCoef,new.Item_Unit2Qty, new.Production_ExpireDate, new.Production_BatchNo); else set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Item_UnitCoef,new.Item_Unit2Qty, new.Production_ExpireDate, new.Production_BatchNo); end if; else if new.Production_Reversed = 1 then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity), new.Production_ExpireDate, new.Production_BatchNo); else Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/new.Production_Quantity),(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity),new.Synchronized,(new.Production_TotalBase1/new.Production_Quantity),(new.Production_TotalBase2/new.Production_Quantity), new.Production_ExpireDate, new.Production_BatchNo); end if; end if; #Delete from StkTransactionSizes where Transactiondt_id = old.Production_ID and Transaction_Type = 'PD'; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.Production_ID,new.Production_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.Production_Quantity); END $$ DELIMITER; Drop Trigger if Exists Production_BD; DELIMITER $$ CREATE TRIGGER Production_BD BEFORE DELETE ON Production FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.Production_id and Transaction_Type = 'PD'; #Delete from StkTransactionSizes where Transactiondt_id = old.Production_ID and Transaction_Type = 'PD'; Delete From ProductionDt where ProductionDt.Production_ID = old.Production_ID; END $$ Delimiter; ########################################################################################################## Drop Trigger if exists TransferDT_AI; Delimiter $$ CREATE Trigger TransferDT_AI After Insert on transferdt For Each Row Begin Select Module_TransferConfirmation into @Module_TransferConfirmation From Module Limit 1; Select IfNull(Transfer_Confirmed,0) into @Transfer_Confirmed From Transfer Where Transfer_Id = new.Transfer_id Limit 1; if(@Transfer_Confirmed =1 and @Module_TransferConfirmation =1) then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,QuantityDelivered, QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo) Values (new.Transferdt_id,new.Transfer_Date,'TR',new.Item_Code,new.TransferDt_warehouseFrom,new.Project_Code1,new.Costcent_Code,new.TransferDt_Quantity, new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo); Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,QuantityEntered, QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo) Values (new.Transferdt_id,new.Transfer_Date,'TRT',new.Item_Code,new.TransferDt_warehouseTo,new.Project_Code2,new.Costcent_Code,new.TransferDt_Quantity, new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo); #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.TransferDt_ID,new.TransferDt_ID,'TR',new.Size_Measure,new.Color_Code,(-1) * new.TransferDt_Quantity), # (new.TransferDt_ID,new.TransferDt_ID,'TRT',new.Size_Measure,new.Color_Code,new.TransferDt_Quantity); #nadine #08-11-2016 ELSEIF (@Module_TransferConfirmation = 0) then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,QuantityDelivered, QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo) Values (new.Transferdt_id,new.Transfer_Date,'TR',new.Item_Code,new.TransferDt_warehouseFrom,new.Project_Code1,new.Costcent_Code,new.TransferDt_Quantity, new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo); Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,QuantityEntered, QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo) Values (new.Transferdt_id,new.Transfer_Date,'TRT',new.Item_Code,new.TransferDt_warehouseTo,new.Project_Code2,new.Costcent_Code,new.TransferDt_Quantity, new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo); #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.TransferDt_ID,new.TransferDt_ID,'TR',new.Size_Measure,new.Color_Code,(-1) * new.TransferDt_Quantity), # (new.TransferDt_ID,new.TransferDt_ID,'TRT',new.Size_Measure,new.Color_Code,new.TransferDt_Quantity); End If; END $$ Delimiter; Drop Trigger if exists TransferDT_BU; Drop Trigger if exists TransferDT_AU; Delimiter $$ CREATE Trigger TransferDT_AU After Update on transferdt For Each Row Begin Select Module_TransferConfirmation into @Module_TransferConfirmation From Module Limit 1; Select IfNull(Transfer_Confirmed,0) into @Transfer_Confirmed From Transfer Where Transfer_Id = new.Transfer_id Limit 1; if(@Transfer_Confirmed =1 and @Module_TransferConfirmation =1) then Delete From StkTransaction where Transaction_id = old.TransferDt_id and (Transaction_Type = 'TR' or Transaction_Type = 'TRT'); Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityDelivered, QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo) values(new.Transferdt_id,new.Transfer_Date,'TR',new.Item_Code,new.TransferDt_warehouseFrom,new.Project_Code1,new.Costcent_Code,new.TransferDt_Quantity, new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo); Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityEntered, QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo) Values (new.Transferdt_id,new.Transfer_Date,'TRT',new.Item_Code,new.TransferDt_warehouseTo,new.Project_Code2,new.Costcent_Code,new.TransferDt_Quantity, new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo); #Delete from StkTransactionSizes where Transactiondt_id = old.TransferDt_ID and Transaction_Type in('TR','TRT'); #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.TransferDt_ID,new.TransferDt_ID,'TR',new.Size_Measure,new.Color_Code,(-1) * new.TransferDt_Quantity), # (new.TransferDt_ID,new.TransferDt_ID,'TRT',new.Size_Measure,new.Color_Code,new.TransferDt_Quantity); #nadine #08-11-2016 ELSEIF (@Module_TransferConfirmation = 0) then Delete From StkTransaction where Transaction_id = old.TransferDt_id and (Transaction_Type = 'TR' or Transaction_Type = 'TRT'); Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityDelivered, QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo) values(new.Transferdt_id,new.Transfer_Date,'TR',new.Item_Code,new.TransferDt_warehouseFrom,new.Project_Code1,new.Costcent_Code,new.TransferDt_Quantity, new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo); Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,QuantityEntered, QtyUnit,PricePerUnit,Synchronized,UnitCoef,Unit2Qty,Item_BatchNo) Values (new.Transferdt_id,new.Transfer_Date,'TRT',new.Item_Code,new.TransferDt_warehouseTo,new.Project_Code2,new.Costcent_Code,new.TransferDt_Quantity, new.TransferDt_QtyUnit,ifnull(new.TransferDt_PricePerUnit,0),new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty, new.TransferDt_BatchNo); #Delete from StkTransactionSizes where Transactiondt_id = old.TransferDt_ID and Transaction_Type in('TR','TRT'); #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.TransferDt_ID,new.TransferDt_ID,'TR',new.Size_Measure,new.Color_Code,(-1) * new.TransferDt_Quantity), # (new.TransferDt_ID,new.TransferDt_ID,'TRT',new.Size_Measure,new.Color_Code,new.TransferDt_Quantity); End If; END $$ Delimiter; Drop Trigger if exists TransferDT_BD; Delimiter $$ CREATE Trigger TransferDT_BD Before Delete on transferdt For Each Row Begin Delete from StkTransaction where Transaction_id = old.TransferDt_id and (Transaction_Type = 'TR' or Transaction_Type = 'TRT'); #Delete from StkTransactionSizes where Transactiondt_id = old.TransferDt_ID and Transaction_Type in('TR','TRT'); END $$ Delimiter; ######################################################################################################################## # Transfer Table Drop Trigger if exists Transfer_BD; Delimiter $$ CREATE Trigger Transfer_BD Before Delete on Transfer For Each Row Begin Delete From TransferDt WHERE TransferDt.Transfer_ID = old.Transfer_ID; END $$ Delimiter ; ######################################################################################################################## # SorderDT Table Drop Trigger if exists SorderDt_AI; Delimiter $$ CREATE Trigger SorderDt_AI After Insert on SorderDt For Each Row Begin Declare vCompany VARCHAR(64); Declare vInvoiceModule,vInvoiceOnSorder, vSalesTTC TINYINT(1); Declare vForm_id VARCHAR(50); Declare vYear Integer; Declare vBase1, vBase2, vVatPercentage varchar(6); Declare vJournal_Type VARCHAR(2); Declare vSalesman_Code VARCHAR(10); Declare vSorder_reference, vTotal, vTotalB1, vTotalB2, vDecimalBase double; Update Sorder set Sorder_TotalQuantity = Sorder_TotalQuantity + new.SorderDt_Quantity where Sorder.Sorder_ID = new.Sorder_ID; Select Sorder_Type into @SorderType from Sorder Where Sorder.Sorder_id = new.Sorder_id; if @SorderType = 'SO' then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantitySOrdered,Price,PriceBase1,PriceBase2,Synchronized,UnitCoef,Unit2Qty,Item_CodePacked) Values (new.Sorderdt_id,new.Sorder_Date,@SorderType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.SorderDt_Quantity,new.SorderDt_Price,new.SorderDt_PriceBase1,new.SroderDt_PriceBase2,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked); end if; #headerlink detaillink select Module_InvoiceGrid into vInvoiceModule from module; select InvoiceOnSorder into vInvoiceOnSorder from transsetup limit 1; select base1, base2, ifnull(Setup_VatPercentage,0), decimalbase into vBase1, vBase2, vVatPercentage, vDecimalBase from setup; select SalesTTC into vSalesTTC From SalesSetup; select journal_type, Salesman_Code, Sorder_reference into vJournal_Type, vSalesman_Code, vSorder_reference from sorder where sorder_id = NEW.sorder_id; if vInvoiceModule = 2 and vInvoiceOnSorder = 1 then select SUBSTRING_INDEX(DATABASE(), '_', 1) into vCompany from setup; select uExtractNumberFromString(DATABASE()) into vYear; set vForm_id = concat(vJournal_Type,NEW.Sorderdt_id,vYear); if(vJournal_Type = 'SO') then #get Amounts select if(vSalesTTC = 1, Round(((SorderDt_Quantity*SorderDt_PriceTTC)-((SorderDt_Quantity*SorderDt_PriceTTC*ifNull(SorderDt_Discount,0))/100)), vDecimalBase), Round(((SorderDt_Quantity*SorderDt_Price)-((SorderDt_Quantity*SorderDt_Price*ifNull(SorderDt_Discount,0))/100)), vDecimalBase)) As Total into vTotal from sorderdt where sorderdt_id = NEW.sorderdt_id; if vTotal = '' or vTotal is null then set vTotal = 0; end if; set vTotalB1 = convertcurFunc(NEW.Currency_Code,vBase1, NEW.Sorder_Date,0, 0, '','', vTotal); set vTotalB2 = convertcurFunc(NEW.Currency_Code,vBase2, NEW.Sorder_Date,0, 0, '','', vTotal); insert into accesslabels.headerlink (headerlink_date,year,company,journal_type,form_id,ledger_number,project_code, costcent_code, saleman_number, description,value_date, reference,currency_code, manual_no, amount, amountbase1, amountbase2 ) values (NEW.Sorder_Date,vYear,vCompany,vJournal_Type,vForm_id,NEW.Client_Code,NEW.Project_code,NEW.Costcent_code, vSalesman_Code, NEW.item_code, NEW.Sorder_date, vSorder_reference, NEW.Currency_Code, NULL, vTotal, vTotalB1, vTotalB2); end if; #end if journal type END IF; #end first if END $$ Delimiter; Drop Trigger if exists SorderDt_AU; Delimiter $$ CREATE Trigger SorderDt_AU After Update on SorderDt For Each Row Begin Declare vCompany VARCHAR(64); Declare vHeaderlink_id, vdetaillink_id INTEGER; Declare vInvoiceModule,vInvoiceOnSorder, vSalesTTC TINYINT(1); Declare vCheckid TINYINT(1); Declare vForm_id VARCHAR(50); Declare detaillinkid double; Declare vYear Integer; Declare vBase1, vBase2, vVatPercentage varchar(6); Declare vJournal_Type VARCHAR(2); Declare vSalesman_Code VARCHAR(10); Declare vSorder_reference, vTotal, vTotalB1, vTotalB2, vDecimalBase double; Update Sorder set Sorder_TotalQuantity = Sorder_TotalQuantity - old.SorderDt_Quantity where Sorder.Sorder_ID = old.Sorder_ID; Update Sorder set Sorder_TotalQuantity = Sorder_TotalQuantity + new.SorderDt_Quantity where Sorder.Sorder_ID = new.Sorder_ID; Select Sorder_Type into @SorderType from Sorder Where Sorder.Sorder_id = old.Sorder_id; if @SorderType = 'SO' then Delete from StkTransaction where Transaction_Type = @SorderType and Transaction_ID = old.SorderDt_ID; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantitySOrdered,Price,PriceBase1,PriceBase2,Synchronized,UnitCoef,Unit2Qty,Item_CodePacked) Values (new.Sorderdt_id,new.Sorder_Date,@SorderType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.SorderDt_Quantity,new.SorderDt_Price,new.SorderDt_PriceBase1,new.SroderDt_PriceBase2,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty,new.Item_CodePacked); end if; #headerlink detaillink select Module_InvoiceGrid into vInvoiceModule from module; select uExtractNumberFromString(DATABASE()) into vYear; select InvoiceOnSorder into vInvoiceOnSorder from transsetup limit 1; select base1, base2, ifnull(Setup_VatPercentage,0), decimalbase into vBase1, vBase2, vVatPercentage, vDecimalBase from setup; select SalesTTC into vSalesTTC From SalesSetup; if vInvoiceModule = 2 and vInvoiceOnSorder = 1 then select SUBSTRING_INDEX(DATABASE(), '_', 1) into vCompany from setup; select journal_type, Salesman_Code, Sorder_reference into vJournal_Type, vSalesman_Code, vSorder_reference from sorder where sorder_id = NEW.sorder_id; set vForm_id = concat(vJournal_Type,NEW.Sorderdt_id,vYear); select headerlink_id into vHeaderlink_id from accesslabels.headerlink where form_id=vForm_id and journal_type=vJournal_Type and year=vYear and company=vCompany; select 1 into vCheckid from accesslabels.detaillink where form_id=vForm_id and related_journalType=vJournal_Type and year=vYear and company=vCompany; if(vCheckid='' or vCheckid is null) then set vCheckid:=0; end if; IF (vHeaderlink_id != '' or vHeaderlink_id is not null) and (vCheckid=0) then if(vJournal_Type = 'SO') then #get Amounts select if(vSalesTTC = 1, Round(((SorderDt_Quantity*SorderDt_PriceTTC)-((SorderDt_Quantity*SorderDt_PriceTTC*ifNull(SorderDt_Discount,0))/100)), vDecimalBase), Round(((SorderDt_Quantity*SorderDt_Price)-((SorderDt_Quantity*SorderDt_Price*ifNull(SorderDt_Discount,0))/100)), vDecimalBase)) As Total into vTotal from sorderdt where sorderdt_id = NEW.sorderdt_id; if vTotal = '' or vTotal is null then set vTotal = 0; end if; set vTotalB1 = convertcurFunc(NEW.Currency_Code,vBase1, NEW.Sorder_Date,0, 0, '','', vTotal); set vTotalB2 = convertcurFunc(NEW.Currency_Code,vBase2, NEW.Sorder_Date,0, 0, '','', vTotal); update accesslabels.headerlink set headerlink_date = NEW.Sorder_date, ledger_number = NEW.Client_code, project_code = NEW.Project_code, costcent_code = NEW.costcent_code, saleman_number = vSalesman_Code, amount = vTotal, amountbase1 = vTotalB1, amountbase2 = vTotalB2, currency_code = NEW.Currency_Code, reference = vSorder_reference, description = NEW.item_code, value_date = NEW.Sorder_date where headerlink_id=vHeaderlink_id; end if; #end if journal type end if; #end if headerlink END IF; #end first if END $$ Delimiter; Drop Trigger if exists SorderDt_BD; Delimiter $$ CREATE Trigger SorderDt_BD Before Delete on SorderDt For Each Row Begin Declare vCompany VARCHAR(64); Declare vHeaderlink_id INTEGER; Declare vInvoiceModule,vInvoiceOnSorder TINYINT(1); Declare vCheckid TINYINT(1); Declare vForm_id VARCHAR(50); Declare vYear Integer; Declare detaillinkid DOUBLE; Declare vJournal_Type VARCHAR(2); DECLARE EXIT HANDLER FOR 1442 BEGIN END; Select Sorder_Type into @SorderType from Sorder Where Sorder.Sorder_id = old.Sorder_id; if @SorderType = 'SO' then Delete from StkTransaction where Transaction_Type = @SorderType and Transaction_ID = old.SorderDt_ID; end if; Update Sorder set Sorder_TotalQuantity = Sorder_TotalQuantity - old.SorderDt_Quantity where Sorder.Sorder_ID = old.Sorder_ID; #headerlink detaillink select Module_InvoiceGrid into vInvoiceModule from module; select uExtractNumberFromString(DATABASE()) into vYear; select InvoiceOnSorder into vInvoiceOnSorder from transsetup limit 1; if vInvoiceModule = 2 and vInvoiceOnSorder = 1 then select SUBSTRING_INDEX(DATABASE(), '_', 1) into vCompany from setup; select journal_type into vJournal_Type from sorder where sorder_id = OLD.sorder_id; set vForm_id = concat(vJournal_Type,OLD.Sorderdt_id,vYear); select headerlink_id into vHeaderlink_id from accesslabels.headerlink where form_id=vForm_id and journal_type=vJournal_Type and year=vYear and company=vCompany; select 1 into vCheckid from accesslabels.detaillink where form_id=vForm_id and related_journalType=vJournal_Type and year=vYear and company=vCompany; if(vCheckid='' or vCheckid is null) then set vCheckid:=0; end if; IF (vHeaderlink_id != '' or vHeaderlink_id is not null) and (vCheckid=0) then DELETE FROM accesslabels.headerlink where headerlink_id=vHeaderlink_id; END IF; #end if headerlink END IF; #end first if END $$ Delimiter; ######################################################################################################################## # Sorder Table Drop Trigger if exists Sorder_BD; Delimiter $$ CREATE Trigger Sorder_BD Before Delete on Sorder For Each Row Begin Delete From SorderDt WHERE SorderDt.Sorder_ID = old.Sorder_ID; END $$ Delimiter; #################################################################################################################### #RMAdt Table DROP TRIGGER IF EXISTS RMADt_AI; Delimiter $$ CREATE TRIGGER RMADt_AI AFTER INSERT ON RMADt FOR EACH ROW BEGIN IF NEW.RMA_Type='MI' then UPDATE RMA SET RMA_TotalQuantity = RMA_TotalQuantity + NEW.RMADt_Quantity WHERE RMA.RMA_ID = NEW.RMA_ID; ELSE IF NEW.RMA_Type='MR' then SELECT RMA_ID INTO @RID FROM RMADt WHERE RMADt_ID = NEW.MI_ID; UPDATE RMA SET RMA_TotalQuantityUsed = RMA_TotalQuantityUsed + NEW.RMADt_Quantity WHERE RMA.RMA_ID = @RID; END IF; END IF; END $$ Delimiter; DROP TRIGGER IF EXISTS RMADt_AU; Delimiter $$ CREATE TRIGGER RMADt_AU AFTER UPDATE ON RMADt FOR EACH ROW BEGIN IF OLD.RMA_Type='MI' then UPDATE RMA SET RMA_TotalQuantity = RMA_TotalQuantity - OLD.RMADt_Quantity WHERE RMA.RMA_ID = OLD.RMA_ID; ELSE IF OLD.RMA_Type='MR' then Select RMA_id into @RID from RMADt where RMADt_ID = old.MI_ID; UPDATE RMA SET RMA_TotalQuantityUsed = RMA_TotalQuantityUsed - OLD.RMADt_Quantity WHERE RMA.RMA_ID = @RID; END IF; END IF; IF NEW.RMA_Type='MI' then UPDATE RMA SET RMA_TotalQuantity = RMA_TotalQuantity + NEW.RMADt_Quantity WHERE RMA.RMA_ID = NEW.RMA_ID; ELSE IF NEW.RMA_Type='MR' then Select RMA_ID into @RID from RMADt where RMADt_ID = new.MI_ID; UPDATE RMA SET RMA_TotalQuantityUsed = RMA_TotalQuantityUsed + NEW.RMADt_Quantity WHERE RMA.RMA_ID = @RID; END IF; END IF; END $$ Delimiter; DROP TRIGGER IF EXISTS RMADt_BD; Delimiter $$ CREATE TRIGGER RMADt_BD BEFORE DELETE ON RMADt FOR EACH ROW BEGIN DECLARE EXIT HANDLER FOR 1442 BEGIN END; IF OLD.RMA_Type='MI' then UPDATE RMA SET RMA_TotalQuantity = RMA_TotalQuantity - OLD.RMADt_Quantity WHERE RMA.RMA_ID = OLD.RMA_ID; ELSE IF OLD.RMA_Type='MR' then Select RMA_id into @RID from RMADt where RMADt_ID = old.MI_ID; UPDATE RMA SET RMA_TotalQuantityUsed = RMA_TotalQuantityUsed - OLD.RMADt_Quantity WHERE RMA.RMA_ID = @RID; END IF; END IF; END $$ Delimiter; #################################################################################################################### #RMA Table DROP TRIGGER IF EXISTS RMA_BU; DROP TRIGGER IF EXISTS RMA_BD; Delimiter $$ CREATE TRIGGER RMA_BD BEFORE DELETE ON RMA FOR EACH ROW BEGIN DELETE FROM RMADt where RMA_ID = old.RMA_ID; END $$ Delimiter; ########################################################################################################################## #DeliveriedQty Delivery DROP TRIGGER IF EXISTS DeliveriedQty_AI; Delimiter $$ CREATE TRIGGER DeliveriedQty_AI After Insert on DeliveriedQty For EACH Row Begin Update Deliverydt set DeliveryDt_DeliveriedQty = DeliveryDt_DeliveriedQty + new.DeliveriedQty_Qty where DeliveryDt_id = new.DeliveryDt_id; END $$ Delimiter; DROP TRIGGER IF EXISTS DeliveriedQty_BU; DROP TRIGGER IF EXISTS DeliveriedQty_AU; Delimiter $$ CREATE TRIGGER DeliveriedQty_AU After UPDATE on DeliveriedQty For EACH Row Begin Update Deliverydt set DeliveryDt_DeliveriedQty = DeliveryDt_DeliveriedQty - old.DeliveriedQty_Qty where DeliveryDt_id = old.DeliveryDt_id; Update Deliverydt set DeliveryDt_DeliveriedQty = DeliveryDt_DeliveriedQty + new.DeliveriedQty_Qty where DeliveryDt_id = new.DeliveryDt_id; END $$ Delimiter; DROP TRIGGER IF EXISTS DeliveriedQty_BD; Delimiter $$ CREATE TRIGGER DeliveriedQty_BD Before Delete on DeliveriedQty For EACH Row Begin Update Deliverydt set DeliveryDt_DeliveriedQty = DeliveryDt_DeliveriedQty - old.DeliveriedQty_Qty where DeliveryDt_id = old.DeliveryDt_id; END $$ Delimiter; #################################################################################################################### #DeliveriedQtyH Table DROP TRIGGER IF EXISTS DeliveriedQtyH_BD; Delimiter $$ CREATE TRIGGER DeliveriedQtyH_BD Before Delete on DeliveriedQtyH For EACH Row Begin Delete from DeliveriedQty where DeliveriedQtyH_id = old.DeliveriedQtyH_id; END $$ Delimiter; ############################################# #Items Table Drop trigger if exists Items_BI; Delimiter $$ create trigger Items_BI before Insert on Items For each row begin #if new.Item_Unit < 1 then if new.Item_Unit < 0 then set new.Item_Unit = 1; end if; END $$ Delimiter; Drop trigger if exists Items_BU; DELIMITER $$ create trigger Items_BU before update on Items For each row begin #if new.Item_Unit < 1 then if new.Item_Unit < 0 then set new.Item_Unit = 1; end if; END $$ Delimiter; ################################################### # PrequestDT Table Drop Trigger if exists PrequestDt_AI; Delimiter $$ CREATE Trigger PrequestDt_AI After Insert on PrequestDt For Each Row Begin Update Prequest set Prqst_TotalQuantity = Prqst_TotalQuantity + new.PrqstDt_Quantity where Prequest.Prqst_ID = new.Prqst_ID; /* Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,Quantity_PQR,Synchronized,UnitCoef,Unit2Qty) Values (new.Prqstdt_id,new.Prqst_Date,'PQ',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.PrqstDt_Quantity,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty); */ END $$ Delimiter; Drop Trigger if exists PrequestDt_AU; Delimiter $$ CREATE Trigger PrequestDt_AU After Update on PrequestDt For Each Row Begin Update Prequest set Prqst_TotalQuantity = Prqst_TotalQuantity - old.PrqstDt_Quantity where Prequest.Prqst_ID = old.Prqst_ID; Update Prequest set Prqst_TotalQuantity = Prqst_TotalQuantity + new.PrqstDt_Quantity where Prequest.Prqst_ID = new.Prqst_ID; /* Delete from stkTransaction where Transaction_ID = old.Prqstdt_Id and Transaction_Type = 'PQ'; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,Quantity_PQR,Synchronized,UnitCoef,Unit2Qty) Values (new.Prqstdt_id,new.Prqst_Date,'PQ',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.PrqstDt_Quantity,new.Synchronized,new.Item_UnitCoef,new.Item_Unit2Qty); */ END $$ Delimiter; Drop Trigger if exists PrequestDt_BD; Delimiter $$ CREATE Trigger PrequestDt_BD Before Delete on PrequestDt For Each Row Begin Declare exit handler for 1442 begin end; Delete from stkTransaction where Transaction_ID = old.Prqstdt_Id and Transaction_Type = 'PQ'; Update Prequest set Prqst_TotalQuantity = Prqst_TotalQuantity - old.PrqstDt_Quantity where Prequest.Prqst_ID = old.Prqst_ID; END $$ Delimiter; ######################################################################################################################## # Prequest Table Drop Trigger if exists Prequest_BD; Delimiter $$ CREATE Trigger Prequest_BD Before Delete on Prequest For Each Row Begin Delete From PrequestDt WHERE PrequestDt.Prqst_ID = old.Prqst_ID; END $$ Delimiter; #################################################################################################################### #STKEVAL trigger Drop Trigger if exists StkEvalDt_AI; Delimiter $$ CREATE Trigger StkEvalDt_AI After Insert on StkEvalDt For Each Row Begin select base1, base2 into @base1,@base2 from setup; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Project_Code,Costcent_Code,Currency_Code,Price,PriceBase1,PriceBase2) Values (new.StkEvaldt_id,new.StkEval_Date,'SE',new.Item_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.StkEvaldt_Value, ConvertCurFunc(new.currency_code,@base1, new.STkEval_date, 0, 0, '', '', new.STkEvaldt_value), ConvertCurFunc(new.currency_code,@base2, new.STkEval_date, 0, 0, '', '', new.STkEvaldt_value)); END $$ Delimiter; Drop Trigger if exists StkEvalDt_AU; Delimiter $$ CREATE Trigger StkEvalDt_AU After Update on StkEvalDt For Each Row Begin select base1, base2 into @base1,@base2 from setup; Delete from stkTransaction where Transaction_ID = old.StkEvaldt_Id and Transaction_Type = 'SE'; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code,Project_Code,Costcent_Code,Currency_Code,Price,PriceBase1,PriceBase2) Values (new.StkEvaldt_id,new.StkEval_Date,'SE',new.Item_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.StkEvaldt_Value, ConvertCurFunc(new.currency_code,@base1, new.STkEval_date, 0, 0, '', '', new.STkEvaldt_value), ConvertCurFunc(new.currency_code,@base2, new.STkEval_date, 0, 0, '', '', new.STkEvaldt_value)); END $$ Delimiter; Drop Trigger if exists StkEvalDt_BD; Delimiter $$ CREATE Trigger StkEvalDt_BD Before Delete on StkEvalDt For Each Row Begin Delete from stkTransaction where Transaction_ID = old.StkEvaldt_Id and Transaction_Type = 'SE'; END $$ Delimiter; #################################################################################################################### # STkEval table Drop Trigger if exists StkEval_BD; Delimiter $$ CREATE Trigger StkEval_BD Before Delete on StkEval For Each Row Begin Delete From StkEvalDt WHERE StkEvalDt.StkEval_ID = old.StkEval_ID; END $$ Delimiter; #################################################################################################################### #PurchaseDTSizes Table Drop Trigger if exists PurchasesDTSizes_AI; Delimiter $$ CREATE Trigger PurchasesDTSizes_AI After Insert on PurchasesDTSizes For Each Row Begin if new.Journal_Type = 'PI' then Set @Sign = 1; else if new.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; # Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) # Values (new.PurchasesDtSizes_Incremen,new.Purchasedt_id,new.Journal_Type,new.Size_Measure,new.Color_Code,@Sign * new.PurchasesDtSizes_Quantity); END $$ Delimiter; Drop Trigger if exists PurchasesDTSizes_AU; Delimiter $$ CREATE Trigger PurchasesDTSizes_AU After Update on PurchasesDTSizes For Each Row Begin if new.Journal_Type = 'PI' then Set @Sign = 1; else if new.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; #Delete from StkTransactionSizes where Transactiondt_id = old.PurchasesDtSizes_Incremen and Transaction_Type = old.Journal_Type; # Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) # Values (new.PurchasesDtSizes_Incremen,new.Purchasedt_id,new.Journal_Type,new.Size_Measure,new.Color_Code,@Sign * new.PurchasesDtSizes_Quantity); END $$ Delimiter; Drop Trigger if exists PurchasesDTSizes_BD; Delimiter $$ CREATE Trigger PurchasesDTSizes_BD Before Delete on PurchasesdtSizes For Each Row Begin Delete from StkTransactionSizes where Transactiondt_id = old.PurchasesDtSizes_Incremen and Transaction_Type = old.Journal_Type; END $$ Delimiter; ######################################################################################################################## #DeliveryDTSizes Table Drop Trigger if Exists DeliveryDtSizes_AI; DELIMITER $$ CREATE TRIGGER DeliveryDtSizes_AI AFTER INSERT ON deliverydtSizes FOR EACH ROW BEGIN if new.Journal_Type = 'DI' then set @Sign = -1; else if new.Journal_Type = 'DR' then set @Sign = 1; end if; end if; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.DeliveryDtSizes_Increment,new.DeliveryDt_ID,new.Journal_Type,new.Size_Measure,new.Color_Code,@Sign * new.DeliveryDtSizes_Quantity); END $$ DELIMITER; Drop Trigger if Exists DeliveryDtSizes_AU; DELIMITER $$ CREATE TRIGGER DeliveryDtSizes_AU AFTER UPDATE ON deliverydtSizes FOR EACH ROW BEGIN if new.Journal_Type = 'DI' then set @Sign = -1; else if new.Journal_Type = 'DR' then set @Sign = 1; end if; end if; #Delete from StkTransactionSizes where Transactiondt_id = old.DeliveryDtSizes_Increment and Transaction_Type = old.Journal_Type; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.deliveryDtSizes_Increment,new.Deliverydt_id,new.Journal_Type,new.Size_Measure,new.Color_Code,@Sign * new.DeliveryDtSizes_Quantity); END $$ DELIMITER; Drop Trigger if Exists DeliveryDtSizes_BD; DELIMITER $$ CREATE TRIGGER DeliveryDtSizes_BD BEFORE DELETE ON deliverydtSizes FOR EACH ROW BEGIN Delete from StkTransactionSizes where Transactiondt_id = old.DeliveryDtSizes_Increment and Transaction_Type = old.Journal_Type; END $$ Delimiter; ######################################################################################################################## # Journal Table /*Drop Trigger if Exists Journal_BI; DELIMITER $$ CREATE TRIGGER Journal_BI Before INSERT ON Journal FOR EACH ROW BEGIN insert into temptablefolio (journaldate,folio,event,ttime) values (new.journal_id,new.Journal_Folio,'BI',now()); Set @Value =(new.Journal_Remark<>''); Set New.Journal_Folio = Concat(LPad(Month(new.Journal_Date),2,'0'),LPad(NextFolio(Month(new.Journal_Date),new.Journal_ID),4,'0')); insert into temptablefolio (journaldate,folio,event,ttime) values (new.Journal_id,new.Journal_Folio,'ABI',now()); END $$ DELIMITER ; Drop Trigger if Exists Journal_BU; DELIMITER $$ CREATE TRIGGER Journal_BU BEFORE UPDATE ON Journal FOR EACH ROW BEGIN insert into temptablefolio (journaldate,folio,event,ttime) values (new.Journal_id,new.Journal_Folio,'BU',now()); if (Month(new.Journal_Date) <> Month(old.Journal_Date)) then Set New.Journal_Folio = Concat(LPad(Month(new.Journal_Date),2,'0'),LPad(NextFolio(Month(new.Journal_Date),new.Journal_ID),4,'0')); end if; insert into temptablefolio (journaldate,folio,event,ttime) values (new.Journal_id,new.Journal_Folio,'ABU',now()); END $$*/ ######################################################################################################################## #maroun 2010-07-12 ----------------------------------------------start-------------------------------------------------------------- ###################################################################################################### # Production Master Table Drop Trigger if Exists ProductionMaster_AI; DELIMITER $$ CREATE TRIGGER ProductionMaster_AI AFTER INSERT ON ProductionMaster FOR EACH ROW BEGIN Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) Values (new.ProductionMaster_id,new.ProductionMaster_date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.ProductionMaster_Quantity,(new.ProductionMaster_Amount),(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Synchronized,(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Item_UnitCoef,new.Item_Unit2Qty); else set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2) Values (new.ProductionMaster_id,new.ProductionMaster_date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(new.ProductionMaster_Quantity*@ItemUnit),(new.ProductionMaster_Amount),(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Synchronized,(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2)); end if; END $$ DELIMITER; Drop Trigger if Exists ProductionMaster_BU; Drop Trigger if Exists ProductionMaster_AU; DELIMITER $$ CREATE TRIGGER ProductionMaster_AU AFTER UPDATE ON ProductionMaster FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.ProductionMaster_id and Transaction_Type = 'PD'; Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) Values (new.ProductionMaster_id,new.ProductionMaster_date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,new.ProductionMaster_Quantity,(new.ProductionMaster_Amount),(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Synchronized,(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Item_UnitCoef,new.Item_Unit2Qty); else Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2) Values (new.ProductionMaster_id,new.ProductionMaster_date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(new.ProductionMaster_Quantity*@ItemUnit),(new.ProductionMaster_Amount),(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2),new.Synchronized,(new.ProductionMaster_Amount1),(new.ProductionMaster_Amount2)); end if; END $$ DELIMITER; Drop Trigger if Exists ProductionMaster_BD; DELIMITER $$ CREATE TRIGGER ProductionMaster_BD BEFORE DELETE ON ProductionMaster FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.ProductionMaster_id and Transaction_Type = 'PD'; END $$ Delimiter; ########################################################################################################## ###################################################################################################### # Production Table Drop Trigger if Exists Production_AI; DELIMITER $$ CREATE TRIGGER Production_AI AFTER INSERT ON Production FOR EACH ROW BEGIN Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; #maroun 08.07.2010 Select module_productionmm into @moduleproductionmm From Module Limit 1; if not @moduleproductionmm then #Ayman 05.12.2009 Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then if new.Production_Reversed = 1 then set @Sign =-1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Item_UnitCoef,new.Item_Unit2Qty); else set @Sign = 1; if not @moduleproductionmm then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Item_UnitCoef,new.Item_Unit2Qty); end if; end if; else if new.Production_Reversed = 1 then set @Sign =-1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1))); else set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1))); end if; end if; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.Production_ID,new.Production_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.Production_Quantity); end if; END $$ DELIMITER; Drop Trigger if Exists Production_BU; Drop Trigger if Exists Production_AU; DELIMITER $$ CREATE TRIGGER Production_AU AFTER UPDATE ON Production FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.Production_id and Transaction_Type = 'PD'; Select Item_Unit Into @ItemUnit From Items Where Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; #maroun 08.07.2010 Select module_productionmm into @moduleproductionmm From Module Limit 1; if not @moduleproductionmm then #Ayman 05.12.2009 Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then if new.Production_Reversed = 1 then set @Sign =-1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Item_UnitCoef,new.Item_Unit2Qty); else set @Sign = 1; if not @moduleproductionmm then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,new.Production_Quantity,(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Item_UnitCoef,new.Item_Unit2Qty); end if; end if; else if new.Production_Reversed = 1 then Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1))); else Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code,Project_Code,Costcent_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2) Values (new.Production_id,new.Production_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,new.Currency_Code,(new.Production_Quantity*@ItemUnit),(new.Production_Total/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1)),new.Synchronized,(new.Production_TotalBase1/IFNULL(new.Production_Quantity,1)),(new.Production_TotalBase2/IFNULL(new.Production_Quantity,1))); end if; end if; #Delete from StkTransactionSizes where Transactiondt_id = old.Production_ID and Transaction_Type = 'PD'; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.Production_ID,new.Production_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.Production_Quantity); end if; END $$ DELIMITER; Drop Trigger if Exists Production_BD; DELIMITER $$ CREATE TRIGGER Production_BD BEFORE DELETE ON Production FOR EACH ROW BEGIN Select module_productionmm into @moduleproductionmm From Module Limit 1; if not @moduleproductionmm then Delete from StkTransaction where Transaction_ID = old.Production_id and Transaction_Type = 'PD'; #Delete from StkTransactionSizes where Transactiondt_id = old.Production_ID and Transaction_Type = 'PD'; end if; Delete From ProductionDt where ProductionDt.Production_ID = old.Production_ID; Delete From ProductionMaster where ProductionMaster.Production_ID = old.Production_ID; END $$ Delimiter; ########################################################################################################## #maroun 2010-07-12 ----------------------------------------------End-------------------------------------------------------------- #nabil 2010-08-23 Drop Trigger if Exists BudgetInvAmendment_AI; DELIMITER $$ CREATE TRIGGER BudgetInvAmendment_AI After Insert on BudgetInvAmendment For Each Row Begin update `budgetinvdt` set BUDGETINVDT_DEACTIVATE=1, BUDGETINVDT_DEACTIVATEDATE=new.BudgetInvAmendment_activatedate, BUDGETINVAMENDMENT_ID=0 where ITEM_CODE=new.item_code and BUDGETINVDT_DEACTIVATE=0; Insert into `budgetinvdt` (BUDGETINV_ID,ITEM_CODE,BUDGETINVDT_QUANTITY,BUDGETINVDT_AMOUNT,BUDGETINVDT_AMOUNTBASE1, BUDGETINVDT_AMOUNTBASE2,BUDGETINVDT_REMARK,JOURNAL_TYPE,BUDGETINVDT_DEACTIVATE, BUDGETINVDT_DEACTIVATEDATE,BUDGETINVDT_SYSDATE,BUDGETINVDT_ACTIVATEDATE,BUDGETINVSTATUS_ID, BUDGETINVAMENDMENT_ID) Values (new.BUDGETINV_ID,new.item_code,new.BudgetInvAmendment_quantity,new.BudgetInvAmendment_amount, new.BudgetInvAmendment_amountBase1,new.BudgetInvAmendment_amountBase2, new.BudgetInvAmendment_remark,new.JOURNAL_TYPE,(SELECT BUDGETINVSTATUS_DEACTIVATE FROM BUDGETINVSTATUS WHERE BUDGETINVSTATUS_ID=new.BudgetInvStatus_id), new.budgetinvamendment_deactivatedate,NOW(),new.BudgetInvAmendment_activatedate,new.BudgetInvStatus_id, new.BUDGETINVAMENDMENT_ID); END $$ Delimiter; Drop Trigger if Exists BudgetInvAmendment_AU; DELIMITER $$ CREATE TRIGGER BudgetInvAmendment_AU After UPDATE on BudgetInvAmendment For Each Row Begin UPDATE `budgetinvdt` SET ITEM_CODE=new.item_code, BUDGETINVDT_QUANTITY=new.BudgetInvAmendment_quantity, BUDGETINVDT_AMOUNT=new.BudgetInvAmendment_amount, BUDGETINVDT_AMOUNTBASE1=new.BudgetInvAmendment_amountBase1, BUDGETINVDT_AMOUNTBASE2=new.BudgetInvAmendment_amountBase2, BUDGETINVDT_REMARK=new.BudgetInvAmendment_remark, JOURNAL_TYPE=new.JOURNAL_TYPE, BUDGETINVDT_DEACTIVATE=(SELECT BUDGETINVSTATUS_DEACTIVATE FROM BUDGETINVSTATUS WHERE BUDGETINVSTATUS_ID=new.BudgetInvStatus_id), BUDGETINVDT_DEACTIVATEDATE=new.budgetinvamendment_deactivatedate, BUDGETINVDT_SYSDATE=NOW(), BUDGETINVDT_ACTIVATEDATE=new.BudgetInvAmendment_activatedate, BUDGETINVSTATUS_ID=new.BudgetInvStatus_id WHERE BUDGETINVAMENDMENT_ID=new.BUDGETINVAMENDMENT_ID; END $$ Delimiter; Drop Trigger if Exists BudgetInvAmendment_BD; DELIMITER $$ CREATE TRIGGER BudgetInvAmendment_BD Before DELETE on BudgetInvAmendment For Each Row Begin delete from `budgetinvdt` WHERE BUDGETINVAMENDMENT_ID=OLD.BUDGETINVAMENDMENT_ID; END $$ Delimiter; ##nabil 2011-02-18 #Drop Trigger if Exists Chkjour_AI; #DELIMITER $$ #CREATE TRIGGER Chkjour_AI After Insert on Chkjour #For Each Row Begin # if new.chk_type='C' then # update checks set journaldt_id2=new.journaldt_id # where ledger_number=new.ledger_number # and chk_number=new.chk_number; # else # insert into checks (chk_number,ledger_number,chk_bank,ledger_activity, # currency_code,Chk_ValueDate, # Chk_Amount,Chk_Amount1,Chk_Amount2,journaldt_id1) # values # (new.chk_number,new.ledger_number,new.chk_bank, # (select ledger_activity from ledger where ledger_number=new.ledger_number), # new.currency_code,new.chk_valuedate, # new.Chk_Amount,new.Chk_Amount1,new.Chk_Amount2,new.journaldt_id); # end if; #END $$ #Delimiter; #Drop Trigger if Exists Chkjour_BD; #DELIMITER $$ #CREATE TRIGGER Chkjour_BD Before DELETE on Chkjour #For Each Row Begin # if old.chk_type='C' then # update checks set journaldt_id2=0 # where ledger_number=old.ledger_number # and chk_number=old.chk_number; # else # delete from checks # WHERE chk_number=old.chk_number # and ledger_number=old.ledger_number # and journaldt_id1=old.journaldt_id # and (journaldt_id2=0 or journaldt_id2 is null); # end if; #END $$ #Delimiter; #Drop Trigger if Exists billjour_AI; #DELIMITER $$ #CREATE TRIGGER billjour_AI After Insert on billjour #For Each Row Begin # if new.bill_type='C' then # update bills set journaldt_id2=new.journaldt_id # where ledger_number=new.ledger_number # and bill_number=new.bill_number; # else # insert into bills (bill_number,ledger_number,bill_bank,ledger_activity, # currency_code,bill_ValueDate, # bill_Amount,bill_Amount1,bill_Amount2,journaldt_id1) # values # (new.bill_number,new.ledger_number,new.bill_bank, # (select ledger_activity from ledger where ledger_number=new.ledger_number), # new.currency_code,new.bill_valuedate, # new.bill_Amount,new.bill_Amount1,new.bill_Amount2,new.journaldt_id); # end if; #END $$ #Delimiter; #Drop Trigger if Exists billjour_BD; #DELIMITER $$ #CREATE TRIGGER billjour_BD Before DELETE on billjour #For Each Row Begin # if old.bill_type='C' then # update bills set journaldt_id2=0 # where ledger_number=old.ledger_number # and bill_number=old.bill_number; # else # delete from bills # WHERE bill_number=old.bill_number # and ledger_number=old.ledger_number # and journaldt_id1=old.journaldt_id # and (journaldt_id2=0 or journaldt_id2 is null); # end if; #END $$ #Delimiter; ##TOOK FROM TAREK 11/11/2011 Drop Trigger if exists GRNPurchaseDt_BD; Delimiter $$ CREATE Trigger GRNPurchaseDt_BD Before Delete on GRNPurchasesdt For Each Row Begin Declare exit handler for 1442 begin end; Delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type ='GR'; if old.RelatedDt_Id <> 0 then Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO'; end if; UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity where GRNPurchases.Purchase_ID = old.Purchase_ID; UPDATE GRNPurchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem - 1 where GRNPurchases.Purchase_ID = old.Purchase_ID; END $$ Delimiter; Drop Trigger if exists GRNPurchaseDT_AI; Delimiter $$ CREATE Trigger GRNPurchaseDT_AI After Insert on GRNPurchasesDT For Each Row Begin UPDATE GRNPurchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem + 1 where GRNPurchases.Purchase_ID = new.Purchase_ID; if new.Journal_Type = 'PI' then Set @Sign = 1; else if new.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code, Costcent_Code,QuantityEntered,Price,PriceBase1,PriceBase2,CostBase1,CostBase2, UnitCoef, Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Purchasedt_id,new.Purchase_Date,'GR',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code, @Sign*new.PurchaseDt_Quantity,0,0,0,0,0, new.Item_UnitCoef, @Sign*new.Item_Unit2Qty, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo); if new.RelatedDt_Id <> 0 then Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO'; end if; END $$ Delimiter; Drop Trigger if exists GRNPurchaseDT_AU; Delimiter $$ CREATE Trigger GRNPurchaseDT_AU After Update on GRNPurchasesDT For Each Row Begin UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity where GRNPurchases.Purchase_ID = old.Purchase_ID; UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity where GRNPurchases.Purchase_ID = new.Purchase_ID; if new.Journal_Type = 'PI' then Set @Sign = 1; else if new.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type = 'GR'; UPDATE GRNPurchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code, Costcent_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, UnitCoef, Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Purchasedt_id,new.Purchase_Date,'GR',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code, @Sign*new.PurchaseDt_Quantity,0,0,0,0,0,0, new.Item_UnitCoef, new.Item_Unit2Qty, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo); if new.RelatedDt_Id <> 0 then Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO'; Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO'; end if; END $$ Delimiter; Drop Trigger if exists ProdDtItem_BI; Delimiter $$ CREATE Trigger ProdDtItem_BI Before Insert on ProdDtItem for each row begin set new.serialno_code=concat( (( (dayofyear(new.proddtitem_date)-1) + (dayofweek(concat(year(new.proddtitem_date),'-01-01'))) -1 ) div 7) +1, substr(year(new.proddtitem_date),3), substr('0000',1,4-length(new.ProdDtItem_Reference1)), new.ProdDtItem_Reference1, substr('0000',1,4-length(new.ProdDtItem_Reference2)) , new.ProdDtItem_Reference2); END $$ Delimiter; Drop Trigger if exists ProdDtItem_BU; Delimiter $$ CREATE Trigger ProdDtItem_BU Before Update on ProdDtItem for each row begin set new.serialno_code=concat( (( (dayofyear(new.proddtitem_date)-1) + (dayofweek(concat(year(new.proddtitem_date),'-01-01'))) -1 ) div 7) +1, substr(year(new.proddtitem_date),3), substr('0000',1,4-length(new.ProdDtItem_Reference1)), new.ProdDtItem_Reference1, substr('0000',1,4-length(new.ProdDtItem_Reference2)) , new.ProdDtItem_Reference2); END $$ Delimiter; ###################################################################################################### # ProductionRM Table Drop Trigger if Exists ProductionRM_AI; DELIMITER $$ CREATE TRIGGER ProductionRM_AI AFTER INSERT ON ProductionRM FOR EACH ROW BEGIN /*Select Divisions_Code Into @ProjectCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'PROJECT'; Select Divisions_Code Into @CostcenterCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'COSTCENTER'; Select Divisions_Code Into @MachineCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'MACHINE';*/ Set @JourType := 'PDT'; if (new.RelatedDt_Id Is Not Null and new.RelatedDt_Id != "") Then Set @JourType := 'PDO'; End If; Select ProductionHD_Reversed into @Reversed From ProductionHD where ProductionHD_ID = new.ProductionHD_ID; if @Reversed = 1 then set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo) Values (new.ProductionRM_Id,new.ProductionHD_Date,@JourType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,(new.ProductionRM_Quantity),new.ProductionRM_Cost,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,0,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionRM_ExpireDate,new.ProductionRM_BatchNo); else set @Sign = -1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo) Values (new.ProductionRM_Id,new.ProductionHD_Date,@JourType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,(new.ProductionRM_Quantity),new.ProductionRM_Cost,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,0,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionRM_ExpireDate,new.ProductionRM_BatchNo); end if; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.ProductionRM_ID,new.ProductionRM_ID,@JourType,new.Size_Measure,new.Color_Code,@Sign * new.ProductionRM_Quantity); END $$ DELIMITER; Drop Trigger if Exists ProductionRM_AU; DELIMITER $$ CREATE TRIGGER ProductionRM_AU AFTER UPDATE ON ProductionRM FOR EACH ROW BEGIN Set @JourType := 'PDT'; if (new.RelatedDt_Id Is Not Null and new.RelatedDt_Id != "") Then Set @JourType := 'PDO'; End If; Delete from StkTransaction where Transaction_ID = old.ProductionRM_Id and Transaction_Type = @JourType; Select ProductionHD_Reversed into @Reversed From ProductionHD where ProductionHD_ID = new.ProductionHD_ID; /*Select Divisions_Code Into @ProjectCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'PROJECT'; Select Divisions_Code Into @CostcenterCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'COSTCENTER'; Select Divisions_Code Into @MachineCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'MACHINE';*/ if @Reversed = 1 then set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo) Values (new.ProductionRM_id,new.ProductionHD_Date,@JourType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,new.ProductionRM_Quantity,new.ProductionRM_Cost,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,0,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionRM_ExpireDate,new.ProductionRM_BatchNo); else set @Sign = -1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo) Values (new.ProductionRM_id,new.ProductionHD_Date,@JourType,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,new.ProductionRM_Quantity,new.ProductionRM_Cost,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,0,new.ProductionRM_CostBase1,new.ProductionRM_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionRM_ExpireDate,new.ProductionRM_BatchNo); end if; #Delete from StkTransactionSizes where Transactiondt_id = old.ProductionRM_ID and Transaction_Type = @JourType; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.ProductionRM_ID,new.ProductionRM_ID,@JourType,new.Size_Measure,new.Color_Code,@Sign * new.ProductionRM_Quantity); END $$ DELIMITER; Drop Trigger if Exists ProductionRM_BD; DELIMITER $$ CREATE TRIGGER ProductionRM_BD BEFORE DELETE ON ProductionRM FOR EACH ROW BEGIN Set @JourType := 'PDT'; if (old.RelatedDt_Id Is Not Null and old.RelatedDt_Id != "") Then Set @JourType := 'PDO'; End If; Delete from StkTransaction where Transaction_ID = old.ProductionRM_Id and Transaction_Type = @JourType; # Delete from StkTransactionSizes where Transactiondt_id = old.ProductionRM_ID and Transaction_Type = @JourType; END $$ Delimiter; ###################################################################################################### # ProductionFG Table Drop Trigger if Exists ProductionFG_AI; DELIMITER $$ CREATE TRIGGER ProductionFG_AI AFTER INSERT ON ProductionFG FOR EACH ROW BEGIN /*Select Divisions_Code Into @ProjectCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'PROJECT'; Select Divisions_Code Into @CostcenterCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'COSTCENTER'; Select Divisions_Code Into @MachineCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'MACHINE';*/ Select ProductionHD_Reversed into @Reversed From ProductionHD where ProductionHD_ID = new.ProductionHD_ID; if @Reversed = 1 then set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo) Values (new.ProductionFG_Id,new.ProductionHD_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,(new.ProductionFG_Quantity),new.ProductionFG_Cost,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,0,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionFG_ExpireDate,new.ProductionFG_BatchNo); else set @Sign = -1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo) Values (new.ProductionFG_Id,new.ProductionHD_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,(new.ProductionFG_Quantity),new.ProductionFG_Cost,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,0,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionFG_ExpireDate,new.ProductionFG_BatchNo); end if; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.ProductionFG_ID,new.ProductionFG_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.ProductionFG_Quantity); END $$ DELIMITER; Drop Trigger if Exists ProductionFG_AU; DELIMITER $$ CREATE TRIGGER ProductionFG_AU AFTER UPDATE ON ProductionFG FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.ProductionFG_Id and Transaction_Type = 'PD'; Select ProductionHD_Reversed into @Reversed From ProductionHD where ProductionHD_ID = new.ProductionHD_ID; /*Select Divisions_Code Into @ProjectCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'PROJECT'; Select Divisions_Code Into @CostcenterCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'COSTCENTER'; Select Divisions_Code Into @MachineCode From ProductionHDDivisions Inner Join Divisions on ProductionHDDivisions.Divisions_Id = Divisions.Divisions_Id Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id Where ProductionHD_ID = new.ProductionHD_ID And Upper(DivisionTypes_Name) = 'MACHINE';*/ if @Reversed = 1 then set @Sign = 1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceOut,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo) Values (new.ProductionFG_id,new.ProductionHD_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,new.ProductionFG_Quantity,new.ProductionFG_Cost,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,0,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionFG_ExpireDate,new.ProductionFG_BatchNo); else set @Sign = -1; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Machine_Code,Currency_Code,QuantityProduceIn,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty,Item_ExpireDate,Item_BatchNo) Values (new.ProductionFG_id,new.ProductionHD_Date,'PD',new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcenter_Code,null,new.Currency_Code,new.ProductionFG_Quantity,new.ProductionFG_Cost,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,0,new.ProductionFG_CostBase1,new.ProductionFG_CostBase2,new.Item_UnitCoef,new.Item_Unit2Qty,new.ProductionFG_ExpireDate,new.ProductionFG_BatchNo); end if; #Delete from StkTransactionSizes where Transactiondt_id = old.ProductionFG_ID and Transaction_Type = 'PD'; #Insert into `StkTransactionSizes` (TransactionDt_ID,Transaction_ID,Transaction_Type,Size_Measure,Color_Code,Quantity) #Values (new.ProductionFG_ID,new.ProductionFG_ID,'PD',new.Size_Measure,new.Color_Code,@Sign * new.ProductionFG_Quantity); END $$ DELIMITER; Drop Trigger if Exists ProductionFG_BD; DELIMITER $$ CREATE TRIGGER ProductionFG_BD BEFORE DELETE ON ProductionFG FOR EACH ROW BEGIN Delete from StkTransaction where Transaction_ID = old.ProductionFG_Id and Transaction_Type = 'PD'; #Delete from StkTransactionSizes where Transactiondt_id = old.ProductionFG_ID and Transaction_Type = 'PD'; END $$ Delimiter; ########################################################################################################## Drop Trigger if Exists Ledger_BI; DELIMITER $$ CREATE TRIGGER Ledger_BI before insert on ledger For Each Row Begin #if new.ledger_ID is null then set new.ledger_id = (Select ifnull(max(ledger_id),1)+1 AS MAXID from ledger); #end if; END $$ Delimiter; #**************************************************************************************************************** /*Drop Trigger if Exists Currency_BI; DELIMITER $$ CREATE TRIGGER Currency_BI before insert on Currency For Each Row Begin #if new.Currency_ID is null then set new.Currency_id = (Select max(Currency_id)+1 AS MAXID from Currency); #end if; END $$ Delimiter; */ Drop Trigger if Exists clients_BI; DELIMITER $$ CREATE TRIGGER clients_BI before insert on clients For Each Row Begin #if new.client_ID is null then set new.client_id = (Select ifnull(max(client_id),0)+1 AS MAXID from clients); #end if; END $$ Delimiter; /* Drop Trigger if Exists costcent_BI; DELIMITER $$ CREATE TRIGGER costcent_BI before insert on costcent For Each Row Begin #if new.costcent_ID is null then set new.costcent_id = (Select max(ifnull(costcent_id,0))+1 AS MAXID from costcent); #end if; END $$ Delimiter; */ #**************************************************************************************************************** #Gemayel trigger #Drop Trigger if Exists bankledger_AI; #Delimiter $$ #create trigger bankledger_AI after insert on ledger #for each row begin # if substr(new.ledger_number,1,3) = '511' then # insert into bankledger( Bank_ID, Ledger_Number,BankType_id, BankLedgerType_id, bankledger_outstandingcheck) # values(7,new.ledger_number,6,2,0); # end if; #END $$ delimiter; #**************************************************************************************************************** #Gilbert 25-11-2013 /*Drop Trigger if Exists project_BI; DELIMITER $$ CREATE TRIGGER project_BI before insert on project For Each Row Begin #if new.project_ID is null then set new.project_id = (Select max(ifnull(project_id,0))+1 AS MAXID from project); #end if; END $$ Delimiter;*/ #**************************************************************************************************************** #Nabil 27-01-2014 Drop Trigger if Exists Items_BI; /*DELIMITER $$ CREATE TRIGGER Items_BI before insert on items For Each Row Begin if new.Item_Unit < 0 then set new.Item_Unit = 1; end if; set new.item_id = (Select max(ifnull(item_id,0))+1 AS MAXID from items); END $$ Delimiter; */ ####################################################################################################################### #nadine #04-10-2016 Drop Trigger if exists PurchaseDT_AI; Delimiter $$ CREATE Trigger PurchaseDT_AI After Insert on PurchasesDT For Each Row Begin UPDATE Purchases SET Purchase_TotalNoOfItem = Purchase_TotalNoOfItem + 1 where Purchases.Purchase_ID = new.Purchase_ID; if new.Journal_Type = 'PI' then Set @Sign = 1; UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity where Purchases.Purchase_ID = new.Purchase_ID; else if new.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; Set @ItemUnit = 0; Select Item_Code, Item_Unit,Item_LastDatePurchase into @ItemCode, @ItemUnit ,@LastPurchaseDate From Items WHERE Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; #Ayman 03.12.2009 Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then Set @Price = new.PurchaseDt_Price + @Sign*new.PurchaseDT_Charges/new.PurchaseDt_Quantity - new.PurchaseDt_Price*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountAmount/new.PurchaseDt_Quantity; Set @PriceBase1 = new.PurchaseDt_PriceBase1 + @Sign*new.PurchaseDT_ChargesBase1/new.PurchaseDt_Quantity - new.PurchaseDt_PriceBase1*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase1/new.PurchaseDt_Quantity; Set @PriceBase2 = new.PurchaseDt_PriceBase2 + @Sign*new.PurchaseDT_ChargesBase2/new.PurchaseDt_Quantity - new.PurchaseDt_PriceBase2*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase2/new.PurchaseDt_Quantity; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty, Item_ExpireDate, Item_BatchNo) Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,@Sign*new.PurchaseDt_Quantity,@Price,ifnull(@PriceBase1,0),ifnull(@PriceBase2,0),new.Synchronized,ifnull(@PriceBase1,0),ifnull(@PriceBase2,0),new.Item_UnitCoef,new.Item_Unit2Qty, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo ); else Set @Price=((new.PurchaseDt_Price/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_Charges/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_Price/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountAmount/(@ItemUnit*new.PurchaseDt_Quantity))); Set @PriceBase1=((new.PurchaseDt_PriceBase1/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase1/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_PriceBase1/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase1/(@ItemUnit*new.PurchaseDt_Quantity))); Set @PriceBase2=((new.PurchaseDt_PriceBase2/@ItemUnit)+(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase2/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_PriceBase2/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase2/(@ItemUnit*new.PurchaseDt_Quantity))); if @Price is null then set @Price = 0;end if; if @PriceBase1 is null then set @PriceBase1 = 0;end if; if @PriceBase2 is null then set @PriceBase2 = 0;end if; Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2, Item_ExpireDate, Item_BatchNo) #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*@ItemUnit),@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2); Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code)) ,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2, new.PurchaseDt_ExpireDate, new.PurchaseDt_BatchNo); end if; if new.RelatedDt_Id <> 0 and new.journal_type = 'PI' then Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO'; end if; # select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = new.Purchase_id; # select module_freezone into @module from module limit 1; # if @module then # update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn + @sign * new.PurchaseDt_Quantity # where Purchase_invoiceNo = @PInvoice and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code; # if row_count() = 0 then # insert into itemCustom # set Purchase_invoiceNo = @PInvoice, Item_code = new.Item_Code, Warehouse_code = new.Warehouse_Code, # ItemCust_QtyIn = @sign * new.PurchaseDt_Quantity; # end if; # end if; # if ((@LastPurchaseDate <=new.Purchase_Date) or (@LastPurchaseDate is null)) AND (new.journal_type = 'PI') AND (@price>0) then # update items set Item_LastDatePurchase= cast(new.Purchase_Date as date), # item_lastcostprice=if(currency_code=new.currency_code,@price, # convertcurFunc(new.currency_code,currency_code,new.Purchase_Date,0,0,'','',@price)), # item_lastcostpriceBase1=@PriceBase1,item_lastcostpriceBase2=@PriceBase2 # where item_code=new.item_code; # end if; set @VarDate:=null; select ItemTrans_LastDatePurchase into @VarDate from ItemsTrans where item_code=new.Item_Code; if @VarDate is null then insert into ItemsTrans (Item_code,ItemTrans_LastDatePurchase, itemTrans_lastcostprice,itemTrans_lastcostpriceBase1,itemTrans_lastcostpricebase2,ItemTrans_LastPricePurchase) values (new.Item_Code,new.Purchase_date,@Price,@PriceBase1,@PriceBase2,new.PurchaseDt_Price); else if new.Purchase_date>=@VarDate then update ItemsTrans set ItemTrans_LastDatePurchase=new.Purchase_date, itemTrans_lastcostprice=@Price, itemTrans_lastcostpriceBase1=@PriceBase1, itemTrans_lastcostpriceBase2=@PriceBase2, ItemTrans_LastPricePurchase=new.PurchaseDt_Price where item_code=new.Item_Code; end if; end if; #nadine #04-10-2016 select items.Category_code,Currency_Code into @item_category, @item_cur from items left join category on category.Category_code = items.Category_code where item_code = new.Item_Code; select ifnull(module_sellingbyclient,0),ifnull(sellingprice_basedonCost,0) into @module_sellingbyclient, @sellingprice_basedonCost from module; if @sellingprice_basedonCost =1 and @item_category is not null and @item_category != '' then set @vtotprice := (((ifnull(NEW.PurchaseDt_Price,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountAmount,0) ) + ifnull(NEW.PurchaseDt_Charges,0))/NEW.PurchaseDt_Quantity; set @vtotpriceb1 := (((ifnull(NEW.PurchaseDt_PriceBase1,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountBase1,0) ) + ifnull(NEW.PurchaseDt_ChargesBase1,0))/NEW.PurchaseDt_Quantity; set @vtotpriceb2 := (((ifnull(NEW.PurchaseDt_PriceBase2,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountBase2,0) ) + ifnull(NEW.PurchaseDt_ChargesBase2,0))/NEW.PurchaseDt_Quantity; set @vtotpricecur := convertcurFunc(@item_cur, NEW.Currency_Code, NEW.Purchase_Date, 0, 0, '', '', @vtotprice); update items,category set Item_SellingPrice1 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price1,0)/100)), Item_SellingPrice1Base1 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price1,0)/100)), Item_SellingPrice1Base2 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price1,0)/100)), Item_SellingPrice2 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price2,0)/100)), Item_SellingPrice2Base1 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price2,0)/100)), Item_SellingPrice2Base2 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price2,0)/100)), Item_SellingPrice3 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price3,0)/100)), Item_SellingPrice3Base1 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price3,0)/100)), Item_SellingPrice3Base2 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price3,0)/100)), Item_SellingPrice4 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price4,0)/100)), Item_SellingPrice4Base1 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price4,0)/100)), Item_SellingPrice4Base2 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price4,0)/100)), Item_SellingPrice5 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price5,0)/100)), Item_SellingPrice5Base1 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price5,0)/100)), Item_SellingPrice5Base2 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price5,0)/100)), Item_SellingPrice6 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price6,0)/100)), Item_SellingPrice6Base1 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price6,0)/100)), Item_SellingPrice6Base2 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price6,0)/100)) where category.Category_code = items.Category_code and item_code=NEW.item_code; if @module_sellingbyclient = 0 then select clientssellingprice_amount=Item_SellingPrice1, clientssellingprice_amount2=Item_SellingPrice2, clientssellingprice_amount3=Item_SellingPrice3, clientssellingprice_amount4 = Item_SellingPrice4, clientssellingprice_amount5 = Item_SellingPrice5, clientssellingprice_amount6 = Item_SellingPrice6 into @checkprice1,@checkprice2,@checkprice3,@checkprice4,@checkprice5,@checkprice6 from clientssellingprice left join items on item_code = clientssellingprice_code where clientssellingprice_code = NEW.item_code and clientssellingprice_default = 1; if @checkprice1=0 or @checkprice2=0 or @checkprice3=0 or @checkprice4=0 or @checkprice5=0 or @checkprice6=0 then update clientssellingprice set clientssellingprice_default = 0 where clientssellingprice_code = NEW.item_code; insert into clientssellingprice (clientssellingprice_code, clientssellingprice_default,clientssellingprice_currency, clientssellingprice_amount,clientssellingprice_amountb1, clientssellingprice_amountb2, clientssellingprice_amount2,clientssellingprice_amount2b1, clientssellingprice_amount2b2, clientssellingprice_amount3, clientssellingprice_amount3b1, clientssellingprice_amount3b2, clientssellingprice_amount4, clientssellingprice_amount4b1, clientssellingprice_amount4b2, clientssellingprice_amount5, clientssellingprice_amount5b1,clientssellingprice_amount5b2, clientssellingprice_amount6, clientssellingprice_amount6b1, clientssellingprice_amount6b2) select NEW.item_code, 1,@item_cur, Item_SellingPrice1,Item_SellingPrice1Base1,Item_SellingPrice1Base2, Item_SellingPrice2,Item_SellingPrice2Base1,Item_SellingPrice2Base2, Item_SellingPrice3,Item_SellingPrice3Base1,Item_SellingPrice3Base2, Item_SellingPrice4,Item_SellingPrice4Base1,Item_SellingPrice4Base2, Item_SellingPrice5,Item_SellingPrice5Base1,Item_SellingPrice5Base2, Item_SellingPrice6,Item_SellingPrice6Base1,Item_SellingPrice6Base2 from items where item_code = NEW.item_code; end if; end if; end if; END $$ Delimiter; #################################################################################################################### #nadine #04-10-2016 Drop Trigger if exists PurchaseDT_AU; Delimiter $$ CREATE Trigger PurchaseDT_AU After Update on PurchasesDT For Each Row Begin if new.Journal_Type = 'PI' then Set @Sign = 1; UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity - old.PurchaseDt_Quantity where Purchases.Purchase_ID = old.Purchase_ID; UPDATE Purchases SET Purchase_TotalQuantity = Purchase_TotalQuantity + new.PurchaseDt_Quantity where Purchases.Purchase_ID = new.Purchase_ID; else if new.Journal_Type = 'PR' then Set @Sign = -1; end if; end if; Set @ItemUnit = 0; Select Item_Code, Item_Unit,item_LastDatePurchase into @ItemCode, @ItemUnit ,@LastPurchaseDate From Items WHERE Item_Code = new.Item_Code; IF @ItemUnit=0 OR @ItemUnit IS NULL then set @ItemUnit=1; end if; #toni habshi 3.09.2008 #delete from StkTransaction where Transaction_id = old.Purchasedt_id and Transaction_Type = old.Journal_Type; #Ayman 03.12.2009 Select Module_DoubleUnit into @ModuleDoubleUnit From Module Limit 1; if @ModuleDoubleUnit then Set @Price = new.PurchaseDt_Price + @Sign*new.PurchaseDT_Charges/new.PurchaseDt_Quantity - new.PurchaseDt_Price*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountAmount/new.PurchaseDt_Quantity; Set @PriceBase1 = new.PurchaseDt_PriceBase1 + @Sign*new.PurchaseDT_ChargesBase1/new.PurchaseDt_Quantity - new.PurchaseDt_PriceBase1*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase1/new.PurchaseDt_Quantity; Set @PriceBase2 = new.PurchaseDt_PriceBase2 + @Sign*new.PurchaseDT_ChargesBase2/new.PurchaseDt_Quantity - new.PurchaseDt_PriceBase2*new.purchasedt_discountper/100 - new.PurchaseDt_DiscountBase2/new.PurchaseDt_Quantity; #Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2,UnitCoef,Unit2Qty) #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,@Sign*new.PurchaseDt_Quantity,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2,new.Item_UnitCoef,new.Item_Unit2Qty); update `StkTransaction` set Transaction_Date=new.Purchase_Date ,Item_Code=new.Item_Code , Warehouse_Code=new.Warehouse_Code , Project_Code=new.Project_Code ,Costcent_Code=new.Costcent_Code ,Currency_Code=new.Currency_Code ,QuantityEntered=@Sign*new.PurchaseDt_Quantity ,Price=@Price ,PriceBase1=@PriceBase1 ,PriceBase2=@PriceBase2 ,Synchronized=new.Synchronized ,CostBase1=@PriceBase1 ,CostBase2=@PriceBase2 ,UnitCoef=new.Item_UnitCoef ,Unit2Qty=new.Item_Unit2Qty ,Item_ExpireDate = new.PurchaseDt_ExpireDate ,Item_BatchNo = new.PurchaseDt_BatchNo where Transaction_ID=new.Purchasedt_id and Transaction_Type=new.Journal_Type; else Set @Price = ((new.PurchaseDt_Price/@ItemUnit) +(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_Charges/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_Price/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountAmount/(@ItemUnit*new.PurchaseDt_Quantity))); Set @PriceBase1 = ((new.PurchaseDt_PriceBase1/@ItemUnit) + (if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase1/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_PriceBase1/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase1/(@ItemUnit*new.PurchaseDt_Quantity))); Set @PriceBase2 = ((new.PurchaseDt_PriceBase2/@ItemUnit) +(if(new.Journal_Type = 'PI',1,-1)*new.PurchaseDT_ChargesBase2/(@ItemUnit*new.PurchaseDt_Quantity))- ((((new.PurchaseDt_PriceBase2/@ItemUnit)*new.purchasedt_discountper)/100)+new.PurchaseDt_DiscountBase2/(@ItemUnit*new.PurchaseDt_Quantity))); if @Price is null then set @Price = 0;end if; if @PriceBase1 is null then set @PriceBase1 = 0;end if; if @PriceBase2 is null then set @PriceBase2 = 0;end if; #Insert into `StkTransaction` (Transaction_ID,Transaction_Date,Transaction_Type,Item_Code, Warehouse_Code, Project_Code,Costcent_Code,Currency_Code,QuantityEntered,Price,PriceBase1,PriceBase2,Synchronized,CostBase1,CostBase2) #Values (new.Purchasedt_id,new.Purchase_Date,new.Journal_Type,new.Item_Code,new.Warehouse_Code,new.Project_Code,new.Costcent_Code,new.Currency_Code,(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code)) #,@Price,@PriceBase1,@PriceBase2,new.Synchronized,@PriceBase1,@PriceBase2); update `StkTransaction` set Transaction_Date=new.Purchase_Date ,Item_Code=new.Item_Code , Warehouse_Code=new.Warehouse_Code , Project_Code=new.Project_Code ,Costcent_Code=new.Costcent_Code ,Currency_Code=new.Currency_Code ,QuantityEntered=(@Sign*new.PurchaseDt_Quantity*(Select Item_Unit From Items WHERE Item_Code = new.Item_Code)) ,Price=@Price ,PriceBase1=@PriceBase1 ,PriceBase2=@PriceBase2 ,Synchronized=new.Synchronized ,CostBase1=@PriceBase1 ,CostBase2=@PriceBase2 ,Item_ExpireDate = new.PurchaseDt_ExpireDate ,Item_BatchNo = new.PurchaseDt_BatchNo where Transaction_ID=new.Purchasedt_id and Transaction_Type=new.Journal_Type; end if; if new.RelatedDt_Id <> 0 and new.journal_type = 'PI' then Update `StkTransaction` Set QuantityPordered = QuantityPordered + old.PurchaseDt_Quantity where Transaction_ID = old.RelatedDt_ID and Transaction_Type = 'PO'; Update `StkTransaction` Set QuantityPordered = QuantityPordered - new.PurchaseDt_Quantity where Transaction_ID = new.RelatedDt_ID and Transaction_Type = 'PO'; end if; # select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = old.Purchase_id; # select module_freezone into @module from module limit 1; # if @module then # update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn - @sign * old.PurchaseDt_Quantity # where Purchase_invoiceNo = @PInvoice and Item_code = old.Item_Code and Warehouse_code = old.Warehouse_Code; # if row_count() = 0 then # Insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = old.Item_Code, # Warehouse_code = old.Warehouse_Code, ItemCust_QtyIn = (-1)*@sign * old.PurchaseDt_Quantity; # end if; # end if; # select Purchase_invoiceno into @PInvoice from Purchases where Purchase_Id = new.Purchase_id; # select module_freezone into @module from module limit 1; # if @module then # update itemCustom set ItemCust_QtyIn = ItemCust_QtyIn + @sign * new.PurchaseDt_Quantity # where Purchase_invoiceNo = @PInvoice and Item_code = new.Item_Code and Warehouse_code = new.Warehouse_Code; # if row_count() = 0 then # insert into itemCustom set Purchase_invoiceNo = @PInvoice, Item_code = new.Item_Code, # Warehouse_code = new.Warehouse_Code, ItemCust_QtyIn = @sign * new.PurchaseDt_Quantity; # end if; # end if; # if ((@LastPurchaseDate <=new.Purchase_Date) or (@LastPurchaseDate is null)) AND (new.journal_type = 'PI') AND (@price>0) then # update items set Item_LastDatePurchase= cast(new.Purchase_Date as date), # item_lastcostprice=if(currency_code=new.currency_code,@price, # convertcurFunc(new.currency_code,currency_code,new.Purchase_Date,0,0,'','',@price)), # item_lastcostpriceBase1=@PriceBase1,item_lastcostpriceBase2=@PriceBase2 # where item_code=new.item_code; # end if; set @VarDate:=null; delete from itemstrans where item_code=old.Item_Code and ItemTrans_LastDatePurchase=old.Purchase_date; select ItemTrans_LastDatePurchase into @VarDate from ItemsTrans where item_code=new.Item_Code; if @VarDate is null then insert into ItemsTrans (Item_code,ItemTrans_LastDatePurchase, itemTrans_lastcostprice,itemTrans_lastcostpriceBase1,itemTrans_lastcostpriceBase2,ItemTrans_LastPricePurchase) values (new.Item_Code,new.Purchase_date,@Price,@PriceBase1,@PriceBase2,new.PurchaseDt_Price); else if new.Purchase_date>=@VarDate then update ItemsTrans set ItemTrans_LastDatePurchase=new.Purchase_date, itemTrans_lastcostprice=@Price, itemTrans_lastcostpriceBase1=@PriceBase1, itemTrans_lastcostpriceBase2=@PriceBase2, ItemTrans_LastPricePurchase=new.PurchaseDt_Price where item_code=new.Item_Code; end if; end if; #nadine #04-10-2016 select items.Category_code,Currency_Code into @item_category, @item_cur from items left join category on category.Category_code = items.Category_code where item_code = new.Item_Code; select ifnull(module_sellingbyclient,0),ifnull(sellingprice_basedonCost,0) into @module_sellingbyclient, @sellingprice_basedonCost from module; if @sellingprice_basedonCost =1 and @item_category is not null and @item_category != '' then set @vtotprice := (((ifnull(NEW.PurchaseDt_Price,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountAmount,0) ) + ifnull(NEW.PurchaseDt_Charges,0))/NEW.PurchaseDt_Quantity; set @vtotpriceb1 := (((ifnull(NEW.PurchaseDt_PriceBase1,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountBase1,0) ) + ifnull(NEW.PurchaseDt_ChargesBase1,0))/NEW.PurchaseDt_Quantity; set @vtotpriceb2 := (((ifnull(NEW.PurchaseDt_PriceBase2,0)*NEW.PurchaseDt_Quantity)*(1-(NEW.PurchaseDt_DiscountPer/100)) - ifnull(NEW.PurchaseDt_DiscountBase2,0) ) + ifnull(NEW.PurchaseDt_ChargesBase2,0))/NEW.PurchaseDt_Quantity; set @vtotpricecur := convertcurFunc(@item_cur, NEW.Currency_Code, NEW.Purchase_Date, 0, 0, '', '', @vtotprice); update items,category set Item_SellingPrice1 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price1,0)/100)), Item_SellingPrice1Base1 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price1,0)/100)), Item_SellingPrice1Base2 = if(ifnull(Category_Price1,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price1,0)/100)), Item_SellingPrice2 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price2,0)/100)), Item_SellingPrice2Base1 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price2,0)/100)), Item_SellingPrice2Base2 = if(ifnull(Category_Price2,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price2,0)/100)), Item_SellingPrice3 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price3,0)/100)), Item_SellingPrice3Base1 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price3,0)/100)), Item_SellingPrice3Base2 = if(ifnull(Category_Price3,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price3,0)/100)), Item_SellingPrice4 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price4,0)/100)), Item_SellingPrice4Base1 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price4,0)/100)), Item_SellingPrice4Base2 = if(ifnull(Category_Price4,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price4,0)/100)), Item_SellingPrice5 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price5,0)/100)), Item_SellingPrice5Base1 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price5,0)/100)), Item_SellingPrice5Base2 = if(ifnull(Category_Price5,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price5,0)/100)), Item_SellingPrice6 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpricecur,0) + (ifnull(@vtotpricecur,0)*ifnull(Category_Price6,0)/100)), Item_SellingPrice6Base1 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpriceb1,0) + (ifnull(@vtotpriceb1,0)*ifnull(Category_Price6,0)/100)), Item_SellingPrice6Base2 = if(ifnull(Category_Price6,0)=0,0,ifnull(@vtotpriceb2,0) + (ifnull(@vtotpriceb2,0)*ifnull(Category_Price6,0)/100)) where category.Category_code = items.Category_code and item_code=NEW.item_code; if @module_sellingbyclient = 0 then select clientssellingprice_amount=Item_SellingPrice1, clientssellingprice_amount2=Item_SellingPrice2, clientssellingprice_amount3=Item_SellingPrice3, clientssellingprice_amount4 = Item_SellingPrice4, clientssellingprice_amount5 = Item_SellingPrice5, clientssellingprice_amount6 = Item_SellingPrice6 into @checkprice1,@checkprice2,@checkprice3,@checkprice4,@checkprice5,@checkprice6 from clientssellingprice left join items on item_code = clientssellingprice_code where clientssellingprice_code = NEW.item_code and clientssellingprice_default = 1; if @checkprice1=0 or @checkprice2=0 or @checkprice3=0 or @checkprice4=0 or @checkprice5=0 or @checkprice6=0 then update clientssellingprice set clientssellingprice_default = 0 where clientssellingprice_code = NEW.item_code; insert into clientssellingprice (clientssellingprice_code, clientssellingprice_default,clientssellingprice_currency, clientssellingprice_amount,clientssellingprice_amountb1, clientssellingprice_amountb2, clientssellingprice_amount2,clientssellingprice_amount2b1, clientssellingprice_amount2b2, clientssellingprice_amount3, clientssellingprice_amount3b1, clientssellingprice_amount3b2, clientssellingprice_amount4, clientssellingprice_amount4b1, clientssellingprice_amount4b2, clientssellingprice_amount5, clientssellingprice_amount5b1,clientssellingprice_amount5b2, clientssellingprice_amount6, clientssellingprice_amount6b1, clientssellingprice_amount6b2) select NEW.item_code, 1,@item_cur, Item_SellingPrice1,Item_SellingPrice1Base1,Item_SellingPrice1Base2, Item_SellingPrice2,Item_SellingPrice2Base1,Item_SellingPrice2Base2, Item_SellingPrice3,Item_SellingPrice3Base1,Item_SellingPrice3Base2, Item_SellingPrice4,Item_SellingPrice4Base1,Item_SellingPrice4Base2, Item_SellingPrice5,Item_SellingPrice5Base1,Item_SellingPrice5Base2, Item_SellingPrice6,Item_SellingPrice6Base1,Item_SellingPrice6Base2 from items where item_code = NEW.item_code; end if; end if; end if; END $$ Delimiter; #####################################################################################################################