/// <summary>
/// Generate Invoices
/// </summary>
/// <param name="idr">pstmt order query</param>
/// <returns>info</returns>
//private String Generate(IDataReader idr)
private String Generate(DataTable dt)
{
//JID_1139 Avoided the duplicate charge line created Invoice(customer)
bool isAllownonItem = Util.GetValueOfString(GetCtx().GetContext("$AllowNonItem")).Equals("Y");
foreach (DataRow dr in dt.Rows)
{
MOrder order = new MOrder(GetCtx(), dr, Get_TrxName());
// Credit Limit check
MBPartner bp = MBPartner.Get(GetCtx(), order.GetC_BPartner_ID());
if (bp.GetCreditStatusSettingOn() == "CH")
{
decimal creditLimit = bp.GetSO_CreditLimit();
string creditVal = bp.GetCreditValidation();
if (creditLimit != 0)
{
decimal creditAvlb = creditLimit - bp.GetSO_CreditUsed();
if (creditAvlb <= 0)
{
if (creditVal == "C" || creditVal == "D" || creditVal == "F")
{
AddLog(Msg.GetMsg(GetCtx(), "StopInvoice") + bp.GetName());
continue;
}
else if (creditVal == "I" || creditVal == "J" || creditVal == "L")
{
if (_msg != null)
{
_msg.Clear();
}
_msg.Append(Msg.GetMsg(GetCtx(), "WarningInvoice") + bp.GetName());
//AddLog(Msg.GetMsg(GetCtx(), "WarningInvoice") + bp.GetName());
}
}
}
}
// JID_0161 // change here now will check credit settings on field only on Business Partner Header // Lokesh Chauhan 15 July 2019
else if (bp.GetCreditStatusSettingOn() == X_C_BPartner.CREDITSTATUSSETTINGON_CustomerLocation)
{
MBPartnerLocation bpl = new MBPartnerLocation(GetCtx(), order.GetC_BPartner_Location_ID(), null);
//MBPartner bpartner = MBPartner.Get(GetCtx(), order.GetC_BPartner_ID());
//if (bpl.GetCreditStatusSettingOn() == "CL")
//{
decimal creditLimit = bpl.GetSO_CreditLimit();
string creditVal = bpl.GetCreditValidation();
if (creditLimit != 0)
{
decimal creditAvlb = creditLimit - bpl.GetSO_CreditUsed();
if (creditAvlb <= 0)
{
if (creditVal == "C" || creditVal == "D" || creditVal == "F")
{
AddLog(Msg.GetMsg(GetCtx(), "StopInvoice") + bp.GetName() + " " + bpl.GetName());
continue;
}
else if (creditVal == "I" || creditVal == "J" || creditVal == "L")
{
if (_msg != null)
{
_msg.Clear();
}
_msg.Append(Msg.GetMsg(GetCtx(), "WarningInvoice") + bp.GetName() + " " + bpl.GetName());
//AddLog(Msg.GetMsg(GetCtx(), "WarningInvoice") + bp.GetName() + " " + bpl.GetName());
}
}
}
//}
}
// Credit Limit End
// New Invoice Location
// JID_1237 : While creating invoice need to consolidate order on the basis of Org, Payment Term, BP Location (Bill to Location) and Pricelist.
if (!_ConsolidateDocument ||
(_invoice != null &&
(_invoice.GetC_BPartner_Location_ID() != order.GetBill_Location_ID() ||
_invoice.GetC_PaymentTerm_ID() != order.GetC_PaymentTerm_ID() ||
_invoice.GetM_PriceList_ID() != order.GetM_PriceList_ID() ||
_invoice.GetAD_Org_ID() != order.GetAD_Org_ID() ||
((_invoice.GetC_ConversionType_ID() != 0 ? _invoice.GetC_ConversionType_ID() : defaultConversionType)
!= (order.GetC_ConversionType_ID() != 0 ? order.GetC_ConversionType_ID() : defaultConversionType))
)))
{
CompleteInvoice();
}
bool completeOrder = MOrder.INVOICERULE_AfterOrderDelivered.Equals(order.GetInvoiceRule());
// Schedule After Delivery
bool doInvoice = false;
if (MOrder.INVOICERULE_CustomerScheduleAfterDelivery.Equals(order.GetInvoiceRule()))
{
_bp = new MBPartner(GetCtx(), order.GetBill_BPartner_ID(), null);
if (_bp.GetC_InvoiceSchedule_ID() == 0)
{
log.Warning("BPartner has no Schedule - set to After Delivery");
order.SetInvoiceRule(MOrder.INVOICERULE_AfterDelivery);
order.Save();
}
else
{
MInvoiceSchedule ins = MInvoiceSchedule.Get(GetCtx(), _bp.GetC_InvoiceSchedule_ID(), Get_TrxName());
if (ins.CanInvoice(order.GetDateOrdered(), order.GetGrandTotal()))
{
doInvoice = true;
}
else
{
continue;
}
}
} // Schedule
// After Delivery
if (doInvoice || MOrder.INVOICERULE_AfterDelivery.Equals(order.GetInvoiceRule()))
{
MInOut shipment = null;
MInOutLine[] shipmentLines = order.GetShipmentLines();
MOrderLine[] oLines = order.GetLines(true, null);
for (int i = 0; i < shipmentLines.Length; i++)
{
MInOutLine shipLine = shipmentLines[i];
if (shipLine.IsInvoiced())
{
continue;
}
if (shipment == null ||
shipment.GetM_InOut_ID() != shipLine.GetM_InOut_ID())
{
shipment = new MInOut(GetCtx(), shipLine.GetM_InOut_ID(), Get_TrxName());
}
if (!shipment.IsComplete() || // ignore incomplete or reversals
shipment.GetDocStatus().Equals(MInOut.DOCSTATUS_Reversed))
{
continue;
}
//JID_1139 Avoided the duplicate charge records
if (shipLine.GetM_Product_ID() > 0 || isAllownonItem)
{
CreateLine(order, shipment, shipLine);
}
}// shipment lines
//JID_1139 Avoided the duplicate charge records
if (!isAllownonItem)
{
for (int i = 0; i < oLines.Length; i++)
{
MOrderLine oLine = oLines[i];
if (oLine.GetC_Charge_ID() > 0)
{
Decimal toInvoice = Decimal.Subtract(oLine.GetQtyOrdered(), oLine.GetQtyInvoiced());
log.Fine("Immediate - ToInvoice=" + toInvoice + " - " + oLine);
Decimal qtyEntered = toInvoice;
// Correct UOM for QtyEntered
if (oLine.GetQtyEntered().CompareTo(oLine.GetQtyOrdered()) != 0)
{
qtyEntered = Decimal.Round(Decimal.Divide(Decimal.Multiply(
toInvoice, oLine.GetQtyEntered()),
oLine.GetQtyOrdered()), 12, MidpointRounding.AwayFromZero);
}
//JID_1139_1 avoided the charge line with 0 qty inserted
if (oLine.IsContract() == false && oLine.GetQtyOrdered() > oLine.GetQtyInvoiced())
{
CreateLine(order, oLine, toInvoice, qtyEntered);
log.Info("ID " + oLine.Get_ID() + "Qty Ordered " + oLine.GetQtyOrdered() + " Qty Invoiced " + oLine.GetQtyInvoiced());
}
}
}
}
}
// After Order Delivered, Immediate
else
{
MOrderLine[] oLines = order.GetLines(true, null);
for (int i = 0; i < oLines.Length; i++)
{
MOrderLine oLine = oLines[i];
Decimal toInvoice = Decimal.Subtract(oLine.GetQtyOrdered(), oLine.GetQtyInvoiced());
if (toInvoice.CompareTo(Env.ZERO) == 0 && oLine.GetM_Product_ID() != 0)
{
continue;
}
//
bool fullyDelivered = oLine.GetQtyOrdered().CompareTo(oLine.GetQtyDelivered()) == 0;
//JID_1136: While creating the Invoices against the charge system should not check the Ordered qty= Delivered qty. need to check this only in case of products
if (completeOrder && oLine.GetC_Charge_ID() > 0)
{
fullyDelivered = true;
if (oLine.GetC_Charge_ID() > 0)
{
log.Fine("After Order Delivery - ToInvoice=" + toInvoice + " - " + oLine);
Decimal qtyEntered = toInvoice;
// Correct UOM for QtyEntered
if (oLine.GetQtyEntered().CompareTo(oLine.GetQtyOrdered()) != 0)
{
qtyEntered = Decimal.Round(Decimal.Divide(Decimal.Multiply(
toInvoice, oLine.GetQtyEntered()),
oLine.GetQtyOrdered()), 12, MidpointRounding.AwayFromZero);
}
//
if (oLine.IsContract() == false && !isAllownonItem)
{
CreateLine(order, oLine, toInvoice, qtyEntered);
log.Info("ID " + oLine.Get_ID() + "Qty Ordered " + oLine.GetQtyOrdered() + " Qty Invoiced " + oLine.GetQtyInvoiced());
}
}
}
// Complete Order
if (completeOrder && !fullyDelivered)
{
log.Fine("Failed CompleteOrder - " + oLine);
completeOrder = false;
break;
}
// Immediate
else if (MOrder.INVOICERULE_Immediate.Equals(order.GetInvoiceRule()))
{
log.Fine("Immediate - ToInvoice=" + toInvoice + " - " + oLine);
Decimal qtyEntered = toInvoice;
// Correct UOM for QtyEntered
if (oLine.GetQtyEntered().CompareTo(oLine.GetQtyOrdered()) != 0)
{
qtyEntered = Decimal.Round(Decimal.Divide(Decimal.Multiply(
toInvoice, oLine.GetQtyEntered()),
oLine.GetQtyOrdered()), 12, MidpointRounding.AwayFromZero);
}
//
if (oLine.IsContract() == false)
{
CreateLine(order, oLine, toInvoice, qtyEntered);
log.Info("ID " + oLine.Get_ID() + "Qty Ordered " + oLine.GetQtyOrdered() + " Qty Invoiced " + oLine.GetQtyInvoiced());
}
}
else
{
log.Fine("Failed: " + order.GetInvoiceRule()
+ " - ToInvoice=" + toInvoice + " - " + oLine);
}
} // for all order lines
if (MOrder.INVOICERULE_Immediate.Equals(order.GetInvoiceRule()))
{
_line += 1000;
}
}
// Complete Order successful
if (completeOrder && MOrder.INVOICERULE_AfterOrderDelivered.Equals(order.GetInvoiceRule()))
{
MInOut[] shipments = order.GetShipments(true);
for (int i = 0; i < shipments.Length; i++)
{
MInOut ship = shipments[i];
if (!ship.IsComplete() || // ignore incomplete or reversals
ship.GetDocStatus().Equals(MInOut.DOCSTATUS_Reversed))
{
continue;
}
MInOutLine[] shipLines = ship.GetLines(false);
for (int j = 0; j < shipLines.Length; j++)
{
MInOutLine shipLine = shipLines[j];
if (!order.IsOrderLine(shipLine.GetC_OrderLine_ID()))
{
continue;
}
if (!shipLine.IsInvoiced())
{
CreateLine(order, ship, shipLine);
}
} // lines
_line += 1000;
} // all shipments
} // complete Order
} // for all orders
CompleteInvoice();
return("@Created@ = " + _created);
}
/**
* Generate Shipments
* @param pstmt order query
* @return info
*/
private String Generate(IDataReader idr)
{
DataTable dt = new DataTable();
try
{
dt.Load(idr);
idr.Close();
foreach (DataRow dr in dt.Rows)// while (dr.next ())
{
MOrder order = new MOrder(GetCtx(), dr, Get_TrxName());
// Credit Limit check added by Vivek on 24/08/2016
MBPartner bp = MBPartner.Get(GetCtx(), order.GetC_BPartner_ID());
if (bp.GetCreditStatusSettingOn() == "CH")
{
decimal creditLimit = bp.GetSO_CreditLimit();
string creditVal = bp.GetCreditValidation();
if (creditLimit != 0)
{
decimal creditAvlb = creditLimit - bp.GetSO_CreditUsed();
if (creditAvlb <= 0)
{
if (creditVal == "C" || creditVal == "D" || creditVal == "F")
{
AddLog(Msg.GetMsg(GetCtx(), "StopInvoice") + bp.GetName());
continue;
}
else if (creditVal == "I" || creditVal == "J" || creditVal == "L")
{
if (_msg != null)
{
_msg.Clear();
}
_msg.Append(Msg.GetMsg(GetCtx(), "WarningInvoice") + bp.GetName());
//AddLog(Msg.GetMsg(GetCtx(), "WarningInvoice") + bp.GetName());
}
}
}
}
else
{
MBPartnerLocation bpl = new MBPartnerLocation(GetCtx(), order.GetC_BPartner_Location_ID(), null);
MBPartner bpartner = MBPartner.Get(GetCtx(), order.GetC_BPartner_ID());
if (bpl.GetCreditStatusSettingOn() == "CL")
{
decimal creditLimit = bpl.GetSO_CreditLimit();
string creditVal = bpl.GetCreditValidation();
if (creditLimit != 0)
{
decimal creditAvlb = creditLimit - bpl.GetSO_CreditUsed();
if (creditAvlb <= 0)
{
if (creditVal == "C" || creditVal == "D" || creditVal == "F")
{
AddLog(Msg.GetMsg(GetCtx(), "StopInvoice") + bp.GetName() + " " + bpl.GetName());
continue;
}
else if (creditVal == "I" || creditVal == "J" || creditVal == "L")
{
if (_msg != null)
{
_msg.Clear();
}
_msg.Append(Msg.GetMsg(GetCtx(), "WarningInvoice") + bp.GetName() + " " + bpl.GetName());
//AddLog(Msg.GetMsg(GetCtx(), "WarningInvoice") + bp.GetName() + " " + bpl.GetName());
}
}
}
}
}
// Credit Limit End
// New Invoice Location
if (!_ConsolidateDocument ||
(_invoice != null &&
(_invoice.GetC_BPartner_Location_ID() != order.GetBill_Location_ID() ||
_invoice.GetC_PaymentTerm_ID() != order.GetC_PaymentTerm_ID())))
{
CompleteInvoice();
}
bool completeOrder = MOrder.INVOICERULE_AfterOrderDelivered.Equals(order.GetInvoiceRule());
// Schedule After Delivery
bool doInvoice = false;
if (MOrder.INVOICERULE_CustomerScheduleAfterDelivery.Equals(order.GetInvoiceRule()))
{
_bp = new MBPartner(GetCtx(), order.GetBill_BPartner_ID(), null);
if (_bp.GetC_InvoiceSchedule_ID() == 0)
{
log.Warning("BPartner has no Schedule - set to After Delivery");
order.SetInvoiceRule(MOrder.INVOICERULE_AfterDelivery);
order.Save();
}
else
{
MInvoiceSchedule ins = MInvoiceSchedule.Get(GetCtx(), _bp.GetC_InvoiceSchedule_ID(), Get_TrxName());
if (ins.CanInvoice(order.GetDateOrdered(), order.GetGrandTotal()))
{
doInvoice = true;
}
else
{
continue;
}
}
} // Schedule
// After Delivery
if (doInvoice || MOrder.INVOICERULE_AfterDelivery.Equals(order.GetInvoiceRule()))
{
MInOut shipment = null;
MInOutLine[] shipmentLines = order.GetShipmentLines();
MOrderLine[] oLines = order.GetLines(true, null);
for (int i = 0; i < shipmentLines.Length; i++)
{
MInOutLine shipLine = shipmentLines[i];
if (shipLine.IsInvoiced())
{
continue;
}
if (shipment == null ||
shipment.GetM_InOut_ID() != shipLine.GetM_InOut_ID())
{
shipment = new MInOut(GetCtx(), shipLine.GetM_InOut_ID(), Get_TrxName());
}
if (!shipment.IsComplete() || // ignore incomplete or reversals
shipment.GetDocStatus().Equals(MInOut.DOCSTATUS_Reversed))
{
continue;
}
//
CreateLine(order, shipment, shipLine);
} // shipment lines
for (int i = 0; i < oLines.Length; i++)
{
MOrderLine oLine = oLines[i];
if (oLine.GetC_Charge_ID() > 0)
{
Decimal toInvoice = Decimal.Subtract(oLine.GetQtyOrdered(), oLine.GetQtyInvoiced());
log.Fine("Immediate - ToInvoice=" + toInvoice + " - " + oLine);
Decimal qtyEntered = toInvoice;
// Correct UOM for QtyEntered
if (oLine.GetQtyEntered().CompareTo(oLine.GetQtyOrdered()) != 0)
{
qtyEntered = Decimal.Round(Decimal.Divide(Decimal.Multiply(
toInvoice, oLine.GetQtyEntered()),
oLine.GetQtyOrdered()), 12, MidpointRounding.AwayFromZero);
}
//
if (oLine.IsContract() == false)
{
CreateLine(order, oLine, toInvoice, qtyEntered);
log.Info("ID " + oLine.Get_ID() + "Qty Ordered " + oLine.GetQtyOrdered() + " Qty Invoiced " + oLine.GetQtyInvoiced());
}
}
}
}
// After Order Delivered, Immediate
else
{
MOrderLine[] oLines = order.GetLines(true, null);
for (int i = 0; i < oLines.Length; i++)
{
MOrderLine oLine = oLines[i];
Decimal toInvoice = Decimal.Subtract(oLine.GetQtyOrdered(), oLine.GetQtyInvoiced());
if (toInvoice.CompareTo(Env.ZERO) == 0 && oLine.GetM_Product_ID() != 0)
{
continue;
}
//
bool fullyDelivered = oLine.GetQtyOrdered().CompareTo(oLine.GetQtyDelivered()) == 0;
// Complete Order
if (completeOrder && !fullyDelivered)
{
log.Fine("Failed CompleteOrder - " + oLine);
completeOrder = false;
break;
}
// Immediate
else if (MOrder.INVOICERULE_Immediate.Equals(order.GetInvoiceRule()))
{
log.Fine("Immediate - ToInvoice=" + toInvoice + " - " + oLine);
Decimal qtyEntered = toInvoice;
// Correct UOM for QtyEntered
if (oLine.GetQtyEntered().CompareTo(oLine.GetQtyOrdered()) != 0)
{
qtyEntered = Decimal.Round(Decimal.Divide(Decimal.Multiply(
toInvoice, oLine.GetQtyEntered()),
oLine.GetQtyOrdered()), 12, MidpointRounding.AwayFromZero);
}
//
if (oLine.IsContract() == false)
{
CreateLine(order, oLine, toInvoice, qtyEntered);
log.Info("ID " + oLine.Get_ID() + "Qty Ordered " + oLine.GetQtyOrdered() + " Qty Invoiced " + oLine.GetQtyInvoiced());
}
}
else
{
log.Fine("Failed: " + order.GetInvoiceRule()
+ " - ToInvoice=" + toInvoice + " - " + oLine);
}
} // for all order lines
if (MOrder.INVOICERULE_Immediate.Equals(order.GetInvoiceRule()))
{
_line += 1000;
}
}
// Complete Order successful
if (completeOrder && MOrder.INVOICERULE_AfterOrderDelivered.Equals(order.GetInvoiceRule()))
{
MInOut[] shipments = order.GetShipments(true);
for (int i = 0; i < shipments.Length; i++)
{
MInOut ship = shipments[i];
if (!ship.IsComplete() || // ignore incomplete or reversals
ship.GetDocStatus().Equals(MInOut.DOCSTATUS_Reversed))
{
continue;
}
MInOutLine[] shipLines = ship.GetLines(false);
for (int j = 0; j < shipLines.Length; j++)
{
MInOutLine shipLine = shipLines[j];
if (!order.IsOrderLine(shipLine.GetC_OrderLine_ID()))
{
continue;
}
if (!shipLine.IsInvoiced())
{
CreateLine(order, ship, shipLine);
}
} // lines
_line += 1000;
} // all shipments
} // complete Order
} // for all orders
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, "", e);
}
finally
{
if (idr != null)
{
idr.Close();
}
dt = null;
}
CompleteInvoice();
return("@Created@ = " + _created);
}
} // prepare
/// <summary>
/// Perrform Process.
/// </summary>
/// <returns>clear message</returns>
protected override String DoIt()
{
StringBuilder sql = null;
int no = 0;
String clientCheck = " AND AD_Client_ID=" + _AD_Client_ID;
// **** Prepare ****
// Delete Old Imported
if (_deleteOldImported)
{
sql = new StringBuilder("DELETE FROM I_Invoice "
+ "WHERE I_IsImported='Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Delete Old Impored =" + no);
}
// Set Client, Org, IsActive, Created/Updated
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET AD_Client_ID = COALESCE (AD_Client_ID,").Append(_AD_Client_ID).Append("),"
+ " AD_Org_ID = COALESCE (AD_Org_ID,").Append(_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");
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Info("Reset=" + no);
String ts = DataBase.DB.IsPostgreSQL() ? "COALESCE(I_ErrorMsg,'')" : "I_ErrorMsg"; //java bug, it could not be used directly
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Org, '"
+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
+ " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Org=" + no);
}
// Document Type - PO - SO
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
+ " AND d.DocBaseType IN ('API','APC') AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set PO DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
+ " AND d.DocBaseType IN ('ARI','ARC') AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set SO DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT C_DocType_ID FROM C_DocType d WHERE d.Name=o.DocTypeName"
+ " AND d.DocBaseType IN ('API','ARI','APC','ARC') AND o.AD_Client_ID=d.AD_Client_ID) "
//+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append (clientCheck);
+ "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid DocTypeName, ' "
+ "WHERE C_DocType_ID IS NULL AND DocTypeName IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid DocTypeName=" + no);
}
// DocType Default
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'"
+ " AND d.DocBaseType='API' AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx='N' AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set PO Default DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'"
+ " AND d.DocBaseType='ARI' AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx='Y' AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set SO Default DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_DocType_ID=(SELECT MAX(C_DocType_ID) FROM C_DocType d WHERE d.IsDefault='Y'"
+ " AND d.DocBaseType IN('ARI','API') AND o.AD_Client_ID=d.AD_Client_ID) "
+ "WHERE C_DocType_ID IS NULL AND IsSOTrx IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Fine("Set Default DocType=" + no);
}
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No DocType, ' "
+ "WHERE C_DocType_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No DocType=" + no);
}
// Set IsSOTrx
sql = new StringBuilder("UPDATE I_Invoice o SET IsSOTrx='Y' "
+ "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='ARI' AND o.AD_Client_ID=d.AD_Client_ID)"
+ " AND C_DocType_ID IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set IsSOTrx=Y=" + no);
sql = new StringBuilder("UPDATE I_Invoice o SET IsSOTrx='N' "
+ "WHERE EXISTS (SELECT * FROM C_DocType d WHERE o.C_DocType_ID=d.C_DocType_ID AND d.DocBaseType='API' AND o.AD_Client_ID=d.AD_Client_ID)"
+ " AND C_DocType_ID IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set IsSOTrx=N=" + no);
// Price List
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'"
+ " AND p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default Currency PriceList=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p WHERE p.IsDefault='Y'"
+ " AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default PriceList=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p "
+ " WHERE p.C_Currency_ID=o.C_Currency_ID AND p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_PriceList_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Currency PriceList=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_PriceList_ID=(SELECT MAX(M_PriceList_ID) FROM M_PriceList p "
+ " WHERE p.IsSOPriceList=o.IsSOTrx AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_PriceList_ID IS NULL AND C_Currency_ID IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set PriceList=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No PriceList, ' "
+ "WHERE M_PriceList_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No PriceList=" + no);
}
// Payment Rule
// We support Payment Rule being input in the login language
VAdvantage.Login.Language language = VAdvantage.Login.Language.GetLoginLanguage(GetCtx()); // Base Language
String AD_Language = language.GetAD_Language();
sql = new StringBuilder("UPDATE I_Invoice O " +
"SET PaymentRule= " +
"(SELECT R.Value " +
" FROM AD_Ref_List R " +
" left outer join AD_Ref_List_Trl RT " +
" on RT.AD_Ref_List_ID = R.AD_Ref_List_ID and RT.AD_Language = @param " +
" WHERE R.AD_Reference_ID = 195 and coalesce( RT.Name, R.Name ) = O.PaymentRuleName ) " +
"WHERE PaymentRule is null AND PaymentRuleName IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@param", AD_Language);
no = DataBase.DB.ExecuteQuery(sql.ToString(), param, Get_TrxName());
log.Fine("Set PaymentRule=" + no);
// do not set a default; if null, the import logic will derive from the business partner
// do not error in absence of a default
// Payment Term
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_PaymentTerm_ID=(SELECT C_PaymentTerm_ID FROM C_PaymentTerm p"
+ " WHERE o.PaymentTermValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE C_PaymentTerm_ID IS NULL AND PaymentTermValue IS NOT NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set PaymentTerm=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_PaymentTerm_ID=(SELECT MAX(C_PaymentTerm_ID) FROM C_PaymentTerm p"
+ " WHERE p.IsDefault='Y' AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE C_PaymentTerm_ID IS NULL AND o.PaymentTermValue IS NULL AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default PaymentTerm=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No PaymentTerm, ' "
+ "WHERE C_PaymentTerm_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No PaymentTerm=" + no);
}
// BP from EMail
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u"
+ " WHERE o.EMail=u.EMail AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) "
+ "WHERE C_BPartner_ID IS NULL AND EMail IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BP from EMail=" + no);
// BP from ContactName
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET (C_BPartner_ID,AD_User_ID)=(SELECT C_BPartner_ID,AD_User_ID FROM AD_User u"
+ " WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL) "
+ "WHERE C_BPartner_ID IS NULL AND ContactName IS NOT NULL"
+ " AND EXISTS (SELECT Name FROM AD_User u WHERE o.ContactName=u.Name AND o.AD_Client_ID=u.AD_Client_ID AND u.C_BPartner_ID IS NOT NULL GROUP BY Name HAVING COUNT(*)=1)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BP from ContactName=" + no);
// BP from Value
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_BPartner_ID=(SELECT MAX(C_BPartner_ID) FROM C_BPartner bp"
+ " WHERE o.BPartnerValue=bp.Value AND o.AD_Client_ID=bp.AD_Client_ID) "
+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BP from Value=" + no);
// Default BP
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_BPartner_ID=(SELECT C_BPartnerCashTrx_ID FROM AD_ClientInfo c"
+ " WHERE o.AD_Client_ID=c.AD_Client_ID) "
+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NULL AND Name IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Default BP=" + no);
// Existing Location ? Exact Match
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_BPartner_Location_ID=(SELECT C_BPartner_Location_ID"
+ " FROM C_BPartner_Location bpl INNER JOIN C_Location l ON (bpl.C_Location_ID=l.C_Location_ID)"
+ " WHERE o.C_BPartner_ID=bpl.C_BPartner_ID AND bpl.AD_Client_ID=o.AD_Client_ID"
+ " AND DUMP(o.Address1)=DUMP(l.Address1) AND DUMP(o.Address2)=DUMP(l.Address2)"
+ " AND DUMP(o.City)=DUMP(l.City) AND DUMP(o.Postal)=DUMP(l.Postal)"
+ " AND DUMP(o.C_Region_ID)=DUMP(l.C_Region_ID) AND DUMP(o.C_Country_ID)=DUMP(l.C_Country_ID)) "
+ "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
+ " AND I_IsImported='N'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Found Location=" + no);
// Set Location from BPartner
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_BPartner_Location_ID=(SELECT MAX(C_BPartner_Location_ID) FROM C_BPartner_Location l"
+ " WHERE l.C_BPartner_ID=o.C_BPartner_ID AND o.AD_Client_ID=l.AD_Client_ID"
+ " AND ((l.IsBillTo='Y' AND o.IsSOTrx='Y') OR o.IsSOTrx='N')"
+ ") "
+ "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set BP Location from BP=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BP Location, ' "
+ "WHERE C_BPartner_ID IS NOT NULL AND C_BPartner_Location_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No BP Location=" + no);
}
// Set Country
/**
* sql = new StringBuilder ("UPDATE I_Invoice o "
+ "SET CountryCode=(SELECT CountryCode FROM C_Country c WHERE c.IsDefault='Y'"
+ " AND c.AD_Client_ID IN (0, o.AD_Client_ID) AND ROWNUM=1) "
+ "WHERE C_BPartner_ID IS NULL AND CountryCode IS NULL AND C_Country_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append (clientCheck);
+ no = DataBase.DB.ExecuteQuery(sql.ToString(),null, Get_TrxName());
+ log.Fine("Set Country Default=" + no);
**/
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_Country_ID=(SELECT C_Country_ID FROM C_Country c"
+ " WHERE o.CountryCode=c.CountryCode AND c.IsSummary='N' AND c.AD_Client_ID IN (0, o.AD_Client_ID)) "
+ "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL AND CountryCode IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Country=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Country, ' "
+ "WHERE C_BPartner_ID IS NULL AND C_Country_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Country=" + no);
}
// Set Region
sql = new StringBuilder("UPDATE I_Invoice o "
+ "Set RegionName=(SELECT MAX(Name) FROM C_Region r"
+ " WHERE r.IsDefault='Y' AND r.C_Country_ID=o.C_Country_ID"
+ " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) "
+ "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Region Default=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice o "
+ "Set C_Region_ID=(SELECT C_Region_ID FROM C_Region r"
+ " WHERE r.Name=o.RegionName AND r.C_Country_ID=o.C_Country_ID"
+ " AND r.AD_Client_ID IN (0, o.AD_Client_ID)) "
+ "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL AND RegionName IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Region=" + no);
//
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Region, ' "
+ "WHERE C_BPartner_ID IS NULL AND C_Region_ID IS NULL "
+ " AND EXISTS (SELECT * FROM C_Country c"
+ " WHERE c.C_Country_ID=o.C_Country_ID AND c.HasRegion='Y')"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Region=" + no);
}
// Product
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p"
+ " WHERE o.ProductValue=p.Value AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND ProductValue IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Product from Value=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p"
+ " WHERE o.UPC=p.UPC AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND UPC IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Product from UPC=" + no);
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET M_Product_ID=(SELECT MAX(M_Product_ID) FROM M_Product p"
+ " WHERE o.SKU=p.SKU AND o.AD_Client_ID=p.AD_Client_ID) "
+ "WHERE M_Product_ID IS NULL AND SKU IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Product fom SKU=" + no);
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Product, ' "
+ "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Product=" + no);
}
// Tax
sql = new StringBuilder("UPDATE I_Invoice o "
+ "SET C_Tax_ID=(SELECT MAX(C_Tax_ID) FROM C_Tax t"
+ " WHERE o.TaxIndicator=t.TaxIndicator AND o.AD_Client_ID=t.AD_Client_ID) "
+ "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
log.Fine("Set Tax=" + no);
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=Invalid Tax, ' "
+ "WHERE C_Tax_ID IS NULL AND TaxIndicator IS NOT NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("Invalid Tax=" + no);
}
Commit();
// -- New BPartner ---------------------------------------------------
// Go through Invoice Records w/o C_BPartner_ID
sql = new StringBuilder("SELECT * FROM I_Invoice "
+ "WHERE I_IsImported='N' AND C_BPartner_ID IS NULL").Append(clientCheck);
IDataReader idr = null;
try
{
//PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString(), Get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
while (idr.Read())
{
X_I_Invoice imp = new X_I_Invoice(GetCtx(), idr, Get_TrxName());
if (imp.GetBPartnerValue() == null)
{
if (imp.GetEMail() != null)
{
imp.SetBPartnerValue(imp.GetEMail());
}
else if (imp.GetName() != null)
{
imp.SetBPartnerValue(imp.GetName());
}
else
{
continue;
}
}
if (imp.GetName() == null)
{
if (imp.GetContactName() != null)
{
imp.SetName(imp.GetContactName());
}
else
{
imp.SetName(imp.GetBPartnerValue());
}
}
// BPartner
MBPartner bp = MBPartner.Get(GetCtx(), imp.GetBPartnerValue());
if (bp == null)
{
bp = new MBPartner(GetCtx(), -1, Get_TrxName());
bp.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID());
bp.SetValue(imp.GetBPartnerValue());
bp.SetName(imp.GetName());
if (!bp.Save())
{
continue;
}
}
imp.SetC_BPartner_ID(bp.GetC_BPartner_ID());
// BP Location
MBPartnerLocation bpl = null;
MBPartnerLocation[] bpls = bp.GetLocations(true);
for (int i = 0; bpl == null && i < bpls.Length; i++)
{
if (imp.GetC_BPartner_Location_ID() == bpls[i].GetC_BPartner_Location_ID())
{
bpl = bpls[i];
}
// Same Location ID
else if (imp.GetC_Location_ID() == bpls[i].GetC_Location_ID())
{
bpl = bpls[i];
}
// Same Location Info
else if (imp.GetC_Location_ID() == 0)
{
MLocation loc = bpl.GetLocation(false);
if (loc.Equals(imp.GetC_Country_ID(), imp.GetC_Region_ID(),
imp.GetPostal(), "", imp.GetCity(),
imp.GetAddress1(), imp.GetAddress2()))
{
bpl = bpls[i];
}
}
}
if (bpl == null)
{
// New Location
MLocation loc = new MLocation(GetCtx(), 0, Get_TrxName());
loc.SetAddress1(imp.GetAddress1());
loc.SetAddress2(imp.GetAddress2());
loc.SetCity(imp.GetCity());
loc.SetPostal(imp.GetPostal());
if (imp.GetC_Region_ID() != 0)
{
loc.SetC_Region_ID(imp.GetC_Region_ID());
}
loc.SetC_Country_ID(imp.GetC_Country_ID());
if (!loc.Save())
{
continue;
}
//
bpl = new MBPartnerLocation(bp);
bpl.SetC_Location_ID(imp.GetC_Location_ID());
if (!bpl.Save())
{
continue;
}
}
imp.SetC_Location_ID(bpl.GetC_Location_ID());
imp.SetC_BPartner_Location_ID(bpl.GetC_BPartner_Location_ID());
// User/Contact
if (imp.GetContactName() != null ||
imp.GetEMail() != null ||
imp.GetPhone() != null)
{
MUser[] users = bp.GetContacts(true);
MUser user = null;
for (int i = 0; user == null && i < users.Length; i++)
{
String name = users[i].GetName();
if (name.Equals(imp.GetContactName()) ||
name.Equals(imp.GetName()))
{
user = users[i];
imp.SetAD_User_ID(user.GetAD_User_ID());
}
}
if (user == null)
{
user = new MUser(bp);
if (imp.GetContactName() == null)
{
user.SetName(imp.GetName());
}
else
{
user.SetName(imp.GetContactName());
}
user.SetEMail(imp.GetEMail());
user.SetPhone(imp.GetPhone());
if (user.Save())
{
imp.SetAD_User_ID(user.GetAD_User_ID());
}
}
}
imp.Save();
} // for all new BPartners
idr.Close();
//
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, "CreateBP", e);
}
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='E', I_ErrorMsg=" + ts + "||'ERR=No BPartner, ' "
+ "WHERE C_BPartner_ID IS NULL"
+ " AND I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
if (no != 0)
{
log.Warning("No BPartner=" + no);
}
Commit();
// -- New Invoices -----------------------------------------------------
int noInsert = 0;
int noInsertLine = 0;
// Go through Invoice Records w/o
sql = new StringBuilder("SELECT * FROM I_Invoice "
+ "WHERE I_IsImported='N'").Append(clientCheck)
.Append(" ORDER BY C_BPartner_ID, C_BPartner_Location_ID, I_Invoice_ID");
try
{
//PreparedStatement pstmt = DataBase.prepareStatement (sql.ToString(), Get_TrxName());
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, Get_TrxName());
// Group Change
int oldC_BPartner_ID = 0;
int oldC_BPartner_Location_ID = 0;
String oldDocumentNo = "";
//
MInvoice invoice = null;
int lineNo = 0;
while (idr.Read())
{
X_I_Invoice imp = new X_I_Invoice(GetCtx(), idr, null);
String cmpDocumentNo = imp.GetDocumentNo();
if (cmpDocumentNo == null)
{
cmpDocumentNo = "";
}
// New Invoice
if (oldC_BPartner_ID != imp.GetC_BPartner_ID() ||
oldC_BPartner_Location_ID != imp.GetC_BPartner_Location_ID() ||
!oldDocumentNo.Equals(cmpDocumentNo))
{
if (invoice != null)
{
invoice.ProcessIt(_docAction);
invoice.Save();
}
// Group Change
oldC_BPartner_ID = imp.GetC_BPartner_ID();
oldC_BPartner_Location_ID = imp.GetC_BPartner_Location_ID();
oldDocumentNo = imp.GetDocumentNo();
if (oldDocumentNo == null)
{
oldDocumentNo = "";
}
//
invoice = new MInvoice(GetCtx(), 0, null);
invoice.SetClientOrg(imp.GetAD_Client_ID(), imp.GetAD_Org_ID());
invoice.SetC_DocTypeTarget_ID(imp.GetC_DocType_ID(), true);
if (imp.GetDocumentNo() != null)
{
invoice.SetDocumentNo(imp.GetDocumentNo());
}
//
invoice.SetC_BPartner_ID(imp.GetC_BPartner_ID());
invoice.SetC_BPartner_Location_ID(imp.GetC_BPartner_Location_ID());
if (imp.GetAD_User_ID() != 0)
{
invoice.SetAD_User_ID(imp.GetAD_User_ID());
}
//
if (imp.GetDescription() != null)
{
invoice.SetDescription(imp.GetDescription());
}
if (imp.GetPaymentRule() != null)
{
invoice.SetPaymentRule(imp.GetPaymentRule());
}
invoice.SetC_PaymentTerm_ID(imp.GetC_PaymentTerm_ID());
invoice.SetM_PriceList_ID(imp.GetM_PriceList_ID());
MPriceList pl = MPriceList.Get(GetCtx(), imp.GetM_PriceList_ID(), Get_TrxName());
invoice.SetIsTaxIncluded(pl.IsTaxIncluded());
// SalesRep from Import or the person running the import
if (imp.GetSalesRep_ID() != 0)
{
invoice.SetSalesRep_ID(imp.GetSalesRep_ID());
}
if (invoice.GetSalesRep_ID() == 0)
{
invoice.SetSalesRep_ID(GetAD_User_ID());
}
//
if (imp.GetAD_OrgTrx_ID() != 0)
{
invoice.SetAD_OrgTrx_ID(imp.GetAD_OrgTrx_ID());
}
if (imp.GetC_Activity_ID() != 0)
{
invoice.SetC_Activity_ID(imp.GetC_Activity_ID());
}
if (imp.GetC_Campaign_ID() != 0)
{
invoice.SetC_Campaign_ID(imp.GetC_Campaign_ID());
}
if (imp.GetC_Project_ID() != 0)
{
invoice.SetC_Project_ID(imp.GetC_Project_ID());
}
//
if (imp.GetDateInvoiced() != null)
{
invoice.SetDateInvoiced(imp.GetDateInvoiced());
}
if (imp.GetDateAcct() != null)
{
invoice.SetDateAcct(imp.GetDateAcct());
}
//
invoice.Save();
noInsert++;
lineNo = 10;
}
imp.SetC_Invoice_ID(invoice.GetC_Invoice_ID());
// New InvoiceLine
MInvoiceLine line = new MInvoiceLine(invoice);
if (imp.GetLineDescription() != null)
{
line.SetDescription(imp.GetLineDescription());
}
line.SetLine(lineNo);
lineNo += 10;
if (imp.GetM_Product_ID() != 0)
{
line.SetM_Product_ID(imp.GetM_Product_ID(), true);
}
line.SetQty(imp.GetQtyOrdered());
line.SetPrice();
Decimal?price = (Decimal?)imp.GetPriceActual();
if (price != null && Env.ZERO.CompareTo(price) != 0)
{
line.SetPrice(price.Value);
}
if (imp.GetC_Tax_ID() != 0)
{
line.SetC_Tax_ID(imp.GetC_Tax_ID());
}
else
{
line.SetTax();
imp.SetC_Tax_ID(line.GetC_Tax_ID());
}
Decimal?taxAmt = (Decimal?)imp.GetTaxAmt();
if (taxAmt != null && Env.ZERO.CompareTo(taxAmt) != 0)
{
line.SetTaxAmt(taxAmt);
}
line.Save();
//
imp.SetC_InvoiceLine_ID(line.GetC_InvoiceLine_ID());
imp.SetI_IsImported(X_I_Invoice.I_ISIMPORTED_Yes);
imp.SetProcessed(true);
//
if (imp.Save())
{
noInsertLine++;
}
}
if (invoice != null)
{
invoice.ProcessIt(_docAction);
invoice.Save();
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, "CreateInvoice", e);
}
// Set Error to indicator to not imported
sql = new StringBuilder("UPDATE I_Invoice "
+ "SET I_IsImported='N', Updated=SysDate "
+ "WHERE I_IsImported<>'Y'").Append(clientCheck);
no = DataBase.DB.ExecuteQuery(sql.ToString(), null, Get_TrxName());
AddLog(0, null, Utility.Util.GetValueOfDecimal(no), "@Errors@");
//
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsert), "@C_Invoice_ID@: @Inserted@");
AddLog(0, null, Utility.Util.GetValueOfDecimal(noInsertLine), "@C_InvoiceLine_ID@: @Inserted@");
return("");
} // doIt
/// <summary>
/// Generate Shipments
/// </summary>
/// <param name="idr">Order Query</param>
/// <returns>info</returns>
private String Generate(IDataReader idr)
{
DataTable dt = new DataTable();
MClient client = MClient.Get(GetCtx());
try
{
dt.Load(idr);
idr.Close();
//ResultSet dr = pstmt.executeQuery();
foreach (DataRow dr in dt.Rows)// while (dr.next ()) // Order
{
order = new MOrder(GetCtx(), dr, Get_TrxName());
// Credit Limit check added by Vivek on 24/08/2016
MBPartner bp = MBPartner.Get(GetCtx(), order.GetC_BPartner_ID());
if (bp.GetCreditStatusSettingOn() == "CH")
{
decimal creditLimit = bp.GetSO_CreditLimit();
string creditVal = bp.GetCreditValidation();
if (creditLimit != 0)
{
decimal creditAvlb = creditLimit - bp.GetSO_CreditUsed();
if (creditAvlb <= 0)
{
if (creditVal == "B" || creditVal == "D" || creditVal == "E" || creditVal == "F")
{
AddLog(Msg.GetMsg(GetCtx(), "StopShipment") + bp.GetName());
continue;
}
else if (creditVal == "H" || creditVal == "J" || creditVal == "K" || creditVal == "L")
{
if (_msg != null)
{
_msg.Clear();
}
_msg.Append(Msg.GetMsg(GetCtx(), "WarningShipment") + bp.GetName());
//AddLog(Msg.GetMsg(GetCtx(), "WarningShipment") + bp.GetName());
}
}
}
}
else
{
MBPartnerLocation bpl = new MBPartnerLocation(GetCtx(), order.GetC_BPartner_Location_ID(), null);
MBPartner bpartner = MBPartner.Get(GetCtx(), order.GetC_BPartner_ID());
if (bpl.GetCreditStatusSettingOn() == "CL")
{
decimal creditLimit = bpl.GetSO_CreditLimit();
string creditVal = bpl.GetCreditValidation();
if (creditLimit != 0)
{
decimal creditAvlb = creditLimit - bpl.GetSO_CreditUsed();
if (creditAvlb <= 0)
{
if (creditVal == "B" || creditVal == "D" || creditVal == "E" || creditVal == "F")
{
AddLog(Msg.GetMsg(GetCtx(), "StopShipment") + bp.GetName() + " " + bpl.GetName());
continue;
}
else if (creditVal == "H" || creditVal == "J" || creditVal == "K" || creditVal == "L")
{
if (_msg != null)
{
_msg.Clear();
}
_msg.Append(Msg.GetMsg(GetCtx(), "WarningShipment") + bp.GetName() + " " + bpl.GetName());
//AddLog(Msg.GetMsg(GetCtx(), "WarningShipment") + bp.GetName() + " " + bpl.GetName());
}
}
}
}
}
// Credit Limit End
// New Header different Shipper, Shipment Location
if (!_consolidateDocument ||
(_shipment != null &&
(_shipment.GetC_BPartner_Location_ID() != order.GetC_BPartner_Location_ID() ||
_shipment.GetM_Shipper_ID() != order.GetM_Shipper_ID())))
{
CompleteShipment();
}
log.Fine("check: " + order + " - DeliveryRule=" + order.GetDeliveryRule());
//
DateTime?minGuaranteeDate = _movementDate;
bool completeOrder = MOrder.DELIVERYRULE_CompleteOrder.Equals(order.GetDeliveryRule());
// OrderLine WHERE
String where = " AND M_Warehouse_ID=" + _M_Warehouse_ID;
if (_datePromised != null)
{
where += " AND (TRUNC(DatePromised,'DD')<=" + DB.TO_DATE((DateTime?)_datePromised, true)
+ " OR DatePromised IS NULL)";
//where += " AND (TRUNC(DatePromised,'DD')<='" + String.Format("{0:dd-MMM-yy}", _datePromised)
// + "' OR DatePromised IS NULL)";
}
// Exclude Auto Delivery if not Force
if (!MOrder.DELIVERYRULE_Force.Equals(order.GetDeliveryRule()))
{
where += " AND (C_OrderLine.M_Product_ID IS NULL"
+ " OR EXISTS (SELECT * FROM M_Product p "
+ "WHERE C_OrderLine.M_Product_ID=p.M_Product_ID"
+ " AND IsExcludeAutoDelivery='N'))";
}
// Exclude Unconfirmed
if (!_isUnconfirmedInOut)
{
where += " AND NOT EXISTS (SELECT * FROM M_InOutLine iol"
+ " INNER JOIN M_InOut io ON (iol.M_InOut_ID=io.M_InOut_ID) "
+ "WHERE iol.C_OrderLine_ID=C_OrderLine.C_OrderLine_ID AND io.DocStatus IN ('IP','WC'))";
}
// Deadlock Prevention - Order by M_Product_ID
MOrderLine[] lines = order.GetLines(where, "ORDER BY C_BPartner_Location_ID, M_Product_ID");
for (int i = 0; i < lines.Length; i++)
{
MOrderLine line = lines[i];
// if order line is not drop ship type
if (!line.IsDropShip())
{
if (line.GetM_Warehouse_ID() != _M_Warehouse_ID)
{
continue;
}
log.Fine("check: " + line);
Decimal onHand = Env.ZERO;
Decimal toDeliver = Decimal.Subtract(line.GetQtyOrdered(),
line.GetQtyDelivered());
Decimal QtyNotDelivered = Util.GetValueOfDecimal(DB.ExecuteScalar(@"SELECT SUM(MovementQty) FROM M_Inout i INNER JOIN M_InoutLine il ON i.M_Inout_ID = il.M_Inout_ID
WHERE il.C_OrderLine_ID = " + line.GetC_OrderLine_ID() + @" AND il.Isactive = 'Y' AND i.docstatus NOT IN ('RE' , 'VO' , 'CL' , 'CO')", null, Get_Trx()));
toDeliver -= QtyNotDelivered;
MProduct product = line.GetProduct();
// Nothing to Deliver
if (product != null && Env.Signum(toDeliver) == 0)
{
continue;
}
// Added by Bharat on 07 April 2017 as code already on Admpiere but deleted here.
if (line.GetC_Charge_ID() != 0)
{
continue;
}
// Check / adjust for confirmations
Decimal unconfirmedShippedQty = Env.ZERO;
if (_isUnconfirmedInOut && product != null && Env.Signum(toDeliver) != 0)
{
String where2 = "EXISTS (SELECT * FROM M_InOut io WHERE io.M_InOut_ID=M_InOutLine.M_InOut_ID AND io.DocStatus IN ('IP','WC'))";
MInOutLine[] iols = MInOutLine.GetOfOrderLine(GetCtx(),
line.GetC_OrderLine_ID(), where2, null);
for (int j = 0; j < iols.Length; j++)
{
unconfirmedShippedQty = Decimal.Add(unconfirmedShippedQty, iols[j].GetMovementQty());
}
String logInfo = "Unconfirmed Qty=" + unconfirmedShippedQty
+ " - ToDeliver=" + toDeliver + "->";
toDeliver = Decimal.Subtract(toDeliver, unconfirmedShippedQty);
logInfo += toDeliver;
if (Env.Signum(toDeliver) < 0)
{
toDeliver = Env.ZERO;
logInfo += " (set to 0)";
}
// Adjust On Hand
onHand = Decimal.Subtract(onHand, unconfirmedShippedQty);
log.Fine(logInfo);
}
// Comments & lines w/o product & services
if ((product == null || !product.IsStocked()) &&
(Env.Signum(line.GetQtyOrdered()) == 0 || // comments
Env.Signum(toDeliver) != 0)) // lines w/o product
{
if (!MOrder.DELIVERYRULE_CompleteOrder.Equals(order.GetDeliveryRule())) // printed later
{
CreateLine(order, line, toDeliver, null, false);
}
continue;
}
// Stored Product
MProductCategory pc = MProductCategory.Get(order.GetCtx(), product.GetM_Product_Category_ID());
String MMPolicy = pc.GetMMPolicy();
if (MMPolicy == null || MMPolicy.Length == 0)
{
MMPolicy = client.GetMMPolicy();
}
//
MStorage[] storages = GetStorages(line.GetM_Warehouse_ID(),
line.GetM_Product_ID(), line.GetM_AttributeSetInstance_ID(),
product.GetM_AttributeSet_ID(),
line.GetM_AttributeSetInstance_ID() == 0,
(DateTime?)minGuaranteeDate,
MClient.MMPOLICY_FiFo.Equals(MMPolicy));
for (int j = 0; j < storages.Length; j++)
{
MStorage storage = storages[j];
onHand = Decimal.Add(onHand, storage.GetQtyOnHand());
}
bool fullLine = onHand.CompareTo(toDeliver) >= 0 ||
Env.Signum(toDeliver) < 0;
// Complete Order
if (completeOrder && !fullLine)
{
log.Fine("Failed CompleteOrder - OnHand=" + onHand
+ " (Unconfirmed=" + unconfirmedShippedQty
+ "), ToDeliver=" + toDeliver + " - " + line);
completeOrder = false;
break;
}
// Complete Line
else if (fullLine && MOrder.DELIVERYRULE_CompleteLine.Equals(order.GetDeliveryRule()))
{
log.Fine("CompleteLine - OnHand=" + onHand
+ " (Unconfirmed=" + unconfirmedShippedQty
+ ", ToDeliver=" + toDeliver + " - " + line);
//
CreateLine(order, line, toDeliver, storages, false);
}
// Availability
else if (MOrder.DELIVERYRULE_Availability.Equals(order.GetDeliveryRule()) &&
(Env.Signum(onHand) > 0 ||
Env.Signum(toDeliver) < 0))
{
Decimal deliver = toDeliver;
if (deliver.CompareTo(onHand) > 0)
{
deliver = onHand;
}
log.Fine("Available - OnHand=" + onHand
+ " (Unconfirmed=" + unconfirmedShippedQty
+ "), ToDeliver=" + toDeliver
+ ", Delivering=" + deliver + " - " + line);
//
CreateLine(order, line, deliver, storages, false);
}
// Force
else if (MOrder.DELIVERYRULE_Force.Equals(order.GetDeliveryRule()))
{
Decimal deliver = toDeliver;
log.Fine("Force - OnHand=" + onHand
+ " (Unconfirmed=" + unconfirmedShippedQty
+ "), ToDeliver=" + toDeliver
+ ", Delivering=" + deliver + " - " + line);
//
CreateLine(order, line, deliver, storages, true);
}
// Manual
else if (MOrder.DELIVERYRULE_Manual.Equals(order.GetDeliveryRule()))
{
log.Fine("Manual - OnHand=" + onHand
+ " (Unconfirmed=" + unconfirmedShippedQty
+ ") - " + line);
}
else
{
log.Fine("Failed: " + order.GetDeliveryRule() + " - OnHand=" + onHand
+ " (Unconfirmed=" + unconfirmedShippedQty
+ "), ToDeliver=" + toDeliver + " - " + line);
}
}
}// for all order lines
// Complete Order successful
if (completeOrder && MOrder.DELIVERYRULE_CompleteOrder.Equals(order.GetDeliveryRule()))
{
for (int i = 0; i < lines.Length; i++)
{
MOrderLine line = lines[i];
// if order line is not drop ship type
if (!line.IsDropShip())
{
if (line.GetM_Warehouse_ID() != _M_Warehouse_ID)
{
continue;
}
MProduct product = line.GetProduct();
Decimal toDeliver = Decimal.Subtract(line.GetQtyOrdered(), line.GetQtyDelivered());
//
MStorage[] storages = null;
if (product != null && product.IsStocked())
{
MProductCategory pc = MProductCategory.Get(order.GetCtx(), product.GetM_Product_Category_ID());
String MMPolicy = pc.GetMMPolicy();
if (MMPolicy == null || MMPolicy.Length == 0)
{
MMPolicy = client.GetMMPolicy();
}
//
storages = GetStorages(line.GetM_Warehouse_ID(),
line.GetM_Product_ID(), line.GetM_AttributeSetInstance_ID(),
product.GetM_AttributeSet_ID(),
line.GetM_AttributeSetInstance_ID() == 0, (DateTime?)minGuaranteeDate,
MClient.MMPOLICY_FiFo.Equals(MMPolicy));
}
//
CreateLine(order, line, toDeliver, storages, false);
}
}
}
_line += 1000;
} // while order
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
log.Log(Level.SEVERE, _sql, e);
}
finally
{
if (idr != null)
{
idr.Close();
}
dt = null;
}
CompleteShipment();
return("@Created@ = " + _created);
}
