/**
* Process
*
* @return info
* @throws Exception
*/
protected String doIt() throws Exception {
if (p_IsAllCurrencies) p_C_Currency_ID = 0;
log.info(
"C_AcctSchema_ID="
+ p_C_AcctSchema_ID
+ ",C_ConversionTypeReval_ID="
+ p_C_ConversionTypeReval_ID
+ ",DateReval="
+ p_DateReval
+ ", APAR="
+ p_APAR
+ ", IsAllCurrencies="
+ p_IsAllCurrencies
+ ",C_Currency_ID="
+ p_C_Currency_ID
+ ", C_DocType_ID="
+ p_C_DocTypeReval_ID);
// Parameter
if (p_DateReval == null) p_DateReval = new Timestamp(System.currentTimeMillis());
// Delete - just to be sure
String sql = "DELETE FROM T_InvoiceGL WHERE AD_PInstance_ID=" + getAD_PInstance_ID();
int no = DB.executeUpdate(sql, get_TrxName());
if (no > 0) log.info("Deleted #" + no);
// Insert Trx
String dateStr = DB.TO_DATE(p_DateReval, true);
sql =
"INSERT INTO T_InvoiceGL (AD_Client_ID, AD_Org_ID, IsActive, Created,CreatedBy, Updated,UpdatedBy,"
+ " AD_PInstance_ID, C_Invoice_ID, GrandTotal, OpenAmt, "
+ " Fact_Acct_ID, AmtSourceBalance, AmtAcctBalance, "
+ " AmtRevalDr, AmtRevalCr, C_DocTypeReval_ID, IsAllCurrencies, "
+ " DateReval, C_ConversionTypeReval_ID, AmtRevalDrDiff, AmtRevalCrDiff, APAR) "
// --
+ "SELECT i.AD_Client_ID, i.AD_Org_ID, i.IsActive, i.Created,i.CreatedBy, i.Updated,i.UpdatedBy,"
+ getAD_PInstance_ID()
+ ", i.C_Invoice_ID, i.GrandTotal, invoiceOpen(i.C_Invoice_ID, 0), "
+ " fa.Fact_Acct_ID, fa.AmtSourceDr-fa.AmtSourceCr, fa.AmtAcctDr-fa.AmtAcctCr, "
// AmtRevalDr, AmtRevalCr,
+ " currencyConvert(fa.AmtSourceDr, i.C_Currency_ID, a.C_Currency_ID, "
+ dateStr
+ ", "
+ p_C_ConversionTypeReval_ID
+ ", i.AD_Client_ID, i.AD_Org_ID),"
+ " currencyConvert(fa.AmtSourceCr, i.C_Currency_ID, a.C_Currency_ID, "
+ dateStr
+ ", "
+ p_C_ConversionTypeReval_ID
+ ", i.AD_Client_ID, i.AD_Org_ID),"
+ (p_C_DocTypeReval_ID == 0 ? "NULL" : String.valueOf(p_C_DocTypeReval_ID))
+ ", "
+ (p_IsAllCurrencies ? "'Y'," : "'N',")
+ dateStr
+ ", "
+ p_C_ConversionTypeReval_ID
+ ", 0, 0, '"
+ p_APAR
+ "' "
//
+ "FROM C_Invoice_v i"
+ " INNER JOIN Fact_Acct fa ON (fa.AD_Table_ID=318 AND fa.Record_ID=i.C_Invoice_ID"
+ " AND (i.GrandTotal=fa.AmtSourceDr OR i.GrandTotal=fa.AmtSourceCr))"
+ " INNER JOIN C_AcctSchema a ON (fa.C_AcctSchema_ID=a.C_AcctSchema_ID) "
+ "WHERE i.IsPaid='N'"
+ " AND EXISTS (SELECT * FROM C_ElementValue ev "
+ "WHERE ev.C_ElementValue_ID=fa.Account_ID AND (ev.AccountType='A' OR ev.AccountType='L'))"
+ " AND fa.C_AcctSchema_ID="
+ p_C_AcctSchema_ID;
if (!p_IsAllCurrencies) sql += " AND i.C_Currency_ID<>a.C_Currency_ID";
if (ONLY_AR.equals(p_APAR)) sql += " AND i.IsSOTrx='Y'";
else if (ONLY_AP.equals(p_APAR)) sql += " AND i.IsSOTrx='N'";
if (!p_IsAllCurrencies && p_C_Currency_ID != 0)
sql += " AND i.C_Currency_ID=" + p_C_Currency_ID;
no = DB.executeUpdate(sql, get_TrxName());
if (no != 0) log.info("Inserted #" + no);
else if (CLogMgt.isLevelFiner()) log.warning("Inserted #" + no + " - " + sql);
else log.warning("Inserted #" + no);
// Calculate Difference
sql =
DB.convertSqlToNative(
"UPDATE T_InvoiceGL gl "
+ "SET (AmtRevalDrDiff,AmtRevalCrDiff)="
+ "(SELECT gl.AmtRevalDr-fa.AmtAcctDr, gl.AmtRevalCr-fa.AmtAcctCr "
+ "FROM Fact_Acct fa "
+ "WHERE gl.Fact_Acct_ID=fa.Fact_Acct_ID) "
+ "WHERE AD_PInstance_ID="
+ getAD_PInstance_ID());
int noT = DB.executeUpdate(sql, get_TrxName());
if (noT > 0) log.config("Difference #" + noT);
// Percentage
sql =
"UPDATE T_InvoiceGL SET Percent = 100 "
+ "WHERE GrandTotal=OpenAmt AND AD_PInstance_ID="
+ getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no > 0) log.info("Not Paid #" + no);
sql =
"UPDATE T_InvoiceGL SET Percent = ROUND(OpenAmt*100/GrandTotal,6) "
+ "WHERE GrandTotal<>OpenAmt AND GrandTotal <> 0 AND AD_PInstance_ID="
+ getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no > 0) log.info("Partial Paid #" + no);
sql =
"UPDATE T_InvoiceGL SET AmtRevalDr = AmtRevalDr * Percent/100,"
+ " AmtRevalCr = AmtRevalCr * Percent/100,"
+ " AmtRevalDrDiff = AmtRevalDrDiff * Percent/100,"
+ " AmtRevalCrDiff = AmtRevalCrDiff * Percent/100 "
+ "WHERE Percent <> 100 AND AD_PInstance_ID="
+ getAD_PInstance_ID();
no = DB.executeUpdate(sql, get_TrxName());
if (no > 0) log.config("Partial Calc #" + no);
// Create Document
String info = "";
if (p_C_DocTypeReval_ID != 0) {
if (p_C_Currency_ID != 0) log.warning("Can create Journal only for all currencies");
else info = createGLJournal();
}
return "#" + noT + info;
} // doIt
