/// <summary> /// Work /// </summary> protected override void DoWork() { _summary = new StringBuilder(); // Get Schemata if (_modelLocal.GetC_AcctSchema_ID() == 0) { _ass = MAcctSchema.GetClientAcctSchema(GetCtx(), _modelLocal.GetAD_Client_ID()); } else // only specific accounting schema { _ass = new MAcctSchema[] { new MAcctSchema(GetCtx(), _modelLocal.GetC_AcctSchema_ID(), null) }; } // PostSession(); MCost.Create(_clientLocal); // int no = _modelLocal.DeleteLog(); _summary.Append("Logs deleted=").Append(no); // MAcctProcessorLog pLog = new MAcctProcessorLog(_modelLocal, _summary.ToString()); pLog.SetReference("#" + Utility.Util.GetValueOfString(_runCount) // String.valueOf(p_runCount) + " - " + TimeUtil.FormatElapsed(_startWork)); //new DateTime(_startWork))); pLog.Save(); }
/// <summary> /// Get Price from Price List /// </summary> /// <param name="cost">cost record</param> /// <returns>price or null</returns> private Decimal?GetPrice(MCost cost) { Decimal?retValue = null; String sql = "SELECT PriceLimit " + "FROM M_ProductPrice " + "WHERE M_Product_ID=" + cost.GetM_Product_ID() + " AND M_PriceList_Version_ID=" + _M_PriceList_Version_ID; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, null); //pstmt.setInt (1, cost.GetM_Product_ID()); //pstmt.setInt (2, _M_PriceList_Version_ID); //ResultSet dr = pstmt.executeQuery (); if (idr.Read()) { retValue = Utility.Util.GetValueOfDecimal(idr[0]);//.getBigDecimal(1); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } return(retValue); }
/// <summary> /// Create New Client level Costing Record /// </summary> /// <param name="product">product</param> /// <param name="?">acct schema</param> /// <returns>true if created</returns> private bool CreateNew(MProduct product, MAcctSchema as1) { MCost cost = MCost.Get(product, 0, as1, 0, _ce.GetM_CostElement_ID()); if (cost.Is_New()) { return(cost.Save()); } return(false); }
public ActionResult Costs_Create([DataSourceRequest] DataSourceRequest request, CostViewModel custVM) { if (custVM != null && ModelState.IsValid) { MCost cust = new MCost(); cust.SetAssignedIdTo(Guid.NewGuid().ToString()); ConvertToCost(custVM, cust); cust.CreatedDate = DateTime.Now; cust.CreatedBy = User.Identity.Name; cust.DataStatus = "New"; _costTasks.Insert(cust); } return(Json(new[] { custVM }.ToDataSourceResult(request, ModelState))); }
/// <summary> /// Update Cost Records /// </summary> /// <param name="cost">cost</param> /// <returns>true if updated</returns> private bool Update(MCost cost) { bool updated = false; if (_SetFutureCostTo.Equals(_SetStandardCostTo)) { Decimal costs = Utility.Util.GetValueOfDecimal(GetCosts(cost, _SetFutureCostTo)); if (Env.Signum(costs) != 0) { cost.SetFutureCostPrice(costs); cost.SetCurrentCostPrice(costs); updated = true; } } else { if (_SetStandardCostTo.Length > 0) { Decimal costs = Utility.Util.GetValueOfDecimal(GetCosts(cost, _SetStandardCostTo)); if (Env.Signum(costs) != 0) { cost.SetCurrentCostPrice(costs); updated = true; } } if (_SetFutureCostTo.Length > 0) { Decimal costs = Utility.Util.GetValueOfDecimal(GetCosts(cost, _SetFutureCostTo)); if (Env.Signum(costs) != 0) { cost.SetFutureCostPrice(costs); updated = true; } } } if (updated) { updated = cost.Save(); } return(updated); }
/// <summary> /// Get Old Current Cost Price /// </summary> /// <param name="cost">costs</param> /// <returns>price if found</returns> private Decimal?GetOldCurrentCostPrice(MCost cost) { Decimal?retValue = null; String sql = "SELECT CostStandard, CurrentCostPrice " + "FROM M_Product_Costing " + "WHERE M_Product_ID=" + cost.GetM_Product_ID() + " AND C_AcctSchema_ID=" + cost.GetC_AcctSchema_ID(); IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, null); //pstmt.setInt (1, cost.GetM_Product_ID()); //pstmt.setInt (2, cost.GetC_AcctSchema_ID()); //ResultSet dr = pstmt.executeQuery (); if (idr.Read()) { retValue = Utility.Util.GetValueOfDecimal(idr[0]);//.getBigDecimal(1); if (retValue == null || Env.Signum(Utility.Util.GetValueOfDecimal(retValue)) == 0) { retValue = Utility.Util.GetValueOfDecimal(idr[1]); } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } return(retValue); }
/// <summary> /// Get Total Costs (amt*qty) in Accounting Schema Currency /// </summary> /// <param name="as1">accounting schema</param> /// <param name="AD_Org_ID"></param> /// <param name="costingMethod">if null uses Accounting Schema - AcctSchema.COSTINGMETHOD_*</param> /// <param name="C_OrderLine_ID">optional order line</param> /// <param name="zeroCostsOK">zero/no costs are OK</param> /// <returns>cost or null, if qty or costs cannot be determined</returns> public Decimal?GetProductCosts(MAcctSchema as1, int AD_Org_ID, String costingMethod, int C_OrderLine_ID, bool zeroCostsOK) { if (_qty == null) { log.Fine("No Qty"); return(null); } // No Product if (_product == null) { log.Fine("No Product"); return(null); } // Decimal?cost = MCost.GetCurrentCost(_product, _M_AttributeSetInstance_ID, as1, AD_Org_ID, costingMethod, Utility.Util.GetValueOfDecimal(_qty), C_OrderLine_ID, zeroCostsOK, _trx); if (cost == null || cost == 0) { log.Fine("No Costs"); return(null); } return(cost); }
/// <summary> /// Get Costs /// </summary> /// <param name="cost">Cost</param> /// <param name="to">where to get costs from </param> /// <returns>costs (could be 0) or null if not found</returns> private Decimal?GetCosts(MCost cost, String to) { Decimal?retValue = null; // Average Invoice if (to.Equals(TO_AverageInvoice)) { MCostElement ce = GetCostElement(TO_AverageInvoice); if (ce == null) { throw new Exception("CostElement not found: " + TO_AverageInvoice); } MCost xCost = MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } // Average Invoice History else if (to.Equals(TO_AverageInvoiceHistory)) { MCostElement ce = GetCostElement(TO_AverageInvoice); if (ce == null) { throw new Exception("CostElement not found: " + TO_AverageInvoice); } MCost xCost = MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetHistoryAverage(); } } // Average PO else if (to.Equals(TO_AveragePO)) { MCostElement ce = GetCostElement(TO_AveragePO); if (ce == null) { throw new Exception("CostElement not found: " + TO_AveragePO); } MCost xCost = MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } // Average PO History else if (to.Equals(TO_AveragePOHistory)) { MCostElement ce = GetCostElement(TO_AveragePO); if (ce == null) { throw new Exception("CostElement not found: " + TO_AveragePO); } MCost xCost = MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetHistoryAverage(); } } // FiFo else if (to.Equals(TO_FiFo)) { MCostElement ce = GetCostElement(TO_FiFo); if (ce == null) { throw new Exception("CostElement not found: " + TO_FiFo); } MCost xCost = MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } // Future Std Costs else if (to.Equals(TO_FutureStandardCost)) { retValue = cost.GetFutureCostPrice(); } // Last Inv Price else if (to.Equals(TO_LastInvoicePrice)) { MCostElement ce = GetCostElement(TO_LastInvoicePrice); if (ce != null) { MCost xCost = MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } if (retValue == null) { MProduct product = MProduct.Get(GetCtx(), cost.GetM_Product_ID()); MAcctSchema as1 = MAcctSchema.Get(GetCtx(), cost.GetC_AcctSchema_ID()); retValue = MCost.GetLastInvoicePrice(product, cost.GetM_AttributeSetInstance_ID(), cost.GetAD_Org_ID(), as1.GetC_Currency_ID()); } } // Last PO Price else if (to.Equals(TO_LastPOPrice)) { MCostElement ce = GetCostElement(TO_LastPOPrice); if (ce != null) { MCost xCost = MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } if (retValue == null) { MProduct product = MProduct.Get(GetCtx(), cost.GetM_Product_ID()); MAcctSchema as1 = MAcctSchema.Get(GetCtx(), cost.GetC_AcctSchema_ID()); retValue = MCost.GetLastPOPrice(product, cost.GetM_AttributeSetInstance_ID(), cost.GetAD_Org_ID(), as1.GetC_Currency_ID()); } } // FiFo else if (to.Equals(TO_LiFo)) { MCostElement ce = GetCostElement(TO_LiFo); if (ce == null) { throw new Exception("CostElement not found: " + TO_LiFo); } MCost xCost = MCost.Get(GetCtx(), cost.GetAD_Client_ID(), cost.GetAD_Org_ID(), cost.GetM_Product_ID(), cost.GetM_CostType_ID(), cost.GetC_AcctSchema_ID(), ce.GetM_CostElement_ID(), cost.GetM_AttributeSetInstance_ID()); if (xCost != null) { retValue = xCost.GetCurrentCostPrice(); } } // Old Std Costs else if (to.Equals(TO_OldStandardCost)) { retValue = GetOldCurrentCostPrice(cost); } // Price List else if (to.Equals(TO_PriceListLimit)) { retValue = GetPrice(cost); } // Standard Costs else if (to.Equals(TO_StandardCost)) { retValue = cost.GetCurrentCostPrice(); } return(retValue); }
/// <summary> /// Update Cost Records /// </summary> /// <returns>no updated</returns> private int Update() { int counter = 0; String sql = "SELECT * FROM M_Cost c WHERE M_CostElement_ID=" + _ce.GetM_CostElement_ID(); if (_M_Product_Category_ID != 0) { sql += " AND EXISTS (SELECT * FROM M_Product p " + "WHERE c.M_Product_ID=p.M_Product_ID AND p.M_Product_Category_ID=" + _M_Product_Category_ID + ")"; } DataTable dt = null; IDataReader idr = null; try { idr = DataBase.DB.ExecuteReader(sql, null, null); //pstmt.setInt (1, _ce.GetM_CostElement_ID()); //if (_M_Product_Category_ID != 0) // pstmt.setInt (2, _M_Product_Category_ID); //ResultSet dr = pstmt.executeQuery (); dt = new DataTable(); dt.Load(idr); idr.Close(); foreach (DataRow dr in dt.Rows) { MCost cost = new MCost(GetCtx(), dr, Get_Trx()); for (int i = 0; i < _ass.Length; i++) { // Update Costs only for default Cost Type if (_ass[i].GetC_AcctSchema_ID() == cost.GetC_AcctSchema_ID() && _ass[i].GetM_CostType_ID() == cost.GetM_CostType_ID()) { if (Update(cost)) { counter++; } } } } } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql, e); } finally { dt = null; if (idr != null) { idr.Close(); } } log.Info("#" + counter); AddLog(0, null, new Decimal(counter), "@Updated@"); return(counter); }
} // setWorkOrder /// <summary> /// Called before Save for Pre-Save Operation /// Set Line number if missing ///Check for duplicate component under one operation. /// </summary> /// <param name="newRecord"></param> /// <returns></returns> protected override Boolean BeforeSave(bool newRecord) { // Get Line No if (GetVAMFG_Line() == 0) { String sql = "SELECT COALESCE(MAX(VAMFG_Line),0)+10 FROM VAMFG_M_WorkOrderComponent WHERE VAMFG_M_WorkOrderOperation_ID=" + GetVAMFG_M_WorkOrderOperation_ID(); int ii = DB.GetSQLValue(Get_TrxName(), sql); SetVAMFG_Line(ii); } if (newRecord) { String sql = "SELECT * from VAMFG_M_WorkOrderComponent WHERE M_Product_ID=@param1 and VAMFG_M_WorkOrderOperation_ID = @param2"; SqlParameter[] param = null; IDataReader idr = null; DataTable dt = new DataTable(); //PreparedStatement pstmt = DB.prepareStatement(sql, null); //ResultSet rs = null; bool success = true; try { param = new SqlParameter[2]; param[0] = new SqlParameter("@param1", GetM_Product_ID()); param[1] = new SqlParameter("@param2", GetVAMFG_M_WorkOrderOperation_ID()); idr = DB.ExecuteReader(sql, param, null); if (idr.Read()) { // Show error log.SaveError("Error", Msg.GetMsg(GetCtx(), "DuplicateComponent")); success = false; } if (idr != null) { idr.Close(); idr = null; } } catch { if (idr != null) { idr.Close(); idr = null; } log.Log(Level.SEVERE, sql); } if (!success) { return(false); } } if ((GetVAMFG_QtyRequired().CompareTo(Env.ZERO)) < 0) { log.SaveError("Error", Msg.ParseTranslation(GetCtx(), "@QtyRequired@ < 0")); return(false); } if (GetVAMFG_Line() < 0) { log.SaveError("Error", Msg.ParseTranslation(GetCtx(), "@Line@ < 0")); return(false); } // get current cost from product cost on new record and when product changed // currency conversion also required if order has different currency with base currency if (newRecord || (Is_ValueChanged("M_Product_ID"))) { if (GetM_Product_ID() > 0) { decimal currentcostprice = MCost.GetproductCosts(GetAD_Client_ID(), GetAD_Org_ID(), GetM_Product_ID(), VAdvantage.Utility.Util.GetValueOfInt(GetM_AttributeSetInstance_ID()), Get_TrxName()); MVAMFGMWorkOrderOperation woo = new MVAMFGMWorkOrderOperation(GetCtx(), GetVAMFG_M_WorkOrderOperation_ID(), Get_TrxName()); MVAMFGMWorkOrder wor = new MVAMFGMWorkOrder(GetCtx(), woo.GetVAMFG_M_WorkOrder_ID(), Get_TrxName()); if (wor.GetVAMFG_Description() != null && wor.GetVAMFG_Description().Contains("(->")) { // not to set cuurent cost price on reversal because its already filed during creation of line } else { SetCurrentCostPrice(currentcostprice); } } } if (Is_ValueChanged("VAMFG_QtyRequired")) { //BigDecimal qtyRequired = getQtyRequired().setScale(MUOM.getPrecision(getCtx(), getC_UOM_ID()), BigDecimal.ROUND_HALF_UP); Decimal qtyRequired = Decimal.Round((GetVAMFG_QtyRequired()), VAdvantage.Model.MUOM.GetPrecision(GetCtx(), GetC_UOM_ID())); if (qtyRequired.CompareTo(GetVAMFG_QtyRequired()) != 0) { log.Fine("Corrected QtyRequired Scale UOM=" + GetC_UOM_ID() + "; QtyRequired=" + GetVAMFG_QtyRequired() + "->" + qtyRequired); SetVAMFG_QtyRequired(qtyRequired); } } return(true); } // beforeSave
} // beforeDelete /// <summary> /// Is used to save record pn production execution line /// </summary> /// <param name="newRecord">newRecord</param> /// <returns>TRUE if success</returns> protected override Boolean BeforeSave(bool newRecord) { if (GetVAMFG_Line() < 0) { log.SaveError("Error", Msg.ParseTranslation(GetCtx(), "@Line@ < 0")); return(false); } MVAMFGMWrkOdrTransaction wot = new MVAMFGMWrkOdrTransaction(GetCtx(), GetVAMFG_M_WrkOdrTransaction_ID(), Get_TrxName()); string _sql = "Select Sum(GOM01_ActualQuantity) as QtyEntered From VAMFG_M_WrkOdrTrnsctionLine Where VAMFG_M_WrkOdrTransaction_ID=" + GetVAMFG_M_WrkOdrTransaction_ID() + " AND M_Product_ID=" + GetM_Product_ID() + "" + " AND M_AttributeSetInstance_ID=" + GetM_AttributeSetInstance_ID(); decimal Qty = Util.GetValueOfDecimal(DB.ExecuteScalar(_sql)); if (Qty > 0) { Qty = Qty + GetGOM01_ActualQuantity(); string qry = "SELECT currentqty FROM M_Transaction WHERE M_Transaction_ID = (SELECT MAX(M_Transaction_ID) FROM M_Transaction WHERE movementdate = " + " (SELECT MAX(movementdate) FROM M_Transaction WHERE movementdate <= " + GlobalVariable.TO_DATE(wot.GetVAMFG_DateTrx(), true) + " AND M_Product_ID = " + GetM_Product_ID() + " AND M_Locator_ID = " + GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + GetM_AttributeSetInstance_ID() + ") AND M_Product_ID = " + GetM_Product_ID() + " AND M_Locator_ID = " + GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + GetM_AttributeSetInstance_ID() + ") AND AD_Org_ID = " + GetAD_Org_ID() + " AND M_Product_ID = " + GetM_Product_ID() + " AND M_Locator_ID = " + GetM_Locator_ID() + " AND M_AttributeSetInstance_ID = " + GetM_AttributeSetInstance_ID(); decimal CurrentQty = VAdvantage.Utility.Util.GetValueOfDecimal(DB.ExecuteScalar(qry)); if (CurrentQty < Qty) { log.SaveError("Error", Msg.Translate(GetCtx(), "Insufficient qty in warehouse for the product")); return(false); } } // get current cost from product cost on new record and when product changed // currency conversion also required if order has different currency with base currency if ((newRecord || (Is_ValueChanged("M_Product_ID"))) && GetM_Product_ID() > 0) { decimal currentcostprice = MCost.GetproductCosts(GetAD_Client_ID(), GetAD_Org_ID(), GetM_Product_ID(), VAdvantage.Utility.Util.GetValueOfInt(GetM_AttributeSetInstance_ID()), Get_TrxName()); decimal currentQty = GetCurrentQtyFromMCost(GetAD_Client_ID(), GetAD_Org_ID(), GetM_Product_ID(), VAdvantage.Utility.Util.GetValueOfInt(GetM_AttributeSetInstance_ID()), Get_TrxName()); bool isGomel = Env.IsModuleInstalled("GOM01_"); //MVAMFGMWrkOdrTransaction wot = new MVAMFGMWrkOdrTransaction(GetCtx(), GetVAMFG_M_WrkOdrTransaction_ID(), Get_TrxName()); if (wot.GetVAMFG_Description() != null && wot.GetVAMFG_Description().Contains("{->")) { // not to set cuurent cost price on reversal because its already filed during creation of line } else if (isGomel && Get_ColumnIndex("GOM01_ActualQuantity") > 0 && GetGOM01_ActualQuantity() <= currentQty) { SetCurrentCostPrice(currentcostprice); } else if (!isGomel) // if gomel module is not available, then update currenct cost price on save { SetCurrentCostPrice(currentcostprice); } } if (Is_ValueChanged("VAMFG_QtyEntered")) { //BigDecimal qtyEntered = GetVAMFG_QtyEntered().setScale(MUOM.getPrecision(getCtx(), getC_UOM_ID()), BigDecimal.ROUND_HALF_UP); Decimal qtyEntered = Decimal.Round((GetVAMFG_QtyEntered()), VAdvantage.Model.MUOM.GetPrecision(GetCtx(), GetC_UOM_ID())); if (qtyEntered.CompareTo(GetVAMFG_QtyEntered()) != 0) { log.Fine("Corrected QtyEntered Scale UOM=" + GetC_UOM_ID() + "; QtyEntered =" + GetVAMFG_QtyEntered() + "->" + qtyEntered); SetVAMFG_QtyEntered(qtyEntered); } } return(true); }
private void ConvertToCost(CostViewModel custVM, MCost cust) { cust.CostName = custVM.CostName; }
/// <summary> /// Calculate Cost based on Qty based on in Lifo/Fifo order /// </summary> /// <param name="product">product</param> /// <param name="M_ASI_ID">costing level ASI</param> /// <param name="mas">accounting schema</param> /// <param name="Org_ID">costing level org</param> /// <param name="ce">Cost Element</param> /// <param name="Qty">quantity to be reduced</param> /// <param name="trxName">transaction</param> /// <returns>cost for qty or null of error</returns> public static Decimal?GetCosts(MProduct product, int M_ASI_ID, MAcctSchema mas, int Org_ID, MCostElement ce, Decimal Qty, Trx trxName) { if (Env.Signum(Qty) == 0) { return(Env.ZERO); } MCostQueue[] costQ = GetQueue(product, M_ASI_ID, mas, Org_ID, ce, trxName); // Decimal cost = Env.ZERO; Decimal remainingQty = Qty; Decimal?firstPrice = null; Decimal?lastPrice = null; // for (int i = 0; i < costQ.Length; i++) { MCostQueue queue = costQ[i]; // Negative Qty i.e. add if (Env.Signum(remainingQty) <= 0) { Decimal oldQty = queue.GetCurrentQty(); lastPrice = queue.GetCurrentCostPrice(); Decimal costBatch = Decimal.Multiply((Decimal)lastPrice, remainingQty); cost = Decimal.Add(cost, costBatch); _log.Config("ASI=" + queue.GetM_AttributeSetInstance_ID() + " - Cost=" + lastPrice + " * Qty=" + remainingQty + "(!) = " + costBatch); return(cost); } // Positive queue if (Env.Signum(queue.GetCurrentQty()) > 0) { Decimal reduction = remainingQty; if (reduction.CompareTo(queue.GetCurrentQty()) > 0) { reduction = queue.GetCurrentQty(); } Decimal oldQty = queue.GetCurrentQty(); lastPrice = queue.GetCurrentCostPrice(); Decimal costBatch = Decimal.Multiply((Decimal)lastPrice, reduction); cost = Decimal.Add(cost, costBatch); _log.Fine("ASI=" + queue.GetM_AttributeSetInstance_ID() + " - Cost=" + lastPrice + " * Qty=" + reduction + " = " + costBatch); remainingQty = Decimal.Subtract(remainingQty, reduction); // Done if (Env.Signum(remainingQty) == 0) { _log.Config("Cost=" + cost); return(cost); } if (firstPrice == null) { firstPrice = lastPrice; } } } // for queue if (lastPrice == null) { lastPrice = MCost.GetSeedCosts(product, M_ASI_ID, mas, Org_ID, ce.GetCostingMethod(), 0); if (lastPrice == null) { _log.Info("No Price found"); return(null); } _log.Info("No Cost Queue"); } Decimal costBatch1 = Decimal.Multiply((Decimal)lastPrice, remainingQty); _log.Fine("RemainingQty=" + remainingQty + " * LastPrice=" + lastPrice + " = " + costBatch1); cost = Decimal.Add(cost, costBatch1); _log.Config("Cost=" + cost); return(cost); }
/** * After Save * @param newRecord new * @param success success * @return success */ protected override bool AfterSave(bool newRecord, bool success) { if (!success) { return(success); } // Value/Name change in Account if (!newRecord && (Is_ValueChanged("Value") || Is_ValueChanged("Name"))) { MAccount.UpdateValueDescription(GetCtx(), "M_Product_ID=" + GetM_Product_ID(), Get_TrxName()); } // Name/Description Change in Asset MAsset.setValueNameDescription if (!newRecord && (Is_ValueChanged("Name") || Is_ValueChanged("Description"))) { //String sql = "UPDATE A_Asset a " // + "SET (Name, Description)=" // + "(SELECT SUBSTR(bp.Name || ' - ' || p.Name,1,60), p.Description " // + "FROM M_Product p, C_BPartner bp " // + "WHERE p.M_Product_ID=a.M_Product_ID AND bp.C_BPartner_ID=a.C_BPartner_ID) " // + "WHERE IsActive='Y'" // // + " AND GuaranteeDate > SysDate" // + " AND M_Product_ID=" + GetM_Product_ID(); String sql = " UPDATE A_Asset a SET Name=(SELECT SUBSTR(bp.Name || ' - ' || p.Name,1,60) FROM M_Product p, C_BPartner bp WHERE p.M_Product_ID=a.M_Product_ID AND bp.C_BPartner_ID=a.C_BPartner_ID)," + "Description=(SELECT p.Description FROM M_Product p, C_BPartner bp WHERE p.M_Product_ID=a.M_Product_ID AND bp.C_BPartner_ID=a.C_BPartner_ID)" + "WHERE IsActive='Y' AND M_Product_ID=" + GetM_Product_ID(); int no = 0; try { no = Utility.Util.GetValueOfInt(DataBase.DB.ExecuteQuery(sql, null, Get_TrxName())); } catch { } log.Fine("Asset Description updated #" + no); } // New - Acct, Tree, Old Costing if (newRecord) { Insert_Accounting("M_Product_Acct", "M_Product_Category_Acct", "p.M_Product_Category_ID=" + GetM_Product_Category_ID()); // MAcctSchema[] mass = MAcctSchema.GetClientAcctSchema(GetCtx(), GetAD_Client_ID(), Get_TrxName()); for (int i = 0; i < mass.Length; i++) { // Old MProductCosting pcOld = new MProductCosting(this, mass[i].GetC_AcctSchema_ID()); pcOld.Save(); } } // New Costing if (newRecord || Is_ValueChanged("M_Product_Category_ID")) { MCost.Create(this); } return(success); }
/// <summary> /// Before Save /// </summary> /// <param name="newRecord">new</param> /// <returns>true</returns> protected override Boolean BeforeSave(Boolean newRecord) { Decimal VA024_ProvisionPrice = 0; MProduct product = MProduct.Get(GetCtx(), GetM_Product_ID()); // chck pallet Functionality applicable or not bool isContainrApplicable = MTransaction.ProductContainerApplicable(GetCtx()); // Get Old Value of AttributeSetInstance_ID _mvlOldAttId = Util.GetValueOfInt(Get_ValueOld("M_AttributeSetInstance_ID")); // Set Line No if (GetLine() == 0) { String sql = "SELECT COALESCE(MAX(Line),0)+10 AS DefaultValue FROM M_MovementLine WHERE M_Movement_ID=" + GetM_Movement_ID(); int ii = DataBase.DB.GetSQLValue(Get_TrxName(), sql); SetLine(ii); } // JID_0775: System is not checking on move line that the attribute set instance is mandatory. if (GetM_AttributeSetInstance_ID() == 0) { if (product != null && product.GetM_AttributeSet_ID() != 0) { MAttributeSet mas = MAttributeSet.Get(GetCtx(), product.GetM_AttributeSet_ID()); if (mas.IsMandatory() || mas.IsMandatoryAlways()) { log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "M_AttributeSetInstance_ID")); return(false); } } } // Check Locator For Header Warehouse MMovement mov = new MMovement(GetCtx(), GetM_Movement_ID(), Get_TrxName()); MLocator loc = new MLocator(GetCtx(), GetM_Locator_ID(), Get_TrxName()); Tuple <string, string, string> aInfo = null; if (Env.HasModulePrefix("DTD001_", out aInfo)) { if (mov.GetDTD001_MWarehouseSource_ID() == loc.GetM_Warehouse_ID()) { } else { String sql = "SELECT M_Locator_ID FROM M_Locator WHERE M_Warehouse_ID=" + mov.GetDTD001_MWarehouseSource_ID() + " AND IsDefault = 'Y'"; int ii = DataBase.DB.GetSQLValue(Get_TrxName(), sql); SetM_Locator_ID(ii); } } // when we try to move product from container to container which are in same locator then no need to check this case if (GetM_Locator_ID() == GetM_LocatorTo_ID() && (Get_ColumnIndex("M_ProductContainer_ID") > 0 && Get_ColumnIndex("Ref_M_ProductContainerTo_ID") > 0 && !(GetM_ProductContainer_ID() > 0 || GetRef_M_ProductContainerTo_ID() > 0))) { log.SaveError("Error", Msg.ParseTranslation(GetCtx(), "'From @M_Locator_ID@' and '@M_LocatorTo_ID@' cannot be same."));//change message according to requirement return(false); } if (Env.Signum(GetMovementQty()) == 0 && Util.GetValueOfInt(GetTargetQty()) == 0) { log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "MovementQty")); return(false); } Tuple <String, String, String> mInfo = null; if (Env.HasModulePrefix("DTD001_", out mInfo)) { if (!newRecord && Util.GetValueOfInt(Get_ValueOld("M_Product_ID")) != GetM_Product_ID()) { log.SaveError("Message", Msg.GetMsg(GetCtx(), "DTD001_ProdNotChanged")); return(false); } if (!newRecord && Util.GetValueOfInt(Get_ValueOld("M_Locator_ID")) != GetM_Locator_ID()) { log.SaveError("Message", Msg.GetMsg(GetCtx(), "DTD001_LocatorNotChanged")); return(false); } if (!newRecord && Util.GetValueOfInt(Get_ValueOld("M_RequisitionLine_ID")) != GetM_RequisitionLine_ID()) { log.SaveError("Message", Msg.GetMsg(GetCtx(), "DTD001_ReqNotChanged")); return(false); } } //if (Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA203_'", null, null)) > 0) if (Env.IsModuleInstalled("VA203_")) { if (GetM_RequisitionLine_ID() > 0) { MRequisitionLine reqline = new MRequisitionLine(GetCtx(), GetM_RequisitionLine_ID(), Get_Trx()); // Trx used to handle query stuck problem if (GetM_AttributeSetInstance_ID() != reqline.GetM_AttributeSetInstance_ID()) { log.SaveError("Message", Msg.GetMsg(GetCtx(), "VA203_AttributeInstanceMustBeSame")); return(false); } } } // IF Doc Status = InProgress then No record Save MMovement move = new MMovement(GetCtx(), GetM_Movement_ID(), Get_Trx()); // Trx used to handle query stuck problem if (newRecord && move.GetDocStatus() == "IP") { log.SaveError("Message", Msg.GetMsg(GetCtx(), "DTD001_CannotCreate")); return(false); } // Qty Precision if (newRecord || Is_ValueChanged("QtyEntered")) { SetQtyEntered(GetQtyEntered()); } // change to set Converted Quantity in Movement quantity if there is differnce in UOM of Base Product and UOM Selected on line if (newRecord || Is_ValueChanged("QtyEntered") || Is_ValueChanged("C_UOM_ID")) { Decimal?qty = Util.GetValueOfDecimal(Get_Value("QtyEntered")); if (product.GetC_UOM_ID() != Util.GetValueOfInt(Get_Value("C_UOM_ID"))) { SetMovementQty(MUOMConversion.ConvertProductFrom(GetCtx(), GetM_Product_ID(), Util.GetValueOfInt(Get_Value("C_UOM_ID")), Util.GetValueOfDecimal(Get_Value("QtyEntered")))); } } // Qty Precision if (newRecord || Is_ValueChanged("QtyEntered")) { SetMovementQty(GetMovementQty()); } string qry; if (!mov.IsProcessing() || newRecord) { MWarehouse wh = null; MWarehouse whTo = null; wh = MWarehouse.Get(GetCtx(), mov.GetDTD001_MWarehouseSource_ID()); whTo = MWarehouse.Get(GetCtx(), MLocator.Get(GetCtx(), GetM_LocatorTo_ID()).GetM_Warehouse_ID()); qry = "SELECT NVL(SUM(NVL(QtyOnHand,0)),0) AS QtyOnHand FROM M_Storage where m_locator_id=" + GetM_Locator_ID() + " and m_product_id=" + GetM_Product_ID(); if (GetDTD001_AttributeNumber() == null || GetM_AttributeSetInstance_ID() > 0) { qry += " AND NVL(M_AttributeSetInstance_ID , 0) =" + GetM_AttributeSetInstance_ID(); } OnHandQty = Convert.ToDecimal(DB.ExecuteScalar(qry)); qry = "SELECT NVL(SUM(NVL(QtyOnHand,0)),0) AS QtyOnHand FROM M_Storage where m_locator_id=" + GetM_LocatorTo_ID() + " and m_product_id=" + GetM_Product_ID(); qry += " AND NVL(M_AttributeSetInstance_ID, 0) =" + GetM_AttributeSetInstance_ID(); OnHandQtyTo = Convert.ToDecimal(DB.ExecuteScalar(qry)); // SI_0635 : System is giving error of insufficient qty if disallow is true in TO warehouse and false in From warehouse // when record is in completed & closed stage - then no need to check qty availablity in warehouse if ((wh.IsDisallowNegativeInv() || whTo.IsDisallowNegativeInv()) && (!(move.GetDocStatus() == "CO" || move.GetDocStatus() == "CL" || move.GetDocStatus() == "RE" || move.GetDocStatus() == "VO"))) { // pick container current qty from transaction based on locator / product / ASI / Container / Movement Date if (isContainrApplicable && Get_ColumnIndex("M_ProductContainer_ID") >= 0) { // qry = @"SELECT SUM(t.ContainerCurrentQty) keep (dense_rank last ORDER BY t.MovementDate, t.M_Transaction_ID) AS CurrentQty FROM m_transaction t // INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + // " AND t.AD_Client_ID = " + GetAD_Client_ID() + // @" AND t.M_Locator_ID = " + (move.IsReversal() && GetMovementQty() < 0 ? GetM_LocatorTo_ID() : GetM_Locator_ID()) + // " AND t.M_Product_ID = " + GetM_Product_ID() + " AND NVL(t.M_AttributeSetInstance_ID,0) = " + GetM_AttributeSetInstance_ID() + // " AND NVL(t.M_ProductContainer_ID, 0) = " + (move.IsReversal() && !IsMoveFullContainer() && GetMovementQty() < 0 ? GetRef_M_ProductContainerTo_ID() : GetM_ProductContainer_ID()); // containerQty = Util.GetValueOfDecimal(DB.ExecuteScalar(qry.ToString(), null, null)); // dont use Transaction here - otherwise impact goes wrong on completion // qry = @"SELECT SUM(t.ContainerCurrentQty) keep (dense_rank last ORDER BY t.MovementDate, t.M_Transaction_ID) AS CurrentQty FROM m_transaction t // INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + // " AND t.AD_Client_ID = " + GetAD_Client_ID() + // @" AND t.M_Locator_ID = " + (move.IsReversal() && GetMovementQty() < 0 ? GetM_Locator_ID() : GetM_LocatorTo_ID()) + // " AND t.M_Product_ID = " + GetM_Product_ID() + " AND NVL(t.M_AttributeSetInstance_ID,0) = " + GetM_AttributeSetInstance_ID() + // " AND NVL(t.M_ProductContainer_ID, 0) = " + (move.IsReversal() && !IsMoveFullContainer() && GetMovementQty() < 0 ? GetM_ProductContainer_ID() : GetRef_M_ProductContainerTo_ID()); // containerQtyTo = Util.GetValueOfDecimal(DB.ExecuteScalar(qry.ToString(), null, null)); // dont use Transaction here - otherwise impact goes wrong on completion qry = @"SELECT DISTINCT First_VALUE(t.ContainerCurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + " AND t.AD_Client_ID = " + GetAD_Client_ID() + @" AND t.M_Locator_ID = " + GetM_Locator_ID() + " AND t.M_Product_ID = " + GetM_Product_ID() + " AND NVL(t.M_ProductContainer_ID, 0) = " + GetM_ProductContainer_ID(); // In Case of Attribute Number do not check qty with attribute from storage if (GetDTD001_AttributeNumber() == null || GetM_AttributeSetInstance_ID() > 0) { qry += " AND NVL(M_AttributeSetInstance_ID , 0) =" + GetM_AttributeSetInstance_ID(); } containerQty = Util.GetValueOfDecimal(DB.ExecuteScalar(qry.ToString(), null, null)); // dont use Transaction here - otherwise impact goes wrong on completion qry = @"SELECT DISTINCT First_VALUE(t.ContainerCurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(move.GetMovementDate(), true) + " AND t.AD_Client_ID = " + GetAD_Client_ID() + @" AND t.M_Locator_ID = " + GetM_LocatorTo_ID() + " AND t.M_Product_ID = " + GetM_Product_ID() + " AND NVL(t.M_AttributeSetInstance_ID,0) = " + GetM_AttributeSetInstance_ID() + " AND NVL(t.M_ProductContainer_ID, 0) = " + GetRef_M_ProductContainerTo_ID(); containerQtyTo = Util.GetValueOfDecimal(DB.ExecuteScalar(qry.ToString(), null, null)); // dont use Transaction here - otherwise impact goes wrong on completion } if (wh.IsDisallowNegativeInv() && (OnHandQty - GetMovementQty()) < 0) { // check for From Locator log.SaveError("Info", product.GetName() + " , " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQty); return(false); } else if (isContainrApplicable && wh.IsDisallowNegativeInv() && Get_ColumnIndex("M_ProductContainer_ID") >= 0 && (containerQty - GetMovementQty()) < 0) { // check container qty - for From Locator log.SaveError("Info", product.GetName() + " , " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQtyContainer") + containerQty); return(false); } else if (whTo.IsDisallowNegativeInv() && (OnHandQtyTo + GetMovementQty()) < 0) { // check for To Locator log.SaveError("Info", product.GetName() + " , " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQty") + OnHandQtyTo); return(false); } else if (isContainrApplicable && whTo.IsDisallowNegativeInv() && Get_ColumnIndex("M_ProductContainer_ID") >= 0 && (containerQtyTo + GetMovementQty()) < 0) { // check for To Locator log.SaveError("Info", product.GetName() + " , " + Msg.GetMsg(GetCtx(), "VIS_InsufficientQtyContainerTo") + containerQtyTo); return(false); } } } if (Env.IsModuleInstalled("DTD001_")) { // not used this variable, that why commented //qry = "SELECT NVL(SUM(NVL(QtyOnHand,0)- qtyreserved),0) AS QtyAvailable FROM M_Storage where m_locator_id=" + GetM_Locator_ID() + " and m_product_id=" + GetM_Product_ID(); //if (GetM_AttributeSetInstance_ID() != 0) //{ // qry += " AND NVL(M_AttributeSetInstance_ID , 0) = " + GetM_AttributeSetInstance_ID(); //} //qtyAvailable = Convert.ToDecimal(DB.ExecuteScalar(qry)); qtyReserved = Util.GetValueOfDecimal(Get_ValueOld("MovementQty")); //if (wh.IsDisallowNegativeInv() == true) //{ // if ((qtyAvailable < (GetMovementQty() - qtyReserved))) // { // log.SaveError("Message", product.GetName() + " , " + Msg.GetMsg(GetCtx(), "DTD001_QtyNotAvailable")); // return false; // } //} } //By Amit - 17-April-2017 if (Env.IsModuleInstalled("VA024_")) { // checking are we moving product from one warehouse to other warehouse if (Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT CASE WHEN ((SELECT CASE WHEN o.IsLegalEntity = 'Y' THEN w.AD_Org_ID ELSE (SELECT AD_Org_ID FROM AD_Org WHERE Ad_Org_id = o.LegalEntityOrg ) END FROM m_warehouse w INNER JOIN ad_org o ON o.AD_Org_ID = w.AD_Org_ID WHERE w.m_warehouse_id = m.DTD001_MWarehouseSource_ID)) = (SELECT CASE WHEN o2.IsLegalEntity = 'Y' THEN w2.AD_Org_ID ELSE (SELECT AD_Org_ID FROM AD_Org WHERE Ad_Org_id = o2.LegalEntityOrg) END FROM m_warehouse w2 INNER JOIN ad_org o2 ON o2.AD_Org_ID = w2.AD_Org_ID WHERE M_Warehouse_ID = m.M_Warehouse_ID ) THEN 0 ELSE (SELECT ad_org_id FROM m_warehouse WHERE M_Warehouse_ID = m.M_Warehouse_ID ) END AS result FROM m_movement m WHERE m_movement_id = " + GetM_Movement_ID(), null, Get_Trx())) > 0) { string qry1 = @"SELECT SUM(o.VA024_UnitPrice) FROM VA024_t_ObsoleteInventory o WHERE o.IsActive = 'Y' AND o.M_Product_ID = " + GetM_Product_ID() + @" and ( o.M_AttributeSetInstance_ID = " + GetM_AttributeSetInstance_ID() + @" OR o.M_AttributeSetInstance_ID IS NULL )" + " AND o.AD_Org_ID = " + GetAD_Org_ID(); VA024_ProvisionPrice = Util.GetValueOfDecimal(DB.ExecuteScalar(qry1, null, Get_Trx())); SetVA024_UnitPrice(Util.GetValueOfDecimal(VA024_ProvisionPrice * GetMovementQty())); // is used to get cost of binded cost method / costing level of primary accounting schema Decimal cost = MCost.GetproductCosts(move.GetAD_Client_ID(), move.GetAD_Org_ID(), GetM_Product_ID(), GetM_AttributeSetInstance_ID(), Get_Trx(), move.GetDTD001_MWarehouseSource_ID()); SetVA024_CostPrice((cost - VA024_ProvisionPrice) * GetMovementQty()); } } // Mandatory Instance if (GetM_AttributeSetInstanceTo_ID() == 0) { if (GetM_AttributeSetInstance_ID() != 0) // Set to from { SetM_AttributeSetInstanceTo_ID(GetM_AttributeSetInstance_ID()); } else { if (Env.HasModulePrefix("DTD001_", out mInfo)) { //MProduct product = GetProduct(); if (product != null && product.GetM_AttributeSet_ID() != 0) { //MAttributeSet mas = MAttributeSet.Get(GetCtx(), product.GetM_AttributeSet_ID()); //if (mas.IsInstanceAttribute() // && (mas.IsMandatory() || mas.IsMandatoryAlways())) //{ // log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "M_AttributeSetInstanceTo_ID")); // return false; //} // Code Addeded by Bharat as Discussed with Mukesh Sir if (String.IsNullOrEmpty(GetDTD001_AttributeNumber())) { return(true); } else { if (GetDTD001_AttributeNumber() == "" || GetDTD001_AttributeNumber() == null) { log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "DTD001_AttributeNumber")); return(false); } } //JID_1422: Check No Of Attributes Are Equal To Quantity Or Less Than int Count = CountAttributes(GetDTD001_AttributeNumber()); if (Count != GetQtyEntered()) { if (Count > GetQtyEntered()) { log.SaveError("DTD001_MovementAttrbtGreater", ""); return(false); } else { log.SaveError("DTD001_MovementAttrbtLess", ""); return(false); } } } else { if (product != null) { if (product.GetM_AttributeSet_ID() == 0 && (GetDTD001_AttributeNumber() == "" || GetDTD001_AttributeNumber() == null)) { return(true); } else { //log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "DTD001_AttributeNumber")); //ShowMessage.Info("a", true, "Product is not of Attribute Type", null); log.SaveError("Product is not of Attribute Type", Msg.GetElement(GetCtx(), "DTD001_AttributeNumber")); return(false); } //Check No Of Attributes Are Equal To Quantity Or Less Than //int Count = CountAttributes(GetDTD001_AttributeNumber()); //if (Count != GetMovementQty()) //{ // if (Count > GetMovementQty()) // { // log.SaveError("Error", Msg.GetMsg(GetCtx(), "DTD001_MovementAttrbtGreater")); // return false; // } // else // { // log.SaveError("Error", Msg.GetMsg(GetCtx(), "DTD001_MovementAttrbtLess")); // return false; // } //} } } } else { //MProduct product = GetProduct(); if (product != null && product.GetM_AttributeSet_ID() != 0) { MAttributeSet mas = MAttributeSet.Get(GetCtx(), product.GetM_AttributeSet_ID()); if (mas.IsInstanceAttribute() && (mas.IsMandatory() || mas.IsMandatoryAlways())) { log.SaveError("FillMandatory", Msg.GetElement(GetCtx(), "M_AttributeSetInstanceTo_ID")); return(false); } } } } } // ASI return(true); }
/** * After Save * @param newRecord new * @param success success * @return success */ protected override bool AfterSave(bool newRecord, bool success) { if (!success) { return(success); } VAdvantage.Model.MProduct current = new VAdvantage.Model.MProduct(GetCtx(), GetM_Product_ID(), Get_Trx()); StringBuilder _sql = new StringBuilder(""); //_sql.Append("Select count(*) from ad_table where tablename like 'FRPT_Product_Category_Acct'"); _sql.Append("SELECT count(*) FROM all_objects WHERE object_type IN ('TABLE') AND (object_name) = UPPER('FRPT_Product_Category_Acct') AND OWNER LIKE '" + DB.GetSchema() + "'"); int count = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (count > 0) { PO obj = null; //MFRPTProductAcct obj = null; int _MProduct_ID = GetM_Product_ID(); int _PCategory_ID = GetM_Product_Category_ID(); string sql = "SELECT L.VALUE FROM AD_REF_LIST L inner join AD_Reference r on R.AD_REFERENCE_ID=L.AD_REFERENCE_ID where r.name='FRPT_RelatedTo' and l.name='Product'"; //"select VALUE from AD_Ref_List where name='Product'"; string _RelatedToProduct = Convert.ToString(DB.ExecuteScalar(sql)); //string _RelatedToProduct = X_FRPT_AcctDefault.FRPT_RELATEDTO_Product.ToString(); _sql.Clear(); _sql.Append("Select Count(*) From FRPT_Product_Acct where M_Product_ID=" + _MProduct_ID + " AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID()); int value = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (value < 1) { _sql.Clear(); _sql.Append("Select PCA.c_acctschema_id, PCA.c_validcombination_id, PCA.frpt_acctdefault_id From FRPT_product_category_acct PCA inner join frpt_acctdefault ACC ON acc.frpt_acctdefault_id= PCA.frpt_acctdefault_id where PCA.m_product_category_id=" + _PCategory_ID + " and acc.frpt_relatedto=" + _RelatedToProduct + " AND PCA.IsActive = 'Y' AND PCA.AD_Client_ID = " + GetAD_Client_ID()); //_sql.Append("Select C_AcctSchema_ID, C_ValidCombination_ID, FRPT_AcctDefault_ID from FRPT_product_category_acct where m_product_category_id =" + _PCategory_ID); DataSet ds = DB.ExecuteDataset(_sql.ToString()); if (ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { //obj = new MFRPTProductAcct(GetCtx(), 0, null); obj = MTable.GetPO(GetCtx(), "FRPT_Product_Acct", 0, null); obj.Set_ValueNoCheck("AD_Org_ID", 0); obj.Set_ValueNoCheck("M_Product_ID", _MProduct_ID); obj.Set_ValueNoCheck("C_AcctSchema_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_AcctSchema_ID"])); obj.Set_ValueNoCheck("C_ValidCombination_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ValidCombination_ID"])); obj.Set_ValueNoCheck("FRPT_AcctDefault_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"])); if (!obj.Save()) { } } } } // Change by mohit amortization process //int _CountVA038 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA038_' AND IsActive = 'Y'")); //if (_CountVA038 > 0) //{ // if (GetProductType() == "E" || GetProductType() == "S") // { // if (Util.GetValueOfInt(Get_Value("VA038_AmortizationTemplate_ID")) > 0) // { // DataSet _dsAcct = DB.ExecuteDataset("SELECT C_AcctSchema_ID, FRPT_AcctDefault_ID, C_VALIDCOMBINATION_ID, SEQNO FROM VA038_Amortization_Acct " // + "WHERE IsActive='Y' AND VA038_AmortizationTemplate_ID=" + Util.GetValueOfInt(Get_Value("VA038_AmortizationTemplate_ID"))); // if (_dsAcct != null && _dsAcct.Tables[0].Rows.Count > 0) // { // for (int j = 0; j < _dsAcct.Tables[0].Rows.Count; j++) // { // obj = MTable.GetPO(GetCtx(), "FRPT_Product_Acct", 0, null); // obj.Set_ValueNoCheck("AD_Org_ID", 0); // obj.Set_ValueNoCheck("M_Product_ID", _MProduct_ID); // obj.Set_ValueNoCheck("C_AcctSchema_ID", Util.GetValueOfInt(_dsAcct.Tables[0].Rows[j]["C_AcctSchema_ID"])); // obj.Set_ValueNoCheck("C_ValidCombination_ID", Util.GetValueOfInt(_dsAcct.Tables[0].Rows[j]["C_ValidCombination_ID"])); // obj.Set_ValueNoCheck("FRPT_AcctDefault_ID", Util.GetValueOfInt(_dsAcct.Tables[0].Rows[j]["FRPT_AcctDefault_ID"])); // if (!obj.Save()) // { } // } // } // } // } //} // End amortization process } else { // By Amit //if (!success) // return success; // Value/Name change in Account if (!newRecord && (Is_ValueChanged("Value") || Is_ValueChanged("Name"))) { MAccount.UpdateValueDescription(GetCtx(), "M_Product_ID=" + GetM_Product_ID(), Get_TrxName()); } // Name/Description Change in Asset MAsset.setValueNameDescription if (!newRecord && (Is_ValueChanged("Name") || Is_ValueChanged("Description"))) { String sql = " UPDATE A_Asset a SET Name=(SELECT SUBSTR(bp.Name || ' - ' || p.Name,1,60) FROM M_Product p, C_BPartner bp WHERE p.M_Product_ID=a.M_Product_ID AND bp.C_BPartner_ID=a.C_BPartner_ID)," + "Description=(SELECT p.Description FROM M_Product p, C_BPartner bp WHERE p.M_Product_ID=a.M_Product_ID AND bp.C_BPartner_ID=a.C_BPartner_ID)" + "WHERE IsActive='Y' AND M_Product_ID=" + GetM_Product_ID(); int no = 0; try { no = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, Get_TrxName())); } catch { } log.Fine("Asset Description updated #" + no); } // New - Acct, Tree, Old Costing if (newRecord) { if (String.IsNullOrEmpty(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) || Util.GetValueOfString(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) == "Y") { bool sucs = Insert_Accounting("M_Product_Acct", "M_Product_Category_Acct", "p.M_Product_Category_ID=" + GetM_Product_Category_ID()); //Karan. work done to show message if data not saved in accounting tab. but will save data in current tab. // Before this, data was being saved but giving message "record not saved". if (!sucs) { log.SaveWarning("AcctNotSaved", ""); } } // MAcctSchema[] mass = MAcctSchema.GetClientAcctSchema(GetCtx(), GetAD_Client_ID(), Get_TrxName()); for (int i = 0; i < mass.Length; i++) { // Old MProductCosting pcOld = new MProductCosting(current, mass[i].GetC_AcctSchema_ID()); pcOld.Save(); } } // New Costing // by Amit 22-12-2015 //if (newRecord || Is_ValueChanged("M_Product_Category_ID")) //{ // MCost.Create(this); //} } //22-12-2015 //by Amit for creating records ffor product foe all Costing Element whose costing elemnt type is 'Material' if (newRecord || Is_ValueChanged("M_Product_Category_ID")) { MCost.CreateRecords(current); } //20-12-2016 //By Vivek Chauhan saving Nutrition value against product........... object ModuleId = DB.ExecuteScalar("select ad_moduleinfo_id from ad_moduleinfo where prefix='VA019_' and isactive='Y'"); if (ModuleId != null && ModuleId != DBNull.Value) { object objNDBNo = DB.ExecuteScalar("select va019_ndbno from M_Product where m_product_ID=" + GetM_Product_ID() + ""); if (objNDBNo != null && objNDBNo != DBNull.Value) { CallNutritionApi(Convert.ToString(objNDBNo), GetM_Product_ID()); } } if (Is_Changed()) { string updatequery = "Update M_Product set GMT01_IsSpoorPosted='N' Where M_Product_ID=" + GetM_Product_ID(); int no = DB.ExecuteQuery(updatequery); } return(success); }
protected override string DoIt() { try { // Get Combination Record sql = @"SELECT ce.M_CostElement_ID , ce.Name , cel.lineno , cel.m_ref_costelement FROM M_CostElement ce INNER JOIN m_costelementline cel ON ce.M_CostElement_ID = cel.M_CostElement_ID " + " WHERE ce.AD_Client_ID=" + GetAD_Client_ID() + " AND ce.M_CostElement_ID = " + costElement_ID + " AND ce.IsActive='Y' AND cel.IsActive='Y'"; dsCostCombination = DB.ExecuteDataset(sql, null, null); if (dsCostCombination != null && dsCostCombination.Tables.Count > 0 && dsCostCombination.Tables[0].Rows.Count > 0) { for (int i = 0; i < dsCostCombination.Tables[0].Rows.Count; i++) { costElement.Add(Util.GetValueOfInt(dsCostCombination.Tables[0].Rows[i]["m_ref_costelement"])); } } //var costElementRecord = dsCostCombination.Tables[0].AsEnumerable().Select(r => r.Field<int>("m_ref_costelement")).ToList(); // Get All Product sql = @"SELECT ad_client_id , ad_org_id , m_product_id , m_attributesetinstance_id , c_acctschema_id , m_costtype_id , m_costelement_id , cumulatedamt , cumulatedqty , currentcostprice , currentqty FROM m_cost WHERE ad_client_id = " + GetAD_Client_ID() + " ORDER BY m_product_id , ad_org_id , m_attributesetinstance_id , c_acctschema_id"; dsProductCost = DB.ExecuteDataset(sql, null, null); if (dsProductCost != null && dsProductCost.Tables.Count > 0 && dsProductCost.Tables[0].Rows.Count > 0) { // update all record of m_Cost having cost Element = costElement_ID sql = "UPDATE M_Cost SET currentcostprice = 0 , currentqty = 0 , cumulatedamt = 0 , cumulatedqty = 0 WHERE M_CostElement_ID = " + costElement_ID + " AND AD_Client_ID = " + GetAD_Client_ID(); int no = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_TrxName())); for (int i = 0; i < dsProductCost.Tables[0].Rows.Count; i++) { if (!costElement.Contains(Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["m_costelement_id"]))) continue; if (_m_Product_ID != Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["m_product_id"]) || _ad_Org_ID != Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["ad_org_id"]) || _m_Attributesetinstance_ID != Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["m_attributesetinstance_id"]) || _c_AcctSchema_ID != Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["c_acctschema_id"])) { _m_Product_ID = Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["m_product_id"]); _ad_Org_ID = Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["ad_org_id"]); _m_Attributesetinstance_ID = Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["m_attributesetinstance_id"]); _c_AcctSchema_ID = Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["c_acctschema_id"]); MProduct product = new MProduct(GetCtx(), _m_Product_ID, Get_TrxName()); MAcctSchema acctSchema = new MAcctSchema(GetCtx(), _c_AcctSchema_ID, Get_TrxName()); costcombination = MCost.Get(product, _m_Attributesetinstance_ID, acctSchema, _ad_Org_ID, Util.GetValueOfInt(dsCostCombination.Tables[0].Rows[0]["M_CostElement_ID"])); } // created object of Cost elemnt for checking iscalculated = true/ false ce = MCostElement.Get(GetCtx(), Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["m_costelement_id"])); costcombination.SetCurrentCostPrice(Decimal.Add(costcombination.GetCurrentCostPrice(), Util.GetValueOfDecimal(dsProductCost.Tables[0].Rows[i]["currentcostprice"]))); costcombination.SetCumulatedAmt(Decimal.Add(costcombination.GetCumulatedAmt(), Util.GetValueOfDecimal(dsProductCost.Tables[0].Rows[i]["cumulatedamt"]))); // if calculated = true then we added qty else not and costing method is Standard Costing if (ce.IsCalculated() || ce.GetCostingMethod() == MCostElement.COSTINGMETHOD_StandardCosting) { costcombination.SetCurrentQty(Decimal.Add(costcombination.GetCurrentQty(), Util.GetValueOfDecimal(dsProductCost.Tables[0].Rows[i]["currentqty"]))); costcombination.SetCumulatedQty(Decimal.Add(costcombination.GetCumulatedQty(), Util.GetValueOfDecimal(dsProductCost.Tables[0].Rows[i]["cumulatedqty"]))); } if (costcombination.Save()) { Commit(); } else { log.Info("Cost Combination not updated for this product <===> " + Util.GetValueOfInt(dsProductCost.Tables[0].Rows[i]["m_product_id"])); } } dsProductCost.Dispose(); } } catch { if (dsProductCost != null) { dsProductCost.Dispose(); } if (dsCostCombination != null) { dsCostCombination.Dispose(); } } return Msg.GetMsg(GetCtx(), "SucessfullyUpdated"); }