/**
   * Process
   *
   * @return message
   * @throws Exception
   */
  @Override
  protected String doIt() throws Exception {
    log.info("C_AcctSchema_ID=" + p_C_AcctSchema_ID);
    if (p_C_AcctSchema_ID == 0) throw new CompiereSystemException("C_AcctSchema_ID=0");
    MAcctSchema as = MAcctSchema.get(getCtx(), p_C_AcctSchema_ID);
    if (as.get_ID() == 0)
      throw new CompiereSystemException("Not Found - C_AcctSchema_ID=" + p_C_AcctSchema_ID);

    //	Update
    String sql =
        "UPDATE M_Product_Acct pa "
            + "SET (P_Revenue_Acct,P_Expense_Acct,P_CostAdjustment_Acct,P_InventoryClearing_Acct,P_Asset_Acct,P_COGS_Acct,"
            + " P_PurchasePriceVariance_Acct,P_InvoicePriceVariance_Acct,"
            + " P_TradeDiscountRec_Acct,P_TradeDiscountGrant_Acct,"
            + " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct)="
            + " (SELECT P_Revenue_Acct,P_Expense_Acct,P_CostAdjustment_Acct,P_InventoryClearing_Acct,P_Asset_Acct,P_COGS_Acct,"
            + " P_PurchasePriceVariance_Acct,P_InvoicePriceVariance_Acct,"
            + " P_TradeDiscountRec_Acct,P_TradeDiscountGrant_Acct,"
            + " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct"
            + " FROM M_Product_Category_Acct pca"
            + " WHERE pca.M_Product_Category_ID="
            + p_M_Product_Category_ID
            + " AND pca.C_AcctSchema_ID= pa.C_AcctSchema_ID "
            + "), Updated=SysDate, UpdatedBy=0 "
            + "WHERE pa.C_AcctSchema_ID= ? "
            + " AND EXISTS (SELECT * FROM M_Product p "
            + "WHERE p.M_Product_ID=pa.M_Product_ID"
            + " AND p.M_Product_Category_ID= ? )";
    int updated = DB.executeUpdate(get_TrxName(), sql, p_C_AcctSchema_ID, p_M_Product_Category_ID);
    addLog(0, null, new BigDecimal(updated), "@Updated@");

    //	Insert new Products
    sql =
        "INSERT INTO M_Product_Acct "
            + "(M_Product_ID, C_AcctSchema_ID,"
            + " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
            + " P_Revenue_Acct, P_Expense_Acct, P_CostAdjustment_Acct, P_InventoryClearing_Acct, P_Asset_Acct, P_CoGs_Acct,"
            + " P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct,"
            + " P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct,"
            + " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct) "
            + "SELECT p.M_Product_ID, acct.C_AcctSchema_ID,"
            + " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,"
            + " acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_CostAdjustment_Acct, acct.P_InventoryClearing_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct,"
            + " acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct,"
            + " acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct,"
            + " acct.P_Resource_Absorption_Acct, acct.P_MaterialOverhd_Acct "
            + "FROM M_Product p"
            + " INNER JOIN M_Product_Category_Acct acct ON (acct.M_Product_Category_ID=p.M_Product_Category_ID)"
            + "WHERE acct.C_AcctSchema_ID= ? " //	#
            + " AND p.M_Product_Category_ID= ? " //	#
            + " AND NOT EXISTS (SELECT * FROM M_Product_Acct pa "
            + "WHERE pa.M_Product_ID=p.M_Product_ID"
            + " AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
    int created = DB.executeUpdate(get_TrxName(), sql, p_C_AcctSchema_ID, p_M_Product_Category_ID);
    addLog(0, null, new BigDecimal(created), "@Created@");

    return "@Created@=" + created + ", @Updated@=" + updated;
  } //	doIt
Пример #2
0
  /** Update Header. Set Approved Amount */
  private void updateHeader() {
    String sql =
        "UPDATE S_TimeExpense te"
            + " SET ApprovalAmt = "
            + "(SELECT SUM(Qty*ConvertedAmt) FROM S_TimeExpenseLine tel "
            + "WHERE te.S_TimeExpense_ID=tel.S_TimeExpense_ID) "
            + "WHERE S_TimeExpense_ID=? ";
    DB.executeUpdate(get_Trx(), sql, getS_TimeExpense_ID());

    if (get_Trx() != null) get_Trx().commit();
  } //	updateHeader
Пример #3
0
  /**
   * Create Period Controls
   *
   * @param ctx context
   * @param AD_Client_ID client
   * @param sp server process
   * @param trx transaction
   */
  public static void createPeriodControls(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trx) {
    s_log.info("AD_Client_ID=" + AD_Client_ID);

    //	Delete Duplicates
    String sql =
        "DELETE FROM C_PeriodControl "
            + "WHERE (C_Period_ID, DocBaseType) IN "
            + "(SELECT C_Period_ID, DocBaseType "
            + "FROM C_PeriodControl pc2 "
            + "GROUP BY C_Period_ID, DocBaseType "
            + "HAVING COUNT(*) > 1)"
            + " AND C_PeriodControl_ID NOT IN "
            + "(SELECT MIN(C_PeriodControl_ID) "
            + "FROM C_PeriodControl pc3 "
            + "GROUP BY C_Period_ID, DocBaseType)";
    int no = DB.executeUpdate(trx, sql);
    s_log.info("Duplicates deleted #" + no);

    //	Insert Missing
    sql =
        "SELECT DISTINCT p.AD_Client_ID, p.C_Period_ID, dbt.DocBaseType "
            + "FROM C_Period p, "
            + "C_DocBaseType dbt "
            + "WHERE p.AD_Client_ID=? "
            + " AND NOT EXISTS"
            + " (SELECT * FROM C_PeriodControl pc "
            + "WHERE pc.C_Period_ID=p.C_Period_ID AND pc.DocBaseType=dbt.DocBaseType)"
            + " AND (dbt.AD_Client_ID = 0 OR p.AD_Client_ID = dbt.AD_Client_ID)";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int counter = 0;
    try {
      pstmt = DB.prepareStatement(sql, trx);
      pstmt.setInt(1, AD_Client_ID);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        int Client_ID = rs.getInt(1);
        int C_Period_ID = rs.getInt(2);
        String DocBaseType = rs.getString(3);
        s_log.config(
            "AD_Client_ID="
                + Client_ID
                + ", C_Period_ID="
                + C_Period_ID
                + ", DocBaseType="
                + DocBaseType);
        //
        MPeriodControl pc = new MPeriodControl(ctx, Client_ID, C_Period_ID, DocBaseType, trx);
        if (pc.save()) {
          counter++;
          s_log.fine(pc.toString());
        } else s_log.warning("Not saved: " + pc);
      }
    } catch (Exception e) {
      s_log.log(Level.SEVERE, sql, e);
    } finally {
      DB.closeResultSet(rs);
      DB.closeStatement(pstmt);
    }
    if (sp != null) sp.addLog(0, null, new BigDecimal(counter), "@C_PeriodControl_ID@ @Created@");
    s_log.info("Inserted #" + counter);
  } //	createPeriodControls