Cuando reporten una PO de orden especial creada en tienda y que no se visualice de lado del sistema de mercaderias (MMS) o en el portal de proveedores. Como en siguiente ejemplo de la tienda 8762 donde al consultar la PO esta no se muestra en el sistema MMS.
Lo que tendriamos que haces es lo siguiente.
- Revisar que el mensaje exista en la tabla OutboundTransactionsHistory y hacer el reenvio
/* Reenvio XML de Pedido */
declare @PO varchar(10) = 8645903413
declare @fecha date
declare @ID uniqueidentifier
Select @fecha = CreateDate from poheader NOLOCK WHERE ponumber = @PO
SELECT @ID = ID FROM OutboundTransactionsHistory NOLOCK WHERE
DateCreated >= @fecha and PayLoad like '%' + @PO + '%'
AND WebServiceTypeCode LIKE '003'
--select @ID
INSERT INTO OutBoundIntegrationTransactions SELECT WebServiceSettingCode, WebServiceTypeCode, TypeCode, PayLoad, RunSequence,
NULL, NULL, NULL, NULL, 0, 1, getdate() FROM OutBoundTransactionsHistory
WHERE ID = @ID- Si el mensje no existe en la tabla OutboundTransactionsHistory debemos revisar si existe en la tabla OutBoundIntegrationTransactions si es asi actualizamos la bandera ProcessingCount para que se consuma el mensaje.
select * from OutBoundIntegrationTransactions where Payload like '%8762906593%'
--update OutBoundIntegrationTransactions SET ProcessingCount = 1 where Payload like '%8762906593%'- Si despues de actualizar la badera el mensaje no se ha consumido reinicar el servicio del datapuhs que se encunetra en el servidor ISPCW de la tienda.
Si el mensaje no lo encontramos en ninguna de las tablas anteriores lo que sigue es es ejecutar el siguiente SP solo como dato de entrada poner el numero de PO, con ello se genera el mensaje de la IF03 que corresponde al mensaje d ela creacion del pedido que viaja a MMS y de esta manera ya se debe visualizar la PO de lado de MMS.
-- =============================================
-- Author: MDT
-- Create date: 08/27/2014
-- Modified: 03/31/2015
-- Description: 003 Create an XML when the following actions happen in PODetail: INSERT, UPDATE, DELETE.
-- =============================================
--CREATE PROCEDURE [dbo].[spPurchasedOrderDet]
declare @UserId NVARCHAR(32) = '9999'
declare @WorkstationId NVARCHAR(32) = 'EOD-upldPO'
declare @PurchaseOrderId NVARCHAR(11) = 8739906128
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @storeNumber SMALLINT = (SELECT StoreNumber FROM Store WHERE StoreType = 1)
DECLARE @procDate DATETIME = GETDATE();
DECLARE @seqNo BIGINT;
DECLARE @count INTEGER = 1;
DECLARE @total INTEGER;
DECLARE @xml NVARCHAR(MAX);
DECLARE @ifNumber NVARCHAR(8) = 'IF003';
DECLARE @progName VARCHAR(256) = OBJECT_NAME(@@PROCID)
DECLARE @employeeID INT = 0
DECLARE @errorMessage VARCHAR(MAX)
DECLARE @segMessage VARCHAR(MAX)
DECLARE @timeElapsed DATETIME = GETDATE()
DECLARE @SEGOrderDls AS BIT = 0
DECLARE @rowId INT = 1
DECLARE @PoNumber NVARCHAR(20)
DECLARE @OrderNumber INT
DECLARE @SKU BIGINT
DECLARE @ExchangeRate decimal(11,6)
DECLARE @OD_OrderQty DECIMAL(9, 3)
DECLARE @OD_UnitCost DECIMAL(10, 4)
DECLARE @PO_OrderQty DECIMAL(8, 3)
DECLARE @PO_UnitCost DECIMAL(11, 4)
DECLARE @CurrencyCode NVARCHAR(2)
DECLARE @OrderSeg AS TABLE (
Id INT,
PoNumber NVARCHAR(20),
OrderNumber INT,
SKU BIGINT,
ExchangeRate decimal(11,6),
OD_OrderQty DECIMAL(9, 3),
OD_UnitCost DECIMAL(10, 4),
PO_OrderQty DECIMAL(8, 3),
PO_UnitCost DECIMAL(11, 4)
);
select @SEGOrderDls = 1 from [dbo].[SysConfig] with (NoLock) where Id = 'PO_ORDER_DLS' AND Qty01 =1
IF @SEGOrderDls IS NULL
SET @SEGOrderDls = 0
BEGIN TRY
SELECT distinct @CurrencyCode = v.[CurrencyCode]
FROM [dbo].[PODetail] det
INNER JOIN [dbo].[POHeader] p ON p.PONumber = det.PONumber
INNER JOIN [dbo].[Vendor] v ON v.[VendorID]=p.[VendorID] AND v.[VendorSuffix]=p.[VendorSuffix]
WHERE det.[PONumber] = @PurchaseOrderId
IF @SEGOrderDls = 1 and @CurrencyCode = 'US'
BEGIN
INSERT INTO @OrderSeg
SELECT ROW_NUMBER() OVER(PARTITION BY det.PONumber ORDER BY det.PONumber ASC) ,
det.PONumber,
P.OrderNumber,
det.SKU,
det.[ExchangeRate],
det.OrderQty,
det.UnitCost,
OD.QuantityOrd,
OD.UnitCost
FROM [dbo].[PODetail] det
INNER JOIN [dbo].[POHeader] p ON p.PONumber = det.PONumber
INNER JOIN [dbo].[OrderDetail] OD ON OD.OrderNumber = P.OrderNumber AND det.SKU = OD.SKU
INNER JOIN [dbo].[ItemPLU] i ON det.[SKU]=i.[SKU]
INNER JOIN [dbo].[Vendor] v ON v.[VendorID]=p.[VendorID] AND v.[VendorSuffix]=p.[VendorSuffix]
WHERE det.[PONumber] = @PurchaseOrderId
WHILE (SELECT Count(*) FROM @OrderSeg WHERE ID = @rowId) > 0
BEGIN
Select @PoNumber = PoNumber ,
@OrderNumber = OrderNumber ,
@SKU = SKU ,
@ExchangeRate = ExchangeRate ,
@OD_OrderQty = OD_OrderQty ,
@OD_UnitCost = OD_UnitCost ,
@PO_OrderQty = PO_OrderQty ,
@PO_UnitCost = PO_UnitCost
FROM @OrderSeg WHERE ID = @rowId
SELECT @errorMessage = 'Programa: ' + @progName --+ CHAR(10)
+ CONCAT(' PoNumber:', @PoNumber,' OrderId:',@OrderNumber) --+ CHAR(10)
+ '. Tempo:' + convert(varchar, getdate() - @timeElapsed, 8) + CHAR(10)
+ '. Mensaje: ' + CONCAT('SKU:', @SKU,' OD_OrderQty:',@OD_OrderQty,' OD_UnitCost:',@OD_UnitCost,' OD_UnitCost:',@OD_UnitCost, ' PO_OrderQty:',@PO_OrderQty, ' PO_UnitCost:',@PO_UnitCost) --+ CHAR(10)
EXEC WriteSystemLog 4
, @progName
, @employeeID
, @errorMessage;
SET @rowId = @rowId + 1
END
END
exec [dbo].[TakeANumber_out] @ifNumber, @seqNo OUTPUT;
;WITH XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT @xml = (CONVERT(VARCHAR(MAX), (SELECT
'http://www.nrf-arts.org/IXRetail/namespace/ POSLog.xsd' AS "@xsi:schemaLocation",
(SELECT
CASE
WHEN hdr.POStatus <> 'B' AND hdr.POStatus <> 'C' THEN 'false'
WHEN hdr.POStatus = 'B' OR hdr.POStatus = 'C' THEN 'true'
END AS "@CancelFlag",
(SELECT TOP 1 IIF(TranTotal.OperatMode = 1, 'true', 'false') FROM TranTotal WHERE TranTotal.DocNumber = hdr.OrderNUmber) AS "@TrainingModeFlag",
@storeNumber AS "RetailStoreID",
@workstationId AS "WorkstationID",
hdr.PONumber AS "TillID",
@seqNo AS "SequenceNumber",
FORMAT(@procDate,'yyyy-MM-ddTHH:mm:ss','en-US') AS "BeginDateTime",
FORMAT(@procDate,'yyyy-MM-ddTHH:mm:ss','en-US') AS "EndDateTime",
FORMAT(@procDate,'yyyy-MM-dd','en-US') AS "BusinessDayDate",
'9999' AS "OperatorID",
hdr.PONumber AS "InventoryControlTransaction/PurchaseOrder/DocumentID",
hdr.PONumber AS "InventoryControlTransaction/PurchaseOrder/TransactionReference",
@storeNumber AS "InventoryControlTransaction/PurchaseOrder/RetailStoreID",
hdr.VendorID AS "InventoryControlTransaction/PurchaseOrder/FromParty",
hdr.VendorSuffix AS "InventoryControlTransaction/PurchaseOrder/FromSubParty",
CONVERT(NVARCHAR(10), hdr.CreateDate, 120) AS "InventoryControlTransaction/PurchaseOrder/CreateDate",
CONVERT(NVARCHAR(10), hdr.ShpToArvDate, 120) AS "InventoryControlTransaction/PurchaseOrder/ExpectedDeliveryDate",
CONVERT(NVARCHAR(10), hdr.ShpToArvDate, 120) AS "InventoryControlTransaction/PurchaseOrder/ExpectedShipDate",
hdr.RevisionNo AS "InventoryControlTransaction/PurchaseOrder/RevisionNumber",
(
SELECT CASE
WHEN hdr.POStatus <> 'B' AND hdr.POStatus <> 'C' THEN 'false'
WHEN hdr.POStatus = 'B' OR hdr.POStatus = 'C' THEN 'true'
END AS "@CancelFlag",
det.SKU AS "ItemID",
CASE WHEN v.[CurrencyCode]='US'
THEN CONVERT(decimal(18,3),(det.UnitCost/ det.[ExchangeRate]))
ELSE CONVERT(decimal(18,3),det.UnitCost) END AS "UnitCost",
CASE WHEN v.[CurrencyCode]='US'
THEN CONVERT(decimal(18,3),((det.UnitCost/ det.[ExchangeRate]) * det.OrderQty))
ELSE CONVERT(decimal(18,3),(det.UnitCost * det.OrderQty)) END AS "TotalCost",
det.SeqNo AS "LineItemNumber",
'9999' AS "UserID",
det.ItemDesc AS "Description",
CASE WHEN det.UOM Is NULL OR det.UOM = '' THEN det.VendUOM ELSE det.UOM END AS "QuantityOrdered/@UnitOfMeasureCode",
'1' AS "QuantityOrdered/@Units",
det.OrderQty AS "QuantityOrdered"
FROM [dbo].[PODetail] det
INNER JOIN [dbo].[POHeader] p ON p.PONumber = det.PONumber
INNER JOIN [dbo].[ItemPLU] i ON det.[SKU]=i.[SKU]
INNER JOIN [dbo].[Vendor] v ON v.[VendorID]=p.[VendorID] AND v.[VendorSuffix]=p.[VendorSuffix]
WHERE det.[PONumber] = @PurchaseOrderId
FOR XML PATH ('LineItem'), Type
) AS "InventoryControlTransaction/PurchaseOrder",
hdr.NewStoreDisc AS "InventoryControlTransaction/PurchaseOrder/NewStoreCostDiscount",
CASE
WHEN hdr.POType ='A' THEN 'N'
WHEN hdr.POType ='C' THEN 'S'
WHEN hdr.POType ='R' THEN 'R'
END AS "InventoryControlTransaction/PurchaseOrder/PoType"
FROM POHeader hdr
WHERE hdr.PONumber = @PurchaseOrderId
FOR XML PATH ('Transaction'), Type
)
FOR XML PATH ('POSLog'), Type)));
SET @count = @count + 1;
exec [InsertOutboundIntegrationTransactions] @WebServiceSettingCode = 1,
@WebServiceTypeCode='003',
@Payload = @xml
END TRY
BEGIN CATCH
IF (LEN(RTRIM(LTRIM(ISNULL(@errorMessage,''))))<=0)
BEGIN
SELECT @errorMessage = 'Programa: ' + @progName --+ CHAR(10)
+ '. OrderId:' + CONVERT(varchar, ISNULL(@PurchaseOrderId,'')) --+ CHAR(10)
+ '. Linea:' + CONVERT(varchar, ERROR_LINE()) --+ CHAR(10)
+ '. Tempo:' + convert(varchar, getdate() - @timeElapsed, 8) + CHAR(10)
+ '. Mensaje: ' + ERROR_MESSAGE() --+ CHAR(10)
END
EXEC WriteSystemLog 4
, @progName
, @employeeID
, @errorMessage;
END CATCH
END
GO
