public String product(Properties ctx,int WindowNo,GridTab mTab,GridField mField,Object value){
  Integer M_Product_ID=(Integer)value;
  if (M_Product_ID == null || M_Product_ID.intValue() == 0)   return "";
  BigDecimal priceActual=null;
  Timestamp DateExpense=Env.getContextAsDate(ctx,WindowNo,"DateExpense");
  if (DateExpense == null)   DateExpense=new Timestamp(System.currentTimeMillis());
  String sql=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null;
  try {
    boolean noPrice=true;
    sql="SELECT bomPriceStd(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceStd," + "bomPriceList(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceList," + "bomPriceLimit(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceLimit,"+ "p.C_UOM_ID,pv.ValidFrom,pl.C_Currency_ID "+ "FROM M_Product p, M_ProductPrice pp, M_Pricelist pl, M_PriceList_Version pv "+ "WHERE p.M_Product_ID=pp.M_Product_ID"+ " AND pp.M_PriceList_Version_ID=pv.M_PriceList_Version_ID"+ " AND pv.M_PriceList_ID=pl.M_PriceList_ID"+ " AND pv.IsActive='Y'"+ " AND p.M_Product_ID=?"+ " AND pl.M_PriceList_ID=?"+ " ORDER BY pv.ValidFrom DESC";
    pstmt=DB.prepareStatement(sql,null);
    pstmt.setInt(1,M_Product_ID.intValue());
    pstmt.setInt(2,Env.getContextAsInt(ctx,WindowNo,"M_PriceList_ID"));
    rs=pstmt.executeQuery();
    while (rs.next() && noPrice) {
      java.sql.Date plDate=rs.getDate("ValidFrom");
      if (plDate == null || !DateExpense.before(plDate)) {
        noPrice=false;
        priceActual=rs.getBigDecimal("PriceStd");
        if (priceActual == null)         priceActual=rs.getBigDecimal("PriceList");
        if (priceActual == null)         priceActual=rs.getBigDecimal("PriceLimit");
        Integer ii=new Integer(rs.getInt("C_Currency_ID"));
        if (!rs.wasNull())         mTab.setValue("C_Currency_ID",ii);
      }
    }
    if (noPrice) {
      sql="SELECT bomPriceStd(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceStd," + "bomPriceList(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceList," + "bomPriceLimit(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceLimit,"+ "p.C_UOM_ID,pv.ValidFrom,pl.C_Currency_ID "+ "FROM M_Product p, M_ProductPrice pp, M_Pricelist pl, M_Pricelist bpl, M_PriceList_Version pv "+ "WHERE p.M_Product_ID=pp.M_Product_ID"+ " AND pp.M_PriceList_Version_ID=pv.M_PriceList_Version_ID"+ " AND pv.M_PriceList_ID=bpl.M_PriceList_ID"+ " AND pv.IsActive='Y'"+ " AND bpl.M_PriceList_ID=pl.BasePriceList_ID"+ " AND p.M_Product_ID=?"+ " AND pl.M_PriceList_ID=?"+ " ORDER BY pv.ValidFrom DESC";
      DB.close(rs,pstmt);
      pstmt=DB.prepareStatement(sql,null);
      pstmt.setInt(1,M_Product_ID.intValue());
      pstmt.setInt(2,Env.getContextAsInt(ctx,WindowNo,"M_PriceList_ID"));
      rs=pstmt.executeQuery();
      while (rs.next() && noPrice) {
        java.sql.Date plDate=rs.getDate("ValidFrom");
        if (plDate == null || !DateExpense.before(plDate)) {
          noPrice=false;
          priceActual=rs.getBigDecimal("PriceStd");
          if (priceActual == null)           priceActual=rs.getBigDecimal("PriceList");
          if (priceActual == null)           priceActual=rs.getBigDecimal("PriceLimit");
          Integer ii=new Integer(rs.getInt("C_Currency_ID"));
          if (!rs.wasNull())           mTab.setValue("C_Currency_ID",ii);
        }
      }
    }
  }
 catch (  SQLException e) {
    log.log(Level.SEVERE,sql,e);
    return e.getLocalizedMessage();
  }
 finally {
    DB.close(rs,pstmt);
    rs=null;
    pstmt=null;
  }
  if (priceActual == null)   priceActual=Env.ZERO;
  mTab.setValue("ExpenseAmt",priceActual);
  return "";
}

