示例#1
0
  /**
   * Get SQL for Orders that needs to be shipped
   *
   * @return sql
   */
  private String getOrderSQL() {
    StringBuffer sql =
        new StringBuffer(
            "SELECT ic.C_Order_ID, o.Name, dt.Name, io.DocumentNo, bp.Name, ic.DateOrdered, ic.TotalLines "
                + "FROM C_Invoice_Candidate_v ic, AD_Org o, C_BPartner bp, C_DocType dt "
                + "   , M_InOut io " // @emmie - Proyectar información del remito (tipo de doc y
                                     // documentNo)
                + "WHERE ic.AD_Org_ID=o.AD_Org_ID"
                + " AND ic.C_BPartner_ID=bp.C_BPartner_ID"
                + " AND io.C_DocType_ID=dt.C_DocType_ID"
                + " AND io.C_Order_ID=ic.C_Order_ID"
                + " AND ic.AD_Client_ID=?");

    if (m_AD_Org_ID != null) sql.append(" AND ic.AD_Org_ID=").append(m_AD_Org_ID);
    if (m_C_BPartner_ID != null) sql.append(" AND ic.C_BPartner_ID=").append(m_C_BPartner_ID);

    // bug - [ 1713337 ] "Generate Invoices (manual)" show locked records.
    /* begin - Exclude locked records; @Trifon */
    int AD_User_ID = Env.getContextAsInt(Env.getCtx(), "#AD_User_ID");
    String lockedIDs = MPrivateAccess.getLockedRecordWhere(MOrder.Table_ID, AD_User_ID);
    if (lockedIDs != null) {
      if (sql.length() > 0) sql.append(" AND ");
      sql.append("ic.C_Order_ID").append(lockedIDs);
    }
    /* eng - Exclude locked records; @Trifon */

    //
    sql.append(" ORDER BY o.Name,bp.Name,DateOrdered");

    return sql.toString();
  }
示例#2
0
  /**
   * Get SQL for Customer RMA that need to be invoiced
   *
   * @return sql
   */
  private String getRMASql() {
    StringBuffer sql = new StringBuffer();
    sql.append(
        "SELECT rma.M_RMA_ID, org.Name, dt.Name, rma.DocumentNo, bp.Name, rma.Created, rma.Amt ");
    sql.append("FROM M_RMA rma INNER JOIN AD_Org org ON rma.AD_Org_ID=org.AD_Org_ID ");
    sql.append("INNER JOIN C_DocType dt ON rma.C_DocType_ID=dt.C_DocType_ID ");
    sql.append("INNER JOIN C_BPartner bp ON rma.C_BPartner_ID=bp.C_BPartner_ID ");
    sql.append("INNER JOIN M_InOut io ON rma.InOut_ID=io.M_InOut_ID ");
    sql.append("WHERE rma.DocStatus='CO' ");
    sql.append("AND dt.DocBaseType = 'SOO' ");
    // sql.append("AND NOT EXISTS (SELECT * FROM C_Invoice i ");
    // sql.append("WHERE i.M_RMA_ID=rma.M_RMA_ID AND i.DocStatus IN ('IP', 'CO', 'CL')) ");
    // sql.append("AND EXISTS (SELECT * FROM C_InvoiceLine il INNER JOIN M_InOutLine iol ");
    // sql.append("ON il.M_InOutLine_ID=iol.M_InOutLine_ID INNER JOIN C_Invoice i ");
    // sql.append("ON i.C_Invoice_ID=il.C_Invoice_ID WHERE i.DocStatus IN ('CO', 'CL') ");
    // sql.append("AND iol.M_InOutLine_ID IN ");
    // sql.append("(SELECT M_InOutLine_ID FROM M_RMALine rl WHERE rl.M_RMA_ID=rma.M_RMA_ID ");
    // sql.append("AND rl.M_InOutLine_ID IS NOT NULL)) ");
    sql.append("AND rma.AD_Client_ID=?");

    if (m_AD_Org_ID != null) sql.append(" AND rma.AD_Org_ID=").append(m_AD_Org_ID);
    if (m_C_BPartner_ID != null) sql.append(" AND bp.C_BPartner_ID=").append(m_C_BPartner_ID);

    int AD_User_ID = Env.getContextAsInt(Env.getCtx(), "#AD_User_ID");
    String lockedIDs = MPrivateAccess.getLockedRecordWhere(MRMA.Table_ID, AD_User_ID);
    if (lockedIDs != null) {
      sql.append(" AND rma.M_RMA_ID").append(lockedIDs);
    }

    sql.append(" ORDER BY org.Name, bp.Name, rma.Created ");

    return sql.toString();
  }