public void setWhere(int M_Warehouse_ID, int M_CostType_ID, int M_CostElement_ID) {
    params = new ArrayList<Object>();

    whereClause1 = new StringBuffer("WHERE tc.IsReversal='N' ");
    whereClause2 =
        new StringBuffer(" AND  tc.SeqNo = (SELECT MAX(SeqNo) FROM RV_M_Transaction_Costing tc1")
            .append(
                " WHERE tc.M_Product_ID=tc1.M_Product_ID AND tc.M_Warehouse_ID = tc1.M_Warehouse_ID ");

    whereClause1.append("AND tc.DateAcct<= ").append(DB.TO_DATE(p_DateValue));
    whereClause2.append("AND tc1.DateAcct<= ").append(DB.TO_DATE(p_DateValue));

    if (p_M_Product_ID > 0) {
      whereClause1.append(" AND p.M_Product_ID=? ");
      params.add(p_M_Product_ID);
    }

    if (p_M_Product_Category_ID > 0) {
      whereClause1.append(" AND p.M_Product_Category_ID =? ");
      params.add(p_M_Product_Category_ID);
    }
    if (M_CostType_ID > 0) {
      whereClause1.append(" AND tc.M_CostType_ID =?  ");
      params.add(M_CostType_ID);
      whereClause2.append(" AND tc1.M_CostType_ID=tc.M_CostType_ID ");
    }
    if (M_CostElement_ID > 0) {
      whereClause1.append(" AND tc.M_CostElement_ID=? ");
      params.add(M_CostElement_ID);
      whereClause2.append(" AND tc1.M_CostElement_ID = tc.M_CostElement_ID");
    }

    if (M_Warehouse_ID > 0) {
      whereClause1.append(" AND tc.M_Warehouse_ID=? ");
      params.add(M_Warehouse_ID);
    }
    whereClause2.append(")");
  }
  /**
   * Receive notification of the end of an element.
   *
   * @param uri namespace
   * @param localName simple name
   * @param qName qualified name
   * @throws SAXException
   */
  public void endElement(String uri, String localName, String qName) throws SAXException {
    //	Log.trace(Log.l6_Database+1, "TranslationHandler.endElement", qName);
    if (qName.equals(Translation.XML_TAG)
        || qName.equals(Translation.XML_TAG2)
        || qName.equals(Translation.XML_TAG3)) {

    } else if (qName.equals(Translation.XML_ROW_TAG)) {
      //	Set section
      if (m_sql.length() > 0) m_sql.append(",");
      m_sql.append("Updated=").append(DB.TO_DATE(m_time, false));
      if (!m_isBaseLanguage) {
        if (m_trl != null && ("Y".equals(m_trl) || "N".equals(m_trl)))
          m_sql.append(",IsTranslated='").append(m_trl).append("'");
        else m_sql.append(",IsTranslated='Y'");
      }
      //	Where section
      m_sql.append(" WHERE ");
      if (m_curUUID != null) {
        StringBuilder sql =
            new StringBuilder("SELECT ")
                .append(m_TableName)
                .append("_ID")
                .append(" FROM ")
                .append(m_TableName)
                .append(" WHERE ")
                .append(m_TableName)
                .append("_UU =?");
        int ID = DB.getSQLValueEx(null, sql.toString(), m_curUUID);
        m_sql.append(m_TableName).append("_ID=").append(ID);
      } else {
        m_sql.append(m_TableName).append("_ID=").append(m_curID);
      }
      if (!m_isBaseLanguage) m_sql.append(" AND AD_Language='").append(m_AD_Language).append("'");
      if (m_AD_Client_ID >= 0) m_sql.append(" AND AD_Client_ID=").append(m_AD_Client_ID);
      //	Update section
      m_sql.insert(0, m_updateSQL);

      //	Execute
      int no = DB.executeUpdate(m_sql.toString(), null);
      if (no == 1) {
        if (log.isLoggable(Level.FINE)) log.fine(m_sql.toString());
        m_updateCount++;
      } else if (no == 0) log.warning("Not Found - " + m_sql.toString());
      else log.severe("Update Rows=" + no + " (Should be 1) - " + m_sql.toString());
    } else if (qName.equals(Translation.XML_VALUE_TAG)) {
      if (m_sql.length() > 0) m_sql.append(",");
      m_sql.append(m_curColumnName).append("=").append(DB.TO_STRING(m_curValue.toString()));
    }
  } //	endElement
  /**
   * Get dynamic WHERE part of SQL To be overwritten by concrete classes
   *
   * @return WHERE clause
   */
  protected String getSQLWhere() {
    StringBuilder sql = new StringBuilder();

    Integer S_ResourceType_ID = (Integer) fieldResourceType.getValue();

    if (S_ResourceType_ID != null)
      sql.append(" AND rt.S_ResourceType_ID=").append(S_ResourceType_ID.intValue());

    Integer S_Resource_ID = (Integer) fieldResource.getValue();

    if (S_Resource_ID != null) sql.append(" AND r.S_Resource_ID=").append(S_Resource_ID.intValue());

    Date f = fieldFrom.getValue();
    Timestamp ts = f != null ? new Timestamp(f.getTime()) : null;

    if (ts != null) sql.append(" AND TRUNC(ra.AssignDateFrom)>=").append(DB.TO_DATE(ts, false));

    Date t = fieldTo.getValue();
    ts = t != null ? new Timestamp(t.getTime()) : null;

    if (ts != null) sql.append(" AND TRUNC(ra.AssignDateTo)<=").append(DB.TO_DATE(ts, false));

    return sql.toString();
  } // getSQLWhere
  /** execute the Valuation Effective Date */
  protected String doIt() throws Exception {
    setup();

    for (MWarehouse warehouse : warehouses)
      for (MCostType costType : costTypes)
        for (MCostElement costElement : costElements)
          generateInventoryValue(
              warehouse.getM_Warehouse_ID(),
              costType.getM_CostType_ID(),
              costElement.getM_CostElement_ID());

    DB.executeUpdate(
        "UPDATE T_InventoryValue SET cost = CASE WHEN QtyOnHand <> 0 THEN (CostAmt + CostAmtLL) / QtyOnHand ELSE  0 END  , CumulatedAmt = CostAmt + CostAmtLL,  DateValue = "
            + DB.TO_DATE(p_DateValue)
            + " WHERE AD_PInstance_ID=?",
        getAD_PInstance_ID(),
        get_TrxName());

    return "@Ok@";
  }
