/// <summary>
/// This method updates the carton storage and pallet id of a carton
/// </summary>
/// <param name="carton"></param>
/// <param name="moveFlags"></param>
public void MoveCarton(Carton carton, CartonUpdateFlags moveFlags)
{
const string QUERY = @"
BEGIN
<if c='$carton_area'>
UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.CARTON_STORAGE_AREA = :carton_area ,SC.location_id = :location_id WHERE SC.CARTON_ID = :carton_id;
</if>
<if c='$pallet_id'>
UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.PALLET_ID = :pallet_id WHERE SC.CARTON_ID = :carton_id;
</if>
<if c='$removePallet'>
UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.PALLET_ID = NULL WHERE SC.CARTON_ID = :carton_id;
</if>
<if c='$updateLocation'>
UPDATE <proxy />SRC_CARTON SC SET SC.SUSPENSE_DATE = NULL, SC.location_id = :location_id WHERE SC.CARTON_ID = :carton_id;
</if>
END;
";
var binder = SqlBinder.Create().Parameter("carton_id", carton.CartonId)
.Parameter("carton_area", moveFlags.HasFlag(CartonUpdateFlags.Area) ? carton.CartonArea.AreaId : null)
.Parameter("pallet_id", moveFlags.HasFlag(CartonUpdateFlags.Pallet) ? carton.PalletId : null)
.Parameter("location_id", moveFlags.HasFlag(CartonUpdateFlags.Location) ? carton.LocationId : null);
binder.ParameterXPath("removePallet", moveFlags.HasFlag(CartonUpdateFlags.RemovePallet));
binder.ParameterXPath("updateLocation", moveFlags.HasFlag(CartonUpdateFlags.Location));
_db.ExecuteNonQuery(QUERY, binder);
}
/// <summary>
/// Updates carton properties only if the modified values are different from current values
/// </summary>
/// <param name="currentCarton">.</param>
/// <param name="modifiedCarton"></param>
/// <param name="updateFlags"></param>
/// <param name="reasonCode"></param>
/// <returns></returns>
/// <remarks>
/// Used for optimization. If the modified value is same as current value, the update query can be avoided
/// </remarks>
public bool UpdateMoveCarton(Carton currentCarton, Carton modifiedCarton, CartonUpdateFlags updateFlags, string reasonCode)
{
var modifications = this.GetModifications(currentCarton, modifiedCarton, updateFlags);
if (modifications == CartonUpdateFlags.None)
{
// No property of the carton needs to be modified. Bypass checking of qualification rules.
return(false);
}
var updated = false;
if ((modifications & CartonUpdateFlags.UpdateTasks) != CartonUpdateFlags.None)
{
modifiedCarton.CartonId = currentCarton.CartonId;
_repos.UpdateCarton(modifiedCarton, modifications, reasonCode);
updated = true;
}
if ((modifications & CartonUpdateFlags.MoveTasks) != CartonUpdateFlags.None)
{
modifiedCarton.CartonId = currentCarton.CartonId;
_repos.MoveCarton(modifiedCarton, modifications);
updated = true;
}
return(updated);
}
/// <summary>
/// Pass in what you want update. Get back what actually needs updating. The returned set of flags will alwyays be
/// a subset of the passed flags.
/// </summary>
/// <param name="currentCarton"></param>
/// <param name="modifiedCarton"></param>
/// <param name="updateFlags"></param>
/// <returns></returns>
/// <remarks>
/// modifiedCarton.CartonId is not looked at. It is assumed to be the same as currentCarton.CartonId
/// </remarks>
private CartonUpdateFlags GetModifications(Carton currentCarton, Carton modifiedCarton, CartonUpdateFlags updateFlags)
{
if (modifiedCarton == null)
{
throw new ArgumentNullException("modifiedCarton");
}
if (currentCarton == null || string.IsNullOrWhiteSpace(currentCarton.CartonId))
{
throw new ArgumentNullException("currentCarton", "Current carton or its id cannot be null");
}
if (!string.IsNullOrEmpty(modifiedCarton.CartonArea.AreaId) && GetCartonArea(modifiedCarton.CartonArea.AreaId).IsNumberedLocationArea&&
string.IsNullOrWhiteSpace(modifiedCarton.LocationId))
{
throw new ProviderException("Location is required for numbered areas. ");
}
if (updateFlags.HasFlag(CartonUpdateFlags.MarkReworkComplete) && updateFlags.HasFlag(CartonUpdateFlags.AbandonRework))
{
throw new ProviderException("Mark rework complete and abandon rework can not be performed on same carton. ");
}
var returnFlags = CartonUpdateFlags.None;
if (updateFlags.HasFlag(CartonUpdateFlags.Sku) && currentCarton.SkuInCarton.SkuId != modifiedCarton.SkuInCarton.SkuId)
{
returnFlags |= CartonUpdateFlags.Sku;
}
if (updateFlags.HasFlag(CartonUpdateFlags.Quality) && currentCarton.QualityCode != modifiedCarton.QualityCode)
{
returnFlags |= CartonUpdateFlags.Quality;
}
if (updateFlags.HasFlag(CartonUpdateFlags.Pieces) && currentCarton.Pieces != modifiedCarton.Pieces)
{
returnFlags |= CartonUpdateFlags.Pieces;
}
if (updateFlags.HasFlag(CartonUpdateFlags.Vwh) && currentCarton.VwhId != modifiedCarton.VwhId)
{
returnFlags |= CartonUpdateFlags.Vwh;
}
if (updateFlags.HasFlag(CartonUpdateFlags.Pallet) && currentCarton.PalletId != modifiedCarton.PalletId)
{
returnFlags |= CartonUpdateFlags.Pallet;
}
if (updateFlags.HasFlag(CartonUpdateFlags.Area) && currentCarton.CartonArea.AreaId != modifiedCarton.CartonArea.AreaId)
{
returnFlags |= CartonUpdateFlags.Area;
}
if (updateFlags.HasFlag(CartonUpdateFlags.Location) && currentCarton.LocationId != modifiedCarton.LocationId)
{
returnFlags |= CartonUpdateFlags.Location;
}
if (updateFlags.HasFlag(CartonUpdateFlags.PriceSeasonCode) && currentCarton.PriceSeasonCode != modifiedCarton.PriceSeasonCode)
{
returnFlags |= CartonUpdateFlags.PriceSeasonCode;
}
if (updateFlags.HasFlag(CartonUpdateFlags.MarkReworkComplete) && currentCarton.RemarkWorkNeeded == true)
{
returnFlags |= CartonUpdateFlags.MarkReworkComplete;
}
if (updateFlags.HasFlag(CartonUpdateFlags.AbandonRework) && currentCarton.RemarkWorkNeeded == true)
{
returnFlags |= CartonUpdateFlags.AbandonRework;
}
if (updateFlags.HasFlag(CartonUpdateFlags.RemovePallet) && !string.IsNullOrEmpty(currentCarton.PalletId))
{
returnFlags |= CartonUpdateFlags.RemovePallet;
}
return(returnFlags);
}
/// <summary>
/// Updates quality,SKU,pieces of the carton.
/// </summary>
/// <param name="carton"></param>
/// <param name="updateFlags"></param>
/// <param name="reasonCode"></param>
public void UpdateCarton(Carton carton, CartonUpdateFlags updateFlags, string reasonCode)
{
const string QUERY = @"
DECLARE
Lsku_rec <proxy />pkg_inv_3.SKU_REC;
LRelated_TRansaction_Id NUMBER(10);
BEGIN
Lsku_rec.sku_id := :trgSKU;
Lsku_rec.vwh_id := :trgVwh_id;
Lsku_rec.quality_code := :trgQuality;
LRelated_TRansaction_Id := <proxy />pkg_inv_3.editcarton(acarton_id => :cartonId,
atarget_sku => Lsku_rec,
anew_pieces => :trgPieces,
arelated_transaction_id => NULL,
areason_code => :reasonCode );
<if c='$priceseasoncode'>
UPDATE <proxy />SRC_CARTON
SET PRICE_SEASON_CODE = :priceseasoncode
WHERE CARTON_ID = :cartonId;
</if>
<if c='$completeRework'>
begin
LRelated_TRansaction_Id := <proxy />pkg_carton_work_2.mark_work_complete(acarton_id => :cartonId,
arelated_transaction_id => NULL);
end;
</if>
<if c='$abandonRework'>
begin
LRelated_TRansaction_Id := <proxy />pkg_carton_work_2.undo_work(acarton_id => :cartonId,
arelated_transaction_id => NULL);
end;
</if>
UPDATE <proxy />SRC_CARTON_DETAIL SCD
SET SCD.REQ_PROCESS_ID = NULL,
SCD.REQ_MODULE_CODE= NULL,
SCD.REQ_LINE_NUMBER =NULL
WHERE SCD.CARTON_ID =:cartonId;
UPDATE <proxy />SRC_CARTON SC
SET SUSPENSE_DATE = NULL
WHERE SC.CARTON_ID = :cartonId;
END; ";
if (updateFlags.HasFlag(CartonUpdateFlags.MarkReworkComplete) && updateFlags.HasFlag(CartonUpdateFlags.AbandonRework))
{
throw new ProviderException("Mark rework complete and abandon rework can not be performed on same carton. ");
}
var binder = SqlBinder.Create().Parameter("cartonId", carton.CartonId)
.Parameter("trgVwh_id", updateFlags.HasFlag(CartonUpdateFlags.Vwh) ? carton.VwhId : null)
.Parameter("trgSKU", updateFlags.HasFlag(CartonUpdateFlags.Sku) ? (int?)carton.SkuInCarton.SkuId : null)
.Parameter("trgPieces", updateFlags.HasFlag(CartonUpdateFlags.Pieces) ? (int?)carton.Pieces : null)
.Parameter("reasonCode", reasonCode)
.Parameter("trgQuality", updateFlags.HasFlag(CartonUpdateFlags.Quality) ? carton.QualityCode : null)
.Parameter("completeRework", updateFlags.HasFlag(CartonUpdateFlags.MarkReworkComplete) ? carton.CartonId : null)
.Parameter("abandonRework", updateFlags.HasFlag(CartonUpdateFlags.AbandonRework) ? carton.CartonId : null)
.Parameter("priceseasoncode", updateFlags.HasFlag(CartonUpdateFlags.PriceSeasonCode) ? carton.PriceSeasonCode : null);
_db.ExecuteNonQuery(QUERY, binder);
}
