/** Save Selection Settings for PriceList */
protected void saveSelectionDetail() {
// publish for Callout to read
Integer ID = getSelectedRowKey();
Env.setContext(
Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_Product_ID", ID == null ? "0" : ID.toString());
KeyNamePair kn = (KeyNamePair) pickPriceList.getSelectedItem();
Env.setContext(Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_PriceList_Version_ID", kn.getID());
kn = (KeyNamePair) pickWarehouse.getSelectedItem();
Env.setContext(Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_Warehouse_ID", kn.getID());
//
if (m_M_AttributeSetInstance_ID == -1) // not selected
{
Env.setContext(Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_AttributeSetInstance_ID", "0");
Env.setContext(Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_Locator_ID", "0");
} else {
Env.setContext(
Env.getCtx(),
p_WindowNo,
Env.TAB_INFO,
"M_AttributeSetInstance_ID",
String.valueOf(m_M_AttributeSetInstance_ID));
Env.setContext(
Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_Locator_ID", String.valueOf(m_M_Locator_ID));
}
} // saveSelectionDetail
/**
* ************************************************************************ Action Listener
*
* @param e event
*/
public void actionPerformed(ActionEvent e) {
// don't requery if fieldValue and fieldName are empty
if ((e.getSource() == pickWarehouse || e.getSource() == pickPriceList)
&& (fieldValue.getText().length() == 0 && fieldName.getText().length() == 0)) return;
// Product Attribute Search
if (e.getSource().equals(m_InfoPAttributeButton)) {
cmd_InfoPAttribute();
return;
}
m_pAttributeWhere = null;
// Query Product Attribute Instance
int row = p_table.getSelectedRow();
if (e.getSource().equals(m_PAttributeButton) && row != -1) {
Integer productInteger = getSelectedRowKey();
String productName = (String) p_table.getValueAt(row, INDEX_NAME);
KeyNamePair warehouse = (KeyNamePair) pickWarehouse.getSelectedItem();
if (productInteger == null || productInteger.intValue() == 0 || warehouse == null) return;
String title = warehouse.getName() + " - " + productName;
IPAttributeInstance pai =
PAttributeInstance.get(
this, title, warehouse.getKey(), 0, productInteger.intValue(), m_C_BPartner_ID);
m_M_AttributeSetInstance_ID = pai.getM_AttributeSetInstance_ID();
m_M_Locator_ID = pai.getM_Locator_ID();
if (m_M_AttributeSetInstance_ID != -1) dispose(true);
return;
}
//
super.actionPerformed(e);
} // actionPerformed
/** Show History */
protected void showHistory() {
log.info("");
Integer M_Product_ID = getSelectedRowKey();
if (M_Product_ID == null) return;
KeyNamePair kn = (KeyNamePair) pickWarehouse.getSelectedItem();
int M_Warehouse_ID = kn.getKey();
int M_AttributeSetInstance_ID = m_M_AttributeSetInstance_ID;
if (m_M_AttributeSetInstance_ID < -1) // not selected
M_AttributeSetInstance_ID = 0;
//
InvoiceHistory ih =
new InvoiceHistory(
this, 0, M_Product_ID.intValue(), M_Warehouse_ID, M_AttributeSetInstance_ID);
ih.setVisible(true);
ih = null;
} // showHistory
/**
* ************************************************************************ Construct SQL Where
* Clause and define parameters (setParameters needs to set parameters) Includes first AND
*
* @return SQL WHERE clause
*/
protected String getSQLWhere() {
StringBuffer where = new StringBuffer();
// Optional PLV
int M_PriceList_Version_ID = 0;
KeyNamePair pl = (KeyNamePair) pickPriceList.getSelectedItem();
if (pl != null) M_PriceList_Version_ID = pl.getKey();
if (M_PriceList_Version_ID != 0) where.append(" AND pr.M_PriceList_Version_ID=?");
// Optional Product Category
if (getM_Product_Category_ID() > 0) {
where.append(" AND p.M_Product_Category_ID=?");
}
// Optional Attribute Set
if (getM_AttributeSet_ID() > 0) {
where.append(" AND p.M_AttributeSet_ID=?");
}
// Product Attribute Search
if (m_pAttributeWhere != null) {
where.append(m_pAttributeWhere);
return where.toString();
}
// => Value
String value = fieldValue.getText().toUpperCase();
if (!(value.equals("") || value.equals("%"))) where.append(" AND UPPER(p.Value) LIKE ?");
// => Name
String name = fieldName.getText().toUpperCase();
if (!(name.equals("") || name.equals("%"))) where.append(" AND UPPER(p.Name) LIKE ?");
// => UPC
String upc = fieldUPC.getText().toUpperCase();
if (!(upc.equals("") || upc.equals("%"))) where.append(" AND UPPER(p.UPC) LIKE ?");
// => SKU
String sku = fieldSKU.getText().toUpperCase();
if (!(sku.equals("") || sku.equals("%"))) where.append(" AND UPPER(p.SKU) LIKE ?");
// => Vendor
String vendor = fieldVendor.getText().toUpperCase();
if (!(vendor.equals("") || vendor.equals("%")))
where.append(" AND UPPER(bp.Name) LIKE ? AND ppo.IsCurrentVendor='Y'");
return where.toString();
} // getSQLWhere
/**
* Set Parameters for Query (as defined in getSQLWhere)
*
* @param pstmt pstmt
* @param forCount for counting records
* @throws SQLException
*/
protected void setParameters(PreparedStatement pstmt, boolean forCount) throws SQLException {
int index = 1;
// => Warehouse
int M_Warehouse_ID = 0;
KeyNamePair wh = (KeyNamePair) pickWarehouse.getSelectedItem();
if (wh != null) M_Warehouse_ID = wh.getKey();
if (!forCount) // parameters in select
{
for (int i = 0; i < p_layout.length; i++) {
if (p_layout[i].getColSQL().indexOf('?') != -1) pstmt.setInt(index++, M_Warehouse_ID);
}
}
log.fine("M_Warehouse_ID=" + M_Warehouse_ID + " (" + (index - 1) + "*)");
// => PriceList
int M_PriceList_Version_ID = 0;
KeyNamePair pl = (KeyNamePair) pickPriceList.getSelectedItem();
if (pl != null) M_PriceList_Version_ID = pl.getKey();
if (M_PriceList_Version_ID != 0) {
pstmt.setInt(index++, M_PriceList_Version_ID);
log.fine("M_PriceList_Version_ID=" + M_PriceList_Version_ID);
}
// => Product Category
int M_Product_Category_ID = getM_Product_Category_ID();
if (M_Product_Category_ID > 0) {
pstmt.setInt(index++, M_Product_Category_ID);
log.fine("M_Product_Category_ID=" + M_Product_Category_ID);
}
// => Attribute Set - @Trifon
int M_AttributeSet_ID = getM_AttributeSet_ID();
if (M_AttributeSet_ID > 0) {
pstmt.setInt(index++, M_AttributeSet_ID);
log.fine("M_AttributeSet_ID=" + M_AttributeSet_ID);
}
// Rest of Parameter in Query for Attribute Search
if (m_pAttributeWhere != null) return;
// => Value
String value = fieldValue.getText().toUpperCase();
if (!(value.equals("") || value.equals("%"))) {
if (!value.endsWith("%")) value += "%";
pstmt.setString(index++, value);
log.fine("Value: " + value);
}
// => Name
String name = fieldName.getText().toUpperCase();
if (!(name.equals("") || name.equals("%"))) {
if (!name.endsWith("%")) name += "%";
pstmt.setString(index++, name);
log.fine("Name: " + name);
}
// => UPC
String upc = fieldUPC.getText().toUpperCase();
if (!(upc.equals("") || upc.equals("%"))) {
if (!upc.endsWith("%")) upc += "%";
pstmt.setString(index++, upc);
log.fine("UPC: " + upc);
}
// => SKU
String sku = fieldSKU.getText().toUpperCase();
if (!(sku.equals("") || sku.equals("%"))) {
if (!sku.endsWith("%")) sku += "%";
pstmt.setString(index++, sku);
log.fine("SKU: " + sku);
}
// => Vendor
String vendor = fieldVendor.getText().toUpperCase();
if (!(vendor.equals("") || vendor.equals("%"))) {
if (!vendor.endsWith("%")) vendor += "%";
pstmt.setString(index++, vendor);
log.fine("Vendor: " + vendor);
}
} // setParameters
/** @return selected Attribute Set ID */
public int getM_AttributeSet_ID() {
int M_AttributeSet_ID = 0;
KeyNamePair as = (KeyNamePair) pickAS.getSelectedItem();
if (as != null) M_AttributeSet_ID = as.getKey();
return M_AttributeSet_ID;
}
/** @return selected product category ID */
public int getM_Product_Category_ID() {
int M_Product_Category_ID = 0;
KeyNamePair pc = (KeyNamePair) pickProductCategory.getSelectedItem();
if (pc != null) M_Product_Category_ID = pc.getKey();
return M_Product_Category_ID;
}
/** Create Grid */
private void createGrid() {
if (attributeCombo1 == null || m_setting) return; // init
int indexAttr1 = attributeCombo1.getSelectedIndex();
int indexAttr2 = attributeCombo2.getSelectedIndex();
if (indexAttr1 == indexAttr2) {
log.warning("Same Attribute Selected");
tabbedPane.setSelectedIndex(0);
return;
}
m_setting = true;
m_M_PriceList_Version_ID = 0;
KeyNamePair pl = (KeyNamePair) pickPriceList.getSelectedItem();
if (pl != null) m_M_PriceList_Version_ID = pl.getKey();
m_M_Warehouse_ID = 0;
KeyNamePair wh = (KeyNamePair) pickWarehouse.getSelectedItem();
if (wh != null) m_M_Warehouse_ID = wh.getKey();
// x dimension
int cols = 2;
MAttributeValue[] xValues = null;
if (indexAttr1 > 0) xValues = m_attributes[indexAttr1 - 1].getMAttributeValues();
if (xValues != null) {
cols = xValues.length;
log.info("X - " + m_attributes[indexAttr1 - 1].getName() + " #" + xValues.length);
}
// y dimension
int rows = 2;
MAttributeValue[] yValues = null;
if (indexAttr2 > 0) yValues = m_attributes[indexAttr2 - 1].getMAttributeValues();
if (yValues != null) {
rows = yValues.length;
log.info("Y - " + m_attributes[indexAttr2 - 1].getName() + " #" + yValues.length);
}
//
gridPanel.removeAll();
CPanel grid = new CPanel(new GridLayout(rows, cols, 5, 5));
gridPanel.add(modePanel, BorderLayout.NORTH);
gridPanel.add(new CScrollPane(grid), BorderLayout.CENTER);
//
log.info("Rows=" + rows + " - Cols=" + cols);
for (int row = 0; row < rows; row++) {
for (int col = 0; col < cols; col++) {
MAttributeValue xValue = null;
if (xValues != null) xValue = xValues[col];
MAttributeValue yValue = null;
if (yValues != null) yValue = yValues[row];
// log.fine("Row=" + row + " - Col=" + col);
//
if (row == 0 && col == 0) {
CPanel descr = new CPanel(new GridLayout(2, 1, 0, 0));
if (xValues != null)
descr.add(new JLabel(m_attributes[indexAttr1 - 1].getName(), JLabel.TRAILING));
if (yValues != null) descr.add(new JLabel(m_attributes[indexAttr2 - 1].getName()));
grid.add(descr);
} else if (row == 0) // column labels
{
if (xValue != null) {
grid.add(new JLabel(xValue.getName(), JLabel.TRAILING));
} else grid.add(new JLabel());
} else if (col == 0) // row labels
{
if (yValue != null) grid.add(new JLabel(yValue.getName()));
else grid.add(new JLabel());
} else {
grid.add(getGridElement(xValue, yValue));
}
}
}
//
tabbedPane.setSelectedIndex(1);
m_setting = false;
m_frame.pack();
} // createGrid
private void tableInit() {
m_sql = new StringBuffer();
m_sql2 = new StringBuffer();
// Mostrar todos los ACC y AAP
if (tableInit_option == 0) {
m_sql.append(
"select cb.value ||'-'|| cb.name vendor, co.name country, extract(month from cn.CREATED) month,"
+ " extract (year from cn.CREATED) year, NVL(round(sum(cn.XX_UNITPURCHASEPRICEBS),2),0) total,"
+ " dep.value || '-' || dep.name department, cat.value || '-' || cat.name category,"
+ " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID vendorID"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID and"
+ " cn.XX_NOTIFICATIONTYPE='ACC'and cn.C_ORDER_ID=o.C_ORDER_ID and"
+ " dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'");
m_groupBy =
" group by cb.value ||'-'|| cb.name, co.name, extract(month from cn.CREATED), extract (year from cn.CREATED), dep.value || '-' || dep.name,"
+ " cat.value || '-' || cat.name,o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID";
m_sql2.append(
"select cb.value ||'-'|| cb.name vendor, co.name country, det.XX_MONTH month,"
+ " det.XX_YEAR year, round(cn.XX_UNITPURCHASEPRICEBS,2) total, o.C_BPARTNER_ID vendorID,"
+ " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, cn.XX_CREDITNOTIFYRETURN_ID creditNotID, cn.XX_NOTIFICATIONTYPE tipo"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat, XX_VCN_DETAILADVICE det"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID and cn.XX_NOTIFICATIONTYPE<>'ACC'"
+ " and cn.C_ORDER_ID=o.C_ORDER_ID and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID"
+ " and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID and det.XX_CREDITNOTIFYRETURN_ID=cn.XX_CREDITNOTIFYRETURN_ID and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'");
m_groupBy2 =
" group by cb.value ||'-'|| cb.name, co.name, det.XX_MONTH, det.XX_YEAR, cn.XX_UNITPURCHASEPRICEBS,"
+ " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID, cn.XX_CREDITNOTIFYRETURN_ID, cn.XX_NOTIFICATIONTYPE";
// Búsqueda por mes
if (monthCombo.getSelectedIndex() != 0 && monthCombo.getSelectedItem() != null) {
m_sql
.append(" AND ")
.append("extract(month from cn.created)=")
.append(monthCombo.getSelectedIndex());
m_sql2.append(" AND ").append("det.XX_MONTH=").append(monthCombo.getSelectedIndex());
}
// Búsqueda por año
if (yearField.getValue() != null) {
m_sql
.append(" AND ")
.append("extract(year from cn.created)=")
.append(((BigDecimal) yearField.getValue()).intValue());
m_sql2
.append(" AND ")
.append("det.XX_YEAR=")
.append(((BigDecimal) yearField.getValue()).intValue());
}
// Búsqueda por proveedor
if (comboBPartner.getSelectedIndex() != 0 && comboBPartner.getSelectedItem() != null) {
if (((KeyNamePair) comboBPartner.getSelectedItem()).getKey() != 0) {
int clave_vendor = ((KeyNamePair) comboBPartner.getSelectedItem()).getKey();
m_sql.append(" AND ").append("o.C_BPartner_ID=").append(clave_vendor);
m_sql2.append(" AND ").append("o.C_BPartner_ID=").append(clave_vendor);
}
}
// agrego la categoria
if (categoryCombo.getValue() != null) {
KeyNamePair cat = (KeyNamePair) categoryCombo.getValue();
if (cat.getKey() != -1) {
m_sql.append(" AND o.XX_VMR_CATEGORY_ID = ").append(cat.getKey()).append(" ");
m_sql2.append(" AND o.XX_VMR_CATEGORY_ID = ").append(cat.getKey()).append(" ");
}
}
// agrego el departamento al query
if (departmentCombo.getValue() != null) {
KeyNamePair dep = (KeyNamePair) departmentCombo.getValue();
if (dep.getKey() != -1) {
m_sql.append(" AND o.XX_VMR_DEPARTMENT_ID = ").append(dep.getKey()).append(" ");
m_sql2.append(" AND o.XX_VMR_DEPARTMENT_ID = ").append(dep.getKey()).append(" ");
}
}
}
String SQL = m_sql.toString() + m_groupBy;
int i = 0;
xTableACC.setRowCount(i);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(SQL, null);
rs = pstmt.executeQuery();
while (rs.next()) {
xTableACC.setRowCount(i + 1);
// Proveedor
xTableACC.setValueAt(rs.getString("vendor"), i, 0); // 1
// País
xTableACC.setValueAt(rs.getString("country"), i, 1);
// Departamento
xTableACC.setValueAt(rs.getString("department"), i, 2);
// Categoría
xTableACC.setValueAt(rs.getString("category"), i, 3);
// Mes
xTableACC.setValueAt(rs.getInt("month"), i, 4);
// Año
xTableACC.setValueAt(rs.getInt("year"), i, 5);
// Compras hechas
xTableACC.setValueAt(rs.getBigDecimal("total"), i, 6);
// Se calculan los avisos cerrados(CER) de el mismo mes, año, proveedor, depto y categoría
String SQL2 =
"select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID"
+ " and cn.XX_NOTIFICATIONTYPE='ACC' and cn.C_ORDER_ID=o.C_ORDER_ID"
+ " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID"
+ " and XX_STATUS='CER' and extract(month from cn.CREATED)="
+ rs.getInt("month")
+ " and extract (year from cn.CREATED)="
+ rs.getInt("year")
+ " and o.C_BPARTNER_ID="
+ rs.getInt("vendorID")
+ " and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'";
PreparedStatement pstmt2 = null;
ResultSet rs2 = null;
try {
pstmt2 = DB.prepareStatement(SQL2, null);
rs2 = pstmt2.executeQuery();
if (rs2.next()) xTableACC.setValueAt(rs2.getBigDecimal("totalDesc"), i, 7);
else xTableACC.setValueAt(0, i, 7);
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
DB.closeResultSet(rs2);
DB.closeStatement(pstmt2);
}
// Se calculan los avisos pendientes(ACT) de el mismo mes, año, proveedor, depto y categoría
String SQL3 =
"select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID"
+ " and cn.XX_NOTIFICATIONTYPE='ACC' and cn.C_ORDER_ID=o.C_ORDER_ID"
+ " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID"
+ " and XX_STATUS='ACT' and extract(month from cn.CREATED)="
+ rs.getInt("month")
+ " and extract (year from cn.CREATED)="
+ rs.getInt("year")
+ " and o.C_BPARTNER_ID="
+ rs.getInt("vendorID")
+ " and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'";
PreparedStatement pstmt3 = null;
ResultSet rs3 = null;
try {
pstmt3 = DB.prepareStatement(SQL3, null);
rs3 = pstmt3.executeQuery();
if (rs3.next()) xTableACC.setValueAt(rs3.getBigDecimal("totalDesc"), i, 8);
else xTableACC.setValueAt(0, i, 8);
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
DB.closeResultSet(rs3);
DB.closeStatement(pstmt3);
}
i++;
}
} catch (SQLException e) {
e.getMessage();
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
String sqlAAP = m_sql2.toString() + m_groupBy2;
i = 0;
xTableAAP.setRowCount(i);
pstmt = null;
rs = null;
try {
pstmt = DB.prepareStatement(sqlAAP, null);
rs = pstmt.executeQuery();
while (rs.next()) {
xTableAAP.setRowCount(i + 1);
// Proveedor
xTableAAP.setValueAt(
new KeyNamePair(rs.getInt("vendorID"), rs.getString("vendor")), i, 0); // 1
// País
xTableAAP.setValueAt(
new KeyNamePair(rs.getInt("creditNotID"), rs.getString("country")), i, 1);
// Mes
xTableAAP.setValueAt(rs.getInt("month"), i, 2);
// Año
xTableAAP.setValueAt(rs.getInt("year"), i, 3);
// Tipo
xTableAAP.setValueAt(rs.getString("tipo"), i, 4);
// Compras hechas
xTableAAP.setValueAt(rs.getBigDecimal("total"), i, 5);
// Se calculan los avisos cerrados(CER) de el mismo mes, año, proveedor, depto y categoría
String SQL2 =
"select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID"
+ " and cn.XX_NOTIFICATIONTYPE<>'ACC' and cn.C_ORDER_ID=o.C_ORDER_ID"
+ " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID"
+ " and XX_STATUS='CER' and cn.XX_CREDITNOTIFYRETURN_ID="
+ rs.getInt("creditNotID")
+ " and o.C_BPARTNER_ID="
+ rs.getInt("vendorID")
+ " and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'";
PreparedStatement pstmt2 = null;
ResultSet rs2 = null;
try {
pstmt2 = DB.prepareStatement(SQL2, null);
rs2 = pstmt2.executeQuery();
if (rs2.next()) xTableAAP.setValueAt(rs2.getBigDecimal("totalDesc"), i, 6);
else xTableAAP.setValueAt(0, i, 6);
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
DB.closeResultSet(rs2);
DB.closeStatement(pstmt2);
}
// Se calculan los avisos pendientes(ACT) de el mismo mes, año, proveedor, depto y categoría
String SQL3 =
"select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc"
+ " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat"
+ " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID"
+ " and cn.XX_NOTIFICATIONTYPE<>'ACC' and cn.C_ORDER_ID=o.C_ORDER_ID"
+ " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID"
+ " and XX_STATUS='ACT' and cn.XX_CREDITNOTIFYRETURN_ID="
+ rs.getInt("creditNotID")
+ " and o.C_BPARTNER_ID="
+ rs.getInt("vendorID")
+ " and o.AD_CLIENT_ID="
+ ctx.getAD_Client_ID()
+ " AND o.ISSOTRX = 'N'";
PreparedStatement pstmt3 = null;
ResultSet rs3 = null;
try {
pstmt3 = DB.prepareStatement(SQL3, null);
rs3 = pstmt3.executeQuery();
if (rs3.next()) xTableAAP.setValueAt(rs3.getBigDecimal("totalDesc"), i, 7);
else xTableAAP.setValueAt(0, i, 7);
} catch (SQLException e) {
System.out.print(e.getMessage());
} finally {
DB.closeResultSet(rs3);
DB.closeStatement(pstmt3);
}
i++;
}
} catch (SQLException e) {
e.getMessage();
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
} // tableInit
