} // doIt
/// <summary>
/// Create Beginning Balance Line
/// </summary>
private void CreateBalanceLine()
{
StringBuilder sb = new StringBuilder("INSERT INTO T_ReportStatement "
+ "(AD_PInstance_ID, Fact_Acct_ID, LevelNo,"
+ "DateAcct, Name, Description,"
+ "AmtAcctDr, AmtAcctCr, Balance, Qty) ");
sb.Append("SELECT ").Append(GetAD_PInstance_ID()).Append(",0,0,")
.Append(DataBase.DB.TO_DATE(_DateAcct_From, true)).Append(",")
.Append(DataBase.DB.TO_STRING(Msg.GetMsg(GetCtx(), "BeginningBalance"))).Append(",NULL,"
+ "COALESCE(SUM(AmtAcctDr),0), COALESCE(SUM(AmtAcctCr),0), COALESCE(SUM(AmtAcctDr-AmtAcctCr),0), COALESCE(SUM(Qty),0) "
+ "FROM Fact_Acct_Balance "
+ "WHERE ").Append(_parameterWhere)
.Append(" AND DateAcct < ").Append(DataBase.DB.TO_DATE(_DateAcct_From));
// Start Beginning of Year
if (_Account_ID > 0)
{
_acct = new MElementValue(GetCtx(), _Account_ID, Get_TrxName());
if (!_acct.IsBalanceSheet())
{
MPeriod first = MPeriod.GetFirstInYear(GetCtx(), _DateAcct_From);
if (first != null)
{
sb.Append(" AND DateAcct >= ").Append(DataBase.DB.TO_DATE(first.GetStartDate()));
}
else
{
log.Log(Level.SEVERE, "First period not found");
}
}
}
//
int no = DataBase.DB.ExecuteQuery(sb.ToString(), null, Get_TrxName());
log.Fine("#" + no + " (Account_ID=" + _Account_ID + ")");
log.Finest(sb.ToString());
} // createBalanceLine
/**
* Process
* @return message
* @throws Exception
*/
protected override String DoIt()
{
//log.info("C_Period_ID=" + _C_Period_ID + ", PeriodAction=" + _PeriodAction);
MPeriod period = new MPeriod(GetCtx(), _C_Period_ID, Get_TrxName());
if (period.Get_ID() == 0)
{
throw new Exception("@NotFound@ @C_Period_ID@=" + _C_Period_ID);
}
StringBuilder sql = new StringBuilder("UPDATE C_PeriodControl ");
sql.Append("SET PeriodStatus='");
// Open
if (MPeriodControl.PERIODACTION_OpenPeriod.Equals(_PeriodAction))
{
sql.Append(MPeriodControl.PERIODSTATUS_Open);
}
// Close
else if (MPeriodControl.PERIODACTION_ClosePeriod.Equals(_PeriodAction))
{
sql.Append(MPeriodControl.PERIODSTATUS_Closed);
}
// Close Permanently
else if (MPeriodControl.PERIODACTION_PermanentlyClosePeriod.Equals(_PeriodAction))
{
sql.Append(MPeriodControl.PERIODSTATUS_PermanentlyClosed);
}
else
{
return("-");
}
//
sql.Append("', PeriodAction='N', Updated=SysDate,UpdatedBy=").Append(GetAD_User_ID());
// WHERE
sql.Append(" WHERE C_Period_ID=").Append(period.GetC_Period_ID())
.Append(" AND PeriodStatus<>'P'")
.Append(" AND PeriodStatus<>'").Append(_PeriodAction).Append("'");
// if organization is selected then update period control of selected organizations
if (!String.IsNullOrEmpty(_AD_Org_ID))
{
sql.Append(" AND AD_Org_ID IN (" + _AD_Org_ID + ")");
}
// if Document BaseType is selected then update period control for selected Document BaseType
if (!String.IsNullOrEmpty(_docBaseType))
{
sql.Append(" AND DocBaseType IN (SELECT DocBaseType FROM C_DocBaseType WHERE C_DocBaseType_ID IN (" + _docBaseType + "))");
}
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
// Lokesh Chauhan 17-Dec-2013
/* Change For setting date From in Balance Aggregation window
* For Fact Account Balance updation */
if (no >= 0)
{
if (MPeriodControl.PERIODACTION_ClosePeriod.Equals(_PeriodAction))
{
try
{
string sqlSchID = "SELECT C_AcctSchema_ID FROM C_AcctSchema WHERE IsActive = 'Y' AND AD_Client_ID = " + GetCtx().GetAD_Client_ID();
DataSet ds = DB.ExecuteDataset(sqlSchID);
if (ds != null)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int C_AcctSchema_ID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_AcctSchema_ID"]);
string sqlUpd = "UPDATE Fact_Accumulation SET DateFrom = " + DB.TO_DATE(period.GetStartDate().Value.AddDays(-1)) + " WHERE IsActive = 'Y' AND AD_Client_ID = " + GetCtx().GetAD_Client_ID();
no = DB.ExecuteQuery(sqlUpd, null, Get_TrxName());
if (Get_Trx().Commit())
{
VAdvantage.Report.FinBalance.UpdateBalance(GetCtx(), C_AcctSchema_ID, period.GetStartDate().Value.AddDays(-1), Get_TrxName(), 0, this);
}
}
}
}
catch
{
}
}
}
CacheMgt.Get().Reset("C_PeriodControl", 0);
CacheMgt.Get().Reset("C_Period", _C_Period_ID);
return("@Period Updated@ #" + no);
}
} // doIt
/// <summary>
/// Create Beginning Balance Line
/// </summary>
private void CreateBalanceLine()
{
StringBuilder sql = new StringBuilder(_insert);
// (AD_PInstance_ID, Fact_Acct_ID,
sql.Append("SELECT ").Append(GetAD_PInstance_ID()).Append(",0,");
// AD_Client_ID, AD_Org_ID, Created,CreatedBy, Updated,UpdatedBy,
sql.Append(GetAD_Client_ID()).Append(",");
if (_AD_Org_ID == 0 || _AD_Org_ID == -1)
{
sql.Append("0");
}
else
{
sql.Append(_AD_Org_ID);
}
sql.Append(", SysDate,").Append(GetAD_User_ID())
.Append(",SysDate,").Append(GetAD_User_ID()).Append(",");
// C_AcctSchema_ID, Account_ID, AccountValue, DateTrx, DateAcct, C_Period_ID,
sql.Append(_C_AcctSchema_ID).Append(",");
if (_Account_ID == 0 || _Account_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_Account_ID);
}
if (_AccountValue_From != null)
{
sql.Append(",").Append(DataBase.DB.TO_STRING(_AccountValue_From));
}
else if (_AccountValue_To != null)
{
sql.Append(",' '");
}
else
{
sql.Append(",null");
}
DateTime?balanceDay = _DateAcct_From; // TimeUtil.addDays(_DateAcct_From, -1);
sql.Append(",null,").Append(DataBase.DB.TO_DATE(balanceDay, true)).Append(",");
if (_C_Period_ID == 0 || _C_Period_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_Period_ID);
}
sql.Append(",");
// AD_Table_ID, Record_ID, Line_ID,
sql.Append("null,null,null,");
// GL_Category_ID, GL_Budget_ID, C_Tax_ID, M_Locator_ID, PostingType,
sql.Append("null,null,null,null,'").Append(_PostingType).Append("',");
// C_Currency_ID, AmtSourceDr, AmtSourceCr, AmtSourceBalance,
sql.Append("null,null,null,null,");
// AmtAcctDr, AmtAcctCr, AmtAcctBalance, C_UOM_ID, Qty,
sql.Append(" COALESCE(SUM(AmtAcctDr),0),COALESCE(SUM(AmtAcctCr),0),"
+ "COALESCE(SUM(AmtAcctDr),0)-COALESCE(SUM(AmtAcctCr),0),"
+ " null,COALESCE(SUM(Qty),0),");
// M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID, C_LocFrom_ID,C_LocTo_ID,
if (_M_Product_ID == 0 || _M_Product_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_M_Product_ID);
}
sql.Append(",");
if (_C_BPartner_ID == 0 || _C_BPartner_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_BPartner_ID);
}
sql.Append(",");
if (_AD_OrgTrx_ID == 0 || _AD_OrgTrx_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_AD_OrgTrx_ID);
}
sql.Append(",");
if (_C_LocFrom_ID == 0 || _C_LocFrom_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_LocFrom_ID);
}
sql.Append(",");
if (_C_LocTo_ID == 0 || _C_LocTo_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_LocTo_ID);
}
sql.Append(",");
// C_SalesRegion_ID, C_Project_ID, C_Campaign_ID, C_Activity_ID,
if (_C_SalesRegion_ID == 0 || _C_SalesRegion_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_SalesRegion_ID);
}
sql.Append(",");
if (_C_Project_ID == 0 || _C_Project_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_Project_ID);
}
sql.Append(",");
if (_C_Campaign_ID == 0 || _C_Campaign_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_Campaign_ID);
}
sql.Append(",");
if (_C_Activity_ID == 0 || _C_Activity_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_C_Activity_ID);
}
sql.Append(",");
// User1_ID, User2_ID, A_Asset_ID, Description)
if (_User1_ID == 0 || _User1_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_User1_ID);
}
sql.Append(",");
if (_User2_ID == 0 || _User2_ID == -1)
{
sql.Append("null");
}
else
{
sql.Append(_User2_ID);
}
sql.Append(", null,null");
//
sql.Append(" FROM Fact_Acct WHERE AD_Client_ID=").Append(GetAD_Client_ID())
.Append(" AND ").Append(m_parameterWhere)
.Append(" AND DateAcct < ").Append(DataBase.DB.TO_DATE(_DateAcct_From, true));
// Start Beginning of Year
if (_Account_ID > 0)
{
m_acct = new MElementValue(GetCtx(), _Account_ID, Get_Trx());
if (!m_acct.IsBalanceSheet())
{
MPeriod first = MPeriod.GetFirstInYear(GetCtx(), _DateAcct_From);
if (first != null)
{
sql.Append(" AND DateAcct >= ").Append(DataBase.DB.TO_DATE(first.GetStartDate(), true));
}
else
{
log.Log(Level.SEVERE, "first period not found");
}
}
}
//
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_Trx());
if (no == 0)
{
log.Fine(sql.ToString());
}
log.Fine("#" + no + " (Account_ID=" + _Account_ID + ")");
} // createBalanceLine