} // 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
} // 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