protected override string DoIt() { StringBuilder _sql = new StringBuilder(); MVA009Batch batch = new MVA009Batch(GetCtx(), GetRecord_ID(), Get_TrxName()); MVA009PaymentMethod _paymthd = null; MVA009BatchLineDetails lineDetail = null; MVA009BatchLines line = null; //if (batch.GetVA009_GenerateLines()=="Y") //{ // msg = Msg.GetMsg(GetCtx(), "VA009_BatchLineAlreadyCreated"); // return msg; //} msg = DeleteBatchLines(_sql, batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName()); if (!String.IsNullOrEmpty(msg)) { return(msg); } MBankAccount _bankacc = new MBankAccount(GetCtx(), batch.GetC_BankAccount_ID(), Get_TrxName()); decimal dueamt = 0; _sql.Clear(); _sql.Append(@"Select cp.ad_client_id, cp.ad_org_id,CI.C_Bpartner_ID, ci.c_invoice_id, cp.c_invoicepayschedule_id, cp.duedate, cp.dueamt, cp.discountdate, cp.discountamt,cp.va009_paymentmethod_id,ci.c_currency_id , doc.DocBaseType From C_Invoice CI inner join C_InvoicePaySchedule CP ON CI.c_invoice_id= CP.c_invoice_id INNER JOIN C_DocType doc ON doc.C_DocType_ID = CI.C_DocType_ID Where ci.ispaid='N' AND cp.va009_ispaid='N' AND cp.C_Payment_ID IS NULL AND CI.IsActive = 'Y' and ci.docstatus in ('CO','CL') AND cp.VA009_ExecutionStatus !='Y' AND CI.AD_Client_ID = " + batch.GetAD_Client_ID() + " AND CI.AD_Org_ID = " + batch.GetAD_Org_ID()); if (_C_BPartner_ID > 0) { _sql.Append(" and CI.C_Bpartner_ID=" + _C_BPartner_ID); } if (_C_invoice_ID > 0) { _sql.Append(" and CI.C_invoice_ID=" + _C_invoice_ID); } if (_paySchedule_ID > 0) { _sql.Append(" AND CP.C_InvoicePaySchedule_ID=" + _paySchedule_ID); } if (_docType > 0) { _sql.Append(" ANd CI.C_DocType_ID=" + _docType); } else { _sql.Append(" ANd doc.DocBaseType IN ('API' , 'ARI' , 'APC' , 'ARC') "); } if (_paymentMethod > 0) { _sql.Append(" And CP.VA009_PaymentMethod_ID=" + _paymentMethod); _paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName()); _trigger = _paymthd.IsVA009_IsMandate(); } if (_DateDoc_From != null && _DateDoc_To != null) { _sql.Append(" and cp.duedate BETWEEN "); _sql.Append(GlobalVariable.TO_DATE(_DateDoc_From, true) + " AND "); _sql.Append(GlobalVariable.TO_DATE(_DateDoc_To, true)); } else if (_DateDoc_From != null && _DateDoc_To == null) { _sql.Append(" and cp.duedate >=" + GlobalVariable.TO_DATE(_DateDoc_From, true)); } else if (_DateDoc_From == null && _DateDoc_To != null) { _sql.Append(" and cp.duedate <=" + GlobalVariable.TO_DATE(_DateDoc_To, true)); } //else if (C_ConversionType_ID > 0) //commented the conversion type because while creatring invoice against Base currency, system will set currencyconversionType_ID=0 //{ // _sql.Append(" AND C_ConversionType_ID=" + C_ConversionType_ID); //} if (VA009_IsSameCurrency == true) { _sql.Append(" AND CI.C_Currency_ID =" + _bankacc.GetC_Currency_ID()); } _sql.Append(" Order by CI.C_Bpartner_ID asc , doc.docbasetype "); DataSet ds = new DataSet(); ds = DB.ExecuteDataset(_sql.ToString()); if (ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { if (C_ConversionType_ID == 0) //to Set Default conversion Type { C_ConversionType_ID = GetDefaultConversionType(_sql); } for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if ((Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"])) == 0) { continue; } // if invoice is of AP Invoice and AP Credit Memo then make a single Batch line if (docBaseType == "API" || docBaseType == "APC") { if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) && ("API" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "APC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]))) { line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName()); } else { line = null; } } // if invoice is of AR Invoice and AR Credit Memo then make a single Batch line else if (docBaseType == "ARI" || docBaseType == "ARC") { if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) && ("ARI" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) || "ARC" == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]))) { line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, Get_TrxName()); } else { line = null; } } //if (_BPartner == Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]) && docBaseType == Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"])) //{ // line = new MVA009BatchLines(GetCtx(), _VA009_BatchLine_ID, null); //} // else if (line == null) { line = new MVA009BatchLines(GetCtx(), 0, Get_TrxName()); line.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"])); line.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"])); line.SetVA009_Batch_ID(batch.GetVA009_Batch_ID()); _BPartner = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_BPartner_ID"]); docBaseType = Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]); line.SetC_BPartner_ID(_BPartner); if (_trigger == true) { _sql.Clear(); _sql.Append("Select VA009_BPMandate_id from C_BPartner Where C_BPartner_ID=" + _BPartner + " AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID()); DataSet ds1 = new DataSet(); ds1 = DB.ExecuteDataset(_sql.ToString()); if (ds1.Tables != null && ds1.Tables.Count > 0 && ds1.Tables[0].Rows.Count > 0) { line.SetVA009_BPMandate_ID(Util.GetValueOfInt(ds1.Tables[0].Rows[0]["VA009_BPMandate_id"])); } } if (line.Save(Get_TrxName())) { //line.SetProcessed(true); line.Save(Get_TrxName()); _VA009_BatchLine_ID = line.GetVA009_BatchLines_ID(); } else { Get_TrxName().Rollback(); _BPartner = 0; _VA009_BatchLine_ID = 0; } } lineDetail = new MVA009BatchLineDetails(GetCtx(), 0, Get_TrxName()); lineDetail.SetAD_Client_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Client_ID"])); lineDetail.SetAD_Org_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["Ad_Org_ID"])); lineDetail.SetVA009_BatchLines_ID(line.GetVA009_BatchLines_ID()); lineDetail.SetC_Invoice_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Invoice_ID"])); lineDetail.SetC_InvoicePaySchedule_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"])); lineDetail.SetDueDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DueDate"])); lineDetail.SetC_ConversionType_ID(C_ConversionType_ID); dueamt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DueAmt"])); Decimal DiscountAmt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]); bool issamme = true; decimal comvertedamt = 0; if (Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]) == _bankacc.GetC_Currency_ID()) { issamme = true; } else { issamme = false; } if (!issamme) { dueamt = MConversionRate.Convert(GetCtx(), dueamt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID()); if (DiscountAmt > 0) { DiscountAmt = MConversionRate.Convert(GetCtx(), DiscountAmt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID()); if (DiscountAmt == 0) { Get_TrxName().Rollback(); msg = Msg.GetMsg(GetCtx(), "NoCurrencyConversion"); MCurrency from = new MCurrency(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), Get_TrxName()); MCurrency to = new MCurrency(GetCtx(), Util.GetValueOfInt(_bankacc.GetC_Currency_ID()), Get_TrxName()); return(msg + from.GetISO_Code() + "," + to.GetISO_Code()); } } if (dueamt == 0) { Get_TrxName().Rollback(); msg = Msg.GetMsg(GetCtx(), "NoCurrencyConversion"); MCurrency from = new MCurrency(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), Get_TrxName()); MCurrency to = new MCurrency(GetCtx(), Util.GetValueOfInt(_bankacc.GetC_Currency_ID()), Get_TrxName()); return(msg + from.GetISO_Code() + "," + to.GetISO_Code()); } } if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate())) { //dueamt = dueamt - (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"])); dueamt = dueamt - DiscountAmt; // 145-2.88 } if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC") { lineDetail.SetDueAmt(-1 * dueamt); comvertedamt = (-1 * dueamt); } else { lineDetail.SetDueAmt(dueamt); comvertedamt = (dueamt); } if (issamme == false) { comvertedamt = dueamt; //comvertedamt = MConversionRate.Convert(GetCtx(), dueamt, Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"]), _bankacc.GetC_Currency_ID(), DateTime.Now, C_ConversionType_ID, GetCtx().GetAD_Client_ID(), GetCtx().GetAD_Org_ID()); lineDetail.SetC_Currency_ID(_bankacc.GetC_Currency_ID()); if (Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "APC" || Util.GetValueOfString(ds.Tables[0].Rows[i]["DocBaseType"]) == "ARC") { comvertedamt = (-1 * comvertedamt); } } else { lineDetail.SetC_Currency_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["c_currency_id"])); } lineDetail.SetVA009_ConvertedAmt(comvertedamt); lineDetail.SetVA009_PaymentMethod_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["va009_paymentmethod_id"])); if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) < Util.GetValueOfDateTime(batch.GetVA009_DocumentDate())) { lineDetail.SetDiscountDate(null); lineDetail.SetDiscountAmt(0); } else if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate())) { lineDetail.SetDiscountDate(Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"])); //lineDetail.SetDiscountAmt(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"])); lineDetail.SetDiscountAmt(DiscountAmt); } if (!lineDetail.Save(Get_TrxName())) { Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd")); //return"BatchLine Not Saved"; } else { //lineDetail.SetProcessed(true); //lineDetail.Save(Get_TrxName()); //MInvoicePaySchedule _invpay = new MInvoicePaySchedule(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]), Get_TrxName()); //_invpay.SetVA009_ExecutionStatus("Y"); //_invpay.Save(Get_TrxName()); } } batch.SetVA009_GenerateLines("Y"); //batch.SetProcessed(true); //Commeted by Arpit asked by Ashish Gandhi to set processed only if the Payment completion is done batch.Save(Get_TrxName()); if (_paymentMethod != 0) { //_paymthd = new MVA009PaymentMethod(GetCtx(), _paymentMethod, Get_TrxName()); batch.SetVA009_PaymentMethod_ID(_paymentMethod); batch.SetVA009_PaymentRule(_paymthd.GetVA009_PaymentRule()); batch.SetVA009_PaymentTrigger(_paymthd.GetVA009_PaymentTrigger()); if (!batch.Save(Get_TrxName())) { Get_TrxName().Rollback(); return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd")); } } return(Msg.GetMsg(GetCtx(), "VA009_BatchLineCrtd"));; } else { return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd")); }; }
/// <summary> /// Perform Process /// </summary> /// <returns>Message (text with variables)</returns> protected override String DoIt() { log.Info("C_Commission_ID=" + GetRecord_ID() + ", StartDate=" + p_StartDate); if (p_StartDate == null) { p_StartDate = DateTime.Now; } m_com = new MCommission(GetCtx(), GetRecord_ID(), Get_Trx()); if (m_com.Get_ID() == 0) { throw new Exception("No Commission"); } // Create Commission MCommissionRun comRun = new MCommissionRun(m_com); SetStartEndDate(); comRun.SetStartDate(p_StartDate); System.Threading.Thread.CurrentThread.CurrentCulture = Utility.Env.GetLanguage(GetCtx()).GetCulture(Utility.Env.GetLoginLanguage(GetCtx()).GetAD_Language()); System.Threading.Thread.CurrentThread.CurrentUICulture = Utility.Env.GetLanguage(GetCtx()).GetCulture(Utility.Env.GetLoginLanguage(GetCtx()).GetAD_Language()); // 01-Jan-2000 - 31-Jan-2001 - USD Classes.SimpleDateFormat format = Classes.DisplayType.GetDateFormat(Classes.DisplayType.Date); String description = format.Format(p_StartDate) + " - " + format.Format(m_EndDate) + " - " + MCurrency.GetISO_Code(GetCtx(), m_com.GetC_Currency_ID()); //string description = p_StartDate // + " - " + m_EndDate // + " - " + MCurrency.GetISO_Code(GetCtx(), m_com.GetC_Currency_ID()); comRun.SetDescription(description); System.Threading.Thread.CurrentThread.CurrentCulture = Utility.Env.GetLanguage(GetCtx()).GetCulture(Utility.Env.GetBaseAD_Language()); System.Threading.Thread.CurrentThread.CurrentUICulture = Utility.Env.GetLanguage(GetCtx()).GetCulture(Utility.Env.GetBaseAD_Language()); if (!comRun.Save()) { throw new Exception("Could not save Commission Run please check Organization"); } MCommissionLine[] lines = m_com.GetLines(); for (int i = 0; i < lines.Length; i++) { #region #endregion // Amt for Line - Updated By Trigger MCommissionAmt comAmt = new MCommissionAmt(comRun, lines[i].GetC_CommissionLine_ID()); if (!comAmt.Save()) { throw new SystemException("Could not save Commission Amt"); } // StringBuilder sql = new StringBuilder(); if (MCommission.DOCBASISTYPE_Receipt.Equals(m_com.GetDocBasisType())) { if (m_com.IsListDetails()) { sql.Append("SELECT h.C_Currency_ID, (l.LineNetAmt*al.Amount/h.GrandTotal) AS Amt," + " (l.QtyInvoiced*al.Amount/h.GrandTotal) AS Qty," + " NULL, l.C_InvoiceLine_ID, p.DocumentNo||'_'||h.DocumentNo," + " COALESCE(prd.Value,l.Description), h.DateInvoiced " + "FROM C_Payment p" + " INNER JOIN C_AllocationLine al ON (p.C_Payment_ID=al.C_Payment_ID)" + " INNER JOIN C_Invoice h ON (al.C_Invoice_ID = h.C_Invoice_ID)" + " INNER JOIN C_InvoiceLine l ON (h.C_Invoice_ID = l.C_Invoice_ID) " + " LEFT OUTER JOIN M_Product prd ON (l.M_Product_ID = prd.M_Product_ID) " + "WHERE p.DocStatus IN ('CL','CO','RE')" + " AND h.IsSOTrx='Y'" + " AND p.AD_Client_ID = @clientid" + " AND l.IsCommissionCalculated = 'N' " + " AND p.DateTrx BETWEEN @sdate AND @edate"); } else { sql.Append("SELECT h.C_Currency_ID, SUM(l.LineNetAmt*al.Amount/h.GrandTotal) AS Amt," + " SUM(l.QtyInvoiced*al.Amount/h.GrandTotal) AS Qty," + " NULL, NULL, NULL, NULL, MAX(h.DateInvoiced) " + "FROM C_Payment p" + " INNER JOIN C_AllocationLine al ON (p.C_Payment_ID=al.C_Payment_ID)" + " INNER JOIN C_Invoice h ON (al.C_Invoice_ID = h.C_Invoice_ID)" + " INNER JOIN C_InvoiceLine l ON (h.C_Invoice_ID = l.C_Invoice_ID) " + "WHERE p.DocStatus IN ('CL','CO','RE')" + " AND h.IsSOTrx='Y'" + " AND p.AD_Client_ID = @clientid" + " AND l.IsCommissionCalculated = 'N' " + " AND p.DateTrx BETWEEN @sdate AND @edate"); } } else if (MCommission.DOCBASISTYPE_Order.Equals(m_com.GetDocBasisType())) { if (m_com.IsListDetails()) { sql.Append("SELECT h.C_Currency_ID, l.LineNetAmt, l.QtyOrdered, " + "l.C_OrderLine_ID, NULL, h.DocumentNo," + " COALESCE(prd.Value,l.Description),h.DateOrdered " + "FROM C_Order h" + " INNER JOIN C_OrderLine l ON (h.C_Order_ID = l.C_Order_ID)" + " LEFT OUTER JOIN M_Product prd ON (l.M_Product_ID = prd.M_Product_ID) " + "WHERE h.DocStatus IN ('CL','CO')" + " AND h.IsSOTrx='Y'" + " AND h.AD_Client_ID = @clientid" + " AND l.IsCommissionCalculated = 'N' " + " AND h.DateOrdered BETWEEN @sdate AND @edate"); } else { sql.Append("SELECT h.C_Currency_ID, SUM(l.LineNetAmt) AS Amt," + " SUM(l.QtyOrdered) AS Qty, " + "NULL, NULL, NULL, NULL, MAX(h.DateOrdered) " + "FROM C_Order h" + " INNER JOIN C_OrderLine l ON (h.C_Order_ID = l.C_Order_ID) " + "WHERE h.DocStatus IN ('CL','CO')" + " AND h.IsSOTrx='Y'" + " AND l.IsCommissionCalculated = 'N' " + " AND h.AD_Client_ID = @clientid" + " AND h.DateOrdered BETWEEN @sdate AND @edate"); } } else // Invoice Basis { if (m_com.IsListDetails()) { sql.Append("SELECT h.C_Currency_ID, l.LineNetAmt, l.QtyInvoiced, " + "NULL, l.C_InvoiceLine_ID, h.DocumentNo," + " COALESCE(prd.Value,l.Description),h.DateInvoiced " + "FROM C_Invoice h" + " INNER JOIN C_InvoiceLine l ON (h.C_Invoice_ID = l.C_Invoice_ID)" + " LEFT OUTER JOIN M_Product prd ON (l.M_Product_ID = prd.M_Product_ID) " + "WHERE h.DocStatus IN ('CL','CO','RE')" + " AND h.IsSOTrx='Y'" + " AND l.IsCommissionCalculated = 'N' " + " AND h.AD_Client_ID = @clientid" + " AND h.DateInvoiced BETWEEN @sdate AND @edate"); } else { sql.Append("SELECT h.C_Currency_ID, SUM(l.LineNetAmt) AS Amt," + " SUM(l.QtyInvoiced) AS Qty, " + "NULL, NULL, NULL, NULL, MAX(h.DateInvoiced) " + "FROM C_Invoice h" + " INNER JOIN C_InvoiceLine l ON (h.C_Invoice_ID = l.C_Invoice_ID) " + "WHERE h.DocStatus IN ('CL','CO','RE')" + " AND h.IsSOTrx='Y'" + " AND l.IsCommissionCalculated = 'N' " + " AND h.AD_Client_ID = @clientid" + " AND h.DateInvoiced BETWEEN @sdate AND @edate"); } } // CommissionOrders/Invoices if (lines[i].IsCommissionOrders()) { MUser[] users = MUser.GetOfBPartner(GetCtx(), m_com.GetC_BPartner_ID()); if (users == null || users.Length == 0) { throw new Exception("Commission Business Partner has no Users/Contact"); } if (users.Length == 1) { int SalesRep_ID = users[0].GetAD_User_ID(); sql.Append(" AND h.SalesRep_ID=").Append(SalesRep_ID); } else { log.Warning("Not 1 User/Contact for C_BPartner_ID=" + m_com.GetC_BPartner_ID() + " but " + users.Length); sql.Append(" AND h.SalesRep_ID IN (SELECT AD_User_ID FROM AD_User WHERE C_BPartner_ID=") .Append(m_com.GetC_BPartner_ID()).Append(")"); } } //added by Arpit Rai on 7-May-2016 asked & Tested by Ravikant Sir //To calculate Commission Amount For the Particular Agent If Not Selected in Line Tab Of Commission else { MUser[] users = MUser.GetOfBPartner(GetCtx(), m_com.GetC_BPartner_ID()); if (users == null || users.Length == 0) { throw new Exception("Commission Business Partner has no Users/Contact"); } if (users.Length == 1) { int SaleRepID = users[0].GetAD_User_ID(); sql.Append(" AND h.SalesRep_ID = "); sql.Append(SaleRepID); } else { log.Warning("Not 1 User/Contact for C_BPartner_ID=" + m_com.GetC_BPartner_ID() + " but " + users.Length); sql.Append(" AND h.SalesRep_ID IN (SELECT AD_User_ID FROM AD_User WHERE C_BPartner_ID=") .Append(m_com.GetC_BPartner_ID()).Append(")"); } } // Organization if (lines[i].GetOrg_ID() != 0) { sql.Append(" AND h.AD_Org_ID=").Append(lines[i].GetOrg_ID()); } // BPartner if (lines[i].GetC_BPartner_ID() != 0) { sql.Append(" AND h.C_BPartner_ID=").Append(lines[i].GetC_BPartner_ID()); } // BPartner Group if (lines[i].GetC_BP_Group_ID() != 0) { sql.Append(" AND h.C_BPartner_ID IN " + "(SELECT C_BPartner_ID FROM C_BPartner WHERE C_BP_Group_ID=").Append(lines[i].GetC_BP_Group_ID()).Append(")"); } // Sales Region if (lines[i].GetC_SalesRegion_ID() != 0) { sql.Append(" AND h.C_BPartner_Location_ID IN " + "(SELECT C_BPartner_Location_ID FROM C_BPartner_Location WHERE C_SalesRegion_ID=").Append(lines[i].GetC_SalesRegion_ID()).Append(")"); } // Product if (lines[i].GetM_Product_ID() != 0) { sql.Append(" AND l.M_Product_ID=").Append(lines[i].GetM_Product_ID()); } // Product Category if (lines[i].GetM_Product_Category_ID() != 0) { sql.Append(" AND l.M_Product_ID IN " + "(SELECT M_Product_ID FROM M_Product WHERE M_Product_Category_ID=").Append(lines[i].GetM_Product_Category_ID()).Append(")"); } // Grouping if (!m_com.IsListDetails()) { sql.Append(" GROUP BY h.C_Currency_ID"); } // log.Fine("Line=" + lines[i].GetLine() + " - " + sql); // CreateDetail(sql.ToString(), comAmt); //comAmt.CalculateCommission(); comAmt.CalculatecommissionwithNewLogic(); comAmt.Save(); int countDetails = Util.GetValueOfInt(DataBase.DB.ExecuteScalar("SELECT COUNT(*) FROM C_CommissionDetail WHERE C_CommissionAmt_ID=" + comAmt.GetC_CommissionAmt_ID(), null, Get_TrxName())); if (countDetails == 0) { comAmt.Delete(true, Get_Trx()); } } // for all commission lines // comRun.updateFromAmt(); // comRun.save(); // Save Last Run m_com.SetDateLastRun(p_StartDate); m_com.Save(); return("@C_CommissionRun_ID@ = " + comRun.GetDocumentNo() + " - " + comRun.GetDescription()); }