} // copyGL
/// <summary>
/// Copy Default
/// </summary>
/// <param name="targetAS">target</param>
private void CopyDefault(MAcctSchema targetAS)
{
MAcctSchemaDefault source = MAcctSchemaDefault.Get(GetCtx(), _SourceAcctSchema_ID);
MAcctSchemaDefault target = new MAcctSchemaDefault(GetCtx(), 0, Get_Trx());
target.SetC_AcctSchema_ID(_TargetAcctSchema_ID);
target.SetC_AcctSchema_ID(_TargetAcctSchema_ID);
//ArrayList<KeyNamePair> list = source.getAcctInfo();
List <KeyNamePair> list = source.GetAcctInfo();
for (int i = 0; i < list.Count; i++)
{
//KeyNamePair pp = list.get(i);
KeyNamePair pp = list[i];
int sourceC_ValidCombination_ID = pp.GetKey();
String columnName = pp.GetName();
MAccount sourceAccount = MAccount.Get(GetCtx(), sourceC_ValidCombination_ID);
MAccount targetAccount = CreateAccount(targetAS, sourceAccount);
target.SetValue(columnName, Utility.Util.GetValueOfInt(targetAccount.GetC_ValidCombination_ID()));
}
if (!target.Save())
{
throw new Exception("Could not Save Default");
}
} // copyDefault
/// <summary>
/// Is used to create line against Income Account summary account --
/// </summary>
/// <param name="Profit">class object of MProfitLoss </param>
private void InsertProfitLossLine(MProfitLoss Profit)
{
// get consolidated profit amount agsint Organization
String Sql = @"SELECT AD_Org_ID , (SUM(AccountDebit) - SUM(AccountCredit)) AS ProfitAmt
FROM C_ProfitLossLines WHERE C_ProfitAndLoss_ID > 0 AND C_ProfitLoss_ID = " + Profit.GetC_ProfitLoss_ID() + @" GROUP BY AD_Org_ID";
DataSet dsProfit = DB.ExecuteDataset(Sql, null, Get_Trx());
if (dsProfit != null && dsProfit.Tables.Count > 0 && dsProfit.Tables[0].Rows.Count > 0)
{
// get max line no
int lineNo = Convert.ToInt32(DB.ExecuteScalar(@"SELECT NVL(MAX(Line),0)+10 AS line FROM C_ProfitLossLines WHERE C_ProfitLoss_ID = " + Profit.GetC_ProfitLoss_ID(), null, Get_Trx()));
// get Valid Combination against Income Summary Acct from accounting schema
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT IncomeSummary_Acct FROM C_AcctSchema_GL WHERE C_AcctSchema_ID=" + Util.GetValueOfInt(Profit.Get_Value("C_AcctSchema_ID"))));
// get account id
MAccount acct = MAccount.Get(GetCtx(), validComID);
for (int i = 0; i < dsProfit.Tables[0].Rows.Count; i++)
{
Decimal profitAmt = Util.GetValueOfDecimal(dsProfit.Tables[0].Rows[i]["ProfitAmt"]);
// when difference of DR - CR is ZERO then not to create line
if (profitAmt == 0)
{
continue;
}
MProfitLossLines profitLossLines = new MProfitLossLines(GetCtx(), 0, Get_Trx());
profitLossLines.SetAD_Client_ID(Profit.GetAD_Client_ID());
profitLossLines.SetAD_Org_ID(Util.GetValueOfInt(dsProfit.Tables[0].Rows[i]["AD_Org_ID"]));
profitLossLines.SetC_ProfitLoss_ID(Profit.GetC_ProfitLoss_ID());
profitLossLines.SetLine(lineNo);
profitLossLines.SetC_AcctSchema_ID(Util.GetValueOfInt(Profit.Get_Value("C_AcctSchema_ID")));
profitLossLines.SetPostingType(Util.GetValueOfString(Profit.Get_Value("PostingType")));
profitLossLines.SetAccount_ID(acct.GetAccount_ID());
profitLossLines.Set_Value("LedgerCode", acct.GetAccount().Get_Value("Value"));
profitLossLines.Set_Value("LedgerName", acct.GetAccount().Get_Value("Name"));
if (profitAmt > 0)
{
profitLossLines.SetAccountCredit(profitAmt);
}
else
{
profitLossLines.SetAccountDebit(profitAmt);
}
if (!profitLossLines.Save(Get_Trx()))
{
ValueNamePair pp = VLogger.RetrieveError();
log.Fine("Failed - Profit loss line not saved for income acct summary - " + (pp != null && !String.IsNullOrEmpty(pp.GetName()) ? pp.GetName() : ""));
}
else
{
lineNo += 10;
}
}
}
}
/// <summary>
/// Create Facts (the accounting logic) for
/// CMC.
/// <pre>
/// Expense
/// CashExpense DR
/// CashAsset CR
///Receipt
/// CashAsset DR
/// CashReceipt CR
/// Charge
/// Charge DR
/// CashAsset CR
/// Difference
/// CashDifference DR
/// CashAsset CR
/// Invoice
/// CashAsset DR
/// CashTransfer CR
/// Transfer
/// BankInTransit DR
/// CashAsset CR
/// </pre>
/// </summary>
/// <param name="?"></param>
/// <returns>Fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// create Fact Header
List <Fact> facts = new List <Fact>();
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_PROFITLOSS))
{
// Decimal grossAmt = getAmount(Doc.AMTTYPE_Gross);
SetC_Currency_ID(GetCurrency(as1.GetC_AcctSchema_ID()));
// Commitment
Fact fact = new Fact(this, as1, Fact.POST_Actual);
Decimal total = Env.ZERO, totalCredit = Env.ZERO, totalDebit = Env.ZERO;
Decimal credit = Env.ZERO, debit = Env.ZERO;
for (int i = 0; i < _lines.Length; i++)
{
DocLine dline = _lines[i];
MProfitLossLines line = new MProfitLossLines(GetCtx(), dline.Get_ID(), null);
credit = Util.GetValueOfDecimal(dline.GetAmtAcctCr());
debit = Util.GetValueOfDecimal(dline.GetAmtAcctDr());
if (credit > 0)
{
totalCredit = Decimal.Add(totalCredit, credit);
}
if (debit > 0)
{
totalDebit = Decimal.Add(totalDebit, debit);
}
// Account
MAccount expense = MAccount.Get(GetCtx(), GetAD_Client_ID(), GetAD_Org_ID(), line.GetC_AcctSchema_ID(), line.GetAccount_ID(), line.GetC_SubAcct_ID(), line.GetM_Product_ID(), line.GetC_BPartner_ID(), line.GetAD_OrgTrx_ID(),
line.GetC_LocFrom_ID(), line.GetC_LocTo_ID(), line.GetC_SalesRegion_ID(), line.GetC_Project_ID(), line.GetC_Campaign_ID(), line.GetC_Activity_ID(), line.GetUser1_ID(), line.GetUser2_ID(), line.GetUserElement1_ID(), line.GetUserElement2_ID());
fact.CreateLine(dline, expense, GetCurrency(line.GetC_AcctSchema_ID()), debit, credit);
}
total = totalCredit - totalDebit;
if (total != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT IncomeSummary_Acct FROM C_AcctSchema_GL WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
MAccount acct = MAccount.Get(GetCtx(), validComID);
fact.CreateLine(null, acct, GetC_Currency_ID(), total);
}
//if (TotalCurrLoss != Env.ZERO)
//{
// int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )"));
// MAccount acct = MAccount.Get(GetCtx(), validComID);
// TotalCurrLoss = MConversionRate.Convert(GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
// fact.CreateLine(null, acct,
// GetC_Currency_ID(), (TotalCurrLoss));
//}
facts.Add(fact);
}
return(facts);
}
/// <summary>
/// Save account
/// </summary>
/// <param name="ctx"></param>
/// <param name="AD_Client_ID"></param>
/// <param name="AD_Org_ID"></param>
/// <param name="C_AcctSchema_ID"></param>
/// <param name="AD_Account_ID"></param>
/// <param name="C_SubAcct_ID"></param>
/// <param name="M_Product_ID"></param>
/// <param name="C_BPartner_ID"></param>
/// <param name="AD_OrgTrx_ID"></param>
/// <param name="C_LocFrom_ID"></param>
/// <param name="C_LocTo_ID"></param>
/// <param name="C_SRegion_ID"></param>
/// <param name="C_Project_ID"></param>
/// <param name="C_Campaign_ID"></param>
/// <param name="C_Activity_ID"></param>
/// <param name="User1_ID"></param>
/// <param name="User2_ID"></param>
/// <param name="Alias"></param>
/// <returns></returns>
public AccountingObjects SaveAccount(Ctx ctx, int AD_Client_ID, int AD_Org_ID, int C_AcctSchema_ID, int AD_Account_ID, int C_SubAcct_ID, int M_Product_ID,
int C_BPartner_ID, int AD_OrgTrx_ID, int C_LocFrom_ID, int C_LocTo_ID, int C_SRegion_ID, int C_Project_ID, int C_Campaign_ID,
int C_Activity_ID, int User1_ID, int User2_ID, string Alias)
{
AccountingObjects obj = new AccountingObjects();
MAccount acct = MAccount.Get(ctx, AD_Client_ID, AD_Org_ID, C_AcctSchema_ID, AD_Account_ID, C_SubAcct_ID, M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
C_LocFrom_ID, C_LocTo_ID, C_SRegion_ID, C_Project_ID, C_Campaign_ID, C_Activity_ID, User1_ID, User2_ID, 0, 0);
if (acct != null && acct.Get_ID() == 0)
{
acct.Save();
}
// Show Info
if (acct == null || acct.Get_ID() == 0)
{
obj = LoadInfo(0, 0);
}
else
{
// Update Account with optional Alias
bool found = false;
if (Alias.Length > 0)
{
String sql1 = "SELECT COUNT(*) FROM C_ValidCombination WHERE Alias='" + Alias + "'";
int ii = DB.GetSQLValue(null, sql1);
if (ii != 0)
{
found = true;
}
else
{
acct.SetAlias(Alias);
acct.Save();
}
}
if (found)
{
obj.ErrorMsg = "DuplicateAlias";
}
else
{
//obj = LoadInfo(acct.Get_ID(), C_AcctSchema_ID);
obj.C_ValidCombination_ID = acct.Get_ID();
obj.C_AcctSchema_ID = C_AcctSchema_ID;
}
}
return(obj);
}
} // 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
/// <summary>
/// Create Facts (the accounting logic) for
/// </summary>
/// <param name="?"></param>
/// <returns>Fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// create Fact Header
List <Fact> facts = new List <Fact>();
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_INCOMETAX))
{
// Decimal grossAmt = getAmount(Doc.AMTTYPE_Gross);
// Commitment
Fact fact = new Fact(this, as1, Fact.POST_Actual);
Decimal total = Env.ZERO;
Decimal amount = Env.ZERO;
for (int i = 0; i < _lines.Length; i++)
{
DocLine dline = _lines[i];
MIncomeTaxLines line = new MIncomeTaxLines(GetCtx(), dline.Get_ID(), null);
amount = Util.GetValueOfDecimal(line.GetIncomeTaxAmount());
if (amount != Env.ZERO)
{
total = Decimal.Add(total, amount);
}
// Account
MAccount expense = MAccount.Get(GetCtx(), line.GetC_IncomeTax_Acct());
fact.CreateLine(dline, expense, GetC_Currency_ID(), amount);
}
if (total != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT T_Due_Acct FROM C_Tax_Acct WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
MAccount acct = MAccount.Get(GetCtx(), validComID);
fact.CreateLine(null, acct, GetC_Currency_ID(), Decimal.Negate(total));
}
facts.Add(fact);
}
return(facts);
}
/// <summary>
/// Get Account
/// </summary>
/// <param name="AcctType"></param>
/// <param name="as1"></param>
/// <returns>Account</returns>
public MAccount GetAccount(int AcctType, MAcctSchema as1)
{
if (AcctType < 0 || AcctType > 4)
{
return(null);
}
//
String sql = "SELECT T_Due_Acct, T_Liability_Acct, T_Credit_Acct, T_Receivables_Acct, T_Expense_Acct "
+ "FROM C_Tax_Acct WHERE C_Tax_ID=" + _C_Tax_ID + " AND C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID();
int validCombination_ID = 0;
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql, null, null);
if (idr.Read())
{
validCombination_ID = Utility.Util.GetValueOfInt(idr[AcctType]);// .getInt(AcctType + 1); // 1..5
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
idr = null;
}
log.Log(Level.SEVERE, sql, e);
}
if (validCombination_ID == 0)
{
return(null);
}
return(MAccount.Get(as1.GetCtx(), validCombination_ID));
}
/// <summary>
/// Revenue Recognition.
/// Called from FactLine.save
/// <p>
/// Create Revenue recognition plan and return Unearned Revenue account
/// to be used instead of Revenue Account. If not found, it returns
/// the revenue account.
/// </summary>
/// <param name="C_RevenueRecognition_ID">revenue recognition</param>
/// <param name="C_InvoiceLine_ID">invoice line</param>
/// <param name="AD_Client_ID">client</param>
/// <param name="AD_Org_ID">Org</param>
/// <param name="AD_User_ID">user</param>
/// <param name="Account_ID">of Revenue Account</param>
/// <param name="C_SubAcct_ID"> sub account</param>
/// <param name="M_Product_ID">product</param>
/// <param name="C_BPartner_ID">bpartner</param>
/// <param name="AD_OrgTrx_ID"> trx org</param>
/// <param name="C_LocFrom_ID">loc from</param>
/// <param name="C_LocTo_ID">loc to</param>
/// <param name="C_SRegion_ID">sales region</param>
/// <param name="C_Project_ID">project</param>
/// <param name="C_Campaign_ID">campaign</param>
/// <param name="C_Activity_ID">activity</param>
/// <param name="User1_ID"></param>
/// <param name="User2_ID"></param>
/// <param name="UserElement1_ID">user element 1</param>
/// <param name="UserElement2_ID">user element 2</param>
/// <returns></returns>
private int CreateRevenueRecognition(
int C_RevenueRecognition_ID, int C_InvoiceLine_ID,
int AD_Client_ID, int AD_Org_ID, int AD_User_ID,
int Account_ID, int C_SubAcct_ID,
int M_Product_ID, int C_BPartner_ID, int AD_OrgTrx_ID,
int C_LocFrom_ID, int C_LocTo_ID, int C_SRegion_ID, int C_Project_ID,
int C_Campaign_ID, int C_Activity_ID,
int User1_ID, int User2_ID, int UserElement1_ID, int UserElement2_ID)
{
log.Fine("From Accout_ID=" + Account_ID);
// get VC for P_Revenue (from Product)
MAccount revenue = MAccount.Get(GetCtx(),
AD_Client_ID, AD_Org_ID, GetC_AcctSchema_ID(), Account_ID, C_SubAcct_ID,
M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID, C_LocFrom_ID, C_LocTo_ID, C_SRegion_ID,
C_Project_ID, C_Campaign_ID, C_Activity_ID,
User1_ID, User2_ID, UserElement1_ID, UserElement2_ID);
if (revenue != null && revenue.Get_ID() == 0)
{
revenue.Save();
}
if (revenue == null || revenue.Get_ID() == 0)
{
log.Severe("Revenue_Acct not found");
return(Account_ID);
}
int P_Revenue_Acct = revenue.Get_ID();
// get Unearned Revenue Acct from BPartner Group
int unearnedRevenue_Acct = 0;
int new_Account_ID = 0;
String sql = "SELECT ga.UnearnedRevenue_Acct, vc.Account_ID "
+ "FROM C_BP_Group_Acct ga, C_BPartner p, C_ValidCombination vc "
+ "WHERE ga.C_BP_Group_ID=p.C_BP_Group_ID"
+ " AND ga.UnearnedRevenue_Acct=vc.C_ValidCombination_ID"
+ " AND ga.C_AcctSchema_ID=" + GetC_AcctSchema_ID() + " AND p.C_BPartner_ID=" + C_BPartner_ID;
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql, null, Get_TrxName());
if (idr.Read())
{
unearnedRevenue_Acct = Utility.Util.GetValueOfInt(idr[0]); ///.getInt(1);
new_Account_ID = Utility.Util.GetValueOfInt(idr[1]); //.getInt(2);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, sql, e);
}
if (new_Account_ID == 0)
{
log.Severe("UnearnedRevenue_Acct not found");
return(Account_ID);
}
MRevenueRecognitionPlan plan = new MRevenueRecognitionPlan(GetCtx(), 0, null);
plan.SetC_RevenueRecognition_ID(C_RevenueRecognition_ID);
plan.SetC_AcctSchema_ID(GetC_AcctSchema_ID());
plan.SetC_InvoiceLine_ID(C_InvoiceLine_ID);
plan.SetUnEarnedRevenue_Acct(unearnedRevenue_Acct);
plan.SetP_Revenue_Acct(P_Revenue_Acct);
plan.SetC_Currency_ID(GetC_Currency_ID());
plan.SetTotalAmt(GetAcctBalance());
if (!plan.Save(Get_TrxName()))
{
log.Severe("Plan NOT created");
return(Account_ID);
}
log.Fine("From Acctount_ID=" + Account_ID + " to " + new_Account_ID
+ " - Plan from UnearnedRevenue_Acct=" + unearnedRevenue_Acct + " to Revenue_Acct=" + P_Revenue_Acct);
return(new_Account_ID);
}
} // 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
/// <summary>
/// Save account
/// </summary>
/// <param name="ctx"></param>
/// <param name="AD_Client_ID"></param>
/// <param name="AD_Org_ID"></param>
/// <param name="C_AcctSchema_ID"></param>
/// <param name="AD_Account_ID"></param>
/// <param name="C_SubAcct_ID"></param>
/// <param name="M_Product_ID"></param>
/// <param name="C_BPartner_ID"></param>
/// <param name="AD_OrgTrx_ID"></param>
/// <param name="C_LocFrom_ID"></param>
/// <param name="C_LocTo_ID"></param>
/// <param name="C_SRegion_ID"></param>
/// <param name="C_Project_ID"></param>
/// <param name="C_Campaign_ID"></param>
/// <param name="C_Activity_ID"></param>
/// <param name="User1_ID"></param>
/// <param name="User2_ID"></param>
/// <param name="Alias"></param>
/// <returns></returns>
public AccountingObjects SaveAccount(Ctx ctx, int AD_Client_ID, int AD_Org_ID, int C_AcctSchema_ID, int AD_Account_ID, int C_SubAcct_ID, int M_Product_ID,
int C_BPartner_ID, int AD_OrgTrx_ID, int C_LocFrom_ID, int C_LocTo_ID, int C_SRegion_ID, int C_Project_ID, int C_Campaign_ID,
int C_Activity_ID, int User1_ID, int User2_ID, int UserElement1_ID, int UserElement2_ID, int UserElement3_ID, int UserElement4_ID,
int UserElement5_ID, int UserElement6_ID, int UserElement7_ID, int UserElement8_ID, int UserElement9_ID, string Alias)
{
AccountingObjects obj = new AccountingObjects();
MAccount acct = null;
string qry = "SELECT Count(*) FROM AD_Column WHERE ColumnName = 'UserElement3_ID' AND AD_Table_ID = 176";
if (Util.GetValueOfInt(DBase.DB.ExecuteScalar(qry, null, null)) > 0)
{
acct = MAccount.Get(ctx, AD_Client_ID, AD_Org_ID, C_AcctSchema_ID, AD_Account_ID, C_SubAcct_ID, M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
C_LocFrom_ID, C_LocTo_ID, C_SRegion_ID, C_Project_ID, C_Campaign_ID, C_Activity_ID, User1_ID, User2_ID, UserElement1_ID, UserElement2_ID,
UserElement3_ID, UserElement4_ID, UserElement5_ID, UserElement6_ID, UserElement7_ID, UserElement8_ID, UserElement9_ID);
}
else
{
acct = MAccount.Get(ctx, AD_Client_ID, AD_Org_ID, C_AcctSchema_ID, AD_Account_ID, C_SubAcct_ID, M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
C_LocFrom_ID, C_LocTo_ID, C_SRegion_ID, C_Project_ID, C_Campaign_ID, C_Activity_ID, User1_ID, User2_ID, UserElement1_ID, UserElement2_ID);
}
if (acct != null && acct.Get_ID() == 0)
{
acct.Save();
}
// Show Info
if (acct == null || acct.Get_ID() == 0)
{
obj = LoadInfo(0, 0);
}
else
{
// Update Account with optional Alias
bool found = false;
if (Alias.Length > 0)
{
String sql1 = "SELECT COUNT(*) FROM C_ValidCombination WHERE Alias='" + Alias + "'";
int ii = DB.GetSQLValue(null, sql1);
if (ii != 0)
{
found = true;
}
else
{
acct.SetAlias(Alias);
acct.Save();
}
}
if (found)
{
obj.ErrorMsg = "DuplicateAlias";
}
else
{
//obj = LoadInfo(acct.Get_ID(), C_AcctSchema_ID);
obj.C_ValidCombination_ID = acct.Get_ID();
obj.C_AcctSchema_ID = C_AcctSchema_ID;
}
}
return(obj);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// CMC.
/// <pre>
/// Expense
/// CashExpense DR
/// CashAsset CR
///Receipt
/// CashAsset DR
/// CashReceipt CR
/// Charge
/// Charge DR
/// CashAsset CR
/// Difference
/// CashDifference DR
/// CashAsset CR
/// Invoice
/// CashAsset DR
/// CashTransfer CR
/// Transfer
/// BankInTransit DR
/// CashAsset CR
/// </pre>
/// </summary>
/// <param name="?"></param>
/// <returns>Fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// create Fact Header
List <Fact> facts = new List <Fact>();
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_PROFITLOSS))
{
//Change By mohit
// Get Assigned Accounting Schemas based on organization
MProfitLossLines PLossline = new MProfitLossLines(GetCtx(), _lines[0].Get_ID(), null);
MProfitLoss PLoss = new MProfitLoss(GetCtx(), PLossline.GetC_ProfitLoss_ID(), null);
MAcctSchema HeaderAcctSchema = new MAcctSchema(GetCtx(), Util.GetValueOfInt(PLoss.Get_Value("C_AcctSchema_ID")), null);
List <int> _ListAcctSch = new List <int>();
// Profit & Loss account shall be posted only in accounting schema selected on header (By Ashish - discussed with Mukesh sir)
//_ListAcctSch = GetAcctSchemas(PLoss.GetAD_Org_ID());
_ListAcctSch.Add(HeaderAcctSchema.GetC_AcctSchema_ID());
if (_ListAcctSch.Count > 0)
{
int CurrencyType_ID = GetDefaultConversionType(GetAD_Client_ID(), GetAD_Org_ID());
for (int asch = 0; asch < _ListAcctSch.Count; asch++)
{
MAcctSchema AccountingSchema = new MAcctSchema(GetCtx(), _ListAcctSch[asch], null);
// Decimal grossAmt = getAmount(Doc.AMTTYPE_Gross);
SetC_Currency_ID(GetCurrency(AccountingSchema.GetC_AcctSchema_ID()));
// Commitment
Fact fact = new Fact(this, AccountingSchema, Fact.POST_Actual);
Decimal total = Env.ZERO, totalCredit = Env.ZERO, totalDebit = Env.ZERO;
Decimal credit = Env.ZERO, debit = Env.ZERO;
for (int i = 0; i < _lines.Length; i++)
{
DocLine dline = _lines[i];
MProfitLossLines line = new MProfitLossLines(GetCtx(), dline.Get_ID(), null);
if (Util.GetValueOfInt(HeaderAcctSchema.GetC_Currency_ID()) == Util.GetValueOfInt(AccountingSchema.GetC_Currency_ID()))
{
credit = Util.GetValueOfDecimal(dline.GetAmtSourceCr());
debit = Util.GetValueOfDecimal(dline.GetAmtSourceDr());
}
else
{
credit = MConversionRate.Convert(GetCtx(), Util.GetValueOfDecimal(dline.GetAmtSourceCr()), HeaderAcctSchema.GetC_Currency_ID(), AccountingSchema.GetC_Currency_ID(),
PLoss.GetDateAcct(), CurrencyType_ID, GetAD_Client_ID(), GetAD_Org_ID());
debit = MConversionRate.Convert(GetCtx(), Util.GetValueOfDecimal(dline.GetAmtSourceDr()), HeaderAcctSchema.GetC_Currency_ID(), AccountingSchema.GetC_Currency_ID(),
PLoss.GetDateAcct(), CurrencyType_ID, GetAD_Client_ID(), GetAD_Org_ID());
Util.GetValueOfDecimal(dline.GetAmtAcctDr());
}
if (credit > 0)
{
totalCredit = Decimal.Add(totalCredit, credit);
}
if (debit > 0)
{
totalDebit = Decimal.Add(totalDebit, debit);
}
// Account
MAccount expense = MAccount.Get(GetCtx(), GetAD_Client_ID(), GetAD_Org_ID(), AccountingSchema.GetC_AcctSchema_ID(), line.GetAccount_ID(), line.GetC_SubAcct_ID(), line.GetM_Product_ID(), line.GetC_BPartner_ID(), line.GetAD_OrgTrx_ID(),
line.GetC_LocFrom_ID(), line.GetC_LocTo_ID(), line.GetC_SalesRegion_ID(), line.GetC_Project_ID(), line.GetC_Campaign_ID(), line.GetC_Activity_ID(), line.GetUser1_ID(), line.GetUser2_ID(), line.GetUserElement1_ID(), line.GetUserElement2_ID(),
line.GetUserElement3_ID(), line.GetUserElement4_ID(), line.GetUserElement5_ID(), line.GetUserElement6_ID(), line.GetUserElement7_ID(), line.GetUserElement8_ID(), line.GetUserElement9_ID());
fact.CreateLine(dline, expense, GetCurrency(AccountingSchema.GetC_AcctSchema_ID()), debit, credit);
}
total = totalCredit - totalDebit;
if (total != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT IncomeSummary_Acct FROM C_AcctSchema_GL WHERE C_AcctSchema_ID=" + AccountingSchema.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
MAccount acct = MAccount.Get(GetCtx(), validComID);
fact.CreateLine(null, acct, GetC_Currency_ID(), total);
}
//if (TotalCurrLoss != Env.ZERO)
//{
// int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )"));
// MAccount acct = MAccount.Get(GetCtx(), validComID);
// TotalCurrLoss = MConversionRate.Convert(GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
// fact.CreateLine(null, acct,
// GetC_Currency_ID(), (TotalCurrLoss));
//}
facts.Add(fact);
}
}
}
return(facts);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// ARI, ARC, ARF, API, APC.
/// <pre>
/// ARI, ARF
/// Receivables DR
/// Charge CR
/// TaxDue CR
///Revenue CR
/// ARC
/// Receivables CR
/// Charge DR
/// TaxDue DR
/// Revenue RR
///
/// API
/// Payables CR
/// Charge DR
/// TaxCredit DR
/// Expense DR
///
/// APC
/// Payables DR
/// Charge CR
/// TaxCredit CR
/// Expense CR
/// </pre>
/// </summary>
/// <param name="as1">accounting schema</param>
/// <returns> Fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
//
List <Fact> facts = new List <Fact>();
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
// Cash based accounting
if (!as1.IsAccrual())
{
return(facts);
}
// ** ARI, ARF
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_ARINVOICE) ||
GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_ARPROFORMAINVOICE))
{
Decimal grossAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
Decimal serviceAmt = Env.ZERO;
// Header Charge CR
Decimal amt = GetAmount(Doc.AMTTYPE_Charge).Value;
if (Env.Signum(amt) != 0)
{
fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_Charge, as1),
GetC_Currency_ID(), null, amt);
}
// TaxDue CR
for (int i = 0; i < _taxes.Length; i++)
{
amt = _taxes[i].GetAmount();
if (Env.Signum(amt) != 0)
{
FactLine tl = fact.CreateLine(null, _taxes[i].GetAccount(DocTax.ACCTTYPE_TaxDue, as1),
GetC_Currency_ID(), null, amt);
if (tl != null)
{
tl.SetC_Tax_ID(_taxes[i].GetC_Tax_ID());
}
}
}
// Revenue CR
for (int i = 0; i < _lines.Length; i++)
{
amt = _lines[i].GetAmtSource();
Decimal?dAmt = null;
if (as1.IsTradeDiscountPosted())
{
Decimal discount = _lines[i].GetDiscount();
if (Env.Signum(discount) != 0)
{
amt = Decimal.Add(amt, discount);
dAmt = discount;
}
}
fact.CreateLine(_lines[i],
_lines[i].GetAccount(ProductCost.ACCTTYPE_P_Revenue, as1),
GetC_Currency_ID(), dAmt, amt);
if (!_lines[i].IsItem())
{
grossAmt = Decimal.Subtract(grossAmt, amt);
serviceAmt = Decimal.Add(serviceAmt, amt);
}
}
// Set Locations
FactLine[] fLines = fact.GetLines();
for (int i = 0; i < fLines.Length; i++)
{
if (fLines[i] != null)
{
fLines[i].SetLocationFromOrg(fLines[i].GetAD_Org_ID(), true); // from Loc
fLines[i].SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc
}
}
// Receivables DR
int receivables_ID = GetValidCombination_ID(Doc.ACCTTYPE_C_Receivable, as1);
int receivablesServices_ID = GetValidCombination_ID(Doc.ACCTTYPE_C_Receivable_Services, as1);
if (_allLinesItem || !as1.IsPostServices() ||
receivables_ID == receivablesServices_ID)
{
grossAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
serviceAmt = Env.ZERO;
}
else if (_allLinesService)
{
serviceAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
grossAmt = Env.ZERO;
}
if (Env.Signum(grossAmt) != 0)
{
fact.CreateLine(null, MAccount.Get(GetCtx(), receivables_ID),
GetC_Currency_ID(), grossAmt, null);
}
if (Env.Signum(serviceAmt) != 0)
{
fact.CreateLine(null, MAccount.Get(GetCtx(), receivablesServices_ID),
GetC_Currency_ID(), serviceAmt, null);
}
}
// ARC
else if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_ARCREDITMEMO))
{
Decimal grossAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
Decimal serviceAmt = Env.ZERO;
// Header Charge DR
Decimal amt = GetAmount(Doc.AMTTYPE_Charge).Value;
if (Env.Signum(amt) != 0)
{
fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_Charge, as1),
GetC_Currency_ID(), amt, null);
}
// TaxDue DR
for (int i = 0; i < _taxes.Length; i++)
{
amt = _taxes[i].GetAmount();
if (Env.Signum(amt) != 0)
{
FactLine tl = fact.CreateLine(null, _taxes[i].GetAccount(DocTax.ACCTTYPE_TaxDue, as1),
GetC_Currency_ID(), amt, null);
if (tl != null)
{
tl.SetC_Tax_ID(_taxes[i].GetC_Tax_ID());
}
}
}
// Revenue CR
for (int i = 0; i < _lines.Length; i++)
{
amt = _lines[i].GetAmtSource();
Decimal?dAmt = null;
if (as1.IsTradeDiscountPosted())
{
Decimal discount = _lines[i].GetDiscount();
if (Env.Signum(discount) != 0)
{
amt = Decimal.Add(amt, discount);
dAmt = discount;
}
}
fact.CreateLine(_lines[i],
_lines[i].GetAccount(ProductCost.ACCTTYPE_P_Revenue, as1),
GetC_Currency_ID(), amt, dAmt);
if (!_lines[i].IsItem())
{
grossAmt = Decimal.Subtract(grossAmt, amt);
serviceAmt = Decimal.Add(serviceAmt, amt);
}
}
// Set Locations
FactLine[] fLines = fact.GetLines();
for (int i = 0; i < fLines.Length; i++)
{
if (fLines[i] != null)
{
fLines[i].SetLocationFromOrg(fLines[i].GetAD_Org_ID(), true); // from Loc
fLines[i].SetLocationFromBPartner(GetC_BPartner_Location_ID(), false); // to Loc
}
}
// Receivables CR
int receivables_ID = GetValidCombination_ID(Doc.ACCTTYPE_C_Receivable, as1);
int receivablesServices_ID = GetValidCombination_ID(Doc.ACCTTYPE_C_Receivable_Services, as1);
if (_allLinesItem || !as1.IsPostServices() ||
receivables_ID == receivablesServices_ID)
{
grossAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
serviceAmt = Env.ZERO;
}
else if (_allLinesService)
{
serviceAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
grossAmt = Env.ZERO;
}
if (Env.Signum(grossAmt) != 0)
{
fact.CreateLine(null, MAccount.Get(GetCtx(), receivables_ID),
GetC_Currency_ID(), null, grossAmt);
}
if (Env.Signum(serviceAmt) != 0)
{
fact.CreateLine(null, MAccount.Get(GetCtx(), receivablesServices_ID),
GetC_Currency_ID(), null, serviceAmt);
}
}
// ** API
else if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_APINVOICE))
{
Decimal grossAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
Decimal serviceAmt = Env.ZERO;
// Charge DR
fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_Charge, as1),
GetC_Currency_ID(), GetAmount(Doc.AMTTYPE_Charge), null);
// TaxCredit DR
for (int i = 0; i < _taxes.Length; i++)
{
FactLine tl = fact.CreateLine(null, _taxes[i].GetAccount(_taxes[i].GetAPTaxType(), as1),
GetC_Currency_ID(), _taxes[i].GetAmount(), null);
if (tl != null)
{
tl.SetC_Tax_ID(_taxes[i].GetC_Tax_ID());
}
}
// Expense DR
for (int i = 0; i < _lines.Length; i++)
{
DocLine line = _lines[i];
bool landedCost = LandedCost(as1, fact, line, true);
if (landedCost && as1.IsExplicitCostAdjustment())
{
fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1),
GetC_Currency_ID(), line.GetAmtSource(), null);
//
FactLine fl = fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1),
GetC_Currency_ID(), null, line.GetAmtSource());
String desc = line.GetDescription();
if (desc == null)
{
desc = "100%";
}
else
{
desc += " 100%";
}
fl.SetDescription(desc);
}
if (!landedCost)
{
MAccount expense = line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1);
if (line.IsItem())
{
expense = line.GetAccount(ProductCost.ACCTTYPE_P_InventoryClearing, as1);
}
Decimal amt = line.GetAmtSource();
Decimal?dAmt = null;
if (as1.IsTradeDiscountPosted() && !line.IsItem())
{
Decimal discount = line.GetDiscount();
if (Env.Signum(discount) != 0)
{
amt = Decimal.Add(amt, discount);
dAmt = discount;
}
}
fact.CreateLine(line, expense, GetC_Currency_ID(), amt, dAmt);
if (!line.IsItem())
{
grossAmt = Decimal.Subtract(grossAmt, amt);
serviceAmt = Decimal.Add(serviceAmt, amt);
}
//
if (line.GetM_Product_ID() != 0 &&
line.GetProduct().IsService()) // otherwise Inv Matching
{
if (!IsPosted())
{
MCostDetail.CreateInvoice(as1, line.GetAD_Org_ID(),
line.GetM_Product_ID(), line.GetM_AttributeSetInstance_ID(),
line.Get_ID(), 0, // No Cost Element
line.GetAmtSource(), line.GetQty().Value,
line.GetDescription(), GetTrx(), GetRectifyingProcess());
}
}
}
}
// Set Locations
FactLine[] fLines = fact.GetLines();
for (int i = 0; i < fLines.Length; i++)
{
if (fLines[i] != null)
{
fLines[i].SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc
fLines[i].SetLocationFromOrg(fLines[i].GetAD_Org_ID(), false); // to Loc
}
}
// Liability CR
int payables_ID = GetValidCombination_ID(Doc.ACCTTYPE_V_Liability, as1);
int payablesServices_ID = GetValidCombination_ID(Doc.ACCTTYPE_V_Liability_Services, as1);
if (_allLinesItem || !as1.IsPostServices() ||
payables_ID == payablesServices_ID)
{
grossAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
serviceAmt = Env.ZERO;
}
else if (_allLinesService)
{
serviceAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
grossAmt = Env.ZERO;
}
if (Env.Signum(grossAmt) != 0)
{
fact.CreateLine(null, MAccount.Get(GetCtx(), payables_ID),
GetC_Currency_ID(), null, grossAmt);
}
if (Env.Signum(serviceAmt) != 0)
{
fact.CreateLine(null, MAccount.Get(GetCtx(), payablesServices_ID),
GetC_Currency_ID(), null, serviceAmt);
}
//
UpdateProductPO(as1); // Only API
}
// APC
else if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_APCREDITMEMO))
{
Decimal grossAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
Decimal serviceAmt = Env.ZERO;
// Charge CR
fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_Charge, as1),
GetC_Currency_ID(), null, GetAmount(Doc.AMTTYPE_Charge));
// TaxCredit CR
for (int i = 0; i < _taxes.Length; i++)
{
FactLine tl = fact.CreateLine(null, _taxes[i].GetAccount(_taxes[i].GetAPTaxType(), as1),
GetC_Currency_ID(), null, _taxes[i].GetAmount());
if (tl != null)
{
tl.SetC_Tax_ID(_taxes[i].GetC_Tax_ID());
}
}
// Expense CR
for (int i = 0; i < _lines.Length; i++)
{
DocLine line = _lines[i];
bool landedCost = LandedCost(as1, fact, line, false);
if (landedCost && as1.IsExplicitCostAdjustment())
{
fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1),
GetC_Currency_ID(), null, line.GetAmtSource());
//
FactLine fl = fact.CreateLine(line, line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1),
GetC_Currency_ID(), line.GetAmtSource(), null);
String desc = line.GetDescription();
if (desc == null)
{
desc = "100%";
}
else
{
desc += " 100%";
}
fl.SetDescription(desc);
}
if (!landedCost)
{
MAccount expense = line.GetAccount(ProductCost.ACCTTYPE_P_Expense, as1);
if (line.IsItem())
{
expense = line.GetAccount(ProductCost.ACCTTYPE_P_InventoryClearing, as1);
}
Decimal amt = line.GetAmtSource();
Decimal?dAmt = null;
if (as1.IsTradeDiscountPosted() && !line.IsItem())
{
Decimal discount = line.GetDiscount();
if (Env.Signum(discount) != 0)
{
amt = Decimal.Add(amt, discount);
dAmt = discount;
}
}
fact.CreateLine(line, expense,
GetC_Currency_ID(), dAmt, amt);
if (!line.IsItem())
{
grossAmt = Decimal.Subtract(grossAmt, amt);
serviceAmt = Decimal.Add(serviceAmt, amt);
}
//
if (line.GetM_Product_ID() != 0 &&
line.GetProduct().IsService()) // otherwise Inv Matching
{
if (!IsPosted())
{
MCostDetail.CreateInvoice(as1, line.GetAD_Org_ID(),
line.GetM_Product_ID(), line.GetM_AttributeSetInstance_ID(),
line.Get_ID(), 0, // No Cost Element
Decimal.Negate(line.GetAmtSource()), line.GetQty().Value,
line.GetDescription(), GetTrx(), GetRectifyingProcess());
}
}
}
}
// Set Locations
FactLine[] fLines = fact.GetLines();
for (int i = 0; i < fLines.Length; i++)
{
if (fLines[i] != null)
{
fLines[i].SetLocationFromBPartner(GetC_BPartner_Location_ID(), true); // from Loc
fLines[i].SetLocationFromOrg(fLines[i].GetAD_Org_ID(), false); // to Loc
}
}
// Liability DR
int payables_ID = GetValidCombination_ID(Doc.ACCTTYPE_V_Liability, as1);
int payablesServices_ID = GetValidCombination_ID(Doc.ACCTTYPE_V_Liability_Services, as1);
if (_allLinesItem || !as1.IsPostServices() ||
payables_ID == payablesServices_ID)
{
grossAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
serviceAmt = Env.ZERO;
}
else if (_allLinesService)
{
serviceAmt = GetAmount(Doc.AMTTYPE_Gross).Value;
grossAmt = Env.ZERO;
}
if (Env.Signum(grossAmt) != 0)
{
fact.CreateLine(null, MAccount.Get(GetCtx(), payables_ID),
GetC_Currency_ID(), grossAmt, null);
}
if (Env.Signum(serviceAmt) != 0)
{
fact.CreateLine(null, MAccount.Get(GetCtx(), payablesServices_ID),
GetC_Currency_ID(), serviceAmt, null);
}
}
else
{
_error = "DocumentType unknown: " + GetDocumentType();
log.Log(Level.SEVERE, _error);
fact = null;
}
//
facts.Add(fact);
return(facts);
}
/// <summary>
/// Create Facts (the accounting logic) for
/// CMB.
/// <pre>
/// BankAsset DR CR (Statement)
/// BankInTransit DR CR (Payment)
/// Charge DR (Charge)
/// Interest DR CR (Interest)
/// </pre>
/// </summary>
/// <param name="as1"></param>
/// <returns></returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
// Header -- there may be different currency amounts
FactLine fl = null;
int AD_Org_ID = GetBank_Org_ID(); // Bank Account Org
bool addPost = false;
//Check For Module
Tuple <String, String, String> aInfo = null;
if (Env.HasModulePrefix("ED008_", out aInfo))
{
addPost = true;
}
else
{
addPost = false;
}
// Posting Work Done For ED008 Module
if (addPost == true)
{
for (int i = 0; i < _lines.Length; i++)
{
DocLine_Bank line = (DocLine_Bank)_lines[i];
int C_BPartner_ID = line.GetC_BPartner_ID();
int C_Payment_ID = line.GetC_Payment_ID();
// BankAsset DR CR (Statement)
fl = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_BankAsset, as1), line.GetC_Currency_ID(), line.GetStmtAmt());
if (fl != null && AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
if (fl != null && C_BPartner_ID != 0)
{
fl.SetC_BPartner_ID(C_BPartner_ID);
}
// BankInTransit DR CR (Payment)
MAccount acct = null;
string tenderType = Util.GetValueOfString(DB.ExecuteScalar("SELECT tendertype FROM C_Payment WHERE C_Payment_ID=" + C_Payment_ID + " AND AD_Client_ID = " + GetAD_Client_ID()));
// Tender Type RIBA
if ("R".Equals(tenderType))
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_RIBA_Acct FROM C_BankAccount_Acct WHERE C_BankAccount_ID=" + GetC_BankAccount_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (validComID > 0)
{
acct = MAccount.Get(Env.GetCtx(), validComID);
}
if (acct == null)
{
validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_RIBA_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
acct = MAccount.Get(Env.GetCtx(), validComID);
}
}
// Tender Type MAV
else if ("M".Equals(tenderType))
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_MAV_Acct FROM C_BankAccount_Acct WHERE C_BankAccount_ID=" + GetC_BankAccount_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (validComID > 0)
{
acct = MAccount.Get(Env.GetCtx(), validComID);
}
if (acct == null)
{
validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_MAV_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
acct = MAccount.Get(Env.GetCtx(), validComID);
}
}
// Tender Type RID
else if ("I".Equals(tenderType))
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_RID_Acct FROM C_BankAccount_Acct WHERE C_BankAccount_ID=" + GetC_BankAccount_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (validComID > 0)
{
acct = MAccount.Get(Env.GetCtx(), validComID);
}
if (acct == null)
{
validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_RID_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
acct = MAccount.Get(Env.GetCtx(), validComID);
}
}
else
{
acct = GetAccount(Doc.ACCTTYPE_BankInTransit, as1);
}
fl = fact.CreateLine(line, acct, line.GetC_Currency_ID(), Decimal.Negate(line.GetTrxAmt()));
if (fl != null)
{
if (C_BPartner_ID != 0)
{
fl.SetC_BPartner_ID(C_BPartner_ID);
}
if (AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
else
{
fl.SetAD_Org_ID(line.GetAD_Org_ID(true)); // from payment
}
}
// Charge DR (Charge)
fl = fact.CreateLine(line, line.GetChargeAccount(as1, Decimal.Negate(line.GetChargeAmt())), line.GetC_Currency_ID(), Decimal.Negate(line.GetChargeAmt()), null);
if (fl != null && C_BPartner_ID != 0)
{
fl.SetC_BPartner_ID(C_BPartner_ID);
}
// Interest DR CR (Interest)
if (Env.Signum(line.GetInterestAmt()) < 0)
{
fl = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_InterestExp, as1), GetAccount(Doc.ACCTTYPE_InterestExp, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetInterestAmt()));
}
else
{
fl = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_InterestRev, as1), GetAccount(Doc.ACCTTYPE_InterestRev, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetInterestAmt()));
}
if (fl != null && C_BPartner_ID != 0)
{
fl.SetC_BPartner_ID(C_BPartner_ID);
}
}
}
// Default Posting Logic
else
{
// Lines
for (int i = 0; i < _lines.Length; i++)
{
DocLine_Bank line = (DocLine_Bank)_lines[i];
int C_BPartner_ID = line.GetC_BPartner_ID();
// BankAsset DR CR (Statement)
fl = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_BankAsset, as1), line.GetC_Currency_ID(), line.GetStmtAmt());
if (fl != null && AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
if (fl != null && C_BPartner_ID != 0)
{
fl.SetC_BPartner_ID(C_BPartner_ID);
}
// BankInTransit DR CR (Payment)
fl = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_BankInTransit, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetTrxAmt()));
if (fl != null)
{
if (C_BPartner_ID != 0)
{
fl.SetC_BPartner_ID(C_BPartner_ID);
}
if (AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
else
{
fl.SetAD_Org_ID(line.GetAD_Org_ID(true)); // from payment
}
}
// Charge DR (Charge)
fl = fact.CreateLine(line, line.GetChargeAccount(as1, Decimal.Negate(line.GetChargeAmt())), line.GetC_Currency_ID(), Decimal.Negate(line.GetChargeAmt()), null);
if (fl != null && C_BPartner_ID != 0)
{
fl.SetC_BPartner_ID(C_BPartner_ID);
}
// Interest DR CR (Interest)
if (Env.Signum(line.GetInterestAmt()) < 0)
{
fl = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_InterestExp, as1), GetAccount(Doc.ACCTTYPE_InterestExp, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetInterestAmt()));
}
else
{
fl = fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_InterestRev, as1), GetAccount(Doc.ACCTTYPE_InterestRev, as1), line.GetC_Currency_ID(), Decimal.Negate(line.GetInterestAmt()));
}
if (fl != null && C_BPartner_ID != 0)
{
fl.SetC_BPartner_ID(C_BPartner_ID);
}
//
// fact.createTaxCorrection();
}
}
//
List <Fact> facts = new List <Fact>();
facts.Add(fact);
return(facts);
}
} // 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
} // copyDefault
/// <summary>
/// Create Account
/// </summary>
/// <param name="targetAS">target AS</param>
/// <param name="sourceAcct">source account</param>
/// <returns>target account</returns>
private MAccount CreateAccount(MAcctSchema targetAS, MAccount sourceAcct)
{
int AD_Client_ID = targetAS.GetAD_Client_ID();
int C_AcctSchema_ID = targetAS.GetC_AcctSchema_ID();
//
int AD_Org_ID = 0;
int Account_ID = 0;
int C_SubAcct_ID = 0;
int M_Product_ID = 0;
int C_BPartner_ID = 0;
int AD_OrgTrx_ID = 0;
int C_LocFrom_ID = 0;
int C_LocTo_ID = 0;
int C_SalesRegion_ID = 0;
int C_Project_ID = 0;
int C_Campaign_ID = 0;
int C_Activity_ID = 0;
int User1_ID = 0;
int User2_ID = 0;
int UserElement1_ID = 0;
int UserElement2_ID = 0;
//
// Active Elements
MAcctSchemaElement[] elements = targetAS.GetAcctSchemaElements();
for (int i = 0; i < elements.Length; i++)
{
MAcctSchemaElement ase = elements[i];
String elementType = ase.GetElementType();
//
if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Organization))
{
AD_Org_ID = sourceAcct.GetAD_Org_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Account))
{
Account_ID = sourceAcct.GetAccount_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_SubAccount))
{
C_SubAcct_ID = sourceAcct.GetC_SubAcct_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_BPartner))
{
C_BPartner_ID = sourceAcct.GetC_BPartner_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Product))
{
M_Product_ID = sourceAcct.GetM_Product_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Activity))
{
C_Activity_ID = sourceAcct.GetC_Activity_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_LocationFrom))
{
C_LocFrom_ID = sourceAcct.GetC_LocFrom_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_LocationTo))
{
C_LocTo_ID = sourceAcct.GetC_LocTo_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Campaign))
{
C_Campaign_ID = sourceAcct.GetC_Campaign_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_OrgTrx))
{
AD_OrgTrx_ID = sourceAcct.GetAD_OrgTrx_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_Project))
{
C_Project_ID = sourceAcct.GetC_Project_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_SalesRegion))
{
C_SalesRegion_ID = sourceAcct.GetC_SalesRegion_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_UserList1))
{
User1_ID = sourceAcct.GetUser1_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_UserList2))
{
User2_ID = sourceAcct.GetUser2_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_UserElement1))
{
UserElement1_ID = sourceAcct.GetUserElement1_ID();
}
else if (elementType.Equals(MAcctSchemaElement.ELEMENTTYPE_UserElement2))
{
UserElement2_ID = sourceAcct.GetUserElement2_ID();
}
// No UserElement
}
//
return(MAccount.Get(GetCtx(), AD_Client_ID, AD_Org_ID,
C_AcctSchema_ID, Account_ID, C_SubAcct_ID,
M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
C_LocFrom_ID, C_LocTo_ID, C_SalesRegion_ID,
C_Project_ID, C_Campaign_ID, C_Activity_ID,
User1_ID, User2_ID, UserElement1_ID, UserElement2_ID));
} // createAccount
/// <summary>
/// Create Facts (the accounting logic) for
/// CMC.
/// <pre>
/// Expense
/// CashExpense DR
/// CashAsset CR
///Receipt
/// CashAsset DR
/// CashReceipt CR
/// Charge
/// Charge DR
/// CashAsset CR
/// Difference
/// CashDifference DR
/// CashAsset CR
/// Invoice
/// CashAsset DR
/// CashTransfer CR
/// Transfer
/// BankInTransit DR
/// CashAsset CR
/// </pre>
/// </summary>
/// <param name="?"></param>
/// <returns>Fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// Need to have CashBook
if (GetC_CashBook_ID() == 0)
{
_error = "C_CashBook_ID not set";
log.Log(Level.SEVERE, _error);
return(null);
}
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
// Header posting amt as1 Invoices and Transfer could be differenet currency
// CashAsset Total
Decimal assetAmt = Env.ZERO;
Decimal TotalCurrLoss = Env.ZERO;
Decimal TotalCurrGain = Env.ZERO;
int headerCashCurrency = 0;
int childCashCurrency = 0;
int headerCashOrg = 0;
int childCashOrg = 0;
// Lines
for (int i = 0; i < _lines.Length; i++)
{
DocLine_Cash line = (DocLine_Cash)_lines[i];
String CashType = line.GetCashType();
if (CashType.Equals(DocLine_Cash.CASHTYPE_EXPENSE))
{ // amount is negative
// CashExpense DR
// CashAsset CR
fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashExpense, as1),
GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null);
// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// p_vo.C_Currency_ID, null, line.GetAmount().negate());
assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount()));
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_RECEIPT))
{ // amount is positive
// CashAsset DR
// CashReceipt CR
// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// p_vo.C_Currency_ID, line.GetAmount(), null);
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashReceipt, as1),
GetC_Currency_ID(), null, line.GetAmount());
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_CHARGE))
{ // amount is negative
// Charge DR
// CashAsset CR
fact.CreateLine(line, line.GetChargeAccount(as1, GetAmount()),
GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null);
// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// p_vo.C_Currency_ID, null, line.GetAmount().negate());
assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount()));
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_DIFFERENCE))
{ // amount is pos/neg
// CashDifference DR
// CashAsset CR
fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashDifference, as1),
GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// p_vo.C_Currency_ID, line.GetAmount());
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_INVOICE))
{ // amount is pos/neg
// CashAsset DR dr -- Invoice is in Invoice Currency !
// CashTransfer cr CR
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashAsset, as1),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_TRANSFER))
{ // amount is pos/neg
// BankInTransit DR dr -- Transfer is in Bank Account Currency
// CashAsset dr CR
int temp = GetC_BankAccount_ID();
SetC_BankAccount_ID(line.GetC_BankAccount_ID());
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_BankInTransit, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
SetC_BankAccount_ID(temp);
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashAsset, as1),
line.GetC_Currency_ID(), line.GetAmount());
}
}
// Change to Apply Posting Logic against BusinessPartner
else if (CashType.Equals(DocLine_Cash.CASHTYPE_BUSINESSPARTNER))
{ // amount is pos/neg
// CashAsset DR dr -- Invoice is in Invoice Currency !
// CashTransfer cr CR
MBPartner bPartner = new MBPartner(GetCtx(), line.GetC_BPartner_ID(), null);
if (bPartner != null)
{
if (bPartner.IsEmployee())
{
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_E_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_E_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
else if (bPartner.IsVendor())
{
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_V_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_V_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
else if (bPartner.IsCustomer())
{
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_C_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_C_Prepayment, as1, line.GetC_BPartner_ID()),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
}
else
{
if (line.GetC_Currency_ID() == GetC_Currency_ID())
{
assetAmt = Decimal.Add(assetAmt, line.GetAmount());
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashAsset, as1),
line.GetC_Currency_ID(), line.GetAmount());
}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
}
//Code Added to CashBookTransfer
else if (CashType.Equals(DocLine_Cash.CASHTYPE_CASHBOOKTRANSFER))
{
//// amount is negative
//// Charge DR
//// CashAsset CR
//fact.CreateLine(line, line.GetChargeAccount(as1, GetAmount()),
// GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), null);
//// fact.CreateLine(line, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
//// p_vo.C_Currency_ID, null, line.GetAmount().negate());
//assetAmt = Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount()));
int temp = GetC_CashBook_ID();
SetC_CashBook_ID(line.Get_C_CashBook_ID());
if (BeginningBalance > 0)
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
SetC_CashBook_ID(temp);
//if (line.GetC_Currency_ID() == (new MCashBook(GetCtx(), GetC_CashBook_ID(), null)).GetC_Currency_ID())// GetC_Currency_ID())
//{
//if (BeginningBalance > assetAmt)
//if (BeginningBalance >= Math.Abs(line.GetAmount()))
//if (assetAmt <= Math.Abs(line.GetAmount()))
//{
assetAmt = (Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount())));
//}
//else
//{
// assetAmt = (Decimal.Subtract(assetAmt, Decimal.Negate(line.GetAmount())));
//}
//}
//else
//{
// fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
//}
}
else
{
//fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
// line.GetC_Currency_ID(), decimal.Negate(line.GetAmount()));
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
(new MCashBook(GetCtx(), HeaderCasbookID, null)).GetC_Currency_ID(), decimal.Negate(line.GetAmount()));
SetC_CashBook_ID(temp);
//if (line.GetC_Currency_ID() == (new MCashBook(GetCtx(), GetC_CashBook_ID(), null)).GetC_Currency_ID())// GetC_Currency_ID())
//{
assetAmt = (Decimal.Add(assetAmt, line.GetAmount()));
//}
//else
//{
// fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
//}
}
}
else if (CashType.Equals(DocLine_Cash.CASHTYPE_CASHRECIEVEDFROM))
{
int temp = GetC_CashBook_ID();
//SetC_CashBook_ID(line.Get_C_CashBook_ID());
//fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
// line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
//SetC_CashBook_ID(temp);
//if (line.GetC_Currency_ID() == GetC_Currency_ID())
//{
//assetAmt = (Decimal.Add(assetAmt, line.GetAmount()));
//}
//else
//{
// fact.CreateLine(line,
// GetAccount(Doc.ACCTTYPE_CashAsset, as1),
// line.GetC_Currency_ID(), line.GetAmount());
//}
headerCashCurrency = (new MCashBook(GetCtx(), HeaderCasbookID, null).GetC_Currency_ID());
childCashCurrency = (new MCashBook(GetCtx(), line.Get_C_CashBook_ID(), null).GetC_Currency_ID());
headerCashOrg = (new MCashBook(GetCtx(), HeaderCasbookID, null).GetAD_Org_ID());
childCashOrg = (new MCashBook(GetCtx(), line.Get_C_CashBook_ID(), null).GetAD_Org_ID());
//else
//{
if (headerCashCurrency != childCashCurrency)
{
Decimal transferdAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT Amount FROM C_CashLine WHERE C_CashLine_ID =" + line.Get_C_CashLine_Ref_ID()));
Decimal recievedAmt = MConversionRate.Convert(GetCtx(), line.GetAmount(), headerCashCurrency, childCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
Decimal res = Decimal.Subtract(recievedAmt, Math.Abs(transferdAmt));
if (res > 0)
{
TotalCurrGain = Decimal.Add(TotalCurrGain, res);
}
else if (res < 0)
{
TotalCurrLoss = Decimal.Add(TotalCurrLoss, Decimal.Negate(res));
}
SetC_CashBook_ID(line.Get_C_CashBook_ID());
int OrgID = line.GetAD_Org_ID();
if (headerCashOrg != childCashOrg)
{
OrgID = childCashOrg;
}
//else
//{
transferdAmt = MConversionRate.Convert(GetCtx(), transferdAmt, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
//}
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
headerCashCurrency, transferdAmt, OrgID);
SetC_CashBook_ID(temp);
}
else
{
SetC_CashBook_ID(line.Get_C_CashBook_ID());
if (headerCashOrg != childCashOrg)
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()), childCashOrg);
}
else
{
fact.CreateLine(line,
GetAccount(Doc.ACCTTYPE_CashTransfer, as1),
line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
}
SetC_CashBook_ID(temp);
}
//if (headerCashOrg != childCashOrg)
//{
// DataSet ds = DB.ExecuteDataset("SELECT INTERCOMPANYDUETO_ACCT,INTERCOMPANYDUEFROM_ACCT FROM C_AcctSchema_GL WHERE AD_Client_ID=" + GetAD_Client_ID());
// int dueFrom = 0;
// int dueTo = 0;
// if (ds != null && ds.Tables[0].Rows.Count > 0)
// {
// dueTo = Util.GetValueOfInt(ds.Tables[0].Rows[0]["INTERCOMPANYDUETO_ACCT"]);
// dueFrom = Util.GetValueOfInt(ds.Tables[0].Rows[0]["INTERCOMPANYDUEFROM_ACCT"]);
// if (dueFrom > 0 && dueTo > 0)
// {
// fact.CreateLine(line, MAccount.Get(GetCtx(), dueFrom),line.GetC_Currency_ID(), line.GetAmount());
// fact.CreateLine(line, MAccount.Get(GetCtx(), dueTo), line.GetC_Currency_ID(), Decimal.Negate(line.GetAmount()));
// }
// }
//}
//}
assetAmt = (Decimal.Add(assetAmt, line.GetAmount()));
}
} // lines
if (TotalCurrGain != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='80540' AND AD_Client_ID = " + GetAD_Client_ID() + " )"));
MAccount acct = MAccount.Get(GetCtx(), validComID);
TotalCurrGain = MConversionRate.Convert(GetCtx(), TotalCurrGain, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
fact.CreateLine(null, acct,
GetC_Currency_ID(), Decimal.Negate(TotalCurrGain));
}
if (TotalCurrLoss != Env.ZERO)
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT C_ValidCombination_ID FROM C_ValidCombination WHERE Account_ID= ( SELECT C_ElementValue_ID FROM C_ElementValue WHERE Value='82540' AND AD_Client_ID = " + GetAD_Client_ID() + " )"));
MAccount acct = MAccount.Get(GetCtx(), validComID);
TotalCurrLoss = MConversionRate.Convert(GetCtx(), TotalCurrLoss, childCashCurrency, headerCashCurrency, GetAD_Client_ID(), GetAD_Org_ID());
fact.CreateLine(null, acct,
GetC_Currency_ID(), (TotalCurrLoss));
}
//
// Cash Asset
fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_CashAsset, as1),
GetC_Currency_ID(), assetAmt);
List <Fact> facts = new List <Fact>();
facts.Add(fact);
return(facts);
}
/**
* Get Charge Account
* @param C_Charge_ID charge
* @param as account schema
* @param amount amount for expense(+)/revenue(-)
* @return Charge Account or null
*/
public static MAccount GetAccount(int C_Charge_ID, MAcctSchema aSchema, Decimal amount)
{
if (C_Charge_ID == 0 || aSchema == null)
{
return(null);
}
int acct_index = 1; // Expense (positive amt)
if (amount < 0)
{
acct_index = 2; // Revenue (negative amt)
}
String sql = "SELECT CH_Expense_Acct, CH_Revenue_Acct FROM C_Charge_Acct WHERE C_Charge_ID=" + C_Charge_ID + " AND C_AcctSchema_ID=" + aSchema.GetC_AcctSchema_ID();
int Account_ID = 0;
IDataReader dr = null;
try
{
// PreparedStatement pstmt = DataBase.prepareStatement(sql, null);
// pstmt.setInt (1, C_Charge_ID);
// pstmt.setInt (2, aSchema.getC_AcctSchema_ID());
// ResultSet dr = pstmt.executeQuery();
dr = DB.ExecuteReader(sql, null, null);
if (dr.Read())
{
Account_ID = Util.GetValueOfInt(dr[acct_index - 1].ToString());
}
dr.Close();
//pstmt.close();
}
catch (SqlException e)
{
if (dr != null)
{
dr.Close();
}
_log.Log(Level.SEVERE, sql, e);
return(null);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
// No account
if (Account_ID == 0)
{
_log.Severe("NO account for C_Charge_ID=" + C_Charge_ID);
return(null);
}
// Return Account
MAccount acct = MAccount.Get(aSchema.GetCtx(), Account_ID);
return(acct);
} // getAccount
/// <summary>
/// Update Default Account.
//This is the sql to delete unused accounts - with the import still in the table(!):
//DELETE C_ElementValue e
//WHERE NOT EXISTS (SELECT * FROM Fact_Acct f WHERE f.Account_ID=e.C_ElementValue_ID)
// AND NOT EXISTS (SELECT * FROM C_ValidCombination vc WHERE vc.Account_ID=e.C_ElementValue_ID)
// AND NOT EXISTS (SELECT * FROM I_ElementValue i WHERE i.C_ElementValue_ID=e.C_ElementValue_ID);
/// </summary>
/// <param name="TableName">trx</param>
/// <param name="ColumnName">table name</param>
/// <param name="C_AcctSchema_ID">column name</param>
/// <param name="C_ElementValue_ID">account schema</param>
/// <returns>update * statues</returns>
private int UpdateDefaultAccount(String TableName, String ColumnName, int C_AcctSchema_ID, int C_ElementValue_ID)
{
log.Fine(TableName + "." + ColumnName + " - " + C_ElementValue_ID);
int retValue = UPDATE_ERROR;
StringBuilder sql = new StringBuilder("SELECT x.")
.Append(ColumnName).Append(",Account_ID FROM ")
.Append(TableName).Append(" x INNER JOIN C_ValidCombination vc ON (x.")
.Append(ColumnName).Append("=vc.C_ValidCombination_ID) ")
.Append("WHERE x.C_AcctSchema_ID=").Append(C_AcctSchema_ID);
IDataReader idr = null;
try
{
//PreparedStatement pstmt = DataBase.prepareStatement(sql.ToString(), Get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
if (idr.Read())
{
int C_ValidCombination_ID = Utility.Util.GetValueOfInt(idr[0]); // rs.getInt(1);
int Account_ID = Utility.Util.GetValueOfInt(idr[1]); // rs.getInt(2);
// The current account value is the same
if (Account_ID == C_ElementValue_ID)
{
retValue = UPDATE_SAME;
log.Fine("Account_ID same as new value");
}
// We need to update the Account Value
else
{
if (_createNewCombination)
{
MAccount acct = MAccount.Get(GetCtx(), C_ValidCombination_ID);
acct.SetAccount_ID(C_ElementValue_ID);
if (acct.Save())
{
int newC_ValidCombination_ID = acct.GetC_ValidCombination_ID();
if (C_ValidCombination_ID != newC_ValidCombination_ID)
{
sql = new StringBuilder("UPDATE ").Append(TableName)
.Append(" SET ").Append(ColumnName).Append("=").Append(newC_ValidCombination_ID)
.Append(" WHERE C_AcctSchema_ID=").Append(C_AcctSchema_ID);
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("New #" + no + " - "
+ TableName + "." + ColumnName + " - " + C_ElementValue_ID
+ " -- " + C_ValidCombination_ID + " -> " + newC_ValidCombination_ID);
if (no == 1)
{
retValue = UPDATE_YES;
}
}
}
else
{
log.Log(Level.SEVERE, "Account not saved - " + acct);
}
}
else // Replace Combination
{
// Only Acct Combination directly
sql = new StringBuilder("UPDATE C_ValidCombination SET Account_ID=")
.Append(C_ElementValue_ID).Append(" WHERE C_ValidCombination_ID=").Append(C_ValidCombination_ID);
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Replace #" + no + " - "
+ "C_ValidCombination_ID=" + C_ValidCombination_ID + ", New Account_ID=" + C_ElementValue_ID);
if (no == 1)
{
retValue = UPDATE_YES;
// Where Acct was used
sql = new StringBuilder("UPDATE C_ValidCombination SET Account_ID=")
.Append(C_ElementValue_ID).Append(" WHERE Account_ID=").Append(Account_ID);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("ImportAccount.updateDefaultAccount - Replace VC #" + no + " - "
+ "Account_ID=" + Account_ID + ", New Account_ID=" + C_ElementValue_ID);
sql = new StringBuilder("UPDATE Fact_Acct SET Account_ID=")
.Append(C_ElementValue_ID).Append(" WHERE Account_ID=").Append(Account_ID);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("ImportAccount.updateDefaultAccount - Replace Fact #" + no + " - "
+ "Account_ID=" + Account_ID + ", New Account_ID=" + C_ElementValue_ID);
}
} // replace combination
} // need to update
} // for all default accounts
else
{
log.Log(Level.SEVERE, "Account not found " + sql);
}
idr.Close();
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql.ToString(), e);
}
return(retValue);
} // updateDefaultAccount
/// <summary>
/// Create Facts (the accounting logic) for
/// ARP, APP.
///<pre>
/// ARP
/// BankInTransit DR
/// UnallocatedCash CR
/// or Charge/C_Prepayment
/// APP
/// PaymentSelect DR
/// or Charge/V_Prepayment
/// BankInTransit CR
/// CashBankTransfer
/// -
/// </pre>
/// </summary>
/// <param name="as1"></param>
/// <returns>fact</returns>
public override List <Fact> CreateFacts(MAcctSchema as1)
{
// create Fact Header
Fact fact = new Fact(this, as1, Fact.POST_Actual);
// Cash Transfer
if ("X".Equals(_TenderType))
{
List <Fact> facts = new List <Fact>();
facts.Add(fact);
return(facts);
}
int AD_Org_ID = GetBank_Org_ID(); // Bank Account Org
if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_ARRECEIPT))
{
// Work done by Bharat for the posting of ED008 Module
bool addPost = false;
//Check For Module
Tuple <String, String, String> aInfo = null;
if (Env.HasModulePrefix("ED008_", out aInfo))
{
addPost = true;
}
else
{
addPost = false;
}
if (addPost == true)
{
// Tender Type RIBA
if ("R".Equals(_TenderType))
{
MAccount acct = null;
MAccount portAcct = null;
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_RIBA_Acct FROM C_BankAccount_Acct WHERE C_BankAccount_ID=" + GetC_BankAccount_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (validComID > 0)
{
acct = MAccount.Get(Env.GetCtx(), validComID);
}
if (acct == null)
{
validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_RIBA_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
acct = MAccount.Get(Env.GetCtx(), validComID);
}
FactLine fl = fact.CreateLine(null, acct, GetC_Currency_ID(), GetAmount(), null);
int ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_Portfolio_Acct FROM C_BP_Customer_Acct WHERE C_BPartner_ID=" + GetC_BPartner_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (ComID > 0)
{
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
if (portAcct == null)
{
ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_Portfolio_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
fl = fact.CreateLine(null, portAcct, GetC_Currency_ID(), null, GetAmount());
}
// Tender Type MAV
else if ("M".Equals(_TenderType))
{
MAccount acct = null;
MAccount portAcct = null;
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_MAV_Acct FROM C_BankAccount_Acct WHERE C_BankAccount_ID=" + GetC_BankAccount_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (validComID > 0)
{
acct = MAccount.Get(Env.GetCtx(), validComID);
}
if (acct == null)
{
validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_MAV_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
acct = MAccount.Get(Env.GetCtx(), validComID);
}
FactLine fl = fact.CreateLine(null, acct,
GetC_Currency_ID(), GetAmount(), null);
int ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_Portfolio_Acct FROM C_BP_Customer_Acct WHERE C_BPartner_ID=" + GetC_BPartner_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (ComID > 0)
{
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
if (portAcct == null)
{
ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_Portfolio_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
fl = fact.CreateLine(null, portAcct,
GetC_Currency_ID(), null, GetAmount());
}
// Tender Type BOE
else if ("E".Equals(_TenderType))
{
MAccount acct = null;
MAccount portAcct = null;
string boeType = Util.GetValueOfString(DB.ExecuteScalar(@"SELECT ED008_BOEType FROM C_Payment WHERE C_Payment_ID = " + Get_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
int boeID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED008_BOE_ID FROM C_Payment WHERE C_Payment_ID = " + Get_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if ("T".Equals(boeType))
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOE_Acct FROM ED008_BOEAccounting WHERE ED008_BOE_ID=" + boeID + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (validComID > 0)
{
acct = MAccount.Get(Env.GetCtx(), validComID);
}
if (acct == null)
{
validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOE_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
acct = MAccount.Get(Env.GetCtx(), validComID);
}
FactLine fl = fact.CreateLine(null, acct, GetC_Currency_ID(), GetAmount(), null);
int ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOETransit_Acct FROM ED008_BOEAccounting WHERE ED008_BOE_ID=" + boeID + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (ComID > 0)
{
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
if (portAcct == null)
{
ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOETransit_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
fl = fact.CreateLine(null, portAcct, GetC_Currency_ID(), null, GetAmount());
}
else if ("R".Equals(boeType))
{
FactLine fl = fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_BankInTransit, as1), GetC_Currency_ID(), GetAmount(), null);
int ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOE_Acct FROM ED008_BOEAccounting WHERE ED008_BOE_ID=" + boeID + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (ComID > 0)
{
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
if (portAcct == null)
{
ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOE_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
fl = fact.CreateLine(null, portAcct, GetC_Currency_ID(), null, GetAmount());
}
}
// Tender Type RID
else if ("I".Equals(_TenderType))
{
MAccount acct = null;
MAccount portAcct = null;
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_RID_Acct FROM C_BankAccount_Acct WHERE C_BankAccount_ID=" + GetC_BankAccount_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (validComID > 0)
{
acct = MAccount.Get(Env.GetCtx(), validComID);
}
if (acct == null)
{
validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_RID_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
acct = MAccount.Get(Env.GetCtx(), validComID);
}
FactLine fl = fact.CreateLine(null, acct,
GetC_Currency_ID(), GetAmount(), null);
int ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_Portfolio_Acct FROM C_BP_Customer_Acct WHERE C_BPartner_ID=" + GetC_BPartner_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (ComID > 0)
{
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
if (portAcct == null)
{
ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_Portfolio_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
fl = fact.CreateLine(null, portAcct,
GetC_Currency_ID(), null, GetAmount());
}
else
{
// Asset
FactLine fl = fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_BankInTransit, as1),
GetC_Currency_ID(), GetAmount(), null);
if (fl != null && AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
//
MAccount acct = null;
if (GetC_Charge_ID() != 0)
{
acct = MCharge.GetAccount(GetC_Charge_ID(), as1, GetAmount());
}
else if (_Prepayment)
{
acct = GetAccount(Doc.ACCTTYPE_C_Prepayment, as1);
}
else
{
acct = GetAccount(Doc.ACCTTYPE_UnallocatedCash, as1);
}
fl = fact.CreateLine(null, acct, GetC_Currency_ID(), null, GetAmount());
if (fl != null && AD_Org_ID != 0 &&
GetC_Charge_ID() == 0) // don't overwrite charge
{
fl.SetAD_Org_ID(AD_Org_ID);
}
}
}
// Default Posting Logic
else
{
// Asset
FactLine fl = fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_BankInTransit, as1),
GetC_Currency_ID(), GetAmount(), null);
if (fl != null && AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
//
MAccount acct = null;
if (GetC_Charge_ID() != 0)
{
acct = MCharge.GetAccount(GetC_Charge_ID(), as1, GetAmount());
}
else if (_Prepayment)
{
acct = GetAccount(Doc.ACCTTYPE_C_Prepayment, as1);
}
else
{
acct = GetAccount(Doc.ACCTTYPE_UnallocatedCash, as1);
}
fl = fact.CreateLine(null, acct,
GetC_Currency_ID(), null, GetAmount());
if (fl != null && AD_Org_ID != 0 &&
GetC_Charge_ID() == 0) // don't overwrite charge
{
fl.SetAD_Org_ID(AD_Org_ID);
}
}
}
// APP
else if (GetDocumentType().Equals(MDocBaseType.DOCBASETYPE_APPAYMENT))
{
// Work Done For Ed008 Module
bool addPost = false;
//Check For Module
Tuple <String, String, String> aInfo = null;
if (Env.HasModulePrefix("ED008_", out aInfo) || Env.HasModulePrefix("ED010_", out aInfo))
{
addPost = true;
}
if (addPost == true)
{
MAccount acct = null;
MAccount portAcct = null;
string boeType = Util.GetValueOfString(DB.ExecuteScalar(@"SELECT ED008_BOEType FROM C_Payment WHERE C_Payment_ID = " + Get_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
int boeID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED008_BOE_ID FROM C_Payment WHERE C_Payment_ID = " + Get_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
if ("E".Equals(_TenderType))
{
if ("T".Equals(boeType))
{
int validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOETransit_Acct FROM ED008_BOEAccounting WHERE ED008_BOE_ID=" + boeID + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (validComID > 0)
{
acct = MAccount.Get(Env.GetCtx(), validComID);
}
if (acct == null)
{
validComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOETransit_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
acct = MAccount.Get(Env.GetCtx(), validComID);
}
FactLine f2 = fact.CreateLine(null, acct, GetC_Currency_ID(), GetAmount(), null);
int ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOE_Acct FROM ED008_BOEAccounting WHERE ED008_BOE_ID=" + boeID + " AND AD_Client_ID = " + GetAD_Client_ID()));
if (ComID > 0)
{
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
if (portAcct == null)
{
ComID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_BOE_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
portAcct = MAccount.Get(Env.GetCtx(), ComID);
}
f2 = fact.CreateLine(null, portAcct, GetC_Currency_ID(), null, GetAmount());
}
}
else
{
FactLine fl = fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_BankInTransit, as1), GetC_Currency_ID(), null, GetAmount());
if (fl != null && AD_Org_ID != 0 &&
GetC_Charge_ID() == 0) // don't overwrite charge
{
fl.SetAD_Org_ID(AD_Org_ID);
}
DataSet ds = null;
DataSet dsSSCode = null;
decimal amount = 0, WithholdingAmt = 0, sscAmt = 0, payAmt = 0;
payAmt = GetAmount();
string sql = @"SELECT cl.m_product_id, cl.linenetamt,prd.ed010_sscode_id,holddet.ed010_appliedpercentage,holddet.ed010_actualpercentage,withAcct.Withholding_Acct
FROM C_Payment pay INNER JOIN c_invoice inv ON pay.c_invoice_id = inv.c_invoice_id inner join c_invoiceline cl on pay.c_invoice_id=cl.c_invoice_id inner join C_BPartner cb
on pay.c_bpartner_id=cb.c_bpartner_id INNER JOIN m_product prd ON prd.m_product_ID = cl.m_product_ID LEFT JOIN c_withholding hold ON hold.C_WithHolding_Id = prd.c_withholding_id
LEFT JOIN ed010_withholdingdetails holddet ON holddet.C_WithHolding_Id = hold.C_WithHolding_Id left join C_Withholding_Acct withAcct on hold.C_WithHolding_Id=withAcct.C_WithHolding_Id
where cb.ED010_IsWithholding='Y' and cb.ED010_IsSSCode='Y' and pay.ED010_WithholdingAmt > 0 AND " + GlobalVariable.TO_DATE(DateTime.Now.ToLocalTime(), true) +
@"BETWEEN holddet.ED010_FromDate AND holddet.ED010_ToDate and pay.AD_Org_ID = " + GetCtx().GetAD_Org_ID() + " and pay.c_payment_id = " + Get_ID();
ds = new DataSet();
ds = DB.ExecuteDataset(sql, null, null);
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MAccount withAcct = null;
int validID = 0;
amount = (Decimal.Divide(Decimal.Multiply(Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["linenetamt"]), Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["ed010_appliedpercentage"])), 100));
WithholdingAmt = (Decimal.Divide(Decimal.Multiply(amount, Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["ed010_actualpercentage"])), 100));
validID = Util.GetValueOfInt(ds.Tables[0].Rows[i]["Withholding_Acct"]);
if (validID > 0)
{
withAcct = MAccount.Get(Env.GetCtx(), validID);
}
if (withAcct == null)
{
validID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT Withholding_Acct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
withAcct = MAccount.Get(Env.GetCtx(), validID);
}
fl = fact.CreateLine(null, withAcct, GetC_Currency_ID(), null, Decimal.Round(WithholdingAmt, 2));
if (fl != null)
{
payAmt += Decimal.Round(WithholdingAmt, 2);
}
if (fl != null && AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
}
}
}
ds.Dispose();
sql = @"SELECT cl.m_product_id,cb.c_bpartner_id,cl.linenetamt,prd.ed010_sscode_id,sdet.ed010_percentagetype,sdet.ed010_maxamt,sdet.ed010_minamt,sdet.ed010_socialsecurityprcnt,sdet.ed010_orgpercentage,
sdet.ed010_vendorpercentage,sscAcct.ED000_SecCodeAcct FROM C_Payment pay INNER JOIN c_invoice inv ON pay.c_invoice_id = inv.c_invoice_id inner join c_invoiceline cl on pay.c_invoice_id=cl.c_invoice_id inner join C_BPartner cb
on pay.c_bpartner_id=cb.c_bpartner_id INNER JOIN m_product prd ON prd.m_product_ID = cl.m_product_ID LEFT JOIN ed010_sscode scode ON scode.ed010_sscode_ID = prd.ed010_sscode_id
LEFT JOIN ed010_sscodedetails sdet ON sdet.ed010_sscode_ID = scode.ed010_sscode_ID left join ED010_SSCode_Acct sscAcct on scode.ED010_SSCode_ID=sscAcct.ED010_SSCode_ID
where cb.ED010_IsWithholding='Y' and cb.ED010_IsSSCode='Y' and pay.ED010_WithholdingAmt > 0 AND " + GlobalVariable.TO_DATE(DateTime.Now.ToLocalTime(), true) +
@"BETWEEN sdet.ED010_FromDate AND sdet.ED010_ToDate AND pay.AD_Org_ID = " + GetCtx().GetAD_Org_ID() + " and pay.c_payment_id = " + Get_ID();
dsSSCode = new DataSet();
dsSSCode = DB.ExecuteDataset(sql, null, null);
if (dsSSCode.Tables.Count > 0)
{
if (dsSSCode.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSSCode.Tables[0].Rows.Count; i++)
{
MAccount sscAcct = null;
int vlID = 0;
sql = @"SELECT SUM(grandtotal) FROM c_invoice WHERE IsActive = 'Y' AND docstatus = 'CO' AND c_bpartner_Id = " + Util.GetValueOfInt(dsSSCode.Tables[0].Rows[i]["c_bpartner_id"]);
decimal result = Util.GetValueOfDecimal(DB.ExecuteScalar(sql, null, null));
if (result <= Util.GetValueOfDecimal(dsSSCode.Tables[0].Rows[i]["ed010_maxamt"]) && result >= Util.GetValueOfDecimal(dsSSCode.Tables[0].Rows[i]["ed010_minamt"]))
{
if (Util.GetValueOfString(dsSSCode.Tables[0].Rows[i]["ed010_percentagetype"]) == "S")
{
sscAmt = (Decimal.Divide(Decimal.Multiply(Util.GetValueOfDecimal(dsSSCode.Tables[0].Rows[i]["linenetamt"]), Util.GetValueOfDecimal(dsSSCode.Tables[0].Rows[i]["ed010_socialsecurityprcnt"])), 100));
}
else if (Util.GetValueOfString(dsSSCode.Tables[0].Rows[i]["ed010_percentagetype"]) == "M")
{
// amount = (Decimal.Divide(Decimal.Multiply(Util.GetValueOfDecimal(dsSSCode.Tables[0].Rows[i]["linenetamt"]), Util.GetValueOfDecimal(dsSSCode.Tables[0].Rows[i]["ed010_vendorpercentage"])), 100));
sscAmt = (Decimal.Divide(Decimal.Multiply(Util.GetValueOfDecimal(dsSSCode.Tables[0].Rows[i]["linenetamt"]), Util.GetValueOfDecimal(dsSSCode.Tables[0].Rows[i]["ed010_orgpercentage"])), 100));
}
}
vlID = Util.GetValueOfInt(dsSSCode.Tables[0].Rows[i]["ED000_SecCodeAcct"]);
if (vlID > 0)
{
sscAcct = MAccount.Get(Env.GetCtx(), vlID);
}
if (sscAcct == null)
{
vlID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT ED000_SecCodeAcct FROM C_AcctSchema_Default WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND AD_Client_ID = " + GetAD_Client_ID()));
sscAcct = MAccount.Get(Env.GetCtx(), vlID);
}
fl = fact.CreateLine(null, sscAcct, GetC_Currency_ID(), null, Decimal.Round(sscAmt, 2));
if (fl != null)
{
payAmt += Decimal.Round(sscAmt, 2);
}
if (fl != null && AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
}
}
}
dsSSCode.Dispose();
fl = fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_PaymentSelect, as1), GetC_Currency_ID(), payAmt, null);
if (fl != null && AD_Org_ID != 0 && GetC_Charge_ID() == 0) // don't overwrite charge
{
fl.SetAD_Org_ID(AD_Org_ID);
}
}
}
// Default Posting Logic
else
{
MAccount acct = null;
if (GetC_Charge_ID() != 0)
{
acct = MCharge.GetAccount(GetC_Charge_ID(), as1, GetAmount());
}
else if (_Prepayment)
{
acct = GetAccount(Doc.ACCTTYPE_V_Prepayment, as1);
}
else
{
acct = GetAccount(Doc.ACCTTYPE_PaymentSelect, as1);
}
FactLine fl = fact.CreateLine(null, acct,
GetC_Currency_ID(), GetAmount(), null);
if (fl != null && AD_Org_ID != 0 &&
GetC_Charge_ID() == 0) // don't overwrite charge
{
fl.SetAD_Org_ID(AD_Org_ID);
}
// Asset
fl = fact.CreateLine(null, GetAccount(Doc.ACCTTYPE_BankInTransit, as1),
GetC_Currency_ID(), null, GetAmount());
if (fl != null && AD_Org_ID != 0)
{
fl.SetAD_Org_ID(AD_Org_ID);
}
}
}
else
{
_error = "DocumentType unknown: " + GetDocumentType();
log.Log(Level.SEVERE, _error);
fact = null;
}
//
List <Fact> facts1 = new List <Fact>();
facts1.Add(fact);
return(facts1);
}
/// <summary>
/// Create new Charge based on the parameters passed
/// </summary>
/// <param name="ctx"></param>
/// <param name="m_C_AcctSchema_ID"></param>
/// <param name="m_C_TaxCategory_ID"></param>
/// <param name="name"></param>
/// <param name="primaryC_ElementValue_ID"></param>
/// <param name="expense"></param>
/// <returns></returns>
public int CreateCharge(Ctx ctx, int m_C_AcctSchema_ID, int m_C_TaxCategory_ID, String name, int primaryC_ElementValue_ID, Boolean expense)
{
MCharge charge = new MCharge(ctx, 0, null);
charge.SetName(name);
charge.SetC_TaxCategory_ID(m_C_TaxCategory_ID);
if (!charge.Save())
{
// log.Log(Level.SEVERE, name + " not created");
Msg = name + " not created";
ID = 0;
return(0);
}
MAcctSchema m_acctSchema = null;
// Get Primary AcctSchama
if (m_acctSchema == null)
{
m_acctSchema = new MAcctSchema(ctx, m_C_AcctSchema_ID, null);
}
if (m_acctSchema == null || m_acctSchema.GetC_AcctSchema_ID() == 0)
{
ID = 0;
return(0);
}
MAcctSchemaElement primary_ase = m_acctSchema.GetAcctSchemaElement(X_C_AcctSchema_Element.ELEMENTTYPE_Account);
// Get All
MAcctSchema[] ass = MAcctSchema.GetClientAcctSchema(ctx, charge.GetAD_Client_ID());
foreach (MAcctSchema ac in ass)
{
// Target Account
MAccount defaultAcct = MAccount.GetDefault(ac, true); // optional null
// Natural Account
int C_ElementValue_ID = primaryC_ElementValue_ID;
MAcctSchemaElement ase = ac.GetAcctSchemaElement(X_C_AcctSchema_Element.ELEMENTTYPE_Account);
if (primary_ase.GetC_Element_ID() != ase.GetC_Element_ID())
{
MAcctSchemaDefault defAccts = MAcctSchemaDefault.Get(ctx, ac.GetC_AcctSchema_ID());
int C_ValidCombination_ID = defAccts.GetCh_Expense_Acct();
if (!expense)
{
C_ValidCombination_ID = defAccts.GetCh_Revenue_Acct();
}
MAccount chargeAcct = MAccount.Get(ctx, C_ValidCombination_ID);
C_ElementValue_ID = chargeAcct.GetAccount_ID();
// Fallback
if (C_ElementValue_ID == 0)
{
C_ElementValue_ID = defaultAcct.GetAccount_ID();
if (C_ElementValue_ID == 0)
{
C_ElementValue_ID = ase.GetC_ElementValue_ID();
}
if (C_ElementValue_ID == 0)
{
// log.Log(Level.WARNING, "No Default ElementValue for " + ac);
Msg = "No Default ElementValue for " + ac;
continue;
}
}
}
MAccount acct = MAccount.Get(ctx,
charge.GetAD_Client_ID(), charge.GetAD_Org_ID(),
ac.GetC_AcctSchema_ID(),
C_ElementValue_ID, defaultAcct.GetC_SubAcct_ID(),
defaultAcct.GetM_Product_ID(), defaultAcct.GetC_BPartner_ID(), defaultAcct.GetAD_OrgTrx_ID(),
defaultAcct.GetC_LocFrom_ID(), defaultAcct.GetC_LocTo_ID(), defaultAcct.GetC_SalesRegion_ID(),
defaultAcct.GetC_Project_ID(), defaultAcct.GetC_Campaign_ID(), defaultAcct.GetC_Activity_ID(),
defaultAcct.GetUser1_ID(), defaultAcct.GetUser2_ID(),
defaultAcct.GetUserElement1_ID(), defaultAcct.GetUserElement2_ID());
if (acct == null)
{
//log.Log(Level.WARNING, "No Default Account for " + ac);
Msg = "No Default Account for " + ac;
continue;
}
// Update Accounts
StringBuilder sql = new StringBuilder("UPDATE C_Charge_Acct ");
sql.Append("SET CH_Expense_Acct=").Append(acct.GetC_ValidCombination_ID());
sql.Append(", CH_Revenue_Acct=").Append(acct.GetC_ValidCombination_ID());
sql.Append(" WHERE C_Charge_ID=").Append(charge.GetC_Charge_ID());
sql.Append(" AND C_AcctSchema_ID=").Append(ac.GetC_AcctSchema_ID());
//
int no = VAdvantage.DataBase.DB.ExecuteQuery(sql.ToString(), null, null);
if (no != 1)
{
//log.Log(Level.WARNING, "Update #" + no + "\n" + sql.ToString());
Msg = "Update #" + no + "\n" + sql.ToString();
}
}
ID = charge.GetC_Charge_ID();
return(charge.GetC_Charge_ID());
}