/// <summary> /// GetPriceList /// </summary> /// <param name="ctx"></param> /// <param name="fields"></param> /// <returns></returns> public Dictionary <String, String> GetPriceList(Ctx ctx, string fields) { if (fields != null) { string[] paramValue = fields.ToString().Split(','); int M_PriceList_ID; //Assign parameter value M_PriceList_ID = Util.GetValueOfInt(paramValue[0].ToString()); //End Assign parameter value MPriceList prcLst = new MPriceList(ctx, M_PriceList_ID, null); Dictionary <String, String> retDic = new Dictionary <string, string>(); // Reset Orig Shipment MCurrency crncy = new MCurrency(ctx, prcLst.GetC_Currency_ID(), null); //retDic["StdPrecision"] = prcLst.GetPricePrecision().ToString(); //JID_1744 Precision should be as per currency percision retDic["StdPrecision"] = crncy.GetStdPrecision().ToString(); return(retDic); } else { return(null); } }
// Added by Bharat on 12/May/2017 public Dictionary <string, int> GetPriceList(Ctx ctx, string fields) { int M_PriceListVersion_ID = Util.GetValueOfInt(fields); Dictionary <string, int> retDic = new Dictionary <string, int>(); MPriceListVersion ver = new MPriceListVersion(ctx, M_PriceListVersion_ID, null); retDic["M_PriceList_ID"] = ver.GetM_PriceList_ID(); MPriceList list = new MPriceList(ctx, ver.GetM_PriceList_ID(), null); retDic["C_Currency_ID"] = list.GetC_Currency_ID(); return(retDic); }
/// <summary> /// Get the curency of the pricelist /// </summary> /// <param name="fields">Time expense ID</param> /// <returns>Currency ID</returns> public JsonResult GetPriceListCurrency(string fields) { string retJSON = ""; if (Session["ctx"] != null) { VAdvantage.Utility.Ctx ctx = Session["ctx"] as Ctx; MTimeExpense timeExpense = new MTimeExpense(ctx, Util.GetValueOfInt(fields), null); MPriceList priceList = new MPriceList(ctx, timeExpense.GetM_PriceList_ID(), null); retJSON = JsonConvert.SerializeObject(priceList.GetC_Currency_ID()); } return(Json(retJSON, JsonRequestBehavior.AllowGet)); }
/// <summary> /// GetPriceList /// </summary> /// <param name="ctx"></param> /// <param name="fields"></param> /// <returns></returns> public Dictionary <String, String> GetPriceList(Ctx ctx, string fields) { if (fields != null) { string[] paramValue = fields.ToString().Split(','); int M_PriceList_ID; //Assign parameter value M_PriceList_ID = Util.GetValueOfInt(paramValue[0].ToString()); //End Assign parameter value MPriceList prcLst = new MPriceList(ctx, M_PriceList_ID, null); Dictionary <String, String> retDic = new Dictionary <string, string>(); // Reset Orig Shipment retDic["StdPrecision"] = prcLst.GetPricePrecision().ToString(); return(retDic); } else { return(null); } }
} // prepare /// <summary> /// Perrform Process. /// </summary> /// <returns>clear message</returns> protected override String DoIt() { 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_Invoice " + "WHERE I_IsImported='Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Delete Old Impored =" + no); } // Set Client, Org, IsActive, Created/Updated sql = new StringBuilder("UPDATE I_Invoice " + "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(")," + " IsActive = COALESCE (IsActive, 'Y')," + " Created = COALESCE (Created, SysDate)," + " CreatedBy = COALESCE (CreatedBy, 0)," + " Updated = COALESCE (Updated, SysDate)," + " UpdatedBy = COALESCE (UpdatedBy, 0)," + " I_ErrorMsg = NULL," + " 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_Invoice 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); } // Document Type - PO - SO sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('API','APC') AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set PO DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('ARI','ARC') AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set SO DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName" + " AND d.DocBaseType IN ('API','ARI','APC','ARC') AND o.AD_Client_ID=d.AD_Client_ID) " //+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append (clientCheck); + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid DocTypeName, ' " + "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid DocTypeName=" + no); } // DocType Default sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType='API' AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set PO Default DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType='ARI' AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set SO Default DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'" + " AND d.DocBaseType IN('ARI','API') AND o.AD_Client_ID=d.AD_Client_ID) " + "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Fine("Set Default DocType=" + no); } sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No DocType, ' " + "WHERE C_DocType_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No DocType=" + no); } // Set IsSOTrx sql = new StringBuilder("UPDATE I_Invoice o SET IsSOTrx='Y' " + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='ARI' AND o.AD_Client_ID=d.AD_Client_ID)" + " AND C_DocType_ID IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set IsSOTrx=Y=" + no); sql = new StringBuilder("UPDATE I_Invoice o SET IsSOTrx='N' " + "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='API' AND o.AD_Client_ID=d.AD_Client_ID)" + " AND C_DocType_ID IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set IsSOTrx=N=" + no); // Price List sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'" + " AND p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Default Currency PriceList=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'" + " AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Default PriceList=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p " + " WHERE p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Currency PriceList=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p " + " WHERE p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set PriceList=" + no); // sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No PriceList, ' " + "WHERE M_PriceList_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No PriceList=" + no); } // Payment Rule // We support Payment Rule being input in the login language VAdvantage.Login.Language language = VAdvantage.Login.Language.GetLoginLanguage(GetCtx()); // Base Language String AD_Language = language.GetAD_Language(); sql = new StringBuilder("UPDATE I_Invoice O " + "SET PaymentRule= " + "(SELECT R.Value " + " FROM AD_Ref_List R " + " left outer join AD_Ref_List_Trl RT " + " on RT.AD_Ref_List_ID = R.AD_Ref_List_ID and RT.AD_Language = @param " + " WHERE R.AD_Reference_ID = 195 and coalesce( RT.Name, R.Name ) = O.PaymentRuleName ) " + "WHERE PaymentRule is null AND PaymentRuleName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); SqlParameter[] param = new SqlParameter[1]; param[0] = new SqlParameter("@param", AD_Language); no = DataBase.DB.ExecuteQuery(sql.ToString(), param, Get_TrxName()); log.Fine("Set PaymentRule=" + no); // do not set a default; if null, the import logic will derive from the business partner // do not error in absence of a default // Payment Term sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p" + " WHERE o.PaymentTermValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_PaymentTerm_ID IS NULL AND PaymentTermValue IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set PaymentTerm=" + no); sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_PaymentTerm_ID=(SELECT MAX(C_PaymentTerm_ID) FROM C_PaymentTerm p" + " WHERE p.IsDefault='Y' AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE C_PaymentTerm_ID IS NULL AND o.PaymentTermValue IS NULL AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Default PaymentTerm=" + no); // sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No PaymentTerm, ' " + "WHERE C_PaymentTerm_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No PaymentTerm=" + no); } // BP from EMail sql = new StringBuilder("UPDATE I_Invoice o " + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u" + " WHERE o.EMail=u.EMail AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) " + "WHERE C_BPartner_ID IS NULL AND EMail IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set BP from EMail=" + no); // BP from ContactName sql = new StringBuilder("UPDATE I_Invoice o " + "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u" + " WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) " + "WHERE C_BPartner_ID IS NULL AND ContactName IS NOT NULL" + " AND EXISTS (SELECT Name FROM AD_User u WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL GROUP BY Name HAVING COUNT(*)=1)" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set BP from ContactName=" + no); // BP from Value sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_BPartner bp" + " WHERE o.BPartnerValue=bp.Value AND o.AD_Client_ID=bp.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set BP from Value=" + no); // Default BP sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_BPartner_ID=(SELECT C_BPartnerCashTrx_ID FROM AD_ClientInfo c" + " WHERE o.AD_Client_ID=c.AD_Client_ID) " + "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NULL AND Name IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Default BP=" + no); // Existing Location ? Exact Match sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID" + " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)" + " WHERE o.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=o.AD_Client_ID" + " AND DUMP(o.Address1)=DUMP(l.Address1) AND DUMP(o.Address2)=DUMP(l.Address2)" + " AND DUMP(o.City)=DUMP(l.City) AND DUMP(o.Postal)=DUMP(l.Postal)" + " AND DUMP(o.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(o.C_Country_ID)=DUMP(l.C_Country_ID)) " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported='N'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Found Location=" + no); // Set Location from BPartner sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_BPartner_Location_ID=(SELECT MAX(C_BPartner_Location_ID) FROM C_BPartner_Location l" + " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID" + " AND ((l.IsBillTo='Y' AND o.IsSOTrx='Y') OR o.IsSOTrx='N')" + ") " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set BP Location from BP=" + no); // sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BP Location, ' " + "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No BP Location=" + no); } // Set Country /** * sql = new StringBuilder ("UPDATE I_Invoice o " + "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'" + " AND c.AD_Client_ID IN (0, o.AD_Client_ID) AND ROWNUM=1) " + "WHERE C_BPartner_ID IS NULL AND CountryCode IS NULL AND C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").Append (clientCheck); + no = DataBase.DB.ExecuteQuery(sql.ToString(),null, Get_TrxName()); + log.Fine("Set Country Default=" + no); **/ sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c" + " WHERE o.CountryCode=c.CountryCode AND c.IsSummary='N' AND c.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL AND CountryCode IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Country=" + no); // sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Country, ' " + "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Country=" + no); } // Set Region sql = new StringBuilder("UPDATE I_Invoice o " + "Set RegionName=(SELECT MAX(Name) FROM C_Region r" + " WHERE r.IsDefault='Y' AND r.C_Country_ID=o.C_Country_ID" + " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Region Default=" + no); // sql = new StringBuilder("UPDATE I_Invoice o " + "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r" + " WHERE r.Name=o.RegionName AND r.C_Country_ID=o.C_Country_ID" + " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Region=" + no); // sql = new StringBuilder("UPDATE I_Invoice o " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Region, ' " + "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL " + " AND EXISTS (SELECT * FROM C_Country c" + " WHERE c.C_Country_ID=o.C_Country_ID AND c.HasRegion='Y')" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Region=" + no); } // Product sql = new StringBuilder("UPDATE I_Invoice o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.ProductValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND ProductValue 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_Invoice o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.UPC=p.UPC AND o.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_Invoice o " + "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p" + " WHERE o.SKU=p.SKU AND o.AD_Client_ID=p.AD_Client_ID) " + "WHERE M_Product_ID IS NULL AND SKU IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Product fom SKU=" + no); sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Product, ' " + "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Product=" + no); } // Tax sql = new StringBuilder("UPDATE I_Invoice o " + "SET C_Tax_ID=(SELECT MAX(C_Tax_ID) FROM C_Tax t" + " WHERE o.TaxIndicator=t.TaxIndicator AND o.AD_Client_ID=t.AD_Client_ID) " + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); log.Fine("Set Tax=" + no); sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Tax, ' " + "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("Invalid Tax=" + no); } Commit(); // -- New BPartner --------------------------------------------------- // Go through Invoice Records w/o C_BPartner_ID sql = new StringBuilder("SELECT * FROM I_Invoice " + "WHERE I_IsImported='N' AND C_BPartner_ID IS NULL").Append(clientCheck); IDataReader idr = null; try { //PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString(), Get_TrxName()); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); while (idr.Read()) { X_I_Invoice imp = new X_I_Invoice(GetCtx(), idr, Get_TrxName()); if (imp.GetBPartnerValue() == null) { if (imp.GetEMail() != null) { imp.SetBPartnerValue(imp.GetEMail()); } else if (imp.GetName() != null) { imp.SetBPartnerValue(imp.GetName()); } else { continue; } } if (imp.GetName() == null) { if (imp.GetContactName() != null) { imp.SetName(imp.GetContactName()); } else { imp.SetName(imp.GetBPartnerValue()); } } // BPartner MBPartner bp = MBPartner.Get(GetCtx(), imp.GetBPartnerValue()); if (bp == null) { bp = new MBPartner(GetCtx(), -1, Get_TrxName()); bp.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID()); bp.SetValue(imp.GetBPartnerValue()); bp.SetName(imp.GetName()); if (!bp.Save()) { continue; } } imp.SetC_BPartner_ID(bp.GetC_BPartner_ID()); // BP Location MBPartnerLocation bpl = null; MBPartnerLocation[] bpls = bp.GetLocations(true); for (int i = 0; bpl == null && i < bpls.Length; i++) { if (imp.GetC_BPartner_Location_ID() == bpls[i].GetC_BPartner_Location_ID()) { bpl = bpls[i]; } // Same Location ID else if (imp.GetC_Location_ID() == bpls[i].GetC_Location_ID()) { bpl = bpls[i]; } // Same Location Info else if (imp.GetC_Location_ID() == 0) { MLocation loc = bpl.GetLocation(false); if (loc.Equals(imp.GetC_Country_ID(), imp.GetC_Region_ID(), imp.GetPostal(), "", imp.GetCity(), imp.GetAddress1(), imp.GetAddress2())) { bpl = bpls[i]; } } } if (bpl == null) { // New Location MLocation loc = new MLocation(GetCtx(), 0, Get_TrxName()); loc.SetAddress1(imp.GetAddress1()); loc.SetAddress2(imp.GetAddress2()); loc.SetCity(imp.GetCity()); loc.SetPostal(imp.GetPostal()); if (imp.GetC_Region_ID() != 0) { loc.SetC_Region_ID(imp.GetC_Region_ID()); } loc.SetC_Country_ID(imp.GetC_Country_ID()); if (!loc.Save()) { continue; } // bpl = new MBPartnerLocation(bp); bpl.SetC_Location_ID(imp.GetC_Location_ID()); if (!bpl.Save()) { continue; } } imp.SetC_Location_ID(bpl.GetC_Location_ID()); imp.SetC_BPartner_Location_ID(bpl.GetC_BPartner_Location_ID()); // User/Contact if (imp.GetContactName() != null || imp.GetEMail() != null || imp.GetPhone() != null) { MUser[] users = bp.GetContacts(true); MUser user = null; for (int i = 0; user == null && i < users.Length; i++) { String name = users[i].GetName(); if (name.Equals(imp.GetContactName()) || name.Equals(imp.GetName())) { user = users[i]; imp.SetAD_User_ID(user.GetAD_User_ID()); } } if (user == null) { user = new MUser(bp); if (imp.GetContactName() == null) { user.SetName(imp.GetName()); } else { user.SetName(imp.GetContactName()); } user.SetEMail(imp.GetEMail()); user.SetPhone(imp.GetPhone()); if (user.Save()) { imp.SetAD_User_ID(user.GetAD_User_ID()); } } } imp.Save(); } // for all new BPartners idr.Close(); // } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, "CreateBP", e); } sql = new StringBuilder("UPDATE I_Invoice " + "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BPartner, ' " + "WHERE C_BPartner_ID IS NULL" + " AND I_IsImported<>'Y'").Append(clientCheck); no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName()); if (no != 0) { log.Warning("No BPartner=" + no); } Commit(); // -- New Invoices ----------------------------------------------------- int noInsert = 0; int noInsertLine = 0; // Go through Invoice Records w/o sql = new StringBuilder("SELECT * FROM I_Invoice " + "WHERE I_IsImported='N'").Append(clientCheck) .Append(" ORDER BY C_BPartner_ID, C_BPartner_Location_ID, I_Invoice_ID"); try { //PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString(), Get_TrxName()); idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName()); // Group Change int oldC_BPartner_ID = 0; int oldC_BPartner_Location_ID = 0; String oldDocumentNo = ""; // MInvoice invoice = null; int lineNo = 0; while (idr.Read()) { X_I_Invoice imp = new X_I_Invoice(GetCtx(), idr, null); String cmpDocumentNo = imp.GetDocumentNo(); if (cmpDocumentNo == null) { cmpDocumentNo = ""; } // New Invoice if (oldC_BPartner_ID != imp.GetC_BPartner_ID() || oldC_BPartner_Location_ID != imp.GetC_BPartner_Location_ID() || !oldDocumentNo.Equals(cmpDocumentNo)) { if (invoice != null) { invoice.ProcessIt(_docAction); invoice.Save(); } // Group Change oldC_BPartner_ID = imp.GetC_BPartner_ID(); oldC_BPartner_Location_ID = imp.GetC_BPartner_Location_ID(); oldDocumentNo = imp.GetDocumentNo(); if (oldDocumentNo == null) { oldDocumentNo = ""; } // invoice = new MInvoice(GetCtx(), 0, null); invoice.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID()); invoice.SetC_DocTypeTarget_ID(imp.GetC_DocType_ID(), true); if (imp.GetDocumentNo() != null) { invoice.SetDocumentNo(imp.GetDocumentNo()); } // invoice.SetC_BPartner_ID(imp.GetC_BPartner_ID()); invoice.SetC_BPartner_Location_ID(imp.GetC_BPartner_Location_ID()); if (imp.GetAD_User_ID() != 0) { invoice.SetAD_User_ID(imp.GetAD_User_ID()); } // if (imp.GetDescription() != null) { invoice.SetDescription(imp.GetDescription()); } if (imp.GetPaymentRule() != null) { invoice.SetPaymentRule(imp.GetPaymentRule()); } invoice.SetC_PaymentTerm_ID(imp.GetC_PaymentTerm_ID()); invoice.SetM_PriceList_ID(imp.GetM_PriceList_ID()); MPriceList pl = MPriceList.Get(GetCtx(), imp.GetM_PriceList_ID(), Get_TrxName()); invoice.SetIsTaxIncluded(pl.IsTaxIncluded()); // SalesRep from Import or the person running the import if (imp.GetSalesRep_ID() != 0) { invoice.SetSalesRep_ID(imp.GetSalesRep_ID()); } if (invoice.GetSalesRep_ID() == 0) { invoice.SetSalesRep_ID(GetAD_User_ID()); } // if (imp.GetAD_OrgTrx_ID() != 0) { invoice.SetAD_OrgTrx_ID(imp.GetAD_OrgTrx_ID()); } if (imp.GetC_Activity_ID() != 0) { invoice.SetC_Activity_ID(imp.GetC_Activity_ID()); } if (imp.GetC_Campaign_ID() != 0) { invoice.SetC_Campaign_ID(imp.GetC_Campaign_ID()); } if (imp.GetC_Project_ID() != 0) { invoice.SetC_Project_ID(imp.GetC_Project_ID()); } // if (imp.GetDateInvoiced() != null) { invoice.SetDateInvoiced(imp.GetDateInvoiced()); } if (imp.GetDateAcct() != null) { invoice.SetDateAcct(imp.GetDateAcct()); } // invoice.Save(); noInsert++; lineNo = 10; } imp.SetC_Invoice_ID(invoice.GetC_Invoice_ID()); // New InvoiceLine MInvoiceLine line = new MInvoiceLine(invoice); if (imp.GetLineDescription() != null) { line.SetDescription(imp.GetLineDescription()); } line.SetLine(lineNo); lineNo += 10; if (imp.GetM_Product_ID() != 0) { line.SetM_Product_ID(imp.GetM_Product_ID(), true); } line.SetQty(imp.GetQtyOrdered()); line.SetPrice(); Decimal?price = (Decimal?)imp.GetPriceActual(); if (price != null && Env.ZERO.CompareTo(price) != 0) { line.SetPrice(price.Value); } if (imp.GetC_Tax_ID() != 0) { line.SetC_Tax_ID(imp.GetC_Tax_ID()); } else { line.SetTax(); imp.SetC_Tax_ID(line.GetC_Tax_ID()); } Decimal?taxAmt = (Decimal?)imp.GetTaxAmt(); if (taxAmt != null && Env.ZERO.CompareTo(taxAmt) != 0) { line.SetTaxAmt(taxAmt); } line.Save(); // imp.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID()); imp.SetI_IsImported(X_I_Invoice.I_ISIMPORTED_Yes); imp.SetProcessed(true); // if (imp.Save()) { noInsertLine++; } } if (invoice != null) { invoice.ProcessIt(_docAction); invoice.Save(); } idr.Close(); } catch (Exception e) { if (idr != null) { idr.Close(); } log.Log(Level.SEVERE, "CreateInvoice", e); } // Set Error to indicator to not imported sql = new StringBuilder("UPDATE I_Invoice " + "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), "@C_Invoice_ID@: @Inserted@"); AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@C_InvoiceLine_ID@: @Inserted@"); return(""); } // doIt
protected override String DoIt() { StringBuilder Sql = new StringBuilder(); string newcon = ""; String date = string.Format("{0:dd/MM/yy}", today); int Record_id = GetRecord_ID(); X_C_Contract con = new X_C_Contract(GetCtx(), Record_id, Get_TrxName()); if (Record_id != 0) { Sql.Append("SELECT RenewalType FROM C_Contract WHERE C_Contract_ID = " + Record_id + " AND RenewContract = 'N' AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID()); string renewType = Util.GetValueOfString(DB.ExecuteScalar(Sql.ToString(), null, Get_TrxName())); if (renewType == "M") { Sql.Clear(); Sql.Append("SELECT C_Contract_ID FROM C_Contract WHERE C_Contract_ID=" + Record_id + " AND RenewContract = 'N' AND AD_Client_ID = " + GetAD_Client_ID()); } else { Sql.Clear(); Sql.Append("SELECT C_Contract_ID FROM C_Contract WHERE (EndDate- NVL(CancelBeforeDays,0)) <= SYSDATE AND C_Contract_ID=" + Record_id + " AND RenewContract = 'N' AND AD_Client_ID = " + GetAD_Client_ID()); } } else { Sql.Clear(); Sql.Append("SELECT C_Contract_ID FROM C_Contract WHERE (EndDate- NVL(CancelBeforeDays,0)) <= SYSDATE AND RenewalType='A' AND RenewContract = 'N' AND AD_Client_ID = " + GetAD_Client_ID()); } IDataReader dr = DB.ExecuteReader(Sql.ToString(), null, Get_TrxName()); int count = 0; decimal Listprice = 0, Stdprice = 0, TotalRate = 0; int cycles = 0, duration = 0, frequency = 0, months = 0; DateTime? CDate = null; DateTime OldStart, Start, endDate; MPriceList priceList = null; MTax tax = null; X_C_Contract contact = null; X_C_Contract New = null; ValueNamePair pp = null; try { while (dr.Read()) { contact = new X_C_Contract(GetCtx(), Util.GetValueOfInt(dr[0]), Get_TrxName()); if (contact.GetRenewalType() == "M") { // SI_0772: By Clicking on Renew Contract, System is throwing an error as 'NoContractReNewed'. CDate = contact.GetCancellationDate(); cycles = Util.GetValueOfInt(contact.GetCycles()); if (CDate != null) { continue; } New = new X_C_Contract(GetCtx(), 0, Get_TrxName()); New.SetRefContract(contact.GetDocumentNo()); New.SetC_Order_ID(contact.GetC_Order_ID()); New.SetC_OrderLine_ID(contact.GetC_OrderLine_ID()); OldStart = (DateTime)(contact.GetStartDate()); Start = (DateTime)(contact.GetEndDate()); New.SetStartDate(Start.AddDays(1)); New.SetC_BPartner_ID(contact.GetC_BPartner_ID()); New.SetBill_Location_ID(contact.GetBill_Location_ID()); New.SetBill_User_ID(contact.GetBill_User_ID()); New.SetSalesRep_ID(contact.GetSalesRep_ID()); New.SetC_ConversionType_ID(contact.GetC_ConversionType_ID()); New.SetC_PaymentTerm_ID(contact.GetC_PaymentTerm_ID()); frequency = contact.GetC_Frequency_ID(); New.SetC_Frequency_ID(frequency); // Get No Of Months from Frequency months = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NoOfMonths FROM C_Frequency WHERE C_Frequency_ID=" + frequency, null, Get_TrxName())); duration = months * cycles; endDate = New.GetStartDate().Value.AddMonths(duration); endDate = endDate.AddDays(-1); New.SetEndDate(endDate); New.SetTotalInvoice(cycles); if (Record_id != 0) { // JID_1124: System has to pick the Pricelist in Service Contract as defined in Renewal Pricelist. also need to pick Price from Latest Valid From Date Version New.SetM_PriceList_ID(contact.GetRef_PriceList_ID()); priceList = new MPriceList(GetCtx(), contact.GetRef_PriceList_ID(), Get_TrxName()); Sql.Clear(); Sql.Append("SELECT pp.PriceList, pp.PriceStd FROM M_ProductPrice pp INNER JOIN M_PriceList_Version plv ON pp.M_PriceList_Version_ID = plv.M_PriceList_Version_ID" + " WHERE pp.M_Product_ID=" + contact.GetM_Product_ID() + " AND plv.IsActive='Y' AND plv.M_PriceList_ID=" + contact.GetRef_PriceList_ID() + " AND plv.VALIDFROM <= SYSDATE ORDER BY plv.VALIDFROM DESC"); DataSet ds = DB.ExecuteDataset(Sql.ToString(), null, Get_TrxName()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Listprice = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["PriceList"]); Stdprice = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["PriceStd"]); } else { dr.Close(); Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "ProductNotOnPriceList")); } //int Version = Util.GetValueOfInt(DB.ExecuteScalar(Query)); //Query = "SELECT PriceList FROM M_ProductPrice WHERE M_PriceList_Version_ID=" + Version + " AND M_Product_ID=" + contact.GetM_Product_ID(); //decimal Listprice = Util.GetValueOfInt(DB.ExecuteScalar(Query)); //Query = "SELECT PriceStd FROM M_ProductPrice WHERE M_PriceList_Version_ID=" + Version + " AND M_Product_ID=" + contact.GetM_Product_ID(); //decimal Stdprice = Util.GetValueOfInt(DB.ExecuteScalar(Query)); //if (Stdprice == 0 && Listprice == 0) //{ // dr.Close(); // return Msg.GetMsg(GetCtx(), "ProductNotINPriceList"); //} New.SetPriceEntered(Stdprice); New.SetPriceActual(Stdprice); New.SetPriceList(Listprice); } else { priceList = new MPriceList(GetCtx(), contact.GetM_PriceList_ID(), Get_TrxName()); New.SetM_PriceList_ID(contact.GetM_PriceList_ID()); New.SetPriceEntered(contact.GetPriceEntered()); New.SetPriceActual(contact.GetPriceActual()); New.SetPriceList(contact.GetPriceList()); } New.SetC_Currency_ID(priceList.GetC_Currency_ID()); New.SetC_UOM_ID(contact.GetC_UOM_ID()); New.SetM_Product_ID(contact.GetM_Product_ID()); New.SetM_AttributeSetInstance_ID(contact.GetM_AttributeSetInstance_ID()); New.SetQtyEntered(contact.GetQtyEntered()); New.SetC_Tax_ID(contact.GetC_Tax_ID()); New.SetC_Campaign_ID(contact.GetC_Campaign_ID()); New.SetRef_Contract_ID(contact.GetC_Contract_ID()); New.SetC_Project_ID(contact.GetC_Project_ID()); New.SetDescription(contact.GetDescription()); //New.SetTaxAmt(contact.GetTaxAmt()); New.SetCancelBeforeDays(contact.GetCancelBeforeDays()); New.SetCycles(contact.GetCycles()); New.SetRenewContract("N"); New.SetScheduleContract("N"); New.SetDocStatus("DR"); New.SetRenewalType("M"); New.SetLineNetAmt(Decimal.Multiply(New.GetPriceEntered(), New.GetQtyEntered())); //String sqltax = "SELECT Rate FROM C_Tax WHERE C_Tax_ID=" + contact.GetC_Tax_ID(); //Decimal? Rate = Util.GetValueOfDecimal(DB.ExecuteScalar(sqltax, null, Get_TrxName())); //Decimal? TotalRate = Util.GetValueOfDecimal((Util.GetValueOfDecimal(New.GetLineNetAmt()) * Util.GetValueOfDecimal(Rate)) / 100); //TotalRate = Decimal.Round(TotalRate.Value, 2); // if Surcharge Tax is selected on Tax, then set value in Surcharge Amount if (New.Get_ColumnIndex("SurchargeAmt") > 0 && tax.GetSurcharge_Tax_ID() > 0) { Decimal surchargeAmt = Env.ZERO; // Calculate Surcharge Amount TotalRate = tax.CalculateSurcharge(New.GetLineNetAmt(), priceList.IsTaxIncluded(), priceList.GetStandardPrecision(), out surchargeAmt); New.SetTaxAmt(TotalRate); New.SetSurchargeAmt(surchargeAmt); } else { // Calculate Tax Amount tax = MTax.Get(GetCtx(), contact.GetC_Tax_ID()); TotalRate = tax.CalculateTax(New.GetLineNetAmt(), priceList.IsTaxIncluded(), priceList.GetPricePrecision()); New.SetTaxAmt(TotalRate); } // Calculate Discount % Decimal?dis = Decimal.Multiply(Decimal.Divide(Decimal.Subtract(New.GetPriceList(), New.GetPriceEntered()), New.GetPriceList()), 100); New.SetDiscount(dis); // Set Grand Total Amount if (priceList.IsTaxIncluded()) { New.SetGrandTotal(New.GetLineNetAmt()); } else { if (New.Get_ColumnIndex("SurchargeAmt") > 0) { New.SetGrandTotal(Decimal.Add(Decimal.Add(New.GetLineNetAmt(), New.GetTaxAmt()), New.GetSurchargeAmt())); } else { New.SetGrandTotal(Decimal.Add(New.GetLineNetAmt(), New.GetTaxAmt())); } } if (contact.GetBillStartDate() != null) { New.SetBillStartDate(contact.GetBillStartDate().Value.AddMonths(contact.GetTotalInvoice())); } else { New.SetBillStartDate(New.GetStartDate()); } if (New.Save()) { newcon = New.GetDocumentNo(); count++; if (Record_id != 0) { contact.SetRef_Contract_ID(New.GetC_Contract_ID()); contact.SetRenewContract("Y"); if (!contact.Save()) { dr.Close(); Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "ContractNotRenew")); } } } else { dr.Close(); Get_TrxName().Rollback(); pp = VLogger.RetrieveError(); if (pp != null) { return(!String.IsNullOrEmpty(pp.GetName()) ? pp.GetName() : Msg.GetMsg(GetCtx(), "ContractNotRenew")); } else { return(Msg.GetMsg(GetCtx(), "ContractNotRenew")); } } } else { // SI_0772: By Clicking on Renew Contract, System is throwing an error as 'NoContractReNewed'. CDate = contact.GetCancellationDate(); cycles = contact.GetCycles(); if (CDate != null) { continue; } New = new X_C_Contract(GetCtx(), 0, Get_TrxName()); New.SetRefContract(contact.GetDocumentNo()); New.SetC_Order_ID(contact.GetC_Order_ID()); New.SetC_OrderLine_ID(contact.GetC_OrderLine_ID()); OldStart = (DateTime)(contact.GetStartDate()); Start = (DateTime)(contact.GetEndDate()); New.SetStartDate(Start.AddDays(1)); frequency = contact.GetC_Frequency_ID(); // Get No Of Months from Frequency months = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NoOfMonths FROM C_Frequency WHERE C_Frequency_ID=" + frequency, null, Get_TrxName())); duration = months * cycles; endDate = New.GetStartDate().Value.AddMonths(duration); endDate = endDate.AddDays(-1); New.SetEndDate(endDate); New.SetC_BPartner_ID(contact.GetC_BPartner_ID()); New.SetBill_Location_ID(contact.GetBill_Location_ID()); New.SetBill_User_ID(contact.GetBill_User_ID()); New.SetSalesRep_ID(contact.GetSalesRep_ID()); New.SetC_ConversionType_ID(contact.GetC_ConversionType_ID()); New.SetC_PaymentTerm_ID(contact.GetC_PaymentTerm_ID()); New.SetC_Frequency_ID(frequency); // invoice Count Start if (Record_id != 0) { if (contact.GetRef_PriceList_ID() == 0) { dr.Close(); Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "FirstSelectPriceList")); } New.SetM_PriceList_ID(contact.GetRef_PriceList_ID()); priceList = new MPriceList(GetCtx(), contact.GetRef_PriceList_ID(), Get_TrxName()); Sql.Clear(); Sql.Append("SELECT pp.PriceList, pp.PriceStd FROM M_ProductPrice pp INNER JOIN M_PriceList_Version plv ON pp.M_PriceList_Version_ID = plv.M_PriceList_Version_ID" + " WHERE pp.M_Product_ID=" + contact.GetM_Product_ID() + " AND plv.IsActive='Y' AND plv.M_PriceList_ID=" + contact.GetRef_PriceList_ID() + " AND plv.VALIDFROM <= SYSDATE ORDER BY plv.VALIDFROM DESC"); DataSet ds = DB.ExecuteDataset(Sql.ToString(), null, Get_TrxName()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { Listprice = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["PriceList"]); Stdprice = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["PriceStd"]); } else { dr.Close(); Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "ProductNotOnPriceList")); } //String Query = "Select M_PriceList_Version_id from M_PriceList_Version where IsActive='Y' and M_PriceList_Id=" + contact.GetRef_PriceList_ID(); //int Version = Util.GetValueOfInt(DB.ExecuteScalar(Query)); //Query = "Select PriceList,PriceStd from M_ProductPrice where M_PriceList_Version_id=" + Version + " and M_Product_ID=" + contact.GetM_Product_ID(); //decimal Listprice = Util.GetValueOfInt(DB.ExecuteScalar(Query)); //Query = "Select PriceList,PriceStd from M_ProductPrice where M_PriceList_Version_id=" + Version + " and M_Product_ID=" + contact.GetM_Product_ID(); //decimal Stdprice = Util.GetValueOfInt(DB.ExecuteScalar(Query)); //if (Stdprice == 0 && Listprice == 0) //{ // return Msg.GetMsg(GetCtx(), "ProductNotINPriceList"); //} New.SetPriceEntered(Stdprice); New.SetPriceActual(Stdprice); New.SetPriceList(Listprice); } else { priceList = new MPriceList(GetCtx(), contact.GetM_PriceList_ID(), Get_TrxName()); New.SetM_PriceList_ID(contact.GetM_PriceList_ID()); New.SetPriceActual(contact.GetPriceActual()); New.SetPriceList(contact.GetPriceList()); New.SetPriceEntered(contact.GetPriceEntered()); } New.SetTotalInvoice(contact.GetCycles()); New.SetC_Currency_ID(priceList.GetC_Currency_ID()); New.SetC_UOM_ID(contact.GetC_UOM_ID()); New.SetM_Product_ID(contact.GetM_Product_ID()); New.SetM_AttributeSetInstance_ID(contact.GetM_AttributeSetInstance_ID()); New.SetQtyEntered(contact.GetQtyEntered()); New.SetC_Tax_ID(contact.GetC_Tax_ID()); New.SetC_Campaign_ID(contact.GetC_Campaign_ID()); New.SetRef_Contract_ID(contact.GetC_Contract_ID()); New.SetC_Project_ID(contact.GetC_Project_ID()); New.SetDescription(contact.GetDescription()); New.SetCancelBeforeDays(contact.GetCancelBeforeDays()); New.SetCycles(contact.GetCycles()); New.SetRenewContract("N"); New.SetScheduleContract("Y"); New.SetRenewalType("A"); New.SetDocStatus("DR"); New.SetLineNetAmt(Decimal.Multiply(New.GetPriceEntered(), New.GetQtyEntered())); // Calculate Tax Amount tax = MTax.Get(GetCtx(), contact.GetC_Tax_ID()); // if Surcharge Tax is selected on Tax, then set value in Surcharge Amount if (New.Get_ColumnIndex("SurchargeAmt") > 0 && tax.GetSurcharge_Tax_ID() > 0) { Decimal surchargeAmt = Env.ZERO; // Calculate Surcharge Amount TotalRate = tax.CalculateSurcharge(New.GetLineNetAmt(), priceList.IsTaxIncluded(), priceList.GetStandardPrecision(), out surchargeAmt); New.SetTaxAmt(TotalRate); New.SetSurchargeAmt(surchargeAmt); } else { TotalRate = tax.CalculateTax(New.GetLineNetAmt(), priceList.IsTaxIncluded(), priceList.GetPricePrecision()); New.SetTaxAmt(TotalRate); } // Calculate Discount % Decimal?dis = Decimal.Multiply(Decimal.Divide(Decimal.Subtract(New.GetPriceList(), New.GetPriceEntered()), New.GetPriceList()), 100); New.SetDiscount(dis); // Set Grand Total Amount if (priceList.IsTaxIncluded()) { New.SetGrandTotal(New.GetLineNetAmt()); } else { if (New.Get_ColumnIndex("SurchargeAmt") > 0) { New.SetGrandTotal(Decimal.Add(Decimal.Add(New.GetLineNetAmt(), New.GetTaxAmt()), New.GetSurchargeAmt())); } else { New.SetGrandTotal(Decimal.Add(New.GetLineNetAmt(), New.GetTaxAmt())); } } if (contact.GetBillStartDate() != null) { New.SetBillStartDate(contact.GetBillStartDate().Value.AddMonths(contact.GetTotalInvoice())); } else { New.SetBillStartDate(New.GetStartDate()); } if (New.Save()) { newcon = New.GetDocumentNo(); count++; contact.SetRef_Contract_ID(New.GetC_Contract_ID()); contact.SetRenewContract("Y"); if (!contact.Save()) { dr.Close(); Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "ContractNotRenew")); } if (!EnterSchedules(New.GetC_Contract_ID(), cycles)) { dr.Close(); Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "ContractNotRenew")); } New.SetProcessed(true); if (!New.Save()) { dr.Close(); Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "ContractNotRenew")); } } else { dr.Close(); Get_TrxName().Rollback(); pp = VLogger.RetrieveError(); if (pp != null) { return(!String.IsNullOrEmpty(pp.GetName()) ? pp.GetName() : Msg.GetMsg(GetCtx(), "ContractNotRenew")); } else { return(Msg.GetMsg(GetCtx(), "ContractNotRenew")); } } } } dr.Close(); if (count != 0 && Record_id != 0) { return(Msg.GetMsg(GetCtx(), "ContractReNewed") + ": " + newcon); } if (count != 0) { return(Msg.GetMsg(GetCtx(), "ContractReNewed")); } return(Msg.GetMsg(GetCtx(), "ContractNotRenew")); } catch { if (dr != null) { dr.Close(); dr = null; } } finally { if (dr != null) { dr.Close(); dr = null; } } return(Msg.GetMsg(GetCtx(), "ContractNotRenew")); }
/// <summary> /// Perform Process. /// </summary> /// <returns>Message (variables are parsed)</returns> protected override String DoIt() { log.Info("doIt - C_CommissionRun_ID=" + GetRecord_ID()); // Load Data MCommissionRun comRun = new MCommissionRun(GetCtx(), GetRecord_ID(), Get_Trx()); if (comRun.Get_ID() == 0) { throw new ArgumentException("CommissionAPInvoice - No Commission Run"); } if (Env.ZERO.CompareTo(comRun.GetGrandTotal()) == 0) { throw new ArgumentException("@GrandTotal@ = 0"); } MCommission com = new MCommission(GetCtx(), comRun.GetC_Commission_ID(), Get_Trx()); if (com.Get_ID() == 0) { throw new ArgumentException("CommissionAPInvoice - No Commission"); } if (com.GetC_Charge_ID() == 0) { throw new ArgumentException("CommissionAPInvoice - No Charge on Commission"); } MBPartner bp = new MBPartner(GetCtx(), com.GetC_BPartner_ID(), Get_Trx()); if (bp.Get_ID() == 0) { throw new ArgumentException("CommissionAPInvoice - No BPartner"); } // Create Invoice MInvoice invoice = new MInvoice(GetCtx(), 0, null); invoice.SetClientOrg(com.GetAD_Client_ID(), com.GetAD_Org_ID()); invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API invoice.SetBPartner(bp); // JID_0101: When we generate the AP invoice from Commission run window, its giving price list error. if (invoice.GetM_PriceList_ID() == 0) { string sql = "SELECT M_PriceList_ID FROM M_PriceList WHERE IsActive = 'Y' AND AD_Client_ID = " + com.GetAD_Client_ID() + " AND AD_Org_ID = " + com.GetAD_Org_ID() + " AND IsDefault='Y' AND IsSOPriceList='N'"; int pricelist = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx())); if (pricelist == 0) { pricelist = MPriceList.GetDefault(GetCtx(), false).Get_ID(); } if (pricelist > 0) { invoice.SetM_PriceList_ID(pricelist); } } // invoice.setDocumentNo (comRun.getDocumentNo()); // may cause unique constraint invoice.SetSalesRep_ID(GetAD_User_ID()); // caller // if (com.GetC_Currency_ID() != invoice.GetC_Currency_ID()) { throw new ArgumentException("CommissionAPInvoice - Currency of PO Price List not Commission Currency"); } // if (!invoice.Save()) { //return GetReterivedError(invoice, "CommissionAPInvoice - cannot save Invoice"); throw new Exception("CommissionAPInvoice - cannot save Invoice"); } // Create Invoice Line MInvoiceLine iLine = new MInvoiceLine(invoice); iLine.SetC_Charge_ID(com.GetC_Charge_ID()); iLine.SetQty(1); iLine.SetPrice(comRun.GetGrandTotal()); iLine.SetTax(); if (!iLine.Save()) { //return GetReterivedError(iLine, "CommissionAPInvoice - cannot save Invoice Line"); throw new Exception("CommissionAPInvoice - cannot save Invoice Line"); } // return("@C_Invoice_ID@ = " + invoice.GetDocumentNo()); }
/// <summary> /// Create Price List /// </summary> /// <returns>info message</returns> private String Create() { StringBuilder info = new StringBuilder(); // Delete Old Data if (_deleteOld) { int no = DataBase.DB.ExecuteQuery( "DELETE M_ProductPrice " + "WHERE M_PriceList_Version_ID=" + _M_PriceList_Version_ID, null, Get_TrxName()); log.Info("Deleted=" + no); info.Append("@Deleted@=").Append(no).Append(" - "); } int M_Pricelist_Version_Base_ID = _plv.GetM_Pricelist_Version_Base_ID(); MPriceList pl = _plv.GetPriceList(); int curPrecision = pl.GetStandardPrecision(); /** * For All Discount Lines in Sequence */ MDiscountSchema ds = new MDiscountSchema(GetCtx(), _plv.GetM_DiscountSchema_ID(), Get_TrxName()); MDiscountSchemaLine[] dsl = ds.GetLines(false); for (int i = 0; i < dsl.Length; i++) { MDiscountSchemaLine dsLine = dsl[i]; String message = "#" + dsLine.GetSeqNo(); String dd = dsLine.GetDescription(); if (dd != null && dd.Length > 0) { message += " " + dd; } // Clear Temporary Table int noDeleted = DataBase.DB.ExecuteQuery("DELETE FROM T_Selection", null, Get_TrxName()); // Create Selection in Temporary Table String sql = null; int M_DiscountSchemaLine_ID = dsLine.GetM_DiscountSchemaLine_ID(); int p2 = M_Pricelist_Version_Base_ID; if (p2 == 0) // Create from PO { p2 = dsLine.GetAD_Client_ID(); sql = "INSERT INTO T_Selection (T_Selection_ID) " + "SELECT DISTINCT po.M_Product_ID " + "FROM M_Product_PO po " + " INNER JOIN M_Product p ON (p.M_Product_ID=po.M_Product_ID)" + " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #1 + "WHERE p.AD_Client_ID IN (" + p2 + ", 0)" // #2 + " AND p.IsActive='Y' AND po.IsActive='Y' AND po.IsCurrentVendor='Y'" // Optional Restrictions + " AND (dl.M_Product_Category_ID IS NULL OR p.M_Product_Category_ID=dl.M_Product_Category_ID)" + " AND (dl.C_BPartner_ID IS NULL OR po.C_BPartner_ID=dl.C_BPartner_ID)" + " AND (dl.M_Product_ID IS NULL OR p.M_Product_ID=dl.M_Product_ID)"; } else // Create from Price List ** { sql = "INSERT INTO T_Selection (T_Selection_ID) " + "SELECT DISTINCT p.M_Product_ID " + "FROM M_ProductPrice pp" + " INNER JOIN M_Product p ON (p.M_Product_ID=pp.M_Product_ID)" + " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #1 + "WHERE pp.M_PriceList_Version_ID=" + p2 //#2 PriceList_Version_Base_ID + " AND p.IsActive='Y' AND pp.IsActive='Y'" // Optional Restrictions + " AND (dl.M_Product_Category_ID IS NULL OR p.M_Product_Category_ID=dl.M_Product_Category_ID)" + " AND (dl.C_BPartner_ID IS NULL OR EXISTS " + "(SELECT * FROM M_Product_PO po " + "WHERE po.M_Product_ID=p.M_Product_ID AND po.C_BPartner_ID=dl.C_BPartner_ID))" + " AND (dl.M_Product_ID IS NULL OR p.M_Product_ID=dl.M_Product_ID)"; } //idr = DataBase.prepareStatement(sql, get_TrxName()); int noSelected = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); message += ": @Selected@=" + noSelected; // Delete Prices in Selection, so that we can insert if (M_Pricelist_Version_Base_ID == 0 || M_Pricelist_Version_Base_ID != _M_PriceList_Version_ID) { sql = "DELETE FROM M_ProductPrice pp " + "WHERE pp.M_PriceList_Version_ID=" + _M_PriceList_Version_ID + " AND EXISTS (SELECT * FROM T_Selection s WHERE pp.M_Product_ID=s.T_Selection_ID)"; noDeleted = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); message += ", @Deleted@=" + noDeleted; } // Copy (Insert) Prices int noInserted = 0; sql = "INSERT INTO M_ProductPrice " + "(M_PriceList_Version_ID, M_Product_ID," + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy," + " PriceList, PriceStd, PriceLimit) "; // if (M_Pricelist_Version_Base_ID == _M_PriceList_Version_ID) { sql = null; // We have Prices already } else if (M_Pricelist_Version_Base_ID == 0) { /** Copy and Convert from Product_PO */ sql += "SELECT plv.M_PriceList_Version_ID, po.M_Product_ID," + " plv.AD_Client_ID, plv.AD_Org_ID, 'Y', SysDate, plv.UpdatedBy, SysDate, plv.UpdatedBy," // Price List + " COALESCE(currencyConvert(po.PriceList," + " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PriceList)," // Price Std + " COALESCE(currencyConvert(po.PriceList," + " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PriceList)," // Price Limit + " COALESCE(currencyConvert(po.PricePO," + " po.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -po.PricePO) " // + "FROM M_Product_PO po" + " INNER JOIN M_PriceList_Version plv ON (plv.M_PriceList_Version_ID=" + _M_PriceList_Version_ID + ")" // #1 + " INNER JOIN M_PriceList pl ON (pl.M_PriceList_ID=plv.M_PriceList_ID)" + " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #2 // + "WHERE EXISTS (SELECT * FROM T_Selection s WHERE po.M_Product_ID=s.T_Selection_ID)" + " AND po.IsCurrentVendor='Y' AND po.IsActive='Y'"; } else { /** Copy and Convert from other PriceList_Version */ sql += "SELECT plv.M_PriceList_Version_ID, pp.M_Product_ID," + " plv.AD_Client_ID, plv.AD_Org_ID, 'Y', SysDate, plv.UpdatedBy, SysDate, plv.UpdatedBy," // Price List + " COALESCE(currencyConvert(pp.PriceList," + " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceList)," // Price Std + " COALESCE(currencyConvert(pp.PriceStd," + " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceStd)," // Price Limit + " COALESCE(currencyConvert(pp.PriceLimit," + " bpl.C_Currency_ID, pl.C_Currency_ID, dl.ConversionDate, dl.C_ConversionType_ID, plv.AD_Client_ID, plv.AD_Org_ID), -pp.PriceLimit) " // + "FROM M_ProductPrice pp" + " INNER JOIN M_PriceList_Version plv ON (plv.M_PriceList_Version_ID=" + _M_PriceList_Version_ID + ")" // #1 + " INNER JOIN M_PriceList pl ON (pl.M_PriceList_ID=plv.M_PriceList_ID)" + " INNER JOIN M_PriceList_Version bplv ON (pp.M_PriceList_Version_ID=bplv.M_PriceList_Version_ID)" + " INNER JOIN M_PriceList bpl ON (bplv.M_PriceList_ID=bpl.M_PriceList_ID)" + " INNER JOIN M_DiscountSchemaLine dl ON (dl.M_DiscountSchemaLine_ID=" + M_DiscountSchemaLine_ID + ") " // #2 // + "WHERE "; if (M_Pricelist_Version_Base_ID != 0) { sql += "pp.M_PriceList_Version_ID=" + M_Pricelist_Version_Base_ID + " AND"; // #3 M_PriceList_Version_Base_ID } sql += " EXISTS (SELECT * FROM T_Selection s WHERE pp.M_Product_ID=s.T_Selection_ID)" + " AND pp.IsActive='Y'"; } if (sql != null) { //pstmt = DataBase.prepareStatement(sql, get_TrxName()); //pstmt.setInt(1, _M_PriceList_Version_ID); // pstmt.setInt(2, M_DiscountSchemaLine_ID); //if (M_Pricelist_Version_Base_ID != 0) //{ // pstmt.setInt(3, M_Pricelist_Version_Base_ID); // } noInserted = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); message += " @Inserted@=" + noInserted; } /** Calculations **/ MProductPrice[] pp = _plv.GetProductPrice( "AND EXISTS (SELECT * FROM T_Selection s " + "WHERE s.T_Selection_ID=M_ProductPrice.M_Product_ID)"); for (int j = 0; j < pp.Length; j++) { MProductPrice price = pp[j]; Decimal priceList = price.GetPriceList(); Decimal priceStd = price.GetPriceStd(); Decimal priceLimit = price.GetPriceLimit(); // price.SetPriceList(Calculate(dsLine.GetList_Base(), priceList, priceStd, priceLimit, dsLine.GetList_Fixed(), dsLine.GetList_AddAmt(), dsLine.GetList_Discount(), dsLine.GetList_Rounding(), curPrecision)); price.SetPriceStd(Calculate(dsLine.GetStd_Base(), priceList, priceStd, priceLimit, dsLine.GetStd_Fixed(), dsLine.GetStd_AddAmt(), dsLine.GetStd_Discount(), dsLine.GetStd_Rounding(), curPrecision)); price.SetPriceLimit(Calculate(dsLine.GetLimit_Base(), priceList, priceStd, priceLimit, dsLine.GetLimit_Fixed(), dsLine.GetLimit_AddAmt(), dsLine.GetLimit_Discount(), dsLine.GetLimit_Rounding(), curPrecision)); price.Save(); } // for all products // Clear Temporary Table noDeleted = DataBase.DB.ExecuteQuery("DELETE FROM T_Selection", null, Get_TrxName()); // AddLog(message); } // for all lines MProductPrice[] ppl = _plv.GetProductPrice(true); info.Append(" - @Records@=").Append(ppl.Length); return(info.ToString()); }