internal BucketSkuModel(BucketSku entity)
{
Style = entity.Sku.Style;
Color = entity.Sku.Color;
Dimension = entity.Sku.Dimension;
SkuSize = entity.Sku.SkuSize;
UpcCode = entity.Sku.UpcCode;
SkuId = entity.Sku.SkuId;
VwhId = entity.Sku.VwhId;
Volume = entity.Sku.VolumePerDozen / 12;
Weight = entity.Sku.WeightPerDozen / 12;
OrderedPieces = entity.QuantityOrdered;
PiecesCompletePulling = entity.PiecesCompletePulling;
PiecesCompletePitching = entity.PiecesCompletePitching;
PiecesBoxesCreatedPulling = entity.PiecesBoxesCreatedPulling;
PiecesBoxesCreatedPitching = entity.PiecesBoxesCreatedPitching;
BoxesRemainingPulling = entity.BoxesRemainingPulling;
BoxesRemainingPitching = entity.BoxesRemainingPitching;
}
/// <summary>
/// Get the list of SKUs of passed bucket
/// </summary>
/// <param name="bucketId"></param>
/// <param name="stateFilter"> </param>
/// <param name="activityFilter"> </param>
/// <returns></returns>
public IList <BucketSku> GetBucketSkuList(int bucketId)
{
const string QUERY = @"
WITH ALL_ORDERED_SKU AS
(
SELECT PD.SKU_ID AS SKU_ID,
P.VWH_ID AS VWH_ID,
SUM(PD.PIECES_ORDERED) AS QUANTITY_ORDERED
FROM <proxy />PS P
INNER JOIN <proxy />PSDET PD
ON P.PICKSLIP_ID = PD.PICKSLIP_ID
WHERE P.BUCKET_ID = :BUCKET_ID
AND P.TRANSFER_DATE IS NULL
AND PD.TRANSFER_DATE IS NULL
group by PD.SKU_ID, P.VWH_ID
),
ALL_INVENTORY_SKU(SKU_ID,
VWH_ID,
BUILDING_ID,
INVENTORY_AREA,
SHORT_NAME,
PIECES_IN_AREA,
location_id,
DESCRIPTION,
REPLENISH_FROM_AREA_ID
) AS
(SELECT SCD.SKU_ID AS SKU_ID,
SC.VWH_ID AS VWH_ID,
NVL(TIA.WAREHOUSE_LOCATION_ID, MSL.WAREHOUSE_LOCATION_ID) AS BUILDING_ID,
SC.CARTON_STORAGE_AREA AS INVENTORY_AREA,
TIA.SHORT_NAME,
SCD.QUANTITY AS PIECES_IN_AREA,
sc.location_id,
TIA.DESCRIPTION,
NULL
FROM <proxy />SRC_CARTON_DETAIL SCD
INNER JOIN <proxy />SRC_CARTON SC
ON SC.CARTON_ID = SCD.CARTON_ID
LEFT OUTER JOIN <proxy />MASTER_STORAGE_LOCATION MSL
ON SC.LOCATION_ID = MSL.LOCATION_ID
AND SC.CARTON_STORAGE_AREA = MSL.STORAGE_AREA
INNER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON SC.CARTON_STORAGE_AREA = TIA.INVENTORY_STORAGE_AREA
WHERE SC.SUSPENSE_DATE IS NULL
AND SC.QUALITY_CODE = '01'
UNION ALL
SELECT IC.SKU_ID,
IL.VWH_ID,
IL.WAREHOUSE_LOCATION_ID,
IL.IA_ID,
I.SHORT_NAME,
IC.NUMBER_OF_UNITS,
il.location_id,
I.SHORT_DESCRIPTION,
I.DEFAULT_REPREQ_IA_ID
FROM <proxy />IALOC_CONTENT IC
INNER JOIN <proxy />IALOC IL
ON IL.IA_ID = IC.IA_ID
AND IL.LOCATION_ID = IC.LOCATION_ID
INNER JOIN <proxy />IA I
ON I.IA_ID = IL.IA_ID),
PIVOT_ALL_INVENTORY_SKU(SKU_ID,
VWH_ID,
XML_COLUMN) AS
(SELECT *
FROM ALL_INVENTORY_SKU PIVOT XML(
MAX(location_id) KEEP(DENSE_RANK FIRST ORDER BY PIECES_IN_AREA DESC) AS best_location_id,
MAX(PIECES_IN_AREA) AS PIECES_AT_BEST_LOCATION,
SUM(PIECES_IN_AREA) AS PIECES_IN_AREA,
MAX(DESCRIPTION) AS AREA_DESCRIPTION,
MAX(SHORT_NAME) AS AREA_SHORT_NAME,
MAX(REPLENISH_FROM_AREA_ID) AS REPLENISH_FROM_AREA_ID
FOR(INVENTORY_AREA, BUILDING_ID) IN(ANY, ANY))),
BOX_SKU AS
(SELECT BD.SKU_ID AS SKU_ID,
B.VWH_ID AS VWH_ID,
SUM(CASE
WHEN B.CARTON_ID IS NULL AND b.verify_date is not null OR b.STOP_PROCESS_REASON = '$BOXCANCEL' THEN
coalesce(bd.expected_pieces, BD.CURRENT_PIECES)
END) AS PCS_COMPLETE_PITCH,
SUM(CASE
WHEN B.CARTON_ID IS NOT NULL AND b.verify_date is not null OR b.STOP_PROCESS_REASON = '$BOXCANCEL' THEN
coalesce(bd.expected_pieces, BD.CURRENT_PIECES)
END) AS PCS_COMPLETE_PULLING,
SUM(CASE
WHEN b.CARTON_ID IS NULL THEN
BD.EXPECTED_PIECES
END) AS PCS_BOX_CREATED_PITCH,
SUM(CASE
WHEN b.CARTON_ID IS NOT NULL THEN
BD.EXPECTED_PIECES
END) AS PCS_BOX_CREATED_PULL,
COUNT(UNIQUE CASE
WHEN b.CARTON_ID IS NULL AND b.VERIFY_DATE IS NULL OR b.STOP_PROCESS_REASON != '$BOXCANCEL' and b.ia_id is not null THEN
b.UCC128_ID
END) AS BOX_REMAINING_PITCH,
COUNT(UNIQUE CASE
WHEN b.CARTON_ID IS NOT NULL AND b.VERIFY_DATE IS NULL OR b.STOP_PROCESS_REASON != '$BOXCANCEL' and b.ia_id is not null THEN
b.UCC128_ID
END) AS BOX_REMAINING_PULL
FROM <proxy />BOX B
INNER JOIN <proxy />BOXDET BD
ON B.PICKSLIP_ID = BD.PICKSLIP_ID
AND B.UCC128_ID = BD.UCC128_ID
INNER JOIN <proxy />PS P
ON P.PICKSLIP_ID = B.PICKSLIP_ID
WHERE p.bucket_id = :BUCKET_ID
GROUP BY BD.SKU_ID, B.VWH_ID
)
SELECT MS.SKU_ID AS SKU_ID,
MS.STYLE AS STYLE,
MS.COLOR AS COLOR,
MS.DIMENSION AS DIMENSION,
MS.SKU_SIZE AS SKU_SIZE,
ms.UPC_CODE AS UPC_CODE,
AOS.VWH_ID AS VWH_ID,
AOS.QUANTITY_ORDERED AS QUANTITY_ORDERED,
MS.WEIGHT_PER_DOZEN AS WEIGHT_PER_DOZEN,
MS.VOLUME_PER_DOZEN AS VOLUME_PER_DOZEN,
box_sku.PCS_COMPLETE_PITCH as PCS_COMPLETE_PITCH,
box_sku.PCS_COMPLETE_PULLING as PCS_COMPLETE_PULLING,
box_sku.BOX_REMAINING_PITCH as BOX_REMAINING_PITCH,
box_sku.BOX_REMAINING_PULL as BOX_REMAINING_PULL,
box_sku.PCS_BOX_CREATED_PITCH as PCS_BOX_CREATED_PITCH,
box_sku.PCS_BOX_CREATED_PULL as PCS_BOX_CREATED_PULL,
AIS.XML_COLUMN.getstringval() AS XML_COLUMN
FROM ALL_ORDERED_SKU AOS
INNER JOIN <proxy />MASTER_SKU MS
ON MS.SKU_ID = AOS.SKU_ID
LEFT OUTER JOIN PIVOT_ALL_INVENTORY_SKU AIS
ON AIS.SKU_ID = aos.SKU_ID
AND AIS.VWH_ID = AOS.VWH_ID
LEFT OUTER JOIN BOX_SKU BOX_SKU
ON BOX_SKU.SKU_ID = AOS.SKU_ID
AND BOX_SKU.VWH_ID = AOS.VWH_ID";
var binder = SqlBinder.Create(row =>
{
var bs = new BucketSku
{
Sku = new Sku
{
SkuId = row.GetInteger("SKU_ID").Value,
Style = row.GetString("STYLE"),
Color = row.GetString("COLOR"),
Dimension = row.GetString("DIMENSION"),
SkuSize = row.GetString("SKU_SIZE"),
UpcCode = row.GetString("UPC_CODE"),
VwhId = row.GetString("VWH_ID"),
WeightPerDozen = row.GetDecimal("WEIGHT_PER_DOZEN"),
VolumePerDozen = row.GetDecimal("VOLUME_PER_DOZEN")
},
QuantityOrdered = row.GetInteger("QUANTITY_ORDERED"),
BucketSkuInAreas = MapOrderedSkuXml(row.GetString("XML_COLUMN")),
PiecesCompletePitching = row.GetInteger("PCS_COMPLETE_PITCH"),
PiecesCompletePulling = row.GetInteger("PCS_COMPLETE_PULLING"),
BoxesRemainingPitching = row.GetInteger("BOX_REMAINING_PITCH"),
BoxesRemainingPulling = row.GetInteger("BOX_REMAINING_PULL"),
PiecesBoxesCreatedPitching = row.GetInteger("PCS_BOX_CREATED_PITCH"),
PiecesBoxesCreatedPulling = row.GetInteger("PCS_BOX_CREATED_PULL")
};
return(bs);
});
binder.Parameter("BUCKET_ID", bucketId);
return(_db.ExecuteReader(QUERY, binder, 2000));
}
/// <summary>
/// Get the list of SKUs of passed bucket
/// </summary>
/// <param name="bucketId"></param>
/// <param name="stateFilter"> </param>
/// <param name="activityFilter"> </param>
/// <returns></returns>
public IEnumerable <BucketSku> GetBucketSkuList(int bucketId)
{
const string QUERY = @"
WITH ALL_ORDERED_SKU AS
(
SELECT PD.SKU_ID AS SKU_ID,
P.VWH_ID AS VWH_ID,
MAX(B.PITCH_IA_ID) AS PITCH_AREA,
SUM(PD.PIECES_ORDERED) AS QUANTITY_ORDERED
FROM <proxy />PS P
INNER JOIN <proxy />PSDET PD
ON P.PICKSLIP_ID = PD.PICKSLIP_ID
INNER JOIN <proxy />BUCKET B
ON B.BUCKET_ID = P.BUCKET_ID
WHERE P.BUCKET_ID = :BUCKET_ID
AND P.TRANSFER_DATE IS NULL
AND PD.TRANSFER_DATE IS NULL
group by PD.SKU_ID, P.VWH_ID
),
ALL_INVENTORY_SKU(SKU_ID,
VWH_ID,
BUILDING_ID,
INVENTORY_AREA,
SHORT_NAME,
PIECES_IN_AREA,
DESCRIPTION,
REPLENISH_FROM_AREA_ID
) AS
(SELECT SCD.SKU_ID AS SKU_ID,
SC.VWH_ID AS VWH_ID,
NVL(TIA.WAREHOUSE_LOCATION_ID, MSL.WAREHOUSE_LOCATION_ID) AS BUILDING_ID,
SC.CARTON_STORAGE_AREA AS INVENTORY_AREA,
TIA.SHORT_NAME,
SCD.QUANTITY AS PIECES_IN_AREA,
TIA.DESCRIPTION,
NULL
FROM <proxy />SRC_CARTON_DETAIL SCD
INNER JOIN <proxy />SRC_CARTON SC
ON SC.CARTON_ID = SCD.CARTON_ID
LEFT OUTER JOIN <proxy />MASTER_STORAGE_LOCATION MSL
ON SC.LOCATION_ID = MSL.LOCATION_ID
AND SC.CARTON_STORAGE_AREA = MSL.STORAGE_AREA
INNER JOIN <proxy />TAB_INVENTORY_AREA TIA
ON SC.CARTON_STORAGE_AREA = TIA.INVENTORY_STORAGE_AREA
WHERE SC.SUSPENSE_DATE IS NULL
AND SC.QUALITY_CODE = '01'
and scd.sku_id in (select sku_id from ALL_ORDERED_SKU)
UNION ALL
SELECT IC.SKU_ID,
IL.VWH_ID,
IL.WAREHOUSE_LOCATION_ID,
IL.IA_ID,
I.SHORT_NAME,
IC.NUMBER_OF_UNITS,
I.SHORT_DESCRIPTION,
I.DEFAULT_REPREQ_IA_ID
FROM <proxy />IALOC_CONTENT IC
INNER JOIN <proxy />IALOC IL
ON IL.IA_ID = IC.IA_ID
AND IL.LOCATION_ID = IC.LOCATION_ID
INNER JOIN <proxy />IA I
ON I.IA_ID = IL.IA_ID
where ic.sku_id in (select sku_id from ALL_ORDERED_SKU)
),
PIVOT_ALL_INVENTORY_SKU(SKU_ID,
VWH_ID,
XML_COLUMN) AS
(SELECT *
FROM ALL_INVENTORY_SKU PIVOT XML(SUM(PIECES_IN_AREA) AS PIECES_IN_AREA, MIN(PIECES_IN_AREA) AS PIECES_IN_SMALLEST_CARTON,
MAX(DESCRIPTION) AS AREA_DESCRIPTION, MAX(SHORT_NAME) AS AREA_SHORT_NAME, MAX(REPLENISH_FROM_AREA_ID) AS REPLENISH_FROM_AREA_ID
FOR(INVENTORY_AREA, BUILDING_ID) IN(ANY, ANY))),
BOX_SKU AS
(SELECT BD.SKU_ID AS SKU_ID,
B.VWH_ID AS VWH_ID,
SUM(CASE
WHEN B.CARTON_ID IS NULL AND b.verify_date is null THEN
BD.CURRENT_PIECES
END) AS UNVRFY_CUR_PCS_PITCH,
SUM(CASE
WHEN B.CARTON_ID IS NULL AND b.verify_date is not null THEN
BD.CURRENT_PIECES
END) AS VRFY_CUR_PCS_PITCH,
SUM(CASE
WHEN B.CARTON_ID IS NULL AND b.verify_date is null THEN
NVL(BD.EXPECTED_PIECES, BD.CURRENT_PIECES)
END) AS UNVRFY_EXP_PCS_PITCH,
SUM(CASE
WHEN B.CARTON_ID IS NOT NULL AND b.verify_date is not null THEN
BD.CURRENT_PIECES
END) AS VRFY_CUR_PCS_PULL,
SUM(CASE
WHEN B.CARTON_ID IS NOT NULL AND b.verify_date is null THEN
NVL(BD.EXPECTED_PIECES, BD.CURRENT_PIECES)
END) AS UNVRFY_EXP_PCS_PULL,
SUM(CASE
WHEN B.CARTON_ID IS NOT NULL AND b.verify_date is null THEN
BD.CURRENT_PIECES
END) AS UNVRFY_CUR_PCS_PULL,
MAX(CASE
WHEN B.CARTON_ID IS NULL THEN
B.PITCHING_END_DATE
END
) AS MAX_PITCHING_END_DATE,
MIN(CASE
WHEN B.CARTON_ID IS NULL THEN
B.PITCHING_END_DATE
END
) AS MIN_PITCHING_END_DATE,
MAX(CASE
WHEN B.CARTON_ID IS NOT NULL THEN
B.PITCHING_END_DATE
END
) AS MAX_PULL_END_DATE,
MIN(CASE
WHEN B.CARTON_ID IS NOT NULL THEN
B.PITCHING_END_DATE
END
) AS MIN_PULL_END_DATE
FROM <proxy />BOX B
INNER JOIN <proxy />BOXDET BD
ON B.PICKSLIP_ID = BD.PICKSLIP_ID
AND B.UCC128_ID = BD.UCC128_ID
INNER JOIN <proxy />PS P
ON P.PICKSLIP_ID = B.PICKSLIP_ID
WHERE p.bucket_id = :BUCKET_ID
and b.stop_process_date is null
and bd.stop_process_date is null
<if c='$Pitching'>AND B.CARTON_ID IS NULL</if>
<if c='$Pulling'>AND B.CARTON_ID IS NOT NULL</if>
GROUP BY BD.SKU_ID, B.VWH_ID
)
SELECT MS.SKU_ID AS SKU_ID,
MS.STYLE AS STYLE,
MS.COLOR AS COLOR,
MS.DIMENSION AS DIMENSION,
MS.SKU_SIZE AS SKU_SIZE,
ms.UPC_CODE AS UPC_CODE,
AOS.VWH_ID AS VWH_ID,
AOS.QUANTITY_ORDERED AS QUANTITY_ORDERED,
BOX_SKU.UNVRFY_CUR_PCS_PITCH AS UNVRFY_CUR_PCS_PITCH,
BOX_SKU.VRFY_CUR_PCS_PITCH AS VRFY_CUR_PCS_PITCH,
BOX_SKU.UNVRFY_EXP_PCS_PITCH AS UNVRFY_EXP_PCS_PITCH,
BOX_SKU.VRFY_CUR_PCS_PULL AS VRFY_CUR_PCS_PULL,
BOX_SKU.UNVRFY_EXP_PCS_PULL AS UNVRFY_EXP_PCS_PULL,
BOX_SKU.UNVRFY_CUR_PCS_PULL AS UNVRFY_CUR_PCS_PULL,
BOX_SKU.MAX_PITCHING_END_DATE AS MAX_PITCHING_END_DATE,
BOX_SKU.MIN_PITCHING_END_DATE AS MIN_PITCHING_END_DATE,
BOX_SKU.MAX_PULL_END_DATE AS MAX_PULL_END_DATE,
BOX_SKU.MIN_PULL_END_DATE AS MIN_PULL_END_DATE,
MS.WEIGHT_PER_DOZEN AS WEIGHT_PER_DOZEN,
MS.VOLUME_PER_DOZEN AS VOLUME_PER_DOZEN,
(SELECT COUNT(UNIQUE ASSIGNED_UPC_CODE)
FROM IALOC IL
WHERE IL.ASSIGNED_UPC_CODE = ms.UPC_CODE
AND IL.VWH_ID = AOS.VWH_ID
AND IL.IA_ID = AOS.PITCH_AREA) AS COUNT_ASSIGED_SKU,
AOS.PITCH_AREA AS PITCH_AREA,
AIS.XML_COLUMN.getstringval() AS XML_COLUMN
FROM ALL_ORDERED_SKU AOS
INNER JOIN <proxy />MASTER_SKU MS
ON MS.SKU_ID = AOS.SKU_ID
LEFT OUTER JOIN PIVOT_ALL_INVENTORY_SKU AIS
ON AIS.SKU_ID = aos.SKU_ID
AND AIS.VWH_ID = AOS.VWH_ID
LEFT OUTER JOIN BOX_SKU BOX_SKU
ON BOX_SKU.SKU_ID = AOS.SKU_ID
AND BOX_SKU.VWH_ID = AOS.VWH_ID
WHERE 1 = 1
<if c='$Completed'>AND (BOX_SKU.VRFY_CUR_PCS_PITCH > 0 OR BOX_SKU.VRFY_CUR_PCS_PULL > 0 OR BOX_SKU.UNVRFY_CUR_PCS_PULL > 0 OR BOX_SKU.UNVRFY_CUR_PCS_PITCH > 0)</if>
<if c='$InProgress'>AND (BOX_SKU.UNVRFY_EXP_PCS_PULL > NVL(BOX_SKU.UNVRFY_CUR_PCS_PULL,0) OR BOX_SKU.UNVRFY_EXP_PCS_PITCH > NVL(UNVRFY_CUR_PCS_PITCH,0))</if>
";
var binder = SqlBinder.Create(row =>
{
var bs = new BucketSku
{
Sku = new Sku
{
SkuId = row.GetInteger("SKU_ID").Value,
Style = row.GetString("STYLE"),
Color = row.GetString("COLOR"),
Dimension = row.GetString("DIMENSION"),
SkuSize = row.GetString("SKU_SIZE"),
UpcCode = row.GetString("UPC_CODE"),
VwhId = row.GetString("VWH_ID"),
WeightPerDozen = row.GetDecimal("WEIGHT_PER_DOZEN") ?? 0,
VolumePerDozen = row.GetDecimal("VOLUME_PER_DOZEN") ?? 0,
IsAssignedSku = row.GetInteger("COUNT_ASSIGED_SKU") > 0
},
QuantityOrdered = row.GetInteger("QUANTITY_ORDERED") ?? 0,
IsPitchingBucket = !string.IsNullOrWhiteSpace(row.GetString("PITCH_AREA")),
BucketSkuInAreas = MapOrderedSkuXml(row.GetString("XML_COLUMN"))
};
bs.Activities[BucketActivityType.Pitching].MaxEndDate = row.GetDateTimeOffset("MAX_PITCHING_END_DATE");
bs.Activities[BucketActivityType.Pitching].MinEndDate = row.GetDateTimeOffset("MIN_PITCHING_END_DATE");
bs.Activities[BucketActivityType.Pitching].Stats[BoxState.InProgress, PiecesKind.Current] = row.GetInteger("UNVRFY_CUR_PCS_PITCH");
bs.Activities[BucketActivityType.Pitching].Stats[BoxState.Completed, PiecesKind.Current] = row.GetInteger("VRFY_CUR_PCS_PITCH");
bs.Activities[BucketActivityType.Pitching].Stats[BoxState.InProgress, PiecesKind.Expected] = row.GetInteger("UNVRFY_EXP_PCS_PITCH");
bs.Activities[BucketActivityType.Pulling].MaxEndDate = row.GetDateTimeOffset("MAX_PULL_END_DATE");
bs.Activities[BucketActivityType.Pulling].MinEndDate = row.GetDateTimeOffset("MIN_PULL_END_DATE");
bs.Activities[BucketActivityType.Pulling].Stats[BoxState.InProgress, PiecesKind.Current] = row.GetInteger("UNVRFY_CUR_PCS_PULL");
bs.Activities[BucketActivityType.Pulling].Stats[BoxState.Completed, PiecesKind.Current] = row.GetInteger("VRFY_CUR_PCS_PULL");
bs.Activities[BucketActivityType.Pulling].Stats[BoxState.InProgress, PiecesKind.Expected] = row.GetInteger("UNVRFY_EXP_PCS_PULL");
return(bs);
});
binder.Parameter("BUCKET_ID", bucketId);
//if (stateFilter == BoxState.NotSet)
//{
binder.ParameterXPath("All", true);
//}
//if (stateFilter.HasFlag(BoxState.Completed))
//{
// binder.ParameterXPath("Completed", true);
//}
//if (stateFilter.HasFlag(BoxState.InProgress))
//{
// binder.ParameterXPath("InProgress", true);
//}
//switch (activityFilter)
//{
// case BucketActivityType.NotSet:
// break;
// case BucketActivityType.Pitching:
// binder.ParameterXPath("Pitching", true);
// break;
// case BucketActivityType.Pulling:
// binder.ParameterXPath("Pulling", true);
// break;
// default:
// throw new NotImplementedException();
//}
binder.TolerateMissingParams = true;
return(_db.ExecuteReader(QUERY, binder, 2000));
}
