Beispiel #1
0
  /** Create/Save Account */
  private void action_Save() {
    log.info("");
    /** Check completeness (mandatory fields) ... and for duplicates */
    StringBuffer sb = new StringBuffer();
    StringBuffer sql =
        new StringBuffer("SELECT C_ValidCombination_ID, Alias FROM C_ValidCombination WHERE ");
    Object value = null;
    if (s_AcctSchema.isHasAlias()) {
      value = f_Alias.getValue().toString();
      if (value == null) sb.append(Msg.translate(Env.getCtx(), "Alias")).append(", ");
    }
    MAcctSchemaElement[] elements = s_AcctSchema.getAcctSchemaElements();
    for (int i = 0; i < elements.length; i++) {
      MAcctSchemaElement ase = elements[i];
      String type = ase.getElementType();
      //
      if (type.equals(MAcctSchemaElement.ELEMENTTYPE_Organization)) {
        value = f_AD_Org_ID.getValue();
        sql.append("AD_Org_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_Account)) {
        value = f_Account_ID.getValue();
        sql.append("Account_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_SubAccount)) {
        value = f_SubAcct_ID.getValue();
        sql.append("C_SubAcct_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_Product)) {
        value = f_M_Product_ID.getValue();
        sql.append("M_Product_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_BPartner)) {
        value = f_C_BPartner_ID.getValue();
        sql.append("C_BPartner_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_Campaign)) {
        value = f_C_Campaign_ID.getValue();
        sql.append("C_Campaign_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_LocationFrom)) {
        value = f_C_LocFrom_ID.getValue();
        sql.append("C_LocFrom_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_LocationTo)) {
        value = f_C_LocTo_ID.getValue();
        sql.append("C_LocTo_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_Project)) {
        value = f_C_Project_ID.getValue();
        sql.append("C_Project_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_SalesRegion)) {
        value = f_C_SalesRegion_ID.getValue();
        sql.append("C_SalesRegion_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_OrgTrx)) {
        value = f_AD_OrgTrx_ID.getValue();
        sql.append("AD_OrgTrx_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_Activity)) {
        value = f_C_Activity_ID.getValue();
        sql.append("C_Activity_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_UserList1)) {
        value = f_User1_ID.getValue();
        sql.append("User1_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      } else if (type.equals(MAcctSchemaElement.ELEMENTTYPE_UserList2)) {
        value = f_User2_ID.getValue();
        sql.append("User2_ID");
        if (value == null) sql.append(" IS NULL AND ");
        else sql.append("=").append(value).append(" AND ");
      }
      //
      if (ase.isMandatory() && value == null) sb.append(ase.getName()).append(", ");
    } //	Fields in Element Order

    if (sb.length() != 0) {
      ADialog.error(m_WindowNo, this, "FillMandatory", sb.substring(0, sb.length() - 2));
      return;
    }
    if (f_AD_Org_ID == null || f_AD_Org_ID.getValue() == null) {
      ADialog.error(m_WindowNo, this, "FillMandatory", Msg.getElement(Env.getCtx(), "AD_Org_ID"));
      return;
    }
    if (f_Account_ID == null || f_Account_ID.getValue() == null) {
      ADialog.error(m_WindowNo, this, "FillMandatory", Msg.getElement(Env.getCtx(), "Account_ID"));
      return;
    }

    /** Check if already exists */
    sql.append("AD_Client_ID=? AND C_AcctSchema_ID=?");
    log.fine("Check = " + sql.toString());
    int IDvalue = 0;
    String Alias = null;
    try {
      PreparedStatement pstmt = DB.prepareStatement(sql.toString(), null);
      pstmt.setInt(1, m_AD_Client_ID);
      pstmt.setInt(2, s_AcctSchema.getC_AcctSchema_ID());
      ResultSet rs = pstmt.executeQuery();
      if (rs.next()) {
        IDvalue = rs.getInt(1);
        Alias = rs.getString(2);
      }
      rs.close();
      pstmt.close();
    } catch (SQLException e) {
      log.log(Level.SEVERE, sql.toString(), e);
      IDvalue = 0;
    }
    log.fine("ID=" + IDvalue + ", Alias=" + Alias);

    if (Alias == null) Alias = "";

    //	We have an account like this already - check alias
    if (IDvalue != 0 && s_AcctSchema.isHasAlias() && !f_Alias.getValue().toString().equals(Alias)) {
      sql = new StringBuffer("UPDATE C_ValidCombination SET Alias=");
      if (f_Alias.getValue().toString().length() == 0) sql.append("NULL");
      else sql.append("'").append(f_Alias.getValue()).append("'");
      sql.append(" WHERE C_ValidCombination_ID=").append(IDvalue);
      int i = 0;
      try {
        java.sql.PreparedStatement stmt =
            DB.prepareStatement(
                sql.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, null);
        i = stmt.executeUpdate();
        stmt.close();
      } catch (SQLException e) {
        log.log(Level.SEVERE, sql.toString(), e);
      }
      if (i == 0) ADialog.error(m_WindowNo, this, "AccountNotUpdated");
    }

    //	load and display
    if (IDvalue != 0) {
      loadInfo(IDvalue, s_AcctSchema.getC_AcctSchema_ID());
      action_Find(false);
      return;
    }

    log.config("New");
    Alias = null;
    if (f_Alias != null) Alias = f_Alias.getValue().toString();
    int C_SubAcct_ID = 0;
    if (f_SubAcct_ID != null && f_SubAcct_ID.getValue() != null)
      C_SubAcct_ID = ((Integer) f_SubAcct_ID.getValue()).intValue();
    int M_Product_ID = 0;
    if (f_M_Product_ID != null && f_M_Product_ID.getValue() != null)
      M_Product_ID = ((Integer) f_M_Product_ID.getValue()).intValue();
    int C_BPartner_ID = 0;
    if (f_C_BPartner_ID != null && f_C_BPartner_ID.getValue() != null)
      C_BPartner_ID = ((Integer) f_C_BPartner_ID.getValue()).intValue();
    int AD_OrgTrx_ID = 0;
    if (f_AD_OrgTrx_ID != null && f_AD_OrgTrx_ID.getValue() != null)
      AD_OrgTrx_ID = ((Integer) f_AD_OrgTrx_ID.getValue()).intValue();
    int C_LocFrom_ID = 0;
    if (f_C_LocFrom_ID != null && f_C_LocFrom_ID.getValue() != null)
      C_LocFrom_ID = ((Integer) f_C_LocFrom_ID.getValue()).intValue();
    int C_LocTo_ID = 0;
    if (f_C_LocTo_ID != null && f_C_LocTo_ID.getValue() != null)
      C_LocTo_ID = ((Integer) f_C_LocTo_ID.getValue()).intValue();
    int C_SRegion_ID = 0;
    if (f_C_SalesRegion_ID != null && f_C_SalesRegion_ID.getValue() != null)
      C_SRegion_ID = ((Integer) f_C_SalesRegion_ID.getValue()).intValue();
    int C_Project_ID = 0;
    if (f_C_Project_ID != null && f_C_Project_ID.getValue() != null)
      C_Project_ID = ((Integer) f_C_Project_ID.getValue()).intValue();
    int C_Campaign_ID = 0;
    if (f_C_Campaign_ID != null && f_C_Campaign_ID.getValue() != null)
      C_Campaign_ID = ((Integer) f_C_Campaign_ID.getValue()).intValue();
    int C_Activity_ID = 0;
    if (f_C_Activity_ID != null && f_C_Activity_ID.getValue() != null)
      C_Activity_ID = ((Integer) f_C_Activity_ID.getValue()).intValue();
    int User1_ID = 0;
    if (f_User1_ID != null && f_User1_ID.getValue() != null)
      User1_ID = ((Integer) f_User1_ID.getValue()).intValue();
    int User2_ID = 0;
    if (f_User2_ID != null && f_User2_ID.getValue() != null)
      User2_ID = ((Integer) f_User2_ID.getValue()).intValue();

    MAccount acct =
        MAccount.get(
            Env.getCtx(),
            m_AD_Client_ID,
            ((Integer) f_AD_Org_ID.getValue()).intValue(),
            s_AcctSchema.getC_AcctSchema_ID(),
            ((Integer) f_Account_ID.getValue()).intValue(),
            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) loadInfo(0, 0);
    else {
      //	Update Account with optional Alias
      if (Alias != null && Alias.length() > 0) {
        acct.setAlias(Alias);
        acct.save();
      }
      loadInfo(acct.get_ID(), s_AcctSchema.getC_AcctSchema_ID());
    }
    action_Find(false);
  } //	action_Save
Beispiel #2
0
  /**
   * Create GL Journal
   *
   * @return document info
   */
  private String createGLJournal() {
    // FR: [ 2214883 ] Remove SQL code and Replace for Query
    final String whereClause = "AD_PInstance_ID=?";
    List<X_T_InvoiceGL> list =
        new Query(getCtx(), X_T_InvoiceGL.Table_Name, whereClause, get_TrxName())
            .setParameters(getAD_PInstance_ID())
            .setOrderBy("AD_Org_ID")
            .list();
    // FR: [ 2214883 ] Remove SQL code and Replace for Query

    if (list.size() == 0) return " - No Records found";

    //
    MAcctSchema as = MAcctSchema.get(getCtx(), p_C_AcctSchema_ID);
    MAcctSchemaDefault asDefaultAccts = MAcctSchemaDefault.get(getCtx(), p_C_AcctSchema_ID);
    MGLCategory cat = MGLCategory.getDefaultSystem(getCtx());
    if (cat == null) {
      MDocType docType = MDocType.get(getCtx(), p_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(p_C_DocTypeReval_ID);
    batch.setDateDoc(new Timestamp(System.currentTimeMillis()));
    batch.setDateAcct(p_DateReval);
    batch.setC_Currency_ID(as.getC_Currency_ID());
    if (!batch.save()) return " - Could not create Batch";
    //
    MJournal journal = null;
    BigDecimal drTotal = Env.ZERO;
    BigDecimal crTotal = Env.ZERO;
    int AD_Org_ID = 0;
    for (int i = 0; i < list.size(); i++) {
      X_T_InvoiceGL gl = list.get(i);
      if (gl.getAmtRevalDrDiff().signum() == 0 && gl.getAmtRevalCrDiff().signum() == 0) continue;
      MInvoice invoice = new MInvoice(getCtx(), gl.getC_Invoice_ID(), null);
      if (invoice.getC_Currency_ID() == as.getC_Currency_ID()) continue;
      //
      if (journal == null) {
        journal = new MJournal(batch);
        journal.setC_AcctSchema_ID(as.getC_AcctSchema_ID());
        journal.setC_Currency_ID(as.getC_Currency_ID());
        journal.setC_ConversionType_ID(p_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 " - 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));
      BigDecimal dr = gl.getAmtRevalDrDiff();
      BigDecimal cr = gl.getAmtRevalCrDiff();
      drTotal = drTotal.add(dr);
      crTotal = crTotal.add(cr);
      line.setAmtSourceDr(dr);
      line.setAmtAcctDr(dr);
      line.setAmtSourceCr(cr);
      line.setAmtAcctCr(cr);
      line.saveEx();
      //
      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, crTotal, 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, crTotal, AD_Org_ID, (list.size() + 1) * 10);

    return " - " + batch.getDocumentNo() + " #" + list.size();
  } //	createGLJournal
 /**
  * Create Balancing Entry
  *
  * @param asDefaultAccts acct schema default accounts
  * @param journal journal
  * @param drTotal dr
  * @param crTotal cr
  * @param AD_Org_ID org
  * @param lineNo base line no
  */
 private void createBalancing(
     MAcctSchemaDefault asDefaultAccts,
     MJournal journal,
     BigDecimal drTotal,
     BigDecimal crTotal,
     int AD_Org_ID,
     int lineNo) {
   if (journal == null) throw new IllegalArgumentException("Jornal is null");
   //		CR Entry = Gain
   if (drTotal.signum() != 0) {
     MJournalLine line = new MJournalLine(journal);
     line.setLine(lineNo + 1);
     MAccount base = MAccount.get(getCtx(), asDefaultAccts.getUnrealizedGain_Acct());
     MAccount acct =
         MAccount.get(
             getCtx(),
             asDefaultAccts.getAD_Client_ID(),
             AD_Org_ID,
             asDefaultAccts.getC_AcctSchema_ID(),
             base.getAccount_ID(),
             base.getC_SubAcct_ID(),
             base.getM_Product_ID(),
             base.getC_BPartner_ID(),
             base.getAD_OrgTrx_ID(),
             base.getC_LocFrom_ID(),
             base.getC_LocTo_ID(),
             base.getC_SalesRegion_ID(),
             base.getC_Project_ID(),
             base.getC_Campaign_ID(),
             base.getC_Activity_ID(),
             base.getUser1_ID(),
             base.getUser2_ID(),
             base.getUserElement1_ID(),
             base.getUserElement2_ID());
     line.setDescription(Msg.getElement(getCtx(), "UnrealizedGain_Acct"));
     // TODO: C_ValidCombination_ID is no longer a column because we have DR/CR accounts
     // line.setC_ValidCombination_ID(acct.getC_ValidCombination_ID());
     line.setAmtSourceCr(drTotal);
     line.setAmtAcctCr(drTotal);
     line.save();
   }
   //	DR Entry = Loss
   if (crTotal.signum() != 0) {
     MJournalLine line = new MJournalLine(journal);
     line.setLine(lineNo + 2);
     MAccount base = MAccount.get(getCtx(), asDefaultAccts.getUnrealizedLoss_Acct());
     MAccount acct =
         MAccount.get(
             getCtx(),
             asDefaultAccts.getAD_Client_ID(),
             AD_Org_ID,
             asDefaultAccts.getC_AcctSchema_ID(),
             base.getAccount_ID(),
             base.getC_SubAcct_ID(),
             base.getM_Product_ID(),
             base.getC_BPartner_ID(),
             base.getAD_OrgTrx_ID(),
             base.getC_LocFrom_ID(),
             base.getC_LocTo_ID(),
             base.getC_SalesRegion_ID(),
             base.getC_Project_ID(),
             base.getC_Campaign_ID(),
             base.getC_Activity_ID(),
             base.getUser1_ID(),
             base.getUser2_ID(),
             base.getUserElement1_ID(),
             base.getUserElement2_ID());
     line.setDescription(Msg.getElement(getCtx(), "UnrealizedLoss_Acct"));
     // TODO: C_ValidCombination_ID is no longer a column because we have DR/CR accounts
     // line.setC_ValidCombination_ID(acct.getC_ValidCombination_ID());
     line.setAmtSourceDr(crTotal);
     line.setAmtAcctDr(crTotal);
     line.save();
   }
 } //	createBalancing
  @Override
  public ArrayList<Fact> createFacts(MAcctSchema as) {
    Fact fact = new Fact(this, as, Fact.POST_Actual);
    String sql =
        "SELECT m.HR_Concept_id, MAX(c.Name), SUM(m.Amount), MAX(c.AccountSign), MAX(CA.IsBalancing), e.AD_Org_ID, d.C_Activity_ID" // 1,2,3,4,5,6,7
            + " FROM HR_Movement m"
            + " INNER JOIN HR_Concept_Acct ca ON (ca.HR_Concept_ID=m.HR_Concept_ID AND ca.IsActive = 'Y')"
            + " INNER JOIN HR_Concept      c  ON (c.HR_Concept_ID=m.HR_Concept_ID AND c.IsActive = 'Y')"
            + " INNER JOIN C_BPartner      bp ON (bp.C_BPartner_ID = m.C_BPartner_ID)"
            + " INNER JOIN HR_Employee	 e  ON (bp.C_BPartner_ID=e.C_BPartner_ID)"
            + " INNER JOIN HR_Department   d  ON (d.HR_Department_ID=e.HR_Department_ID)"
            + " WHERE m.HR_Process_ID=? AND (m.Qty <> 0 OR m.Amount <> 0) AND c.AccountSign != 'N' AND ca.IsBalancing != 'Y'"
            + " GROUP BY m.HR_Concept_ID,e.AD_Org_ID,d.C_Activity_ID"
            + " ORDER BY e.AD_Org_ID,d.C_Activity_ID";

    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
      pstmt = DB.prepareStatement(sql, process.get_TrxName());
      pstmt.setInt(1, process.getHR_Process_ID());
      rs = pstmt.executeQuery();
      while (rs.next()) {
        int HR_Concept_ID = rs.getInt(1);
        BigDecimal sumAmount = rs.getBigDecimal(3);
        // round amount according to currency
        sumAmount = sumAmount.setScale(as.getStdPrecision(), BigDecimal.ROUND_HALF_UP);
        String AccountSign = rs.getString(4);
        int AD_OrgTrx_ID = rs.getInt(6);
        int C_Activity_ID = rs.getInt(7);
        //
        if (AccountSign != null
            && AccountSign.length() > 0
            && (AccountSign.equals("D") || AccountSign.equals("C"))) {
          // HR_Expense_Acct    DR
          // HR_Revenue_Acct    CR
          MAccount accountBPD =
              MAccount.get(
                  getCtx(), getAccountBalancing(as.getC_AcctSchema_ID(), HR_Concept_ID, "D"));
          FactLine debit =
              fact.createLine(null, accountBPD, as.getC_Currency_ID(), sumAmount, null);
          debit.setAD_OrgTrx_ID(AD_OrgTrx_ID);
          debit.setC_Activity_ID(C_Activity_ID);
          debit.saveEx();
          MAccount accountBPC =
              MAccount.get(
                  getCtx(), this.getAccountBalancing(as.getC_AcctSchema_ID(), HR_Concept_ID, "C"));
          FactLine credit =
              fact.createLine(null, accountBPC, as.getC_Currency_ID(), null, sumAmount);
          credit.setAD_OrgTrx_ID(AD_OrgTrx_ID);
          credit.setC_Activity_ID(C_Activity_ID);
          credit.saveEx();
        }
      }
    } catch (Exception e) {
      log.log(Level.SEVERE, sql, e);
      p_Error = e.getLocalizedMessage();
      return null;
    } finally {
      DB.close(rs, pstmt);
      pstmt = null;
      rs = null;
    }

    ArrayList<Fact> facts = new ArrayList<Fact>();
    facts.add(fact);
    return facts;
  }