/**
* Process
*
* @return message
* @throws Exception
*/
protected String doIt() throws Exception {
log.info("C_AcctSchema_ID=" + p_C_AcctSchema_ID);
if (p_C_AcctSchema_ID == 0) throw new AdempiereSystemError("C_AcctSchema_ID=0");
MAcctSchema as = MAcctSchema.get(getCtx(), p_C_AcctSchema_ID);
if (as.get_ID() == 0)
throw new AdempiereSystemError("Not Found - C_AcctSchema_ID=" + p_C_AcctSchema_ID);
//
String sql = null;
int updated = 0;
int created = 0;
int updatedTotal = 0;
int createdTotal = 0;
// Update existing Customers
sql =
DB.convertSqlToNative(
"UPDATE C_BP_Customer_Acct ca "
+ "SET (C_Receivable_Acct,C_Receivable_Services_Acct,C_PrePayment_Acct)="
+ " (SELECT C_Receivable_Acct,C_Receivable_Services_Acct,C_PrePayment_Acct "
+ " FROM C_BP_Group_Acct"
+ " WHERE C_BP_Group_ID="
+ p_C_BP_Group_ID
+ " AND C_AcctSchema_ID="
+ p_C_AcctSchema_ID
+ "), Updated=now(), UpdatedBy=0 "
+ "WHERE ca.C_AcctSchema_ID="
+ p_C_AcctSchema_ID
+ " AND EXISTS (SELECT * FROM C_BPartner p "
+ "WHERE p.C_BPartner_ID=ca.C_BPartner_ID"
+ " AND p.C_BP_Group_ID="
+ p_C_BP_Group_ID
+ ")");
updated = DB.executeUpdate(sql, get_TrxName());
addLog(0, null, new BigDecimal(updated), "@Updated@ @C_BPartner_ID@ @IsCustomer@");
updatedTotal += updated;
// Insert new Customer
sql =
"INSERT INTO C_BP_Customer_Acct "
+ "(C_BPartner_ID, C_AcctSchema_ID,"
+ " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " C_Receivable_Acct, C_Receivable_Services_Acct, C_PrePayment_Acct) "
+ "SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID,"
+ " p.AD_Client_ID, p.AD_Org_ID, 'Y', now(), 0, now(), 0,"
+ " acct.C_Receivable_Acct, acct.C_Receivable_Services_Acct, acct.C_PrePayment_Acct "
+ "FROM C_BPartner p"
+ " INNER JOIN C_BP_Group_Acct acct ON (acct.C_BP_Group_ID=p.C_BP_Group_ID)"
+ "WHERE acct.C_AcctSchema_ID="
+ p_C_AcctSchema_ID // #
+ " AND p.C_BP_Group_ID="
+ p_C_BP_Group_ID
+ " AND NOT EXISTS (SELECT * FROM C_BP_Customer_Acct ca "
+ "WHERE ca.C_BPartner_ID=p.C_BPartner_ID"
+ " AND ca.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
created = DB.executeUpdate(sql, get_TrxName());
addLog(0, null, new BigDecimal(created), "@Created@ @C_BPartner_ID@ @IsCustomer@");
createdTotal += created;
// Update existing Vendors
sql =
DB.convertSqlToNative(
"UPDATE C_BP_Vendor_Acct va "
+ "SET (V_Liability_Acct,V_Liability_Services_Acct,V_PrePayment_Acct)="
+ " (SELECT V_Liability_Acct,V_Liability_Services_Acct,V_PrePayment_Acct "
+ " FROM C_BP_Group_Acct"
+ " WHERE C_BP_Group_ID="
+ p_C_BP_Group_ID
+ " AND C_AcctSchema_ID="
+ p_C_AcctSchema_ID
+ "), Updated=now(), UpdatedBy=0 "
+ "WHERE va.C_AcctSchema_ID="
+ p_C_AcctSchema_ID
+ " AND EXISTS (SELECT * FROM C_BPartner p "
+ "WHERE p.C_BPartner_ID=va.C_BPartner_ID"
+ " AND p.C_BP_Group_ID="
+ p_C_BP_Group_ID
+ ")");
updated = DB.executeUpdate(sql, get_TrxName());
addLog(0, null, new BigDecimal(updated), "@Updated@ @C_BPartner_ID@ @IsVendor@");
updatedTotal += updated;
// Insert new Vendors
sql =
"INSERT INTO C_BP_Vendor_Acct "
+ "(C_BPartner_ID, C_AcctSchema_ID,"
+ " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " V_Liability_Acct, V_Liability_Services_Acct, V_PrePayment_Acct) "
+ "SELECT p.C_BPartner_ID, acct.C_AcctSchema_ID,"
+ " p.AD_Client_ID, p.AD_Org_ID, 'Y', now(), 0, now(), 0,"
+ " acct.V_Liability_Acct, acct.V_Liability_Services_Acct, acct.V_PrePayment_Acct "
+ "FROM C_BPartner p"
+ " INNER JOIN C_BP_Group_Acct acct ON (acct.C_BP_Group_ID=p.C_BP_Group_ID)"
+ "WHERE acct.C_AcctSchema_ID="
+ p_C_AcctSchema_ID // #
+ " AND p.C_BP_Group_ID="
+ p_C_BP_Group_ID
+ " AND NOT EXISTS (SELECT * FROM C_BP_Vendor_Acct va "
+ "WHERE va.C_BPartner_ID=p.C_BPartner_ID AND va.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
created = DB.executeUpdate(sql, get_TrxName());
addLog(0, null, new BigDecimal(created), "@Created@ @C_BPartner_ID@ @IsVendor@");
createdTotal += created;
return "@Created@=" + createdTotal + ", @Updated@=" + updatedTotal;
} // doIt
/**
* 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
/**
* Get SQL Modify command
*
* @param table table
* @param setNullOption generate null / not null statement
* @return sql separated by ;
*/
public String getSQLModify(final I_AD_Table table, final boolean setNullOption) {
final String tableName = table.getTableName();
final String columnName = getColumnName();
final int displayType = getAD_Reference_ID();
String defaultValue = getDefaultValue();
final boolean mandatory = isMandatory();
final String sqlDataType = getSQLDataType();
final StringBuilder sql = new StringBuilder();
final StringBuilder sqlBase =
new StringBuilder("ALTER TABLE ").append(tableName).append(" MODIFY ").append(columnName);
// Default
final StringBuilder sqlDefault = new StringBuilder(sqlBase).append(" ").append(sqlDataType);
if (defaultValue != null
&& defaultValue.length() > 0
&& defaultValue.indexOf('@') == -1 // no variables
&& (!(DisplayType.isID(displayType)
&& defaultValue.equals("-1")))) // not for ID's with default -1
{
if (DisplayType.isText(displayType)
|| displayType == DisplayType.List
|| displayType == DisplayType.YesNo
// Two special columns: Defined as Table but DB Type is String
|| columnName.equals("EntityType")
|| columnName.equals("AD_Language")
|| (displayType == DisplayType.Button && !(columnName.endsWith("_ID")))) {
if (!defaultValue.startsWith("'") && !defaultValue.endsWith("'"))
defaultValue = DB.TO_STRING(defaultValue);
}
sqlDefault.append(" DEFAULT ").append(defaultValue);
} else {
if (!mandatory) sqlDefault.append(" DEFAULT NULL ");
defaultValue = null;
}
sql.append(DB.convertSqlToNative(sqlDefault.toString()));
// Constraint
// Null Values
if (mandatory && defaultValue != null && defaultValue.length() > 0) {
StringBuffer sqlSet =
new StringBuffer("UPDATE ")
.append(tableName)
.append(" SET ")
.append(columnName)
.append("=")
.append(defaultValue)
.append(" WHERE ")
.append(columnName)
.append(" IS NULL");
sql.append(DB.SQLSTATEMENT_SEPARATOR).append(sqlSet);
}
// Null
if (setNullOption) {
StringBuffer sqlNull = new StringBuffer(sqlBase);
if (mandatory) sqlNull.append(" NOT NULL");
else sqlNull.append(" NULL");
sql.append(DB.SQLSTATEMENT_SEPARATOR).append(DB.convertSqlToNative(sqlNull.toString()));
}
//
return sql.toString();
} // getSQLModify
