} // MLandedCostAllocation
/**
* Parent Constructor
* @param parent parent
* @param M_CostElement_ID cost element
*/
public MLandedCostAllocation(MInvoiceLine parent, int M_CostElement_ID)
: this(parent.GetCtx(), 0, parent.Get_TrxName())
{
SetClientOrg(parent);
SetC_InvoiceLine_ID(parent.GetC_InvoiceLine_ID());
SetM_CostElement_ID(M_CostElement_ID);
} // MLandedCostAllocation
} // MTaxDeclarationLine
/// <summary>
/// Parent Constructor
/// </summary>
/// <param name="parent">parent</param>
/// <param name="invoice">invoice</param>
/// <param name="iLine">invoice line</param>
public MTaxDeclarationLine(MTaxDeclaration parent, MInvoice invoice, MInvoiceLine iLine) : this(parent.GetCtx(), 0, parent.Get_TrxName())
{
// this(parent.getCtx(), 0, parent.get_TrxName());
SetClientOrg(invoice);
SetC_TaxDeclaration_ID(parent.GetC_TaxDeclaration_ID());
SetIsManual(false);
//
SetC_Invoice_ID(invoice.GetC_Invoice_ID());
SetC_BPartner_ID(invoice.GetC_BPartner_ID());
SetC_Currency_ID(invoice.GetC_Currency_ID());
SetDateAcct(invoice.GetDateAcct());
//
SetC_InvoiceLine_ID(iLine.GetC_InvoiceLine_ID());
SetC_Tax_ID(iLine.GetC_Tax_ID());
if (invoice.IsTaxIncluded())
{
SetTaxBaseAmt(iLine.GetLineNetAmt());
SetTaxAmt(iLine.GetTaxAmt());
}
else
{
SetTaxBaseAmt(iLine.GetLineNetAmt());
SetTaxAmt(iLine.GetTaxAmt());
}
} // MTaxDeclarationLine
/// <summary>
/// Set C_InvoiceLine_ID
/// </summary>
/// <param name="line">line</param>
public void SetC_InvoiceLine_ID(MInvoiceLine line)
{
_iLine = line;
if (line == null)
{
SetC_InvoiceLine_ID(0);
}
else
{
SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID());
}
}
/**
* Invoice Line Constructor
* @param iLine invoice line
* @param dateTrx optional date
* @param qty matched quantity
*/
public MMatchInv(MInvoiceLine iLine, DateTime?dateTrx, Decimal qty)
: this(iLine.GetCtx(), 0, iLine.Get_TrxName())
{
SetClientOrg(iLine);
SetC_InvoiceLine_ID(iLine.GetC_InvoiceLine_ID());
SetM_InOutLine_ID(iLine.GetM_InOutLine_ID());
if (dateTrx != null)
{
SetDateTrx(dateTrx);
}
SetM_Product_ID(iLine.GetM_Product_ID());
SetM_AttributeSetInstance_ID(iLine.GetM_AttributeSetInstance_ID());
SetQty(qty);
SetProcessed(true); // auto
}
} // afterSave
/// <summary>
/// This function is used to set Values on Recognition Plan
/// </summary>
/// <param name="invoiceLine">invoice line object </param>
/// <param name="invoice">invoice object</param>
/// <param name="C_RevenueRecognition_ID">Recognition ID</param>
/// <param name="ToCurrency">Currency</param>
public void SetRecognitionPlan(MInvoiceLine invoiceLine, MInvoice invoice, int C_RevenueRecognition_ID, int ToCurrency)
{
SetAD_Client_ID(invoice.GetAD_Client_ID());
SetAD_Org_ID(invoice.GetAD_Org_ID());
SetC_Currency_ID(ToCurrency);
SetC_InvoiceLine_ID(invoiceLine.GetC_InvoiceLine_ID());
SetC_RevenueRecognition_ID(C_RevenueRecognition_ID);
// when tax include into price list, then reduce tax from Line Net Amount
bool isTaxIncide = (new MPriceList(invoice.GetCtx(), invoice.GetM_PriceList_ID(), invoice.Get_Trx())).IsTaxIncluded();
Decimal Amount = invoiceLine.GetLineNetAmt() - (isTaxIncide ? (invoiceLine.GetTaxAmt() + invoiceLine.GetSurchargeAmt()) : 0);
if (invoice.GetC_Currency_ID() != ToCurrency)
{
Amount = MConversionRate.Convert(GetCtx(), Amount, invoice.GetC_Currency_ID(), ToCurrency, invoice.GetDateInvoiced(), invoice.GetC_ConversionType_ID(), invoice.GetAD_Client_ID(), invoice.GetAD_Org_ID());
}
SetTotalAmt(Amount);
SetRecognizedAmt(Env.ZERO);
}
/// <summary>
/// After Save
/// </summary>
/// <param name="newRecord"></param>
/// <param name="success"></param>
/// <returns>Success</returns>
///
public void SetRecognitionPlan(MInvoiceLine invoiceLine, MInvoice invoice, int C_RevenueRecognition_ID)
{
try
{
SetAD_Client_ID(invoice.GetAD_Client_ID());
SetAD_Org_ID(invoice.GetAD_Org_ID());
SetC_Currency_ID(invoice.GetC_Currency_ID());
SetC_InvoiceLine_ID(invoiceLine.GetC_InvoiceLine_ID());
SetC_RevenueRecognition_ID(C_RevenueRecognition_ID);
SetP_Revenue_Acct(0);
SetUnEarnedRevenue_Acct(0);
SetTotalAmt(invoiceLine.GetLineNetAmt());
SetRecognizedAmt(Env.ZERO);
}
catch (Exception ex)
{
// MessageBox.Show("MInvoiceLine--SetInvoice");
}
}
// Create Asset From Invoice Mohit
/**
* Shipment Constructor
* @param Invoice
* @param shipLine shipment line
* @param deliveryCount 0 or number of delivery
*/
public MAsset(MInvoice invoice, MInvoiceLine invoiceline, int deliveryCount)
: this(invoiceline.GetCtx(), 0, invoiceline.Get_TrxName())
{
SetClientOrg(invoiceline);
SetValueNameDescription(invoice, invoiceline, deliveryCount);
// Header
//SetIsOwned(true);
SetC_BPartner_ID(invoice.GetC_BPartner_ID());
SetC_BPartner_Location_ID(invoice.GetC_BPartner_Location_ID());
SetAD_User_ID(invoice.GetAD_User_ID());
//SetM_Locator_ID(invoice.GetM_Locator_ID());
SetIsInPosession(true);
SetAssetServiceDate(invoice.GetDateAcct());
// Line
MProduct product = invoiceline.GetProduct();
SetM_Product_ID(product.GetM_Product_ID());
SetA_Asset_Group_ID(product.GetA_Asset_Group_ID());
//////////////////////////////*
//Changes for vafam
// SetAssetServiceDate(shipment.GetMovementDate());
//SetGuaranteeDate(TimeUtil.AddDays(shipment.GetMovementDate(), product.GetGuaranteeDays()));
MAssetGroup _assetGroup = new MAssetGroup(GetCtx(), GetA_Asset_Group_ID(), invoice.Get_TrxName());
if (_assetGroup.IsOwned())
{
SetIsOwned(true);
//SetC_BPartner_ID(0);
}
if (_assetGroup.IsDepreciated())
{
SetIsDepreciated(true);
SetIsFullyDepreciated(false);
}
////////////////////////////////////
//Change by Sukhwinder for setting Asset type and amortization template on Asset window, MANTIS ID:1762
int countVA038 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA038_' "));
int countVAFAM = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VAFAM_' "));
if (countVA038 > 0)
{
Set_Value("VA038_AmortizationTemplate_ID", Utility.Util.GetValueOfInt(_assetGroup.Get_Value("VA038_AmortizationTemplate_ID")));
}
if (countVAFAM > 0)
{
Set_Value("VAFAM_AssetType", _assetGroup.Get_Value("VAFAM_AssetType").ToString());
Set_Value("VAFAM_DepreciationType_ID", Utility.Util.GetValueOfInt(_assetGroup.Get_Value("VAFAM_DepreciationType_ID")));
}
////////////////////////////////////
// Guarantee & Version
SetGuaranteeDate(TimeUtil.AddDays(invoice.GetDateInvoiced(), product.GetGuaranteeDays()));
SetVersionNo(product.GetVersionNo());
if (invoiceline.GetM_AttributeSetInstance_ID() != 0) // Instance
{
MAttributeSetInstance asi = new MAttributeSetInstance(GetCtx(), invoiceline.GetM_AttributeSetInstance_ID(), Get_TrxName());
SetM_AttributeSetInstance_ID(asi.GetM_AttributeSetInstance_ID());
SetLot(asi.GetLot());
SetSerNo(asi.GetSerNo());
}
SetHelp(invoiceline.GetDescription());
// Qty
int units = product.GetSupportUnits();
if (units == 0)
{
units = 1;
}
if (deliveryCount != 0) // one asset per UOM
{
SetQty(invoiceline.GetQtyEntered(), units);
}
else
{
SetQty((Decimal)units);
}
SetM_InOutLine_ID(invoiceline.GetM_InOutLine_ID());
Set_Value("C_InvoiceLine_ID", invoiceline.GetC_InvoiceLine_ID());
// Activate
MAssetGroup ag = MAssetGroup.Get(GetCtx(), GetA_Asset_Group_ID());
if (!ag.IsCreateAsActive())
{
SetIsActive(false);
}
}
/// <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);
}
/**
* Find/Create PO(Inv) Match
* @param iLine invoice line
* @param sLine receipt line
* @param dateTrx date
* @param qty qty
* @return Match Record
*/
public static MMatchPO Create(MInvoiceLine iLine, MInOutLine sLine, DateTime?dateTrx, Decimal qty)
{
Trx trxName = null;
Ctx ctx = null;
int C_OrderLine_ID = 0;
if (iLine != null)
{
trxName = iLine.Get_Trx();
ctx = iLine.GetCtx();
C_OrderLine_ID = iLine.GetC_OrderLine_ID();
}
if (sLine != null)
{
trxName = sLine.Get_Trx();
ctx = sLine.GetCtx();
C_OrderLine_ID = sLine.GetC_OrderLine_ID();
}
MMatchPO retValue = null;
String sql = "SELECT * FROM M_MatchPO WHERE C_OrderLine_ID=" + C_OrderLine_ID;
// ArrayList list = new ArrayList();
DataSet ds = null;
try
{
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
foreach (DataRow dr in ds.Tables[0].Rows)
{
MMatchPO mpo = new MMatchPO(ctx, dr, trxName);
if (qty.CompareTo(mpo.GetQty()) == 0)
{
if (iLine != null)
{
if (mpo.GetC_InvoiceLine_ID() == 0 ||
mpo.GetC_InvoiceLine_ID() == iLine.GetC_InvoiceLine_ID())
{
mpo.SetC_InvoiceLine_ID(iLine);
if (iLine.GetM_AttributeSetInstance_ID() != 0)
{
if (mpo.GetM_AttributeSetInstance_ID() == 0)
{
mpo.SetM_AttributeSetInstance_ID(iLine.GetM_AttributeSetInstance_ID());
}
else if (mpo.GetM_AttributeSetInstance_ID() != iLine.GetM_AttributeSetInstance_ID())
{
continue;
}
}
}
else
{
continue;
}
}
if (sLine != null)
{
if (mpo.GetM_InOutLine_ID() == 0 ||
mpo.GetM_InOutLine_ID() == sLine.GetM_InOutLine_ID())
{
mpo.SetM_InOutLine_ID(sLine.GetM_InOutLine_ID());
if (sLine.GetM_AttributeSetInstance_ID() != 0)
{
if (mpo.GetM_AttributeSetInstance_ID() == 0)
{
mpo.SetM_AttributeSetInstance_ID(sLine.GetM_AttributeSetInstance_ID());
}
else if (mpo.GetM_AttributeSetInstance_ID() != sLine.GetM_AttributeSetInstance_ID())
{
continue;
}
}
}
else
{
continue;
}
}
retValue = mpo;
break;
}
}
}
catch (Exception e)
{
s_log.Log(Level.SEVERE, sql, e);
}
// Create New
if (retValue == null)
{
if (sLine != null)
{
retValue = new MMatchPO(sLine, dateTrx, qty);
if (iLine != null)
{
retValue.SetC_InvoiceLine_ID(iLine);
}
}
else if (iLine != null)
{
retValue = new MMatchPO(iLine, dateTrx, qty);
}
}
return(retValue);
}
/// <summary>
/// Get Costs of Invoice Line
/// </summary>
/// <param name="il">invoice line</param>
/// <returns>array of landed cost lines</returns>
public static MLandedCost[] GetLandedCosts(MInvoiceLine il)
{
List <MLandedCost> list = new List <MLandedCost>();
String sql = "SELECT * FROM C_LandedCost WHERE C_InvoiceLine_ID=" + il.GetC_InvoiceLine_ID();
try
{
DataSet ds = DataBase.DB.ExecuteDataset(sql, null, il.Get_TrxName());
if (ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
list.Add(new MLandedCost(il.GetCtx(), dr, il.Get_TrxName()));
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
//
MLandedCost[] retValue = new MLandedCost[list.Count];
retValue = list.ToArray();
return(retValue);
}
public static string CreateRevenueRecognitionPlan(int C_InvoiceLine_ID, int C_RevenueRecognition_ID, MInvoice Invoice)
{
try
{
MRevenueRecognitionRun revenueRecognitionRun = null;
DateTime? RecognizationDate = null;
MRevenueRecognition revenueRecognition = new MRevenueRecognition(Invoice.GetCtx(), C_RevenueRecognition_ID, Invoice.Get_Trx());
int defaultAccSchemaOrg_ID = GetDefaultActSchema(Invoice.GetAD_Org_ID());
if (defaultAccSchemaOrg_ID <= 0)
{
_log.Log(Level.SEVERE, "Default Schema not found for the oraganization");
return("Default Schema not found for the oraganization");
}
MINT15AccountingSchemaOrg accountingSchemaOrg = new MINT15AccountingSchemaOrg(Invoice.GetCtx(), defaultAccSchemaOrg_ID, Invoice.Get_Trx());
MRevenueRecognitionPlan revenueRecognitionPlan = new MRevenueRecognitionPlan(Invoice.GetCtx(), 0, Invoice.Get_Trx());
MInvoiceLine invoiceLine = new MInvoiceLine(Invoice.GetCtx(), C_InvoiceLine_ID, Invoice.Get_Trx());
MInvoice invoice = new MInvoice(Invoice.GetCtx(), invoiceLine.GetC_Invoice_ID(), Invoice.Get_Trx());
string sql = "Select INT15_StartDate From C_InvoiceLine Where C_InvoiceLine_ID=" + invoiceLine.GetC_InvoiceLine_ID();
RecognizationDate = Util.GetValueOfDateTime(DB.ExecuteScalar(sql));
if (RecognizationDate == null)
{
RecognizationDate = invoice.GetDateInvoiced();
}
revenueRecognitionPlan.SetRecognitionPlan(invoiceLine, invoice, C_RevenueRecognition_ID);
revenueRecognitionPlan.SetC_AcctSchema_ID(accountingSchemaOrg.GetC_AcctSchema_ID());
revenueRecognitionPlan.SetRecognizedAmt(0);
if (revenueRecognition.GetINT15_RecognizeType() == "R")
{
revenueRecognitionPlan.SetUnEarnedRevenue_Acct(RecognitionCombination(accountingSchemaOrg, "UR", invoiceLine));
revenueRecognitionPlan.SetP_Revenue_Acct(RecognitionCombination(accountingSchemaOrg, "TR", invoiceLine));
}
else
{
revenueRecognitionPlan.SetINT15_PrepaidExpense(RecognitionCombination(accountingSchemaOrg, "DE", invoiceLine));
revenueRecognitionPlan.SetINT15_ProductExpense(RecognitionCombination(accountingSchemaOrg, "PE", invoiceLine));
}
revenueRecognitionPlan.Save();
if (!revenueRecognition.IsTimeBased())
{
MINT15RevenueService[] revenueService = GetServices(revenueRecognition);
for (int i = 0; i < revenueService.Length; i++)
{
MINT15RevenueService revenueserviceline = revenueService[i];
revenueRecognitionRun = new MRevenueRecognitionRun(Invoice.GetCtx(), 0, Invoice.Get_Trx());
revenueRecognitionRun.SetRecognitionRun(revenueRecognition, revenueserviceline, revenueRecognitionPlan);
Decimal recognizedAmt = (revenueRecognitionPlan.GetTotalAmt() * revenueserviceline.GetINT15_Percentage()) / 100;
revenueRecognitionRun.SetRecognizedAmt(recognizedAmt);
revenueRecognitionRun.SetINT15_RevenueService_ID(revenueserviceline.GetINT15_RevenueService_ID());
revenueRecognitionRun.Save();
}
}
else
{
if (revenueRecognition.GetRecognitionFrequency() == "M")
{
//Decimal recognizedAmt = revenueRecognitionPlan.GetTotalAmt() / revenueRecognition.GetNoMonths();
double totaldays = (RecognizationDate.Value.AddMonths(revenueRecognition.GetNoMonths()) - RecognizationDate.Value.Date).TotalDays;
decimal perdayAmt = revenueRecognitionPlan.GetTotalAmt() / Convert.ToDecimal(totaldays);
decimal recognizedAmt = 0;
DateTime?lastdate = null;
int days = 0;
for (int i = 0; i < revenueRecognition.GetNoMonths() + 1; i++)
{
if (i == 0)
{
if (RecognizationDate.Value.Month == 12)
{
lastdate = new DateTime(RecognizationDate.Value.Year, RecognizationDate.Value.Month, 1).AddMonths(1).AddDays(-1);
}
else
{
lastdate = new DateTime(RecognizationDate.Value.Year, RecognizationDate.Value.Month + 1, 1).AddDays(-1);
}
days = Util.GetValueOfInt((lastdate.Value.Date - RecognizationDate.Value.Date).TotalDays);
days += 1;
}
else if (i == (revenueRecognition.GetNoMonths()))
{
DateTime EndDate = RecognizationDate.Value.AddMonths(i);
var startDate = new DateTime(EndDate.Year, EndDate.Month, 1);
days = Util.GetValueOfInt((EndDate.Date - startDate.Date).TotalDays);
}
else
{
DateTime startdate = RecognizationDate.Value.AddMonths(i);
days = DateTime.DaysInMonth(startdate.Year, startdate.Month);
}
recognizedAmt = Convert.ToDecimal(days) * perdayAmt;
revenueRecognitionRun = new MRevenueRecognitionRun(Invoice.GetCtx(), 0, Invoice.Get_Trx());
revenueRecognitionRun.SetRecognitionRun(revenueRecognition, null, revenueRecognitionPlan);
revenueRecognitionRun.SetRecognizedAmt(recognizedAmt);
revenueRecognitionRun.SetINT15_RecognitionDate(RecognizationDate.Value.AddMonths(i));
revenueRecognitionRun.Save();
recognizedAmt = 0;
}
}
else if (revenueRecognition.GetRecognitionFrequency() == "D")
{
Decimal recognizedAmt = revenueRecognitionPlan.GetTotalAmt() / revenueRecognition.GetNoMonths();
int days = 0;
for (int i = 0; i < revenueRecognition.GetNoMonths(); i++)
{
revenueRecognitionRun = new MRevenueRecognitionRun(Invoice.GetCtx(), 0, Invoice.Get_Trx());
revenueRecognitionRun.SetRecognitionRun(revenueRecognition, null, revenueRecognitionPlan);
revenueRecognitionRun.SetRecognizedAmt(recognizedAmt);
revenueRecognitionRun.SetINT15_RecognitionDate(RecognizationDate.Value.AddDays(days));
days += 1;
revenueRecognitionRun.Save();
}
}
}
}
catch (Exception ex)
{
return(ex.ToString());
}
return("Revenue Recognition Created");
}
} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (clear text)</returns>
protected override String DoIt()
{
int index = 1;
StringBuilder sql = new StringBuilder("SELECT * "
+ "FROM S_TimeExpense e "
+ "WHERE e.Processed='Y'"
+ " AND e.AD_Client_ID=@param1"); // #1
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
index++;
sql.Append(" AND e.C_BPartner_ID=@param2"); // #2
}
if (_DateFrom != null)
{
index++;
sql.Append(" AND e.DateReport >=@param3"); // #3
}
if (_DateTo != null)
{
index++;
sql.Append(" AND e.DateReport <=@param4"); // #4
}
// JID_0868
// chanegs done by Bharat on 12 September 2018 to handle the case if invoice is created with an expense for the selected Business Partner
sql.Append(" AND EXISTS (SELECT * FROM S_TimeExpenseLine el "
+ "WHERE e.S_TimeExpense_ID=el.S_TimeExpense_ID"
+ " AND el.C_InvoiceLine_ID IS NULL"
+ " AND el.ConvertedAmt<>0) "
+ "ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID");
//
int old_BPartner_ID = -1;
MInvoice invoice = null;
MTimeExpense te = null;
//
//PreparedStatement pstmt = null;
SqlParameter[] param = new SqlParameter[index];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement (sql.toString (), get_TrxName());
int par = 0;
//pstmt.setInt(par++, getAD_Client_ID());
param[0] = new SqlParameter("@param1", GetAD_Client_ID());
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
par++;
//pstmt.setInt (par++, _C_BPartner_ID);
param[par] = new SqlParameter("@param2", _C_BPartner_ID);
}
if (_DateFrom != null)
{
par++;
//pstmt.setTimestamp (par++, _DateFrom);
param[par] = new SqlParameter("@param3", _DateFrom);
}
if (_DateTo != null)
{
par++;
//pstmt.setTimestamp (par++, _DateTo);
param[par] = new SqlParameter("@param4", _DateTo);
}
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
// ********* Expense Line Loop
{
te = new MTimeExpense(GetCtx(), dr, Get_TrxName());
// New BPartner - New Order
//
if (te.GetC_BPartner_ID() != old_BPartner_ID)
{
CompleteInvoice(invoice, te);
MBPartner bp = new MBPartner(GetCtx(), te.GetC_BPartner_ID(), Get_TrxName());
log.Info("New Invoice for " + bp);
invoice = new MInvoice(GetCtx(), 0, Get_TrxName());
invoice.SetBPartner(bp);
if (invoice.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
invoice = null;
break;
}
// Siddheshwar: added a code to check for payment method if null
if (bp.GetVA009_PO_PaymentMethod_ID() <= 0)
{
paymethod = GetPaymentMethod(te);
if (paymethod <= 0)
{
if (!noPayMethEmp.Contains(bp.GetC_BPartner_ID()))
{
noPayMethEmp.Add(bp.GetC_BPartner_ID());
if (string.IsNullOrEmpty(bpNameNoPM))
{
bpNameNoPM = bp.GetName();
}
else
{
bpNameNoPM += bp.GetName() + ", ";
}
return(Msg.GetMsg(GetCtx(), "NoPayMethEmp") + bpNameNoPM);
}
}
else
{
invoice.SetVA009_PaymentMethod_ID(paymethod);
}
}
else
{
//JID_1783_1 if active paymentMethod not found ,then dont create the invoice.
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM VA009_PaymentMethod WHERE VA009_PaymentMethod_ID=" + bp.GetVA009_PO_PaymentMethod_ID(), null, Get_Trx())).Equals("Y"))
{
invoice.SetVA009_PaymentMethod_ID(bp.GetVA009_PO_PaymentMethod_ID());
}
else
{
return(Msg.GetMsg(GetCtx(), "IsActivePaymentMethodInv"));;
}
}
// Checking payment term
if (bp.GetPO_PaymentTerm_ID() <= 0)
{
payterm = GetPaymentTerm(te);
if (payterm <= 0)
{
if (!PayTermEmp.Contains(bp.GetC_BPartner_ID()))
{
PayTermEmp.Add(bp.GetC_BPartner_ID());
if (string.IsNullOrEmpty(bpNamePT))
{
bpNamePT = bp.GetName();
}
else
{
bpNamePT += bp.GetName() + ", ";
}
return(Msg.GetMsg(GetCtx(), "NoPayTerm") + bpNamePT);
}
}
else
{
invoice.SetC_PaymentTerm_ID(payterm);
}
}
else
{
//JID_1783_1 if active paymentTerm not found ,then dont create the invoice.
if (Util.GetValueOfString(DB.ExecuteScalar("SELECT IsActive FROM C_PaymentTerm WHERE C_PaymentTerm_ID=" + bp.GetPO_PaymentTerm_ID(), null, Get_Trx())).Equals("Y"))
{
invoice.SetC_PaymentTerm_ID(bp.GetPO_PaymentTerm_ID());
}
else
{
return(Msg.GetMsg(GetCtx(), "IsActivePaymentTermInv"));
}
}
invoice.SetIsExpenseInvoice(true); //added by arpit asked by Surya Sir on DEC 28,2015
invoice.SetClientOrg(te.GetAD_Client_ID(), te.GetAD_Org_ID());
//invoice.SetVA009_PaymentMethod_ID(bp.GetVA009_PO_PaymentMethod_ID());
// JID_0868
// chanegs done by Bharat on 12 September 2018 to set the document type where Expense Invoice checkbox is true.
// String qry = "SELECT C_DocType_ID FROM C_DocType "
//+ "WHERE AD_Client_ID=@param1 AND DocBaseType=@param2"
//+ " AND IsActive='Y' AND IsExpenseInvoice = 'Y' "
//+ "ORDER BY C_DocType_ID DESC , IsDefault DESC";
String qry = "SELECT C_DocType_ID FROM C_DocType "
+ "WHERE AD_Client_ID=" + GetAD_Client_ID() + @" AND DocBaseType='" + MDocBaseType.DOCBASETYPE_APINVOICE + @"'"
+ " AND IsActive='Y' AND IsExpenseInvoice = 'Y' AND AD_Org_ID IN(0," + te.GetAD_Org_ID() + ") "
+ " ORDER BY AD_Org_ID Desc, C_DocType_ID DESC , IsDefault DESC";
//int C_DocType_ID = DB.GetSQLValue(null, qry, GetAD_Client_ID(), MDocBaseType.DOCBASETYPE_APINVOICE);
int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar(qry));
if (C_DocType_ID <= 0)
{
log.Log(Level.SEVERE, "Not found for AC_Client_ID="
+ GetAD_Client_ID() + " - " + MDocBaseType.DOCBASETYPE_APINVOICE);
return(Msg.GetMsg(GetCtx(), "NoDocTypeExpInvoice"));
}
else
{
log.Fine(MDocBaseType.DOCBASETYPE_APINVOICE);
}
invoice.SetC_DocTypeTarget_ID(C_DocType_ID);
//invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API
//commented by Arpit on Jan 4,2015 Mentis issue no. 0000310
//invoice.SetDocumentNo(te.GetDocumentNo());
//
invoice.SetM_PriceList_ID(te.GetM_PriceList_ID());
invoice.SetSalesRep_ID(te.GetDoc_User_ID());
String descr = Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
if (!invoice.Save())
{
return(GetRetrievedError(invoice, "Cannot save Invoice"));
// new Exception("Cannot save Invoice");
}
//added by arpit asked by Surya Sir on 29/12/2015*******
else
{
old_BPartner_ID = bp.GetC_BPartner_ID();
}
//end***************
}
// JID_0868
//Description include all document numbers which is come from Time And Expense Recording window to expense invoice in case of multiple records
else if (old_BPartner_ID > 0)
{
String descr = invoice.GetDescription() + "\n" + Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
}
MTimeExpenseLine[] tel = te.GetLines(false);
for (int i = 0; i < tel.Length; i++)
{
MTimeExpenseLine line = tel[i];
// Already Invoiced or nothing to be reimbursed
if (line.GetC_InvoiceLine_ID() != 0 ||
Env.ZERO.CompareTo(line.GetQtyReimbursed()) == 0 ||
Env.ZERO.CompareTo(line.GetPriceReimbursed()) == 0)
{
continue;
}
// Update Header info
if (line.GetC_Activity_ID() != 0 && line.GetC_Activity_ID() != invoice.GetC_Activity_ID())
{
invoice.SetC_Activity_ID(line.GetC_Activity_ID());
}
if (line.GetC_Campaign_ID() != 0 && line.GetC_Campaign_ID() != invoice.GetC_Campaign_ID())
{
invoice.SetC_Campaign_ID(line.GetC_Campaign_ID());
}
if (line.GetC_Project_ID() != 0 && line.GetC_Project_ID() != invoice.GetC_Project_ID())
{
invoice.SetC_Project_ID(line.GetC_Project_ID());
}
if (!invoice.Save())
{
return(GetRetrievedError(invoice, "Cannot save Invoice"));
//new Exception("Cannot save Invoice");
}
// Create OrderLine
MInvoiceLine il = new MInvoiceLine(invoice);
//
if (line.GetM_Product_ID() != 0)
{
il.SetM_Product_ID(line.GetM_Product_ID(), true);
}
//added by arpit asked by Surya Sir on 28/12/2015_____***************************
if (line.GetC_Charge_ID() != 0)
{
il.SetC_Charge_ID(line.GetC_Charge_ID());
}
//end here *****************************
il.SetQty(line.GetQtyReimbursed()); // Entered/Invoiced
il.SetDescription(line.GetDescription());
//
il.SetC_Project_ID(line.GetC_Project_ID());
il.SetC_ProjectPhase_ID(line.GetC_ProjectPhase_ID());
il.SetC_ProjectTask_ID(line.GetC_ProjectTask_ID());
il.SetC_Activity_ID(line.GetC_Activity_ID());
il.SetC_Campaign_ID(line.GetC_Campaign_ID());
//
// il.setPrice(); // not really a list/limit price for reimbursements
il.SetPrice(line.GetPriceReimbursed()); //
il.SetC_UOM_ID(line.GetC_UOM_ID());
// JID_0868
// chanegs done by Bharat on 12 September 2018 to set the Amount in List price column.
il.SetPriceList(line.GetPriceReimbursed());
il.SetTax();
if (!il.Save())
{
return(GetRetrievedError(il, "Cannot save Invoice"));
//new Exception("Cannot save Invoice Line");
}
// Update TEL
line.SetC_InvoiceLine_ID(il.GetC_InvoiceLine_ID());
line.SetIsInvoiced(true);
line.Save();
} // for all expense lines
}
}
else
{
message = Msg.GetMsg(GetCtx(), "NoRecForInv");
}
// ********* Expense Line Loop
dt = null;
//dt.Clear();
}
catch (Exception e)
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
finally
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
}
CompleteInvoice(invoice, te);
//if (_noInvoices == 0)
//{
// message = " @No Record Found for Invoice Creation@";
//}
//Code for Showing Message when PaymentTerm is null
if (!string.IsNullOrEmpty(bpNamePT))
{
message += Msg.GetMsg(GetCtx(), "NoPayTerm") + bpNamePT + "\n";
}
//code for showing Error Message when Invoice is not completed
if (!string.IsNullOrEmpty(bpNameInvoice))
{
message += Msg.GetMsg(GetCtx(), "NoInvoiceCreated") + bpNameInvoice + "\n";
}
if (!string.IsNullOrEmpty(bpNameNoPM))
{
message = Msg.GetMsg(GetCtx(), "NoPayMethEmp") + bpNameNoPM + "\n";
}
if (_noInvoices > 0)
{
message += "" + _noInvoices + " " + Msg.GetMsg(GetCtx(), "VIS_InvGenerated");
}
//return "" + _noInvoices + " @Invoices Generated Successfully@";
return(message);
} // doIt
} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (clear text)</returns>
protected override String DoIt()
{
int index = 1;
StringBuilder sql = new StringBuilder("SELECT * "
+ "FROM S_TimeExpense e "
+ "WHERE e.Processed='Y'"
+ " AND e.AD_Client_ID=@param1"); // #1
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
index++;
sql.Append(" AND e.C_BPartner_ID=@param2"); // #2
}
if (_DateFrom != null)
{
index++;
sql.Append(" AND e.DateReport >=@param3"); // #3
}
if (_DateTo != null)
{
index++;
sql.Append(" AND e.DateReport <=@param4"); // #4
}
// JID_0868
// chanegs done by Bharat on 12 September 2018 to handle the case if invoice is created with an expense for the selected Business Partner
sql.Append(" AND EXISTS (SELECT * FROM S_TimeExpenseLine el "
+ "WHERE e.S_TimeExpense_ID=el.S_TimeExpense_ID"
+ " AND el.C_InvoiceLine_ID IS NULL"
+ " AND el.ConvertedAmt<>0) "
+ "ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID");
//
int old_BPartner_ID = -1;
MInvoice invoice = null;
//
//PreparedStatement pstmt = null;
SqlParameter[] param = new SqlParameter[index];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement (sql.toString (), get_TrxName());
int par = 0;
//pstmt.setInt(par++, getAD_Client_ID());
param[0] = new SqlParameter("@param1", GetAD_Client_ID());
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
par++;
//pstmt.setInt (par++, _C_BPartner_ID);
param[par] = new SqlParameter("@param2", _C_BPartner_ID);
}
if (_DateFrom != null)
{
par++;
//pstmt.setTimestamp (par++, _DateFrom);
param[par] = new SqlParameter("@param3", _DateFrom);
}
if (_DateTo != null)
{
par++;
//pstmt.setTimestamp (par++, _DateTo);
param[par] = new SqlParameter("@param4", _DateTo);
}
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
// ********* Expense Line Loop
{
MTimeExpense te = new MTimeExpense(GetCtx(), dr, Get_TrxName());
// New BPartner - New Order
if (te.GetC_BPartner_ID() != old_BPartner_ID)
{
CompleteInvoice(invoice);
MBPartner bp = new MBPartner(GetCtx(), te.GetC_BPartner_ID(), Get_TrxName());
//
log.Info("New Invoice for " + bp);
invoice = new MInvoice(GetCtx(), 0, null);
invoice.SetIsExpenseInvoice(true); //added by arpit asked by Surya Sir on DEC 28,2015
invoice.SetClientOrg(te.GetAD_Client_ID(), te.GetAD_Org_ID());
// JID_0868
// chanegs done by Bharat on 12 September 2018 to set the document type where Expense Invoice checkbox is true.
String qry = "SELECT C_DocType_ID FROM C_DocType "
+ "WHERE AD_Client_ID=@param1 AND DocBaseType=@param2"
+ " AND IsActive='Y' AND IsExpenseInvoice = 'Y' "
+ "ORDER BY C_DocType_ID DESC , IsDefault DESC";
int C_DocType_ID = DB.GetSQLValue(null, qry, GetAD_Client_ID(), MDocBaseType.DOCBASETYPE_APINVOICE);
if (C_DocType_ID <= 0)
{
log.Log(Level.SEVERE, "Not found for AC_Client_ID="
+ GetAD_Client_ID() + " - " + MDocBaseType.DOCBASETYPE_APINVOICE);
}
else
{
log.Fine(MDocBaseType.DOCBASETYPE_APINVOICE);
}
invoice.SetC_DocTypeTarget_ID(C_DocType_ID);
//invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API
//commented by Arpit on Jan 4,2015 Mentis issue no. 0000310
//invoice.SetDocumentNo(te.GetDocumentNo());
//
invoice.SetBPartner(bp);
if (invoice.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
invoice = null;
break;
}
invoice.SetM_PriceList_ID(te.GetM_PriceList_ID());
invoice.SetSalesRep_ID(te.GetDoc_User_ID());
String descr = Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
if (!invoice.Save())
{
//return GetReterivedError(invoice, "Cannot save Invoice");
new Exception("Cannot save Invoice");
}
//added by arpit asked by Surya Sir on 29/12/2015*******
else
{
old_BPartner_ID = bp.GetC_BPartner_ID();
}
//end***************
}
// JID_0868
//Description include all document numbers which is come from Time And Expense Recording window to expense invoice in case of multiple records
else if (old_BPartner_ID > 0)
{
String descr = invoice.GetDescription() + "\n" + Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
}
MTimeExpenseLine[] tel = te.GetLines(false);
for (int i = 0; i < tel.Length; i++)
{
MTimeExpenseLine line = tel[i];
// Already Invoiced or nothing to be reimbursed
if (line.GetC_InvoiceLine_ID() != 0 ||
Env.ZERO.CompareTo(line.GetQtyReimbursed()) == 0 ||
Env.ZERO.CompareTo(line.GetPriceReimbursed()) == 0)
{
continue;
}
// Update Header info
if (line.GetC_Activity_ID() != 0 && line.GetC_Activity_ID() != invoice.GetC_Activity_ID())
{
invoice.SetC_Activity_ID(line.GetC_Activity_ID());
}
if (line.GetC_Campaign_ID() != 0 && line.GetC_Campaign_ID() != invoice.GetC_Campaign_ID())
{
invoice.SetC_Campaign_ID(line.GetC_Campaign_ID());
}
if (line.GetC_Project_ID() != 0 && line.GetC_Project_ID() != invoice.GetC_Project_ID())
{
invoice.SetC_Project_ID(line.GetC_Project_ID());
}
if (!invoice.Save())
{
//return GetReterivedError(invoice, "Cannot save Invoice");
new Exception("Cannot save Invoice");
}
// Create OrderLine
MInvoiceLine il = new MInvoiceLine(invoice);
//
if (line.GetM_Product_ID() != 0)
{
il.SetM_Product_ID(line.GetM_Product_ID(), true);
}
//added by arpit asked by Surya Sir on 28/12/2015_____***************************
if (line.GetC_Charge_ID() != 0)
{
il.SetC_Charge_ID(line.GetC_Charge_ID());
}
//end here *****************************
il.SetQty(line.GetQtyReimbursed()); // Entered/Invoiced
il.SetDescription(line.GetDescription());
//
il.SetC_Project_ID(line.GetC_Project_ID());
il.SetC_ProjectPhase_ID(line.GetC_ProjectPhase_ID());
il.SetC_ProjectTask_ID(line.GetC_ProjectTask_ID());
il.SetC_Activity_ID(line.GetC_Activity_ID());
il.SetC_Campaign_ID(line.GetC_Campaign_ID());
//
// il.setPrice(); // not really a list/limit price for reimbursements
il.SetPrice(line.GetPriceReimbursed()); //
// JID_0868
// chanegs done by Bharat on 12 September 2018 to set the Amount in List price column.
il.SetPriceList(line.GetPriceReimbursed());
il.SetTax();
if (!il.Save())
{
//return GetReterivedError(il, "Cannot save Invoice");
new Exception("Cannot save Invoice Line");
}
// Update TEL
line.SetC_InvoiceLine_ID(il.GetC_InvoiceLine_ID());
line.SetIsInvoiced(true);
line.Save();
} // for all expense lines
} // ********* Expense Line Loop
dt = null;
//dt.Clear();
}
catch (Exception e)
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
finally
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
}
CompleteInvoice(invoice);
if (_noInvoices == 0)
{
return(" @No Record Found for Invoice Creation@");
}
return("" + _noInvoices + " @Invoices Generated Successfully@");
} // doIt
} // prepare
/// <summary>
/// Perform Process.
/// </summary>
/// <returns>Message (clear text)</returns>
protected override String DoIt()
{
int index = 1;
StringBuilder sql = new StringBuilder("SELECT * "
+ "FROM S_TimeExpense e "
+ "WHERE e.Processed='Y'"
+ " AND e.AD_Client_ID=@param1"); // #1
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
index++;
sql.Append(" AND e.C_BPartner_ID=@param2"); // #2
}
if (_DateFrom != null)
{
index++;
sql.Append(" AND e.DateReport >=@param3"); // #3
}
if (_DateTo != null)
{
index++;
sql.Append(" AND e.DateReport <=@param4"); // #4
sql.Append(" AND EXISTS (SELECT * FROM S_TimeExpenseLine el "
+ "WHERE e.S_TimeExpense_ID=el.S_TimeExpense_ID"
+ " AND el.C_InvoiceLine_ID IS NULL"
+ " AND el.ConvertedAmt<>0) "
+ "ORDER BY e.C_BPartner_ID, e.S_TimeExpense_ID");
}
//
int old_BPartner_ID = -1;
MInvoice invoice = null;
//
//PreparedStatement pstmt = null;
SqlParameter[] param = new SqlParameter[index];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement (sql.toString (), get_TrxName());
int par = 0;
//pstmt.setInt(par++, getAD_Client_ID());
param[0] = new SqlParameter("@param1", GetAD_Client_ID());
if (_C_BPartner_ID != 0 && _C_BPartner_ID != -1)
{
par++;
//pstmt.setInt (par++, _C_BPartner_ID);
param[par] = new SqlParameter("@param2", _C_BPartner_ID);
}
if (_DateFrom != null)
{
par++;
//pstmt.setTimestamp (par++, _DateFrom);
param[par] = new SqlParameter("@param3", _DateFrom);
}
if (_DateTo != null)
{
par++;
//pstmt.setTimestamp (par++, _DateTo);
param[par] = new SqlParameter("@param4", _DateTo);
}
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql.ToString(), param, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
// ********* Expense Line Loop
{
MTimeExpense te = new MTimeExpense(GetCtx(), dr, Get_TrxName());
// New BPartner - New Order
if (te.GetC_BPartner_ID() != old_BPartner_ID)
{
CompleteInvoice(invoice);
MBPartner bp = new MBPartner(GetCtx(), te.GetC_BPartner_ID(), Get_TrxName());
//
log.Info("New Invoice for " + bp);
invoice = new MInvoice(GetCtx(), 0, null);
invoice.SetClientOrg(te.GetAD_Client_ID(), te.GetAD_Org_ID());
invoice.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APINVOICE); // API
invoice.SetDocumentNo(te.GetDocumentNo());
//
invoice.SetBPartner(bp);
if (invoice.GetC_BPartner_Location_ID() == 0)
{
log.Log(Level.SEVERE, "No BP Location: " + bp);
AddLog(0, te.GetDateReport(),
null, "No Location: " + te.GetDocumentNo() + " " + bp.GetName());
invoice = null;
break;
}
invoice.SetM_PriceList_ID(te.GetM_PriceList_ID());
invoice.SetSalesRep_ID(te.GetDoc_User_ID());
String descr = Msg.Translate(GetCtx(), "S_TimeExpense_ID")
+ ": " + te.GetDocumentNo() + " "
+ DisplayType.GetDateFormat(DisplayType.Date).Format(te.GetDateReport());
invoice.SetDescription(descr);
if (!invoice.Save())
{
new Exception("Cannot save Invoice");
}
old_BPartner_ID = bp.GetC_BPartner_ID();
}
MTimeExpenseLine[] tel = te.GetLines(false);
for (int i = 0; i < tel.Length; i++)
{
MTimeExpenseLine line = tel[i];
// Already Invoiced or nothing to be reimbursed
if (line.GetC_InvoiceLine_ID() != 0 ||
Env.ZERO.CompareTo(line.GetQtyReimbursed()) == 0 ||
Env.ZERO.CompareTo(line.GetPriceReimbursed()) == 0)
{
continue;
}
// Update Header info
if (line.GetC_Activity_ID() != 0 && line.GetC_Activity_ID() != invoice.GetC_Activity_ID())
{
invoice.SetC_Activity_ID(line.GetC_Activity_ID());
}
if (line.GetC_Campaign_ID() != 0 && line.GetC_Campaign_ID() != invoice.GetC_Campaign_ID())
{
invoice.SetC_Campaign_ID(line.GetC_Campaign_ID());
}
if (line.GetC_Project_ID() != 0 && line.GetC_Project_ID() != invoice.GetC_Project_ID())
{
invoice.SetC_Project_ID(line.GetC_Project_ID());
}
if (!invoice.Save())
{
new Exception("Cannot save Invoice");
}
// Create OrderLine
MInvoiceLine il = new MInvoiceLine(invoice);
//
if (line.GetM_Product_ID() != 0)
{
il.SetM_Product_ID(line.GetM_Product_ID(), true);
}
il.SetQty(line.GetQtyReimbursed()); // Entered/Invoiced
il.SetDescription(line.GetDescription());
//
il.SetC_Project_ID(line.GetC_Project_ID());
il.SetC_ProjectPhase_ID(line.GetC_ProjectPhase_ID());
il.SetC_ProjectTask_ID(line.GetC_ProjectTask_ID());
il.SetC_Activity_ID(line.GetC_Activity_ID());
il.SetC_Campaign_ID(line.GetC_Campaign_ID());
//
// il.setPrice(); // not really a list/limit price for reimbursements
il.SetPrice(line.GetPriceReimbursed()); //
il.SetTax();
if (!il.Save())
{
new Exception("Cannot save Invoice Line");
}
// Update TEL
line.SetC_InvoiceLine_ID(il.GetC_InvoiceLine_ID());
line.SetIsInvoiced(true);
line.Save();
} // for all expense lines
} // ********* Expense Line Loop
dt = null;
//dt.Clear();
}
catch (Exception e)
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), e);
}
finally
{
if (dt != null)
{
dt = null;
}
if (idr != null)
{
idr.Close();
}
}
CompleteInvoice(invoice);
if (_noInvoices == 0)
{
return(" @No Record Found for Invoice Creation@");
}
return("" + _noInvoices + " @Invoices Generated Successfully@");
} // doIt
/// <summary>
/// Process Invoice Batch
/// </summary>
/// <returns></returns>
protected override String DoIt()
{
log.Info("C_InvoiceBatch_ID=" + _C_InvoiceBatch_ID + ", DocAction=" + _DocAction);
if (_C_InvoiceBatch_ID == 0)
{
throw new Exception("C_InvoiceBatch_ID = 0");
}
MInvoiceBatch batch = new MInvoiceBatch(GetCtx(), _C_InvoiceBatch_ID, Get_TrxName());
if (batch.Get_ID() == 0)
{
throw new Exception("@NotFound@: @C_InvoiceBatch_ID@ - " + _C_InvoiceBatch_ID);
}
if (batch.IsProcessed())
{
throw new Exception("@Processed@");
}
//
if (Signum(batch.GetControlAmt()) != 0 && batch.GetControlAmt().CompareTo(batch.GetDocumentAmt()) != 0)
{
throw new Exception("@ControlAmt@ <> @DocumentAmt@");
}
//
MInvoiceBatchLine[] lines = batch.GetLines(false);
for (int i = 0; i < lines.Length; i++)
{
MInvoiceBatchLine line = lines[i];
if (line.GetC_Invoice_ID() != 0 || line.GetC_InvoiceLine_ID() != 0)
{
continue;
}
if ((_oldDocumentNo != null &&
!_oldDocumentNo.Equals(line.GetDocumentNo())) ||
_oldC_BPartner_ID != line.GetC_BPartner_ID() ||
_oldC_BPartner_Location_ID != line.GetC_BPartner_Location_ID())
{
CompleteInvoice();
}
// New Invoice
if (_invoice == null)
{
_invoice = new MInvoice(batch, line);
if (!_invoice.Save())
{
return(GetRetrievedError(_invoice, "Cannot save Invoice"));
}
//throw new Exception("Cannot save Invoice");
//
_oldDocumentNo = line.GetDocumentNo();
_oldC_BPartner_ID = line.GetC_BPartner_ID();
_oldC_BPartner_Location_ID = line.GetC_BPartner_Location_ID();
}
if (line.IsTaxIncluded() != _invoice.IsTaxIncluded())
{
return(GetRetrievedError(line, "Line " + line.GetLine() + " TaxIncluded inconsistent"));
// rollback
// throw new Exception("Line " + line.GetLine() + " TaxIncluded inconsistent");
}
// Add Line
MInvoiceLine invoiceLine = new MInvoiceLine(_invoice);
invoiceLine.SetDescription(line.GetDescription());
invoiceLine.SetC_Charge_ID(line.GetC_Charge_ID());
invoiceLine.SetQty(line.GetQtyEntered()); // Entered/Invoiced
invoiceLine.SetPrice(line.GetPriceEntered());
invoiceLine.SetC_Tax_ID(line.GetC_Tax_ID());
invoiceLine.SetTaxAmt(line.GetTaxAmt());
invoiceLine.SetLineNetAmt(line.GetLineNetAmt());
invoiceLine.SetLineTotalAmt(line.GetLineTotalAmt());
if (!invoiceLine.Save())
{
return(GetRetrievedError(invoiceLine, "Cannot save Invoice Line"));
// rollback
// throw new Exception("Cannot save Invoice Line");
}
// Update Batch Line
line.SetC_Invoice_ID(_invoice.GetC_Invoice_ID());
line.SetC_InvoiceLine_ID(invoiceLine.GetC_InvoiceLine_ID());
line.Save();
} // for all lines
CompleteInvoice();
//
batch.SetProcessed(true);
batch.Save();
return("#" + _count);
} // doIt
} // prepare
/// <summary>
/// Perrform Process.
/// </summary>
/// <returns>clear message</returns>
protected override String DoIt()
{
StringBuilder sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID;
// **** Prepare ****
// Delete Old Imported
if (_deleteOldImported)
{
sql = new StringBuilder("DELETE FROM I_Invoice "
+ "WHERE I_IsImported='Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append("),"
+ " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_AD_Org_ID).Append("),"
+ " IsActive = COALESCE (IsActive, 'Y'),"
+ " Created = COALESCE (Created, SysDate),"
+ " CreatedBy = COALESCE (CreatedBy, 0),"
+ " Updated = COALESCE (Updated, SysDate),"
+ " UpdatedBy = COALESCE (UpdatedBy, 0),"
+ " I_ErrorMsg = NULL,"
+ " I_IsImported = 'N' "
+ "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Reset=" + no);
String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '"
+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
+ " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Org=" + no);
}
// Document Type - PO - SO
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
+ " AND d.DocBaseType IN ('API','APC') AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set PO DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
+ " AND d.DocBaseType IN ('ARI','ARC') AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set SO DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
+ " AND d.DocBaseType IN ('API','ARI','APC','ARC') AND o.AD_Client_ID=d.AD_Client_ID) "
//+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append (clientCheck);
+ "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid DocTypeName, ' "
+ "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid DocTypeName=" + no);
}
// DocType Default
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'"
+ " AND d.DocBaseType='API' AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set PO Default DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'"
+ " AND d.DocBaseType='ARI' AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set SO Default DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'"
+ " AND d.DocBaseType IN('ARI','API') AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set Default DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No DocType, ' "
+ "WHERE C_DocType_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No DocType=" + no);
}
// Set IsSOTrx
sql = new StringBuilder("UPDATE I_Invoice o SET IsSOTrx='Y' "
+ "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='ARI' AND o.AD_Client_ID=d.AD_Client_ID)"
+ " AND C_DocType_ID IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set IsSOTrx=Y=" + no);
sql = new StringBuilder("UPDATE I_Invoice o SET IsSOTrx='N' "
+ "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='API' AND o.AD_Client_ID=d.AD_Client_ID)"
+ " AND C_DocType_ID IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set IsSOTrx=N=" + no);
// Price List
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'"
+ " AND p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default Currency PriceList=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'"
+ " AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default PriceList=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p "
+ " WHERE p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Currency PriceList=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p "
+ " WHERE p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set PriceList=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No PriceList, ' "
+ "WHERE M_PriceList_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No PriceList=" + no);
}
// Payment Rule
// We support Payment Rule being input in the login language
VAdvantage.Login.Language language = VAdvantage.Login.Language.GetLoginLanguage(GetCtx()); // Base Language
String AD_Language = language.GetAD_Language();
sql = new StringBuilder("UPDATE I_Invoice O " +
"SET PaymentRule= " +
"(SELECT R.Value " +
" FROM AD_Ref_List R " +
" left outer join AD_Ref_List_Trl RT " +
" on RT.AD_Ref_List_ID = R.AD_Ref_List_ID and RT.AD_Language = @param " +
" WHERE R.AD_Reference_ID = 195 and coalesce( RT.Name, R.Name ) = O.PaymentRuleName ) " +
"WHERE PaymentRule is null AND PaymentRuleName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@param", AD_Language);
no = DataBase.DB.ExecuteQuery(sql.ToString(), param, Get_TrxName());
log.Fine("Set PaymentRule=" + no);
// do not set a default; if null, the import logic will derive from the business partner
// do not error in absence of a default
// Payment Term
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p"
+ " WHERE o.PaymentTermValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE C_PaymentTerm_ID IS NULL AND PaymentTermValue IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set PaymentTerm=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_PaymentTerm_ID=(SELECT MAX(C_PaymentTerm_ID) FROM C_PaymentTerm p"
+ " WHERE p.IsDefault='Y' AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE C_PaymentTerm_ID IS NULL AND o.PaymentTermValue IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default PaymentTerm=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No PaymentTerm, ' "
+ "WHERE C_PaymentTerm_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No PaymentTerm=" + no);
}
// BP from EMail
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u"
+ " WHERE o.EMail=u.EMail AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) "
+ "WHERE C_BPartner_ID IS NULL AND EMail IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BP from EMail=" + no);
// BP from ContactName
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u"
+ " WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) "
+ "WHERE C_BPartner_ID IS NULL AND ContactName IS NOT NULL"
+ " AND EXISTS (SELECT Name FROM AD_User u WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL GROUP BY Name HAVING COUNT(*)=1)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BP from ContactName=" + no);
// BP from Value
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_BPartner bp"
+ " WHERE o.BPartnerValue=bp.Value AND o.AD_Client_ID=bp.AD_Client_ID) "
+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BP from Value=" + no);
// Default BP
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_BPartner_ID=(SELECT C_BPartnerCashTrx_ID FROM AD_ClientInfo c"
+ " WHERE o.AD_Client_ID=c.AD_Client_ID) "
+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NULL AND Name IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default BP=" + no);
// Existing Location ? Exact Match
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID"
+ " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)"
+ " WHERE o.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=o.AD_Client_ID"
+ " AND DUMP(o.Address1)=DUMP(l.Address1) AND DUMP(o.Address2)=DUMP(l.Address2)"
+ " AND DUMP(o.City)=DUMP(l.City) AND DUMP(o.Postal)=DUMP(l.Postal)"
+ " AND DUMP(o.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(o.C_Country_ID)=DUMP(l.C_Country_ID)) "
+ "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
+ " AND I_IsImported='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Found Location=" + no);
// Set Location from BPartner
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_BPartner_Location_ID=(SELECT MAX(C_BPartner_Location_ID) FROM C_BPartner_Location l"
+ " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID"
+ " AND ((l.IsBillTo='Y' AND o.IsSOTrx='Y') OR o.IsSOTrx='N')"
+ ") "
+ "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BP Location from BP=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BP Location, ' "
+ "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No BP Location=" + no);
}
// Set Country
/**
* sql = new StringBuilder ("UPDATE I_Invoice o "
+ "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'"
+ " AND c.AD_Client_ID IN (0, o.AD_Client_ID) AND ROWNUM=1) "
+ "WHERE C_BPartner_ID IS NULL AND CountryCode IS NULL AND C_Country_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append (clientCheck);
+ no = DataBase.DB.ExecuteQuery(sql.ToString(),null, Get_TrxName());
+ log.Fine("Set Country Default=" + no);
**/
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c"
+ " WHERE o.CountryCode=c.CountryCode AND c.IsSummary='N' AND c.AD_Client_ID IN (0, o.AD_Client_ID)) "
+ "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL AND CountryCode IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Country=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Country, ' "
+ "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Country=" + no);
}
// Set Region
sql = new StringBuilder("UPDATE I_Invoice o "
+ "Set RegionName=(SELECT MAX(Name) FROM C_Region r"
+ " WHERE r.IsDefault='Y' AND r.C_Country_ID=o.C_Country_ID"
+ " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) "
+ "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Region Default=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice o "
+ "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r"
+ " WHERE r.Name=o.RegionName AND r.C_Country_ID=o.C_Country_ID"
+ " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) "
+ "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Region=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Region, ' "
+ "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL "
+ " AND EXISTS (SELECT * FROM C_Country c"
+ " WHERE c.C_Country_ID=o.C_Country_ID AND c.HasRegion='Y')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Region=" + no);
}
// Product
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p"
+ " WHERE o.ProductValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND ProductValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Product from Value=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p"
+ " WHERE o.UPC=p.UPC AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Product from UPC=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p"
+ " WHERE o.SKU=p.SKU AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND SKU IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Product fom SKU=" + no);
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Product, ' "
+ "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Product=" + no);
}
// Tax
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_Tax_ID=(SELECT MAX(C_Tax_ID) FROM C_Tax t"
+ " WHERE o.TaxIndicator=t.TaxIndicator AND o.AD_Client_ID=t.AD_Client_ID) "
+ "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Tax=" + no);
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Tax, ' "
+ "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Tax=" + no);
}
Commit();
// -- New BPartner ---------------------------------------------------
// Go through Invoice Records w/o C_BPartner_ID
sql = new StringBuilder("SELECT * FROM I_Invoice "
+ "WHERE I_IsImported='N' AND C_BPartner_ID IS NULL").Append(clientCheck);
IDataReader idr = null;
try
{
//PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString(), Get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
X_I_Invoice imp = new X_I_Invoice(GetCtx(), idr, Get_TrxName());
if (imp.GetBPartnerValue() == null)
{
if (imp.GetEMail() != null)
{
imp.SetBPartnerValue(imp.GetEMail());
}
else if (imp.GetName() != null)
{
imp.SetBPartnerValue(imp.GetName());
}
else
{
continue;
}
}
if (imp.GetName() == null)
{
if (imp.GetContactName() != null)
{
imp.SetName(imp.GetContactName());
}
else
{
imp.SetName(imp.GetBPartnerValue());
}
}
// BPartner
MBPartner bp = MBPartner.Get(GetCtx(), imp.GetBPartnerValue());
if (bp == null)
{
bp = new MBPartner(GetCtx(), -1, Get_TrxName());
bp.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID());
bp.SetValue(imp.GetBPartnerValue());
bp.SetName(imp.GetName());
if (!bp.Save())
{
continue;
}
}
imp.SetC_BPartner_ID(bp.GetC_BPartner_ID());
// BP Location
MBPartnerLocation bpl = null;
MBPartnerLocation[] bpls = bp.GetLocations(true);
for (int i = 0; bpl == null && i < bpls.Length; i++)
{
if (imp.GetC_BPartner_Location_ID() == bpls[i].GetC_BPartner_Location_ID())
{
bpl = bpls[i];
}
// Same Location ID
else if (imp.GetC_Location_ID() == bpls[i].GetC_Location_ID())
{
bpl = bpls[i];
}
// Same Location Info
else if (imp.GetC_Location_ID() == 0)
{
MLocation loc = bpl.GetLocation(false);
if (loc.Equals(imp.GetC_Country_ID(), imp.GetC_Region_ID(),
imp.GetPostal(), "", imp.GetCity(),
imp.GetAddress1(), imp.GetAddress2()))
{
bpl = bpls[i];
}
}
}
if (bpl == null)
{
// New Location
MLocation loc = new MLocation(GetCtx(), 0, Get_TrxName());
loc.SetAddress1(imp.GetAddress1());
loc.SetAddress2(imp.GetAddress2());
loc.SetCity(imp.GetCity());
loc.SetPostal(imp.GetPostal());
if (imp.GetC_Region_ID() != 0)
{
loc.SetC_Region_ID(imp.GetC_Region_ID());
}
loc.SetC_Country_ID(imp.GetC_Country_ID());
if (!loc.Save())
{
continue;
}
//
bpl = new MBPartnerLocation(bp);
bpl.SetC_Location_ID(imp.GetC_Location_ID());
if (!bpl.Save())
{
continue;
}
}
imp.SetC_Location_ID(bpl.GetC_Location_ID());
imp.SetC_BPartner_Location_ID(bpl.GetC_BPartner_Location_ID());
// User/Contact
if (imp.GetContactName() != null ||
imp.GetEMail() != null ||
imp.GetPhone() != null)
{
MUser[] users = bp.GetContacts(true);
MUser user = null;
for (int i = 0; user == null && i < users.Length; i++)
{
String name = users[i].GetName();
if (name.Equals(imp.GetContactName()) ||
name.Equals(imp.GetName()))
{
user = users[i];
imp.SetAD_User_ID(user.GetAD_User_ID());
}
}
if (user == null)
{
user = new MUser(bp);
if (imp.GetContactName() == null)
{
user.SetName(imp.GetName());
}
else
{
user.SetName(imp.GetContactName());
}
user.SetEMail(imp.GetEMail());
user.SetPhone(imp.GetPhone());
if (user.Save())
{
imp.SetAD_User_ID(user.GetAD_User_ID());
}
}
}
imp.Save();
} // for all new BPartners
idr.Close();
//
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, "CreateBP", e);
}
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BPartner, ' "
+ "WHERE C_BPartner_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No BPartner=" + no);
}
Commit();
// -- New Invoices -----------------------------------------------------
int noInsert = 0;
int noInsertLine = 0;
// Go through Invoice Records w/o
sql = new StringBuilder("SELECT * FROM I_Invoice "
+ "WHERE I_IsImported='N'").Append(clientCheck)
.Append(" ORDER BY C_BPartner_ID, C_BPartner_Location_ID, I_Invoice_ID");
try
{
//PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString(), Get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
// Group Change
int oldC_BPartner_ID = 0;
int oldC_BPartner_Location_ID = 0;
String oldDocumentNo = "";
//
MInvoice invoice = null;
int lineNo = 0;
while (idr.Read())
{
X_I_Invoice imp = new X_I_Invoice(GetCtx(), idr, null);
String cmpDocumentNo = imp.GetDocumentNo();
if (cmpDocumentNo == null)
{
cmpDocumentNo = "";
}
// New Invoice
if (oldC_BPartner_ID != imp.GetC_BPartner_ID() ||
oldC_BPartner_Location_ID != imp.GetC_BPartner_Location_ID() ||
!oldDocumentNo.Equals(cmpDocumentNo))
{
if (invoice != null)
{
invoice.ProcessIt(_docAction);
invoice.Save();
}
// Group Change
oldC_BPartner_ID = imp.GetC_BPartner_ID();
oldC_BPartner_Location_ID = imp.GetC_BPartner_Location_ID();
oldDocumentNo = imp.GetDocumentNo();
if (oldDocumentNo == null)
{
oldDocumentNo = "";
}
//
invoice = new MInvoice(GetCtx(), 0, null);
invoice.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID());
invoice.SetC_DocTypeTarget_ID(imp.GetC_DocType_ID(), true);
if (imp.GetDocumentNo() != null)
{
invoice.SetDocumentNo(imp.GetDocumentNo());
}
//
invoice.SetC_BPartner_ID(imp.GetC_BPartner_ID());
invoice.SetC_BPartner_Location_ID(imp.GetC_BPartner_Location_ID());
if (imp.GetAD_User_ID() != 0)
{
invoice.SetAD_User_ID(imp.GetAD_User_ID());
}
//
if (imp.GetDescription() != null)
{
invoice.SetDescription(imp.GetDescription());
}
if (imp.GetPaymentRule() != null)
{
invoice.SetPaymentRule(imp.GetPaymentRule());
}
invoice.SetC_PaymentTerm_ID(imp.GetC_PaymentTerm_ID());
invoice.SetM_PriceList_ID(imp.GetM_PriceList_ID());
MPriceList pl = MPriceList.Get(GetCtx(), imp.GetM_PriceList_ID(), Get_TrxName());
invoice.SetIsTaxIncluded(pl.IsTaxIncluded());
// SalesRep from Import or the person running the import
if (imp.GetSalesRep_ID() != 0)
{
invoice.SetSalesRep_ID(imp.GetSalesRep_ID());
}
if (invoice.GetSalesRep_ID() == 0)
{
invoice.SetSalesRep_ID(GetAD_User_ID());
}
//
if (imp.GetAD_OrgTrx_ID() != 0)
{
invoice.SetAD_OrgTrx_ID(imp.GetAD_OrgTrx_ID());
}
if (imp.GetC_Activity_ID() != 0)
{
invoice.SetC_Activity_ID(imp.GetC_Activity_ID());
}
if (imp.GetC_Campaign_ID() != 0)
{
invoice.SetC_Campaign_ID(imp.GetC_Campaign_ID());
}
if (imp.GetC_Project_ID() != 0)
{
invoice.SetC_Project_ID(imp.GetC_Project_ID());
}
//
if (imp.GetDateInvoiced() != null)
{
invoice.SetDateInvoiced(imp.GetDateInvoiced());
}
if (imp.GetDateAcct() != null)
{
invoice.SetDateAcct(imp.GetDateAcct());
}
//
invoice.Save();
noInsert++;
lineNo = 10;
}
imp.SetC_Invoice_ID(invoice.GetC_Invoice_ID());
// New InvoiceLine
MInvoiceLine line = new MInvoiceLine(invoice);
if (imp.GetLineDescription() != null)
{
line.SetDescription(imp.GetLineDescription());
}
line.SetLine(lineNo);
lineNo += 10;
if (imp.GetM_Product_ID() != 0)
{
line.SetM_Product_ID(imp.GetM_Product_ID(), true);
}
line.SetQty(imp.GetQtyOrdered());
line.SetPrice();
Decimal?price = (Decimal?)imp.GetPriceActual();
if (price != null && Env.ZERO.CompareTo(price) != 0)
{
line.SetPrice(price.Value);
}
if (imp.GetC_Tax_ID() != 0)
{
line.SetC_Tax_ID(imp.GetC_Tax_ID());
}
else
{
line.SetTax();
imp.SetC_Tax_ID(line.GetC_Tax_ID());
}
Decimal?taxAmt = (Decimal?)imp.GetTaxAmt();
if (taxAmt != null && Env.ZERO.CompareTo(taxAmt) != 0)
{
line.SetTaxAmt(taxAmt);
}
line.Save();
//
imp.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID());
imp.SetI_IsImported(X_I_Invoice.I_ISIMPORTED_Yes);
imp.SetProcessed(true);
//
if (imp.Save())
{
noInsertLine++;
}
}
if (invoice != null)
{
invoice.ProcessIt(_docAction);
invoice.Save();
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, "CreateInvoice", e);
}
// Set Error to indicator to not imported
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='N', Updated=SysDate "
+ "WHERE I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@");
//
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_Invoice_ID@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@C_InvoiceLine_ID@: @Inserted@");
return("");
} // doIt
/**
* Create Difference Document
* @param inout shipment/receipt
* @param confirm confirm line
* @return true if created
*/
private bool CreateDifferenceDoc(MInOut inout, MInOutLineConfirm confirm)
{
if (_processMsg == null)
{
_processMsg = "";
}
else if (_processMsg.Length > 0)
{
_processMsg += "; ";
}
// Credit Memo if linked Document
if (Env.Signum(confirm.GetDifferenceQty()) != 0 &&
!inout.IsSOTrx() && !inout.IsReturnTrx() && inout.GetRef_InOut_ID() != 0)
{
log.Info("Difference=" + confirm.GetDifferenceQty());
if (_creditMemo == null)
{
_creditMemo = new MInvoice(inout, null);
_creditMemo.SetDescription(Msg.Translate(GetCtx(),
"M_InOutConfirm_ID") + " " + GetDocumentNo());
_creditMemo.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APCREDITMEMO);
if (!_creditMemo.Save(Get_TrxName()))
{
_processMsg += "Credit Memo not created";
return(false);
}
SetC_Invoice_ID(_creditMemo.GetC_Invoice_ID());
}
MInvoiceLine line = new MInvoiceLine(_creditMemo);
line.SetShipLine(confirm.GetLine());
line.SetQty(confirm.GetDifferenceQty()); // Entered/Invoiced
if (!line.Save(Get_TrxName()))
{
_processMsg += "Credit Memo Line not created";
return(false);
}
confirm.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID());
}
// Create Inventory Difference
if (Env.Signum(confirm.GetScrappedQty()) != 0)
{
log.Info("Scrapped=" + confirm.GetScrappedQty());
if (_inventory == null)
{
MWarehouse wh = MWarehouse.Get(GetCtx(), inout.GetM_Warehouse_ID());
_inventory = new MInventory(wh);
_inventory.SetDescription(Msg.Translate(GetCtx(),
"M_InOutConfirm_ID") + " " + GetDocumentNo());
//vikas new 13 jan 2016 1
_inventory.SetIsInternalUse(true);
if (_inventory.GetC_DocType_ID() == 0)
{
MDocType[] types = MDocType.GetOfDocBaseType(GetCtx(), MDocBaseType.DOCBASETYPE_MATERIALPHYSICALINVENTORY);
if (types.Length > 0)
{
// Get Internal Use Inv Doc Type
for (int i = 0; i < types.Length; i++)
{
int _count = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Count(*) FROM C_DocType WHERE IsActive='Y' AND IsInternalUse='Y' AND C_DocType_ID=" + types[i].GetC_DocType_ID()));
if (_count > 0)
{
_inventory.SetC_DocType_ID(types[i].GetC_DocType_ID());
break;
}
}
}
else
{
log.SaveError("Error", Msg.ParseTranslation(GetCtx(), "@NotFound@ @C_DocType_ID@"));
return(false);
}
}
// 13 jan End
if (!_inventory.Save(Get_TrxName()))
{
_processMsg += "Inventory not created";
return(false);
}
SetM_Inventory_ID(_inventory.GetM_Inventory_ID());
}
MInOutLine ioLine = confirm.GetLine();
MInventoryLine line = new MInventoryLine(_inventory,
ioLine.GetM_Locator_ID(), ioLine.GetM_Product_ID(),
ioLine.GetM_AttributeSetInstance_ID(),
confirm.GetScrappedQty(), Env.ZERO);
//new 15 jan
line.SetQtyInternalUse(line.GetQtyBook());
line.SetQtyBook(0);
line.SetIsInternalUse(true);
Tuple <String, String, String> mInfo = null;
if (Env.HasModulePrefix("DTD001_", out mInfo))
{
int _charge = Util.GetValueOfInt(DB.ExecuteScalar("SELECT C_Charge_ID FROM C_Charge WHERE isactive='Y' AND DTD001_ChargeType='INV'"));
line.SetC_Charge_ID(_charge);
}
// End
if (!line.Save(Get_TrxName()))
{
_processMsg += "Inventory Line not created";
return(false);
}
confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
}
//
if (!confirm.Save(Get_TrxName()))
{
_processMsg += "Confirmation Line not saved";
return(false);
}
return(true);
}
public static bool InsertForeignCostMatchInvoice(Ctx ctx, MInvoiceLine invoiceLine, decimal matchQty, int ASI, Trx trx)
{
int acctSchema_ID = 0;
int M_CostElement_ID = 0;
int AD_Org_ID = 0;
int M_ASI_ID = 0;
MProduct product = null;
MAcctSchema acctSchema = null;
MCostForeignCurrency foreignCost = null;
dynamic pc = null;
String cl = null;
MInvoice invoice = null;
try
{
// if cost is calculated then not to calculate again
if (invoiceLine.IsFutureCostCalculated())
{
return(true);
}
invoice = new MInvoice(ctx, invoiceLine.GetC_Invoice_ID(), trx);
if (!invoice.IsSOTrx() && !invoice.IsReturnTrx())
{
acctSchema_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT asch.c_acctschema_id FROM c_acctschema asch INNER JOIN ad_clientinfo ci
ON ci.c_acctschema1_id = asch.c_acctschema_id WHERE ci.ad_client_id = " + invoice.GetAD_Client_ID()));
acctSchema = new MAcctSchema(ctx, acctSchema_ID, trx);
if (acctSchema.GetC_Currency_ID() != invoice.GetC_Currency_ID())
{
// Get Costing Element of Av. Invoice
M_CostElement_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT M_CostElement_ID FROM M_CostElement WHERE AD_Client_ID = "
+ invoice.GetAD_Client_ID() + " AND IsActive = 'Y' AND CostingMethod = 'I'"));
product = new MProduct(ctx, invoiceLine.GetM_Product_ID(), trx);
if (product != null && product.GetProductType() == "I" && product.GetM_Product_ID() > 0) // for Item Type product
{
pc = MProductCategory.Get(product.GetCtx(), product.GetM_Product_Category_ID());
// Get Costing Level
if (pc != null)
{
cl = pc.GetCostingLevel();
}
if (cl == null)
{
cl = acctSchema.GetCostingLevel();
}
if (cl == "C" || cl == "B")
{
AD_Org_ID = 0;
}
else
{
AD_Org_ID = invoice.GetAD_Org_ID();
}
if (cl != "B")
{
M_ASI_ID = 0;
}
else
{
M_ASI_ID = ASI;
}
foreignCost = MCostForeignCurrency.Get(product, M_ASI_ID, AD_Org_ID, M_CostElement_ID, invoice.GetC_BPartner_ID(), invoice.GetC_Currency_ID());
foreignCost.SetC_Invoice_ID(invoice.GetC_Invoice_ID());
foreignCost.SetCumulatedQty(Decimal.Add(foreignCost.GetCumulatedQty(), matchQty));
foreignCost.SetCumulatedAmt(Decimal.Add(foreignCost.GetCumulatedAmt(), Decimal.Multiply(invoiceLine.GetPriceActual(), matchQty)));
if (foreignCost.GetCumulatedQty() != 0)
{
foreignCost.SetCostPerUnit(Decimal.Round(Decimal.Divide(foreignCost.GetCumulatedAmt(), foreignCost.GetCumulatedQty()), acctSchema.GetCostingPrecision()));
}
else
{
foreignCost.SetCostPerUnit(0);
}
if (!foreignCost.Save(trx))
{
ValueNamePair pp = VLogger.RetrieveError();
_log.Severe("Error occured during updating M_Cost_ForeignCurrency. Error name : " + pp.GetName() +
" AND Error Value : " + pp.GetValue() + " , For Invoice line : " + invoiceLine.GetC_InvoiceLine_ID() +
" , AND Ad_Client_ID : " + invoiceLine.GetAD_Client_ID());
return(false);
}
}
}
}
}
catch (Exception ex)
{
_log.Log(Level.SEVERE, "", ex);
return(false);
}
return(true);
}
/**
* Create Difference Document
* @param inout shipment/receipt
* @param confirm confirm line
* @return true if created
*/
private bool CreateDifferenceDoc(MInOut inout, MInOutLineConfirm confirm)
{
if (_processMsg == null)
{
_processMsg = "";
}
else if (_processMsg.Length > 0)
{
_processMsg += "; ";
}
// Credit Memo if linked Document
if (Env.Signum(confirm.GetDifferenceQty()) != 0 &&
!inout.IsSOTrx() && !inout.IsReturnTrx() && inout.GetRef_InOut_ID() != 0)
{
log.Info("Difference=" + confirm.GetDifferenceQty());
if (_creditMemo == null)
{
_creditMemo = new MInvoice(inout, null);
_creditMemo.SetDescription(Msg.Translate(GetCtx(),
"M_InOutConfirm_ID") + " " + GetDocumentNo());
_creditMemo.SetC_DocTypeTarget_ID(MDocBaseType.DOCBASETYPE_APCREDITMEMO);
if (!_creditMemo.Save(Get_TrxName()))
{
_processMsg += "Credit Memo not created";
return(false);
}
SetC_Invoice_ID(_creditMemo.GetC_Invoice_ID());
}
MInvoiceLine line = new MInvoiceLine(_creditMemo);
line.SetShipLine(confirm.GetLine());
line.SetQty(confirm.GetDifferenceQty()); // Entered/Invoiced
if (!line.Save(Get_TrxName()))
{
_processMsg += "Credit Memo Line not created";
return(false);
}
confirm.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID());
}
// Create Inventory Difference
if (Env.Signum(confirm.GetScrappedQty()) != 0)
{
log.Info("Scrapped=" + confirm.GetScrappedQty());
if (_inventory == null)
{
MWarehouse wh = MWarehouse.Get(GetCtx(), inout.GetM_Warehouse_ID());
_inventory = new MInventory(wh);
_inventory.SetDescription(Msg.Translate(GetCtx(),
"M_InOutConfirm_ID") + " " + GetDocumentNo());
if (!_inventory.Save(Get_TrxName()))
{
_processMsg += "Inventory not created";
return(false);
}
SetM_Inventory_ID(_inventory.GetM_Inventory_ID());
}
MInOutLine ioLine = confirm.GetLine();
MInventoryLine line = new MInventoryLine(_inventory,
ioLine.GetM_Locator_ID(), ioLine.GetM_Product_ID(),
ioLine.GetM_AttributeSetInstance_ID(),
confirm.GetScrappedQty(), Env.ZERO);
if (!line.Save(Get_TrxName()))
{
_processMsg += "Inventory Line not created";
return(false);
}
confirm.SetM_InventoryLine_ID(line.GetM_InventoryLine_ID());
}
//
if (!confirm.Save(Get_TrxName()))
{
_processMsg += "Confirmation Line not saved";
return(false);
}
return(true);
}
