/**
* 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_CalendarMonth);
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);
defaultAccum.SetIsUserElement3(true);
defaultAccum.SetIsUserElement4(true);
defaultAccum.SetIsUserElement5(true);
defaultAccum.SetIsUserElement6(true);
defaultAccum.SetIsUserElement7(true);
defaultAccum.SetIsUserElement8(true);
defaultAccum.SetIsUserElement9(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 (String.IsNullOrEmpty(type))
{
dateFrom = 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 if (X_Fact_Accumulation.BALANCEACCUMULATION_Daily.Equals(type))
{
dateClause = " TRUNC(a.DateAcct,'DD') ";
}
else if (X_Fact_Accumulation.BALANCEACCUMULATION_CalendarMonth.Equals(type))
{
dateClause = " TRUNC(a.DateAcct,'MM') ";
}
else if (X_Fact_Accumulation.BALANCEACCUMULATION_CalendarWeek.Equals(type))
{
dateClause = " TRUNC(a.DateAcct,'WW') "; // Calendar Week - WW , Month Week - W
}
//else if (!String.IsNullOrEmpty(type))
//{
// 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, UserElement3_ID, UserElement4_ID, UserElement5_ID, UserElement6_ID,"
+ " UserElement7_ID, UserElement8_ID,UserElement9_ID, "
+ " AmtAcctDr, AmtAcctCr, Qty) ";
String select = " SELECT AD_Client_ID, AD_Org_ID, AD_OrgTrx_ID, C_AcctSchema_ID ";
if (!String.IsNullOrEmpty(type))
{
select = select + " , " + dateClause;
}
else
{
// when balance Accumulation type = null, then consider system date as account date
select = select + " , SYSDATE ";
}
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 ";
}
if (accum.IsUserElement3())
{
select = select + " ,UserElement3_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUserElement4())
{
select = select + " , UserElement4_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUserElement5())
{
select = select + " ,UserElement5_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUserElement6())
{
select = select + " , UserElement6_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUserElement7())
{
select = select + " ,UserElement7_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUserElement8())
{
select = select + " , UserElement8_ID ";
}
else
{
select = select + " ,NULL ";
}
if (accum.IsUserElement9())
{
select = select + " ,UserElement9_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 ";
if (!String.IsNullOrEmpty(type))
{
// when balance Accumulation type != null, then to consider in group by else not required
groupBy = groupBy + " , " + 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 ";
}
if (accum.IsUserElement3())
{
groupBy = groupBy + ", UserElement3_ID ";
}
if (accum.IsUserElement4())
{
groupBy = groupBy + ", UserElement4_ID ";
}
if (accum.IsUserElement5())
{
groupBy = groupBy + ", UserElement5_ID ";
}
if (accum.IsUserElement6())
{
groupBy = groupBy + ", UserElement6_ID ";
}
if (accum.IsUserElement7())
{
groupBy = groupBy + ", UserElement7_ID ";
}
if (accum.IsUserElement8())
{
groupBy = groupBy + ", UserElement8_ID ";
}
if (accum.IsUserElement9())
{
groupBy = groupBy + ", UserElement9_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);
}