public static void CreateDocumentTypes(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trxName)
{
_log.Info("AD_Client_ID=" + AD_Client_ID);
String sql = "SELECT rl.Value, rl.Name "
+ "FROM AD_Ref_List rl "
+ "WHERE rl.AD_Reference_ID=183"
+ " AND rl.IsActive='Y' AND NOT EXISTS "
+ " (SELECT * FROM C_DocType dt WHERE dt.AD_Client_ID='" + AD_Client_ID + "' AND rl.Value=dt.DocBaseType)";
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql, null);
while (idr.Read())
{
String name = idr[1].ToString();
String value = idr[0].ToString();
_log.Config(name + "=" + value);
MDocType dt = new MDocType(ctx, value, name, trxName);
if (dt.Save())
{
if (sp != null)
{
sp.AddLog(0, null, null, name);
}
else
{
_log.Fine(name);
}
}
else
{
if (sp != null)
{
sp.AddLog(0, null, null, "Not created: " + name);
}
else
{
_log.Warning("Not created: " + name);
}
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
} // createDocumentTypes
/**
* Delete Balances
* @param AD_Client_ID client
* @param C_AcctSchema_ID accounting schema 0 for all
* @param dateFrom null for all or first date to delete
* @param trx transaction
* @param svrPrc optional server process
* @return Message to be translated
*/
public static String DeleteBalance(int AD_Client_ID, int C_AcctSchema_ID,
DateTime?dateFrom, Trx trxp, int Fact_Accumulation_ID, SvrProcess svrPrc)
{
Trx trx = trxp;
//List<Object> param = new List<Object>();
StringBuilder sql = new StringBuilder("DELETE FROM Fact_Acct_Balance WHERE AD_Client_ID=" + AD_Client_ID);
//param.add(new Integer(AD_Client_ID));
if (C_AcctSchema_ID != 0)
{
sql.Append(" AND C_AcctSchema_ID=" + C_AcctSchema_ID);
// param.add(new Integer(C_AcctSchema_ID));
}
if (dateFrom != null)
{
//SimpleDateFormat df = new SimpleDateFormat();
//String finalDate = df.Format(dateFrom);
//sql.Append(" AND DateAcct>= TO_DATE('" + finalDate + "','DD-MM-YYYY')");
sql.Append(" AND DateAcct >= " + DB.TO_DATE(dateFrom));
}
if (Fact_Accumulation_ID != 0)
{
sql.Append(" AND Fact_Accumulation_ID = " + Fact_Accumulation_ID);
//param.add(new Integer(Fact_Accumulation_ID));
}
//
int no = DB.ExecuteQuery(sql.ToString(), null, trx);
String msg = "@Deleted@=" + no;
_log.Info("C_AcctSchema_ID=" + C_AcctSchema_ID
+ ",DateAcct=" + dateFrom
+ " #=" + no);
if (svrPrc != null)
{
svrPrc.AddLog(0, dateFrom, new Decimal(no), "Deleted");
}
trx.Commit();
//
return(msg);
}
} // validate
/// <summary>
/// Check existence of Table Sequences.
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckTableSequences(Ctx ctx, SvrProcess sp)
{
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
String sql = "SELECT TableName "
+ "FROM AD_Table t "
+ "WHERE IsActive='Y' AND IsView='N'"
+ " AND NOT EXISTS (SELECT * FROM AD_Sequence s "
+ "WHERE UPPER(s.Name)=UPPER(t.TableName) AND s.IsTableID='Y')";
IDataReader idr = null;
try
{
//pstmt = DataBase.prepareStatement(sql, trxName);
idr = DataBase.DB.ExecuteReader(sql, null, trxName);
while (idr.Read())
{
String tableName = Utility.Util.GetValueOfString(idr[0]);// rs.getString(1);
if (MSequence.CreateTableSequence(ctx, tableName, trxName))
{
if (sp != null)
{
sp.AddLog(0, null, null, tableName);
}
else
{
_log.Fine(tableName);
}
}
else
{
idr.Close();
throw new Exception("Error creating Table Sequence for " + tableName);
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
// Sync Table Name case
//jz replace s with AD_Sequence
sql = "UPDATE AD_Sequence "
+ "SET Name = (SELECT TableName FROM AD_Table t "
+ "WHERE t.IsView='N' AND UPPER(AD_Sequence.Name)=UPPER(t.TableName)) "
+ "WHERE AD_Sequence.IsTableID='Y'"
+ " AND EXISTS (SELECT * FROM AD_Table t "
+ "WHERE t.IsActive='Y' AND t.IsView='N'"
+ " AND UPPER(AD_Sequence.Name)=UPPER(t.TableName) AND AD_Sequence.Name<>t.TableName)";
int no = DataBase.DB.ExecuteQuery(sql, null, trxName);// DataBase.executeUpdate(sql, trxName);
if (no > 0)
{
if (sp != null)
{
sp.AddLog(0, null, null, "SyncName #" + no);
}
else
{
_log.Fine("Sync #" + no);
}
}
if (no >= 0)
{
return;
}
/** Find Duplicates */
sql = "SELECT TableName, s.Name "
+ "FROM AD_Table t, AD_Sequence s "
+ "WHERE t.IsActive='Y' AND t.IsView='N'"
+ " AND UPPER(s.Name)=UPPER(t.TableName) AND s.Name<>t.TableName";
//
try
{
//pstmt = DataBase.prepareStatement (sql, null);
idr = DataBase.DB.ExecuteReader(sql, null, trxName);
while (idr.Read())
{
String TableName = Utility.Util.GetValueOfString(idr[0]);// rs.getString(1);
String SeqName = Utility.Util.GetValueOfString(idr[1]);
sp.AddLog(0, null, null, "ERROR: TableName=" + TableName + " - Sequence=" + SeqName);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
} // checkTableSequences
} // checkTableSequences
/// <summary>
/// Check Table Sequence ID values
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckTableID(Ctx ctx, SvrProcess sp)
{
if (MSysConfig.IsNativeSequence(false))
{
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
DataSet ds = null;
int counter = 0;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Get Max value from Table_ID or AD_Sequence and update.
sql = @"Update AD_Sequence set updatedby=" + ctx.GetAD_User_ID() + ", updated=getdate(), CurrentNext=(SELECT max(maximum) FROM ( SELECT max(" + ds.Tables[0].Rows[i]["Name"].ToString() + @"_ID)+1 as maximum from " + ds.Tables[0].Rows[i]["Name"].ToString() +
@" Union
SELECT currentnext AS maximum FROM AD_Sequence WHERE Name = '" + ds.Tables[0].Rows[i]["Name"].ToString() + "') ";
if (DB.IsPostgreSQL())
{
sql += " foo ";
}
sql += ") WHERE Name = '" + ds.Tables[0].Rows[i]["Name"].ToString() + "'";
int curVal = DB.ExecuteQuery(sql, null, trxName);
if (curVal > 0)
{
sp.AddLog(0, null, null, "Sequence Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString());
counter++;
}
else
{
_log.Severe("Sequence Not Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString());
}
}
}
catch (Exception ex)
{
_log.Log(Level.SEVERE, sql, ex);
}
}
else
{
int IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT IDRangeEnd FROM AD_System");
if (IDRangeEnd <= 0)
{
IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT MIN(IDRangeStart)-1 FROM AD_Replication");
}
_log.Info("IDRangeEnd = " + IDRangeEnd);
//
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
int counter = 0;
//IDataReader idr = null;
DataSet ds = null;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
//pstmt = DataBase.prepareStatement(sql, trxName);
//idr = DataBase.DB.ExecuteReader(sql, null, trxName);
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
//while (idr.Read())
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName);
int old = seq.GetCurrentNext();
int oldSys = seq.GetCurrentNextSys();
if (seq.ValidateTableIDValue())
{
if (seq.GetCurrentNext() != old)
{
String msg = seq.GetName() + " ID "
+ old + " -> " + seq.GetCurrentNext();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.GetCurrentNextSys() != oldSys)
{
String msg = seq.GetName() + " Sys "
+ oldSys + " -> " + seq.GetCurrentNextSys();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.Save())
{
counter++;
}
else
{
_log.Severe("Not updated: " + seq);
}
}
// else if (CLogMgt.isLevel(6))
// log.fine("OK - " + tableName);
}
// idr.Close();
}
catch (Exception e)
{
//if (idr != null)
//{
// idr.Close();
//}
_log.Log(Level.SEVERE, sql, e);
}
_log.Fine("#" + counter);
}
} // checkTableID
} // checkTableSequences
/// <summary>
/// Check Table Sequence ID values
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckTableID(Ctx ctx, SvrProcess sp)
{
if (MSysConfig.IsNativeSequence(false))
{
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
DataSet ds = null;
int counter = 0;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName);
int curVal = DB.GetSQLValue(trxName, "select " + ds.Tables[0].Rows[i]["Name"].ToString() + "_SEQ.currval+1 from DUAL");
seq.SetCurrentNext(curVal);
if (seq.Save())
{
sp.AddLog(0, null, null, "Sequence Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString());
counter++;
}
else
{
_log.Severe("Sequence Not Updated For :" + ds.Tables[0].Rows[i]["Name"].ToString());
}
}
}
catch (Exception ex)
{
_log.Log(Level.SEVERE, sql, ex);
}
}
else
{
int IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT IDRangeEnd FROM AD_System");
if (IDRangeEnd <= 0)
{
IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT MIN(IDRangeStart)-1 FROM AD_Replication");
}
_log.Info("IDRangeEnd = " + IDRangeEnd);
//
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
int counter = 0;
//IDataReader idr = null;
DataSet ds = null;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
//pstmt = DataBase.prepareStatement(sql, trxName);
//idr = DataBase.DB.ExecuteReader(sql, null, trxName);
ds = DataBase.DB.ExecuteDataset(sql, null, trxName);
//while (idr.Read())
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MSequence seq = new MSequence(ctx, ds.Tables[0].Rows[i], trxName);
int old = seq.GetCurrentNext();
int oldSys = seq.GetCurrentNextSys();
if (seq.ValidateTableIDValue())
{
if (seq.GetCurrentNext() != old)
{
String msg = seq.GetName() + " ID "
+ old + " -> " + seq.GetCurrentNext();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.GetCurrentNextSys() != oldSys)
{
String msg = seq.GetName() + " Sys "
+ oldSys + " -> " + seq.GetCurrentNextSys();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.Save())
{
counter++;
}
else
{
_log.Severe("Not updated: " + seq);
}
}
// else if (CLogMgt.isLevel(6))
// log.fine("OK - " + tableName);
}
// idr.Close();
}
catch (Exception e)
{
//if (idr != null)
//{
// idr.Close();
//}
_log.Log(Level.SEVERE, sql, e);
}
_log.Fine("#" + counter);
}
} // checkTableID
} // checkTableSequences
/// <summary>
/// Check Table Sequence ID values
/// </summary>
/// <param name="ctx">context</param>
/// <param name="sp">server process or null</param>
private static void CheckTableID(Ctx ctx, SvrProcess sp)
{
int IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT IDRangeEnd FROM AD_System");
if (IDRangeEnd <= 0)
{
IDRangeEnd = DataBase.DB.GetSQLValue(null,
"SELECT MIN(IDRangeStart)-1 FROM AD_Replication");
}
_log.Info("IDRangeEnd = " + IDRangeEnd);
//
String sql = "SELECT * FROM AD_Sequence "
+ "WHERE IsTableID='Y' "
+ "ORDER BY Name";
int counter = 0;
IDataReader idr = null;
Trx trxName = null;
if (sp != null)
{
trxName = sp.Get_Trx();
}
try
{
//pstmt = DataBase.prepareStatement(sql, trxName);
idr = DataBase.DB.ExecuteReader(sql, null, trxName);
while (idr.Read())
{
MSequence seq = new MSequence(ctx, idr, trxName);
int old = seq.GetCurrentNext();
int oldSys = seq.GetCurrentNextSys();
if (seq.ValidateTableIDValue())
{
if (seq.GetCurrentNext() != old)
{
String msg = seq.GetName() + " ID "
+ old + " -> " + seq.GetCurrentNext();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.GetCurrentNextSys() != oldSys)
{
String msg = seq.GetName() + " Sys "
+ oldSys + " -> " + seq.GetCurrentNextSys();
if (sp != null)
{
sp.AddLog(0, null, null, msg);
}
else
{
_log.Fine(msg);
}
}
if (seq.Save())
{
counter++;
}
else
{
_log.Severe("Not updated: " + seq);
}
}
// else if (CLogMgt.isLevel(6))
// log.fine("OK - " + tableName);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
_log.Fine("#" + counter);
} // checkTableID
} // createDocumentTypes
/// <summary>
///
/// </summary>
/// <param name="ctx"></param>
/// <param name="AD_Client_ID"></param>
/// <param name="sp"></param>
/// <param name="trxName"></param>
public static void CreatePeriodControls(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trxName)
{
_log.Info("AD_Client_ID=" + AD_Client_ID);
// Delete Duplicates
//jz remove correlation ID String sql = "DELETE FROM C_PeriodControl pc1 "
String sql = "DELETE FROM C_PeriodControl "
+ "WHERE (C_Period_ID, DocBaseType) IN "
+ "(SELECT C_Period_ID, DocBaseType "
+ "FROM C_PeriodControl pc2 "
+ "GROUP BY C_Period_ID, DocBaseType "
+ "HAVING COUNT(*) > 1)"
+ " AND C_PeriodControl_ID NOT IN "
+ "(SELECT MIN(C_PeriodControl_ID) "
+ "FROM C_PeriodControl pc3 "
+ "GROUP BY C_Period_ID, DocBaseType)";
int no = DataBase.DB.ExecuteQuery(sql, null, trxName);
_log.Info("Duplicates deleted #" + no);
// Insert Missing
sql = "SELECT DISTINCT p.AD_Client_ID, p.AD_Org_ID, p.C_Period_ID, dt.DocBaseType "
+ "FROM C_Period p"
+ " FULL JOIN C_DocType dt ON (p.AD_Client_ID=dt.AD_Client_ID) "
+ "WHERE p.AD_Client_ID='" + AD_Client_ID + "'"
+ " AND NOT EXISTS"
+ " (SELECT * FROM C_PeriodControl pc "
+ "WHERE pc.C_Period_ID=p.C_Period_ID AND pc.DocBaseType=dt.DocBaseType)";
IDataReader idr = null;
int counter = 0;
try
{
idr = DataBase.DB.ExecuteReader(sql, null, trxName);
while (idr.Read())
{
int Client_ID = Utility.Util.GetValueOfInt(idr[0].ToString());
int Org_ID = Utility.Util.GetValueOfInt(idr[1].ToString()); // Get Organization from Period
int C_Period_ID = Utility.Util.GetValueOfInt(idr[2].ToString());
String DocBaseType = idr[3].ToString();
_log.Config("AD_Client_ID=" + Client_ID
+ ", C_Period_ID=" + C_Period_ID + ", DocBaseType=" + DocBaseType);
//
MPeriodControl pc = new MPeriodControl(ctx, Client_ID, C_Period_ID, DocBaseType, trxName);
pc.SetAD_Org_ID(Org_ID); // Set Organization of Period, on Period Control.
if (pc.Save())
{
counter++;
_log.Fine(pc.ToString());
}
else
{
_log.Warning("Not saved: " + pc);
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
_log.Log(Level.SEVERE, sql, e);
}
if (sp != null)
{
sp.AddLog(0, null, (Decimal)counter, "@C_PeriodControl_ID@ @Created@");
}
_log.Info("Inserted #" + counter);
} // createPeriodControls
/**
* Update / Create Balances.
* Called from FinReport, FactAcctReset (indirect)
* @param AD_Client_ID client
* @param C_AcctSchema_ID accounting schema 0 for all
* @param deleteFirst delete balances first
* @param dateFrom null for all or first date to delete/calculate
* @param trx transaction
* @param svrPrc optional server process
* @return Message to be translated
*/
public static String UpdateBalance(Ctx ctx, int C_AcctSchema_ID,
DateTime?dateFrom, Trx trxp, int Fact_Accumulation_ID,
SvrProcess svrPrc)
{
Trx trx = trxp;
_log.Info("C_AcctSchema_ID=" + C_AcctSchema_ID
+ "DateFrom=" + dateFrom);
long start = CommonFunctions.CurrentTimeMillis();
//List<Object> param = new List<Object>();
List <MFactAccumulation> accums = null;
int no = 0;
if (Fact_Accumulation_ID == 0)
{
accums = MFactAccumulation.GetAll(ctx, C_AcctSchema_ID);
if (accums.Count == 0)
{
// Create a Balance aggregation of type Daily.
MFactAccumulation defaultAccum = new MFactAccumulation(ctx, 0, trx);
defaultAccum.SetAD_Client_ID(ctx.GetAD_Client_ID());
defaultAccum.SetAD_Org_ID(ctx.GetAD_Org_ID());
defaultAccum.SetC_AcctSchema_ID(C_AcctSchema_ID);
defaultAccum.SetBALANCEACCUMULATION(X_Fact_Accumulation.BALANCEACCUMULATION_Daily);
defaultAccum.SetIsActive(true);
defaultAccum.SetIsDefault(true);
defaultAccum.SetISACTIVITY(true);
defaultAccum.SetISBUDGET(true);
defaultAccum.SetISBUSINESSPARTNER(true);
defaultAccum.SetISCAMPAIGN(true);
defaultAccum.SetISLOCATIONFROM(true);
defaultAccum.SetISLOCATIONTO(true);
defaultAccum.SetISPRODUCT(true);
defaultAccum.SetISPROJECT(true);
defaultAccum.SetISSALESREGION(true);
defaultAccum.SetISUSERLIST1(true);
defaultAccum.SetISUSERLIST2(true);
defaultAccum.SetISUSERELEMENT1(true);
defaultAccum.SetISUSERELEMENT2(true);
if (!defaultAccum.Save(trx))
{
_log.Log(Level.SEVERE, "Unable to create Default Balance Aggregation");
return("Unable to create Default Balance Aggregation");
}
else
{
accums.Add(defaultAccum);
}
}
}
else
{
MFactAccumulation selectAccum = new MFactAccumulation(ctx, Fact_Accumulation_ID, trx);
accums = new List <MFactAccumulation>();
accums.Add(selectAccum);
}
foreach (MFactAccumulation accum in accums)
{
// dateFrom = MFactAccumulation.GetDateFrom(accum, dateFrom);
dateFrom = accum.GetDateFrom();
String type = accum.GetBALANCEACCUMULATION();
String trunc = null;
if (X_Fact_Accumulation.BALANCEACCUMULATION_Daily.Equals(type))
{
trunc = TimeUtil.TRUNC_DAY;
}
else if (X_Fact_Accumulation.BALANCEACCUMULATION_CalendarWeek.Equals(type))
{
trunc = TimeUtil.TRUNC_WEEK;
}
else if (X_Fact_Accumulation.BALANCEACCUMULATION_CalendarMonth.Equals(type))
{
trunc = TimeUtil.TRUNC_MONTH;
}
if (X_Fact_Accumulation.BALANCEACCUMULATION_PeriodOfAViennaCalendar.Equals(type) &&
!CheckPeriod(accum, dateFrom))
{
_log.Log(Level.SEVERE, "Necessary Period doesn't exist for the calendar");
return("Necessary Period doesn't exist for the calendar");
}
String dateClause = null;
if (X_Fact_Accumulation.BALANCEACCUMULATION_PeriodOfAViennaCalendar.Equals(type))
{
dateClause = " Period.StartDate ";
}
else
{
dateClause = " a.DateAcct ";
_log.Fine(trunc);
//dateClause = " TRUNC(a.DateAcct, '" + trunc + "' ) ";
}
// Delete the balances
DeleteBalance(ctx.GetAD_Client_ID(), C_AcctSchema_ID,
dateFrom, trx, accum.GetFACT_ACCUMULATION_ID(), svrPrc);
/** Insert **/
//param = new List<Object>();
String insert = "INSERT INTO Fact_Acct_Balance "
+ "(AD_Client_ID, AD_Org_ID, AD_OrgTrx_ID, C_AcctSchema_ID, DateAcct,"
+ " Account_ID, PostingType, Fact_Accumulation_ID, M_Product_ID, C_BPartner_ID,"
+ " C_Project_ID, C_SalesRegion_ID,C_Activity_ID,"
+ " C_Campaign_ID, C_LocTo_ID, C_LocFrom_ID, User1_ID, User2_ID, GL_Budget_ID,"
+ " UserElement1_ID, UserElement2_ID, "
+ " AmtAcctDr, AmtAcctCr, Qty) ";
String select = " SELECT AD_Client_ID, AD_Org_ID, AD_OrgTrx_ID, C_AcctSchema_ID, ";
select = select + dateClause;
select = select + " ,Account_ID, PostingType, " + accum.GetFACT_ACCUMULATION_ID();
if (accum.IsPRODUCT())
{
select = select + " ,M_Product_ID ";
}
else
{
select = select + " , NULL ";
}
if (accum.IsBUSINESSPARTNER())
{
select = select + " , C_BPartner_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsPROJECT())
{
select = select + " ,C_Project_ID ";
}
else
{
select = select + " , NULL ";
}
if (accum.IsSALESREGION())
{
select = select + ", C_SalesRegion_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsACTIVITY())
{
select = select + " ,C_Activity_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsCAMPAIGN())
{
select = select + " ,C_Campaign_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsLOCATIONTO())
{
select = select + " ,C_LocTo_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsLOCATIONFROM())
{
select = select + " ,C_LocFrom_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUSERLIST1())
{
select = select + " ,User1_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUSERLIST2())
{
select = select + " ,User2_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsBUDGET())
{
select = select + " ,GL_Budget_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUSERELEMENT1())
{
select = select + " ,UserElement1_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUSERELEMENT2())
{
select = select + " , UserElement2_ID ";
}
else
{
select = select + " ,NULL ";
}
select = select + " ,COALESCE(SUM(AmtAcctDr),0), COALESCE(SUM(AmtAcctCr),0), COALESCE(SUM(Qty),0) ";
String from = " FROM Fact_Acct a ";
if (X_Fact_Accumulation.BALANCEACCUMULATION_PeriodOfAViennaCalendar.Equals(type))
{
from += " ,(SELECT StartDate,EndDate FROM C_Period "
+ " WHERE C_Year_ID IN (SELECT C_Year_ID "
+ " FROM C_Year WHERE C_Calendar_ID= " + accum.GetC_Calendar_ID() + " ) "
+ " AND IsActive='Y' AND PeriodType='S') Period";
//param.add(new Integer(accum.GetC_Calendar_ID()));
}
String where = " WHERE C_AcctSchema_ID= " + C_AcctSchema_ID;
if (X_Fact_Accumulation.BALANCEACCUMULATION_PeriodOfAViennaCalendar.Equals(type))
{
where += " AND a.DateAcct BETWEEN TRUNC(Period.StartDate,'DD') AND TRUNC(Period.EndDate,'DD') ";
}
//param.add(new Integer(C_AcctSchema_ID));
if (dateFrom != null)
{
//SimpleDateFormat df = new SimpleDateFormat();//"dd-MM-yyyy"
//String finalDate = df.Format(dateFrom);
//where += " AND DateAcct>= TO_DATE('" + finalDate + "','DD-MM-YYYY') ";
where += " AND DateAcct >= " + DB.TO_DATE(dateFrom);
}
String groupBy = " GROUP BY AD_Client_ID,C_AcctSchema_ID, AD_Org_ID, "
+ " AD_OrgTrx_ID,Account_ID, PostingType," + dateClause;
if (accum.IsPRODUCT())
{
groupBy = groupBy + " ,M_Product_ID ";
}
if (accum.IsBUSINESSPARTNER())
{
groupBy = groupBy + " ,C_BPartner_ID ";
}
if (accum.IsPROJECT())
{
groupBy = groupBy + " ,C_Project_ID ";
}
if (accum.IsSALESREGION())
{
groupBy = groupBy + " ,C_SalesRegion_ID ";
}
if (accum.IsACTIVITY())
{
groupBy = groupBy + ", C_Activity_ID ";
}
if (accum.IsCAMPAIGN())
{
groupBy = groupBy + " ,C_Campaign_ID ";
}
if (accum.IsLOCATIONTO())
{
groupBy = groupBy + ", C_LocTo_ID ";
}
if (accum.IsLOCATIONFROM())
{
groupBy = groupBy + ", C_LocFrom_ID ";
}
if (accum.IsUSERLIST1())
{
groupBy = groupBy + ", User1_ID";
}
if (accum.IsUSERLIST2())
{
groupBy = groupBy + ", User2_ID ";
}
if (accum.IsBUDGET())
{
groupBy = groupBy + ", GL_Budget_ID ";
}
if (accum.IsUSERELEMENT1())
{
groupBy = groupBy + ", UserElement1_ID ";
}
if (accum.IsUSERELEMENT2())
{
groupBy = groupBy + ", UserElement2_ID ";
}
String sql = insert + select + from + where + groupBy;
no = DB.ExecuteQuery(sql, null, trx);
_log.Config("Inserts=" + no);
if (svrPrc != null)
{
svrPrc.AddLog(0, dateFrom, new Decimal(no), "Inserts in " + accum.ToString());
}
trx.Commit();
}
start = CommonFunctions.CurrentTimeMillis() - start;
_log.Info((start / 1000) + " sec");
return("#" + no);
}