public static void consolidate(Properties ctx){
  String sql="SELECT * FROM M_MatchPO po " + "WHERE EXISTS (SELECT 1 FROM M_MatchPO x " + "WHERE po.C_OrderLine_ID=x.C_OrderLine_ID AND po.Qty=x.Qty "+ "GROUP BY C_OrderLine_ID, Qty "+ "HAVING COUNT(*) = 2) "+ " AND AD_Client_ID=?"+ "ORDER BY C_OrderLine_ID, M_InOutLine_ID";
  PreparedStatement pstmt=null;
  ResultSet rs=null;
  int success=0;
  int errors=0;
  try {
    pstmt=DB.prepareStatement(sql,null);
    pstmt.setInt(1,Env.getAD_Client_ID(ctx));
    rs=pstmt.executeQuery();
    while (rs.next()) {
      MMatchPO po1=new MMatchPO(ctx,rs,null);
      if (rs.next()) {
        MMatchPO po2=new MMatchPO(ctx,rs,null);
        if (po1.getM_InOutLine_ID() != 0 && po1.getC_InvoiceLine_ID() == 0 && po2.getM_InOutLine_ID() == 0 && po2.getC_InvoiceLine_ID() != 0) {
          String s1="UPDATE M_MatchPO SET C_InvoiceLine_ID=" + po2.getC_InvoiceLine_ID() + " WHERE M_MatchPO_ID="+ po1.getM_MatchPO_ID();
          int no1=DB.executeUpdate(s1,null);
          if (no1 != 1) {
            errors++;
            s_log.warning("Not updated M_MatchPO_ID=" + po1.getM_MatchPO_ID());
            continue;
          }
          String s2="DELETE FROM Fact_Acct WHERE AD_Table_ID=473 AND Record_ID=?";
          int no2=DB.executeUpdate(s2,po2.getM_MatchPO_ID(),null);
          String s3="DELETE FROM M_MatchPO WHERE M_MatchPO_ID=?";
          int no3=DB.executeUpdate(s3,po2.getM_MatchPO_ID(),null);
          if (no2 == 0 && no3 == 1)           success++;
 else {
            s_log.warning("M_MatchPO_ID=" + po2.getM_MatchPO_ID() + " - Deleted="+ no2+ ", Acct="+ no3);
            errors++;
          }
        }
      }
    }
  }
 catch (  Exception e) {
    s_log.log(Level.SEVERE,sql,e);
  }
 finally {
    DB.close(rs,pstmt);
    rs=null;
    pstmt=null;
  }
  if (errors == 0 && success == 0)   ;
 else   s_log.info("Success #" + success + " - Error #"+ errors);
}

public static void consolidate ( Properties ctx ) { String sql = "SELECT * FROM M_MatchPO po " + "WHERE EXISTS (SELECT 1 FROM M_MatchPO x " + "WHERE po.C_OrderLine_ID=x.C_OrderLine_ID AND po.Qty=x.Qty " + "GROUP BY C_OrderLine_ID, Qty " + "HAVING COUNT(*) = 2) " + " AND AD_Client_ID=?" + "ORDER BY C_OrderLine_ID, M_InOutLine_ID" ; PreparedStatement pstmt = null ; ResultSet rs = null ; int success = 0 ; int errors = 0 ; try { pstmt = DB . prepareStatement ( sql , null ) ; pstmt . setInt ( 1 , Env . getAD_Client_ID ( ctx ) ) ; rs = pstmt . executeQuery ( ) ; while ( rs . next ( ) ) { MMatchPO po1 = new MMatchPO ( ctx , rs , null ) ; if ( rs . next ( ) ) { MMatchPO po2 = new MMatchPO ( ctx , rs , null ) ; if ( po1 . getM_InOutLine_ID ( ) != 0 && po1 . getC_InvoiceLine_ID ( ) == 0 && po2 . getM_InOutLine_ID ( ) == 0 && po2 . getC_InvoiceLine_ID ( ) != 0 ) { String s1 = "UPDATE M_MatchPO SET C_InvoiceLine_ID=" + po2 . getC_InvoiceLine_ID ( ) + " WHERE M_MatchPO_ID=" + po1 . getM_MatchPO_ID ( ) ; int no1 = DB . executeUpdate ( s1 , null ) ; if ( no1 != 1 ) { errors ++ ; s_log . warning ( "Not updated M_MatchPO_ID=" + po1 . getM_MatchPO_ID ( ) ) ; continue ; } String s2 = "DELETE FROM Fact_Acct WHERE AD_Table_ID=473 AND Record_ID=?" ; int no2 = DB . executeUpdate ( s2 , po2 . getM_MatchPO_ID ( ) , null ) ; String s3 = "DELETE FROM M_MatchPO WHERE M_MatchPO_ID=?" ; int no3 = DB . executeUpdate ( s3 , po2 . getM_MatchPO_ID ( ) , null ) ; if ( no2 == 0 && no3 == 1 ) success ++ ; else { s_log . warning ( "M_MatchPO_ID=" + po2 . getM_MatchPO_ID ( ) + " - Deleted=" + no2 + ", Acct=" + no3 ) ; errors ++ ; } } } } } catch ( Exception e ) { s_log . log ( Level . SEVERE , sql , e ) ; } finally { DB . close ( rs , pstmt ) ; rs = null ; pstmt = null ; } if ( errors == 0 && success == 0 ) ; else s_log . info ( "Success #" + success + " - Error #" + errors ) ; }
consolidate mpo entries . ( data conversion issue )
