/**
* 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();
}
/**
* 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();
}