public String product ( Properties ctx , int WindowNo , GridTab mTab , GridField mField , Object value ) { Integer M_Product_ID = ( Integer ) value ; if ( M_Product_ID == null || M_Product_ID . intValue ( ) == 0 ) return "" ; BigDecimal priceActual = null ; Timestamp DateExpense = Env . getContextAsDate ( ctx , WindowNo , "DateExpense" ) ; if ( DateExpense == null ) DateExpense = new Timestamp ( System . currentTimeMillis ( ) ) ; String sql = null ; PreparedStatement pstmt = null ; ResultSet rs = null ; try { boolean noPrice = true ; sql = "SELECT bomPriceStd(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceStd," + "bomPriceList(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceList," + "bomPriceLimit(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceLimit," + "p.C_UOM_ID,pv.ValidFrom,pl.C_Currency_ID " + "FROM M_Product p, M_ProductPrice pp, M_Pricelist pl, M_PriceList_Version pv " + "WHERE p.M_Product_ID=pp.M_Product_ID" + " AND pp.M_PriceList_Version_ID=pv.M_PriceList_Version_ID" + " AND pv.M_PriceList_ID=pl.M_PriceList_ID" + " AND pv.IsActive='Y'" + " AND p.M_Product_ID=?" + " AND pl.M_PriceList_ID=?" + " ORDER BY pv.ValidFrom DESC" ; pstmt = DB . prepareStatement ( sql , null ) ; pstmt . setInt ( 1 , M_Product_ID . intValue ( ) ) ; pstmt . setInt ( 2 , Env . getContextAsInt ( ctx , WindowNo , "M_PriceList_ID" ) ) ; rs = pstmt . executeQuery ( ) ; while ( rs . next ( ) && noPrice ) { java . sql . Date plDate = rs . getDate ( "ValidFrom" ) ; if ( plDate == null || ! DateExpense . before ( plDate ) ) { noPrice = false ; priceActual = rs . getBigDecimal ( "PriceStd" ) ; if ( priceActual == null ) priceActual = rs . getBigDecimal ( "PriceList" ) ; if ( priceActual == null ) priceActual = rs . getBigDecimal ( "PriceLimit" ) ; Integer ii = new Integer ( rs . getInt ( "C_Currency_ID" ) ) ; if ( ! rs . wasNull ( ) ) mTab . setValue ( "C_Currency_ID" , ii ) ; } } if ( noPrice ) { sql = "SELECT bomPriceStd(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceStd," + "bomPriceList(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceList," + "bomPriceLimit(p.M_Product_ID,pv.M_PriceList_Version_ID) AS PriceLimit," + "p.C_UOM_ID,pv.ValidFrom,pl.C_Currency_ID " + "FROM M_Product p, M_ProductPrice pp, M_Pricelist pl, M_Pricelist bpl, M_PriceList_Version pv " + "WHERE p.M_Product_ID=pp.M_Product_ID" + " AND pp.M_PriceList_Version_ID=pv.M_PriceList_Version_ID" + " AND pv.M_PriceList_ID=bpl.M_PriceList_ID" + " AND pv.IsActive='Y'" + " AND bpl.M_PriceList_ID=pl.BasePriceList_ID" + " AND p.M_Product_ID=?" + " AND pl.M_PriceList_ID=?" + " ORDER BY pv.ValidFrom DESC" ; DB . close ( rs , pstmt ) ; pstmt = DB . prepareStatement ( sql , null ) ; pstmt . setInt ( 1 , M_Product_ID . intValue ( ) ) ; pstmt . setInt ( 2 , Env . getContextAsInt ( ctx , WindowNo , "M_PriceList_ID" ) ) ; rs = pstmt . executeQuery ( ) ; while ( rs . next ( ) && noPrice ) { java . sql . Date plDate = rs . getDate ( "ValidFrom" ) ; if ( plDate == null || ! DateExpense . before ( plDate ) ) { noPrice = false ; priceActual = rs . getBigDecimal ( "PriceStd" ) ; if ( priceActual == null ) priceActual = rs . getBigDecimal ( "PriceList" ) ; if ( priceActual == null ) priceActual = rs . getBigDecimal ( "PriceLimit" ) ; Integer ii = new Integer ( rs . getInt ( "C_Currency_ID" ) ) ; if ( ! rs . wasNull ( ) ) mTab . setValue ( "C_Currency_ID" , ii ) ; } } } } catch ( SQLException e ) { log . log ( Level . SEVERE , sql , e ) ; return e . getLocalizedMessage ( ) ; } finally { DB . close ( rs , pstmt ) ; rs = null ; pstmt = null ; } if ( priceActual == null ) priceActual = Env . ZERO ; mTab . setValue ( "ExpenseAmt" , priceActual ) ; return "" ; }
expense report line - called from m_product_id , s_resourceassignment_id - set expenseamt
