/// <summary> /// is used to save data in case of Full move container / full qty move /// </summary> /// <param name="movementId">movement header refernce</param> /// <param name="fromLocatorId">From Locator - from where we have to move product</param> /// <param name="fromContainerId">From Container - from which container, we have to move product</param> /// <param name="toLocatorId">To Locator - where we are moving product</param> /// <param name="toContainerId">To container - in which container we are moving product</param> /// <param name="lineNo"></param> /// <param name="isMoveFullContainerQty">Is Container also move with Product</param> /// <param name="trx">Self created Trx</param> /// <returns>Message : lines inserted or not</returns> public String SaveMoveLinewithFullContainer(int movementId, int fromLocatorId, int fromContainerId, int toLocatorId, int toContainerId, int lineNo, bool isMoveFullContainerQty, Trx trx) { MMovement movement = new MMovement(_ctx, movementId, trx); string childContainerId = null; StringBuilder error = new StringBuilder(); // Get Path upto selected container string sql = @"SELECT sys_connect_by_path(m_productcontainer_id,'->') tree FROM m_productcontainer WHERE m_productcontainer_id = " + fromContainerId + @" START WITH ref_m_container_id IS NULL CONNECT BY prior m_productcontainer_id = ref_m_container_id ORDER BY tree "; string pathContainer = Util.GetValueOfString(DB.ExecuteScalar(sql, null, trx)); // child records with Parent Id if (!isMoveFullContainerQty) { sql = @"SELECT tree, m_productcontainer_id FROM (SELECT sys_connect_by_path(m_productcontainer_id,'->') tree , m_productcontainer_id FROM m_productcontainer START WITH ref_m_container_id IS NULL CONNECT BY prior m_productcontainer_id = ref_m_container_id ORDER BY tree ) WHERE tree LIKE ('" + pathContainer + "%') "; DataSet ds = DB.ExecuteDataset(sql, null, trx); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (String.IsNullOrEmpty(childContainerId)) { childContainerId = Util.GetValueOfString(ds.Tables[0].Rows[i]["m_productcontainer_id"]); } else { childContainerId += "," + Util.GetValueOfString(ds.Tables[0].Rows[i]["m_productcontainer_id"]); } } } ds.Dispose(); } else { childContainerId = fromContainerId.ToString(); } // check is same container already moved to another container // Ex :: p1 -> c1 and p1 -> c2 // OR also check -- is any other container is moving into exist target container // Ex :: p1 -> c1 and p2 -> p1 if (!isMoveFullContainerQty) { if (!IsContainerMoved(movementId, pathContainer, childContainerId, toContainerId, trx)) { return(Msg.GetMsg(_ctx, "VIS_AlreadyMoved")); } } // not to move Parent container to its child container if (toContainerId > 0 && childContainerId.Contains(toContainerId.ToString())) { //Parent cant be Move to its own child return(Msg.GetMsg(_ctx, "VIS_cantMoveParentTochild")); } // Get All records of Parent Container and child container sql = @"SELECT * FROM ( SELECT p.M_PRODUCT_ID, p.NAME, p.C_UOM_ID, u.Name AS UomName, t.M_ATTRIBUTESETINSTANCE_ID, t.M_ProductContainer_ID, SUM(t.ContainerCurrentQty) keep (dense_rank last ORDER BY t.MovementDate, t.M_Transaction_ID) AS ContainerCurrentQty FROM M_Transaction t INNER JOIN M_Product p ON p.M_Product_ID = t.M_Product_ID INNER JOIN C_UOM u ON u.C_UOM_ID = p.C_UOM_ID WHERE t.IsActive = 'Y' AND NVL(t.M_ProductContainer_ID, 0) IN ( " + childContainerId + @" ) AND t.MovementDate <=" + GlobalVariable.TO_DATE(movement.GetMovementDate(), true) + @" AND t.M_Locator_ID = " + fromLocatorId + @" AND t.AD_Client_ID = " + movement.GetAD_Client_ID() + @" GROUP BY p.M_PRODUCT_ID, p.NAME, p.C_UOM_ID, u.Name, t.M_ATTRIBUTESETINSTANCE_ID, t.M_ProductContainer_ID ) t WHERE ContainerCurrentQty <> 0 "; DataSet dsRecords = DB.ExecuteDataset(sql, null, trx); if (dsRecords != null && dsRecords.Tables.Count > 0 && dsRecords.Tables[0].Rows.Count > 0) { int movementlineId = 0; MMovementLine moveline = null; MProduct product = null; for (int i = 0; i < dsRecords.Tables[0].Rows.Count; i++) { movementlineId = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(M_MovementLine_ID, 0) AS M_Movement_ID FROM M_MovementLine WHERE M_Movement_ID = " + Util.GetValueOfInt(movementId) + @" AND M_Product_ID = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"]) + @" AND NVL(M_AttributeSetInstance_ID, 0) = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]) + @" AND M_Locator_ID = " + Util.GetValueOfInt(fromLocatorId) + @" AND NVL(M_ProductContainer_ID, 0) = " + Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"]) + @" AND M_LocatorTo_ID = " + Util.GetValueOfInt(toLocatorId) + @" AND NVL(Ref_M_ProductContainerTo_ID, 0) = " + toContainerId + @" AND AD_Org_ID = " + movement.GetAD_Org_ID())); if (movementlineId > 0) { moveline = new MMovementLine(_ctx, movementlineId, trx); } else { moveline = new MMovementLine(_ctx, 0, trx); } if (movementlineId == 0) { #region Create new record of movement line lineNo += 10; moveline.SetAD_Client_ID(movement.GetAD_Client_ID()); moveline.SetAD_Org_ID(movement.GetAD_Org_ID()); moveline.SetM_Movement_ID(movement.GetM_Movement_ID()); moveline.SetLine(lineNo); moveline.SetM_Product_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"])); moveline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_AttributeSetInstance_ID"])); moveline.SetC_UOM_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["C_UOM_ID"])); moveline.SetM_Locator_ID(fromLocatorId); moveline.SetM_LocatorTo_ID(toLocatorId); moveline.SetM_ProductContainer_ID(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"])); moveline.SetRef_M_ProductContainerTo_ID(toContainerId); moveline.SetQtyEntered(Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"])); moveline.SetMovementQty(Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"])); moveline.SetMoveFullContainer(isMoveFullContainerQty ? false : true); // when move full container, then only need to update IsParentMove and Target container (which represent - to which container we are moving) // and set true value on those line which container are moving, not on its child container if (!isMoveFullContainerQty) { moveline.SetIsParentMove(Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_ProductContainer_ID"]) == fromContainerId ? true : false); moveline.SetTargetContainer_ID(fromContainerId); } #endregion } else { #region Update record of movement line moveline.SetQtyEntered(Decimal.Add(moveline.GetQtyEntered(), Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"]))); moveline.SetMovementQty(Decimal.Add(moveline.GetMovementQty(), Util.GetValueOfDecimal(dsRecords.Tables[0].Rows[i]["ContainerCurrentQty"]))); moveline.SetMoveFullContainer(true); #endregion } if (!moveline.Save(trx)) { #region Save error catch and rollback product = MProduct.Get(_ctx, Util.GetValueOfInt(dsRecords.Tables[0].Rows[i]["M_Product_ID"])); ValueNamePair pp = VLogger.RetrieveError(); if (pp != null) { _log.SaveError("Movement line not inserted through Move Container Form : ", pp.GetName()); if (String.IsNullOrEmpty(error.ToString())) { error.Append(Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName()); } else { error.Append(" , " + Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName()); } } trx.Rollback(); #endregion } else { trx.Commit(); } } } else { return(Msg.GetMsg(_ctx, "VIS_ContainerhaveNoRecord")); } return(String.IsNullOrEmpty(error.ToString()) ? "VIS_SuccessFullyInserted" : error.ToString()); }
/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { log.Info("M_Movement_ID=" + _m_Movement_ID + ", M_Locator_ID=" + _m_Locator_ID + ", M_LocatorTo_ID=" + _m_LocatorTo_ID + ", M_Product_ID=" + _m_product_ID + ", M_Product_Category_ID=" + _m_Product_Category_ID + ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld); _movement = new MMovement(GetCtx(), _m_Movement_ID, Get_Trx()); if (_movement.Get_ID() == 0) { throw new SystemException("Not found: M_Movement_ID=" + _m_Movement_ID); } if (_movement.IsProcessed()) { throw new SystemException("@M_Movement_ID@ @Processed@"); } // is used to check Container applicable into system isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx()); // String sqlQry = ""; if (_deleteOld) { sqlQry = "DELETE FROM M_MovementLine WHERE Processed='N' " + "AND M_Movement_ID=" + _m_Movement_ID; int no = DB.ExecuteQuery(sqlQry, null, Get_Trx()); log.Fine("doIt - Deleted #" + no); } // Create Null Storage records if (_qtyRange != null && _qtyRange.Equals("=")) { sqlQry = "INSERT INTO M_Storage " + "(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID," + " qtyOnHand, QtyReserved, QtyOrdered, DateLastInventory) " + "SELECT l.AD_CLIENT_ID, l.AD_ORG_ID, 'Y', SysDate, 0,SysDate, 0," + " l.M_Locator_ID, p.M_Product_ID, 0," + " 0,0,0,null " + "FROM M_Locator l" + " INNER JOIN M_Product p ON (l.AD_Client_ID=p.AD_Client_ID) " + "WHERE l.M_Warehouse_ID=" + _movement.GetM_Warehouse_ID(); if (_m_Locator_ID != 0) { sqlQry += " AND l.M_Locator_ID=" + _m_Locator_ID; } sqlQry += " AND l.IsDefault='Y'" + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'" + " AND NOT EXISTS (SELECT * FROM M_Storage s" + " INNER JOIN M_Locator sl ON (s.M_Locator_ID=sl.M_Locator_ID) " + "WHERE sl.M_Warehouse_ID=l.M_Warehouse_ID" + " AND s.M_Product_ID=p.M_Product_ID)"; int no = DB.ExecuteQuery(sqlQry, null, Get_Trx()); log.Fine("'0' Inserted #" + no); } StringBuilder sql = null; if (!isContainerApplicable) { sql = new StringBuilder( "SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID," + " s.qtyOnHand, p.M_AttributeSet_ID, 0 AS M_ProductContainer_ID " + "FROM M_Product p" + " INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID)" + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) " + "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"); } else { sql = new StringBuilder( "SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID," + " NVL(SUM(s.Qty) , 0) AS qtyOnHand , p.M_AttributeSet_ID, s.M_ProductContainer_ID " + "FROM M_Product p" + " INNER JOIN M_ContainerStorage s ON (s.M_Product_ID=p.M_Product_ID)" + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) " + "WHERE l.M_Warehouse_ID=" + _movement.GetDTD001_MWarehouseSource_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"); } // if (_m_Locator_ID != 0) { sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID); } // if (_m_product_ID != null && !string.IsNullOrEmpty(_m_product_ID)) { sql.Append(" AND p.M_Product_ID IN ( " + _m_product_ID + ") "); } // if (_m_Product_Category_ID != 0) { sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID); } // Do not overwrite existing records if (!_deleteOld) { sql.Append(" AND NOT EXISTS (SELECT * FROM M_MovementLine il " + "WHERE il.M_Movement_ID=" + _m_Movement_ID + " AND il.M_Product_ID=s.M_Product_ID" + " AND il.M_Locator_ID=s.M_Locator_ID" + " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0)"); if (!isContainerApplicable) { sql.Append(@" ) "); } else { sql.Append(@" AND COALESCE(il.M_ProductContainer_ID,0)=COALESCE(s.M_ProductContainer_ID,0) ) "); } } // if (!isContainerApplicable) { sql.Append(" ORDER BY l.Value, p.Value, s.qtyOnHand DESC"); // Locator/Product } else { sql.Append(@" GROUP BY s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID, s.M_ProductContainer_ID, s.Qty ORDER BY s.M_Locator_ID, s.M_Product_ID, s.Qty DESC, s.M_AttributeSetInstance_ID, p.M_AttributeSet_ID,s.M_ProductContainer_ID"); // Locator/Product } // int count = 0; IDataReader idr = null; MProduct product = null; try { idr = DB.ExecuteReader(sql.ToString(), null, Get_Trx()); while (idr.Read()) { int M_Product_ID = Util.GetValueOfInt(idr[0]); product = MProduct.Get(GetCtx(), M_Product_ID); int M_Locator_ID = Util.GetValueOfInt(idr[1]); int M_AttributeSetInstance_ID = Util.GetValueOfInt(idr[2]); Decimal qtyOnHand = Util.GetValueOfDecimal(idr[3]); //if (qtyOnHand == null) commented by manjot Because Decimal is Never equals to Null // qtyOnHand = Env.ZERO; int M_AttributeSet_ID = Util.GetValueOfInt(idr[4]); //container int container_Id = Util.GetValueOfInt(idr[5]); // int compare = qtyOnHand.CompareTo(Env.ZERO); if (_qtyRange == null || (_qtyRange.Equals(">") && compare > 0) || (_qtyRange.Equals("<") && compare < 0) || (_qtyRange.Equals("=") && compare == 0) || (_qtyRange.Equals("N") && compare != 0)) { //Save data on Movement Line _line = new MMovementLine(GetCtx(), 0, Get_Trx()); _line.SetAD_Client_ID(_movement.GetAD_Client_ID()); _line.SetAD_Org_ID(_movement.GetAD_Org_ID()); _line.SetM_Movement_ID(_m_Movement_ID); _line.SetM_Locator_ID(_m_Locator_ID); _line.SetM_LocatorTo_ID(_m_LocatorTo_ID); _line.SetMovementQty(qtyOnHand); _line.SetProcessed(false); _line.SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID); _line.SetM_Product_ID(M_Product_ID); if (isContainerApplicable && _line.Get_ColumnIndex("M_ProductContainer_ID") > 0) { _line.SetM_ProductContainer_ID(container_Id); } if (_line.Get_ColumnIndex("C_UOM_ID") > 0 && product != null) { _line.SetC_UOM_ID(product.GetC_UOM_ID()); } if (_line.Get_ColumnIndex("QtyEntered") > 0) { _line.SetQtyEntered(qtyOnHand); } if (!_line.Save()) { return(GetRetrievedError(_line, "Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID)); //log.Info("Movement Line Not Created for M_Product_ID = " + M_Product_ID + " M_AttributeSetInstance = " + M_AttributeSetInstance_ID); } else { count = count + 1; } } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql.ToString(), e); } // return("@M_MovementLine_ID@ - #" + count); }
/// <summary> /// Is used to save data on movememt line /// </summary> /// <param name="mData"></param> /// <returns></returns> public String SaveMovementLine(List <Dictionary <string, string> > mData) { StringBuilder error = new StringBuilder(); bool isMoveFullContainer = false; bool isMoveFullContainerQty = false; Trx trx = Trx.GetTrx("Movement"); if (mData.Count > 0) { isMoveFullContainer = Util.GetValueOfBool(mData[0]["IsFullMoveContainer"]); //to delete all the movement lines where MoveFullContainer is False if (isMoveFullContainer) { DB.ExecuteQuery("DELETE FROM M_MovementLine WHERE M_Movement_ID = " + Util.GetValueOfInt(mData[0]["M_Movement_ID"]) + " AND MoveFullContainer= 'N' ", null, null); } // Lines not inserted, as movement line already has a full move container line. if (!isMoveFullContainer) { if (Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT COUNT(MoveFullContainer) FROM M_MovementLine WHERE MoveFullContainer= 'Y' AND M_Movement_ID = " + Util.GetValueOfInt(mData[0]["M_Movement_ID"]), null, trx)) > 0) { trx.Close(); return(Msg.GetMsg(_ctx, "VIS_LinehaveFullContainer")); } } // Get Line No int lineNo = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(MAX(Line),0) AS DefaultValue FROM M_MovementLine WHERE M_Movement_ID=" + Util.GetValueOfInt(mData[0]["M_Movement_ID"]), null, trx)); isMoveFullContainerQty = Util.GetValueOfBool(mData[0]["IsfullContainerQtyWise"]); if (!isMoveFullContainer && !isMoveFullContainerQty) { goto moveFullContainer; } else if (isMoveFullContainer || isMoveFullContainerQty) { error.Clear(); error.Append(SaveMoveLinewithFullContainer(Util.GetValueOfInt(mData[0]["M_Movement_ID"]), Util.GetValueOfInt(mData[0]["FromLocator"]), Util.GetValueOfInt(mData[0]["FromContainer"]), Util.GetValueOfInt(mData[0]["ToLocator"]), Util.GetValueOfInt(mData[0]["ToContainer"]), lineNo, isMoveFullContainerQty, trx)); trx.Close(); return(error.ToString()); } moveFullContainer: MMovementLine moveline = null; MProduct product = null; int moveId = 0; for (int i = 0; i < mData.Count; i++) { #region Quantity Only MMovement move = new MMovement(_ctx, Util.GetValueOfInt(mData[i]["M_Movement_ID"]), null); moveId = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT NVL(M_MovementLine_ID, 0) AS M_Movement_ID FROM M_MovementLine WHERE M_Movement_ID = " + Util.GetValueOfInt(mData[i]["M_Movement_ID"]) + @" AND M_Product_ID = " + Util.GetValueOfInt(mData[i]["M_Product_ID"]) + @" AND NVL(M_AttributeSetInstance_ID, 0) = " + Util.GetValueOfInt(mData[i]["M_AttributeSetInstance_ID"]) + @" AND M_Locator_ID = " + Util.GetValueOfInt(mData[i]["FromLocator"]) + @" AND NVL(M_ProductContainer_ID, 0) = " + Util.GetValueOfInt(mData[i]["FromContainer"]) + @" AND M_LocatorTo_ID = " + Util.GetValueOfInt(mData[i]["ToLocator"]) + @" AND NVL(Ref_M_ProductContainerTo_ID, 0) = " + Util.GetValueOfInt(mData[i]["ToContainer"]) + @" AND AD_Org_ID = " + move.GetAD_Org_ID())); if (moveId > 0) { moveline = new MMovementLine(_ctx, moveId, trx); } else { moveline = new MMovementLine(_ctx, 0, trx); } if (moveId == 0) { #region Create new record of movement line lineNo += 10; moveline.SetAD_Client_ID(move.GetAD_Client_ID()); moveline.SetAD_Org_ID(move.GetAD_Org_ID()); moveline.SetM_Movement_ID(move.GetM_Movement_ID()); moveline.SetLine(lineNo); moveline.SetM_Product_ID(Util.GetValueOfInt(mData[i]["M_Product_ID"])); moveline.SetM_AttributeSetInstance_ID(Util.GetValueOfInt(mData[i]["M_AttributeSetInstance_ID"])); moveline.SetC_UOM_ID(Util.GetValueOfInt(mData[i]["C_UOM_ID"])); moveline.SetM_Locator_ID(Util.GetValueOfInt(mData[i]["FromLocator"])); moveline.SetM_LocatorTo_ID(Util.GetValueOfInt(mData[i]["ToLocator"])); moveline.SetM_ProductContainer_ID(Util.GetValueOfInt(mData[i]["FromContainer"])); moveline.SetRef_M_ProductContainerTo_ID(Util.GetValueOfInt(mData[i]["ToContainer"])); moveline.SetQtyEntered(Util.GetValueOfDecimal(mData[i]["MoveQty"])); moveline.SetMovementQty(Util.GetValueOfDecimal(mData[i]["MoveQty"])); moveline.SetMoveFullContainer(Util.GetValueOfBool(mData[i]["IsFullMoveContainer"])); #endregion } else { #region Update record of movement line moveline.SetQtyEntered(Decimal.Add(moveline.GetQtyEntered(), Util.GetValueOfDecimal(mData[i]["MoveQty"]))); moveline.SetMovementQty(Decimal.Add(moveline.GetMovementQty(), Util.GetValueOfDecimal(mData[i]["MoveQty"]))); moveline.SetMoveFullContainer(Util.GetValueOfBool(mData[i]["IsFullMoveContainer"])); #endregion } if (!moveline.Save(trx)) { #region Save error catch and rollback product = MProduct.Get(_ctx, Util.GetValueOfInt(mData[i]["M_Product_ID"])); ValueNamePair pp = VLogger.RetrieveError(); if (pp != null) { _log.SaveError("Movement line not inserted through Move Container Form : ", pp.GetName()); if (String.IsNullOrEmpty(error.ToString())) { error.Append(Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName()); } else { error.Append(" , " + Msg.GetMsg(_ctx, "VIS_MoveLineNotSaveFor") + product.GetName() + Msg.GetMsg(_ctx, "VIS_DueTo") + pp.GetName()); } } trx.Rollback(); #endregion } else { trx.Commit(); } #endregion } } trx.Close(); return(String.IsNullOrEmpty(error.ToString()) ? "VIS_SuccessFullyInserted" : error.ToString()); }
/// <summary> /// Create Facts (the accounting logic) for /// MXP. /// <pre> /// Product PPV <difference> /// PPV_Offset <difference> /// </pre> /// </summary> /// <param name="as1"></param> /// <returns></returns> public override List <Fact> CreateFacts(MAcctSchema as1) { List <Fact> facts = new List <Fact>(); // if (GetM_Product_ID() == 0 || // Nothing to do if no Product Env.Signum(Utility.Util.GetValueOfDecimal(GetQty())) == 0 || _M_InOutLine_ID == 0) // No posting if not matched to Shipment { log.Fine("No Product/Qty - M_Product_ID=" + GetM_Product_ID() + ",Qty=" + GetQty()); return(facts); } // create Fact Header Fact fact = new Fact(this, as1, Fact.POST_Actual); SetC_Currency_ID(as1.GetC_Currency_ID()); // Purchase Order Line Decimal poCost = _oLine.GetPriceCost(); if (Env.Signum(poCost) == 0) { poCost = _oLine.GetPriceActual(); } poCost = Decimal.Multiply(poCost, Utility.Util.GetValueOfDecimal(GetQty())); // Delivered so far // Different currency if (_oLine.GetC_Currency_ID() != as1.GetC_Currency_ID()) { MOrder order = _oLine.GetParent(); Decimal rate = MConversionRate.GetRate( order.GetC_Currency_ID(), as1.GetC_Currency_ID(), order.GetDateAcct(), order.GetC_ConversionType_ID(), _oLine.GetAD_Client_ID(), _oLine.GetAD_Org_ID()); if (rate.ToString() == null) { _error = "Purchase Order not convertible - " + as1.GetName(); return(null); } poCost = Decimal.Multiply(poCost, rate); if (Env.Scale(poCost) > as1.GetCostingPrecision()) { poCost = Decimal.Round(poCost, as1.GetCostingPrecision(), MidpointRounding.AwayFromZero); } } MOrder order1 = _oLine.GetParent(); bool isReturnTrx = order1.IsReturnTrx(); log.Fine("Temp"); if (!IsPosted()) { // Create PO Cost Detail Record firs MCostDetail.CreateOrder(as1, _oLine.GetAD_Org_ID(), GetM_Product_ID(), _M_AttributeSetInstance_ID, _C_OrderLine_ID, 0, // no cost element isReturnTrx ? Decimal.Negate(poCost) : poCost, isReturnTrx ? Decimal.Negate(Utility.Util.GetValueOfDecimal(GetQty())) : Utility.Util.GetValueOfDecimal(GetQty()), // Delivered _oLine.GetDescription(), GetTrx(), GetRectifyingProcess()); } // Current Costs String costingMethod = as1.GetCostingMethod(); MProduct product = MProduct.Get(GetCtx(), GetM_Product_ID()); MProductCategoryAcct pca = MProductCategoryAcct.Get(GetCtx(), product.GetM_Product_Category_ID(), as1.GetC_AcctSchema_ID(), GetTrx()); if (pca.GetCostingMethod() != null) { costingMethod = pca.GetCostingMethod(); } Decimal?costs = _pc.GetProductCosts(as1, GetAD_Org_ID(), costingMethod, _C_OrderLine_ID, false); // non-zero costs // No Costs yet - no PPV if (costs == null || Env.Signum(Utility.Util.GetValueOfDecimal(costs)) == 0) { _error = "Resubmit - No Costs for " + product.GetName(); log.Log(Level.SEVERE, _error); return(null); } // Difference Decimal difference = Decimal.Subtract(poCost, Utility.Util.GetValueOfDecimal(costs)); /***********************************************************************************/ //05,Sep,2011 //Special Check to restic Price varience posting in case of //AvarageInvoice Selected on product Category.Then Neglact the AverageInvoice Cost try { if ((pca.GetCostingMethod() == "I" || as1.GetCostingMethod() == "I") && as1.IsNotPostPOVariance()) { difference = 0; } } catch (Exception ex) { log.SaveError("AccountSchemaColumnError", ex); } /***********************************************************************************/ // Nothing to post if (Env.Signum(difference) == 0) { log.Log(Level.FINE, "No Cost Difference for M_Product_ID=" + GetM_Product_ID()); facts.Add(fact); return(facts); } // Product PPV FactLine cr = fact.CreateLine(null, _pc.GetAccount(ProductCost.ACCTTYPE_P_PPV, as1), as1.GetC_Currency_ID(), difference); if (cr != null) { cr.SetQty(GetQty()); cr.SetC_BPartner_ID(_oLine.GetC_BPartner_ID()); cr.SetC_Activity_ID(_oLine.GetC_Activity_ID()); cr.SetC_Campaign_ID(_oLine.GetC_Campaign_ID()); cr.SetC_Project_ID(_oLine.GetC_Project_ID()); cr.SetC_UOM_ID(_oLine.GetC_UOM_ID()); cr.SetUser1_ID(_oLine.GetUser1_ID()); cr.SetUser2_ID(_oLine.GetUser2_ID()); } // PPV Offset FactLine dr = fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_PPVOffset, as1), as1.GetC_Currency_ID(), Decimal.Negate(difference)); if (dr != null) { dr.SetQty((Decimal?)Decimal.Negate(Utility.Util.GetValueOfDecimal(GetQty()))); dr.SetC_BPartner_ID(_oLine.GetC_BPartner_ID()); dr.SetC_Activity_ID(_oLine.GetC_Activity_ID()); dr.SetC_Campaign_ID(_oLine.GetC_Campaign_ID()); dr.SetC_Project_ID(_oLine.GetC_Project_ID()); dr.SetC_UOM_ID(_oLine.GetC_UOM_ID()); dr.SetUser1_ID(_oLine.GetUser1_ID()); dr.SetUser2_ID(_oLine.GetUser2_ID()); } // facts.Add(fact); return(facts); }
// Added by mohit to get product UOM- 12 June 2018 /// <summary> /// Get product UOM /// </summary> /// <param name="ctx"></param> /// <param name="field"></param> /// <returns></returns> public string GetproductUOM(Ctx ctx, string field) { MProduct Product = new MProduct(ctx, Util.GetValueOfInt(field), null); return(Product.GetC_UOM_ID().ToString()); }
} // prepare /// <summary> /// Perrform Process. /// </summary> /// <returns>Info</returns> protected override String DoIt() { log.Info("M_Locator_ID=" + _M_Locator_ID + ",MovementDate=" + _MovementDate); // StringBuilder sql = null; int no = 0; String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID; // **** Prepare **** // Delete Old Imported if (_DeleteOldImported) { sql = new StringBuilder("DELETE FROM I_Inventory " + "WHERE I_IsImported='Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Delete Old Impored =" + no); } // Set Client, Org, Location, IsActive, Created/Updated sql = new StringBuilder("UPDATE I_Inventory " + "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append(")," + " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append("),"); if (_MovementDate != null) { sql.Append(" MovementDate = COALESCE (MovementDate,").Append(DataBase.DB.TO_DATE(_MovementDate)).Append("),"); } sql.Append(" IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = NULL," + " M_Warehouse_ID = NULL," // reset + " I_IsImported = 'N' " + "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL"); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Info("Reset=" + no); String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly sql = new StringBuilder("UPDATE I_Inventory o " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '" + "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0" + " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Org=" + no); } // gwu: bug 1703137 // if Warehouse key provided, get Warehouse ID sql = new StringBuilder("UPDATE I_Inventory i " + "SET M_Warehouse_ID=(SELECT MAX(M_Warehouse_ID) FROM M_Warehouse w" + " WHERE i.WarehouseValue=w.Value AND i.AD_Client_ID=w.AD_Client_ID) " + "WHERE M_Warehouse_ID IS NULL AND WarehouseValue IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Warehouse from Value =" + no); // Location sql = new StringBuilder("UPDATE I_Inventory i " + "SET M_Locator_ID=(SELECT MAX(M_Locator_ID) FROM M_Locator l" + " WHERE i.LocatorValue=l.Value AND COALESCE (i.M_Warehouse_ID, l.M_Warehouse_ID)=l.M_Warehouse_ID AND i.AD_Client_ID=l.AD_Client_ID) " + "WHERE M_Locator_ID IS NULL AND LocatorValue IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Locator from Value =" + no); sql = new StringBuilder("UPDATE I_Inventory i " + "SET M_Locator_ID=(SELECT MAX(M_Locator_ID) FROM M_Locator l" + " WHERE i.X=l.X AND i.Y=l.Y AND i.Z=l.Z AND COALESCE (i.M_Warehouse_ID, l.M_Warehouse_ID)=l.M_Warehouse_ID AND i.AD_Client_ID=l.AD_Client_ID) " + "WHERE M_Locator_ID IS NULL AND X IS NOT NULL AND Y IS NOT NULL AND Z IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Locator from X,Y,Z =" + no); if (_M_Locator_ID != 0) { sql = new StringBuilder("UPDATE I_Inventory " + "SET M_Locator_ID = ").Append(_M_Locator_ID).Append( " WHERE M_Locator_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Locator from Parameter=" + no); } sql = new StringBuilder("UPDATE I_Inventory " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Location, ' " + "WHERE M_Locator_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No Location=" + no); } // Set M_Warehouse_ID sql = new StringBuilder("UPDATE I_Inventory i " + "SET M_Warehouse_ID=(SELECT M_Warehouse_ID FROM M_Locator l WHERE i.M_Locator_ID=l.M_Locator_ID) " + "WHERE M_Locator_ID IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Warehouse from Locator =" + no); sql = new StringBuilder("UPDATE I_Inventory " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Warehouse, ' " + "WHERE M_Warehouse_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No Warehouse=" + no); } // Product sql = new StringBuilder("UPDATE I_Inventory i " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE i.Value=p.Value AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND Value IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Product from Value=" + no); sql = new StringBuilder("UPDATE I_Inventory i " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE i.UPC=p.UPC AND i.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Product from UPC=" + no); sql = new StringBuilder("UPDATE I_Inventory " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Product, ' " + "WHERE M_Product_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No Product=" + no); } // No QtyCount sql = new StringBuilder("UPDATE I_Inventory " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Qty Count, ' " + "WHERE QtyCount IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No QtyCount=" + no); } Commit(); /*********************************************************************/ MInventory inventory = null; int noInsert = 0; int noInsertLine = 0; // Go through Inventory Records sql = new StringBuilder("SELECT * FROM I_Inventory " + "WHERE I_IsImported='N'").Append(clientCheck) .Append(" ORDER BY M_Warehouse_ID, TRUNC(MovementDate,'DD'), I_Inventory_ID"); IDataReader idr = null; try { //PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString (), Get_TrxName()); //ResultSet rs = pstmt.executeQuery (); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); // int x_M_Warehouse_ID = -1; DateTime?x_MovementDate = null; while (idr.Read()) { X_I_Inventory imp = new X_I_Inventory(GetCtx(), idr, Get_TrxName()); DateTime? MovementDate = TimeUtil.GetDay(imp.GetMovementDate()); if (inventory == null || imp.GetM_Warehouse_ID() != x_M_Warehouse_ID || !MovementDate.Equals(x_MovementDate)) { inventory = new MInventory(GetCtx(), 0, Get_TrxName()); inventory.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID()); inventory.SetDescription("I " + imp.GetM_Warehouse_ID() + " " + MovementDate); inventory.SetM_Warehouse_ID(imp.GetM_Warehouse_ID()); inventory.SetMovementDate(MovementDate); // if (!inventory.Save()) { log.Log(Level.SEVERE, "Inventory not saved"); break; } x_M_Warehouse_ID = imp.GetM_Warehouse_ID(); x_MovementDate = MovementDate; noInsert++; } // Line int M_AttributeSetInstance_ID = 0; if (imp.GetLot() != null || imp.GetSerNo() != null) { MProduct product = MProduct.Get(GetCtx(), imp.GetM_Product_ID()); if (product.IsInstanceAttribute()) { MAttributeSet mas = product.GetAttributeSet(); MAttributeSetInstance masi = new MAttributeSetInstance(GetCtx(), 0, mas.GetM_AttributeSet_ID(), Get_TrxName()); if (mas.IsLot() && imp.GetLot() != null) { masi.SetLot(imp.GetLot(), imp.GetM_Product_ID()); } if (mas.IsSerNo() && imp.GetSerNo() != null) { masi.SetSerNo(imp.GetSerNo()); } masi.SetDescription(); masi.Save(); M_AttributeSetInstance_ID = masi.GetM_AttributeSetInstance_ID(); } } MInventoryLine line = new MInventoryLine(inventory, imp.GetM_Locator_ID(), imp.GetM_Product_ID(), M_AttributeSetInstance_ID, imp.GetQtyBook(), imp.GetQtyCount()); if (line.Save()) { imp.SetI_IsImported(X_I_Inventory.I_ISIMPORTED_Yes); imp.SetM_Inventory_ID(line.GetM_Inventory_ID()); imp.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID()); imp.SetProcessed(true); if (imp.Save()) { noInsertLine++; } } } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, sql.ToString(), e); } // Set Error to indicator to not imported sql = new StringBuilder("UPDATE I_Inventory " + "SET I_IsImported='N', Updated=SysDate " + "WHERE I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@"); // AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@M_Inventory_ID@: @Inserted@"); AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@M_InventoryLine_ID@: @Inserted@"); return(""); } // doIt
/// <summary> /// GetProductPricing /// </summary> /// <param name="ctx"></param> /// <param name="fields"></param> /// <returns></returns> public ProductDataOut GetProductPricing(Ctx ctx, string fields) { string[] paramValue = fields.Split(','); int M_Product_ID, C_BPartner_ID, M_PriceList_ID, M_PriceList_Version_ID, M_AttributeSetInstance_ID = 0, countED011 = 0, C_UOM_ID = 0; decimal Qty; bool isSOTrx; //Assign parameter value M_Product_ID = Util.GetValueOfInt(paramValue[0].ToString()); C_BPartner_ID = Util.GetValueOfInt(paramValue[1].ToString()); Qty = Util.GetValueOfDecimal(paramValue[2].ToString()); isSOTrx = Convert.ToBoolean(paramValue[3]); M_PriceList_ID = Util.GetValueOfInt(paramValue[4].ToString()); M_PriceList_Version_ID = Util.GetValueOfInt(paramValue[5].ToString()); DateTime?orderDate = Util.GetValueOfDateTime(paramValue[6]); DateTime?orderDate1 = Util.GetValueOfDateTime(paramValue[7]); //if (paramValue.Length > 8) if (paramValue.Length == 9 || paramValue.Length == 11) { M_AttributeSetInstance_ID = Util.GetValueOfInt(paramValue[8].ToString()); } if (paramValue.Length > 9) { if (paramValue.Length == 11) { C_UOM_ID = Util.GetValueOfInt(paramValue[9].ToString()); countED011 = Util.GetValueOfInt(paramValue[10].ToString()); } else if (paramValue.Length == 10) { C_UOM_ID = Util.GetValueOfInt(paramValue[8].ToString()); countED011 = Util.GetValueOfInt(paramValue[9].ToString()); } } /** Price List - ValidFrom date validation ** Dt:01/02/2021 ** Modified By: Kumar **/ if (!string.IsNullOrEmpty(Util.GetValueOfString(orderDate))) { StringBuilder sbparams = new StringBuilder(); sbparams.Append(Util.GetValueOfInt(M_PriceList_ID)); sbparams.Append(",").Append(Convert.ToDateTime(orderDate).ToString("MM-dd-yyyy")); sbparams.Append(",").Append(Util.GetValueOfInt(M_Product_ID)); sbparams.Append(",").Append(Util.GetValueOfInt(C_UOM_ID)); sbparams.Append(",").Append(Util.GetValueOfInt(M_AttributeSetInstance_ID)); MPriceListVersionModel objPriceList = new MPriceListVersionModel(); M_PriceList_Version_ID = objPriceList.GetM_PriceList_Version_ID_On_Transaction_Date(ctx, sbparams.ToString()); } //End Assign parameter value MProductPricing pp = new MProductPricing(ctx.GetAD_Client_ID(), ctx.GetAD_Org_ID(), M_Product_ID, C_BPartner_ID, Qty, isSOTrx); //var M_PriceList_ID = ctx.GetContextAsInt(WindowNo, "M_PriceList_ID"); pp.SetM_PriceList_ID(M_PriceList_ID); /** PLV is only accurate if PL selected in header */ //var M_PriceList_Version_ID = ctx.GetContextAsInt(WindowNo, "M_PriceList_Version_ID"); pp.SetM_PriceList_Version_ID(M_PriceList_Version_ID); //if (paramValue.Length > 8) if (paramValue.Length == 9 || paramValue.Length == 11) { pp.SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID); } //var orderDate = System.Convert.ToDateTime(mTab.getValue("DateOrdered")); pp.SetPriceDate(orderDate); pp.SetPriceDate1(orderDate1); if (countED011 > 0) { pp.SetC_UOM_ID(C_UOM_ID); } //Get product stock MProduct product = MProduct.Get(ctx, M_Product_ID); ProductDataOut objInfo = new ProductDataOut { PriceList = pp.GetPriceList(), PriceLimit = pp.GetPriceLimit(), PriceActual = pp.GetPriceStd(), PriceEntered = pp.GetPriceStd(), PriceStd = pp.GetPriceStd(), LineAmt = pp.GetLineAmt(2), C_Currency_ID = System.Convert.ToInt32(pp.GetC_Currency_ID()), Discount = pp.GetDiscount(), C_UOM_ID = System.Convert.ToInt32(pp.GetC_UOM_ID()), //QtyOrdered= mTab.GetValue("QtyEntered")); EnforcePriceLimit = pp.IsEnforcePriceLimit(), DiscountSchema = pp.IsDiscountSchema(), IsStocked = product.IsStocked() }; product = null; pp = null; return(objInfo); }