@Override
protected Object[] getParameters() {
List<Object> params = new ArrayList<Object>();
params.add(Env.getAD_Client_ID(getCtx()));
if (!Util.isEmpty(getOrgID(), true)) {
params.add(getOrgID());
}
if (!Util.isEmpty(getBpartnerID(), true)) {
params.add(getBpartnerID());
}
if (getDateFrom() != null) {
params.add(getDateFrom());
}
if (getDateTo() != null) {
params.add(getDateTo());
}
params.add(Env.getAD_Client_ID(getCtx()));
if (!Util.isEmpty(getOrgID(), true)) {
params.add(getOrgID());
}
if (!Util.isEmpty(getBpartnerID(), true)) {
params.add(getBpartnerID());
}
if (getDateFrom() != null) {
params.add(getDateFrom());
}
if (getDateTo() != null) {
params.add(getDateTo());
}
return params.toArray();
}
/** Inicialización de datos adicionales al ProcessInfo */
private void initilizeProcessInfoAditionals() {
if (!Util.isEmpty(m_pi.getTable_ID(), true) && !Util.isEmpty(m_pi.getRecord_ID(), true)) {
Integer docTypeID = null;
String documentNo = null;
M_Table table = M_Table.get(Env.getCtx(), m_pi.getTable_ID());
PO po = table.getPO(m_pi.getRecord_ID(), table.get_TrxName());
// Verifico si en la tabla actual existe la columna C_DocTypeTarget_ID o
// C_DocType_ID, si es así entonces obtengo su valor
String docTypeColumnName = "C_DocTypeTarget_ID";
int docTypeIndex = po.get_ColumnIndex(docTypeColumnName);
// Si no existe C_DocTypeTarget_ID, busco C_DocType_ID
if (docTypeIndex == -1) {
docTypeColumnName = "C_DocType_ID";
docTypeIndex = po.get_ColumnIndex(docTypeColumnName);
}
// Si tengo la columna con el tipo de documento, entonces obtengo su valor
if (docTypeIndex > -1) {
docTypeID = (Integer) po.get_Value(docTypeIndex);
}
// Verifico si en la tabla actual existe la columna DocumentNo, si es
// asi obtengo su valor
int documentNoIndex = po.get_ColumnIndex("DocumentNo");
if (documentNoIndex > -1) {
documentNo = (String) po.get_Value(documentNoIndex);
}
// Si tengo el tipo de documento, entonces guardo el jasperDTO en el Process Info
if (!Util.isEmpty(docTypeID, true)) {
JasperReportDTO jasperDTO = m_pi.new JasperReportDTO();
jasperDTO.setDocTypeID(docTypeID);
jasperDTO.setDocumentNo(documentNo);
m_pi.setJasperReportDTO(jasperDTO);
}
}
}
protected MOrder getOrder() {
if (order == null) {
MOrder the_order = null;
// 1) Obtengo el pedido desde el parámetro
if (getParameterValue("C_Order_ID") != null) {
the_order = new MOrder(getCtx(), (Integer) getParameterValue("C_Order_ID"), get_TrxName());
}
// 2) Si no existe, entonces lo busco por la factura
if (the_order == null
&& getInvoice() != null
&& !Util.isEmpty(getInvoice().getC_Order_ID(), true)) {
the_order = new MOrder(getCtx(), getInvoice().getC_Order_ID(), get_TrxName());
}
setOrder(the_order);
}
return order;
}
@Override
public void addReportParameters(Properties ctx, Map<String, Object> params) {
// Inventario
MInventory inventory =
new MInventory(ctx, ((BigDecimal) params.get("M_Inventory_ID")).intValue(), null);
params.put("INVENTORY_DOCUMENTNO", inventory.getDocumentNo());
// Opción de visualización: Todos o Sólo con diferencia
params.put(
"VISUAL_OPTION_DESCRIPTION",
MRefList.getListName(
ctx, getVisualOptionReferenceID(), (String) params.get("Visual_Option")));
// Línea de artículo
BigDecimal productLinesID = (BigDecimal) params.get("M_Product_Lines_ID");
if (!Util.isEmpty(productLinesID, true)) {
MProductLines productLines = new MProductLines(ctx, productLinesID.intValue(), null);
params.put("PRODUCT_LINES_VALUE", productLines.getValue());
params.put("PRODUCT_LINES_NAME", productLines.getName());
}
}
/**
* Mantenimiento de la tabla de inventario para que no crezca en cada importación
*
* @throws Exception
*/
protected int maintainInventoryImportTable(String securityCheck) throws Exception {
String maintenanceMonth =
MPreference.searchCustomPreferenceValue(
INVENTORY_MAINTENANCE_PREFERENCE,
getAD_Client_ID(),
Env.getAD_Org_ID(getCtx()),
Env.getAD_User_ID(getCtx()),
true);
// Si la preference no tiene nada, no se elimina nada
if (Util.isEmpty(maintenanceMonth, true)) {
return 0;
}
Integer months = 0;
try {
months = Integer.parseInt(maintenanceMonth);
} catch (Exception e) {
throw new Exception(
"La preferencia o valor predeterminado "
+ INVENTORY_MAINTENANCE_PREFERENCE
+ " no esta existo o contiene un valor no numerico");
}
months = months * -1;
// Eliminación de registros anteriores a los meses de tolerancia hacia atrás
String sql =
"DELETE FROM "
+ X_I_Inventory.Table_Name
+ " WHERE date_trunc('month',?::date) >= date_trunc('month',created) "
+ securityCheck;
Calendar toleranceDate = Calendar.getInstance();
toleranceDate.setTimeInMillis(Env.getDate().getTime());
toleranceDate.add(Calendar.MONTH, months);
PreparedStatement ps =
new CPreparedStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, sql, null, true);
ps.setTimestamp(1, new Timestamp(toleranceDate.getTimeInMillis()));
return ps.executeUpdate();
}
@Override
protected String getQuery() {
String invoiceDateColumn = null;
String paymentDateColumn = null;
StringBuffer var1 = new StringBuffer();
var1.append("SELECT dt.printname, ");
var1.append(" bp.name AS proveedor, ");
var1.append(" pt.name as regla, ");
var1.append(" ci.description, ");
var1.append(" oi.documentno, ");
var1.append(" oi.c_bpartner_id, ");
var1.append(" oi.c_invoice_id, ");
var1.append(" oi.c_invoicepayschedule_id, ");
var1.append(" oi.c_currency_id, ");
var1.append(" oi.issotrx, ");
var1.append(" TRUNC(oi.dateinvoiced) as dateinvoiced, ");
var1.append(" oi.netdays, ");
var1.append(" oi.duedate, ");
var1.append(" oi.daysdue, ");
var1.append(
" case when substring(dt.docbasetype from 3 for 1)='I' then oi.openamt else oi.openamt*-1 end as openamt , ");
var1.append(" oi.ad_org_id, ");
var1.append(" CASE ");
var1.append(" WHEN daysdue IS NULL THEN '' ");
var1.append(" WHEN daysdue <=- 30 THEN '3 - Mayor 30 dias' ");
var1.append(" WHEN daysdue >- 30 ");
var1.append(" AND daysdue <=- 7 THEN '2 - Proximos 30d' ");
var1.append(" WHEN daysdue >- 7 ");
var1.append(" AND daysdue <= 0 THEN '1 - Esta semana' ");
var1.append(" WHEN daysdue > 0 THEN '0 - Vencidos' ");
var1.append(" END AS estado ");
var1.append("FROM rv_openitem oi ");
var1.append(" INNER JOIN c_bpartner bp ");
var1.append(" ON ( oi.c_bpartner_id = bp.c_bpartner_id ) ");
var1.append(" INNER JOIN c_doctype dt ");
var1.append(" ON ( oi.c_doctypetarget_id = dt.c_doctype_id ) ");
var1.append(" INNER JOIN c_paymentterm pt ");
var1.append(" ON ( pt.c_paymentterm_id = oi.c_paymentterm_id ) ");
var1.append(" INNER JOIN c_invoice ci ");
var1.append(" ON ( ci.c_invoice_id = oi.c_invoice_id) ");
var1.append("WHERE oi.ad_client_id=? ");
if (!getTrxType().equals("B")) {
var1.append(" AND oi.issotrx = '").append(getTrxType().equals("C") ? "Y" : "N").append("' ");
}
if (!Util.isEmpty(getOrgID(), true)) {
var1.append(" AND oi.ad_org_id = ? ");
}
if (!Util.isEmpty(getBpartnerID(), true)) {
var1.append(" AND oi.c_bpartner_id = ? ");
}
if (getDateFrom() != null) {
invoiceDateColumn = getDateFilter().equals("T") ? "oi.dateinvoiced" : "oi.duedate";
var1.append(" AND ").append(invoiceDateColumn).append(" >= ?::date ");
}
if (getDateTo() != null) {
invoiceDateColumn =
invoiceDateColumn != null
? invoiceDateColumn
: getDateFilter().equals("T") ? "oi.dateinvoiced" : "oi.duedate";
var1.append(" AND ").append(invoiceDateColumn).append(" <= ?::date ");
}
var1.append(" AND oi.docstatus IN ( 'CO', 'CL' ) ");
var1.append(" AND oi.openamt > 0 ");
var1.append("UNION ");
var1.append("SELECT dt.printname, ");
var1.append(" bp.name AS proveedor, ");
var1.append(" pt.name as regla, ");
var1.append(" ci.description, ");
var1.append(" pi.documentno, ");
var1.append(" pi.c_bpartner_id, ");
var1.append(" NULL, ");
var1.append(" NULL, ");
var1.append(" pi.c_currency_id, ");
var1.append(" pi.isreceipt, ");
var1.append(" TRUNC(pi.datetrx) as dateinvoiced, ");
var1.append(" 0, ");
var1.append(" pi.dateacct, ");
var1.append(" NULL, ");
var1.append(" pi.availableamt *- 1 AS pago, ");
var1.append(" pi.ad_org_id, ");
var1.append(" '' AS estado ");
var1.append("FROM rv_payment pi ");
var1.append(" INNER JOIN c_bpartner bp ");
var1.append(" ON ( pi.c_bpartner_id = bp.c_bpartner_id ) ");
var1.append(" INNER JOIN c_doctype dt ");
var1.append(" ON ( pi.c_doctype_id = dt.c_doctype_id ) ");
var1.append(" INNER JOIN c_invoice ci ");
var1.append(" ON ( ci.c_invoice_id = pi.c_invoice_id) ");
var1.append(" INNER JOIN c_paymentterm pt ");
var1.append(" ON ( pt.c_paymentterm_id = ci.c_paymentterm_id ) ");
var1.append(" WHERE ");
var1.append(" pi.docstatus IN ( 'CO', 'CL' ) ");
var1.append(" AND pi.ad_client_id = ? ");
if (!getTrxType().equals("B")) {
var1.append(" AND pi.isreceipt = '")
.append(getTrxType().equals("C") ? "Y" : "N")
.append("' ");
}
if (!Util.isEmpty(getOrgID(), true)) {
var1.append(" AND pi.ad_org_id=? ");
}
if (!Util.isEmpty(getBpartnerID(), true)) {
var1.append(" AND pi.c_bpartner_id = ? ");
}
var1.append(" AND pi.availableamt > 0 ");
if (getDateFrom() != null) {
paymentDateColumn = getDateFilter().equals("T") ? "pi.datetrx" : "pi.dateacct";
var1.append(" AND ").append(paymentDateColumn).append(" >= ?::date ");
}
if (getDateTo() != null) {
paymentDateColumn =
paymentDateColumn != null
? paymentDateColumn
: getDateFilter().equals("T") ? "oi.dateinvoiced" : "oi.duedate";
var1.append(" AND ").append(paymentDateColumn).append(" <= ?::date ");
}
var1 = new StringBuffer("Select * from (").append(var1);
if (new String("Fecha").equals(getSubtotales_por())) {
var1.append(") AS T1 ORDER BY dateinvoiced ");
} else {
var1.append(") AS T1 ORDER BY proveedor ");
}
return var1.toString();
}
/**
* Descripción de Método
*
* @return
* @throws java.lang.Exception
*/
protected String doIt() throws java.lang.Exception {
String errorLocatorNotFound = "'" + getMsg("LocatorNotFound") + ". '";
String errorWarehouseNotFound = "'" + getMsg("WarehouseNotFound") + ". '";
String errorInvalidLocator = "'" + getMsg("InvalidLocatorWarehouse") + ". '";
String errorProductNotFound = "'" + getMsg("ProductNotFound") + ". '";
String errorInvalidQtyCount = "'" + getMsg("InvalidQtyCount") + ". '";
String errorNoInventory = "'" + getMsg("NoExistsInventory") + ". '";
log.info("M_Locator_ID=" + p_M_Locator_ID + ",MovementDate=" + p_MovementDate);
StringBuffer sql = null;
int no = 0;
String securityCheck = " AND AD_Client_ID=" + p_AD_Client_ID + " AND IsActive = 'Y' ";
maintainInventoryImportTable(securityCheck);
// Delete Old Imported
if (p_DeleteOldImported) {
sql =
new StringBuffer("DELETE I_Inventory " + "WHERE I_IsImported='Y'").append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Delete Old Impored =" + no);
}
// Reset
sql =
new StringBuffer(" UPDATE I_Inventory " + " SET AD_Client_ID = COALESCE (AD_Client_ID,")
.append(p_AD_Client_ID)
.append(")," + " AD_Org_ID = COALESCE (AD_Org_ID,")
.append(p_AD_Org_ID)
.append(
"),"
+ " IsActive = COALESCE (IsActive, 'Y'),"
+ " Created = COALESCE (Created, SysDate),"
+ " CreatedBy = COALESCE (CreatedBy, 0),"
+ " Updated = COALESCE (Updated, SysDate),"
+ " UpdatedBy = COALESCE (UpdatedBy, 0),"
+ " I_ErrorMsg = NULL, "
+ " I_IsImported = 'N' "
+ " WHERE I_IsImported <> 'Y' OR I_IsImported IS NULL")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.info("Reset=" + no);
// sql = new StringBuffer( "UPDATE I_Inventory i " + "SET I_IsImported='E',
// I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '" + " WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0" + "
// OR EXISTS (SELECT * FROM AD_Org oo WHERE i.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR
// oo.IsActive='N')))" + " AND I_IsImported<>'Y'" ).append( clientCheck );
// no = DB.executeUpdate( sql.toString());
// if( no != 0 ) {
// log.warning( "Invalid Org=" + no );
// }
//////////////////////////////////////////////////////////////////////////////////////
// Set Movement Date
//////////////////////////////////////////////////////////////////////////////////////
sql =
new StringBuffer(" UPDATE I_Inventory " + " SET MovementDate = ")
.append(DB.TO_DATE(p_MovementDate))
.append(" WHERE I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Movement Date = " + no);
//////////////////////////////////////////////////////////////////////////////////////
// Set Warehouse and Locator
//////////////////////////////////////////////////////////////////////////////////////
// Warehouse From Value
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET M_Warehouse_ID = "
+ " (SELECT M_Warehouse_ID "
+ " FROM M_Warehouse w"
+ " WHERE (TRIM(i.WarehouseValue) = TRIM(w.Value) OR"
+ " TRIM(i.WarehouseValue) = TRIM(w.Name)) AND "
+ " i.AD_Client_ID = w.AD_Client_ID AND "
+ " w.IsActive = 'Y' AND "
+ " ROWNUM=1) "
+ " WHERE WarehouseValue IS NOT NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Warehouse from Value = " + no);
// Locator From Value
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET M_Locator_ID = "
+ " (SELECT M_Locator_ID "
+ " FROM M_Locator l"
+ " WHERE i.LocatorValue = l.Value AND "
+ " i.AD_Client_ID = l.AD_Client_ID AND "
+ " l.IsActive = 'Y' AND "
+ " ROWNUM=1) "
+ " WHERE LocatorValue IS NOT NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Locator from Value = " + no);
// locator from X,Y,Z
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET M_Locator_ID="
+ " (SELECT M_Locator_ID "
+ " FROM M_Locator l"
+ " WHERE i.X=l.X AND i.Y=l.Y AND i.Z=l.Z AND "
+ " i.AD_Client_ID=l.AD_Client_ID AND "
+ " l.IsActive = 'Y' AND "
+ " ROWNUM=1) "
+ " WHERE M_Locator_ID IS NULL AND "
+ " X IS NOT NULL AND Y IS NOT NULL AND Z IS NOT NULL AND "
+ " I_IsImported<>'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Locator from X,Y,Z = " + no);
// Warehouse From Locator
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET M_Warehouse_ID = "
+ " (SELECT M_Warehouse_ID "
+ " FROM M_Locator l "
+ " WHERE i.M_Locator_ID=l.M_Locator_ID) "
+ " WHERE M_Warehouse_ID IS NULL AND "
+ " M_Locator_ID IS NOT NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Warehouse from Locator = " + no);
// locator default from warehouse
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET M_Locator_ID = "
+ " (SELECT M_Locator_ID "
+ " FROM M_Locator l "
+ " WHERE i.M_Warehouse_ID=l.M_Warehouse_ID"
+ " ORDER BY isdefault DESC "
+ " LIMIT 1) "
+ " WHERE M_Warehouse_ID IS NOT NULL AND "
+ " M_Locator_ID IS NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Locator Default from Warehouse = " + no);
// Locator From parameter (default locator)
if (p_M_Locator_ID != 0) {
sql =
new StringBuffer(" UPDATE I_Inventory " + " SET M_Locator_ID = ")
.append(p_M_Locator_ID)
.append(
", " + " LocatorValue = (SELECT Value FROM M_Locator WHERE M_Locator_ID = ")
.append(p_M_Locator_ID)
.append(") " + " WHERE M_Locator_ID IS NULL AND " + " I_IsImported<>'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Locator from Parameter = " + no);
}
// No locator detection
sql =
new StringBuffer(
" UPDATE I_Inventory "
+ " SET I_IsImported = 'E', "
+ " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || "
+ errorLocatorNotFound
+ " WHERE M_Locator_ID IS NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
if (no != 0) {
log.warning("Locator not found = " + no);
}
// No Warehouse detection
sql =
new StringBuffer(
" UPDATE I_Inventory "
+ " SET I_IsImported = 'E', "
+ " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || "
+ errorWarehouseNotFound
+ " WHERE M_Warehouse_ID IS NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
if (no != 0) {
log.warning("Warehouse not found = " + no);
}
// Locator warehouse not the line warehouse detection
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET I_IsImported = 'E', "
+ " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || "
+ errorInvalidLocator
+ " WHERE i.M_Warehouse_ID IS NOT NULL AND "
+ " i.M_Locator_ID IS NOT NULL AND "
+ " i.I_IsImported <> 'Y' AND "
+ " i.M_Warehouse_ID <> (SELECT l.M_Warehouse_ID "
+ " FROM M_Locator l "
+ " WHERE l.M_Locator_ID = i.M_Locator_ID) ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
if (no != 0) {
log.warning("Locator Warehouse not the line Warehouse = " + no);
}
//////////////////////////////////////////////////////////////////////////////////////
// Set Product
//////////////////////////////////////////////////////////////////////////////////////
// ... From Value
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET M_Product_ID = "
+ " (SELECT M_Product_ID "
+ " FROM M_Product p"
+ " WHERE i.Value = p.Value AND "
+ " i.AD_Client_ID = p.AD_Client_ID AND "
+ " p.IsActive = 'Y' AND "
+ " ROWNUM=1) "
+ " WHERE Value IS NOT NULL AND "
+ " I_IsImported <> 'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Product from Value=" + no);
// ... From UPC Instance
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET M_AttributeSetInstance_ID = "
+ " (SELECT M_AttributeSetInstance_ID "
+ " FROM M_Product_UPC_Instance p "
+ " WHERE i.UPC = p.UPC AND "
+ " i.M_Product_ID = p.M_Product_ID AND "
+ " i.AD_Client_ID = p.AD_Client_ID AND "
+ " p.IsActive = 'Y' AND "
+ " ROWNUM=1) "
+ " WHERE M_AttributeSetInstance_ID IS NULL AND "
+ " UPC IS NOT NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set AttributeInstance from UPC = " + no);
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET INSTANCE_DESCRIPTION = "
+ " (SELECT Description "
+ " FROM M_AttributeSetInstance p "
+ " WHERE i.M_AttributeSetInstance_ID = p.M_AttributeSetInstance_ID AND "
+ " i.AD_Client_ID = p.AD_Client_ID AND "
+ " p.IsActive = 'Y' AND "
+ " ROWNUM=1) "
+ " WHERE INSTANCE_DESCRIPTION IS NULL AND "
+ " M_AttributeSetInstance_ID IS NOT NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Instance_Description from AttributeInstance = " + no);
if (no == 0) {
// ... From UPC
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET M_Product_ID = "
+ " (SELECT M_Product_ID "
+ " FROM M_Product p "
+ " WHERE i.UPC = p.UPC AND "
+ " i.AD_Client_ID = p.AD_Client_ID AND "
+ " p.IsActive = 'Y' AND "
+ " ROWNUM=1) "
+ " WHERE M_Product_ID IS NULL AND "
+ " UPC IS NOT NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Product from UPC = " + no);
}
// No product detection
sql =
new StringBuffer(
" UPDATE I_Inventory "
+ " SET I_IsImported = 'E', "
+ " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || "
+ errorProductNotFound
+ " WHERE M_Product_ID IS NULL AND "
+ " I_IsImported<>'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
if (no != 0) {
log.warning("Product not found = " + no);
}
// No QtyCount
sql =
new StringBuffer(
" UPDATE I_Inventory "
+ " SET I_IsImported = 'E', "
+ " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || "
+ errorInvalidQtyCount
+ " WHERE QtyCount IS NULL AND "
+ " I_IsImported <> 'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
if (no != 0) {
log.warning("No QtyCount = " + no);
}
//////////////////////////////////////////////////////////////////////////////////////
// Set Organization from VALUE
//////////////////////////////////////////////////////////////////////////////////////
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET AD_Org_ID = "
+ " (SELECT AD_Org_ID "
+ " FROM AD_Org p"
+ " WHERE i.OrgValue = p.Value AND "
+ " i.AD_Client_ID = p.AD_Client_ID AND "
+ " p.IsActive = 'Y'"
+ " ) "
+ " WHERE OrgValue IS NOT NULL AND "
+ " I_IsImported <> 'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Org from OrgValue=" + no);
//////////////////////////////////////////////////////////////////////////////////////
// Set Organization
//////////////////////////////////////////////////////////////////////////////////////
sql =
new StringBuffer(
" UPDATE I_Inventory i "
+ " SET AD_Org_ID = "
+ " (SELECT AD_Org_ID "
+ " FROM M_Warehouse p "
+ " WHERE i.M_Warehouse_ID = p.M_Warehouse_ID AND "
+ " ROWNUM=1) "
+ " WHERE M_Warehouse_ID IS NOT NULL AND "
+ " I_IsImported <> 'Y' ")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Organization from Warehouse = " + no);
//////////////////////////////////////////////////////////////////////////////////////
// Set Inventory
//////////////////////////////////////////////////////////////////////////////////////
MDocType docTypePI =
MDocType.getDocType(getCtx(), MDocType.DOCTYPE_MaterialPhysicalInventory, null);
sql =
new StringBuffer(
"UPDATE I_Inventory i "
+ "SET M_Inventory_ID = (SELECT m.m_inventory_id "
+ " FROM m_inventory m "
+ " WHERE m.documentno = i.inventory_documentno "
+ " AND m.m_warehouse_id = i.m_warehouse_id"
+ " AND m.docstatus IN ('DR','IP') "
+ " AND m.inventorykind = '"
+ MInventory.INVENTORYKIND_PhysicalInventory
+ "'"
+ " AND m.c_doctype_id = "
+ docTypePI.getC_DocType_ID()
+ " LIMIT 1) "
+ "WHERE inventory_documentno is not null "
+ " AND M_Warehouse_ID IS NOT NULL "
+ " AND I_IsImported <> 'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Inventory ID From Inventory DocumentNo = " + no);
// Error si tiene un nro de documento pero no existe tal inventario
sql =
new StringBuffer(
" UPDATE I_Inventory "
+ " SET I_IsImported = 'E', "
+ " I_ErrorMsg = COALESCE(I_ErrorMsg,'') || "
+ errorNoInventory
+ " WHERE inventory_documentno is not null AND "
+ " M_Inventory_ID IS NULL AND "
+ " I_IsImported <> 'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
if (no != 0) {
log.warning("No Inventory ID = " + no);
}
//////////////////////////////////////////////////////////////////////////////////////
// Set Inventory Line
//////////////////////////////////////////////////////////////////////////////////////
sql =
new StringBuffer(
"UPDATE I_Inventory i "
+ "SET M_InventoryLine_ID = (SELECT m.m_inventoryline_id "
+ " FROM m_inventoryline m "
+ " WHERE m.m_product_id = i.m_product_id "
+ " AND m.m_inventory_id = i.m_inventory_id"
+ " AND (CASE WHEN i.m_attributesetinstance_id is null THEN m.m_attributesetinstance_id = 0"
+ " WHEN i.m_attributesetinstance_id = 0 THEN m.m_attributesetinstance_id = 0"
+ " ELSE m.m_attributesetinstance_id = i.m_attributesetinstance_id "
+ " END)"
+ " AND m.isactive = 'Y'"
+ " LIMIT 1) "
+ "WHERE M_Inventory_ID is not null "
+ " AND M_Product_ID is not null "
+ " AND I_IsImported <> 'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
log.fine("Set Inventory Line ID = " + no);
MInventory inventory = null;
MInventoryLine line = null;
int noInsert = 0;
int noUpdate = 0;
int noInsertLine = 0;
int noUpdateLine = 0;
boolean insertLine = false;
// Go through Inventory Records
sql =
new StringBuffer(
" SELECT I_Inventory_ID " + " FROM I_Inventory " + " WHERE I_IsImported = 'N'")
.append(securityCheck)
.append(
" ORDER BY M_Inventory_ID, M_Warehouse_ID, TRUNC(MovementDate), I_Inventory_ID");
try {
PreparedStatement pstmt = DB.prepareStatement(sql.toString());
ResultSet rs = pstmt.executeQuery();
//
int x_M_Warehouse_ID = -1;
Timestamp x_MovementDate = null;
X_I_Inventory imp = null;
while (rs.next()) {
imp = new X_I_Inventory(getCtx(), rs.getInt("I_Inventory_ID"), null);
Timestamp MovementDate = TimeUtil.getDay(imp.getMovementDate());
// Inventario existente
if (!Util.isEmpty(imp.getM_Inventory_ID(), true)) {
if (inventory == null || inventory.getM_Inventory_ID() != imp.getM_Inventory_ID()) {
inventory = new MInventory(getCtx(), imp.getM_Inventory_ID(), null);
manageInventory(inventory);
noUpdate++;
}
} else if ((inventory == null)
|| (imp.getM_Warehouse_ID() != x_M_Warehouse_ID)
|| !MovementDate.equals(x_MovementDate)) {
inventory = new MInventory(getCtx(), 0, null);
inventory.setClientOrg(imp.getAD_Client_ID(), imp.getAD_Org_ID());
inventory.setDescription("I " + imp.getM_Warehouse_ID() + " " + MovementDate);
inventory.setM_Warehouse_ID(imp.getM_Warehouse_ID());
inventory.setMovementDate(MovementDate);
manageInventory(inventory);
if (!inventory.save()) {
log.log(Level.SEVERE, "Inventory not saved: " + CLogger.retrieveErrorAsString());
break;
}
x_M_Warehouse_ID = imp.getM_Warehouse_ID();
x_MovementDate = MovementDate;
noInsert++;
}
// Line
// Added by Lucas Hernandez - Kunan
// int M_AttributeSetInstance_ID = 0;
// Línea de inventario existente
if (!Util.isEmpty(imp.getM_InventoryLine_ID(), true)) {
line = new MInventoryLine(getCtx(), imp.getM_InventoryLine_ID(), null);
line.setQtyCount(imp.getQtyCount());
line.setQtyInternalUse(line.getQtyBook().subtract(imp.getQtyCount()));
} else {
line =
new MInventoryLine(
inventory,
imp.getM_Locator_ID(),
imp.getM_Product_ID(),
imp.getM_AttributeSetInstance_ID(),
imp.getQtyBook(),
imp.getQtyCount());
}
insertLine = line.getM_InventoryLine_ID() == 0;
if (line.save()) {
no =
DB.executeUpdate(
"UPDATE I_Inventory SET i_isimported='Y',i_errormsg=null,m_inventory_id="
+ line.getM_Inventory_ID()
+ ",M_InventoryLine_ID="
+ line.getM_InventoryLine_ID()
+ ",processed='Y' WHERE i_inventory_id = "
+ imp.getID());
// imp.setI_IsImported( true );
// imp.setI_ErrorMsg(null);
// imp.setM_Inventory_ID( line.getM_Inventory_ID());
// imp.setM_InventoryLine_ID( line.getM_InventoryLine_ID());
// imp.setProcessed( true );
// if( imp.save()) {
if (no == 1) {
if (insertLine) {
noInsertLine++;
} else {
noUpdateLine++;
}
}
} else {
no =
DB.executeUpdate(
"UPDATE I_Inventory SET i_isimported='E',i_errormsg="
+ CLogger.retrieveErrorAsString()
+ " WHERE i_inventory_id = "
+ imp.getID());
}
}
rs.close();
pstmt.close();
} catch (Exception e) {
log.log(Level.SEVERE, "doIt", e);
}
// Set Error to indicator to not imported
sql =
new StringBuffer(
" UPDATE I_Inventory "
+ " SET I_IsImported = 'N', "
+ " Updated=SysDate "
+ " WHERE I_IsImported <> 'Y'")
.append(securityCheck);
no = DB.executeUpdate(sql.toString());
addLog(0, null, new BigDecimal(no), "@Errors@");
// Post Import
postImport();
addLog(0, null, new BigDecimal(noInsert), "@M_Inventory_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdate), "@M_Inventory_ID@: @Updated@");
addLog(0, null, new BigDecimal(noInsertLine), "@M_InventoryLine_ID@: @Inserted@");
addLog(0, null, new BigDecimal(noUpdateLine), "@M_InventoryLine_ID@: @Updated@");
return "";
} // doIt