/// <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());
}
