/// <summary>
/// Load Invoice Line
/// </summary>
/// <param name="journal"></param>
/// <returns>DocLine Array</returns>
private DocLine[] LoadLines(MJournal journal)
{
List <DocLine> list = new List <DocLine>();
MJournalLine[] lines = journal.GetLines(false);
for (int i = 0; i < lines.Length; i++)
{
MJournalLine line = lines[i];
DocLine docLine = new DocLine(line, this);
// -- Source Amounts
docLine.SetAmount(line.GetAmtSourceDr(), line.GetAmtSourceCr());
// -- Converted Amounts
docLine.SetConvertedAmt(_C_AcctSchema_ID, line.GetAmtAcctDr(), line.GetAmtAcctCr());
// -- Account
MAccount account = line.GetAccount();
docLine.SetAccount(account);
// -- Quantity
docLine.SetQty(line.GetQty(), false);
// -- Date
docLine.SetDateAcct(journal.GetDateAcct());
// -- Organization of Line was set to Org of Account
list.Add(docLine);
}
// Return Array
int size = list.Count;
DocLine[] dls = new DocLine[size];
dls = list.ToArray();
return(dls);
}
} // getLines
/// <summary>
/// Copy Lines from other Journal
/// </summary>
/// <param name="fromJournal">Journal</param>
/// <param name="dateAcct">date used - if null original</param>
/// <param name="typeCR">type of copying (C)orrect=negate - (R)everse=flip dr/cr - otherwise just copy</param>
/// <returns>number of lines copied</returns>
public int CopyLinesFrom(MJournal fromJournal, DateTime?dateAcct, char typeCR)
{
if (IsProcessed() || fromJournal == null)
{
return(0);
}
int count = 0;
int lineCount = 0;
MJournalLine[] fromLines = fromJournal.GetLines(false);
for (int i = 0; i < fromLines.Length; i++)
{
MJournalLine toLine = new MJournalLine(GetCtx(), 0, fromJournal.Get_TrxName());
PO.CopyValues(fromLines[i], toLine, GetAD_Client_ID(), GetAD_Org_ID());
toLine.SetGL_Journal_ID(GetGL_Journal_ID());
//
if (dateAcct != null)
{
toLine.SetDateAcct(dateAcct);
}
// Amounts
if (typeCR == 'C') // correct
{
// toLine.SetAmtSourceDr(fromLines[i].GetAmtSourceDr().negate());
toLine.SetAmtSourceDr(Decimal.Negate(fromLines[i].GetAmtSourceDr()));
toLine.SetAmtSourceCr(Decimal.Negate(fromLines[i].GetAmtSourceCr())); //.negate());
}
else if (typeCR == 'R') // reverse
{
toLine.SetAmtSourceDr(fromLines[i].GetAmtSourceCr());
toLine.SetAmtSourceCr(fromLines[i].GetAmtSourceDr());
}
toLine.SetIsGenerated(true);
toLine.SetProcessed(false);
// // Set Orignal Document Reference on Reversal Document
if (Get_ColumnIndex("IsReversal") > 0 && IsReversal())
{
if (toLine.Get_ColumnIndex("ReversalDoc_ID") > 0)
{
toLine.SetReversalDoc_ID(fromLines[i].GetGL_JournalLine_ID());
}
}
if (toLine.Save())
{
count++;
lineCount += toLine.CopyLinesFrom(fromLines[i], toLine.GetGL_JournalLine_ID());
}
}
if (fromLines.Length != count)
{
log.Log(Level.SEVERE, "Line difference - JournalLines=" + fromLines.Length + " <> Saved=" + count);
}
return(count);
} // copyLinesFrom
} // copyLinesFrom
// Mainsh 18/7/2016...
public int CopyLines(MJournal fromJournal, char typeCR)
{
DateTime?dateAcct = GetDateAcct();
if (IsProcessed() || fromJournal == null)
{
return(0);
}
int count = 0;
int lineCount = 0;
MJournalLine[] fromLines = fromJournal.GetLines(false);
for (int i = 0; i < fromLines.Length; i++)
{
MJournalLine toLine = new MJournalLine(GetCtx(), 0, fromJournal.Get_TrxName());
PO.CopyValues(fromLines[i], toLine, GetAD_Client_ID(), GetAD_Org_ID());
toLine.SetGL_Journal_ID(GetGL_Journal_ID());
//
//if (dateAcct != null)
//{
// toLine.SetDateAcct(dateAcct);
//}
// Amounts
//if (typeCR == 'C') // correct
//{
// // toLine.SetAmtSourceDr(fromLines[i].GetAmtSourceDr().negate());
// toLine.SetAmtSourceDr(Decimal.Negate(fromLines[i].GetAmtSourceDr()));
// toLine.SetAmtSourceCr(Decimal.Negate(fromLines[i].GetAmtSourceCr()));//.negate());
//}
//else if (typeCR == 'R') // reverse
//{
toLine.SetAmtSourceDr(fromLines[i].GetAmtSourceDr());
toLine.SetAmtSourceCr(fromLines[i].GetAmtSourceCr());
// }
toLine.SetDescription(fromLines[i].GetDescription());
toLine.SetC_Currency_ID(fromLines[i].GetC_Currency_ID());
toLine.SetIsGenerated(true);
toLine.SetProcessed(false);
toLine.SetQty(fromLines[i].GetQty());
toLine.SetElementType(fromLines[i].GetElementType());
if (toLine.Save(fromJournal.Get_TrxName()))
{
count++;
lineCount += toLine.CopyLinesFrom(fromLines[i], toLine.GetGL_JournalLine_ID());
}
}
if (fromLines.Length != count)
{
log.Log(Level.SEVERE, "Line difference - JournalLines=" + fromLines.Length + " <> Saved=" + count);
}
return(count);
} // copyLinesFrom
/// <summary>
/// Get or Create Gl Journal Line
/// </summary>
/// <param name="Journal">GL Journal</param>
/// <param name="Line">GL Journal Line</param>
/// <param name="M_Product_ID">Product</param>
/// <param name="C_Charge_ID">Charge</param>
/// <param name="Campaign_ID">Campaign</param>
/// <param name="Account_ID">Account</param>
/// <param name="Opprtunity_ID">Opportunity</param>
/// <param name="Activity_ID">Activity</param>
/// <param name="BPartner_ID">Business Partner</param>
/// <param name="Org_Id">Organization</param>
/// <param name="trxOrg_ID">Transaction Organization</param>
/// <returns>journal line object</returns>
public MJournalLine GetOrCreate(MJournal Journal, MJournalLine Line, int M_Product_ID, int C_Charge_ID, int Campaign_ID, int Account_ID, int Opprtunity_ID, int Activity_ID, int BPartner_ID, int Org_Id, int trxOrg_ID)
{
MJournalLine retValue = null;
String sql = "SELECT * FROM GL_JournalLine " +
" WHERE GL_Journal_ID = " + Journal.GetGL_Journal_ID() +
" AND NVL(M_Product_ID,0)=" + M_Product_ID +
" AND NVL(ACCOUNT_ID,0)=" + Account_ID +
" AND NVL(C_CAMPAIGN_ID,0)=" + Campaign_ID +
" AND NVL(C_PROJECT_ID,0)=" + Opprtunity_ID +
" AND NVL(C_ACTIVITY_ID,0)=" + Activity_ID +
" AND NVL(C_BPARTNER_ID,0)=" + BPartner_ID +
" AND NVL(AD_ORGTRX_ID,0)=" + trxOrg_ID +
" AND NVL(AD_ORG_ID,0)=" + Org_Id;
DataTable dt = null;
IDataReader idr = null;
try
{
idr = DB.ExecuteReader(sql, null, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)
{
retValue = new MJournalLine(Journal.GetCtx(), dr, Get_TrxName());
}
}
catch (Exception ex)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, ex);
}
finally
{
if (idr != null)
{
idr.Close();
}
dt = null;
}
if (retValue == null)
{
retValue = CreateJournalLine(Journal, Line, M_Product_ID, C_Charge_ID, Campaign_ID, Account_ID, Opprtunity_ID, Activity_ID, BPartner_ID, trxOrg_ID);
}
return(retValue);
}
/// <summary>
/// Generate Journal Line
/// </summary>
/// <param name="Journal">GL Journal</param>
/// <param name="Line">Gl Journal line</param>
/// <param name="M_Product_ID">Product</param>
/// <param name="C_Charge_ID">Charge</param>
/// <param name="Campaign_ID">Campaign</param>
/// <param name="Account_ID">Account</param>
/// <param name="Opprtunity_ID">Opportunity</param>
/// <param name="Activity_ID">Activity</param>
/// <param name="BPartner_ID">Business Partner</param>
/// <param name="trxOrg_ID">Transaction Organization</param>
/// <returns>object of journal line</returns>
public MJournalLine CreateJournalLine(MJournal Journal, MJournalLine Line, int M_Product_ID, int C_Charge_ID, int Campaign_ID, int Account_ID, int Opprtunity_ID, int Activity_ID, int BPartner_ID, int trxOrg_ID)
{
Line = new MJournalLine(Journal);
Line.SetLine(lineno);
Line.Set_ValueNoCheck("Account_ID", Account_ID);
Line.Set_ValueNoCheck("C_BPartner_ID", BPartner_ID);
Line.Set_ValueNoCheck("M_Product_ID", M_Product_ID);
Line.SetAD_OrgTrx_ID(trxOrg_ID);
//Line.Set_ValueNoCheck("C_Charge_ID", C_Charge_ID);
Line.Set_ValueNoCheck("C_Campaign_ID", Campaign_ID);
Line.Set_ValueNoCheck("C_Project_ID", Opprtunity_ID);
Line.Set_ValueNoCheck("C_Activity_ID", Activity_ID);
return(Line);
}
} // setRate
/// <summary>
/// Get Journal Lines
/// </summary>
/// <param name="requery">requery</param>
/// <returns>Array of lines</returns>
public MJournalLine[] GetLines(Boolean requery)
{
//ArrayList<MJournalLine> list = new ArrayList<MJournalLine>();
List <MJournalLine> list = new List <MJournalLine>();
String sql = "SELECT * FROM GL_JournalLine WHERE GL_Journal_ID=@Param1 ORDER BY Line";
//PreparedStatement pstmt = null;
SqlParameter[] Param = new SqlParameter[1];
IDataReader idr = null;
DataTable dt = null;
try
{
//pstmt = DataBase.prepareStatement(sql, get_TrxName());
//pstmt.setInt(1, getGL_Journal_ID());
Param[0] = new SqlParameter("@Param1", GetGL_Journal_ID());
idr = DB.ExecuteReader(sql, Param, Get_TrxName());
dt = new DataTable();
dt.Load(idr);
idr.Close();
//while (rs.next())
foreach (DataRow dr in dt.Rows)
{
list.Add(new MJournalLine(GetCtx(), dr, Get_TrxName()));
}
dt = null;
}
catch (Exception ex)
{
if (idr != null)
{
idr.Close();
}
if (dt != null)
{
dt = null;
}
log.Log(Level.SEVERE, "getLines", ex);
}
//
MJournalLine[] retValue = new MJournalLine[list.Count];
//list.toArray(retValue);
retValue = list.ToArray();
return(retValue);
} // getLines
} // createGLJournal
/// <summary>
/// Create Balancing Entry
/// </summary>
/// <param name="asDefaultAccts">acct schema default accounts</param>
/// <param name="journal">journal</param>
/// <param name="drTotal">dr</param>
/// <param name="crTotal">cr</param>
/// <param name="AD_Org_ID">org</param>
/// <param name="lineNo">lineno base line no</param>
private void CreateBalancing(MAcctSchemaDefault asDefaultAccts, MJournal journal,
Decimal drTotal, Decimal crTotal, int AD_Org_ID, int lineNo)
{
if (journal == null)
{
throw new ArgumentException("Jornal is null");
}
// CR Entry = Gain
if (Env.Signum(drTotal) != 0)
{
MJournalLine line = new MJournalLine(journal);
line.SetLine(lineNo + 1);
MAccount bas = MAccount.Get(GetCtx(), asDefaultAccts.GetUnrealizedGain_Acct());
MAccount acct = MAccount.Get(GetCtx(), asDefaultAccts.GetAD_Client_ID(), AD_Org_ID,
asDefaultAccts.GetC_AcctSchema_ID(), bas.GetAccount_ID(), bas.GetC_SubAcct_ID(),
bas.GetM_Product_ID(), bas.GetC_BPartner_ID(), bas.GetAD_OrgTrx_ID(),
bas.GetC_LocFrom_ID(), bas.GetC_LocTo_ID(), bas.GetC_SalesRegion_ID(),
bas.GetC_Project_ID(), bas.GetC_Campaign_ID(), bas.GetC_Activity_ID(),
bas.GetUser1_ID(), bas.GetUser2_ID(), bas.GetUserElement1_ID(), bas.GetUserElement2_ID());
line.SetDescription(Msg.GetElement(GetCtx(), "UnrealizedGain_Acct"));
line.SetC_ValidCombination_ID(acct.GetC_ValidCombination_ID());
line.SetAmtSourceCr(drTotal);
line.SetAmtAcctCr(drTotal);
line.Save();
}
// DR Entry = Loss
if (Env.Signum(crTotal) != 0)
{
MJournalLine line = new MJournalLine(journal);
line.SetLine(lineNo + 2);
MAccount bas = MAccount.Get(GetCtx(), asDefaultAccts.GetUnrealizedLoss_Acct());
MAccount acct = MAccount.Get(GetCtx(), asDefaultAccts.GetAD_Client_ID(), AD_Org_ID,
asDefaultAccts.GetC_AcctSchema_ID(), bas.GetAccount_ID(), bas.GetC_SubAcct_ID(),
bas.GetM_Product_ID(), bas.GetC_BPartner_ID(), bas.GetAD_OrgTrx_ID(),
bas.GetC_LocFrom_ID(), bas.GetC_LocTo_ID(), bas.GetC_SalesRegion_ID(),
bas.GetC_Project_ID(), bas.GetC_Campaign_ID(), bas.GetC_Activity_ID(),
bas.GetUser1_ID(), bas.GetUser2_ID(), bas.GetUserElement1_ID(), bas.GetUserElement2_ID());
line.SetDescription(Msg.GetElement(GetCtx(), "UnrealizedLoss_Acct"));
line.SetC_ValidCombination_ID(acct.GetC_ValidCombination_ID());
line.SetAmtSourceDr(crTotal);
line.SetAmtAcctDr(crTotal);
line.Save();
}
} // createBalancing
} // copyLinesFrom
//end
//added by To Create Journal Lines Arpit Rai 15th Dec,2016
public int CopyJLines(MJournal fromJournal, DateTime?dateAcct)
{
if (IsProcessed() || fromJournal == null)
{
return(0);
}
int count = 0;
int lineCount = 0;
MJournalLine[] fromLines = fromJournal.GetLines(false);
for (int i = 0; i < fromLines.Length; i++)
{
MJournalLine toLine = new MJournalLine(GetCtx(), 0, fromJournal.Get_TrxName());
PO.CopyValues(fromLines[i], toLine, GetAD_Client_ID(), GetAD_Org_ID());
toLine.SetGL_Journal_ID(GetGL_Journal_ID());
if (dateAcct != null)
{
toLine.SetDateAcct(dateAcct);
}
else
{
toLine.SetDateAcct(DateTime.Now);
}
toLine.SetAmtSourceDr(fromLines[i].GetAmtSourceDr());
toLine.SetAmtSourceCr(fromLines[i].GetAmtSourceCr());
toLine.SetDescription(fromLines[i].GetDescription());
toLine.SetC_Currency_ID(fromLines[i].GetC_Currency_ID());
toLine.SetIsGenerated(true);
toLine.SetProcessed(false);
toLine.SetQty(fromLines[i].GetQty());
toLine.SetElementType(fromLines[i].GetElementType());
if (toLine.Save(fromJournal.Get_TrxName()))
{
count++;
lineCount += toLine.CopyLinesFrom(fromLines[i], toLine.GetGL_JournalLine_ID());
}
}
if (fromLines.Length != count)
{
log.Log(Level.SEVERE, "Line difference - JournalLines=" + fromLines.Length + " <> Saved=" + count);
}
return(count);
}
/// <summary>
/// Load Invoice Line
/// </summary>
/// <param name="journal"></param>
/// <returns>DocLine Array</returns>
private DocLine[] LoadLines(MJournal journal)
{
MAcctSchema mSc = new MAcctSchema(GetCtx(), _C_AcctSchema_ID, null);
List <DocLine> list = new List <DocLine>();
MJournalLine[] lines = journal.GetLines(false);
record_Id = lines[0].GetGL_Journal_ID();
for (int i = 0; i < lines.Length; i++)
{
MJournalLine line = lines[i];
if (line.GetElementType() == null)
{
DocLine docLine = new DocLine(line, this);
// -- Source Amounts
docLine.SetAmount(line.GetAmtAcctDr(), line.GetAmtAcctCr());
// -- Converted Amounts
// no need to update converted amount here
//docLine.SetConvertedAmt(_C_AcctSchema_ID, line.GetAmtAcctDr(), line.GetAmtAcctCr());
// -- Account
MAccount account = line.GetAccount();
docLine.SetAccount(account);
// -- Quantity
docLine.SetQty(line.GetQty(), false);
// -- Date
docLine.SetDateAcct(journal.GetDateAcct());
// -- Organization of Line was set to Org of Account
list.Add(docLine);
}
else
{
string sql = "SELECT * FROM GL_LineDimension WHERE GL_JournalLine_ID=" + line.Get_ID();
DataSet ds = DB.ExecuteDataset(sql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int m = 0; m < ds.Tables[0].Rows.Count; m++)
{
DataRow dr = ds.Tables[0].Rows[m];
X_GL_LineDimension lDim = new X_GL_LineDimension(GetCtx(), dr, null);
DocLine docLine = new DocLine(lDim, this);
// -- Source Amounts
decimal cRate = line.GetCurrencyRate();
if (cRate == 0)
{
cRate = 1;
}
decimal amtAcctCr = 0;
decimal amtAcctDr = 0;
//MAcctSchema mSc = new MAcctSchema(GetCtx(), _C_AcctSchema_ID, null);
if (line.GetAmtSourceDr() != 0)
{
amtAcctDr = lDim.GetAmount() * cRate;
docLine.SetAmount(amtAcctDr, 0);
amtAcctDr = Decimal.Round(amtAcctDr, mSc.GetStdPrecision());
}
else
{
amtAcctCr = lDim.GetAmount() * cRate;
docLine.SetAmount(0, amtAcctCr);
amtAcctCr = Decimal.Round(amtAcctCr, mSc.GetStdPrecision());
}
// -- Converted Amounts
// no need to update converted amount here
//docLine.SetConvertedAmt(_C_AcctSchema_ID, amtAcctDr, amtAcctCr);
// -- Account
MAccount account = line.GetAccount();
docLine.SetAccount(account);
// -- Quantity
docLine.SetQty(lDim.GetQty(), false);
// -- Date
docLine.SetDateAcct(journal.GetDateAcct());
// -- User Dimension
docLine = SetUserDimension(lDim, docLine);
// -- Organization of Line was set to Org of Account
list.Add(docLine);
}
}
}
}
// Return Array
int size = list.Count;
DocLine[] dls = new DocLine[size];
dls = list.ToArray();
return(dls);
}
} // doIt
/// <summary>
/// Create GL Journal
/// </summary>
/// <returns>document info</returns>
private String CreateGLJournal()
{
List <X_T_InvoiceGL> list = new List <X_T_InvoiceGL>();
String sql = "SELECT * FROM T_InvoiceGL "
+ "WHERE AD_PInstance_ID=" + GetAD_PInstance_ID()
+ " ORDER BY AD_Org_ID";
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql, null, Get_TrxName());
while (idr.Read())
{
list.Add(new X_T_InvoiceGL(GetCtx(), idr, Get_TrxName()));
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
if (list.Count == 0)
{
return(" - No Records found");
}
//
MAcctSchema aas = MAcctSchema.Get(GetCtx(), _C_AcctSchema_ID);
MAcctSchemaDefault asDefaultAccts = MAcctSchemaDefault.Get(GetCtx(), _C_AcctSchema_ID);
MGLCategory cat = MGLCategory.GetDefaultSystem(GetCtx());
if (cat == null)
{
MDocType docType = MDocType.Get(GetCtx(), _C_DocTypeReval_ID);
cat = MGLCategory.Get(GetCtx(), docType.GetGL_Category_ID());
}
//
MJournalBatch batch = new MJournalBatch(GetCtx(), 0, Get_TrxName());
batch.SetDescription(GetName());
batch.SetC_DocType_ID(_C_DocTypeReval_ID);
batch.SetDateDoc(DateTime.Now);// new Timestamp(System.currentTimeMillis()));
batch.SetDateAcct(_DateReval);
batch.SetC_Currency_ID(aas.GetC_Currency_ID());
if (!batch.Save())
{
return(GetRetrievedError(batch, "Could not create Batch"));
//return " - Could not create Batch";
}
//
MJournal journal = null;
Decimal? drTotal = Env.ZERO;
Decimal? crTotal = Env.ZERO;
int AD_Org_ID = 0;
for (int i = 0; i < list.Count; i++)
{
X_T_InvoiceGL gl = list[i];//.get(i);
if (Env.Signum(gl.GetAmtRevalDrDiff()) == 0 && Env.Signum(gl.GetAmtRevalCrDiff()) == 0)
{
continue;
}
MInvoice invoice = new MInvoice(GetCtx(), gl.GetC_Invoice_ID(), null);
if (invoice.GetC_Currency_ID() == aas.GetC_Currency_ID())
{
continue;
}
//
if (journal == null)
{
journal = new MJournal(batch);
journal.SetC_AcctSchema_ID(aas.GetC_AcctSchema_ID());
journal.SetC_Currency_ID(aas.GetC_Currency_ID());
journal.SetC_ConversionType_ID(_C_ConversionTypeReval_ID);
MOrg org = MOrg.Get(GetCtx(), gl.GetAD_Org_ID());
journal.SetDescription(GetName() + " - " + org.GetName());
journal.SetGL_Category_ID(cat.GetGL_Category_ID());
if (!journal.Save())
{
return(GetRetrievedError(journal, "Could not create Journal"));
//return " - Could not create Journal";
}
}
//
MJournalLine line = new MJournalLine(journal);
line.SetLine((i + 1) * 10);
line.SetDescription(invoice.GetSummary());
//
MFactAcct fa = new MFactAcct(GetCtx(), gl.GetFact_Acct_ID(), null);
line.SetC_ValidCombination_ID(MAccount.Get(fa));
Decimal?dr = gl.GetAmtRevalDrDiff();
Decimal?cr = gl.GetAmtRevalCrDiff();
drTotal = Decimal.Add(drTotal.Value, dr.Value);
crTotal = Decimal.Add(crTotal.Value, cr.Value);
line.SetAmtSourceDr(dr.Value);
line.SetAmtAcctDr(dr.Value);
line.SetAmtSourceCr(cr.Value);
line.SetAmtAcctCr(cr.Value);
line.Save();
//
if (AD_Org_ID == 0) // invoice org id
{
AD_Org_ID = gl.GetAD_Org_ID();
}
// Change in Org
if (AD_Org_ID != gl.GetAD_Org_ID())
{
CreateBalancing(asDefaultAccts, journal, drTotal.Value, crTotal.Value, AD_Org_ID, (i + 1) * 10);
//
AD_Org_ID = gl.GetAD_Org_ID();
drTotal = Env.ZERO;
crTotal = Env.ZERO;
journal = null;
}
}
CreateBalancing(asDefaultAccts, journal, drTotal.Value, crTotal.Value, AD_Org_ID, (list.Count + 1) * 10);
return(" - " + batch.GetDocumentNo() + " #" + list.Count);
} // createGLJournal
/// <summary>
/// Process
/// </summary>
/// <returns></returns>
protected override string DoIt()
{
try
{
MRevenueRecognitionPlan revenueRecognitionPlan = null;
MInvoiceLine invoiceLine = null;
MInvoice invoice = null;
MRevenueRecognition mRevenueRecognition = new MRevenueRecognition(GetCtx(), _RevenueRecognition_ID, Get_Trx());
// Count of previous date's RevenueRecognition_Run whose journal is not created
string sql = "SELECT COUNT(C_RevenueRecognition_Run_ID) FROM C_RevenueRecognition_Run run " +
"INNER JOIN C_RevenueRecognition_Plan pl ON pl.C_RevenueRecognition_Plan_ID = run.C_RevenueRecognition_Plan_ID WHERE ";
if (C_InvoiceLine_ID > 0)
{
sql += "pl.C_invoiceLine_ID = " + C_InvoiceLine_ID + " AND ";
}
sql += "pl.C_RevenueRecognition_ID = " + _RevenueRecognition_ID + " AND NVL(GL_Journal_ID, 0)<=0 AND run.RECOGNITIONDATE < " + GlobalVariable.TO_DATE(DateTime.Now, true);
if (Util.GetValueOfInt(DB.ExecuteScalar(sql)) == 0)
{
MRevenueRecognitionPlan[] revenueRecognitionPlans = MRevenueRecognitionPlan.GetRecognitionPlans(mRevenueRecognition, C_InvoiceLine_ID, _orgId);
journal_ID = new int[revenueRecognitionPlans.Length];
if (ReversalType == "P")
{
for (int i = 0; i < revenueRecognitionPlans.Length; i++)
{
revenueRecognitionPlan = revenueRecognitionPlans[i];
invoiceLine = new MInvoiceLine(GetCtx(), revenueRecognitionPlan.GetC_InvoiceLine_ID(), Get_Trx());
invoice = new MInvoice(GetCtx(), invoiceLine.GetC_Invoice_ID(), Get_Trx());
//get Sum of Amount Whose journal is not yet created
sql = "SELECT SUM(run.Recognizedamt) AS TotalRecognizedAmt FROM C_RevenueRecognition_Run run WHERE " +
"C_RevenueRecognition_Plan_ID = " + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID() + " AND NVL(GL_Journal_ID,0) <= 0";
totalAmt = Util.GetValueOfInt(DB.ExecuteScalar(sql));
if (totalAmt != 0)
{
//if totalAmount is not 0 then only create Journal
if (revenueRecognitionPlan.GetC_AcctSchema_ID() != _AcctSchema_ID || revenueRecognitionPlan.GetC_Currency_ID() != _Currency_ID)
{
if (journal != null)
{
if (DocNo == null)
{
DocNo = journal.GetDocumentNo();
}
else
{
DocNo += "," + journal.GetDocumentNo();
}
journal_ID[i - 1] = journal.GetGL_Journal_ID();
}
journal = new MJournal(GetCtx(), 0, Get_Trx());
journal.SetC_DocType_ID(_DocType);
journal = CreateJournalHDR(revenueRecognitionPlan);
if (journal.Save(Get_TrxName()))
{
_AcctSchema_ID = journal.GetC_AcctSchema_ID();
_Currency_ID = journal.GetC_Currency_ID();
lineno = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(Line), 0)+10 AS DefaultValue FROM GL_JournalLine WHERE GL_Journal_ID=" + journal.GetGL_Journal_ID(), null, invoice.Get_Trx()));
}
else
{
pp = VLogger.RetrieveError();
if (pp != null)
{
errorMsg = pp.GetName();
if (errorMsg == "")
{
errorMsg = pp.GetValue();
}
}
if (errorMsg == "")
{
errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated");
}
Get_TrxName().Rollback();
return(errorMsg);
}
}
revenueRecognitionPlan.SetRecognizedAmt(totalAmt + revenueRecognitionPlan.GetRecognizedAmt());
if (!revenueRecognitionPlan.Save())
{
pp = VLogger.RetrieveError();
if (pp != null)
{
errorMsg = pp.GetName();
if (errorMsg == "")
{
errorMsg = pp.GetValue();
}
}
if (errorMsg == "")
{
errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated");
}
Get_TrxName().Rollback();
return(errorMsg);
}
for (int k = 0; k < 2; k++)
{
journalLine = new MJournalLine(journal);
journalLine = GenerateJounalLine(journal, invoice, invoiceLine, revenueRecognitionPlan, totalAmt, mRevenueRecognition.GetRecognitionType(), k);
if (journalLine.Save(Get_TrxName()))
{
lineno += 10;
}
else
{
pp = VLogger.RetrieveError();
if (pp != null)
{
errorMsg = pp.GetName();
if (errorMsg == "")
{
errorMsg = pp.GetValue();
}
}
if (errorMsg == "")
{
errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated");
}
Get_TrxName().Rollback();
return(errorMsg);
}
}
sql = "UPDATE C_RevenueRecognition_Run set Gl_Journal_ID= " + journal.GetGL_Journal_ID() +
" WHERE C_RevenueRecognition_Plan_ID= " + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID() + "AND NVL(Gl_Journal_ID,0)=0";
int count = DB.ExecuteQuery(sql, null, Get_Trx());
log.Log(Level.INFO, (Msg.GetMsg(GetCtx(), "RevenueRecognitionRunUpdated") + count));
}
}
if (journal != null)
{
if (DocNo == null)
{
DocNo = journal.GetDocumentNo();
}
else
{
DocNo += "," + journal.GetDocumentNo();
}
journal_ID[journal_ID.Length - 1] = journal.GetGL_Journal_ID();
}
}
#region Existing Reversal Type
//else if (ReversalType == "E")
//{
// for (int i = 0; i < revenueRecognitionPlans.Length; i++)
// {
// MRevenueRecognitionPlan revenueRecognitionPlan = revenueRecognitionPlans[i];
// MInvoiceLine invoiceLine = new MInvoiceLine(GetCtx(), revenueRecognitionPlan.GetC_InvoiceLine_ID(), Get_Trx());
// MInvoice invoice = new MInvoice(GetCtx(), invoiceLine.GetC_Invoice_ID(), Get_Trx());
// sql = "Select Distinct round(SUM(recognizedamt),5) as RecognizedAmt from C_RevenueRecognition_Run Where C_RevenueRecognition_Plan_ID = " + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID() + " And NVL(GL_Journal_ID,0) > 0 ";
// DataSet ds = new DataSet();
// ds = DB.ExecuteDataset(sql);
// if (ds != null && ds.Tables[0].Rows.Count > 0)
// {
// decimal Amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["RecognizedAmt"]);
// string sql1 = "Select GL_Journal_ID from C_RevenueRecognition_Run Where C_RevenueRecognition_Plan_ID = " + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID() + " And NVL(GL_Journal_ID,0) > 0 AND RowNum=1";
// int GL_Journal_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql1));
// MJournal journal = new MJournal(GetCtx(), GL_Journal_ID, Get_TrxName());
// log.Info(ToString());
// // Journal
// MJournal reverse = new MJournal(journal);
// reverse.SetDateDoc(DateTime.Now);
// reverse.SetC_Period_ID(journal.GetC_Period_ID());
// int Period_ID = MPeriod.GetC_Period_ID(GetCtx(), DateTime.Now);
// reverse.SetDateAcct(DateTime.Now);
// if (reverse.Save())
// {
// MJournalLine[] journalLines = journal.GetLines(false);
// if (journalLines.Length > 0)
// {
// for (int j = 0; j < journalLines.Length; j++)
// {
// MJournalLine journalLine = journalLines[j];
// MJournalLine newjournalLine = new MJournalLine(GetCtx(), 0, Get_TrxName());
// PO.CopyValues(journalLine, newjournalLine, GetAD_Client_ID(), GetAD_Org_ID());
// newjournalLine.SetGL_Journal_ID(reverse.GetGL_Journal_ID());
// newjournalLine.SetDateAcct(DateTime.Now);
// // Amounts
// if (newjournalLine.GetAmtAcctCr() > 0)
// {
// newjournalLine.SetAmtAcctDr(0);
// newjournalLine.SetAmtSourceDr(0);
// newjournalLine.SetAmtSourceCr(Decimal.Negate(Amt));
// newjournalLine.SetAmtAcctCr(Decimal.Negate(Amt));
// }
// else
// {
// newjournalLine.SetAmtAcctDr(Decimal.Negate(Amt));
// newjournalLine.SetAmtSourceDr(Decimal.Negate(Amt));
// newjournalLine.SetAmtSourceCr(0);
// newjournalLine.SetAmtAcctCr(0);
// }
// newjournalLine.SetIsGenerated(true);
// newjournalLine.SetProcessed(false);
// newjournalLine.Save();
// if (j == 1)
// {
// break;
// }
// }
// }
// }
// if (reverse != null && reverse.GetDocStatus() != "CO")
// {
// reverse.CompleteIt();
// reverse.SetProcessed(true);
// reverse.SetDocStatus("CO");
// reverse.SetDocAction("CL");
// reverse.Save(Get_TrxName());
// if (DocNo == null)
// {
// DocNo = reverse.GetDocumentNo();
// }
// int count = Util.GetValueOfInt(DB.ExecuteQuery("Update C_RevenueRecognition_Run Set GL_Journal_ID=null WHere C_RevenueRecognition_Plan_ID=" + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID()));
// int count1 = Util.GetValueOfInt(DB.ExecuteQuery("Update C_RevenueRecognition_Plan Set RecognizedAmt=RecognizedAmt - " + Amt + " WHere C_RevenueRecognition_Plan_ID=" + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID()));
// }
// }
// }
//}
#endregion
Get_TrxName().Commit();
if (journal_ID != null)
{
for (int i = 0; i < journal_ID.Length; i++)
{
if (journal_ID[i] > 0)
{
string result = RevenueRun.CompleteOrReverse(GetCtx(), journal_ID[i], 169, "CO");
if (!String.IsNullOrEmpty(result))
{
journalIDS += ", " + journal_ID[i] + " " + result;
}
}
}
}
if (DocNo == null)
{
return(Msg.GetMsg(GetCtx(), "FoundNoRevenueRecognitionPlan"));
}
}
else
{
return(Msg.GetMsg(GetCtx(), "NotRecoganized"));
}
}
catch (Exception ex)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "GLJournalnotallocateddueto") + ex.Message);
Get_TrxName().Rollback();
return(ex.Message);
}
if (!String.IsNullOrEmpty(journalIDS))
{
return(Msg.GetMsg(GetCtx(), "GLJournalCreated") + DocNo + ", " + Msg.GetMsg(GetCtx(), "GLJournalNotCompleted") + journalIDS);
}
else
{
return(Msg.GetMsg(GetCtx(), "GLJournalCreated") + DocNo);
}
}
/// <summary>
/// Create Gl Journal Line
/// </summary>
/// <param name="Journal">GL Journal</param>
/// <param name="Invoice">Invoice</param>
/// <param name="InvoiceLine">Invoice Line</param>
/// <param name="RevenueRecognitionPlan">Revenue Recognition Plan</param>
/// <param name="TotalAmt">Sum of RecognizeAmt</param>
/// <param name="RecognitionType">Recognition Type</param>
/// <param name="k">loop variable</param>
/// <returns>>Journal line object</returns>
public MJournalLine GenerateJounalLine(MJournal Journal, MInvoice Invoice, MInvoiceLine InvoiceLine,
MRevenueRecognitionPlan RevenueRecognitionPlan, Decimal TotalAmt, string RecognitionType, int k)
{
int combination_ID = 0;
if (k == 0)
{
combination_ID = RevenueRun.GetCombinationID(InvoiceLine.GetM_Product_ID(), InvoiceLine.GetC_Charge_ID(), journal.GetC_AcctSchema_ID(), Invoice.IsSOTrx(), Invoice.IsReturnTrx(), totalAmt, RevenueRecognitionPlan.GetC_RevenueRecognition_ID());
journalLine.SetLine(lineno);
if (RecognitionType.Equals("E") && TotalAmt > 0)
{
journalLine.SetAmtAcctDr(TotalAmt);
journalLine.SetAmtSourceDr(TotalAmt);
journalLine.SetAmtSourceCr(0);
journalLine.SetAmtAcctCr(0);
}
else if (RecognitionType.Equals("E") && TotalAmt < 0)
{
journalLine.SetAmtAcctCr(Decimal.Negate(TotalAmt));
journalLine.SetAmtSourceCr(Decimal.Negate(TotalAmt));
journalLine.SetAmtSourceDr(0);
journalLine.SetAmtAcctDr(0);
}
else if (RecognitionType.Equals("R") && TotalAmt > 0)
{
journalLine.SetAmtAcctCr(TotalAmt);
journalLine.SetAmtSourceCr(TotalAmt);
journalLine.SetAmtSourceDr(0);
journalLine.SetAmtAcctDr(0);
}
else if (RecognitionType.Equals("R") && TotalAmt < 0)
{
journalLine.SetAmtAcctDr(Decimal.Negate(TotalAmt));
journalLine.SetAmtSourceDr(Decimal.Negate(TotalAmt));
journalLine.SetAmtSourceCr(0);
journalLine.SetAmtAcctCr(0);
}
int account_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Account_ID From C_ValidCombination Where C_ValidCombination_ID=" + combination_ID));
journalLine.Set_ValueNoCheck("Account_ID", account_ID);
journalLine.Set_ValueNoCheck("C_BPartner_ID", Invoice.GetC_BPartner_ID());
journalLine.SetAD_OrgTrx_ID(InvoiceLine.Get_ColumnIndex("AD_OrgTrx_ID") > 0 ? InvoiceLine.GetAD_OrgTrx_ID() : Invoice.GetAD_OrgTrx_ID());
journalLine.Set_ValueNoCheck("C_Project_ID", InvoiceLine.GetC_Project_ID() > 0 ? InvoiceLine.GetC_Project_ID() : Invoice.Get_Value("C_ProjectRef_ID"));
journalLine.Set_ValueNoCheck("C_Campaign_ID", InvoiceLine.Get_ColumnIndex("C_Campaign_ID") > 0 ? InvoiceLine.GetC_Campaign_ID() : Invoice.GetC_Campaign_ID());
journalLine.Set_ValueNoCheck("C_Activity_ID", InvoiceLine.Get_ColumnIndex("C_Activity_ID") > 0 ? InvoiceLine.GetC_Activity_ID() : Invoice.GetC_Activity_ID());
journalLine.Set_ValueNoCheck("M_Product_ID", InvoiceLine.GetM_Product_ID());
}
else
{
combination_ID = RevenueRun.GetCombinationID(0, 0, RevenueRecognitionPlan.GetC_AcctSchema_ID(), Invoice.IsSOTrx(), Invoice.IsReturnTrx(), totalAmt, RevenueRecognitionPlan.GetC_RevenueRecognition_ID());
int account_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Account_ID From C_ValidCombination Where C_ValidCombination_ID=" + combination_ID));
journalLine = RevenueRun.GetOrCreate(journal, journalLine, InvoiceLine.GetM_Product_ID(), InvoiceLine.GetC_Charge_ID(),
InvoiceLine.Get_ColumnIndex("C_Campaign_ID") > 0 ? InvoiceLine.GetC_Campaign_ID() : Invoice.GetC_Campaign_ID(),
account_ID, InvoiceLine.GetC_Project_ID() > 0 ? InvoiceLine.GetC_Project_ID() : Util.GetValueOfInt(Invoice.Get_Value("C_ProjectRef_ID")),
InvoiceLine.Get_ColumnIndex("C_Activity_ID") > 0 ? InvoiceLine.GetC_Activity_ID() : Invoice.GetC_Activity_ID(), Invoice.GetC_BPartner_ID(),
Invoice.GetAD_Org_ID(), InvoiceLine.Get_ColumnIndex("AD_OrgTrx_ID") > 0 ? InvoiceLine.GetAD_OrgTrx_ID() : Invoice.GetAD_OrgTrx_ID());
journalLine.SetLine(lineno);
if (RecognitionType.Equals("E") && TotalAmt > 0)
{
journalLine.SetAmtAcctCr(journalLine.GetAmtAcctCr() + TotalAmt);
journalLine.SetAmtSourceCr(journalLine.GetAmtSourceCr() + TotalAmt);
journalLine.SetAmtSourceDr(0);
journalLine.SetAmtAcctDr(0);
}
else if (RecognitionType.Equals("E") && TotalAmt < 0)
{
journalLine.SetAmtAcctDr(journalLine.GetAmtAcctDr() + Decimal.Negate(TotalAmt));
journalLine.SetAmtSourceDr(journalLine.GetAmtSourceDr() + Decimal.Negate(TotalAmt));
journalLine.SetAmtSourceCr(0);
journalLine.SetAmtAcctCr(0);
}
else if (RecognitionType.Equals("R") && TotalAmt > 0)
{
journalLine.SetAmtAcctDr(journalLine.GetAmtAcctDr() + TotalAmt);
journalLine.SetAmtSourceDr(journalLine.GetAmtSourceDr() + TotalAmt);
journalLine.SetAmtSourceCr(0);
journalLine.SetAmtAcctCr(0);
}
else if (RecognitionType.Equals("R") && TotalAmt < 0)
{
journalLine.SetAmtAcctCr(journalLine.GetAmtAcctCr() + Decimal.Negate(TotalAmt));
journalLine.SetAmtSourceCr(journalLine.GetAmtSourceCr() + Decimal.Negate(TotalAmt));
journalLine.SetAmtSourceDr(0);
journalLine.SetAmtAcctDr(0);
}
}
return(journalLine);
}
} // invalidateIt
/// <summary>
/// Prepare Document
/// </summary>
/// <returns>new status (In Progress or Invalid) </returns>
public String PrepareIt()
{
log.Info(ToString());
m_processMsg = ModelValidationEngine.Get().FireDocValidate(this, ModalValidatorVariables.DOCTIMING_BEFORE_PREPARE);
if (m_processMsg != null)
{
return(DocActionVariables.STATUS_INVALID);
}
MDocType dt = MDocType.Get(GetCtx(), GetC_DocType_ID());
// Get Period
MPeriod period = MPeriod.Get(GetCtx(), GetDateAcct());
if (period == null)
{
log.Warning("No Period for " + GetDateAcct());
m_processMsg = "@PeriodNotFound@";
return(DocActionVariables.STATUS_INVALID);
}
// Standard Period
if (period.GetC_Period_ID() != GetC_Period_ID() &&
period.IsStandardPeriod())
{
m_processMsg = "@PeriodNotValid@";
return(DocActionVariables.STATUS_INVALID);
}
Boolean open = period.IsOpen(dt.GetDocBaseType());
if (!open)
{
log.Warning(period.GetName()
+ ": Not open for " + dt.GetDocBaseType() + " (" + GetDateAcct() + ")");
m_processMsg = "@PeriodClosed@";
return(DocActionVariables.STATUS_INVALID);
}
// JID_0521 - Restrict if debit and credit amount is not equal.-Mohit-12-jun-2019.
if (GetTotalCr() != GetTotalDr())
{
m_processMsg = Msg.GetMsg(GetCtx(), "DBAndCRAmtNotEqual");
return(DocActionVariables.STATUS_INVALID);
}
// Lines
MJournalLine[] lines = GetLines(true);
if (lines.Length == 0)
{
m_processMsg = "@NoLines@";
return(DocActionVariables.STATUS_INVALID);
}
//Manish 18/7/2016 .. Because if line dimention (Amount) column sum is not equals to debit or credit value complete process will not done.
int journalDRAndCR = 0;
string getlinevalues = "SELECT NVL(ElementType,null) AS ElementType,AmtSourceDr,AmtAcctCr,AmtSourceCr,GL_JournalLine_ID FROM GL_JournalLine where GL_Journal_ID=" + Get_Value("GL_Journal_ID");
DataSet dts = DB.ExecuteDataset(getlinevalues, null, null);
if (dts != null && dts.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dts.Tables[0].Rows.Count; i++)
{
journalDRAndCR = 0;
if (dts.Tables[0].Rows[i]["ElementType"].ToString() == "")
{
continue;
}
if (Convert.ToInt32(dts.Tables[0].Rows[i]["AmtSourceDr"]) > 0)
{
journalDRAndCR = Convert.ToInt32(dts.Tables[0].Rows[i]["AmtSourceDr"]);
}
else
{
if (Convert.ToInt32(dts.Tables[0].Rows[i]["AmtSourceCr"]) > 0)
{
journalDRAndCR = Convert.ToInt32(dts.Tables[0].Rows[i]["AmtSourceCr"]);
}
}
string getlineval = "SELECT SUM(amount) FROM gl_linedimension where GL_JournalLine_ID=" + Convert.ToInt32(dts.Tables[0].Rows[i]["GL_JournalLine_ID"]);
int count = Util.GetValueOfInt(DB.ExecuteScalar(getlineval));
if (journalDRAndCR != count)
{
m_processMsg = "@AmountNotMatch@";
return(DocActionVariables.STATUS_INVALID);
}
}
}
// Add up Amounts
Decimal AmtSourceDr = Env.ZERO;
Decimal AmtSourceCr = Env.ZERO;
for (int i = 0; i < lines.Length; i++)
{
MJournalLine line = lines[i];
if (!IsActive())
{
continue;
}
//
if (line.IsDocControlled())
{
m_processMsg = "@DocControlledError@ - @Line@=" + line.GetLine()
+ " - " + line.GetAccountElementValue();
return(DocActionVariables.STATUS_INVALID);
}
//
AmtSourceDr = Decimal.Add(AmtSourceDr, line.GetAmtAcctDr());
AmtSourceCr = Decimal.Add(AmtSourceCr, line.GetAmtAcctCr());
}
SetTotalDr(AmtSourceDr);
SetTotalCr(AmtSourceCr);
// Control Amount
if (Env.ZERO.CompareTo(GetControlAmt()) != 0 &&
GetControlAmt().CompareTo(GetTotalDr()) != 0)
{
m_processMsg = "@ControlAmtError@";
return(DocActionVariables.STATUS_INVALID);
}
// Unbalanced Jornal & Not Suspense
if (AmtSourceDr.CompareTo(AmtSourceCr) != 0)
{
MAcctSchemaGL gl = MAcctSchemaGL.Get(GetCtx(), GetC_AcctSchema_ID());
if (gl == null || !gl.IsUseSuspenseBalancing())
{
m_processMsg = "@UnbalancedJornal@";
return(DocActionVariables.STATUS_INVALID);
}
}
if (!DOCACTION_Complete.Equals(GetDocAction()))
{
SetDocAction(DOCACTION_Complete);
}
m_justPrepared = true;
return(DocActionVariables.STATUS_INPROGRESS);
} // prepareIt
/// <summary>
/// Create GL Journal
/// </summary>
/// <param name="mRevenueRecognition">Revenue Recognition</param>
/// <returns>Message</returns>
public string CreateJournals(MRevenueRecognition mRevenueRecognition)
{
try
{
MRevenueRecognitionRun[] mRevenueRecognitionRuns = null;
MRevenueRecognitionPlan revenueRecognitionPlan = null;
MInvoiceLine invoiceLine = null;
MInvoice invoice = null;
mRevenueRecognitionRuns = MRevenueRecognitionRun.GetRecognitionRuns(mRevenueRecognition, _RecognitionDate, _orgId, false);
journal_ID = new int[mRevenueRecognitionRuns.Length];
if (mRevenueRecognitionRuns.Length > 0)
{
for (int j = 0; j < mRevenueRecognitionRuns.Length; j++)
{
MRevenueRecognitionRun revenueRecognitionRun = mRevenueRecognitionRuns[j];
revenueRecognitionPlan = new MRevenueRecognitionPlan(GetCtx(), revenueRecognitionRun.GetC_RevenueRecognition_Plan_ID(), Get_TrxName());
invoiceLine = new MInvoiceLine(GetCtx(), revenueRecognitionPlan.GetC_InvoiceLine_ID(), Get_TrxName());
invoice = new MInvoice(GetCtx(), invoiceLine.GetC_Invoice_ID(), Get_TrxName());
if (revenueRecognitionPlan.GetC_AcctSchema_ID() != _AcctSchema_ID || revenueRecognitionPlan.GetC_Currency_ID() != _Currency_ID || revenueRecognitionRun.GetRecognitionDate() != _RecognizeDate)
{
if (journal != null)
{
if (DocNo == null)
{
DocNo = journal.GetDocumentNo();
}
else
{
DocNo += ", " + journal.GetDocumentNo();
}
journal_ID[j - 1] = journal.GetGL_Journal_ID();
}
journal = new MJournal(GetCtx(), 0, Get_TrxName());
journal.SetC_DocType_ID(_DocType);
journal = CreateJournalHDR(revenueRecognitionPlan, revenueRecognitionRun, mRevenueRecognition.GetRecognitionFrequency());
if (journal.Save())
{
_AcctSchema_ID = journal.GetC_AcctSchema_ID();
_Currency_ID = journal.GetC_Currency_ID();
_RecognizeDate = revenueRecognitionRun.GetRecognitionDate();
lineno = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(Line), 0)+10 AS DefaultValue FROM GL_JournalLine WHERE GL_Journal_ID=" + journal.GetGL_Journal_ID(), null, invoice.Get_Trx()));
}
else
{
pp = VLogger.RetrieveError();
if (pp != null)
{
errorMsg = pp.GetName();
if (errorMsg == "")
{
errorMsg = pp.GetValue();
}
}
if (errorMsg == "")
{
errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated");
}
Get_TrxName().Rollback();
return(errorMsg);
}
}
for (int k = 0; k < 2; k++)
{
journalLine = new MJournalLine(journal);
journalLine = GenerateJounalLine(journal, invoice, invoiceLine, revenueRecognitionPlan, revenueRecognitionRun, mRevenueRecognition.GetRecognitionType(), k);
if (journalLine.Save())
{
revenueRecognitionRun.SetGL_Journal_ID(journal.GetGL_Journal_ID());
revenueRecognitionRun.Save();
lineno += 10;
}
else
{
pp = VLogger.RetrieveError();
if (pp != null)
{
errorMsg = pp.GetName();
if (errorMsg == "")
{
errorMsg = pp.GetValue();
}
}
if (errorMsg == "")
{
errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated");
}
Get_TrxName().Rollback();
return(errorMsg);
}
}
revenueRecognitionPlan.SetRecognizedAmt(revenueRecognitionRun.GetRecognizedAmt() + revenueRecognitionPlan.GetRecognizedAmt());
if (!revenueRecognitionPlan.Save())
{
pp = VLogger.RetrieveError();
if (pp != null)
{
errorMsg = pp.GetName();
if (errorMsg == "")
{
errorMsg = pp.GetValue();
}
}
if (errorMsg == "")
{
errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated");
}
Get_TrxName().Rollback();
return(errorMsg);
}
}
if (journal != null)
{
if (DocNo == null)
{
DocNo = journal.GetDocumentNo();
}
else
{
DocNo += ", " + journal.GetDocumentNo();
}
journal_ID[journal_ID.Length - 1] = journal.GetGL_Journal_ID();
}
}
}
catch (Exception ex)
{
log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "GLJournalnotallocateddueto") + ex.Message);
Get_TrxName().Rollback();
return(ex.Message);
}
if (DocNo == null)
{
return(Msg.GetMsg(GetCtx(), "FoundNoRevenueRecognitionPlan"));
}
Get_TrxName().Commit();
if (journal_ID != null)
{
for (int i = 0; i < journal_ID.Length; i++)
{
if (journal_ID[i] > 0)
{
string result = CompleteOrReverse(GetCtx(), journal_ID[i], 169, "CO");
if (!String.IsNullOrEmpty(result))
{
journalIDS += ", " + journal_ID[i] + " " + result;
}
}
}
}
if (!String.IsNullOrEmpty(journalIDS))
{
return(Msg.GetMsg(GetCtx(), "GLJournalCreated") + DocNo + " " + Msg.GetMsg(GetCtx(), "GLJournalNotCompleted") + journalIDS);
}
else
{
return(Msg.GetMsg(GetCtx(), "GLJournalCreated") + DocNo);
}
}
} // prepare
/// <summary>
/// Perrform Process.
/// </summary>
/// <returns>message</returns>
protected override String DoIt()
{
log.Info("IsValidateOnly=" + _IsValidateOnly + ", IsImportOnlyNoErrors=" + _IsImportOnlyNoErrors);
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_GLJournal "
+ "WHERE I_IsImported='Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Delete Old Impored =" + no);
}
// Set IsActive, Created/Updated
sql = new StringBuilder("UPDATE I_GLJournal "
+ "SET 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);
// Set Client from Name
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET AD_Client_ID=(SELECT c.AD_Client_ID FROM AD_Client c WHERE c.Value=i.ClientValue) "
+ "WHERE (AD_Client_ID IS NULL OR AD_Client_ID=0) AND ClientValue IS NOT NULL"
+ " AND I_IsImported<>'Y'");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Client from Value=" + no);
// Set Default Client, Doc Org, AcctSchema, DatAcct
sql = new StringBuilder("UPDATE I_GLJournal "
+ "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append("),"
+ " AD_OrgDoc_ID = COALESCE (AD_OrgDoc_ID,").Append(_AD_Org_ID).Append("),");
if (_C_AcctSchema_ID != 0)
{
sql.Append(" C_AcctSchema_ID = COALESCE (C_AcctSchema_ID,").Append(_C_AcctSchema_ID).Append("),");
}
if (_DateAcct != null)
{
sql.Append(" DateAcct = COALESCE (DateAcct,").Append(DataBase.DB.TO_DATE(_DateAcct)).Append("),");
}
sql.Append(" Updated = COALESCE (Updated, SysDate) "
+ "WHERE I_IsImported<>'Y' OR I_IsImported IS NULL");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Client/DocOrg/Default=" + no);
// Error Doc Org
String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly
sql = new StringBuilder("UPDATE I_GLJournal o "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Doc Org, '"
+ "WHERE (AD_OrgDoc_ID IS NULL OR AD_OrgDoc_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 Doc Org=" + no);
}
// Set AcctSchema
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_AcctSchema_ID=(SELECT a.C_AcctSchema_ID FROM C_AcctSchema a"
+ " WHERE i.AcctSchemaName=a.Name AND i.AD_Client_ID=a.AD_Client_ID) "
+ "WHERE C_AcctSchema_ID IS NULL AND AcctSchemaName IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set AcctSchema from Name=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_AcctSchema_ID=(SELECT c.C_AcctSchema1_ID FROM AD_ClientInfo c WHERE c.AD_Client_ID=i.AD_Client_ID) "
+ "WHERE C_AcctSchema_ID IS NULL AND AcctSchemaName IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set AcctSchema from Client=" + no);
// Error AcctSchema
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid AcctSchema, '"
+ "WHERE (C_AcctSchema_ID IS NULL OR C_AcctSchema_ID=0"
+ " OR NOT EXISTS (SELECT * FROM C_AcctSchema a WHERE i.AD_Client_ID=a.AD_Client_ID))"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid AcctSchema=" + no);
}
// Set DateAcct (mandatory)
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET DateAcct=SysDate "
+ "WHERE DateAcct IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set DateAcct=" + no);
// Document Type
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_DocType_ID=(SELECT d.C_DocType_ID FROM C_DocType d"
+ " WHERE d.Name=i.DocTypeName AND d.DocBaseType='GLJ' AND i.AD_Client_ID=d.AD_Client_ID) "
+ "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());
log.Fine("Set DocType=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid DocType, '"
+ "WHERE (C_DocType_ID IS NULL OR C_DocType_ID=0"
+ " OR NOT EXISTS (SELECT * FROM C_DocType d WHERE i.AD_Client_ID=d.AD_Client_ID AND d.DocBaseType='GLJ'))"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid DocType=" + no);
}
// GL Category
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET GL_Category_ID=(SELECT c.GL_Category_ID FROM GL_Category c"
+ " WHERE c.Name=i.CategoryName AND i.AD_Client_ID=c.AD_Client_ID) "
+ "WHERE GL_Category_ID IS NULL AND CategoryName IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set DocType=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Category, '"
+ "WHERE (GL_Category_ID IS NULL OR GL_Category_ID=0)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Category=" + no);
}
// Set Currency
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_Currency_ID=(SELECT c.C_Currency_ID FROM C_Currency c"
+ " WHERE c.ISO_Code=i.ISO_Code AND c.AD_Client_ID IN (0,i.AD_Client_ID)) "
+ "WHERE C_Currency_ID IS NULL AND ISO_Code IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Currency from ISO=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_Currency_ID=(SELECT a.C_Currency_ID FROM C_AcctSchema a"
+ " WHERE a.C_AcctSchema_ID=i.C_AcctSchema_ID AND a.AD_Client_ID=i.AD_Client_ID)"
+ "WHERE C_Currency_ID IS NULL AND ISO_Code IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default Currency=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Currency, '"
+ "WHERE (C_Currency_ID IS NULL OR C_Currency_ID=0)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Currency=" + no);
}
// Set Conversion Type
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET ConversionTypeValue='S' "
+ "WHERE C_ConversionType_ID IS NULL AND ConversionTypeValue IS NULL"
+ " AND I_IsImported='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set CurrencyType Value to Spot =" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_ConversionType_ID=(SELECT c.C_ConversionType_ID FROM C_ConversionType c"
+ " WHERE c.Value=i.ConversionTypeValue AND c.AD_Client_ID IN (0,i.AD_Client_ID)) "
+ "WHERE C_ConversionType_ID IS NULL AND ConversionTypeValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set CurrencyType from Value=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid CurrencyType, '"
+ "WHERE (C_ConversionType_ID IS NULL OR C_ConversionType_ID=0) AND ConversionTypeValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid CurrencyTypeValue=" + no);
}
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No ConversionType, '"
+ "WHERE (C_ConversionType_ID IS NULL OR C_ConversionType_ID=0)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No CourrencyType=" + no);
}
// Set/Overwrite Home Currency Rate
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET CurrencyRate=1"
+ "WHERE EXISTS (SELECT * FROM C_AcctSchema a"
+ " WHERE a.C_AcctSchema_ID=i.C_AcctSchema_ID AND a.C_Currency_ID=i.C_Currency_ID)"
+ " AND C_Currency_ID IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Home CurrencyRate=" + no);
// Set Currency Rate
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET CurrencyRate=(SELECT MAX(r.MultiplyRate) FROM C_Conversion_Rate r, C_AcctSchema s"
+ " WHERE s.C_AcctSchema_ID=i.C_AcctSchema_ID AND s.AD_Client_ID=i.AD_Client_ID"
+ " AND r.C_Currency_ID=i.C_Currency_ID AND r.C_Currency_ID_TO=s.C_Currency_ID"
+ " AND r.AD_Client_ID=i.AD_Client_ID AND r.AD_Org_ID=i.AD_OrgDoc_ID"
+ " AND r.C_ConversionType_ID=i.C_ConversionType_ID"
+ " AND i.DateAcct BETWEEN r.ValidFrom AND r.ValidTo "
// ORDER BY ValidFrom DESC
+ ") WHERE CurrencyRate IS NULL OR CurrencyRate=0 AND C_Currency_ID>0"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Org Rate=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET CurrencyRate=(SELECT MAX(r.MultiplyRate) FROM C_Conversion_Rate r, C_AcctSchema s"
+ " WHERE s.C_AcctSchema_ID=i.C_AcctSchema_ID AND s.AD_Client_ID=i.AD_Client_ID"
+ " AND r.C_Currency_ID=i.C_Currency_ID AND r.C_Currency_ID_TO=s.C_Currency_ID"
+ " AND r.AD_Client_ID=i.AD_Client_ID"
+ " AND r.C_ConversionType_ID=i.C_ConversionType_ID"
+ " AND i.DateAcct BETWEEN r.ValidFrom AND r.ValidTo "
// ORDER BY ValidFrom DESC
+ ") WHERE CurrencyRate IS NULL OR CurrencyRate=0 AND C_Currency_ID>0"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Client Rate=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No Rate, '"
+ "WHERE CurrencyRate IS NULL OR CurrencyRate=0"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No Rate=" + no);
}
// Set Period
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_Period_ID=(SELECT MAX(p.C_Period_ID) FROM C_Period p"
+ " INNER JOIN C_Year y ON (y.C_Year_ID=p.C_Year_ID)"
+ " INNER JOIN AD_ClientInfo c ON (c.C_Calendar_ID=y.C_Calendar_ID)"
+ " WHERE c.AD_Client_ID=i.AD_Client_ID"
+ " AND i.DateAcct BETWEEN p.StartDate AND p.EndDate AND p.PeriodType='S') "
+ "WHERE C_Period_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Period=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Period, '"
+ "WHERE C_Period_ID IS NULL OR C_Period_ID NOt IN "
+ "(SELECT C_Period_ID FROM C_Period p"
+ " INNER JOIN C_Year y ON (y.C_Year_ID=p.C_Year_ID)"
+ " INNER JOIN AD_ClientInfo c ON (c.C_Calendar_ID=y.C_Calendar_ID) "
+ " WHERE c.AD_Client_ID=i.AD_Client_ID"
+ " AND i.DateAcct BETWEEN p.StartDate AND p.EndDate AND p.PeriodType='S')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Period=" + no);
}
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_ErrorMsg=" + ts + "||'WARN=Period Closed, ' "
+ "WHERE C_Period_ID IS NOT NULL AND NOT EXISTS"
+ " (SELECT * FROM C_PeriodControl pc WHERE pc.C_Period_ID=i.C_Period_ID AND DocBaseType='GLJ' AND PeriodStatus='O') "
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Period Closed=" + no);
}
// Posting Type
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET PostingType='A' "
+ "WHERE PostingType IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Actual PostingType=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid PostingType, ' "
+ "WHERE PostingType IS NULL OR NOT EXISTS"
+ " (SELECT * FROM AD_Ref_List r WHERE r.AD_Reference_ID=125 AND i.PostingType=r.Value)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid PostingTypee=" + no);
}
// ** Account Elements (optional) **
// (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0)
// Set Org from Name (* is overwritten and default)
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET AD_Org_ID=(SELECT o.AD_Org_ID FROM AD_Org o"
+ " WHERE o.Value=i.OrgValue AND o.IsSummary='N' AND i.AD_Client_ID=o.AD_Client_ID) "
+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0) AND OrgValue IS NOT NULL"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Org from Value=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET AD_Org_ID=AD_OrgDoc_ID "
+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0) AND OrgValue IS NULL AND AD_OrgDoc_ID IS NOT NULL AND AD_OrgDoc_ID<>0"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Org from Doc Org=" + no);
// Error Org
sql = new StringBuilder("UPDATE I_GLJournal 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 (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Org=" + no);
}
// Set Account
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET Account_ID=(SELECT MAX(ev.C_ElementValue_ID) FROM C_ElementValue ev"
+ " INNER JOIN C_Element e ON (e.C_Element_ID=ev.C_Element_ID)"
+ " INNER JOIN C_AcctSchema_Element ase ON (e.C_Element_ID=ase.C_Element_ID AND ase.ElementType='AC')"
+ " WHERE ev.Value=i.AccountValue AND ev.IsSummary='N'"
+ " AND i.C_AcctSchema_ID=ase.C_AcctSchema_ID AND i.AD_Client_ID=ev.AD_Client_ID) "
+ "WHERE Account_ID IS NULL AND AccountValue IS NOT NULL"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Account from Value=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Account, '"
+ "WHERE (Account_ID IS NULL OR Account_ID=0)"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Account=" + no);
}
// Set BPartner
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_BPartner_ID=(SELECT bp.C_BPartner_ID FROM C_BPartner bp"
+ " WHERE bp.Value=i.BPartnerValue AND bp.IsSummary='N' AND i.AD_Client_ID=bp.AD_Client_ID) "
+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BPartner from Value=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid BPartner, '"
+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid BPartner=" + no);
}
// Set Product
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET M_Product_ID=(SELECT MAX(p.M_Product_ID) FROM M_Product p"
+ " WHERE (p.Value=i.ProductValue OR p.UPC=i.UPC OR p.SKU=i.SKU)"
+ " AND p.IsSummary='N' AND i.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) 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_GLJournal i "
+ "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 (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Product=" + no);
}
// Set Project
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET C_Project_ID=(SELECT p.C_Project_ID FROM C_Project p"
+ " WHERE p.Value=i.ProjectValue AND p.IsSummary='N' AND i.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE C_Project_ID IS NULL AND ProjectValue IS NOT NULL"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Project from Value=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Project, '"
+ "WHERE C_Project_ID IS NULL AND ProjectValue IS NOT NULL"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Project=" + no);
}
// Set TrxOrg
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET AD_OrgTrx_ID=(SELECT o.AD_Org_ID FROM AD_Org o"
+ " WHERE o.Value=i.OrgValue AND o.IsSummary='N' AND i.AD_Client_ID=o.AD_Client_ID) "
+ "WHERE AD_OrgTrx_ID IS NULL AND OrgTrxValue IS NOT NULL"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set OrgTrx from Value=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid OrgTrx, '"
+ "WHERE AD_OrgTrx_ID IS NULL AND OrgTrxValue IS NOT NULL"
+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid OrgTrx=" + no);
}
// Source Amounts
sql = new StringBuilder("UPDATE I_GLJournal "
+ "SET AmtSourceDr = 0 "
+ "WHERE AmtSourceDr IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set 0 Source Dr=" + no);
sql = new StringBuilder("UPDATE I_GLJournal "
+ "SET AmtSourceCr = 0 "
+ "WHERE AmtSourceCr IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set 0 Source Cr=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_ErrorMsg=" + ts + "||'WARN=Zero Source Balance, ' "
+ "WHERE (AmtSourceDr-AmtSourceCr)=0"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Zero Source Balance=" + no);
}
// Accounted Amounts (Only if No Error)
sql = new StringBuilder("UPDATE I_GLJournal "
+ "SET AmtAcctDr = ROUND(AmtSourceDr * CurrencyRate, 2) " // HARDCODED rounding
+ "WHERE AmtAcctDr IS NULL OR AmtAcctDr=0"
+ " AND I_IsImported='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Calculate Acct Dr=" + no);
sql = new StringBuilder("UPDATE I_GLJournal "
+ "SET AmtAcctCr = ROUND(AmtSourceCr * CurrencyRate, 2) "
+ "WHERE AmtAcctCr IS NULL OR AmtAcctCr=0"
+ " AND I_IsImported='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Calculate Acct Cr=" + no);
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_ErrorMsg=" + ts + "||'WARN=Zero Acct Balance, ' "
+ "WHERE (AmtSourceDr-AmtSourceCr)<>0 AND (AmtAcctDr-AmtAcctCr)=0"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Zero Acct Balance=" + no);
}
sql = new StringBuilder("UPDATE I_GLJournal i "
+ "SET I_ErrorMsg=" + ts + "||'WARN=Check Acct Balance, ' "
+ "WHERE ABS(AmtAcctDr-AmtAcctCr)>100000000" // 100 mio
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Chack Acct Balance=" + no);
}
/*********************************************************************/
// Get Balance
sql = new StringBuilder("SELECT SUM(AmtSourceDr)-SUM(AmtSourceCr), SUM(AmtAcctDr)-SUM(AmtAcctCr) "
+ "FROM I_GLJournal "
+ "WHERE I_IsImported='N'").Append(clientCheck);
IDataReader idr = null;
try
{
//pstmt = DataBase.prepareStatement (sql.ToString(), get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
if (idr.Read())
{
Decimal?source = Utility.Util.GetValueOfDecimal(idr[0]); // rs.getBigDecimal(1);
Decimal?acct = Utility.Util.GetValueOfDecimal(idr[1]); // rs.getBigDecimal(2);
if (Env.Signum(source.Value) == 0 &&
acct != null && Env.Signum(acct.Value) == 0)
{
log.Info("Import Balance = 0");
}
else
{
log.Warning("Balance Source=" + source.Value + ", Acct=" + acct.Value);
}
if (source != null)
{
AddLog(0, null, source, "@AmtSourceDr@ - @AmtSourceCr@");
}
if (acct != null)
{
AddLog(0, null, acct, "@AmtAcctDr@ - @AmtAcctCr@");
}
}
idr.Close();
}
catch (Exception ex)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql.ToString(), ex);
}
// Count Errors
int errors = DataBase.DB.GetSQLValue(Get_TrxName(),
"SELECT COUNT(*) FROM I_GLJournal WHERE I_IsImported NOT IN ('Y','N')" + clientCheck);
if (errors != 0)
{
if (_IsValidateOnly || _IsImportOnlyNoErrors)
{
throw new Exception("@Errors@=" + errors);
}
}
else if (_IsValidateOnly)
{
return("@Errors@=" + errors);
}
log.Info("Validation Errors=" + errors);
Commit();
/*********************************************************************/
int noInsert = 0;
int noInsertJournal = 0;
int noInsertLine = 0;
MJournalBatch batch = null; // Change Batch per Batch DocumentNo
String BatchDocumentNo = "";
MJournal journal = null;
String JournalDocumentNo = "";
DateTime? DateAcct = null;
// Go through Journal Records
sql = new StringBuilder("SELECT * FROM I_GLJournal "
+ "WHERE I_IsImported='N'").Append(clientCheck)
.Append(" ORDER BY COALESCE(BatchDocumentNo, TO_NCHAR(I_GLJournal_ID)), COALESCE(JournalDocumentNo, TO_NCHAR(I_GLJournal_ID)), C_AcctSchema_ID, PostingType, C_DocType_ID, GL_Category_ID, C_Currency_ID, TRUNC(DateAcct,'DD'), Line, I_GLJournal_ID");
try
{
//pstmt = DataBase.prepareStatement (sql.ToString (), get_TrxName());
//ResultSet rs = pstmt.executeQuery ();
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
X_I_GLJournal imp = new X_I_GLJournal(GetCtx(), idr, Get_TrxName());
// New Batch if Batch Document No changes
String impBatchDocumentNo = imp.GetBatchDocumentNo();
if (impBatchDocumentNo == null)
{
impBatchDocumentNo = "";
}
if (batch == null ||
imp.IsCreateNewBatch() ||
journal.GetC_AcctSchema_ID() != imp.GetC_AcctSchema_ID() ||
!BatchDocumentNo.Equals(impBatchDocumentNo))
{
BatchDocumentNo = impBatchDocumentNo; // cannot compare real DocumentNo
batch = new MJournalBatch(GetCtx(), 0, null);
batch.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_OrgDoc_ID());
if (imp.GetBatchDocumentNo() != null &&
imp.GetBatchDocumentNo().Length > 0)
{
batch.SetDocumentNo(imp.GetBatchDocumentNo());
}
batch.SetC_DocType_ID(imp.GetC_DocType_ID());
batch.SetPostingType(imp.GetPostingType());
String description = imp.GetBatchDescription();
if (description == null || description.Length == 0)
{
description = "*Import-";
}
else
{
description += " *Import-";
}
description += DateTime.Now; //new Timestamp(System.currentTimeMillis());
batch.SetDescription(description);
if (!batch.Save())
{
log.Log(Level.SEVERE, "Batch not saved");
Exception ex = VLogger.RetrieveException();
if (ex != null)
{
AddLog(0, null, null, ex.Message);
if (idr != null)
{
idr.Close();
}
throw ex;
}
break;
}
noInsert++;
journal = null;
}
// Journal
String impJournalDocumentNo = imp.GetJournalDocumentNo();
if (impJournalDocumentNo == null)
{
impJournalDocumentNo = "";
}
DateTime?impDateAcct = TimeUtil.GetDay(imp.GetDateAcct());
if (journal == null ||
imp.IsCreateNewJournal() ||
!JournalDocumentNo.Equals(impJournalDocumentNo) ||
journal.GetC_DocType_ID() != imp.GetC_DocType_ID() ||
journal.GetGL_Category_ID() != imp.GetGL_Category_ID() ||
!journal.GetPostingType().Equals(imp.GetPostingType()) ||
journal.GetC_Currency_ID() != imp.GetC_Currency_ID() ||
!impDateAcct.Equals(DateAcct)
)
{
JournalDocumentNo = impJournalDocumentNo; // cannot compare real DocumentNo
DateAcct = impDateAcct.Value;
journal = new MJournal(GetCtx(), 0, Get_TrxName());
journal.SetGL_JournalBatch_ID(batch.GetGL_JournalBatch_ID());
journal.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_OrgDoc_ID());
//
String description = imp.GetBatchDescription();
if (description == null || description.Length == 0)
{
description = "(Import)";
}
journal.SetDescription(description);
if (imp.GetJournalDocumentNo() != null && imp.GetJournalDocumentNo().Length > 0)
{
journal.SetDocumentNo(imp.GetJournalDocumentNo());
}
//
journal.SetC_AcctSchema_ID(imp.GetC_AcctSchema_ID());
journal.SetC_DocType_ID(imp.GetC_DocType_ID());
journal.SetGL_Category_ID(imp.GetGL_Category_ID());
journal.SetPostingType(imp.GetPostingType());
journal.SetGL_Budget_ID(imp.GetGL_Budget_ID());
//
journal.SetCurrency(imp.GetC_Currency_ID(), imp.GetC_ConversionType_ID(), imp.GetCurrencyRate());
//
journal.SetC_Period_ID(imp.GetC_Period_ID());
journal.SetDateAcct(imp.GetDateAcct()); // sets Period if not defined
journal.SetDateDoc(imp.GetDateAcct());
//
if (!journal.Save())
{
log.Log(Level.SEVERE, "Journal not saved");
Exception ex = VLogger.RetrieveException();
if (ex != null)
{
AddLog(0, null, null, ex.Message);
if (idr != null)
{
idr.Close();
}
throw ex;
}
break;
}
noInsertJournal++;
}
// Lines
MJournalLine line = new MJournalLine(journal);
//
line.SetDescription(imp.GetDescription());
line.SetCurrency(imp.GetC_Currency_ID(), imp.GetC_ConversionType_ID(), imp.GetCurrencyRate());
// Set/Get Account Combination
if (imp.GetC_ValidCombination_ID() == 0)
{
MAccount acct = MAccount.Get(GetCtx(), imp.GetAD_Client_ID(), imp.GetAD_Org_ID(),
imp.GetC_AcctSchema_ID(), imp.GetAccount_ID(), 0,
imp.GetM_Product_ID(), imp.GetC_BPartner_ID(), imp.GetAD_OrgTrx_ID(),
imp.GetC_LocFrom_ID(), imp.GetC_LocTo_ID(), imp.GetC_SalesRegion_ID(),
imp.GetC_Project_ID(), imp.GetC_Campaign_ID(), imp.GetC_Activity_ID(),
imp.GetUser1_ID(), imp.GetUser2_ID(), 0, 0);
if (acct != null && acct.Get_ID() == 0)
{
acct.Save();
}
if (acct == null || acct.Get_ID() == 0)
{
imp.SetI_ErrorMsg("ERROR creating Account");
imp.SetI_IsImported(X_I_GLJournal.I_ISIMPORTED_No);
imp.Save();
continue;
}
else
{
line.SetC_ValidCombination_ID(acct.Get_ID());
imp.SetC_ValidCombination_ID(acct.Get_ID());
}
}
else
{
line.SetC_ValidCombination_ID(imp.GetC_ValidCombination_ID());
}
//
line.SetLine(imp.GetLine());
line.SetAmtSourceCr(imp.GetAmtSourceCr());
line.SetAmtSourceDr(imp.GetAmtSourceDr());
line.SetAmtAcct(imp.GetAmtAcctDr(), imp.GetAmtAcctCr()); // only if not 0
line.SetDateAcct(imp.GetDateAcct());
//
line.SetC_UOM_ID(imp.GetC_UOM_ID());
line.SetQty(imp.GetQty());
//
if (line.Save())
{
imp.SetGL_JournalBatch_ID(batch.GetGL_JournalBatch_ID());
imp.SetGL_Journal_ID(journal.GetGL_Journal_ID());
imp.SetGL_JournalLine_ID(line.GetGL_JournalLine_ID());
imp.SetI_IsImported(X_I_GLJournal.I_ISIMPORTED_Yes);
imp.SetProcessed(true);
if (imp.Save())
{
noInsertLine++;
}
}
} // while records
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, "", e);
}
// clean up
// Set Error to indicator to not imported
sql = new StringBuilder("UPDATE I_GLJournal "
+ "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), "@GL_JournalBatch_ID@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertJournal), "@GL_Journal_ID@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@GL_JournalLine_ID@: @Inserted@");
return("");
} // doIt
