Para los casos donde tienda reporta que no puede hacer impresiones tipo mayoreo de ciertos skus, debemos revisar en la pantalla de consulta de articulos en la pestaña de promociones si el sku cuenta con dos promociones una que corresponde a precio de mayoreo y la segunda promocion sea de tipo Articulos a un solo precio
Como se aprecia en la siguiente imagen.
En a consulta de artículos no se muestra el precio que corresponde de la promoción MMP (Mercancía Mismo Precio), ya que no existe como tal un ícono para dicho tipo de promoción; así como tampoco de etiqueta.
El artículo no permite imprimir una etiqueta de precio mayoreo, debido a qué, en la promo Mercancía Mismo Precio el precio que el equipo de admnistracio comercial es de $125 + IVA. ($145 pesos); precio el cual es menor al precio mayoreo (PM) de $148.75 pesos a partir de 3 unidades para el sku de ejemplo 141945 AIRE LIMPIO CAMPOS F . Y en definitiva, no permite imprimir una etiqueta de precio más bajo (NLP), debido a que el artículo no tiene configurada ninguna promoción NLP por parte de AdCom o el merchant.
Por lo que único tipo de etiqueta que permite imprimir sea la etiqueta tipo ‘S’. en caso de alguna duda pueden revisarlo con Erick Perez de Administracion comercial.
Anexo query.
--SP [dbo].[spGetPromotionsBySearchSKU]
DECLARE @SKU BIGINT = 141945 -- Capturar el SKU a consultar
DECLARE @IS_PRO BIT = NULL
BEGIN
SET NOCOUNT ON;
SELECT * FROM ItemPLU WITH (NOLOCK) WHERE SKU = @SKU
DECLARE @TaxRate DECIMAL(4,2)
, @IsLegacyEnabled bit
SELECT @TaxRate = T.Rate / 100
FROM dbo.ItemPLU AS IP (NOLOCK)
INNER JOIN dbo.Tax AS T (NOLOCK) ON T.TaxCode = IP.TaxCode
WHERE IP.SKU = @SKU
IF EXISTS(SELECT 1 FROM dbo.POSDevCntrl AS PDC WHERE PDC.NormalFlow = 0)
SET @IsLegacyEnabled = 0
ELSE
SET @IsLegacyEnabled = 1
SELECT PH.PromoID, IGH.GroupID,IP.DeptNo,PD.TriggerGroupID , IP.SKU, PH.Description,
IGH.GroupType, PH.TypeID, IP.ClassNo, IP.SubClass, PD.TriggerQuantity, PD.TargetQuantity,
ID.Discount, ID.Quantity, PD.TargetGroupID, IP.[Description] AS SkuDesc, ID.DiscountTypeID, ((1+ @TaxRate) * IP.RetailPrice) RetailPrice, PH.CustomerTypeID
INTO #Promotions
FROM PromotionHeader PH WITH (NOLOCK)
INNER JOIN PromotionDetail PD WITH (NOLOCK) on PH.PromoID = PD.PromoID AND PD.TriggerGroupID is not null AND PD.IsActive = 1
INNER JOIN ItemGroupHeader IGH WITH (NOLOCK) on (IGH.[GroupID] = PD.[TriggerGroupID] OR IGH.GroupID = PD.TargetGroupID) AND IGH.IsActive = 1
LEFT OUTER JOIN ItemDetail ID WITH (NOLOCK) on ID.GroupID = IGH.GroupID AND ID.IsActive = 1 AND (IGH.GroupType = '1' OR IGH.GroupType = '101')
LEFT OUTER JOIN [dbo].[DepartmentDetail] DD WITH (NOLOCK) on DD.GroupID = IGH.GroupID AND DD.IsActive = 1 AND (IGH.GroupType = '2' OR IGH.GroupType = '102')
LEFT OUTER JOIN [dbo].[ClassDetail] CD WITH (NOLOCK) on CD.GroupID = IGH.GroupID AND CD.IsActive = 1 AND (IGH.GroupType = '3' OR IGH.GroupType = '103')
LEFT OUTER JOIN [dbo].[SubClassDetail] SCD WITH (NOLOCK) on SCD.GroupID = IGH.GroupID AND SCD.IsActive = 1 AND (IGH.GroupType = '4' OR IGH.GroupType = '104')
INNER JOIN ItemPlu IP WITH (NOLOCK) ON (IP.SKU = ID.SKU OR IP.DeptNo = DD.DepartmentId OR IP.ClassNo = CD.ClassId OR IP.Subclass = SCD.SubclassId)
OR (IGH.GroupType = '5')
WHERE IP.SKU = @SKU AND PH.IsActive = 1 AND GETDATE() BETWEEN PH.StartDate AND PH.EndDate
UNION ALL
SELECT PH.PromoID, IGH.GroupID,IP.DeptNo,PD.TriggerGroupID , IP.SKU, PH.Description,
IGH.GroupType, PH.TypeID, IP.ClassNo, IP.SubClass, PD.TriggerQuantity, PD.TargetQuantity,
ID.Discount, ID.Quantity, PD.TargetGroupID, IP.[Description] AS SkuDesc, ID.DiscountTypeID, ((1+ @TaxRate) * IP.RetailPrice) RetailPrice, PH.CustomerTypeID
FROM PromotionHeader PH WITH (NOLOCK)
INNER JOIN PromotionDetail PD WITH (NOLOCK) on PH.PromoID = PD.PromoID AND PD.TriggerGroupID is not null AND PD.IsActive = 1
INNER JOIN ItemGroupHeader IGH WITH (NOLOCK) on IGH.GroupID = PD.TriggerGroupID AND IGH.IsActive = 1
INNER JOIN ItemDetail ID WITH (NOLOCK) on ID.GroupID = IGH.GroupID AND ID.IsActive = 1 AND (IGH.GroupType = '1' OR IGH.GroupType = '101')
INNER JOIN [dbo].[ItemKitHdr] KH WITH (NOLOCK) ON KH.KitNum = ID.SKU
INNER JOIN [dbo].[ItemKitDtl] KD WITH (NOLOCK) ON KH.KitNum = KD.KitNum
INNER JOIN ItemPlu IP WITH (NOLOCK) ON IP.SKU = KD.SKU
WHERE IP.SKU = @SKU AND PH.IsActive = 1 AND GETDATE() BETWEEN PH.StartDate AND PH.EndDate;
Delete #Promotions where PromoID in (Select PromoID from #Promotions where GroupType in ('101','102','103','104'));
--DELETE #Promotions WHERE AveragePriceWithTax = 'No Aplica'
IF (ISNULL (@IS_PRO, 0) = 0)
DELETE #Promotions WHERE CustomerTypeID = 2 --EXCLUYENDO CLIENTES PRO
IF @IsLegacyEnabled = 1
BEGIN
UPDATE P SET P.RetailPrice = ((1+ @TaxRate) * C.GetAmount)
FROM #Promotions AS P
INNER JOIN dbo.Coupon AS C ON C.GetSKU = P.SKU AND C.CouponType = 7 AND GETDATE() BETWEEN C.StartDate AND C.EndDate
WHERE P.TypeID = 12
END
;WITH MSITC AS
(
SELECT PPD2.PromoID, dbo.GROUP_CONCAT(PPD2.[Plan]) AS MSI , dbo.GROUP_CONCAT(PPD2.Threshold) AS Threshold
FROM [PromotionPlanDetail] PPD2
INNER JOIN #Promotions AS P ON P.PromoID = PPD2.PromoID
WHERE PPD2.IsActive = 1 AND P.TypeID = 1
GROUP BY PPD2.PromoID
)
, MSITH AS
(
SELECT PD.PromoID, dbo.GROUP_CONCAT(KH.KitNum) AS lstSKU
FROM
#Promotions AS P
INNER JOIN PromotionDetail PD WITH (NOLOCK) ON PD.PromoID = P.PromoID
INNER JOIN ItemGroupHeader IGH WITH (NOLOCK) on IGH.GroupID = PD.TriggerGroupID AND IGH.IsActive = 1
INNER JOIN ItemDetail ID WITH (NOLOCK) on ID.GroupID = IGH.GroupID AND ID.IsActive = 1 AND (IGH.GroupType = '1' OR IGH.GroupType = '101')
INNER JOIN [dbo].[ItemKitHdr] KH WITH (NOLOCK) ON KH.KitNum = ID.SKU
WHERE ID.SKU <> @SKU AND P.TypeID = 1
GROUP BY PD.PromoID
)
, PTHDetail AS
(
SELECT ID.Quantity, ID.DiscountTypeID, ID.Discount, ID.GroupID, dbo.GROUP_CONCAT_D(CONVERT(NVARCHAR, ID.SKU)+' '+IP.[Description], '|') AS lstSKUDesc
FROM
#Promotions AS P
INNER JOIN dbo.ItemGroupHeader AS IGH ON IGH.PromoID=P.PromoID
INNER JOIN dbo.ItemDetail AS ID(NOLOCK)ON ID.GroupID=IGH.GroupID
INNER JOIN ItemPLU IP WITH(NOLOCK)ON ID.SKU=IP.SKU
WHERE
ID.IsActive=1 AND P.TypeID IN (20, 3, 2)
GROUP BY ID.Quantity, ID.DiscountTypeID, ID.Discount, ID.GroupID
)
SELECT DISTINCT IGH.PromoID
,T.SKU
,CouponType = 0
,T.DeptNo AS Department
,PH.TypeId AS PromoType
,PH.[Description] AS PromoDesc
,Type_Desc = PT.Description
,PH.StartDate
,PH.EndDate
,T.ClassNo AS Class
,T.SubClass AS SubClass
,Threshold = 0
,ThresholdList = MI.Threshold
,CreditTerm = 0
,CreditTermList = MI.MSI
,buyQty = (CASE WHEN PH.TypeId IN (1,2,3,4,8,16,17,6,7,10,20) THEN CONVERT( NVARCHAR, T.TriggerQuantity ) + '@' + ISNULL(PIDBuy.lstSKUDesc,'')
ELSE CONVERT( NVARCHAR, T.TriggerQuantity )
END)
,getQty = (CASE WHEN PH.TypeId IN (1,2,3,4,8,16,17,6,7,10,20) THEN CONVERT( NVARCHAR, T.TargetQuantity ) + '@' + ISNULL(PIDGet.lstSKUDesc,'')
WHEN PH.TypeId = 12 THEN CONVERT( NVARCHAR, T.Quantity )
ELSE CONVERT( NVARCHAR, T.TargetQuantity )
END)
,MT.lstSKU AS items
, CASE WHEN @IsLegacyEnabled = 1 THEN T.RetailPrice
WHEN T.DiscountTypeID = 2 THEN (CAST(T.RetailPrice - ROUND(((T.Discount / 100) * T.RetailPrice), 2) AS DECIMAL (10, 2)))
WHEN T.DiscountTypeID = 3 THEN T.Discount
WHEN T.DiscountTypeID = 4 THEN T.RetailPrice - T.Discount
END AS GetAmount1
,(CASE WHEN PH.TypeId IN (20) THEN PIDGet.Discount ELSE T.Discount END) AS Discount
,(CASE WHEN PH.TypeId IN (20) THEN PIDGet.DiscountTypeID ELSE T.DiscountTypeID END) AS DiscountTypeID
,PH.CustomerTypeID
FROM [dbo].[ItemGroupHeader] IGH WITH (NOLOCK)
INNER JOIN [dbo].[PromotionHeader] PH WITH (NOLOCK) ON PH.PromoID = IGH.PromoID
INNER JOIN #Promotions T ON T.PromoID = IGH.PromoID
LEFT OUTER JOIN MSITC MI ON MI.PromoID = IGH.PromoID
LEFT OUTER JOIN MSITH MT ON MT.PromoID = IGH.PromoID
LEFT OUTER JOIN PTHDetail PIDBuy ON PIDBuy.groupid = T.TriggerGroupID
LEFT OUTER JOIN PTHDetail PIDGet ON PIDGet.groupid = T.TargetGroupID
LEFT JOIN PromoType PT WITH (NOLOCK) ON PT.PromoType = PH.TypeID
WHERE PH.StartDate <= GETDATE() AND PH.EndDate >= GETDATE()
AND T.SKU = @SKU
AND IGH.IsActive = 1
AND PH.IsActive = 1
Select 'Vigentes' 'ItemDetail',ph.PromoID,ph.Description,ph.TypeID,pt.[Description], ph.StartDate,ph.EndDate,PH.StatusID,CASE PH.StatusID WHEN 0 THEN 'Vacia' WHEN 1 THEN 'Con Planes' WHEN 2 THEN 'Bines Asignados' WHEN 3 THEN 'Tiendas Activadas' WHEN 4 THEN 'Articulos asignados' WHEN 5 THEN 'Tiendas y Articulos asignados' WHEN 6 THEN 'Finalizada' WHEN 7 THEN 'Peticion por autorizacion' WHEN 8 THEN 'Autorizada' WHEN 9 THEN 'Modificada despues de autorizar' WHEN 10 THEN 'Reautorizada' WHEN 11 THEN 'Cancelada' ELSE 'UNKNOWN - No existe tipo de Estatus' END as 'ESTATUS',CASE PH.CustomerTypeID WHEN 0 THEN 'No aplica tienda' WHEN 1 THEN 'PISO' WHEN 2 THEN 'PRO' WHEN 3 THEN 'PISO Y PRO' ELSE '? - No existe tipo - ' + CAST(PH.CustomerTypeID AS CHAR) END AS 'TIPO CTE' ,ph.Created,ph.Modified,id.SKU,Case When pdp.promoid is null then d.[description] else d2.[description] end 'Tipo',Case When id.DiscountTypeID = 2 then id.quantity else pd.TriggerQuantity end 'Compra',Case When id.DiscountTypeID = 2 then '-' else pd.TargetQuantity end 'Regalo',Case When pdp.promoid is null then id.Discount else pdp.Amount end 'Monto',Case When pdp.promoid is null then id.IsActive else pdp.IsActive end 'ID/PDP Active',Ph.IsActive 'PH Active',pd.triggergroupid,Pd.IsActive 'PD Active',IGH.IsActive 'IGH Active' from ItemDetail id (NoLock) LEFT JOIN ItemGroupHeader igh (NoLock) on id.groupid = igh.GroupID LEFT JOIN PromotionHeader ph (Nolock) on ph.promoid = igh.promoid LEFT JOIN PromotionDetail pd (Nolock) on pd.promoid = igh.promoid and pd.TriggerGroupID = igh.GroupID LEFT JOIN PromotionDetailPrice pdp (Nolock) ON pdp.promoid = pd.promoid and pd.sequence = pdp.sequence LEFT JOIN DiscountType d (NoLock) on d.DiscountTypeID = id.DiscountTypeID LEFT JOIN DiscountType d2 (NoLock) on d2.DiscountTypeID = pdp.DiscountTypeID LEFT JOIN PromoType pt (Nolock) on ph.typeid = pt.PromoType where sku = @SKU and CONVERT(DATE, GETDATE()) BETWEEN convert(date,ph.startdate) AND convert(date,ph.enddate )
drop table #Promotions
END
GO
