/**
   * 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
예제 #2
0
  /**
   * 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
예제 #3
0
  /**
   * 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