/// <summary>
/// Get Rate
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Decimal GetRate(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int CurFrom_ID;
int CurTo_ID;
DateTime?convDate;
int ConversionType_ID;
int AD_Client_ID;
int AD_Org_ID;
//Assign parameter value
CurFrom_ID = Util.GetValueOfInt(paramValue[0].ToString());
CurTo_ID = Util.GetValueOfInt(paramValue[1].ToString());
// 18/7/2016 create only try catch block. if problem the get current date time
try {
convDate = System.Convert.ToDateTime(paramValue[2].ToString());
}
catch {
convDate = DateTime.Now;
}
//end 18/7/2016
ConversionType_ID = Util.GetValueOfInt(paramValue[3].ToString());
AD_Client_ID = Util.GetValueOfInt(paramValue[4].ToString());
AD_Org_ID = Util.GetValueOfInt(paramValue[5].ToString());
//End Assign parameter value
Decimal rate = MConversionRate.GetRate(CurFrom_ID, CurTo_ID, convDate, ConversionType_ID, AD_Client_ID, AD_Org_ID);
return(rate);
}
/// <summary>
/// Get Payment Detail
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Decimal GetPayment(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
//Assign parameter value
int c_payment_ID = Util.GetValueOfInt(paramValue[0]);
int CurTo_ID = Util.GetValueOfInt(paramValue[1]);
//Conversion based on Bank StatementLine Date
DateTime?convDate = Util.GetValueOfDateTime(paramValue[2]);
Decimal rate = 0;
//End Assign parameter value
string qry = "SELECT PayAmt, C_Currency_ID, C_ConversionType_ID, DateAcct FROM C_Payment_v WHERE C_Payment_ID=" + c_payment_ID;
DataSet ds = DB.ExecuteDataset(qry, null, null);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
decimal payAmt = Util.GetValueOfDecimal(ds.Tables[0].Rows[0][0]);
int c_currency_ID = Util.GetValueOfInt(ds.Tables[0].Rows[0][1]);
int c_conversionType_ID = Util.GetValueOfInt(ds.Tables[0].Rows[0][2]);
//DateTime? dateAcct = Util.GetValueOfDateTime(ds.Tables[0].Rows[0][3]); // JID_0333: Currency conversion should be based on Payment Account Date and Currency type
//rate = MConversionRate.Convert(ctx, payAmt, c_currency_ID, CurTo_ID, dateAcct, c_conversionType_ID, ctx.GetAD_Client_ID(), ctx.GetAD_Org_ID());
// Conversion Rate should be based on StatementLine Date Requirement by Ranvir
rate = MConversionRate.Convert(ctx, payAmt, c_currency_ID, CurTo_ID, convDate, c_conversionType_ID, ctx.GetAD_Client_ID(), ctx.GetAD_Org_ID());
}
return(rate);
}
/// <summary>
/// Convert
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Decimal CurrencyConvert(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int CurFrom_ID;
int CurTo_ID;
DateTime?convDate;
int ConversionType_ID;
int AD_Client_ID;
int AD_Org_ID;
//Assign parameter value
Decimal amt = Util.GetValueOfDecimal(paramValue[0].ToString());
CurFrom_ID = Util.GetValueOfInt(paramValue[1].ToString());
CurTo_ID = Util.GetValueOfInt(paramValue[2].ToString());
try
{
convDate = System.Convert.ToDateTime(paramValue[3].ToString());
}
catch
{
convDate = DateTime.Now;
}
ConversionType_ID = Util.GetValueOfInt(paramValue[4].ToString());
AD_Client_ID = Util.GetValueOfInt(paramValue[5].ToString());
AD_Org_ID = Util.GetValueOfInt(paramValue[6].ToString());
Decimal convert = MConversionRate.Convert(ctx, amt, CurFrom_ID, CurTo_ID, convDate, ConversionType_ID, AD_Client_ID, AD_Org_ID);
return(convert);
}
} // setC_Currency_ID
/// <summary>
/// Set Rate
/// </summary>
private void SetRate()
{
// Source info
int C_Currency_ID = GetC_Currency_ID();
int C_ConversionType_ID = GetC_ConversionType_ID();
if (C_Currency_ID == 0 || C_ConversionType_ID == 0)
{
return;
}
DateTime?DateAcct = GetDateAcct();
if (DateAcct == null)
{
DateAcct = DateTime.Now;// new Timestamp(System.currentTimeMillis());
}
//
int C_AcctSchema_ID = GetC_AcctSchema_ID();
MAcctSchema a = MAcctSchema.Get(GetCtx(), C_AcctSchema_ID);
int AD_Client_ID = GetAD_Client_ID();
int AD_Org_ID = GetAD_Org_ID();
Decimal?CurrencyRate = (Decimal?)MConversionRate.GetRate(C_Currency_ID, a.GetC_Currency_ID(),
DateAcct, C_ConversionType_ID, AD_Client_ID, AD_Org_ID);
log.Fine("rate = " + CurrencyRate);
//if (CurrencyRate.Value == null)
//{
// CurrencyRate = Env.ZERO;
//}
SetCurrencyRate(CurrencyRate.Value);
} // setRate
/// <summary>
/// Create Facts (the accounting logic) for
/// GLJ.
/// (only for the accounting scheme, it was created)
/// <pre>
/// account DR CR
/// </pre>
/// </summary>
/// <param name="?"></param>
/// <returns>fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
List <Fact> facts = new List <Fact>();
// Other Acct Schema
// need to Post GL Journal for Multiple Accounting Schema that's why commented this section
//if (as1.GetC_AcctSchema_ID() != _C_AcctSchema_ID)
//{
// return facts;
//}
// create Fact Header
Fact fact = new Fact(this, as1, _PostingType);
// get conversion rate from Assigned accounting schema tab -
Decimal conversionRate = Util.GetValueOfDecimal(DB.ExecuteScalar(@"SELECT CurrencyRate FROM GL_AssignAcctSchema WHERE
C_AcctSchema_ID = " + as1.GetC_AcctSchema_ID() + " AND GL_Journal_ID = " + record_Id, null, null));
// GLJ
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_GLJOURNAL))
{
// account DR CR
for (int i = 0; i < _lines.Length; i++)
{
// need to Post GL Journal for Multiple Accounting Schema that's why commented this condition
//if (_lines[i].GetC_AcctSchema_ID() == as1.GetC_AcctSchema_ID())
//{
// set conversion rate on line, so that amount to be converted based on that multiply rate
if (as1.GetC_AcctSchema_ID() != _C_AcctSchema_ID && _lines[i].GetC_Currency_ID() != as1.GetC_Currency_ID())
{
conversionRate = MConversionRate.GetRate(_lines[i].GetC_Currency_ID(), as1.GetC_Currency_ID(),
_lines[i].GetDateAcct(), _lines[i].GetC_ConversionType_ID(),
as1.GetAD_Client_ID(), _lines[i].GetAD_Org_ID());
_lines[i].SetConversionRate(conversionRate);
}
else if (as1.GetC_AcctSchema_ID() != _C_AcctSchema_ID)
{
_lines[i].SetConversionRate(conversionRate);
}
fact.CreateLine(_lines[i],
_lines[i].GetAccount(),
_lines[i].GetC_Currency_ID(),
_lines[i].GetAmtSourceDr(),
_lines[i].GetAmtSourceCr());
//}
} // for all lines
}
else
{
_error = "DocumentType unknown: " + GetDocumentType();
log.Log(Level.SEVERE, _error);
fact = null;
}
//
facts.Add(fact);
return(facts);
}
/// <summary>
/// Get Payment Detail
/// </summary>
/// <param name="ctx">Context</param>
/// <param name="fields">String Fields</param>
/// <returns>Decimal Converted Amount</returns>
public Decimal GetConvertedAmt(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
//Assign parameter value
int c_payment_ID = Util.GetValueOfInt(paramValue[0]);
int CurTo_ID = Util.GetValueOfInt(paramValue[1]);
DateTime?convDate = Util.GetValueOfDateTime(paramValue[2]);
Decimal rate = 0;
//End Assign parameter value
string qry = "SELECT PayAmt, C_Currency_ID, C_ConversionType_ID FROM C_Payment_v WHERE C_Payment_ID=" + c_payment_ID;
DataSet ds = DB.ExecuteDataset(qry, null, null);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
decimal payAmt = Util.GetValueOfDecimal(ds.Tables[0].Rows[0][0]);
int c_currency_ID = Util.GetValueOfInt(ds.Tables[0].Rows[0][1]);
int c_conversionType_ID = Util.GetValueOfInt(ds.Tables[0].Rows[0][2]);
rate = MConversionRate.Convert(ctx, payAmt, c_currency_ID, CurTo_ID, convDate, c_conversionType_ID, ctx.GetAD_Client_ID(), ctx.GetAD_Org_ID());
}
return(rate);
}
/// <summary>
/// Get Rate
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Decimal GetRate(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int CurFrom_ID;
int CurTo_ID;
DateTime?convDate;
int ConversionType_ID;
int AD_Client_ID;
int AD_Org_ID;
//Assign parameter value
CurFrom_ID = Util.GetValueOfInt(paramValue[0].ToString());
CurTo_ID = Util.GetValueOfInt(paramValue[1].ToString());
convDate = System.Convert.ToDateTime(paramValue[2].ToString());
ConversionType_ID = Util.GetValueOfInt(paramValue[3].ToString());
AD_Client_ID = Util.GetValueOfInt(paramValue[4].ToString());
AD_Org_ID = Util.GetValueOfInt(paramValue[5].ToString());
//End Assign parameter value
Decimal rate = MConversionRate.GetRate(CurFrom_ID, CurTo_ID, convDate, ConversionType_ID, AD_Client_ID, AD_Org_ID);
return(rate);
}
/// <summary>
/// Convert
/// </summary>
/// <param name="ctx"></param>
/// <param name="fields"></param>
/// <returns></returns>
public Decimal Convert(Ctx ctx, string fields)
{
string[] paramValue = fields.Split(',');
int CurFrom_ID;
int CurTo_ID;
//DateTime? convDate;
//int ConversionType_ID;
int AD_Client_ID;
int AD_Org_ID;
//Assign parameter value
Decimal amt = Util.GetValueOfDecimal(paramValue[0].ToString());
CurFrom_ID = Util.GetValueOfInt(paramValue[1].ToString());
CurTo_ID = Util.GetValueOfInt(paramValue[2].ToString());
//CurTo_ID = Util.GetValueOfInt(paramValue[1].ToString());
//CurFrom_ID = Util.GetValueOfInt(paramValue[2].ToString());
AD_Client_ID = Util.GetValueOfInt(paramValue[3].ToString());
AD_Org_ID = Util.GetValueOfInt(paramValue[4].ToString());
Decimal convert = MConversionRate.Convert(ctx, amt, CurFrom_ID, CurTo_ID, AD_Client_ID, AD_Org_ID);
return(convert);
}
/// <summary>
/// Create Invoice Line from Shipment
/// </summary>
/// <param name="order">order</param>
/// <param name="ship">shipment header</param>
/// <param name="sLine">shipment line</param>
private void CreateLine(MOrder order, MInOut ship, MInOutLine sLine)
{
if (_invoice == null)
{
_invoice = new MInvoice(order, 0, _DateInvoiced);
int _CountVA009 = Env.IsModuleInstalled("VA009_") ? 1 : 0;
if (_CountVA009 > 0)
{
int _PaymentMethod_ID = order.GetVA009_PaymentMethod_ID();
// during consolidation, payment method need to set that is defined on selected business partner.
// If not defined on BP then it will set from order
// during sale cycle -- VA009_PaymentMethod_ID
// during purchase cycle -- VA009_PO_PaymentMethod_ID
int bpPamentMethod_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT " + (order.IsSOTrx() ? " VA009_PaymentMethod_ID " : " VA009_PO_PaymentMethod_ID ") +
@" FROM C_BPartner WHERE C_BPartner_ID = " + order.GetC_BPartner_ID(), null, Get_Trx()));
if (_ConsolidateDocument && bpPamentMethod_ID != 0)
{
_PaymentMethod_ID = bpPamentMethod_ID;
}
if (_PaymentMethod_ID > 0)
{
_invoice.SetVA009_PaymentMethod_ID(_PaymentMethod_ID);
}
}
int _CountVA026 = Env.IsModuleInstalled("VA026_") ? 1 : 0;
if (_CountVA026 > 0)
{
_invoice.SetVA026_LCDetail_ID(order.GetVA026_LCDetail_ID());
}
// Added by Bharat on 29 Jan 2018 to set Inco Term from Order
if (_invoice.Get_ColumnIndex("C_IncoTerm_ID") > 0)
{
_invoice.SetC_IncoTerm_ID(order.GetC_IncoTerm_ID());
}
if (Env.IsModuleInstalled("VA077_"))
{
//.setva077
_invoice.Set_Value("VA077_AdditionalAnnualCharge", order.Get_Value("VA077_AdditionalAnnualCharge"));
_invoice.Set_Value("VA077_ChangeStartDate", order.Get_Value("VA077_ChangeStartDate"));
_invoice.Set_Value("VA077_ContractCPEndDate", order.Get_Value("VA077_ContractCPEndDate"));
_invoice.Set_Value("VA077_ContractCPStartDate", order.Get_Value("VA077_ContractCPStartDate"));
_invoice.Set_Value("VA077_HistoricContractDate", order.Get_Value("VA077_HistoricContractDate"));
_invoice.Set_Value("VA077_IsLegalEntity", order.Get_Value("VA077_IsLegalEntity"));
_invoice.Set_Value("VA077_MarginPercent", order.Get_Value("VA077_MarginPercent"));
_invoice.Set_Value("VA077_NewAnnualContractTotal", order.Get_Value("VA077_NewAnnualContractTotal"));
_invoice.Set_Value("VA077_OldAnnualContractTotal", order.Get_Value("VA077_OldAnnualContractTotal"));
_invoice.Set_Value("VA077_PartialAmtCatchUp", order.Get_Value("VA077_PartialAmtCatchUp"));
_invoice.Set_Value("VA077_SalesCoWorker", order.Get_Value("VA077_SalesCoWorker"));
_invoice.Set_Value("VA077_SalesCoWorkerPer", order.Get_Value("VA077_SalesCoWorkerPer"));
_invoice.Set_Value("VA077_TotalMarginAmt", order.Get_Value("VA077_TotalMarginAmt"));
_invoice.Set_Value("VA077_TotalPurchaseAmt", order.Get_Value("VA077_TotalPurchaseAmt"));
_invoice.Set_Value("VA077_TotalSalesAmt", order.Get_Value("VA077_TotalSalesAmt"));
}
if (!_invoice.Save())
{
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Could not create Invoice (s). " + pp.GetName());
}
throw new Exception("Could not create Invoice (s)");
}
}
#region Comment Create Shipment Comment Line
// Create Shipment Comment Line
//if (_ship == null
// || _ship.GetM_InOut_ID() != ship.GetM_InOut_ID())
//{
// MDocType dt = MDocType.Get(GetCtx(), ship.GetC_DocType_ID());
// if (_bp == null || _bp.GetC_BPartner_ID() != ship.GetC_BPartner_ID())
// {
// _bp = new MBPartner(GetCtx(), ship.GetC_BPartner_ID(), Get_TrxName());
// }
// // Reference: Delivery: 12345 - 12.12.12
// MClient client = MClient.Get(GetCtx(), order.GetAD_Client_ID());
// String AD_Language = client.GetAD_Language();
// if (client.IsMultiLingualDocument() && _bp.GetAD_Language() != null)
// {
// AD_Language = _bp.GetAD_Language();
// }
// if (AD_Language == null)
// {
// // MessageBox.Show("Set base Language");
// //AD_Language = Language.getBaseAD_Language();
// }
// //java.text.SimpleDateFormat format = DisplayType.getDateFormat
// // (DisplayType.Date, Language.getLanguage(AD_Language));
// //String reference = dt.GetPrintName(_bp.GetAD_Language())
// // + ": " + ship.GetDocumentNo()
// // + " - " + format.format(ship.GetMovementDate());
// String reference = dt.GetPrintName(_bp.GetAD_Language())
// + ": " + ship.GetDocumentNo()
// + " - " + ship.GetMovementDate();
// _ship = ship;
// //
// MInvoiceLine line = new MInvoiceLine(_invoice);
// line.SetIsDescription(true);
// line.SetDescription(reference);
// line.SetLine(_line + sLine.GetLine() - 2);
// if (!line.Save())
// {
// throw new Exception("Could not create Invoice Comment Line (sh)");
// }
// // Optional Ship Address if not Bill Address
// if (order.GetBill_Location_ID() != ship.GetC_BPartner_Location_ID())
// {
// MLocation addr = MLocation.GetBPLocation(GetCtx(), ship.GetC_BPartner_Location_ID(), null);
// line = new MInvoiceLine(_invoice);
// line.SetIsDescription(true);
// line.SetDescription(addr.ToString());
// line.SetLine(_line + sLine.GetLine() - 1);
// if (!line.Save())
// {
// throw new Exception("Could not create Invoice Comment Line 2 (sh)");
// }
// }
//}
#endregion
//
MInvoiceLine line1 = new MInvoiceLine(_invoice);
line1.SetShipLine(sLine);
line1.SetQtyEntered(sLine.GetQtyEntered());
line1.SetQtyInvoiced(sLine.GetMovementQty());
line1.SetLine(_line + sLine.GetLine());
line1.SetM_AttributeSetInstance_ID(sLine.GetM_AttributeSetInstance_ID());
if (sLine.GetA_Asset_ID() > 0)
{
line1.SetA_Asset_ID(sLine.GetA_Asset_ID());
if (line1.Get_ColumnIndex("VAFAM_AssetCost") > 0)
{
int PAcctSchema_ID = 0;
int pCurrency_ID = 0;
MAcctSchema as1 = MClient.Get(GetCtx(), GetAD_Client_ID()).GetAcctSchema();
if (as1 != null)
{
PAcctSchema_ID = as1.GetC_AcctSchema_ID();
pCurrency_ID = as1.GetC_Currency_ID();
}
decimal LineNetAmt = Decimal.Multiply(line1.GetPriceActual(), line1.GetQtyEntered());
decimal AssetCost = GetAssetCost(sLine.GetA_Asset_ID(), sLine.GetM_Product_ID(), PAcctSchema_ID);
AssetCost = Decimal.Multiply(AssetCost, line1.GetQtyEntered());
if (LineNetAmt > 0)
{
LineNetAmt = MConversionRate.Convert(GetCtx(), LineNetAmt, _invoice.GetC_Currency_ID(), pCurrency_ID, _invoice.GetAD_Client_ID(), _invoice.GetAD_Org_ID());
}
decimal Diff = LineNetAmt - AssetCost;
line1.Set_Value("VAFAM_AssetCost", AssetCost);
line1.Set_Value("VAFAM_Difference", Diff);
}
}
if (Env.IsModuleInstalled("VA077_"))
{
//invoice line
line1.Set_Value("VA077_CNAutodesk", sLine.Get_Value("VA077_CNAutodesk"));
line1.Set_Value("VA077_Duration", sLine.Get_Value("VA077_Duration"));
line1.Set_Value("VA077_MarginAmt", sLine.Get_Value("VA077_MarginAmt"));
line1.Set_Value("VA077_MarginPercent", sLine.Get_Value("VA077_MarginPercent"));
line1.Set_Value("VA077_OldSN", sLine.Get_Value("VA077_OldSN"));
line1.Set_Value("VA077_ProductInfo", sLine.Get_Value("VA077_ProductInfo"));
line1.Set_Value("VA077_PurchasePrice", sLine.Get_Value("VA077_PurchasePrice"));
line1.Set_Value("VA077_RegEmail", sLine.Get_Value("VA077_RegEmail"));
line1.Set_Value("VA077_SerialNo", sLine.Get_Value("VA077_SerialNo"));
line1.Set_Value("VA077_UpdateFromVersn", sLine.Get_Value("VA077_UpdateFromVersn"));
line1.Set_Value("VA077_UserRef_ID", sLine.Get_Value("VA077_UserRef_ID"));
line1.Set_Value("VA077_EndDate", sLine.Get_Value("VA077_EndDate"));
line1.Set_Value("VA077_StartDate", sLine.Get_Value("VA077_StartDate"));
}
if (!line1.Save())
{
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Could not create Invoice Line (s). " + pp.GetName());
}
throw new Exception("Could not create Invoice Line (s)");
}
// Link
sLine.SetIsInvoiced(true);
if (!sLine.Save())
{
ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError();
if (pp != null)
{
throw new ArgumentException("Could not update Shipment Line. " + pp.GetName());
}
throw new Exception("Could not update Shipment Line");
}
log.Fine(line1.ToString());
}
/// <summary>
/// Apply Payment Term with schedule to order
/// </summary>
/// <param name="order">Order</param>
/// <param name="_sch">Payment Term Schedule</param>
private void ApplyAdvanceSchedule(MOrder order, MPaySchedule _sch)
{
MVA009OrderPaySchedule ips = null;
Decimal remainder = order.GetGrandTotal();
#region IsAdvance true on Schedule
if (_sch.IsVA009_Advance())
{
ips = new MVA009OrderPaySchedule(order, _sch);
ips.SetVA009_ExecutionStatus("A");
MOrder _Order = new MOrder(GetCtx(), order.GetC_Order_ID(), Get_TrxName());
ips.SetVA009_PaymentMethod_ID(order.GetVA009_PaymentMethod_ID());
ips.SetC_PaymentTerm_ID(order.GetC_PaymentTerm_ID());
ips.SetVA009_GrandTotal(order.GetGrandTotal());
MPaymentTerm payterm = new MPaymentTerm(GetCtx(), order.GetC_PaymentTerm_ID(), Get_TrxName());
int _graceDay = payterm.GetGraceDays();
ips.SetVA009_FollowupDate(ips.GetDueDate().Value.AddDays(_graceDay));
ips.SetVA009_PlannedDueDate(ips.GetDueDate());
int BaseCurrency = GetCtx().GetContextAsInt("$C_Currency_ID");
if (BaseCurrency != order.GetC_Currency_ID())
{
decimal multiplyRate = MConversionRate.GetRate(order.GetC_Currency_ID(), BaseCurrency, order.GetDateAcct(), order.GetC_ConversionType_ID(), order.GetAD_Client_ID(), order.GetAD_Org_ID());
ips.SetVA009_OpenAmnt(ips.GetDueAmt() * multiplyRate);
}
else
{
ips.SetVA009_OpenAmnt(ips.GetDueAmt());
}
// Get Next Business Day if Next Business Days check box is set to true
DateTime?payDueDate = null;
if (payterm.IsNextBusinessDay())
{
payDueDate = payterm.GetNextBusinessDate(ips.GetDueDate());
ips.SetDueDate(payDueDate);
payDueDate = payterm.GetNextBusinessDate(ips.GetDiscountDate());
ips.SetDiscountDate(payDueDate);
}
ips.SetC_Currency_ID(order.GetC_Currency_ID());
ips.SetVA009_BseCurrncy(BaseCurrency);
ips.SetVA009_OpnAmntInvce(ips.GetDueAmt());
ips.SetC_BPartner_ID(order.GetC_BPartner_ID());
string sql = "SELECT VA009_PaymentMode, VA009_PaymentType, VA009_PaymentTrigger FROM VA009_PaymentMethod WHERE VA009_PaymentMethod_ID="
+ order.GetVA009_PaymentMethod_ID() + " AND IsActive = 'Y' AND AD_Client_ID = " + order.GetAD_Client_ID();
DataSet ds = new DataSet();
ds = DB.ExecuteDataset(sql);
if (ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
ips.SetVA009_PaymentMode(Util.GetValueOfString(ds.Tables[0].Rows[0]["VA009_PaymentMode"]));
if (!String.IsNullOrEmpty(Convert.ToString(ds.Tables[0].Rows[0]["VA009_PaymentType"])))
{
ips.SetVA009_PaymentType(Util.GetValueOfString(ds.Tables[0].Rows[0]["VA009_PaymentType"]));
}
ips.SetVA009_PaymentTrigger(Util.GetValueOfString(ds.Tables[0].Rows[0]["VA009_PaymentTrigger"]));
ips.SetVA009_ExecutionStatus("A");
}
ips.SetProcessed(true);
ips.Save(order.Get_TrxName());
log.Fine(ips.ToString());
remainder = Decimal.Subtract(remainder, ips.GetDueAmt());
}
#endregion
}
/// <summary>
/// Apply Payment Term without schedule to Order
/// </summary>
/// <param name="order">Order</param>
private void ApplyAdvanceTermSchedule(MOrder order)
{
//for(int i=0;i<_schedule.Length;i++))
StringBuilder _sql = new StringBuilder();
Decimal remainder = order.GetGrandTotal();
MVA009OrderPaySchedule schedule = new MVA009OrderPaySchedule(GetCtx(), 0, Get_TrxName());
MPaymentTerm payterm = new MPaymentTerm(GetCtx(), order.GetC_PaymentTerm_ID(), Get_TrxName());
schedule.SetAD_Client_ID(order.GetAD_Client_ID());
schedule.SetAD_Org_ID(order.GetAD_Org_ID());
schedule.SetC_Order_ID(order.GetC_Order_ID());
schedule.SetC_PaymentTerm_ID(order.GetC_PaymentTerm_ID());
schedule.SetVA009_PaymentMethod_ID(order.GetVA009_PaymentMethod_ID());
//schedule.SetDueDate(GetDueDate(order));
// Get Next Business Day if Next Business Days check box is set to true
DateTime?payDueDate = null;
if (payterm.IsNextBusinessDay())
{
payDueDate = payterm.GetNextBusinessDate(TimeUtil.AddDays(order.GetDateOrdered(), payterm.GetNetDays()));
}
else
{
payDueDate = TimeUtil.AddDays(order.GetDateOrdered(), payterm.GetNetDays());
}
schedule.SetDueDate(payDueDate);
schedule.SetDueAmt(order.GetGrandTotal());
if (payterm.IsNextBusinessDay())
{
payDueDate = payterm.GetNextBusinessDate(TimeUtil.AddDays(order.GetDateOrdered(), payterm.GetDiscountDays()));
}
else
{
payDueDate = TimeUtil.AddDays(order.GetDateOrdered(), payterm.GetDiscountDays());
}
schedule.SetDiscountDate(payDueDate);
//schedule.SetDiscountDate(order.GetDateOrdered().Value.AddDays(Util.GetValueOfInt(payterm.GetDiscountDays())));
schedule.SetDiscountAmt((Util.GetValueOfDecimal((order.GetGrandTotal() * payterm.GetDiscount()) / 100)));
if (payterm.IsNextBusinessDay())
{
payDueDate = payterm.GetNextBusinessDate(TimeUtil.AddDays(order.GetDateOrdered(), payterm.GetDiscountDays2()));
}
else
{
payDueDate = TimeUtil.AddDays(order.GetDateOrdered(), payterm.GetDiscountDays2());
}
schedule.SetDiscountDays2(payDueDate);
//schedule.SetDiscountDays2(order.GetDateOrdered().Value.AddDays(Util.GetValueOfInt(payterm.GetDiscountDays2())));
schedule.SetDiscount2((Util.GetValueOfDecimal((order.GetGrandTotal() * payterm.GetDiscount2()) / 100)));
schedule.SetVA009_PlannedDueDate(GetDueDate(order));
int BaseCurrency = GetCtx().GetContextAsInt("$C_Currency_ID");
if (BaseCurrency != order.GetC_Currency_ID())
{
decimal multiplyRate = MConversionRate.GetRate(order.GetC_Currency_ID(), BaseCurrency, order.GetDateAcct(), order.GetC_ConversionType_ID(), order.GetAD_Client_ID(), order.GetAD_Org_ID());
schedule.SetVA009_OpenAmnt(order.GetGrandTotal() * multiplyRate);
}
else
{
schedule.SetVA009_OpenAmnt(order.GetGrandTotal());
}
schedule.SetC_Currency_ID(order.GetC_Currency_ID());
schedule.SetVA009_BseCurrncy(BaseCurrency);
schedule.SetVA009_OpnAmntInvce(order.GetGrandTotal());
schedule.SetC_BPartner_ID(order.GetC_BPartner_ID());
MOrder _Order = new MOrder(GetCtx(), order.GetC_Order_ID(), Get_TrxName());
//schedule.SetVA009_PaymentMethod_ID(_Order.GetVA009_PaymentMethod_ID());
//schedule.SetC_PaymentTerm_ID(_Order.GetC_PaymentTerm_ID());
int _graceDay = payterm.GetGraceDays();
DateTime?_followUpDay = GetDueDate(order);
schedule.SetVA009_FollowupDate(_followUpDay.Value.AddDays(_graceDay));
_sql.Clear();
_sql.Append("SELECT VA009_PaymentMode, VA009_PaymentType, VA009_PaymentTrigger FROM VA009_PaymentMethod WHERE VA009_PaymentMethod_ID="
+ order.GetVA009_PaymentMethod_ID() + " AND IsActive = 'Y' AND AD_Client_ID = " + order.GetAD_Client_ID());
DataSet ds = new DataSet();
ds = DB.ExecuteDataset(_sql.ToString());
if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
schedule.SetVA009_PaymentMode(Util.GetValueOfString(ds.Tables[0].Rows[0]["VA009_PaymentMode"]));
schedule.SetVA009_PaymentType(Util.GetValueOfString(ds.Tables[0].Rows[0]["VA009_PaymentType"]));
schedule.SetVA009_PaymentTrigger(Util.GetValueOfString(ds.Tables[0].Rows[0]["VA009_PaymentTrigger"]));
schedule.SetVA009_ExecutionStatus("A");
}
// updateInvoice
if (!schedule.Save())
{
ValueNamePair pp = VLogger.RetrieveError();
log.Info("Error found during creation of Order Schedule against Order ID = " + order.GetC_Order_ID() +
" Error Name is " + pp.GetName());
}
}
/// <summary>
/// Create Facts (the accounting logic) for
/// CMC.
/// <pre>
/// Expense
/// CashExpense DR
/// CashAsset CR
///Receipt
/// CashAsset DR
/// CashReceipt CR
/// Charge
/// Charge DR
/// CashAsset CR
/// Difference
/// CashDifference DR
/// CashAsset CR
/// Invoice
/// CashAsset DR
/// CashTransfer CR
/// Transfer
/// BankInTransit DR
/// CashAsset CR
/// </pre>
/// </summary>
/// <param name="?"></param>
/// <returns>Fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// Need to have CashBook
if (GetC_CashBook_ID() == 0)
{
_error = "C_CashBook_ID not set";
log.Log(Level.SEVERE, _error);
return(null);
}
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
// Header posting amt as1 Invoices and Transfer could be differenet currency
// CashAsset Total
Decimal assetAmt = Env.ZERO;
Decimal TotalCurrLoss = Env.ZERO;
Decimal TotalCurrGain = Env.ZERO;
int headerCashCurrency = 0;
int childCashCurrency = 0;
int headerCashOrg = 0;
int childCashOrg = 0;
// Lines
for (int i = 0; i < _lines.Length; i++)
{
DocLine_Cash line = (DocLine_Cash)_lines[i];
String CashType = line.GetCashType();
if (CashType.Equals(DocLine_Cash.CASHTYPE_EXPENSE))
{ // amount is negative
// CashExpense DR
// CashAsset CR
fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashExpense, as1),
GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null);
// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// p_vo.C_Currency_ID, null, line.GetAmount().negate());
assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount()));
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_RECEIPT))
{ // amount is positive
// CashAsset DR
// CashReceipt CR
// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// p_vo.C_Currency_ID, line.GetAmount(), null);
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashReceipt, as1),
GetC_Currency_ID(), null, line.GetAmount());
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_CHARGE))
{ // amount is negative
// Charge DR
// CashAsset CR
fact.CreateLine(line, line.GetChargeAccount(as1, GetAmount()),
GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null);
// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// p_vo.C_Currency_ID, null, line.GetAmount().negate());
assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount()));
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_DIFFERENCE))
{ // amount is pos/neg
// CashDifference DR
// CashAsset CR
fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashDifference, as1),
GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// p_vo.C_Currency_ID, line.GetAmount());
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_INVOICE))
{ // amount is pos/neg
// CashAsset DR dr -- Invoice is in Invoice Currency !
// CashTransfer cr CR
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashAsset, as1),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_TRANSFER))
{ // amount is pos/neg
// BankInTransit DR dr -- Transfer is in Bank Account Currency
// CashAsset dr CR
int temp = GetC_BankAccount_ID();
SetC_BankAccount_ID(line.GetC_BankAccount_ID());
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_BankInTransit, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
SetC_BankAccount_ID(temp);
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashAsset, as1),
line.GetC_Currency_ID(), line.GetAmount());
}
}
// Change to Apply Posting Logic against BusinessPartner
else if (CashType.Equals(DocLine_Cash.CASHTYPE_BUSINESSPARTNER))
{ // amount is pos/neg
// CashAsset DR dr -- Invoice is in Invoice Currency !
// CashTransfer cr CR
MBPartner bPartner = new MBPartner(GetCtx(), line.GetC_BPartner_ID(), null);
if (bPartner != null)
{
if (bPartner.IsEmployee())
{
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_E_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_E_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
else if (bPartner.IsVendor())
{
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_V_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_V_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
else if (bPartner.IsCustomer())
{
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_C_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_C_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
}
else
{
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashAsset, as1),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
}
//Code Added to CashBookTransfer
else if (CashType.Equals(DocLine_Cash.CASHTYPE_CASHBOOKTRANSFER))
{
//// amount is negative
//// Charge DR
//// CashAsset CR
//fact.CreateLine(line, line.GetChargeAccount(as1, GetAmount()),
// GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null);
//// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
//// p_vo.C_Currency_ID, null, line.GetAmount().negate());
//assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount()));
int temp = GetC_CashBook_ID();
SetC_CashBook_ID(line.Get_C_CashBook_ID());
if (BeginningBalance > 0)
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
SetC_CashBook_ID(temp);
//if (line.GetC_Currency_ID() == (new MCashBook(GetCtx(), GetC_CashBook_ID(), null)).GetC_Currency_ID())// GetC_Currency_ID())
//{
//if (BeginningBalance > assetAmt)
//if (BeginningBalance >= Math.Abs(line.GetAmount()))
//if (assetAmt <= Math.Abs(line.GetAmount()))
//{
assetAmt = (Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount())));
//}
//else
//{
// assetAmt = (Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount())));
//}
//}
//else
//{
// fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
//}
}
else
{
//fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
// line.GetC_Currency_ID(), decimal.Negate(line.GetAmount()));
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
(new MCashBook(GetCtx(), HeaderCasbookID, null)).GetC_Currency_ID(), decimal.Negate(line.GetAmount()));
SetC_CashBook_ID(temp);
//if (line.GetC_Currency_ID() == (new MCashBook(GetCtx(), GetC_CashBook_ID(), null)).GetC_Currency_ID())// GetC_Currency_ID())
//{
assetAmt = (Decimal.Add(assetAmt, line.GetAmount()));
//}
//else
//{
// fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
//}
}
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_CASHRECIEVEDFROM))
{
int temp = GetC_CashBook_ID();
//SetC_CashBook_ID(line.Get_C_CashBook_ID());
//fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
// line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
//SetC_CashBook_ID(temp);
//if (line.GetC_Currency_ID() == GetC_Currency_ID())
//{
//assetAmt = (Decimal.Add(assetAmt, line.GetAmount()));
//}
//else
//{
// fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// line.GetC_Currency_ID(), line.GetAmount());
//}
headerCashCurrency = (new MCashBook(GetCtx(), HeaderCasbookID, null).GetC_Currency_ID());
childCashCurrency = (new MCashBook(GetCtx(), line.Get_C_CashBook_ID(), null).GetC_Currency_ID());
headerCashOrg = (new MCashBook(GetCtx(), HeaderCasbookID, null).GetAD_Org_ID());
childCashOrg = (new MCashBook(GetCtx(), line.Get_C_CashBook_ID(), null).GetAD_Org_ID());
//else
//{
if (headerCashCurrency != childCashCurrency)
{
Decimal transferdAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT Amount FROM C_CashLine WHERE C_CashLine_ID =" + line.Get_C_CashLine_Ref_ID()));
Decimal recievedAmt = MConversionRate.Convert(GetCtx(), line.GetAmount(), headerCashCurrency, childCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
Decimal res = Decimal.Subtract(recievedAmt, Math.Abs(transferdAmt));
if (res > 0)
{
TotalCurrGain = Decimal.Add(TotalCurrGain, res);
}
else if (res < 0)
{
TotalCurrLoss = Decimal.Add(TotalCurrLoss, Decimal.Negate(res));
}
SetC_CashBook_ID(line.Get_C_CashBook_ID());
int OrgID = line.GetAD_Org_ID();
if (headerCashOrg != childCashOrg)
{
OrgID = childCashOrg;
}
//else
//{
transferdAmt = MConversionRate.Convert(GetCtx(), transferdAmt, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
//}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
headerCashCurrency, transferdAmt, OrgID);
SetC_CashBook_ID(temp);
}
else
{
SetC_CashBook_ID(line.Get_C_CashBook_ID());
if (headerCashOrg != childCashOrg)
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), childCashOrg);
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
SetC_CashBook_ID(temp);
}
//if (headerCashOrg != childCashOrg)
//{
// DataSet ds = DB.ExecuteDataset("SELECT INTERCOMPANYDUETO_ACCT,INTERCOMPANYDUEFROM_ACCT FROM C_AcctSchema_GL WHERE AD_Client_ID=" + GetAD_Client_ID());
// int dueFrom = 0;
// int dueTo = 0;
// if (ds != null && ds.Tables[0].Rows.Count > 0)
// {
// dueTo = Util.GetValueOfInt(ds.Tables[0].Rows[0]["INTERCOMPANYDUETO_ACCT"]);
// dueFrom = Util.GetValueOfInt(ds.Tables[0].Rows[0]["INTERCOMPANYDUEFROM_ACCT"]);
// if (dueFrom > 0 && dueTo > 0)
// {
// fact.CreateLine(line, MAccount.Get(GetCtx(), dueFrom),line.GetC_Currency_ID(), line.GetAmount());
// fact.CreateLine(line, MAccount.Get(GetCtx(), dueTo), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
// }
// }
//}
//}
assetAmt = (Decimal.Add(assetAmt, line.GetAmount()));
}
} // lines
if (TotalCurrGain != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='80540' AND AD_Client_ID = " + GetAD_Client_ID() + " )"));
MAccount acct = MAccount.Get(GetCtx(), validComID);
TotalCurrGain = MConversionRate.Convert(GetCtx(), TotalCurrGain, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
fact.CreateLine(null, acct,
GetC_Currency_ID(), Decimal.Negate(TotalCurrGain));
}
if (TotalCurrLoss != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )"));
MAccount acct = MAccount.Get(GetCtx(), validComID);
TotalCurrLoss = MConversionRate.Convert(GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
fact.CreateLine(null, acct,
GetC_Currency_ID(), (TotalCurrLoss));
}
//
// Cash Asset
fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
GetC_Currency_ID(), assetAmt);
List <Fact> facts = new List <Fact>();
facts.Add(fact);
return(facts);
}
} // doIt
/// <summary>
/// Process Expense Line
/// </summary>
/// <param name="te">header</param>
/// <param name="tel">line</param>
/// <param name="bp">bp</param>
private void ProcessLine(MTimeExpense te, MTimeExpenseLine tel, MBPartner bp)
{
if (_order == null)
{
log.Info("New Order for " + bp + ", Project=" + tel.GetC_Project_ID());
_order = new MOrder(GetCtx(), 0, Get_TrxName());
_order.SetAD_Org_ID(tel.GetAD_Org_ID());
_order.SetC_DocTypeTarget_ID(MOrder.DocSubTypeSO_Standard);
//
_order.SetBPartner(bp);
if (_order.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
_order = null;
return;
}
_order.SetM_Warehouse_ID(te.GetM_Warehouse_ID());
////Added By Arpit asked by Surya Sir..................29-12-2015
//_order.SetSalesRep_ID(GetCtx().GetAD_User_ID());
//End
if (tel.GetC_Activity_ID() != 0)
{
_order.SetC_Activity_ID(tel.GetC_Activity_ID());
}
if (tel.GetC_Campaign_ID() != 0)
{
_order.SetC_Campaign_ID(tel.GetC_Campaign_ID());
}
if (tel.GetC_Project_ID() != 0)
{
_order.SetC_Project_ID(tel.GetC_Project_ID());
// Optionally Overwrite BP Price list from Project
MProject project = new MProject(GetCtx(), tel.GetC_Project_ID(), Get_TrxName());
if (project.GetM_PriceList_ID() != 0)
{
_order.SetM_PriceList_ID(project.GetM_PriceList_ID());
}
}
_order.SetSalesRep_ID(te.GetDoc_User_ID());
//
if (!_order.Save())
{
throw new Exception("Cannot save Order");
}
}
else
{
// Update Header info
if (tel.GetC_Activity_ID() != 0 && tel.GetC_Activity_ID() != _order.GetC_Activity_ID())
{
_order.SetC_Activity_ID(tel.GetC_Activity_ID());
}
if (tel.GetC_Campaign_ID() != 0 && tel.GetC_Campaign_ID() != _order.GetC_Campaign_ID())
{
_order.SetC_Campaign_ID(tel.GetC_Campaign_ID());
}
if (!_order.Save())
{
new Exception("Cannot save Order");
}
}
// OrderLine
MOrderLine ol = new MOrderLine(_order);
//
if (tel.GetM_Product_ID() != 0)
{
ol.SetM_Product_ID(tel.GetM_Product_ID(),
tel.GetC_UOM_ID());
}
if (tel.GetS_ResourceAssignment_ID() != 0)
{
ol.SetS_ResourceAssignment_ID(tel.GetS_ResourceAssignment_ID());
}
// Set charge ID
if (tel.GetC_Charge_ID() != 0)
{
ol.SetC_Charge_ID(tel.GetC_Charge_ID());
ol.SetPriceActual(tel.GetExpenseAmt());
ol.SetQty(tel.GetQty());
}
ol.SetQty(tel.GetQtyInvoiced()); //
ol.SetDescription(tel.GetDescription());
//
ol.SetC_Project_ID(tel.GetC_Project_ID());
ol.SetC_ProjectPhase_ID(tel.GetC_ProjectPhase_ID());
ol.SetC_ProjectTask_ID(tel.GetC_ProjectTask_ID());
ol.SetC_Activity_ID(tel.GetC_Activity_ID());
ol.SetC_Campaign_ID(tel.GetC_Campaign_ID());
//
Decimal price = tel.GetPriceInvoiced(); //
if (price.CompareTo(Env.ZERO) != 0)
{
if (tel.GetC_Currency_ID() != _order.GetC_Currency_ID())
{
price = MConversionRate.Convert(GetCtx(), price,
tel.GetC_Currency_ID(), _order.GetC_Currency_ID(),
_order.GetAD_Client_ID(), _order.GetAD_Org_ID());
}
ol.SetPrice(price);
}
else
{
ol.SetPrice();
}
if (tel.GetC_UOM_ID() != 0 && ol.GetC_UOM_ID() == 0)
{
ol.SetC_UOM_ID(tel.GetC_UOM_ID());
}
ol.SetTax();
if (!ol.Save())
{
throw new Exception("Cannot save Order Line");
}
// Update TimeExpense Line
tel.SetC_OrderLine_ID(ol.GetC_OrderLine_ID());
if (tel.Save())
{
log.Fine("Updated " + tel + " with C_OrderLine_ID");
}
else
{
log.Log(Level.SEVERE, "Not Updated " + tel + " with C_OrderLine_ID");
}
} // processLine
/// <SUMmary>
/// Sales Order Products
/// </SUMmary>
/// <returns>No of lines created</returns>
private int SalesOrderProducts()
{
sql = "SELECT ol.m_product_id,ol.QtyOrdered,M_AttributeSetInstance_ID,ol.C_UOM_ID," +
" ol.C_OrderLine_ID,o.C_Order_ID,(NVL(PriceEntered,0) * NVL(QtyEntered,0)) AS Price,o.C_Currency_ID FROM C_Order o " +
" INNER JOIN C_OrderLine ol ON o.C_Order_ID = ol.C_Order_ID " +
" INNER JOIN C_Doctype d ON o.c_DocTypeTarget_ID = d.C_Doctype_ID " +
" WHERE d.DocBaseType='" + MDocBaseType.DOCBASETYPE_SALESORDER + "' " +
" AND d.DocSubTypeSo NOT IN ('" + MDocType.DOCSUBTYPESO_BlanketOrder + "','" + MDocType.DOCSUBTYPESO_Proposal + "')" +
" AND o.IsSOTrx='Y' AND o.IsReturnTrx='N' AND o.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND o.DateOrdered BETWEEN (SELECT startdate FROM C_Period WHERE C_Period_ID = " + C_Period_ID + ") " +
" AND (SELECT enddate FROM C_Period WHERE C_Period_ID = " + C_Period_ID + ") AND ol.QtyOrdered > ol.QtyDelivered " +
" AND ol.C_OrderLine_ID NOT IN (SELECT C_OrderLine_ID FROM va073_masterforecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(ol.M_Product_ID,0)>0 AND o.DocStatus IN('CO','CL') ";
sql = MRole.GetDefault(GetCtx()).AddAccessSQL(sql, "C_Order", true, true); // fully qualified - RO
dsOrder = new DataSet();
dsOrder = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOrder != null && dsOrder.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsOrder.Tables[0].Rows.Count; i++)
{
//create MasterForecastLine
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOrder.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_Order_ID"]),
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"]), 0, 0, 0, 0,
C_Period_ID, Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfDecimal(dsOrder.Tables[0].Rows[i]["QtyOrdered"]), ConvertedAmt,
Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + "for OrderLine" + Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"])
+ vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + "for OrderLine" + Util.GetValueOfInt(dsOrder.Tables[0].Rows[i]["C_OrderLine_ID"]));
}
}
else
{
Count++;
LineNo += 10;
//Update quantities AND price at Product line
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundSalesOrder"));
}
return(Count);
}
/// <SUMmary>
/// Oppportunity Products
/// </SUMmary>
/// <returns>No of Lines created</returns>
private int OnlyOpportunityProducts()
{
if (!Env.IsModuleInstalled("VA073_"))
{
//sql = " SELECT distinct(pl.m_product_id) FROM c_projectline pl INNER JOIN c_project p ON p.c_project_id = pl.c_project_id WHERE p.c_order_id IS NULL"
// + " AND p.ref_order_id IS ANDNULL pl.m_product_id NOT IN (SELECT DISTINCT(M_Product_ID) FROM c_forecastline fl "
// + " INNER JOIN c_forecast f ON (fl.c_forecast_id = f.c_forecast_id) WHERE f.c_period_id = " + C_Period_ID
// + " AND f.ad_client_id = " + GetCtx().GetAD_Client_ID() + " AND fl.isactive = 'Y')";
sql = " SELECT DISTINCT(pl.m_product_id) FROM c_projectline pl INNER JOIN c_project p ON p.c_project_id = pl.c_project_id WHERE p.c_order_id IS NULL"
+ " AND p.ref_order_id IS NULL AND pl.m_product_id NOT IN (SELECT m_product_id FROM c_masterforecastline WHERE isactive = 'Y' AND c_masterforecast_id = " + GetRecord_ID() + ")";
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, mf.Get_Trx());
while (idr.Read())
{
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
sql = "SELECT SUM(nvl(pl.plannedqty,0)) AS Quantity ,SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) AS Price, p.C_Currency_ID,pl.C_UOM_ID" +
" FROM c_projectline pl INNER JOIN c_project p ON (p.c_project_id = pl.c_project_id) "
+ " WHERE " +
"pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") "
+ " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " +
"AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND p.c_order_id IS NULL AND p.ref_order_id IS NULL AND pl.isactive = 'Y'"
+ " GROUP BY C_Currency_ID,pl.C_UOM_ID";
// totalQtyOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
//sql = " SELECT SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) FROM c_projectline pl INNER JOIN c_project p ON (p.c_project_id = pl.c_project_id) "
// + " WHERE " +
//"pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") "
//+ " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " +
//" AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND p.c_order_id IS NULL AND p.ref_order_id IS NULL AND pl.isactive = 'Y'";
//totalPriceOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
//Conversion from Project to MasterForecast Currency
totalPriceOpp = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Conversion from BaseUOM to UOM on Project Line
totalQtyOpp = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(idr[0]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Quantity"]));
}
if (totalQtyOpp.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPriceOpp.Value, totalQtyOpp.Value);
avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero);
mfLine = GenerateMasterForecast(Util.GetValueOfInt(idr[0]), 0, Util.GetValueOfDecimal(Decimal.Zero), totalQtyOpp, avgPrice);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
}
}
if (idr != null)
{
idr.Close();
idr = null;
}
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
}
else
{
//VA073_ Module is Installed
sql = "SELECT pl.m_product_id, p.c_project_id,p.C_Currency_ID,pl.c_projectline_id, pl.plannedqty,pl.C_UOM_ID," +
"(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) AS Price,pl.M_AttributeSetInstance_ID " +
" FROM C_Project p " +
"INNER JOIN C_ProjectLine pl ON p.C_Project_ID = pl.C_Project_ID" +
" WHERE p.c_order_id IS NULL AND p.ref_order_id IS NULL AND c_period_id = " + C_Period_ID + " AND p.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND C_ProjectLine_ID NOT IN (SELECT C_ProjectLine_ID FROM va073_masterforecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(pl.M_Product_ID,0)>0 ";
sql = MRole.GetDefault(GetCtx()).AddAccessSQL(sql, "C_Project", true, true); // fully qualified - RO
dsOpp = new DataSet();
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsOpp.Tables[0].Rows.Count; i++)
{
//Create MasterForecastline
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Conversion from BaseUOM to UOM on Project Line
OppQty = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["plannedqty"]));
if (OppQty == null)
{
OppQty = Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["plannedqty"]);
}
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, 0, 0, Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_Project_ID"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]), 0, 0,
C_Period_ID, Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_UOM_ID"]), Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_Product_ID"]),
OppQty, ConvertedAmt,
Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ProjectLine " + Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]) + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ProjectLine " + Util.GetValueOfInt(dsOpp.Tables[0].Rows[i]["C_ProjectLine_ID"]));
}
}
else
{
Count++;
LineNo += 10;
//Update quantities AND price at Product line
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundOpportunity"));
}
}
return(Count);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// MXI.
/// (single line)
/// <pre>
/// NotInvoicedReceipts DR (Receipt Org)
/// InventoryClearing CR
/// InvoicePV DR CR (difference)
/// Commitment
/// Expense CR
/// Offset DR
/// </pre>
/// </summary>
/// <param name="as1"></param>
/// <returns></returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
List <Fact> facts = new List <Fact>();
// Nothing to do
if (GetM_Product_ID() == 0 || // no Product
Env.Signum(GetQty().Value) == 0 ||
Env.Signum(_receiptLine.GetMovementQty()) == 0) // Qty = 0
{
log.Fine("No Product/Qty - M_Product_ID=" + GetM_Product_ID()
+ ",Qty=" + GetQty() + ",InOutQty=" + _receiptLine.GetMovementQty());
return(facts);
}
MMatchInv matchInv = (MMatchInv)GetPO();
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
SetC_Currency_ID(as1.GetC_Currency_ID());
/** Needs to be handeled in PO Matching as1 no Receipt info
* if (_pc.isService())
* {
* log.Fine("Service - skipped");
* return fact;
* }
**/
// NotInvoicedReceipt DR
// From Receipt
Decimal multiplier = Math.Abs(Decimal.Round(Decimal.Divide(GetQty().Value, _receiptLine.GetMovementQty()), 12, MidpointRounding.AwayFromZero));
FactLine dr = fact.CreateLine(null,
GetAccount(Doc.ACCTTYPE_NotInvoicedReceipts, as1),
as1.GetC_Currency_ID(), Env.ONE, null); // updated below
if (dr == null)
{
_error = "No Product Costs";
return(null);
}
dr.SetQty(GetQty());
// dr.setM_Locator_ID(_receiptLine.getM_Locator_ID());
// MInOut receipt = _receiptLine.getParent();
// dr.setLocationFromBPartner(receipt.getC_BPartner_Location_ID(), true); // from Loc
// dr.setLocationFromLocator(_receiptLine.getM_Locator_ID(), false); // to Loc
Decimal temp = dr.GetAcctBalance();
// Set AmtAcctCr/Dr from Receipt (sets also Project)
if (!dr.UpdateReverseLine(MInOut.Table_ID, // Amt updated
_receiptLine.GetM_InOut_ID(), _receiptLine.GetM_InOutLine_ID(),
multiplier))
{
_error = "Mat.Receipt not posted yet";
return(null);
}
log.Fine("CR - Amt(" + temp + "->" + dr.GetAcctBalance()
+ ") - " + dr.ToString());
// InventoryClearing CR
// From Invoice
MAccount expense = _pc.GetAccount(ProductCost.ACCTTYPE_P_InventoryClearing, as1);
if (_pc.IsService())
{
expense = _pc.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1);
}
Decimal LineNetAmt = _invoiceLine.GetLineNetAmt();
multiplier = Math.Abs(Decimal.Round(Decimal.Divide(GetQty().Value, _invoiceLine.GetQtyInvoiced()), 12, MidpointRounding.AwayFromZero));
if (multiplier.CompareTo(Env.ONE) != 0)
{
LineNetAmt = Decimal.Multiply(LineNetAmt, multiplier);
}
if (_pc.IsService())
{
LineNetAmt = dr.GetAcctBalance(); // book out exact receipt amt
}
FactLine cr = null;
if (as1.IsAccrual())
{
cr = fact.CreateLine(null, expense,
as1.GetC_Currency_ID(), null, LineNetAmt); // updated below
if (cr == null)
{
log.Fine("Line Net Amt=0 - M_Product_ID=" + GetM_Product_ID()
+ ",Qty=" + GetQty() + ",InOutQty=" + _receiptLine.GetMovementQty());
facts.Add(fact);
return(facts);
}
cr.SetQty(Decimal.Negate(GetQty().Value));
temp = cr.GetAcctBalance();
// Set AmtAcctCr/Dr from Invoice (sets also Project)
if (as1.IsAccrual() && !cr.UpdateReverseLine(MInvoice.Table_ID, // Amt updated
_invoiceLine.GetC_Invoice_ID(), _invoiceLine.GetC_InvoiceLine_ID(), multiplier))
{
_error = "Invoice not posted yet";
return(null);
}
log.Fine("DR - Amt(" + temp + "->" + cr.GetAcctBalance()
+ ") - " + cr.ToString());
}
else // Cash Acct
{
MInvoice invoice = _invoiceLine.GetParent();
if (as1.GetC_Currency_ID() == invoice.GetC_Currency_ID())
{
LineNetAmt = MConversionRate.Convert(GetCtx(), LineNetAmt,
invoice.GetC_Currency_ID(), as1.GetC_Currency_ID(),
invoice.GetDateAcct(), invoice.GetC_ConversionType_ID(),
invoice.GetAD_Client_ID(), invoice.GetAD_Org_ID());
}
cr = fact.CreateLine(null, expense,
as1.GetC_Currency_ID(), null, LineNetAmt);
cr.SetQty(Decimal.Negate(Decimal.Multiply(GetQty().Value, multiplier)));
}
cr.SetC_Activity_ID(_invoiceLine.GetC_Activity_ID());
cr.SetC_Campaign_ID(_invoiceLine.GetC_Campaign_ID());
cr.SetC_Project_ID(_invoiceLine.GetC_Project_ID());
cr.SetC_UOM_ID(_invoiceLine.GetC_UOM_ID());
cr.SetUser1_ID(_invoiceLine.GetUser1_ID());
cr.SetUser2_ID(_invoiceLine.GetUser2_ID());
// Invoice Price Variance difference
Decimal ipv = Decimal.Negate(Decimal.Add(cr.GetAcctBalance(), dr.GetAcctBalance()));
if (Env.Signum(ipv) != 0)
{
FactLine pv = fact.CreateLine(null,
_pc.GetAccount(ProductCost.ACCTTYPE_P_IPV, as1),
as1.GetC_Currency_ID(), ipv);
pv.SetC_Activity_ID(_invoiceLine.GetC_Activity_ID());
pv.SetC_Campaign_ID(_invoiceLine.GetC_Campaign_ID());
pv.SetC_Project_ID(_invoiceLine.GetC_Project_ID());
pv.SetC_UOM_ID(_invoiceLine.GetC_UOM_ID());
pv.SetUser1_ID(_invoiceLine.GetUser1_ID());
pv.SetUser2_ID(_invoiceLine.GetUser2_ID());
}
log.Fine("IPV=" + ipv + "; Balance=" + fact.GetSourceBalance());
MInOut inOut = _receiptLine.GetParent();
bool isReturnTrx = inOut.IsReturnTrx();
if (!IsPosted())
{
// Cost Detail Record - data from Expense/IncClearing (CR) record
MCostDetail.CreateInvoice(as1, GetAD_Org_ID(),
GetM_Product_ID(), matchInv.GetM_AttributeSetInstance_ID(),
_invoiceLine.GetC_InvoiceLine_ID(), 0, // No cost element
Decimal.Negate(cr.GetAcctBalance()), isReturnTrx ? Decimal.Negate(Utility.Util.GetValueOfDecimal(GetQty())) : Utility.Util.GetValueOfDecimal(GetQty()), // correcting
GetDescription(), GetTrx(), GetRectifyingProcess());
// Update Costing
UpdateProductInfo(as1.GetC_AcctSchema_ID(),
MAcctSchema.COSTINGMETHOD_StandardCosting.Equals(as1.GetCostingMethod()));
}
//
facts.Add(fact);
/** Commitment release ****/
if (as1.IsAccrual() && as1.IsCreateCommitment())
{
fact = Doc_Order.GetCommitmentRelease(as1, this,
Utility.Util.GetValueOfDecimal(GetQty()), _invoiceLine.GetC_InvoiceLine_ID(), Env.ONE);
if (fact == null)
{
return(null);
}
facts.Add(fact);
} // Commitment
return(facts);
}
protected override string DoIt()
{
string status = "OK";
string baseCurrency = DB.ExecuteScalar("Select ISO_Code from C_Currency Where C_Currency_ID=" + baseCurrencyID).ToString();
string currencySourceName = DB.ExecuteScalar("Select url from C_CurrencySource Where C_CurrencySource_ID=" + C_CurrencySource_ID).ToString();
string myCurrency = "";
int myCurrencyID = 0;
string sql = @"SELECT ISO_Code,C_Currency_ID FROM C_Currency WHERE IsActive='Y' AND ISMYCURRENCY='Y'";
DataSet ds = DB.ExecuteDataset(sql);
Trx trx = Trx.Get("CreateConVersionEnties");
try
{
if (ds != null)
{
String URL = "http://localhost/CloudService55/AccountService.asmx";
//String CloudURL = "http://cloudservice.viennaadvantage.com/AccountService.asmx";
BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.None)
{
CloseTimeout = new TimeSpan(00, 20, 00),
SendTimeout = new TimeSpan(00, 20, 00),
OpenTimeout = new TimeSpan(00, 20, 00),
ReceiveTimeout = new TimeSpan(00, 20, 00),
MaxReceivedMessageSize = int.MaxValue,
MaxBufferSize = int.MaxValue
};
int defaultconversionType = 0;
try
{
defaultconversionType = Convert.ToInt32(DB.ExecuteScalar("select c_conversiontype_id from c_conversiontype where isdefault='Y' and isactive='Y'"));
}
catch { }
MConversionRate conversion = null;
Decimal rate1 = 0;
Decimal rate2 = 0;
Decimal one = new Decimal(1.0);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCurrency = ds.Tables[0].Rows[i]["ISO_Code"].ToString();
myCurrencyID = Convert.ToInt32(ds.Tables[0].Rows[i]["C_Currency_ID"]);
var client = new ModelLibrary.AcctService.AccountServiceSoapClient(binding, new EndpointAddress(URL));
if (!String.IsNullOrEmpty(myCurrency) &&
!String.IsNullOrEmpty(baseCurrency) &&
!String.IsNullOrEmpty(currencySourceName))
{
string result = client.GetConvertedCurrencyValue(baseCurrency, myCurrency, currencySourceName, KEY);
if (!String.IsNullOrEmpty(result))
{
conversion = new MConversionRate(GetCtx(), 0, trx);
conversion.SetAD_Org_ID(0);
conversion.SetAD_Client_ID(GetCtx().GetAD_Client_ID());
conversion.SetValidFrom(DateTime.Now);
conversion.SetValidTo(DateTime.Now);
conversion.SetC_ConversionType_ID(defaultconversionType);
conversion.SetC_Currency_ID(myCurrencyID);
conversion.SetC_Currency_To_ID(baseCurrencyID);
//conversion.SetC_Currency_To_ID();
rate1 = Convert.ToDecimal(result);
rate2 = Utility.Env.ZERO;
one = new Decimal(1.0);
if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
{
rate2 = Decimal.Round(Decimal.Divide(one, rate1), 12); // MidpointRounding.AwayFromZero);
}
conversion.SetMultiplyRate(rate1);
conversion.SetDivideRate(rate2);
if (!conversion.Save(trx))
{
status = "ConversionRateNotsaved";
}
}
result = client.GetConvertedCurrencyValue(myCurrency, baseCurrency, currencySourceName, KEY);
if (!String.IsNullOrEmpty(result))
{
conversion = new MConversionRate(GetCtx(), 0, trx);
conversion.SetAD_Org_ID(0);
conversion.SetAD_Client_ID(GetCtx().GetAD_Client_ID());
conversion.SetValidFrom(DateTime.Now);
conversion.SetValidTo(DateTime.Now);
conversion.SetC_ConversionType_ID(defaultconversionType);
conversion.SetC_Currency_ID(baseCurrencyID);
conversion.SetC_Currency_To_ID(myCurrencyID);
//conversion.SetC_Currency_To_ID();
rate1 = Convert.ToDecimal(result);
rate2 = Utility.Env.ZERO;
one = new Decimal(1.0);
if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
{
rate2 = Decimal.Round(Decimal.Divide(one, rate1), 12); // MidpointRounding.AwayFromZero);
}
conversion.SetMultiplyRate(rate1);
conversion.SetDivideRate(rate2);
if (!conversion.Save(trx))
{
status = "ConversionRateNotsaved";
}
}
}
}
}
}
catch (Exception ex)
{
status = ex.Message;
}
if (status.Equals("OK"))
{
trx.Commit();
}
else
{
trx.Rollback();
}
trx.Close();
return(status);
}
/// <summary>
/// Create Landed Cost accounting & Cost lines
/// </summary>
/// <param name="as1">accounting schema</param>
/// <param name="fact">fact</param>
/// <param name="line">document line</param>
/// <param name="dr">DR entry (normal api)</param>
/// <returns>true if landed costs were created</returns>
private bool LandedCost(MAcctSchema as1, Fact fact, DocLine line, bool dr)
{
int C_InvoiceLine_ID = line.Get_ID();
MLandedCostAllocation[] lcas = MLandedCostAllocation.GetOfInvoiceLine(
GetCtx(), C_InvoiceLine_ID, GetTrx());
if (lcas.Length == 0)
{
return(false);
}
// Delete Old
String sql = "DELETE FROM M_CostDetail WHERE C_InvoiceLine_ID=" + C_InvoiceLine_ID;
int no = DataBase.DB.ExecuteQuery(sql, null, GetTrx());
if (no != 0)
{
log.Config("CostDetail Deleted #" + no);
}
// Calculate Total Base
double totalBase = 0;
for (int i = 0; i < lcas.Length; i++)
{
totalBase += Convert.ToDouble(lcas[i].GetBase());//.doubleValue();
}
// Create New
MInvoiceLine il = new MInvoiceLine(GetCtx(), C_InvoiceLine_ID, GetTrx());
for (int i = 0; i < lcas.Length; i++)
{
MLandedCostAllocation lca = lcas[i];
if (Env.Signum(lca.GetBase()) == 0)
{
continue;
}
double percent = totalBase / Convert.ToDouble(lca.GetBase());
String desc = il.GetDescription();
if (desc == null)
{
desc = percent + "%";
}
else
{
desc += " - " + percent + "%";
}
if (line.GetDescription() != null)
{
desc += " - " + line.GetDescription();
}
// Accounting
ProductCost pc = new ProductCost(GetCtx(),
lca.GetM_Product_ID(), lca.GetM_AttributeSetInstance_ID(), GetTrx());
Decimal?drAmt = null;
Decimal?crAmt = null;
if (dr)
{
drAmt = lca.GetAmt();
}
else
{
crAmt = lca.GetAmt();
}
FactLine fl = fact.CreateLine(line, pc.GetAccount(ProductCost.ACCTTYPE_P_CostAdjustment, as1),
GetC_Currency_ID(), drAmt, crAmt);
fl.SetDescription(desc);
// Cost Detail - Convert to AcctCurrency
Decimal allocationAmt = lca.GetAmt();
if (GetC_Currency_ID() != as1.GetC_Currency_ID())
{
allocationAmt = MConversionRate.Convert(GetCtx(), allocationAmt,
GetC_Currency_ID(), as1.GetC_Currency_ID(),
GetDateAcct(), GetC_ConversionType_ID(),
GetAD_Client_ID(), GetAD_Org_ID());
}
if (Env.Scale(allocationAmt) > as1.GetCostingPrecision())
{
allocationAmt = Decimal.Round(allocationAmt, as1.GetCostingPrecision(), MidpointRounding.AwayFromZero);
}
if (!dr)
{
allocationAmt = Decimal.Negate(allocationAmt);
}
if (!IsPosted())
{
MCostDetail cd = new MCostDetail(as1, lca.GetAD_Org_ID(),
lca.GetM_Product_ID(), lca.GetM_AttributeSetInstance_ID(),
lca.GetM_CostElement_ID(),
allocationAmt, lca.GetQty(), // Qty
desc, GetTrx());
cd.SetC_InvoiceLine_ID(C_InvoiceLine_ID);
bool ok = cd.Save();
if (ok && !cd.IsProcessed())
{
MClient client = MClient.Get(as1.GetCtx(), as1.GetAD_Client_ID());
if (client.IsCostImmediate())
{
cd.Process();
}
}
}
}
log.Config("Created #" + lcas.Length);
return(true);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// MMS, MMR.
/// <pre>
/// Shipment
/// CoGS (RevOrg) DR
/// Inventory CR
/// Shipment of Project Issue
/// CoGS DR
/// Project CR
/// Receipt
/// Inventory DR
/// NotInvoicedReceipt CR
/// </pre>
/// </summary>
/// <param name="as1">accounting schema</param>
/// <returns>Fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
SetC_Currency_ID(as1.GetC_Currency_ID());
// Line pointers
FactLine dr = null;
FactLine cr = null;
// *** Sales - Shipment
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_MATERIALDELIVERY))
{
for (int i = 0; i < _lines.Length; i++)
{
DocLine line = _lines[i];
MInOutLine sLine = new MInOutLine(GetCtx(), line.Get_ID(), null);
Decimal costs = 0;
if (sLine.GetA_Asset_ID() > 0)
{
costs = Util.GetValueOfDecimal(DB.ExecuteScalar(@"SELECT cost.CUrrentcostPrice
FROM m_cost cost
INNER JOIN A_Asset ass
ON(ass.a_asset_ID=cost.a_asset_ID)
INNER JOIN M_InOutLine IOL
ON(IOL.A_Asset_ID =ass.A_Asset_ID)
WHERE IOL.M_InOutLine_ID=" + sLine.GetM_InOutLine_ID() + @"
ORDER By cost.created desc"));
// Change if Cost not found against Asset then get Product Cost
if (Env.Signum(costs) == 0) // zero costs OK
{
costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), true);
}
}
else
{
costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), true);
}
if (Env.Signum(costs) == 0) // zero costs OK
{
MProduct product = line.GetProduct();
if (product.IsStocked())
{
_error = "No Costs for " + line.GetProduct().GetName();
log.Log(Level.WARNING, _error);
return(null);
}
else // ignore service
{
continue;
}
}
if (!IsReturnTrx())
{
// CoGS DR
dr = fact.CreateLine(line,
line.GetAccount(ProductCost.ACCTTYPE_P_Cogs, as1),
as1.GetC_Currency_ID(), costs, null);
if (dr == null)
{
_error = "FactLine DR not created: " + line;
log.Log(Level.WARNING, _error);
return(null);
}
dr.SetM_Locator_ID(line.GetM_Locator_ID());
dr.SetLocationFromLocator(line.GetM_Locator_ID(), true); // from Loc
dr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc
dr.SetAD_Org_ID(line.GetOrder_Org_ID()); // Revenue X-Org
dr.SetQty(Decimal.Negate(line.GetQty().Value));
// Inventory CR
cr = fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Asset, as1),
as1.GetC_Currency_ID(), null, costs);
if (cr == null)
{
_error = "FactLine CR not created: " + line;
log.Log(Level.WARNING, _error);
return(null);
}
cr.SetM_Locator_ID(line.GetM_Locator_ID());
cr.SetLocationFromLocator(line.GetM_Locator_ID(), true); // from Loc
cr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc
}
else // Reverse accounting entries for returns
{
// CoGS CR
cr = fact.CreateLine(line,
line.GetAccount(ProductCost.ACCTTYPE_P_Cogs, as1),
as1.GetC_Currency_ID(), null, costs);
if (cr == null)
{
_error = "FactLine CR not created: " + line;
log.Log(Level.WARNING, _error);
return(null);
}
cr.SetM_Locator_ID(line.GetM_Locator_ID());
cr.SetLocationFromLocator(line.GetM_Locator_ID(), true); // from Loc
cr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc
cr.SetAD_Org_ID(line.GetOrder_Org_ID()); // Revenue X-Org
cr.SetQty(Decimal.Negate(line.GetQty().Value));
// Inventory DR
dr = fact.CreateLine(line,
line.GetAccount(ProductCost.ACCTTYPE_P_Asset, as1),
as1.GetC_Currency_ID(), costs, null);
if (dr == null)
{
_error = "FactLine DR not created: " + line;
log.Log(Level.WARNING, _error);
return(null);
}
dr.SetM_Locator_ID(line.GetM_Locator_ID());
dr.SetLocationFromLocator(line.GetM_Locator_ID(), true); // from Loc
dr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc
}
//
if (line.GetM_Product_ID() != 0)
{
if (!IsPosted())
{
MCostDetail.CreateShipment(as1, line.GetAD_Org_ID(),
line.GetM_Product_ID(), line.GetM_AttributeSetInstance_ID(),
line.Get_ID(), 0,
costs, IsReturnTrx() ? Decimal.Negate(line.GetQty().Value) : line.GetQty().Value,
line.GetDescription(), true, GetTrx(), GetRectifyingProcess());
}
}
} // for all lines
if (!IsPosted())
{
UpdateProductInfo(as1.GetC_AcctSchema_ID()); // only for SO!
}
} // Shipment
// *** Purchasing - Receipt
else if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_MATERIALRECEIPT))
{
for (int i = 0; i < _lines.Length; i++)
{
Decimal costs = 0;
DocLine line = _lines[i];
MProduct product = line.GetProduct();
/***********************************************************/
//05,Sep,2011
//Special Check to restic Price varience posting in case of
//AvarageInvoice Selected on product Category.Then Neglact the AverageInvoice Cost
MProductCategoryAcct pca = MProductCategoryAcct.Get(product.GetCtx(),
product.GetM_Product_Category_ID(), as1.GetC_AcctSchema_ID(), null);
try
{
if (as1.IsNotPostPOVariance() && line.GetC_OrderLine_ID() > 0)
{
MOrderLine oLine = new MOrderLine(product.GetCtx(), line.GetC_OrderLine_ID(), null);
MOrder order = new MOrder(product.GetCtx(), oLine.GetC_Order_ID(), null);
Decimal convertedCost = MConversionRate.Convert(product.GetCtx(),
oLine.GetPriceEntered(), order.GetC_Currency_ID(), as1.GetC_Currency_ID(),
line.GetDateAcct(), order.GetC_ConversionType_ID(),
oLine.GetAD_Client_ID(), line.GetAD_Org_ID());
costs = Decimal.Multiply(convertedCost, oLine.GetQtyEntered());
}
else
{
costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), false); // non-zero costs
}
}
catch (Exception ex)
{
log.SaveError("AccountSchemaColumnError", ex);
costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), false); // non-zero costs
}
/***********************************************************/
//Decimal costs = costs = line.GetProductCosts(as1, line.GetAD_Org_ID(), false); // non-zero costs
if (Env.Signum(costs) == 0)
{
_error = "Resubmit - No Costs for " + product.GetName();
log.Log(Level.WARNING, _error);
return(null);
}
// Inventory/Asset DR
MAccount assets = line.GetAccount(ProductCost.ACCTTYPE_P_Asset, as1);
if (product.IsService())
{
assets = line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1);
}
if (!IsReturnTrx())
{
// Inventory/Asset DR
dr = fact.CreateLine(line, assets,
as1.GetC_Currency_ID(), costs, null);
if (dr == null)
{
_error = "DR not created: " + line;
log.Log(Level.WARNING, _error);
return(null);
}
dr.SetM_Locator_ID(line.GetM_Locator_ID());
dr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc
dr.SetLocationFromLocator(line.GetM_Locator_ID(), false); // to Loc
// NotInvoicedReceipt CR
cr = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_NotInvoicedReceipts, as1),
as1.GetC_Currency_ID(), null, costs);
if (cr == null)
{
_error = "CR not created: " + line;
log.Log(Level.WARNING, _error);
return(null);
}
cr.SetM_Locator_ID(line.GetM_Locator_ID());
cr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc
cr.SetLocationFromLocator(line.GetM_Locator_ID(), false); // to Loc
cr.SetQty(Decimal.Negate(line.GetQty().Value));
}
else // reverse accounting entries for returns
{
// Inventory/Asset CR
cr = fact.CreateLine(line, assets,
as1.GetC_Currency_ID(), null, costs);
if (cr == null)
{
_error = "CR not created: " + line;
log.Log(Level.WARNING, _error);
return(null);
}
cr.SetM_Locator_ID(line.GetM_Locator_ID());
cr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc
cr.SetLocationFromLocator(line.GetM_Locator_ID(), false); // to Loc
// NotInvoicedReceipt DR
dr = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_NotInvoicedReceipts, as1),
as1.GetC_Currency_ID(), costs, null);
if (dr == null)
{
_error = "DR not created: " + line;
log.Log(Level.WARNING, _error);
return(null);
}
dr.SetM_Locator_ID(line.GetM_Locator_ID());
dr.SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc
dr.SetLocationFromLocator(line.GetM_Locator_ID(), false); // to Loc
dr.SetQty(Decimal.Negate(line.GetQty().Value));
}
}
} // Receipt
else
{
_error = "DocumentType unknown: " + GetDocumentType();
log.Log(Level.SEVERE, _error);
return(null);
}
//
List <Fact> facts = new List <Fact>();
facts.Add(fact);
return(facts);
}
/**
* Create Invoice Line from Shipment
* @param order order
* @param ship shipment header
* @param sLine shipment line
*/
private void CreateLine(MOrder order, MInOut ship, MInOutLine sLine)
{
if (_invoice == null)
{
_invoice = new MInvoice(order, 0, _DateInvoiced);
//---------------------------Column Added by Anuj------------------
int _CountVA009 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA009_' AND IsActive = 'Y'"));
if (_CountVA009 > 0)
{
int _PaymentMethod_ID = order.GetVA009_PaymentMethod_ID();
if (_PaymentMethod_ID > 0)
{
_invoice.SetVA009_PaymentMethod_ID(_PaymentMethod_ID);
}
}
//-----------------Column Added by Anuj------------------
int _CountVA026 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA026_' AND IsActive = 'Y'"));
if (_CountVA026 > 0)
{
_invoice.SetVA026_LCDetail_ID(order.GetVA026_LCDetail_ID());
}
// Added by Bharat on 29 Jan 2018 to set Inco Term from Order
if (_invoice.Get_ColumnIndex("C_IncoTerm_ID") > 0)
{
_invoice.SetC_IncoTerm_ID(order.GetC_IncoTerm_ID());
}
if (!_invoice.Save())
{
throw new Exception("Could not create Invoice (s)");
}
}
#region Comment Create Shipment Comment Line
// Create Shipment Comment Line
//if (_ship == null
// || _ship.GetM_InOut_ID() != ship.GetM_InOut_ID())
//{
// MDocType dt = MDocType.Get(GetCtx(), ship.GetC_DocType_ID());
// if (_bp == null || _bp.GetC_BPartner_ID() != ship.GetC_BPartner_ID())
// {
// _bp = new MBPartner(GetCtx(), ship.GetC_BPartner_ID(), Get_TrxName());
// }
// // Reference: Delivery: 12345 - 12.12.12
// MClient client = MClient.Get(GetCtx(), order.GetAD_Client_ID());
// String AD_Language = client.GetAD_Language();
// if (client.IsMultiLingualDocument() && _bp.GetAD_Language() != null)
// {
// AD_Language = _bp.GetAD_Language();
// }
// if (AD_Language == null)
// {
// // MessageBox.Show("Set base Language");
// //AD_Language = Language.getBaseAD_Language();
// }
// //java.text.SimpleDateFormat format = DisplayType.getDateFormat
// // (DisplayType.Date, Language.getLanguage(AD_Language));
// //String reference = dt.GetPrintName(_bp.GetAD_Language())
// // + ": " + ship.GetDocumentNo()
// // + " - " + format.format(ship.GetMovementDate());
// String reference = dt.GetPrintName(_bp.GetAD_Language())
// + ": " + ship.GetDocumentNo()
// + " - " + ship.GetMovementDate();
// _ship = ship;
// //
// MInvoiceLine line = new MInvoiceLine(_invoice);
// line.SetIsDescription(true);
// line.SetDescription(reference);
// line.SetLine(_line + sLine.GetLine() - 2);
// if (!line.Save())
// {
// throw new Exception("Could not create Invoice Comment Line (sh)");
// }
// // Optional Ship Address if not Bill Address
// if (order.GetBill_Location_ID() != ship.GetC_BPartner_Location_ID())
// {
// MLocation addr = MLocation.GetBPLocation(GetCtx(), ship.GetC_BPartner_Location_ID(), null);
// line = new MInvoiceLine(_invoice);
// line.SetIsDescription(true);
// line.SetDescription(addr.ToString());
// line.SetLine(_line + sLine.GetLine() - 1);
// if (!line.Save())
// {
// throw new Exception("Could not create Invoice Comment Line 2 (sh)");
// }
// }
//}
#endregion
//
MInvoiceLine line1 = new MInvoiceLine(_invoice);
line1.SetShipLine(sLine);
line1.SetQtyEntered(sLine.GetQtyEntered());
line1.SetQtyInvoiced(sLine.GetMovementQty());
line1.SetLine(_line + sLine.GetLine());
line1.SetM_AttributeSetInstance_ID(sLine.GetM_AttributeSetInstance_ID());
if (sLine.GetA_Asset_ID() > 0)
{
line1.SetA_Asset_ID(sLine.GetA_Asset_ID());
if (line1.Get_ColumnIndex("VAFAM_AssetCost") > 0)
{
int PAcctSchema_ID = 0;
int pCurrency_ID = 0;
MAcctSchema as1 = MClient.Get(GetCtx(), GetAD_Client_ID()).GetAcctSchema();
if (as1 != null)
{
PAcctSchema_ID = as1.GetC_AcctSchema_ID();
pCurrency_ID = as1.GetC_Currency_ID();
}
decimal LineNetAmt = Decimal.Multiply(line1.GetPriceActual(), line1.GetQtyEntered());
decimal AssetCost = GetAssetCost(sLine.GetA_Asset_ID(), sLine.GetM_Product_ID(), PAcctSchema_ID);
AssetCost = Decimal.Multiply(AssetCost, line1.GetQtyEntered());
if (LineNetAmt > 0)
{
LineNetAmt = MConversionRate.Convert(GetCtx(), LineNetAmt, _invoice.GetC_Currency_ID(), pCurrency_ID, _invoice.GetAD_Client_ID(), _invoice.GetAD_Org_ID());
}
decimal Diff = LineNetAmt - AssetCost;
line1.Set_Value("VAFAM_AssetCost", AssetCost);
line1.Set_Value("VAFAM_Difference", Diff);
}
}
if (!line1.Save())
{
throw new Exception("Could not create Invoice Line (s)");
}
// Link
sLine.SetIsInvoiced(true);
if (!sLine.Save())
{
throw new Exception("Could not update Shipment Line");
}
log.Fine(line1.ToString());
}
/// <SUMmary>
/// Consolidate Data FROM sales order , Team Forecast, Opportunity
/// </SUMmary>
/// <returns>Info</returns>
protected override string DoIt()
{
mf = new X_C_MasterForecast(GetCtx(), GetRecord_ID(), Get_Trx());
if (Util.GetValueOfInt(mf.Get_Value("M_PriceList_ID")) == 0)
{
return(Msg.GetMsg(mf.GetCtx(), "CreatelinesManually"));
}
C_Period_ID = mf.GetC_Period_ID();
Currency = Util.GetValueOfInt(mf.Get_Value("C_Currency_ID"));
StdPrecision = Util.GetValueOfInt(DB.ExecuteScalar("SELECT StdPrecision FROM C_Currency WHERE C_Currency_ID=" + Currency, null, null));
//Get Table_Id to create PO Object
// sql = @"SELECT AD_TABLE_ID FROM AD_TABLE WHERE tablename LIKE 'VA073_MasterForecastLineDetail' AND IsActive = 'Y'";
// tableId = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null));
// tbl = new MTable(GetCtx(), tableId, null);
tbl = MTable.Get(GetCtx(), "VA073_MasterForecastLineDetail");
// sql = "delete FROM c_masterforecastline WHERE c_masterforecast_id = " + mf.GetC_MasterForecast_ID();
// int count = DB.ExecuteQuery(sql, null, null);
if (C_Period_ID != 0)
{
sql = "SELECT COUNT(C_MasterForecastLine_ID) FROM c_masterforecastline WHERE c_masterforecast_id = " + GetRecord_ID();
int count = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, null));
if (count > 0)
{
sql = "UPDATE c_masterforecastline set Processed = 'Y' WHERE c_masterforecast_id = " + GetRecord_ID();
int res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
sql = "UPDATE c_masterforecast set Processed = 'Y' WHERE c_masterforecast_id = " + GetRecord_ID();
res = Util.GetValueOfInt(DB.ExecuteQuery(sql, null, null));
msg = Msg.GetMsg(GetCtx(), "RecordsProcessed");
return(msg);
}
if (!Env.IsModuleInstalled("VA073_"))
{
sql = "SELECT DISTINCT(M_Product_ID) FROM c_forecastline fl INNER JOIN c_forecast f ON (fl.c_forecast_id = f.c_forecast_id) WHERE f.c_period_id = " + C_Period_ID + " AND f.ad_client_id = " + GetCtx().GetAD_Client_ID() + " AND f.isactive = 'Y' AND f.processed = 'Y'";
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, mf.Get_Trx());
while (idr.Read())
{
Decimal?totalQtyTeam = 0;
Decimal?totalPriceTeam = 0;
Decimal?totalQtyOpp = 0;
Decimal?totalPriceOpp = 0;
sql = "SELECT SUM(nvl(qtyentered,0)) AS Quantity,SUM(nvl(pricestd,0)) AS Price,f.C_Currency_ID FROM c_forecastline fl" +
" INNER JOIN C_Forecast f ON f.C_Forecast_ID = fl.C_Forecast_ID " +
" WHERE fl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND f.Processed = 'Y' AND f.isactive = 'Y'" +
" GROUP BY f.C_Currency_ID";
//totalQtyTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
//// sql = "SELECT SUM(nvl(qtyentered,0) * nvl(pricestd,0)) FROM c_forecastline WHERE m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND Processed = 'Y'";
//sql = "SELECT SUM(nvl(pricestd,0)) FROM c_forecastline WHERE m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND Processed = 'Y' AND isactive = 'Y'";
//totalPriceTeam = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_Trx()));
dsForecast = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsForecast != null && dsForecast.Tables[0].Rows.Count > 0)
{
totalPriceTeam = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[0]["Price"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[0]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
totalQtyTeam = Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[0]["Quantity"]);
}
if (mf.IsIncludeOpp())
{
sql = "SELECT SUM(NVL(pl.plannedqty,0)) AS Quantity ,SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) AS Price, p.C_Currency_ID,pl.C_UOM_ID" +
" FROM c_projectline pl INNER JOIN c_project p ON (p.c_project_id = pl.c_project_id) "
+ " WHERE " +
"pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") "
+ " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " +
"AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) + " AND p.c_order_id IS NULL AND p.ref_order_id IS NULL AND pl.isactive = 'Y'"
+ " GROUP BY C_Currency_ID,pl.C_UOM_ID";
//totalQtyOpp = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_Trx()));
//sql = " SELECT SUM(NVL(pl.plannedqty,0) * NVL(pl.plannedprice,0)) FROM c_projectline pl INNER JOIN c_project p ON (p.c_project_id = pl.c_project_id) "
// + " WHERE " +
// " pl.planneddate BETWEEN (SELECT startdate FROM c_period WHERE c_period_id = " + C_Period_ID + ") "
// + " AND (SELECT enddate FROM c_period WHERE c_period_id = " + C_Period_ID + ") " +
// " AND pl.m_product_id = " + Util.GetValueOfInt(idr[0]) +
// " AND p.c_order_id IS NULL AND p.ref_order_id IS NULL AND pl.isactive = 'Y' AND p.ad_client_id = " + mf.GetAD_Client_ID();
dsOpp = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsOpp != null && dsOpp.Tables[0].Rows.Count > 0)
{
//Conversion from Project to MasterForecast Currency
totalPriceOpp = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Price"]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Conversion from BaseUOM to UOM on Project Line
totalQtyOpp = MUOMConversion.ConvertProductFrom(mf.GetCtx(), Util.GetValueOfInt(idr[0]),
Util.GetValueOfInt(dsOpp.Tables[0].Rows[0]["C_UOM_ID"]), Util.GetValueOfDecimal(dsOpp.Tables[0].Rows[0]["Quantity"]));
}
}
Decimal?totalPrice = Decimal.Add(totalPriceTeam.Value, totalPriceOpp.Value);
Decimal?totalQty = Decimal.Add(totalQtyTeam.Value, totalQtyOpp.Value);
if (totalQty.Value > 0)
{
Decimal?avgPrice = Decimal.Divide(totalPrice.Value, totalQty.Value);
avgPrice = Decimal.Round(avgPrice.Value, 2, MidpointRounding.AwayFromZero);
mfLine = GenerateMasterForecast(Util.GetValueOfInt(idr[0]), 0, totalQtyTeam, totalQtyOpp, avgPrice);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
}
}
if (idr != null)
{
idr.Close();
idr = null;
}
}
catch
{
if (idr != null)
{
idr.Close();
idr = null;
}
}
if (mf.IsIncludeOpp())
{
OnlyOpportunityProducts();
}
mf.SetCurrentVersion(true);
mf.SetProcessed(true);
if (!mf.Save())
{
log.SaveError("MasterForecastNotSaved", "MasterForecastNotSaved");
return(GetRetrievedError(mf, "MasterForecastNotSaved"));
}
msg = Msg.GetMsg(GetCtx(), "ProcessCompleted");
}
else
{
//VA073 module installed -- Consolidate FROM Sales order , opportunity , Team Forecast
TeamForecastProduct();
if (mf.IsIncludeOpp())
{
OnlyOpportunityProducts();
}
if (Util.GetValueOfBool(mf.Get_Value("VA073_IsIncludeOpenSO")))
{
SalesOrderProducts();
}
if (Count == 0)
{
mf.Get_Trx().Rollback();
}
else
{
//UPDATE Master forecast Line Set processed to true
sql = "UPDATE C_MasterForecastLine SET Processed='Y' WHERE C_MasterForecast_ID=" + GetRecord_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
//UPDATE Master forecast Set processed to true
sql = "UPDATE C_MasterForecast SET Processed='Y' WHERE C_MasterForecast_ID=" + GetRecord_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
msg = Msg.GetMsg(mf.GetCtx(), "ProductLinesDetailCreated") + Count;
}
}
return(msg);
}
/// <SUMmary>
/// Team Forecast Products
/// </SUMmary>
/// <returns>No of lines created</returns>
private int TeamForecastProduct()
{
sql = @"SELECT fl.M_Product_ID,fl.M_AttributeSetInstance_ID,fl.qtyentered,fl.BaseQty,f.C_Forecast_ID,
C_ForecastLine_ID,f.C_Period_ID,fl.C_UOM_ID,NVL(pricestd,0) AS Price,f.C_Currency_ID
FROM C_Forecast f " +
" INNER JOIN C_Forecastline fl ON fl.c_forecast_id = f.c_forecast_id " +
" WHERE f.c_period_id = " + C_Period_ID + " AND f.AD_Org_ID = " + mf.GetAD_Org_ID() +
" AND f.isactive = 'Y' AND f.processed = 'Y'" +
" AND C_ForecastLine_ID NOT IN (SELECT C_ForecastLine_ID FROM VA073_MasterForecastlinedetail WHERE " +
"AD_Org_ID = " + mf.GetAD_Org_ID() + " AND C_Period_ID=" + C_Period_ID + ") AND NVL(fl.M_Product_ID,0)>0 ";
sql = MRole.GetDefault(mf.GetCtx()).AddAccessSQL(sql, "C_Forecast", true, true); // fully qualified - RO
dsForecast = new DataSet();
dsForecast = DB.ExecuteDataset(sql, null, mf.Get_Trx());
if (dsForecast != null && dsForecast.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsForecast.Tables[0].Rows.Count; i++)
{
//Create MasterForecastLine
mfLine = GenerateMasterForecast(Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]), 0, 0, 0);
if (!mfLine.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved") + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "MasterForecastLineNotSaved"));
}
}
else
{
LineNo = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(LineNo), 0)+10 FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID(), null, mf.Get_Trx()));
//Convert Line Amount as per Currency Defined ON Master Forecast
ConvertedAmt = MConversionRate.Convert(mf.GetCtx(), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["Price"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Currency_ID"]), Currency,
Util.GetValueOfDateTime(mf.Get_Value("TRXDATE")),
Util.GetValueOfInt(mf.Get_Value("C_ConversionType_ID")), mf.GetAD_Client_ID(), mf.GetAD_Org_ID());
//Create Product Line Details
po = GenerateProductLineDetails(mfLine, LineNo, 0, 0, 0, 0, Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Forecast_ID"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_Period_ID"]), mfLine.GetC_UOM_ID(),
Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_Product_ID"]), Util.GetValueOfDecimal(dsForecast.Tables[0].Rows[i]["qtyentered"]),
ConvertedAmt, Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["M_AttributeSetInstance_ID"]));
if (!po.Save())
{
ValueNamePair vp = VLogger.RetrieveError();
if (vp != null)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ForecastLine " + Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]) + " " + vp.GetValue() + " - " + vp.GetName());
}
else
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "ProductLineDetailNotSaved") + " for ForecastLine " + Util.GetValueOfInt(dsForecast.Tables[0].Rows[i]["C_ForecastLine_ID"]));
}
}
else
{
//Update quantities AND Price at Product line
Count++;
LineNo += 10;
sql = "UPDATE c_masterforecastline SET " +
"ForcastQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Forecast_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"OppQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Project_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"VA073_SalesOrderQty =(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE NVL(C_Order_ID,0)>0 AND c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
"TotalQty=(SELECT NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE c_masterforecastline_ID=" + mfLine.GetC_MasterForecastLine_ID() + ") , " +
"Price= (Round((SELECT NVL(SUM(price),0)/ NVL(SUM(QtyEntered),0) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + "), " +
StdPrecision + ")), " +
"PlannedRevenue =(ROUND((SELECT SUM(price) FROM VA073_MasterForecastLineDetail WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID() + ")," + StdPrecision + "))" +
" WHERE C_MasterForecastLine_ID=" + mfLine.GetC_MasterForecastLine_ID();
DB.ExecuteQuery(sql, null, mf.Get_Trx());
}
}
}
}
else
{
log.Log(Level.INFO, Msg.GetMsg(GetCtx(), "NoRecordFoundForecast"));
}
return(Count);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// MXP.
/// <pre>
/// Product PPV <difference>
/// PPV_Offset <difference>
/// </pre>
/// </summary>
/// <param name="as1"></param>
/// <returns></returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
List <Fact> facts = new List <Fact>();
//
if (GetM_Product_ID() == 0 || // Nothing to do if no Product
Env.Signum(Utility.Util.GetValueOfDecimal(GetQty())) == 0 ||
_M_InOutLine_ID == 0) // No posting if not matched to Shipment
{
log.Fine("No Product/Qty - M_Product_ID=" + GetM_Product_ID()
+ ",Qty=" + GetQty());
return(facts);
}
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
SetC_Currency_ID(as1.GetC_Currency_ID());
// Purchase Order Line
Decimal poCost = _oLine.GetPriceCost();
if (Env.Signum(poCost) == 0)
{
poCost = _oLine.GetPriceActual();
}
poCost = Decimal.Multiply(poCost, Utility.Util.GetValueOfDecimal(GetQty())); // Delivered so far
// Different currency
if (_oLine.GetC_Currency_ID() != as1.GetC_Currency_ID())
{
MOrder order = _oLine.GetParent();
Decimal rate = MConversionRate.GetRate(
order.GetC_Currency_ID(), as1.GetC_Currency_ID(),
order.GetDateAcct(), order.GetC_ConversionType_ID(),
_oLine.GetAD_Client_ID(), _oLine.GetAD_Org_ID());
if (rate.ToString() == null)
{
_error = "Purchase Order not convertible - " + as1.GetName();
return(null);
}
poCost = Decimal.Multiply(poCost, rate);
if (Env.Scale(poCost) > as1.GetCostingPrecision())
{
poCost = Decimal.Round(poCost, as1.GetCostingPrecision(), MidpointRounding.AwayFromZero);
}
}
MOrder order1 = _oLine.GetParent();
bool isReturnTrx = order1.IsReturnTrx();
log.Fine("Temp");
if (!IsPosted())
{
// Create PO Cost Detail Record firs
MCostDetail.CreateOrder(as1, _oLine.GetAD_Org_ID(),
GetM_Product_ID(), _M_AttributeSetInstance_ID,
_C_OrderLine_ID, 0, // no cost element
isReturnTrx ? Decimal.Negate(poCost) : poCost, isReturnTrx ? Decimal.Negate(Utility.Util.GetValueOfDecimal(GetQty())) : Utility.Util.GetValueOfDecimal(GetQty()), // Delivered
_oLine.GetDescription(), GetTrx(), GetRectifyingProcess());
}
// Current Costs
String costingMethod = as1.GetCostingMethod();
MProduct product = MProduct.Get(GetCtx(), GetM_Product_ID());
MProductCategoryAcct pca = MProductCategoryAcct.Get(GetCtx(),
product.GetM_Product_Category_ID(), as1.GetC_AcctSchema_ID(), GetTrx());
if (pca.GetCostingMethod() != null)
{
costingMethod = pca.GetCostingMethod();
}
Decimal?costs = _pc.GetProductCosts(as1, GetAD_Org_ID(), costingMethod, _C_OrderLine_ID, false); // non-zero costs
// No Costs yet - no PPV
if (costs == null || Env.Signum(Utility.Util.GetValueOfDecimal(costs)) == 0)
{
_error = "Resubmit - No Costs for " + product.GetName();
log.Log(Level.SEVERE, _error);
return(null);
}
// Difference
Decimal difference = Decimal.Subtract(poCost, Utility.Util.GetValueOfDecimal(costs));
/***********************************************************************************/
//05,Sep,2011
//Special Check to restic Price varience posting in case of
//AvarageInvoice Selected on product Category.Then Neglact the AverageInvoice Cost
try
{
if (as1.IsNotPostPOVariance())
{
difference = 0;
}
}
catch (Exception ex)
{
log.SaveError("AccountSchemaColumnError", ex);
}
/***********************************************************************************/
// Nothing to post
if (Env.Signum(difference) == 0)
{
log.Log(Level.FINE, "No Cost Difference for M_Product_ID=" + GetM_Product_ID());
facts.Add(fact);
return(facts);
}
// Product PPV
FactLine cr = fact.CreateLine(null,
_pc.GetAccount(ProductCost.ACCTTYPE_P_PPV, as1),
as1.GetC_Currency_ID(), difference);
if (cr != null)
{
cr.SetQty(GetQty());
cr.SetC_BPartner_ID(_oLine.GetC_BPartner_ID());
cr.SetC_Activity_ID(_oLine.GetC_Activity_ID());
cr.SetC_Campaign_ID(_oLine.GetC_Campaign_ID());
cr.SetC_Project_ID(_oLine.GetC_Project_ID());
cr.SetC_UOM_ID(_oLine.GetC_UOM_ID());
cr.SetUser1_ID(_oLine.GetUser1_ID());
cr.SetUser2_ID(_oLine.GetUser2_ID());
}
// PPV Offset
FactLine dr = fact.CreateLine(null,
GetAccount(Doc.ACCTTYPE_PPVOffset, as1),
as1.GetC_Currency_ID(), Decimal.Negate(difference));
if (dr != null)
{
dr.SetQty((Decimal?)Decimal.Negate(Utility.Util.GetValueOfDecimal(GetQty())));
dr.SetC_BPartner_ID(_oLine.GetC_BPartner_ID());
dr.SetC_Activity_ID(_oLine.GetC_Activity_ID());
dr.SetC_Campaign_ID(_oLine.GetC_Campaign_ID());
dr.SetC_Project_ID(_oLine.GetC_Project_ID());
dr.SetC_UOM_ID(_oLine.GetC_UOM_ID());
dr.SetUser1_ID(_oLine.GetUser1_ID());
dr.SetUser2_ID(_oLine.GetUser2_ID());
}
//
facts.Add(fact);
return(facts);
}
protected override string DoIt()
{
string status = "OK";
// Trx trx = Get_Trx();
ds.Clear();
try
{
ds = DB.ExecuteDataset(@"SELECT distinct cl.AD_Client_ID,
cl.AD_Org_ID,
cl.CurrencyRateUpdateFrequency,
acct.C_Currency_ID
,cr.ISO_Code,cl.C_CurrencySource_ID
FROM ad_client cl
INNER JOIN AD_CLientinfo ci
ON ci.ad_client_ID=cl.ad_client_ID
INNER JOIN C_AcctSchema acct
ON acct.C_AcctSchema_ID =ci.C_AcctSchema1_ID
Left Join C_Currency cr on cr.C_Currency_ID=acct.C_Currency_ID
WHERE cl.ad_client_Id! =0 AND cl.UpdateCurrencyRate='A' AND cl.IsMultiCurrency='Y'
AND cl.currencyrateupdatefrequency IS NOT NULL");
// Where ci.AD_CLient_ID= " + GetAD_Client_ID());
//in this DataSet we'll get CLient's Base Currency & the Currency ID
// int clientCount = ds.Tables[0].Rows.Count;
if (ds.Tables[0].Rows.Count > 0)
{
_lstCurr = new List <CurrencyProp>();
}
else
{
return(VAdvantage.Utility.Msg.GetMsg(GetCtx(), "NoMultiCurrencySettingsFound"));
}
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
CurrencyProp _curr = new CurrencyProp();
_curr.baseCurrency = Convert.ToString(ds.Tables[0].Rows[j]["ISO_Code"]);
_curr.baseCurrencyID = Convert.ToInt32(ds.Tables[0].Rows[j]["C_Currency_ID"]);
_curr.frequency = Convert.ToString(ds.Tables[0].Rows[j]["CurrencyRateUpdateFrequency"]);
_curr.AD_Client_ID = Convert.ToInt32(ds.Tables[0].Rows[j]["AD_Client_ID"]);
_curr.AD_Org_ID = Convert.ToInt32(ds.Tables[0].Rows[j]["AD_Org_ID"]);
_curr.CurrencySource = Convert.ToInt32(ds.Tables[0].Rows[j]["C_CurrencySource_ID"]);
_lstCurr.Add(_curr);
}
ds.Clear();
sql = @"Select Cur.ISO_Code, Cur.C_Currency_ID From C_Currency Cur Where Cur.IsMyCurrency='Y' And Cur.IsActive='Y' ";
ds = DB.ExecuteDataset(sql); // Here we get all currencies in which our Client is in dealing with
//String frequency;// = DB.ExecuteScalar("Select currencyrateupdatefrequency from AD_Client where IsActive='Y' AND currencyrateupdatefrequency is not null").ToString();
// getting the frequency(TimePeriod) for the converted rate
if (ds != null)
{
for (Int32 k = 0; k < _lstCurr.Count; k++)
{
string currencySourceName = DB.ExecuteScalar("Select url from C_CurrencySource Where C_CurrencySource_ID=" + _lstCurr[k].CurrencySource).ToString();
//int defaultconversionType = 0;
//try
//{
// defaultconversionType = Convert.ToInt32(DB.ExecuteScalar(@"SELECT C_ConversionType_id, Surchargepercentage,Surchargevalue FROM c_conversiontype WHERE autocalculate='Y' AND isactive ='Y'"));
//}
//catch
//{
//}
DataSet dsConversion = DB.ExecuteDataset(@"SELECT C_ConversionType_id, Surchargepercentage,Surchargevalue,CurrencyRateUpdateFrequency FROM c_conversiontype WHERE isautocalculate='Y' AND isactive ='Y'");
if (dsConversion != null && dsConversion.Tables[0].Rows.Count > 0)
{
for (int x = 0; x < dsConversion.Tables[0].Rows.Count; x++)
{
int defaultconversionType = 0;
defaultconversionType = Convert.ToInt32(dsConversion.Tables[0].Rows[x]["C_ConversionType_id"]);
MConversionRate conversion = null;
Decimal rate1 = 0;
Decimal rate2 = 0;
Decimal one = new Decimal(1.0);
string updateFrequency = _lstCurr[k].frequency;
if (dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"] != null && dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"] != DBNull.Value &&
Convert.ToString(dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"]) != "")
{
updateFrequency = Convert.ToString(dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"]);
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCurrency = ds.Tables[0].Rows[i]["ISO_Code"].ToString();
myCurrencyID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Currency_ID"]);
sql = String.Empty;
sql = @" Select ValidTo from C_Conversion_Rate where IsActive='Y' AND C_ConversionType_id=" + defaultconversionType + " AND C_Currency_ID=" + _lstCurr[k].baseCurrencyID + " AND C_Currency_To_ID=" + myCurrencyID
+ " AND Created=(SELECT Max(Created) FROM C_Conversion_Rate WHERE isactive ='Y' AND C_ConversionType_id=" + defaultconversionType + " AND "
+ " C_Currency_ID =" + _lstCurr[k].baseCurrencyID + " AND C_Currency_To_ID=" + myCurrencyID + ") AND AD_Client_ID = " + _lstCurr[k].AD_Client_ID + "AND AD_Org_ID= " + _lstCurr[k].AD_Org_ID;
//the Maximum date from Converted rate of every currency
if (DateTime.Now.Date > Convert.ToDateTime(DB.ExecuteScalar(sql.Trim(), null, null)).Date)
{
if (!String.IsNullOrEmpty(myCurrency) && !String.IsNullOrEmpty(_lstCurr[k].baseCurrency) &&
!String.IsNullOrEmpty(currencySourceName) && (myCurrencyID != _lstCurr[k].baseCurrencyID))
{
String result = GetConvertedCurrencyValue(_lstCurr[k].baseCurrency, myCurrency, currencySourceName);
if (!String.IsNullOrEmpty(result))
{
conversion = new MConversionRate(GetCtx(), 0, null);
conversion.SetAD_Org_ID((_lstCurr[k].AD_Org_ID));
conversion.SetAD_Client_ID(_lstCurr[k].AD_Client_ID);
//conversion.SetValidFrom(DateTime.Now.AddDays(-1));
conversion.SetValidFrom(DateTime.Now);
if (updateFrequency.Equals("D"))
{
conversion.SetValidTo(DateTime.Now);
}
else if (updateFrequency.Equals("W"))
{
conversion.SetValidTo(DateTime.Now.AddDays(7));
}
else if (updateFrequency.Equals("M"))
{
conversion.SetValidTo(DateTime.Now.AddMonths(1));
}
conversion.SetC_ConversionType_ID(defaultconversionType);
conversion.SetC_Currency_ID(_lstCurr[k].baseCurrencyID);
conversion.SetC_Currency_To_ID(myCurrencyID);
rate2 = VAdvantage.Utility.Env.ZERO;
one = new Decimal(1.0);
//if (dsConversion.Tables[0].Rows[x]["Surchargepercentage"] != null && dsConversion.Tables[0].Rows[x]["Surchargepercentage"] != DBNull.Value
// && Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) != 0)
//{
// rate1 = (Convert.ToDecimal(result) + (Convert.ToDecimal(result) * (Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) / 100)));
// if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
// {
// rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero);
// }
// rate2 = (rate2 + rate2 * (Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) / 100));
//}
//else if (dsConversion.Tables[0].Rows[x]["Surchargevalue"] != null && dsConversion.Tables[0].Rows[x]["Surchargevalue"] != DBNull.Value
// && Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"]) != 0)
//{
// rate1 = (Convert.ToDecimal(result) + Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"]));
// if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
// {
// rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero);
// }
// rate2 = (rate2 + Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"]));
//}
//else
//{
// rate1 = Convert.ToDecimal(result);
// if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
// {
// rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero);
// }
//}
rate1 = Convert.ToDecimal(result);
//if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
//{
// rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero);
//}
conversion.SetMultiplyRate(rate1);
//conversion.SetDivideRate(rate2);
if (!conversion.Save())
{
//status = "ConversionRateNotsaved";
}
}
}
}
}
}
}
}
}
}
catch (Exception ex)
{
//trx.Rollback();
//status = ex.Message;
//trx.Close();
return(status);
}
//trx.Commit();
//trx.Close();
ds.Dispose();
_lstCurr = null;
return(status);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// CMC.
/// <pre>
/// Expense
/// CashExpense DR
/// CashAsset CR
///Receipt
/// CashAsset DR
/// CashReceipt CR
/// Charge
/// Charge DR
/// CashAsset CR
/// Difference
/// CashDifference DR
/// CashAsset CR
/// Invoice
/// CashAsset DR
/// CashTransfer CR
/// Transfer
/// BankInTransit DR
/// CashAsset CR
/// </pre>
/// </summary>
/// <param name="?"></param>
/// <returns>Fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// create Fact Header
List <Fact> facts = new List <Fact>();
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_PROFITLOSS))
{
//Change By mohit
// Get Assigned Accounting Schemas based on organization
MProfitLossLines PLossline = new MProfitLossLines(GetCtx(), _lines[0].Get_ID(), null);
MProfitLoss PLoss = new MProfitLoss(GetCtx(), PLossline.GetC_ProfitLoss_ID(), null);
MAcctSchema HeaderAcctSchema = new MAcctSchema(GetCtx(), Util.GetValueOfInt(PLoss.Get_Value("C_AcctSchema_ID")), null);
List <int> _ListAcctSch = new List <int>();
// Profit & Loss account shall be posted only in accounting schema selected on header (By Ashish - discussed with Mukesh sir)
//_ListAcctSch = GetAcctSchemas(PLoss.GetAD_Org_ID());
_ListAcctSch.Add(HeaderAcctSchema.GetC_AcctSchema_ID());
if (_ListAcctSch.Count > 0)
{
int CurrencyType_ID = GetDefaultConversionType(GetAD_Client_ID(), GetAD_Org_ID());
for (int asch = 0; asch < _ListAcctSch.Count; asch++)
{
MAcctSchema AccountingSchema = new MAcctSchema(GetCtx(), _ListAcctSch[asch], null);
// Decimal grossAmt = getAmount(Doc.AMTTYPE_Gross);
SetC_Currency_ID(GetCurrency(AccountingSchema.GetC_AcctSchema_ID()));
// Commitment
Fact fact = new Fact(this, AccountingSchema, Fact.POST_Actual);
Decimal total = Env.ZERO, totalCredit = Env.ZERO, totalDebit = Env.ZERO;
Decimal credit = Env.ZERO, debit = Env.ZERO;
for (int i = 0; i < _lines.Length; i++)
{
DocLine dline = _lines[i];
MProfitLossLines line = new MProfitLossLines(GetCtx(), dline.Get_ID(), null);
if (Util.GetValueOfInt(HeaderAcctSchema.GetC_Currency_ID()) == Util.GetValueOfInt(AccountingSchema.GetC_Currency_ID()))
{
credit = Util.GetValueOfDecimal(dline.GetAmtSourceCr());
debit = Util.GetValueOfDecimal(dline.GetAmtSourceDr());
}
else
{
credit = MConversionRate.Convert(GetCtx(), Util.GetValueOfDecimal(dline.GetAmtSourceCr()), HeaderAcctSchema.GetC_Currency_ID(), AccountingSchema.GetC_Currency_ID(),
PLoss.GetDateAcct(), CurrencyType_ID, GetAD_Client_ID(), GetAD_Org_ID());
debit = MConversionRate.Convert(GetCtx(), Util.GetValueOfDecimal(dline.GetAmtSourceDr()), HeaderAcctSchema.GetC_Currency_ID(), AccountingSchema.GetC_Currency_ID(),
PLoss.GetDateAcct(), CurrencyType_ID, GetAD_Client_ID(), GetAD_Org_ID());
Util.GetValueOfDecimal(dline.GetAmtAcctDr());
}
if (credit > 0)
{
totalCredit = Decimal.Add(totalCredit, credit);
}
if (debit > 0)
{
totalDebit = Decimal.Add(totalDebit, debit);
}
// Account
MAccount expense = MAccount.Get(GetCtx(), GetAD_Client_ID(), GetAD_Org_ID(), AccountingSchema.GetC_AcctSchema_ID(), line.GetAccount_ID(), line.GetC_SubAcct_ID(), line.GetM_Product_ID(), line.GetC_BPartner_ID(), line.GetAD_OrgTrx_ID(),
line.GetC_LocFrom_ID(), line.GetC_LocTo_ID(), line.GetC_SalesRegion_ID(), line.GetC_Project_ID(), line.GetC_Campaign_ID(), line.GetC_Activity_ID(), line.GetUser1_ID(), line.GetUser2_ID(), line.GetUserElement1_ID(), line.GetUserElement2_ID(),
line.GetUserElement3_ID(), line.GetUserElement4_ID(), line.GetUserElement5_ID(), line.GetUserElement6_ID(), line.GetUserElement7_ID(), line.GetUserElement8_ID(), line.GetUserElement9_ID());
fact.CreateLine(dline, expense, GetCurrency(AccountingSchema.GetC_AcctSchema_ID()), debit, credit);
}
total = totalCredit - totalDebit;
if (total != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT IncomeSummary_Acct FROM C_AcctSchema_GL WHERE C_AcctSchema_ID=" + AccountingSchema.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
MAccount acct = MAccount.Get(GetCtx(), validComID);
fact.CreateLine(null, acct, GetC_Currency_ID(), total);
}
//if (TotalCurrLoss != Env.ZERO)
//{
// int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )"));
// MAccount acct = MAccount.Get(GetCtx(), validComID);
// TotalCurrLoss = MConversionRate.Convert(GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
// fact.CreateLine(null, acct,
// GetC_Currency_ID(), (TotalCurrLoss));
//}
facts.Add(fact);
}
}
}
return(facts);
}
} // doIt
/// <summary>
/// Process Expense Line
/// </summary>
/// <param name="te">header</param>
/// <param name="tel">line</param>
/// <param name="bp">bp</param>
private void ProcessLine(MTimeExpense te, MTimeExpenseLine tel, MBPartner bp)
{
if (_order == null)
{
log.Info("New Order for " + bp + ", Project=" + tel.GetC_Project_ID());
_order = new MOrder(GetCtx(), 0, Get_TrxName());
_order.SetAD_Org_ID(tel.GetAD_Org_ID());
_order.SetC_DocTypeTarget_ID(MOrder.DocSubTypeSO_Standard);
//
_order.SetBPartner(bp);
if (_order.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
_order = null;
return;
}
_order.SetM_Warehouse_ID(te.GetM_Warehouse_ID());
//Bhupendra: Add payment term
// to check for if payment term is null
if (bp.GetC_PaymentTerm_ID() == 0)
{
// set the default payment method as check
int payTerm = GetPaymentTerm();
if (payTerm <= 0)
{
message = Msg.GetMsg(GetCtx(), "IsActivePaymentTerm");
return;
}
else
{
_order.SetC_PaymentTerm_ID(payTerm);
}
}
else
{
//check weather paymentterm is active or not
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM C_PaymentTerm WHERE C_PaymentTerm_ID=" + bp.GetC_PaymentTerm_ID(), null, Get_Trx())).Equals("Y"))
{
_order.SetC_PaymentTerm_ID(bp.GetC_PaymentTerm_ID());
}
else
{
message = Msg.GetMsg(GetCtx(), "IsActivePaymentTerm");
return;
}
}
// Bhupendra: added a cond to check for payment method if null
// Added by mohit - to set payment method and sales rep id.
if (bp.GetVA009_PaymentMethod_ID() == 0)
{
// set the default payment method as check
int paymethod = GetPaymentMethod();
if (paymethod <= 0)
{
message = Msg.GetMsg(GetCtx(), "IsActivePaymentMethod");
return;
}
else
{
_order.SetVA009_PaymentMethod_ID(paymethod);
}
}
else
{
//check weather the PaymentMethod is active or not
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM VA009_PaymentMethod WHERE VA009_PaymentMethod_ID=" + bp.GetVA009_PaymentMethod_ID(), null, Get_Trx())).Equals("Y"))
{
_order.SetVA009_PaymentMethod_ID(bp.GetVA009_PaymentMethod_ID());
}
else
{
message = Msg.GetMsg(GetCtx(), "IsActivePaymentMethod");
return;
}
}
_order.SetSalesRep_ID(te.GetDoc_User_ID());
////Added By Arpit asked by Surya Sir..................29-12-2015
//_order.SetSalesRep_ID(GetCtx().GetAD_User_ID());
//End
if (tel.GetC_Activity_ID() != 0)
{
_order.SetC_Activity_ID(tel.GetC_Activity_ID());
}
if (tel.GetC_Campaign_ID() != 0)
{
_order.SetC_Campaign_ID(tel.GetC_Campaign_ID());
}
if (tel.GetC_Project_ID() != 0)
{
_order.SetC_Project_ID(tel.GetC_Project_ID());
// Optionally Overwrite BP Price list from Project
MProject project = new MProject(GetCtx(), tel.GetC_Project_ID(), Get_TrxName());
if (project.GetM_PriceList_ID() != 0)
{
//check weather the PriceList is active or not
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM M_PriceList WHERE M_PriceList_ID=" + project.GetM_PriceList_ID(), null, Get_Trx())).Equals("Y"))
{
_order.SetM_PriceList_ID(project.GetM_PriceList_ID());
}
else
{
message = Msg.GetMsg(GetCtx(), "IsActivePriceList");
return;
}
}
}
else
{
if (bp.GetM_PriceList_ID() != 0)
{
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM M_PriceList WHERE M_PriceList_ID=" + bp.GetM_PriceList_ID(), null, Get_Trx())).Equals("Y"))
{
_order.SetM_PriceList_ID(bp.GetM_PriceList_ID());
}
else
{
message = Msg.GetMsg(GetCtx(), "IsActivePriceList");
return;
}
}
}
_order.SetSalesRep_ID(te.GetDoc_User_ID());
//
if (!_order.Save())
{
Rollback();
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
message = pp.GetName();
//if GetName is Empty then it will check GetValue
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg("", pp.GetValue());
}
}
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg(GetCtx(), "CantSaveOrder");
}
return;
//throw new Exception("Cannot save Order");
}
}
else
{
// Update Header info
if (tel.GetC_Activity_ID() != 0 && tel.GetC_Activity_ID() != _order.GetC_Activity_ID())
{
_order.SetC_Activity_ID(tel.GetC_Activity_ID());
}
if (tel.GetC_Campaign_ID() != 0 && tel.GetC_Campaign_ID() != _order.GetC_Campaign_ID())
{
_order.SetC_Campaign_ID(tel.GetC_Campaign_ID());
}
if (!_order.Save())
{
Rollback();
//get error message from ValueNamePair
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
message = pp.GetName();
//if GetName is Empty then it will check GetValue
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg("", pp.GetValue());
}
}
//it will check message is null or not
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg(GetCtx(), "CantSaveOrder");
}
return;
//new Exception("Cannot save Order");
}
}
// OrderLine
MOrderLine ol = new MOrderLine(_order);
//
if (tel.GetM_Product_ID() != 0)
{
ol.SetM_Product_ID(tel.GetM_Product_ID(),
tel.GetC_UOM_ID());
}
if (tel.GetS_ResourceAssignment_ID() != 0)
{
ol.SetS_ResourceAssignment_ID(tel.GetS_ResourceAssignment_ID());
}
// Set charge ID
if (tel.GetC_Charge_ID() != 0)
{
ol.SetC_Charge_ID(tel.GetC_Charge_ID());
ol.SetPriceActual(tel.GetExpenseAmt());
ol.SetQty(tel.GetQty());
}
ol.SetQty(tel.GetQtyInvoiced()); //
ol.SetDescription(tel.GetDescription());
//
ol.SetC_Project_ID(tel.GetC_Project_ID());
ol.SetC_ProjectPhase_ID(tel.GetC_ProjectPhase_ID());
ol.SetC_ProjectTask_ID(tel.GetC_ProjectTask_ID());
ol.SetC_Activity_ID(tel.GetC_Activity_ID());
ol.SetC_Campaign_ID(tel.GetC_Campaign_ID());
//
Decimal price = tel.GetPriceInvoiced(); //
if (price.CompareTo(Env.ZERO) != 0)
{
if (tel.GetC_Currency_ID() != _order.GetC_Currency_ID())
{
price = MConversionRate.Convert(GetCtx(), price,
tel.GetC_Currency_ID(), _order.GetC_Currency_ID(),
_order.GetAD_Client_ID(), _order.GetAD_Org_ID());
}
ol.SetPrice(price);
// added by Bhupendra to set the entered price
ol.SetPriceEntered(price);
}
else
{
ol.SetPrice();
}
if (tel.GetC_UOM_ID() != 0 && ol.GetC_UOM_ID() == 0)
{
ol.SetC_UOM_ID(tel.GetC_UOM_ID());
}
ol.SetTax();
if (!ol.Save())
{
Rollback();
//get error message from ValueNamePair
ValueNamePair pp = VLogger.RetrieveError();
if (pp != null)
{
message = pp.GetName();
//if GetName is Empty then it will check GetValue
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg("", pp.GetValue());
}
}
//it will check message is null or not
if (string.IsNullOrEmpty(message))
{
message = Msg.GetMsg(GetCtx(), "CantSaveOrderLine");
}
return;
//throw new Exception("Cannot save Order Line");
}
// Update TimeExpense Line
tel.SetC_OrderLine_ID(ol.GetC_OrderLine_ID());
if (tel.Save())
{
log.Fine("Updated " + tel + " with C_OrderLine_ID");
}
else
{
log.Log(Level.SEVERE, "Not Updated " + tel + " with C_OrderLine_ID");
}
} // processLine
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"));
};
}
protected override string DoIt()
{
batchid = GetBatchId();
if (batchid > 0)
{
StringBuilder _sql = new StringBuilder();
MVA009Batch batch = new MVA009Batch(GetCtx(), batchid, Get_TrxName());
MBankAccount _bankacc = new MBankAccount(GetCtx(), batch.GetC_BankAccount_ID(), Get_TrxName());
MVA009PaymentMethod _paymthd = null;
MVA009BatchLines line = null;
MVA009BatchLineDetails lineDetail = null;
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 NOT IN ( 'Y','J') AND CI.AD_Client_ID = " + GetAD_Client_ID() + " AND CI.AD_Org_ID = " + 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)
{
_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(), null, Get_TrxName());
if (ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
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(), null, Get_TrxName());
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())
{
line.SetProcessed(true);
line.Save();
_VA009_BatchLine_ID = line.GetVA009_BatchLines_ID();
}
else
{
_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"]));
if (Util.GetValueOfDateTime(ds.Tables[0].Rows[i]["DiscountDate"]) >= Util.GetValueOfDateTime(batch.GetVA009_DocumentDate()))
{
dueamt = dueamt - (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["DiscountAmt"]));
// 145-2.88
}
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 (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 = 0;
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"]));
}
if (!lineDetail.Save())
{
//return"BatchLine Not Saved";
}
else
{
lineDetail.SetProcessed(true);
MInvoicePaySchedule _invpay = new MInvoicePaySchedule(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_InvoicePaySchedule_id"]), Get_TrxName());
_invpay.SetVA009_ExecutionStatus("Y");
_invpay.Save();
lineDetail.Save();
}
}
batch.SetVA009_GenerateLines("Y");
batch.SetProcessed(true);
batch.Save();
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());
batch.Save();
if (_paymthd.GetVA009_PaymentRule() == "M")
{
VA009_CreatePayments payment = new VA009_CreatePayments();
payment.DoIt(batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName(), 0);
}
else if (_paymthd.GetVA009_PaymentRule() == "E")
{
VA009_ICICI_Snorkel _Snrkl = new VA009_ICICI_Snorkel();
_Snrkl.GetMethod(batch.GetVA009_Batch_ID(), GetCtx(), Get_TrxName());
}
}
return(Msg.GetMsg(GetCtx(), "VA009_BatchLineCrtd"));;
}
else
{
DB.ExecuteQuery("DELETE FROM VA009_Batch WHERE VA009_Batch_ID=" + batchid, null, Get_TrxName());
return(Msg.GetMsg(GetCtx(), "VA009_BatchLineNotCrtd"));
}
}
else
{
DB.ExecuteQuery("DELETE FROM VA009_Batch WHERE VA009_Batch_ID=" + batchid, null, Get_TrxName());
return(Msg.GetMsg(GetCtx(), "VA009_BatchNotCrtd"));
}
}
protected override string DoIt()
{
MCash cashheader = new MCash(GetCtx(), GetRecord_ID(), Get_Trx());
sql = "select C_Currency_ID from C_CashBook where c_Cashbook_id=" + cashheader.GetC_CashBook_ID();
int C_Currencyheader_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
sql = "select * from C_Cashline WHERE C_Cash_ID=" + GetRecord_ID();
DataSet dscashline = DB.ExecuteDataset(sql, null, Get_Trx());
if (dscashline != null)
{
if (dscashline.Tables[0].Rows.Count > 0)
{
for (i = 0; i < dscashline.Tables[0].Rows.Count; i++)
{
cashline = new MCashLine(GetCtx(), Util.GetValueOfInt(dscashline.Tables[0].Rows[i]["C_CashLine_ID"]), Get_Trx());
if (cashline.GetCashType().ToString() == "A" || cashline.GetCashType().ToString() == "F")
{
sql = "Select * from C_Cash where C_CashBook_Id=" + cashline.GetC_CashBook_ID() + " and docstatus='DR' and DateAcct=" + GlobalVariable.TO_DATE(DateTime.Now, true);
DataSet dscashbook = DB.ExecuteDataset(sql, null, Get_Trx());
if (dscashbook != null)
{
if (dscashbook.Tables[0].Rows.Count > 0)
{
int j;
for (j = 0; j < dscashbook.Tables[0].Rows.Count; j++)
{
cash = new MCash(GetCtx(), Util.GetValueOfInt(dscashbook.Tables[0].Rows[j]["C_Cash_ID"]), Get_Trx());
if (!_cashIds.Contains(cash.GetC_Cash_ID()))
{
_cashIds.Add(cash.GetC_Cash_ID());
}
//ViennaAdvantage.Model.MCashLine cashline1 = new ViennaAdvantage.Model.MCashLine(GetCtx(), 0, Get_Trx());
MCashLine cashline1 = new MCashLine(GetCtx(), 0, Get_Trx());
cashline1.SetC_Cash_ID(cash.GetC_Cash_ID());
cashline1.SetAD_Client_ID(cash.GetAD_Client_ID());
cashline1.SetAD_Org_ID(cash.GetAD_Org_ID());
if (cashline.GetCashType().ToString() == "A")
{
cashline1.SetCashType("F");
}
if (cashline.GetCashType().ToString() == "F")
{
cashline1.SetCashType("A");
}
// Added by Bharat as discussed with Ravikant on 22 March 2017
if (cashline1.Get_ColumnIndex("C_ConversionType_ID") > 0)
{
cashline1.SetC_ConversionType_ID(cashline.GetC_ConversionType_ID());
}
cashline1.SetC_CashBook_ID(cashheader.GetC_CashBook_ID());
cashline1.SetC_BPartner_ID(cashline.GetC_BPartner_ID());
sql = "select C_Currency_ID from C_CashBook where c_Cashbook_id=" + cash.GetC_CashBook_ID();
Currency_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
if (Currency_ID == C_Currencyheader_ID)
{
cashline1.SetC_Currency_ID(C_Currencyheader_ID);
cashline1.SetAmount(Decimal.Negate(cashline.GetAmount()));
cashline1.SetConvertedAmt(Util.GetValueOfString(Decimal.Negate(cashline.GetAmount())));
}
else
{
//cashline1.SetC_Currency_ID(Currency_ID);
// Change by Bharat as discussed with Ravikant on 22 March 2017
cashline1.SetC_Currency_ID(C_Currencyheader_ID);
if (cashline1.Get_ColumnIndex("C_ConversionType_ID") > 0)
{
//_Curencyrate = MConversionRate.GetRate(C_Currencyheader_ID, Currency_ID, cash.GetDateAcct(), cashline.GetC_ConversionType_ID(),
// cash.GetAD_Client_ID(), cash.GetAD_Org_ID());
convertedamount = Decimal.Negate(Util.GetValueOfDecimal(cashline.GetConvertedAmt()));
// if converted amount not found then get amount based on currency conversion avaliable
if (cashline.GetAmount() != 0 && convertedamount == 0)
{
convertedamount = MConversionRate.Convert(GetCtx(), Decimal.Negate(cashline.GetAmount()), Currency_ID, C_Currencyheader_ID,
cash.GetDateAcct(), cashline.GetC_ConversionType_ID(), cash.GetAD_Client_ID(), cash.GetAD_Org_ID());
cashline.SetConvertedAmt(Util.GetValueOfString(Decimal.Negate(convertedamount)));
}
if (cashline.GetAmount() != 0 && convertedamount == 0)
{
return(Msg.GetMsg(GetCtx(), "NoCurrencyRateDefined"));
}
else if (cashline.GetAmount() == 0)
{
return("Amount Should be greater than zero");
}
cashline1.SetAmount(convertedamount);
cashline1.SetConvertedAmt(Util.GetValueOfString(Decimal.Negate(cashline.GetAmount())));
}
else
{
sql = "select multiplyrate from c_conversion_rate where isactive='Y' and c_currency_id=" + C_Currencyheader_ID + " and c_currency_to_id=" + Currency_ID;
_Curencyrate = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_Trx()));
if (_Curencyrate == 0)
{
return(Msg.GetMsg(GetCtx(), "NoCurrencyRateDefined"));
}
else
{
convertedamount = Decimal.Round(Decimal.Multiply(_Curencyrate, Decimal.Negate(cashline.GetAmount())), 2);
}
cashline1.SetConvertedAmt(Util.GetValueOfString(convertedamount));
cashline1.SetAmount(convertedamount);
}
}
cashline1.SetC_Charge_ID(cashline.GetC_Charge_ID());
if (cashline.GetCashType().ToString() == "A")
{
cashline1.SetVSS_PAYMENTTYPE("R");
}
if (cashline.GetCashType().ToString() == "F")
{
cashline1.SetVSS_PAYMENTTYPE("P");
}
cashline1.SetC_CashLine_ID_1(cashline.GetC_CashLine_ID());
if (!cashline1.Save())
{
Rollback();
log.Severe("NotSaved");
return(" NotSaved Cashline1 [ Header Found ] ");
}
if (cashline.GetCashType().ToString() == "F")
{
cashline.SetC_CashLine_ID_1(cashline1.GetC_CashLine_ID());
}
if (!cashline.Save())
{
Rollback();
log.Severe("Not Saved");
return(" NotSaved Cashline [ Header Found ] ");
}
//change by Amit 1-june-2016 after discussion with ravikant
//int BeginBal = Util.GetValueOfInt(DB.ExecuteScalar("select completedbalance from c_cashbook where c_cashbook_id=" + cash.GetC_CashBook_ID(), null, Get_Trx()));
//cash.SetBeginningBalance(BeginBal);
//if (!cash.Save(Get_Trx()))
//{
// Rollback();
// log.Severe("Not Saved");
// return " NotSaved Cash [ Header Found ] ";
//}
//end
break;
}
}
else
{
dscashbook.Dispose();
MCash cash1 = new MCash(GetCtx(), 0, Get_Trx());
cash1.SetAD_Client_ID(cashheader.GetAD_Client_ID());
cash1.SetAD_Org_ID(cashheader.GetAD_Org_ID());
cash1.SetC_DocType_ID(cashheader.GetC_DocType_ID());
cash1.SetName(Util.GetValueOfString(System.DateTime.Today.ToShortDateString()));
cash1.SetC_CashBook_ID(cashline.GetC_CashBook_ID());
cash1.SetDateAcct(DateTime.Now);
cash1.SetStatementDate(DateTime.Now);
//change by Amit 1-june-2016 after discussion with ravikant
int BeginBal = Util.GetValueOfInt(DB.ExecuteScalar("select SUM(completedbalance + runningbalance) from c_cashbook where c_cashbook_id=" + cashline.GetC_CashBook_ID(), null, Get_Trx()));
cash1.SetBeginningBalance(BeginBal);
//end
//Change by Bharat as discussed with Ravikant on 22 March 2017
sql = "select C_Currency_ID from C_CashBook where c_Cashbook_id=" + cash1.GetC_CashBook_ID();
Currency_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
if (cash1.Get_ColumnIndex("C_Currency_ID") > 0)
{
cash1.SetC_Currency_ID(Currency_ID);
}
//End
if (!cash1.Save())
{
Rollback();
VAdvantage.Model.ValueNamePair ppE = VLogger.RetrieveError();
if (ppE != null)
{
return(ppE.GetName() + "<--->" + ppE.GetValue());
}
return("cash1 not saved [header not found->]");
}
// return cash1.GetC_Cash_ID() + "suc";
if (!_cashIds.Contains(cash1.GetC_Cash_ID()))
{
_cashIds.Add(cash1.GetC_Cash_ID());
}
MCashLine cashline1 = new MCashLine(GetCtx(), 0, Get_Trx());
cashline1.SetC_Cash_ID(cash1.GetC_Cash_ID());
cashline1.SetAD_Client_ID(cash1.GetAD_Client_ID());
cashline1.SetAD_Org_ID(cash1.GetAD_Org_ID());
if (cashline.GetCashType().ToString() == "A")
{
cashline1.SetCashType("F");
}
if (cashline.GetCashType().ToString() == "F")
{
cashline1.SetCashType("A");
}
if (cashline1.Get_ColumnIndex("C_ConversionType_ID") > 0)
{
cashline1.SetC_ConversionType_ID(cashline.GetC_ConversionType_ID());
}
cashline1.SetC_CashBook_ID(cashheader.GetC_CashBook_ID());
cashline1.SetC_BPartner_ID(cashline.GetC_BPartner_ID());
//Change by Bharat as discussed with Ravikant on 22 March 2017
//sql = "select C_Currency_ID from C_CashBook where c_Cashbook_id=" + cash1.GetC_CashBook_ID();
//Currency_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql, null, Get_Trx()));
if (Currency_ID == C_Currencyheader_ID)
{
cashline1.SetAmount(Decimal.Negate(cashline.GetAmount()));
cashline1.SetC_Currency_ID(C_Currencyheader_ID);
cashline1.SetConvertedAmt(Util.GetValueOfString(Decimal.Negate(cashline.GetAmount())));
}
else
{
//Change by Bharat as discussed with Ravikant on 22 March 2017
cashline1.SetC_Currency_ID(C_Currencyheader_ID);
if (cashline1.Get_ColumnIndex("C_ConversionType_ID") > 0)
{
//_Curencyrate = MConversionRate.GetRate(C_Currencyheader_ID, Currency_ID, cashheader.GetDateAcct(), cashline.GetC_ConversionType_ID(),
// cashheader.GetAD_Client_ID(), cashheader.GetAD_Org_ID());
convertedamount = Decimal.Negate(Util.GetValueOfDecimal(cashline.GetConvertedAmt()));
// if converted amount not found then get amount based on currency conversion avaliable
if (cashline.GetAmount() != 0 && convertedamount == 0)
{
convertedamount = MConversionRate.Convert(GetCtx(), Decimal.Negate(cashline.GetAmount()), Currency_ID, C_Currencyheader_ID,
cash1.GetDateAcct(), cashline.GetC_ConversionType_ID(), cash1.GetAD_Client_ID(), cash1.GetAD_Org_ID());
cashline.SetConvertedAmt(Util.GetValueOfString(Decimal.Negate(convertedamount)));
}
if (cashline.GetAmount() != 0 && convertedamount == 0)
{
return(Msg.GetMsg(GetCtx(), "NoCurrencyRateDefined"));
}
else if (cashline.GetAmount() == 0)
{
return("Amount Should be greater than zero");
}
cashline1.SetAmount(convertedamount);
cashline1.SetConvertedAmt(Util.GetValueOfString(Decimal.Negate(cashline.GetAmount())));
}
else
{
sql = "select multiplyrate from c_conversion_rate where isactive='Y' and c_currency_id=" + C_Currencyheader_ID + " and c_currency_to_id=" + Currency_ID;
_Curencyrate = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, Get_Trx()));
if (_Curencyrate == 0)
{
return(Msg.GetMsg(GetCtx(), "NoCurrencyRateDefined"));
}
else
{
convertedamount = Decimal.Round(Decimal.Multiply(_Curencyrate, Decimal.Negate(cashline.GetAmount())), 2);
}
cashline1.SetConvertedAmt(Util.GetValueOfString(convertedamount));
cashline1.SetAmount(convertedamount);
}
}
cashline1.SetC_Charge_ID(cashline.GetC_Charge_ID());
if (cashline.GetCashType().ToString() == "A")
{
cashline1.SetVSS_PAYMENTTYPE("R");
}
if (cashline.GetCashType().ToString() == "F")
{
cashline1.SetVSS_PAYMENTTYPE("P");
}
cashline1.SetC_CashLine_ID_1(cashline.GetC_CashLine_ID());
if (!cashline1.Save(Get_Trx()))
{
Rollback();
log.Severe("NotSaved");
return(" NotSaved Cashline1 [ Header not Found ] ");
}
if (cashline.GetCashType().ToString() == "F")
{
cashline.SetC_CashLine_ID_1(cashline1.GetC_CashLine_ID());
}
if (!cashline.Save(Get_Trx()))
{
Rollback();
log.Severe("NotSaved");
return(" NotSaved Cashline [ Header not Found ] ");
}
//change by Amit 1-june-2016 after discussion with ravikant
//int BeginBal = Util.GetValueOfInt(DB.ExecuteScalar("select completedbalance from c_cashbook where c_cashbook_id=" + cash1.GetC_CashBook_ID(), null, Get_Trx()));
//cash1.SetBeginningBalance(BeginBal);
//if (!cash1.Save())
//{
// Rollback();
// log.Severe("NotSaved");
// return " NotSaved Cash1 [ Header not Found(bb) ] ";
//}
//end
}
}
else
{
//dscashbook.Dispose();
}
}
else
{
cashtype++;
}
}
// Discussed with Ravikant it is re updating the Header so commented on 23-March-2017 by Bharat
//decimal OpenBal = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT completedbalance FROM C_CashBook WHERE c_cashbook_id=" + cashheader.GetC_CashBook_ID() + "", null, Get_Trx()));
//cashheader.SetBeginningBalance(OpenBal);
//if (!cashheader.Save())
//{
// Rollback();
// log.Severe("Not Saved");
//}
}
else
{
dscashline.Dispose();
cashheader.SetGenerateCashBookTransfer("Y");
if (!cashheader.Save())
{
Rollback();
log.Severe("NotSaved");
}
return(Msg.GetMsg(GetCtx(), "NoCashLineExist"));
}
if (_cashIds.Count > 0)
{
for (int k = 0; k < _cashIds.Count; k++)
{
cash = new MCash(GetCtx(), _cashIds[k], Get_Trx());
cash.SetDocStatus(cash.CompleteIt());
if (!cash.Save())
{
Rollback();
log.Severe("NotSaved");
}
}
}
}
cashheader.SetGenerateCashBookTransfer("Y");
if (!cashheader.Save())
{
log.Severe("NotSaved");
}
if (cashtype == i)
{
return(Msg.GetMsg(GetCtx(), "NoCashLineExist"));
}
return("Completed");
}
protected override string DoIt()
{
string status = "OK";
// Trx trx = Get_Trx();
XDocument doc = null;
decimal exchangeRate = 0;
string currencyQty = "";
string content = "";
ds.Clear();
try
{
// Get Currency from all Accounting Schemas available in the Tenant.
ds = DB.ExecuteDataset(@"SELECT DISTINCT cl.AD_Client_ID,
cl.AD_Org_ID,
cl.CurrencyRateUpdateFrequency,
acct.C_Currency_ID
,cr.ISO_Code,cl.C_CurrencySource_ID
FROM AD_Client cl
INNER JOIN C_AcctSchema acct
ON acct.AD_Client_ID =cl.AD_Client_ID
INNER JOIN C_Currency cr on cr.C_Currency_ID=acct.C_Currency_ID
WHERE cl.IsActive='Y' AND cl.AD_Client_ID!=0 AND cl.UpdateCurrencyRate='A' AND cl.IsMultiCurrency='Y'
AND acct.IsActive='Y' AND cl.currencyrateupdatefrequency IS NOT NULL", null, Get_Trx());
// Where ci.AD_CLient_ID= " + GetAD_Client_ID());
//in this DataSet we'll get CLient's Base Currency & the Currency ID
// int clientCount = ds.Tables[0].Rows.Count;
if (ds.Tables[0].Rows.Count > 0)
{
_lstCurr = new List <CurrencyProp>();
}
else
{
return(VAdvantage.Utility.Msg.GetMsg(GetCtx(), "NoMultiCurrencySettingsFound"));
}
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
CurrencyProp _curr = new CurrencyProp();
_curr.baseCurrency = Convert.ToString(ds.Tables[0].Rows[j]["ISO_Code"]);
_curr.baseCurrencyID = Convert.ToInt32(ds.Tables[0].Rows[j]["C_Currency_ID"]);
_curr.frequency = Convert.ToString(ds.Tables[0].Rows[j]["CurrencyRateUpdateFrequency"]);
_curr.AD_Client_ID = Convert.ToInt32(ds.Tables[0].Rows[j]["AD_Client_ID"]);
_curr.AD_Org_ID = Convert.ToInt32(ds.Tables[0].Rows[j]["AD_Org_ID"]);
_curr.CurrencySource = Convert.ToInt32(ds.Tables[0].Rows[j]["C_CurrencySource_ID"]);
_lstCurr.Add(_curr);
}
ds.Clear();
sql.Append(@"Select Cur.ISO_Code, Cur.C_Currency_ID From C_Currency Cur Where Cur.IsMyCurrency='Y' And Cur.IsActive='Y' ");
ds = DB.ExecuteDataset(sql.ToString(), null, Get_Trx()); // Here we get all currencies in which our Client is in dealing with
if (ds != null)
{
for (Int32 k = 0; k < _lstCurr.Count; k++)
{
sql.Clear(); // Get UTRL and Api Key From Currency Source
sql.Append("SELECT URL, ApiKey FROM C_CurrencySource WHERE C_CurrencySource_ID=" + _lstCurr[k].CurrencySource);
DataSet dsSource = DB.ExecuteDataset(sql.ToString(), null, Get_Trx());
if (dsSource != null && dsSource.Tables.Count > 0 && dsSource.Tables[0].Rows.Count > 0)
{
string currencySourceName = Util.GetValueOfString(dsSource.Tables[0].Rows[0]["URL"]);
string apiKey = Util.GetValueOfString(dsSource.Tables[0].Rows[0]["ApiKey"]);
if (!String.IsNullOrEmpty(currencySourceName) && currencySourceName.ToLower().Contains("pwebapps.ezv.admin.ch"))
{
doc = XDocument.Load("http://www.pwebapps.ezv.admin.ch/apps/rates/rate/getxml?activeSearchType=userDefinedDay");
}
else if (!String.IsNullOrEmpty(currencySourceName) && currencySourceName.ToLower().Contains("api.bnm.gov.my"))
{
content = GetConvertedCurrencyValue("", "", currencySourceName, apiKey);
}
DataSet dsConversion = DB.ExecuteDataset(@"SELECT C_ConversionType_id, Surchargepercentage,Surchargevalue,CurrencyRateUpdateFrequency
FROM c_conversiontype WHERE isautocalculate='Y' AND isactive ='Y'", null, Get_Trx());
if (dsConversion != null && dsConversion.Tables[0].Rows.Count > 0)
{
for (int x = 0; x < dsConversion.Tables[0].Rows.Count; x++)
{
int defaultconversionType = 0;
defaultconversionType = Convert.ToInt32(dsConversion.Tables[0].Rows[x]["C_ConversionType_id"]);
MConversionRate conversion = null;
Decimal rate1 = 0;
Decimal rate2 = 0;
Decimal one = new Decimal(1.0);
string updateFrequency = _lstCurr[k].frequency;
if (dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"] != null && dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"] != DBNull.Value &&
Convert.ToString(dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"]) != "")
{
updateFrequency = Convert.ToString(dsConversion.Tables[0].Rows[x]["CurrencyRateUpdateFrequency"]);
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCurrency = ds.Tables[0].Rows[i]["ISO_Code"].ToString();
myCurrencyID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Currency_ID"]);
if (!String.IsNullOrEmpty(currencySourceName) && currencySourceName.ToLower().Contains("pwebapps.ezv.admin.ch"))
{
currencyQty = "";
exchangeRate = 0;
if (doc != null)
{
var devise = from p in doc.Descendants()
where p.Name.LocalName == "devise"
select p;
if (devise != null)
{
foreach (XElement d in devise.Where(d => d.Attributes("code").FirstOrDefault().Value == myCurrency.ToLower()))
{
var data = from p in d.Descendants()
where p.Name.LocalName == "waehrung" || p.Name.LocalName == "kurs"
select p;
if (data != null)
{
currencyQty = data.Where(n => n.Name.LocalName == "waehrung").FirstOrDefault().Value.ToString();
exchangeRate = Util.GetValueOfDecimal(data.Where(n => n.Name.LocalName == "kurs").FirstOrDefault().Value);
}
}
}
if (!String.IsNullOrEmpty(currencyQty) && exchangeRate != 0)
{
decimal qty = Util.GetValueOfDecimal(currencyQty.Substring(0, currencyQty.Length - 4));
exchangeRate /= qty;
}
else
{
continue;
}
conversion = new MConversionRate(GetCtx(), 0, Get_Trx());
conversion.SetAD_Org_ID((_lstCurr[k].AD_Org_ID));
conversion.SetAD_Client_ID(_lstCurr[k].AD_Client_ID);
//conversion.SetValidFrom(DateTime.Now.AddDays(-1));
conversion.SetValidFrom(DateTime.Now);
if (updateFrequency.Equals("D"))
{
conversion.SetValidTo(DateTime.Now);
}
else if (updateFrequency.Equals("W"))
{
conversion.SetValidTo(DateTime.Now.AddDays(7));
}
else if (updateFrequency.Equals("M"))
{
conversion.SetValidTo(DateTime.Now.AddMonths(1));
}
conversion.SetC_ConversionType_ID(defaultconversionType);
conversion.SetC_Currency_ID(myCurrencyID);
conversion.SetC_Currency_To_ID(318);
rate1 = Convert.ToDecimal(exchangeRate);
conversion.SetMultiplyRate(rate1);
if (!conversion.Save())
{
}
}
}
else if (!String.IsNullOrEmpty(currencySourceName) && currencySourceName.ToLower().Contains("api.bnm.gov.my"))
{
exchangeRate = 0;
if (!String.IsNullOrEmpty(content))
{
dynamic rates = JsonConvert.DeserializeObject <dynamic>(content);
if (rates != null && rates.data != null)
{
for (int j = 0; j < rates.data.Count; j++)
{
if (rates.data[j].currency_code == myCurrency.ToUpper())
{
int unit = Util.GetValueOfInt(rates.data[j].unit);
exchangeRate = Util.GetValueOfDecimal(rates.data[j].rate["middle_rate"]);
if (exchangeRate > 0 && unit > 1)
{
exchangeRate /= unit;
break;
}
}
}
}
if (exchangeRate != 0)
{
conversion = new MConversionRate(GetCtx(), 0, Get_Trx());
conversion.SetAD_Org_ID((_lstCurr[k].AD_Org_ID));
conversion.SetAD_Client_ID(_lstCurr[k].AD_Client_ID);
//conversion.SetValidFrom(DateTime.Now.AddDays(-1));
conversion.SetValidFrom(DateTime.Now);
if (updateFrequency.Equals("D"))
{
conversion.SetValidTo(DateTime.Now);
}
else if (updateFrequency.Equals("W"))
{
conversion.SetValidTo(DateTime.Now.AddDays(7));
}
else if (updateFrequency.Equals("M"))
{
conversion.SetValidTo(DateTime.Now.AddMonths(1));
}
conversion.SetC_ConversionType_ID(defaultconversionType);
conversion.SetC_Currency_ID(myCurrencyID);
conversion.SetC_Currency_To_ID(301);
rate1 = Convert.ToDecimal(exchangeRate);
conversion.SetMultiplyRate(rate1);
if (!conversion.Save())
{
}
}
}
}
else
{
sql.Clear();
sql.Append(@"Select ValidTo from C_Conversion_Rate where IsActive='Y' AND C_ConversionType_id=" + defaultconversionType + " AND C_Currency_ID=" + _lstCurr[k].baseCurrencyID + " AND C_Currency_To_ID=" + myCurrencyID
+ " AND Created=(SELECT Max(Created) FROM C_Conversion_Rate WHERE isactive ='Y' AND C_ConversionType_id=" + defaultconversionType + " AND "
+ " C_Currency_ID =" + _lstCurr[k].baseCurrencyID + " AND C_Currency_To_ID=" + myCurrencyID + ") AND AD_Client_ID = " + _lstCurr[k].AD_Client_ID + "AND AD_Org_ID= " + _lstCurr[k].AD_Org_ID);
//the Maximum date from Converted rate of every currency
object validDate = DB.ExecuteScalar(sql.ToString(), null, Get_Trx());
//Check if valid date available.. and less than current date..
//By Karan 22 June
if (validDate != null && validDate != DBNull.Value && DateTime.Now.Date > Convert.ToDateTime(validDate).Date)
{
if (!String.IsNullOrEmpty(myCurrency) && !String.IsNullOrEmpty(_lstCurr[k].baseCurrency) &&
!String.IsNullOrEmpty(currencySourceName) && (myCurrencyID != _lstCurr[k].baseCurrencyID))
{
String result = GetConvertedCurrencyValue(_lstCurr[k].baseCurrency, myCurrency, currencySourceName, apiKey);
if (!String.IsNullOrEmpty(result))
{
conversion = new MConversionRate(GetCtx(), 0, Get_Trx());
conversion.SetAD_Org_ID((_lstCurr[k].AD_Org_ID));
conversion.SetAD_Client_ID(_lstCurr[k].AD_Client_ID);
//conversion.SetValidFrom(DateTime.Now.AddDays(-1));
conversion.SetValidFrom(DateTime.Now);
if (updateFrequency.Equals("D"))
{
conversion.SetValidTo(DateTime.Now);
}
else if (updateFrequency.Equals("W"))
{
conversion.SetValidTo(DateTime.Now.AddDays(7));
}
else if (updateFrequency.Equals("M"))
{
conversion.SetValidTo(DateTime.Now.AddMonths(1));
}
conversion.SetC_ConversionType_ID(defaultconversionType);
conversion.SetC_Currency_ID(_lstCurr[k].baseCurrencyID);
conversion.SetC_Currency_To_ID(myCurrencyID);
rate2 = VAdvantage.Utility.Env.ZERO;
one = new Decimal(1.0);
//if (dsConversion.Tables[0].Rows[x]["Surchargepercentage"] != null && dsConversion.Tables[0].Rows[x]["Surchargepercentage"] != DBNull.Value
// && Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) != 0)
//{
// rate1 = (Convert.ToDecimal(result) + (Convert.ToDecimal(result) * (Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) / 100)));
// if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
// {
// rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero);
// }
// rate2 = (rate2 + rate2 * (Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargepercentage"]) / 100));
//}
//else if (dsConversion.Tables[0].Rows[x]["Surchargevalue"] != null && dsConversion.Tables[0].Rows[x]["Surchargevalue"] != DBNull.Value
// && Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"]) != 0)
//{
// rate1 = (Convert.ToDecimal(result) + Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"]));
// if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
// {
// rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero);
// }
// rate2 = (rate2 + Convert.ToDecimal(dsConversion.Tables[0].Rows[x]["Surchargevalue"]));
//}
//else
//{
// rate1 = Convert.ToDecimal(result);
// if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
// {
// rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero);
// }
//}
rate1 = Convert.ToDecimal(result);
//if (System.Convert.ToDouble(rate1) != 0.0) // no divide by zero
//{
// rate2 = Decimal.Round(Decimal.Divide(one, Convert.ToDecimal(result)), 12);// MidpointRounding.AwayFromZero);
//}
conversion.SetMultiplyRate(rate1);
//conversion.SetDivideRate(rate2);
if (!conversion.Save())
{
//status = "ConversionRateNotsaved";
}
}
}
}
}
}
}
}
}
}
}
}
catch (Exception ex)
{
//trx.Rollback();
//status = ex.Message;
//trx.Close();
return(status);
}
//trx.Commit();
//trx.Close();
ds.Dispose();
_lstCurr = null;
return(status);
}