Esempio n. 5
0
  /**
   * Process
   *
   * @return message
   * @throws Exception
   */
  protected String doIt() throws Exception {

    Calendar dateFrom = Calendar.getInstance();
    dateFrom.setTimeInMillis(System.currentTimeMillis());
    dateFrom.set(Calendar.HOUR_OF_DAY, 0);
    dateFrom.set(Calendar.MINUTE, 0);
    dateFrom.set(Calendar.SECOND, 0);
    dateFrom.set(Calendar.MILLISECOND, 0);
    p_dateFrom = new Timestamp(dateFrom.getTimeInMillis());
    p_dateFrom.setNanos(0);

    log.info("Calculating initial balance");
    /* initial balance */
    StringBuilder sqlIni =
        new StringBuilder(
            "SELECT SUM(acctBalance(Account_ID,AmtAcctDr,AmtAcctCr)) FROM Fact_Acct WHERE DateAcct<=");
    sqlIni
        .append(DB.TO_DATE(p_dateFrom))
        .append(" AND PostingType='")
        .append(MFactAcct.POSTINGTYPE_Actual)
        .append("' AND ");
    // initial balance - the balance of selected account on selected schema on default hierarchy
    String whereClause =
        MReportTree.getWhereClause(
            getCtx(), 0, MAcctSchemaElement.ELEMENTTYPE_Account, p_C_ElementValue_ID);
    sqlIni.append(whereClause);

    BigDecimal initialBalance = DB.getSQLValueBD(get_TrxName(), sqlIni.toString(), new Object[] {});

    X_T_CashFlow cfini = new X_T_CashFlow(getCtx(), 0, get_TrxName());
    cfini.setAD_Org_ID(0);
    cfini.setAD_PInstance_ID(getAD_PInstance_ID());
    cfini.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_1_InitialBalance);
    cfini.setCashFlowType(X_T_CashFlow.CASHFLOWTYPE_Operational);
    cfini.setDateTrx(p_dateFrom);
    cfini.setIsActive(true);
    cfini.setIsSOTrx(true);
    cfini.setLineTotalAmt(initialBalance);
    cfini.setProbability(Env.ONEHUNDRED);
    cfini.setDateTo(p_dateTo);
    cfini.setC_AcctSchema_ID(p_C_AcctSchema_ID);
    cfini.setC_ElementValue_ID(p_C_ElementValue_ID);
    if (!cfini.save()) throw new AdempiereSystemError("Error saving cash flow ini");
    if (log.isLoggable(Level.INFO)) log.info("Initial balance calculated = " + initialBalance);

    /* plan records */
    String sqlPlan =
        "SELECT cpl.AD_Org_ID, "
            + "COALESCE(cpl.C_Activity_ID, cp.C_Activity_ID) AS C_Activity_ID, "
            + "cp.CashFlowType, "
            + "COALESCE(cpl.C_BPartner_ID, cp.C_BPartner_ID) AS C_BPartner_ID, "
            + "COALESCE(cpl.C_Campaign_ID, cp.C_Campaign_ID) AS C_Campaign_ID, "
            + "cpl.C_Charge_ID, "
            + "COALESCE(cpl.C_Project_ID, cp.C_Project_ID) AS C_Project_ID, "
            + "cpl.DateTrx, "
            + "cpl.Description, "
            + "cp.IsSOTrx, "
            + "cpl.LineTotalAmt, "
            + "cpl.M_Product_ID, "
            + "cpl.Name, "
            + "cpl.Probability, "
            + "cpl.C_CashPlanLine_ID, "
            + "cp.C_CashPlan_ID "
            + "FROM C_CashPlanLine cpl "
            + "JOIN C_CashPlan cp ON (cp.C_CashPlan_ID=cpl.C_CashPlan_ID) "
            + "WHERE cp.AD_Client_ID=? AND cp.IsActive='Y' AND cpl.IsActive='Y' AND cpl.DateTrx BETWEEN ? AND ?";
    PreparedStatement pstmtPlan = null;
    ResultSet rsPlan = null;
    try {
      pstmtPlan = DB.prepareStatement(sqlPlan, get_TrxName());
      pstmtPlan.setInt(1, getAD_Client_ID());
      pstmtPlan.setTimestamp(2, p_dateFrom);
      pstmtPlan.setTimestamp(3, p_dateTo);
      rsPlan = pstmtPlan.executeQuery();
      int noPlan = 0;
      while (rsPlan.next()) {
        noPlan++;
        boolean issotrx = "Y".equals(rsPlan.getString("IsSOTrx"));
        BigDecimal total = rsPlan.getBigDecimal("LineTotalAmt");
        if (!issotrx) total = total.negate();
        X_T_CashFlow cfplan = new X_T_CashFlow(getCtx(), 0, get_TrxName());
        cfplan.setAD_Org_ID(rsPlan.getInt("AD_Org_ID"));
        cfplan.setAD_PInstance_ID(getAD_PInstance_ID());
        cfplan.setC_Activity_ID(rsPlan.getInt("C_Activity_ID"));
        cfplan.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_2_Plan);
        cfplan.setCashFlowType(rsPlan.getString("CashFlowType"));
        cfplan.setC_BPartner_ID(rsPlan.getInt("C_BPartner_ID"));
        cfplan.setC_Campaign_ID(rsPlan.getInt("C_Campaign_ID"));
        cfplan.setC_Charge_ID(rsPlan.getInt("C_Charge_ID"));
        cfplan.setC_Project_ID(rsPlan.getInt("C_Project_ID"));
        cfplan.setDateTrx(rsPlan.getTimestamp("DateTrx"));
        cfplan.setDescription(rsPlan.getString("Description"));
        cfplan.setIsActive(true);
        cfplan.setIsSOTrx(issotrx);
        cfplan.setLineTotalAmt(total);
        cfplan.setM_Product_ID(rsPlan.getInt("M_Product_ID"));
        cfplan.setName(rsPlan.getString("Name"));
        cfplan.setProbability(rsPlan.getBigDecimal("Probability"));
        cfplan.setDateTo(p_dateTo);
        cfplan.setC_AcctSchema_ID(p_C_AcctSchema_ID);
        cfplan.setC_ElementValue_ID(p_C_ElementValue_ID);
        cfplan.setC_CashPlanLine_ID(rsPlan.getInt("C_CashPlanLine_ID"));
        if (!cfplan.save()) throw new AdempiereSystemError("Error saving cash flow plan");
      }
      if (log.isLoggable(Level.INFO)) log.info(noPlan + " plan inserted");
    } catch (Exception e) {
      log.log(Level.SEVERE, sqlPlan, e);
    } finally {
      DB.close(rsPlan, pstmtPlan);
      rsPlan = null;
      pstmtPlan = null;
    }

    /* commitment records */
    String sqlOpenOrders =
        "SELECT o.C_Order_ID, o.IsPayScheduleValid, "
            + "SUM((ol.QtyOrdered-ol.QtyInvoiced)*ol.PriceActual)/o.TotalLines as Pending "
            + "FROM C_Order o JOIN C_OrderLine ol ON (o.C_Order_ID=ol.C_Order_ID) "
            + "WHERE o.AD_Client_ID=? AND o.TotalLines != 0 AND o.DocStatus IN ('CO') AND ol.QtyInvoiced<ol.QtyOrdered "
            + "GROUP BY o.C_Order_ID, o.IsPayScheduleValid, o.TotalLines";
    PreparedStatement pstmtOpenOrders = null;
    ResultSet rsOpenOrders = null;
    try {
      pstmtOpenOrders = DB.prepareStatement(sqlOpenOrders, get_TrxName());
      pstmtOpenOrders.setInt(1, getAD_Client_ID());
      rsOpenOrders = pstmtOpenOrders.executeQuery();
      int noOrders = 0;
      int noOrdIns = 0;
      int noOrdSchIns = 0;
      while (rsOpenOrders.next()) {
        noOrders++;
        if ((noOrders % 100) == 0)
          if (log.isLoggable(Level.INFO)) log.info(noOrders + " orders processed");
        int order_id = rsOpenOrders.getInt("C_Order_ID");
        boolean isPaySchedule = ("Y".equals(rsOpenOrders.getString("IsPayScheduleValid")));
        BigDecimal pending = rsOpenOrders.getBigDecimal("Pending");
        MOrder order = new MOrder(getCtx(), order_id, get_TrxName());
        MCurrency curr = MCurrency.get(getCtx(), order.getC_Currency_ID());
        BigDecimal open = order.getGrandTotal().multiply(pending);
        // subtract payments done directly to order still not allocated to an invoice
        // TODO: review if is possible to check just the balance for partially allocated payments
        // TODO: currency convert of payment if different currency from order
        BigDecimal paid =
            DB.getSQLValueBD(
                get_TrxName(),
                "SELECT SUM(CASE WHEN IsReceipt='Y' THEN PayAmt ELSE -PayAmt END) FROM C_Payment WHERE DocStatus IN ('CO','CL') AND C_Order_ID=? AND C_Invoice_ID IS NULL AND IsAllocated='N'",
                order_id);
        if (paid != null) {
          if (!order.isSOTrx()) paid = paid.negate();
          open = open.subtract(paid);
        }
        if (open.scale() > curr.getStdPrecision())
          open = open.setScale(curr.getStdPrecision(), BigDecimal.ROUND_HALF_UP);
        BigDecimal invoiced = order.getGrandTotal().subtract(open);
        if (isPaySchedule) {
          MOrderPaySchedule[] schedule =
              MOrderPaySchedule.getOrderPaySchedule(getCtx(), order_id, 0, get_TrxName());
          BigDecimal accum = Env.ZERO;
          for (MOrderPaySchedule ops : schedule) {
            accum = accum.add(ops.getDueAmt());
            if (invoiced.compareTo(accum) > 0) continue;
            if (ops.getDueDate().compareTo(p_dateTo) <= 0) {
              BigDecimal opensch;
              if (accum.subtract(invoiced).compareTo(ops.getDueAmt()) > 0)
                opensch = ops.getDueAmt();
              else opensch = accum.subtract(invoiced);
              if (!order.isSOTrx()) opensch = opensch.negate();
              noOrdSchIns++;
              X_T_CashFlow cforderps = new X_T_CashFlow(getCtx(), 0, get_TrxName());
              cforderps.setAD_Org_ID(order.getAD_Org_ID());
              cforderps.setAD_PInstance_ID(getAD_PInstance_ID());
              cforderps.setC_Activity_ID(order.getC_Activity_ID());
              cforderps.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_3_CommitmentsOrders);
              // cforder.setCashFlowType(X_T_CashFlow.CASHFLOWTYPE_Operational);
              cforderps.setC_BPartner_ID(order.getC_BPartner_ID());
              cforderps.setC_Campaign_ID(order.getC_Campaign_ID());
              cforderps.setC_Project_ID(order.getC_Project_ID());
              cforderps.setDateTrx(ops.getDueDate());
              cforderps.setDescription(order.getDescription());
              cforderps.setIsActive(true);
              cforderps.setIsSOTrx(order.isSOTrx());
              cforderps.setLineTotalAmt(opensch);
              cforderps.setProbability(Env.ONEHUNDRED);
              cforderps.setDateTo(p_dateTo);
              cforderps.setC_AcctSchema_ID(p_C_AcctSchema_ID);
              cforderps.setC_ElementValue_ID(p_C_ElementValue_ID);
              cforderps.setC_CashPlanLine_ID(order.getC_CashPlanLine_ID());
              cforderps.setC_Order_ID(order_id);
              if (!cforderps.save())
                throw new AdempiereSystemError("Error saving cash flow order pay schedule");
            }
          }
        } else {
          Timestamp dueDate =
              DB.getSQLValueTS(
                  get_TrxName(),
                  "SELECT paymentTermDueDate(?, ?) FROM Dual",
                  new Object[] {order.getC_PaymentTerm_ID(), order.getDateOrdered()});
          if (dueDate.compareTo(p_dateTo) <= 0) {
            if (!order.isSOTrx()) open = open.negate();
            noOrdIns++;
            X_T_CashFlow cforder = new X_T_CashFlow(getCtx(), 0, get_TrxName());
            cforder.setAD_Org_ID(order.getAD_Org_ID());
            cforder.setAD_PInstance_ID(getAD_PInstance_ID());
            cforder.setC_Activity_ID(order.getC_Activity_ID());
            cforder.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_3_CommitmentsOrders);
            // cforder.setCashFlowType(X_T_CashFlow.CASHFLOWTYPE_Operational);
            cforder.setC_BPartner_ID(order.getC_BPartner_ID());
            cforder.setC_Campaign_ID(order.getC_Campaign_ID());
            cforder.setC_Project_ID(order.getC_Project_ID());
            cforder.setDateTrx(dueDate);
            cforder.setDescription(order.getDescription());
            cforder.setIsActive(true);
            cforder.setIsSOTrx(order.isSOTrx());
            cforder.setLineTotalAmt(open);
            cforder.setProbability(Env.ONEHUNDRED);
            cforder.setDateTo(p_dateTo);
            cforder.setC_AcctSchema_ID(p_C_AcctSchema_ID);
            cforder.setC_ElementValue_ID(p_C_ElementValue_ID);
            cforder.setC_CashPlanLine_ID(order.getC_CashPlanLine_ID());
            cforder.setC_Order_ID(order_id);
            if (!cforder.save()) throw new AdempiereSystemError("Error saving cash flow order");
          }
        }
      }
      if (log.isLoggable(Level.INFO))
        log.info(
            noOrders
                + " orders processed, "
                + noOrdIns
                + " orders inserted, "
                + noOrdSchIns
                + " schedule inserted");
    } catch (Exception e) {
      log.log(Level.SEVERE, sqlOpenOrders, e);
    } finally {
      DB.close(rsOpenOrders, pstmtOpenOrders);
      rsOpenOrders = null;
      pstmtOpenOrders = null;
    }

    /* actual records */
    String sqlActual =
        "SELECT oi.AD_Org_ID, oi.C_Invoice_ID, oi.C_BPartner_ID, oi.IsSOTrx, oi.DueDate, oi.OpenAmt, oi.C_Campaign_ID, oi.C_Project_ID, oi.C_Activity_ID "
            + "FROM RV_OpenItem oi "
            + "WHERE oi.AD_Client_ID=? AND oi.DueDate <= ?";
    PreparedStatement pstmtActual = null;
    ResultSet rsActual = null;
    try {
      pstmtActual = DB.prepareStatement(sqlActual, get_TrxName());
      pstmtActual.setInt(1, getAD_Client_ID());
      pstmtActual.setTimestamp(2, p_dateTo);
      rsActual = pstmtActual.executeQuery();
      int noInv = 0;
      while (rsActual.next()) {
        boolean issotrx = "Y".equals(rsActual.getString("IsSOTrx"));
        BigDecimal openamt = rsActual.getBigDecimal("OpenAmt");
        if (!issotrx) openamt = openamt.negate();
        MInvoice invoice = new MInvoice(getCtx(), rsActual.getInt("C_Invoice_ID"), get_TrxName());
        noInv++;
        X_T_CashFlow cfactual = new X_T_CashFlow(getCtx(), 0, get_TrxName());
        cfactual.setAD_Org_ID(rsActual.getInt("AD_Org_ID"));
        cfactual.setAD_PInstance_ID(getAD_PInstance_ID());
        cfactual.setC_Activity_ID(rsActual.getInt("C_Activity_ID"));
        cfactual.setCashFlowSource(X_T_CashFlow.CASHFLOWSOURCE_4_ActualDebtInvoices);
        // cfactual.setCashFlowType(X_T_CashFlow.CASHFLOWTYPE_Operational);
        cfactual.setC_BPartner_ID(rsActual.getInt("C_BPartner_ID"));
        cfactual.setC_Campaign_ID(rsActual.getInt("C_Campaign_ID"));
        cfactual.setC_Project_ID(rsActual.getInt("C_Project_ID"));
        cfactual.setDateTrx(rsActual.getTimestamp("DueDate"));
        cfactual.setDescription(invoice.getDescription());
        cfactual.setIsActive(true);
        cfactual.setIsSOTrx(issotrx);
        cfactual.setLineTotalAmt(openamt);
        cfactual.setProbability(Env.ONEHUNDRED);
        cfactual.setDateTo(p_dateTo);
        cfactual.setC_AcctSchema_ID(p_C_AcctSchema_ID);
        cfactual.setC_ElementValue_ID(p_C_ElementValue_ID);
        cfactual.setC_CashPlanLine_ID(invoice.getC_CashPlanLine_ID());
        cfactual.setC_Invoice_ID(rsActual.getInt("C_Invoice_ID"));
        if (!cfactual.save()) throw new AdempiereSystemError("Error saving cash flow actual");
      }
      if (log.isLoggable(Level.INFO)) log.info(noInv + " invoices inserted");
    } catch (Exception e) {
      log.log(Level.SEVERE, sqlActual, e);
    } finally {
      DB.close(rsActual, pstmtActual);
      rsActual = null;
      pstmtActual = null;
    }

    /* subtract from plan lines the related orders */
    String sqlupdord =
        "UPDATE T_CashFlow "
            + "SET LineTotalAmt = LineTotalAmt - "
            + "(SELECT COALESCE(SUM(LineTotalAmt),0) "
            + "FROM T_CashFlow cf "
            + "WHERE cf.AD_PInstance_ID = T_CashFlow.AD_Pinstance_ID "
            + "AND cf.C_CashPlanLine_ID=T_CashFlow.C_CashPlanLine_ID AND "
            + "CashFlowSource = ? /* Orders */) "
            + "WHERE AD_PInstance_ID = ? "
            + "AND CashFlowSource = ? /* Plan */ "
            + "AND EXISTS (SELECT 1 FROM T_CashFlow cf "
            + "WHERE cf.AD_PInstance_ID = T_CashFlow.AD_PInstance_ID "
            + "AND cf.C_CashPlanLine_ID=T_CashFlow.C_CashPlanLine_ID "
            + "AND CashFlowSource = ? /* Orders */)";
    int noupdord =
        DB.executeUpdate(
            sqlupdord,
            new Object[] {
              X_T_CashFlow.CASHFLOWSOURCE_3_CommitmentsOrders,
              getAD_PInstance_ID(),
              X_T_CashFlow.CASHFLOWSOURCE_2_Plan,
              X_T_CashFlow.CASHFLOWSOURCE_3_CommitmentsOrders
            },
            false,
            get_TrxName());
    if (log.isLoggable(Level.INFO)) log.info(noupdord + " plans subtracted from orders");

    /* subtract from plan lines the related invoices */
    String sqlupdinv =
        "UPDATE T_CashFlow "
            + "SET LineTotalAmt = LineTotalAmt - "
            + "(SELECT COALESCE(SUM(LineTotalAmt),0) "
            + "FROM T_CashFlow cf "
            + "WHERE cf.AD_PInstance_ID = T_CashFlow.AD_Pinstance_ID "
            + "AND cf.C_CashPlanLine_ID=T_CashFlow.C_CashPlanLine_ID AND "
            + "CashFlowSource = ? /* Invoices */) "
            + "WHERE AD_PInstance_ID = ? "
            + "AND CashFlowSource = ? /* Plan */ "
            + "AND EXISTS (SELECT 1 FROM T_CashFlow cf "
            + "WHERE cf.AD_PInstance_ID = T_CashFlow.AD_PInstance_ID "
            + "AND cf.C_CashPlanLine_ID=T_CashFlow.C_CashPlanLine_ID "
            + "AND CashFlowSource = ? /* Invoices */)";
    int noupdinv =
        DB.executeUpdate(
            sqlupdinv,
            new Object[] {
              X_T_CashFlow.CASHFLOWSOURCE_4_ActualDebtInvoices,
              getAD_PInstance_ID(),
              X_T_CashFlow.CASHFLOWSOURCE_2_Plan,
              X_T_CashFlow.CASHFLOWSOURCE_4_ActualDebtInvoices
            },
            false,
            get_TrxName());
    if (log.isLoggable(Level.INFO)) log.info(noupdinv + " plans subtracted from invoices");

    /* delete overplanned records */
    String sqldeloverplanned =
        "DELETE FROM T_CashFlow "
            + "WHERE AD_PInstance_ID = ? "
            + "AND CashFlowSource = ? /* Plan */ "
            + "AND ((IsSOTrx='Y' AND LineTotalAmt<=0) OR (IsSOTrx='N' AND LineTotalAmt>=0))";
    int nodelplan =
        DB.executeUpdate(
            sqldeloverplanned,
            new Object[] {getAD_PInstance_ID(), X_T_CashFlow.CASHFLOWSOURCE_2_Plan},
            false,
            get_TrxName());
    if (log.isLoggable(Level.INFO)) log.info(nodelplan + " overplanned plans deleted");

    return "OK";
  } // 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
Esempio n. 7
0
 /**
  * Get Total Info
  *
  * @return <= end
  */
 public String getTotalWhere() {
   StringBuilder sql = new StringBuilder("<= ");
   sql.append(DB.TO_DATE(m_EndDate));
   return sql.toString();
 } //	getPeriodWhere
Esempio n. 8
0
 /**
  * Get Year Info
  *
  * @return BETWEEN start AND end
  */
 public String getYearWhere() {
   StringBuilder sql = new StringBuilder("BETWEEN ");
   sql.append(DB.TO_DATE(m_YearStartDate)).append(" AND ").append(DB.TO_DATE(m_EndDate));
   return sql.toString();
 } //	getPeriodWhere