-- ============================================= -- Author: OMTV -- Create date: 22/06/2017 -- Description: Crear IF001 e IF002 para una transacción o ajuste en especifico -- ============================================= ALTER PROCEDURE [dbo].[usp_OutboundIntegrationStageSalesCreditonDemand] ( @TaxSequenceNumber AS INT , @TenderSequenceNumber AS INT ) AS BEGIN IF OBJECT_ID('tempdb..#ARTSCreditTransDemand') IS NOT NULL DROP TABLE #ARTSCreditTransDemand; DECLARE @time VARCHAR(14) , @xmlDoc VARCHAR(max) , @WebSettingCode INT , @CREDIT_XML XML; DECLARE @UPCs VARCHAR(MAX), @ConcatUPCs VARCHAR(MAX) SELECT @UPCs = COALESCE(@UPCs +',' ,'') + CONVERT(varchar(13), i.VendorUPC) FROM #ARTSSaleDemand arts INNER JOIN dbo.TranDetail td ON arts.TranNumber = td.TranNumber INNER JOIN dbo.ItemXref i ON i.SKU = td.SKU WHERE td.PickTake IN ('P', 'D') GROUP BY i.VendorUPC SELECT @ConcatUPCs = @UPCs DECLARE @SalesSequenceNumber AS INT DECLARE @LayawaysSequenceNumber AS INT DECLARE @CommentSequenceNumber AS INT DECLARE @POASequenceNumber AS INT PRINT 'Retrieving Sales Transaction Details for Credit Server...'; BEGIN TRY BEGIN /* REGION: Creation of XML for Credit Server */ SELECT TOP (1) REPLICATE('0',11-LEN(RTRIM(h.CustomerID))) + RTRIM(h.CustomerID) AS 'CustomerID' , h.TrainingModeFlag , h.RetailStoreID , h.WorkstationID , h.TillID , h.SequenceNumber , h.BeginDateTime , h.EndDateTime , h.BusinessDayDate , h.OperatorID , oh.CreateDate , oh.LastMntDate , h.TransactionCount , tto.RecType , tto.TypeOfSale , tto.PostVoidTOS , tto.PostVoidTran , tto.AppliedDep , od.ExtendedAmt , od.SKU , od.SKUDesc , od.OrderStatus , oh.OrderTotal , od.ClassNo , od.DeptNo , oh.OrderType , oh.CopySource , oh.EmployeeNo , oh.OutsideSlpn , tdet.PickTake , h.TranNumber , h.TranDate , tt.TenderCode , tt.MagStripe , tt.CardExpire , td.MiscNumber , tt.CreditAuthCode , oh.PrintedTotal , h.SubKey , h.Address , tto.SoldToAdd2 , h.City , h.State , h.PostalCode , h.CountryCode , h.Name , tto.PONumber , tto.ShipToAdd1 , tto.ShipToAdd2 , tto.ShipToCity , tto.ShipToState , tto.ShipToZip , tto.ShipBusName , tto.InvoiceNo , h.CustomerType , h.BusinessCustomerFlag , h.Telephone , h.TranTotal , h.TaxAmount , tto.Deposits , oh.FRegTaxTotal , oh.RegTaxTotal , oh.OrderNumber , tto.CostOfGoods INTO #ARTSCreditTransDemand FROM #ARTSSaleDemand h LEFT JOIN dbo.Customer c ON h.CustomerID = c.CustomerID LEFT JOIN dbo.TranTender tt ON h.TranNumber = tt.TranNumber and h.TranDate = tt.TranDate and h.Tillid = tt.RegisterId LEFT JOIN dbo.TranTendData td ON tt.TranNumber = td.TranNumber and td.TranDate = tt.TranDate and tt.RegisterId = td.RegisterId AND td.SigSequence = tt.SigSequence INNER JOIN dbo.TranTotal tto ON tto.TranNumber = h.TranNumber and tto.TranDate = h.TranDate and tto.RegisterId = h.Tillid LEFT JOIN dbo.TranDetail tdet ON h.TranNumber = tdet.TranNumber and td.TranDate = tdet.TranDate and h.Tillid = tdet.RegisterId LEFT JOIN dbo.OrderHeader oh ON (tdet.DocNumber = oh.OrderNumber and tdet.TypeOfSale NOT IN (34,36,44,46)) OR (tto.DocNumber = oh.OrderNumber and tto.TypeOfSale IN (34,36,44,46)) LEFT JOIN dbo.OrderDetail od ON oh.OrderNumber = od.OrderNumber LEFT JOIN dbo.TranTendData dat ON dat.RegisterID = tt.RegisterID AND dat.TranNumber = tt.TranNumber AND dat.TranDate = tt.TranDate AND dat.SigSequence = tt.SigSequence AND ((dat.[Sequence] = 2 AND -- MSI Tender 3 len(dat.MiscNumber) = 9 AND dat.MiscNumber NOT LIKE '%001') OR (dat.[Sequence] = 3 AND -- MSI Tender 4 len(dat.MiscNumber) = 9 AND dat.MiscNumber NOT LIKE '%001')) WHERE (c.Type = 'D' AND c.CustTypeNo NOT IN (10, 11)) OR -- Clientes AR que no son de Credinomina (c.CustTypeNo IN (10,11)) OR -- Clientes de Credinomina (tt.TenderCode IN (3,4,18, 19) AND SUBSTRING(td.MiscNumber,0,6) NOT IN ('600973', '854860', '881105') AND dat.MiscNumber IS NOT NULL) OR (SUBSTRING(td.MiscNumber,0,6) IN ('600973', '854860', '881105', '520021')) OR (tto.TypeOfSale = 52) OR -- Pago a Tarjeta de Credito (tto.TypeOfSale = 88) OR -- Post Voids (tto.TypeOfSale IN (34, 35, 36) AND oh.OrderType = 'S') OR -- Ordenes Especiales AR (tto.TypeOfSale IN (44, 45, 46) AND oh.OrderType = 'S' AND oh.CopySource = 'A') OR -- Apartados (tdet.MiscAcct = 6) -- Accesorios Financieros ORDER BY h.SequenceNumber ASC SET @SalesSequenceNumber = (select COUNT(*) from #ARTSSaleDemandLineItemDemand); SET @LayawaysSequenceNumber = (SELECT COUNT(*) FROM #ARTSCreditTransDemand ac JOIN dbo.OrderDetail od ON ac.OrderNumber = od.OrderNumber WHERE ac.TypeOfSale IN (44, 45, 46) AND ac.OrderType = 'S' AND ac.CopySource = 'A' ); SET @POASequenceNumber = CASE WHEN @LayawaysSequenceNumber > 0 THEN @LayawaysSequenceNumber + 1 ELSE @SalesSequenceNumber + 1 END; SET @TaxSequenceNumber = CASE WHEN EXISTS( SELECT * FROM #ARTSCreditTransDemand ac INNER JOIN dbo.TranPayment tp ON ac.TranNumber = tp.TranNumber ) THEN @POASequenceNumber + 1 ELSE @POASequenceNumber END; SET @CommentSequenceNumber = @TaxSequenceNumber + (SELECT COUNT(*) FROM dbo.TranTender tt JOIN #ARTSCreditTransDemand ac ON tt.TranDate = ac.TranDate AND tt.TranNumber = ac.TranNumber --modificado 23-05-2016 AND tt.RegisterID = ac.TillID) + 1; IF (@@ROWCOUNT > 0) /* Exist transactions to send Credit Server */ BEGIN SET @CREDIT_XML = ( SELECT TOP (1) ct.TrainingModeFlag AS '@TrainingModeFlag' , ct.RetailStoreID , ct.WorkstationID , ct.TillID , ct.SequenceNumber , ct.BeginDateTime , ct.EndDateTime , ct.BusinessDayDate , ct.OperatorID , ( SELECT ct.RecType AS '@TransactionStatus' , ct.POSTVoidTOS AS '@POSTVoidTOS' , ct1.TransactionCount , ( SELECT LineItem.SequenceNumber , ( SELECT Sale.ItemType AS '@ItemType' , Sale.ItemID , Sale.SpecialOrderNumber , Sale.MerchandiseHierarchy171 AS 'MerchandiseHierarchy' , Sale.ItemDescription as Description , FORMAT(Sale.UnitCostPrice, 'N2') AS 'UnitCostPrice' , FORMAT(Sale.UnitListPrice, 'N2') AS 'UnitListPrice' , FORMAT(Sale.RegularSalesUnitPrice, 'N2') AS 'RegularSalesUnitPrice' , FORMAT(Sale.ActualSalesUnitPrice, 'N2') AS 'ActualSalesUnitPrice' , FORMAT(Sale.ExtendedAmount, 'N2') AS 'ExtendedAmount' , FORMAT(Sale.UnitDiscountAmount, 'N2') AS 'UnitDiscountAmount' , FORMAT(Sale.ExtendedDiscountAmount, 'N2') AS 'ExtendedDiscountAmount' , ( SELECT CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.PrimaryUnits ELSE CASE WHEN Sale.AlternativeUOMConvert IS NOT NULL AND Sale.AlternativeUOMConvert<>0 THEN Sale.Quantity /Sale.AlternativeUOMConvert ELSE '' END END AS '@PrimaryUnits' , CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.UnitOfMeasureCode ELSE Sale.PrimaryUOM END AS '@UnitOfMeasureCode' , CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.Units ELSE CASE WHEN Sale.AlternativeUOMConvert IS NOT NULL AND Sale.AlternativeUOMConvert<>0 THEN CAST(1 AS DECIMAL(8,4))/Sale.AlternativeUOMConvert ELSE '' END END AS "@Units" , Sale.Quantity AS "text()" FOR XML PATH('Quantity') , ELEMENTS , TYPE ) , ( SELECT RetailPriceModifier.MethodCode AS "@MethodCode" , ( SELECT LineItem.SequenceNumber , m.PreviousPrice , m.NewPrice --, m.PromotionID , c.ARTS_Code AS 'ReasonCode' , ( SELECT TOP (1) m.ACTION AS "@Action" , ( SELECT m.Amount AS "text()" FOR XML PATH('') , TYPE ) FROM #ARTSRetailPriceModifierDemand m2 WHERE m2.RecID = m.RecID AND m2.SequenceNumber = m.SequenceNumber ORDER BY m.ACTION ASC FOR XML PATH('Amount') , TYPE ) FROM #ARTSRetailPriceModifierDemand m INNER JOIN dbo.ARTS_TypeCode c ON m.ReasonCode COLLATE DATABASE_DEFAULT = c.POS_Code COLLATE DATABASE_DEFAULT AND c.NAME = 'ReasonCode' AND m.RecID = RetailPriceModifier.RecID AND m.SequenceNumber = RetailPriceModifier.SequenceNumber WHERE m.RecID = RetailPriceModifier.RecID AND m.SequenceNumber = RetailPriceModifier.SequenceNumber AND m.PromoCondition = RetailPriceModifier.PromoCondition FOR XML PATH('') , TYPE , ELEMENTS ) FROM #ARTSRetailPriceModifierDemand RetailPriceModifier WHERE RetailPriceModifier.RecID = Sale.RecID FOR XML PATH('RetailPriceModifier') ,TYPE --FOR XML AUTO -- , TYPE ), ct1.InvoiceNo AS 'Invoice' ,( SELECT CASE WHEN td.SecCouponNo <> '' THEN (CONCAT(madre.PromoID, '-', RIGHT('00'+CAST(hija.CreditTerm AS VARCHAR(2)),2)) ) ELSE NULL END 'TEXT()' FROM TranDetail td INNER JOIN PromoDet pd ON pd.PromoRel = td.SecCouponNo INNER JOIN PromoHdr madre ON madre.PromoID = pd.PromoID INNER JOIN PromoHdr hija ON hija.PromoID = pd.PromoRel WHERE TD.RegisterID = SALE.RegisterID and td.TranDate = sale.trandate and sale.trannumber = td.TranNumber and sale.RecID = td.AutoSequence ) AS 'CredTermPromotion' FROM #ARTSSaleDemandLineItemDemand AS Sale WHERE Sale.PickTake = 'T' AND Sale.RecID = LineItem.RecID AND ct.TypeOfSale <> 88--Take Method FOR XML PATH('Sale') , TYPE , ELEMENTS --FOR XML AUTO -- , TYPE ) , ( SELECT Sale.ItemType AS '@ItemType' , Sale.ItemID , Sale.SpecialOrderNumber , ( SELECT [text()] = ISNULL(( SELECT TOP (1) od.OrdSeqNumber FROM dbo.OrderDetail od INNER JOIN dbo.TranDetail td ON od.OrderNumber = td.DocNumber AND od.OrdSeqNumber = td.SequenceNo GROUP BY od.OrdSeqNumber HAVING COUNT(od.OrderNumber) > 0 ORDER BY od.OrdSeqNumber ASC ), '0') FOR XML PATH('SpecialOrderSequenceNumber') , TYPE ) , Sale.MerchandiseHierarchy171 AS 'MerchandiseHierarchy' , Sale.ItemDescription as Description , FORMAT(Sale.UnitCostPrice, 'N2') AS 'UnitCostPrice' , FORMAT(Sale.UnitListPrice, 'N2') AS 'UnitListPrice' , FORMAT(Sale.RegularSalesUnitPrice, 'N2') AS 'RegularSalesUnitPrice' , FORMAT(Sale.ActualSalesUnitPrice, 'N2') AS 'ActualSalesUnitPrice' , FORMAT(Sale.ExtendedAmount, 'N2') AS 'ExtendedAmount' , FORMAT(Sale.UnitDiscountAmount, 'N2') AS 'UnitDiscountAmount' , FORMAT(Sale.ExtendedDiscountAmount, 'N2') AS 'ExtendedDiscountAmount' , ( SELECT CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.PrimaryUnits ELSE CASE WHEN Sale.AlternativeUOMConvert IS NOT NULL AND Sale.AlternativeUOMConvert<>0 THEN Sale.Quantity /Sale.AlternativeUOMConvert ELSE '' END END AS '@PrimaryUnits' , CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.UnitOfMeasureCode ELSE Sale.PrimaryUOM END AS '@UnitOfMeasureCode' , CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.Units ELSE CASE WHEN Sale.AlternativeUOMConvert IS NOT NULL AND Sale.AlternativeUOMConvert<>0 THEN CAST(1 AS DECIMAL(8,4))/Sale.AlternativeUOMConvert ELSE '' END END AS "@Units" , Sale.Quantity AS "text()" FOR XML PATH('Quantity') , ELEMENTS , TYPE ) , ( SELECT RetailPriceModifier.MethodCode AS "@MethodCode" , ( SELECT LineItem.SequenceNumber , m.PreviousPrice , m.NewPrice --, m.PromotionID , c.ARTS_Code AS 'ReasonCode' , ( SELECT TOP (1) m.ACTION AS "@Action" , ( SELECT m.Amount AS "text()" FOR XML PATH('') , TYPE ) FROM #ARTSRetailPriceModifierDemand m2 WHERE m2.RecID = m.RecID AND m2.SequenceNumber = m.SequenceNumber ORDER BY m.ACTION ASC FOR XML PATH('Amount') , TYPE ) FROM #ARTSRetailPriceModifierDemand m INNER JOIN dbo.ARTS_TypeCode c ON m.ReasonCode COLLATE DATABASE_DEFAULT = c.POS_Code COLLATE DATABASE_DEFAULT AND c.NAME = 'ReasonCode' AND m.RecID = RetailPriceModifier.RecID AND m.SequenceNumber = RetailPriceModifier.SequenceNumber WHERE m.RecID = RetailPriceModifier.RecID AND m.SequenceNumber = RetailPriceModifier.SequenceNumber and m.PromoCondition = RetailPriceModifier.PromoCondition FOR XML PATH('') , TYPE , ELEMENTS ) FROM #ARTSRetailPriceModifierDemand RetailPriceModifier WHERE RetailPriceModifier.RecID = Sale.RecID FOR XML PATH('RetailPriceModifier'),TYPE --FOR XML AUTO -- , TYPE ) , ( SELECT [text()] = ISNULL(( SELECT TOP (1) CAST(l.ZoneId AS VARCHAR(100)) + ' ' + CAST(l.Bays AS VARCHAR(5)) FROM #ARTSCreditTransDemand ac JOIN dbo.LocationSKU l ON ac.SKU = l.SKU WHERE ac.PickTake = 'P' GROUP BY ZoneId, Bays HAVING COUNT(ac.SKU) > 0 ORDER BY l.ZoneId ASC ), '0') FOR XML PATH('SellingLocation'), TYPE ) , ( SELECT ( SELECT ( SELECT IsNull(A.FullName,B.FullName) FROM( SELECT IsNull(e.FirstName + ' ' + e.LastName,'') as FullName, 1 as Id FROM #ARTSCreditTransDemand ac INNER JOIN dbo.Employee e ON ac.EmployeeNo = e.EmployeeNo) A right join (select '?' as FullName, 1 as Id) B on A.Id=B.Id ) as "@OperatorName" , ( SELECT e.PayrollId FROM #ARTSCreditTransDemand ac JOIN dbo.Employee e ON ac.EmployeeNo = e.EmployeeNo ) FOR XML PATH('AssociateID'), TYPE ) FOR XML PATH('Associate') , ELEMENTS , TYPE ) , ct1.InvoiceNo AS 'Invoice' ,( SELECT CASE WHEN td.SecCouponNo <> '' THEN (CONCAT(madre.PromoID, '-', RIGHT('00'+CAST(hija.CreditTerm AS VARCHAR(2)),2)) ) ELSE NULL END 'TEXT()' FROM TranDetail td INNER JOIN PromoDet pd ON pd.PromoRel = td.SecCouponNo INNER JOIN PromoHdr madre ON madre.PromoID = pd.PromoID INNER JOIN PromoHdr hija ON hija.PromoID = pd.PromoRel WHERE TD.RegisterID = SALE.RegisterID and td.TranDate = sale.trandate and sale.trannumber = td.TranNumber and sale.RecID = td.AutoSequence ) AS 'CredTermPromotion' FROM #ARTSSaleDemandLineItemDemand AS Sale WHERE Sale.PickTake = 'P' AND Sale.RecID = LineItem.RecID AND ct.TypeOfSale <> 88--Pickup Method FOR XML PATH('SaleForPickup'), TYPE, ELEMENTS --FOR XML AUTO -- , TYPE ) , ( SELECT Sale.ItemType AS '@ItemType' , Sale.ItemID , Sale.SpecialOrderNumber , ( SELECT [text()] = ISNULL(( SELECT TOP (1) od.OrdSeqNumber FROM dbo.OrderDetail od INNER JOIN dbo.TranDetail td ON od.OrderNumber = td.DocNumber AND od.OrdSeqNumber = td.SequenceNo GROUP BY od.OrdSeqNumber HAVING COUNT(od.OrderNumber) > 0 ORDER BY od.OrdSeqNumber ASC ), '0') FOR XML PATH('SpecialOrderSequenceNumber'), TYPE ) , Sale.MerchandiseHierarchy171 AS 'MerchandiseHierarchy' , Sale.ItemDescription as Description , FORMAT(Sale.UnitCostPrice, 'N2') AS 'UnitCostPrice' , FORMAT(Sale.UnitListPrice, 'N2') AS 'UnitListPrice' , FORMAT(Sale.RegularSalesUnitPrice, 'N2') AS 'RegularSalesUnitPrice' , FORMAT(Sale.ActualSalesUnitPrice, 'N2') AS 'ActualSalesUnitPrice' , FORMAT(Sale.ExtendedAmount, 'N2') AS 'ExtendedAmount' , FORMAT(Sale.UnitDiscountAmount, 'N2') AS 'UnitDiscountAmount' , FORMAT(Sale.ExtendedDiscountAmount, 'N2') AS 'ExtendedDiscountAmount' , ( SELECT CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.PrimaryUnits ELSE CASE WHEN Sale.AlternativeUOMConvert IS NOT NULL AND Sale.AlternativeUOMConvert<>0 THEN Sale.Quantity /Sale.AlternativeUOMConvert ELSE '' END END AS '@PrimaryUnits' , CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.UnitOfMeasureCode ELSE Sale.PrimaryUOM END AS '@UnitOfMeasureCode' , CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.Units ELSE CASE WHEN Sale.AlternativeUOMConvert IS NOT NULL AND Sale.AlternativeUOMConvert<>0 THEN CAST(1 AS DECIMAL(8,4))/Sale.AlternativeUOMConvert ELSE '' END END AS "@Units" , Sale.Quantity AS "text()" FOR XML PATH('Quantity') , ELEMENTS , TYPE ) , ( SELECT [text()] = ISNULL(( SELECT TOP (1) CAST(l.ZoneId AS VARCHAR(100)) + ' ' + CAST(l.Bays AS VARCHAR(5)) FROM #ARTSCreditTransDemand ac JOIN dbo.LocationSKU l ON ac.SKU = l.SKU WHERE ac.PickTake = 'D' GROUP BY ZoneId, Bays HAVING COUNT(ac.SKU) > 0 ORDER BY l.ZoneId ASC ), '0') FOR XML PATH('SellingLocation'), TYPE ) , ( SELECT ( SELECT IsNull(A.FullName,B.FullName) as "@OperatorName" FROM( SELECT IsNull(e.FirstName + ' ' + e.LastName,'') as FullName, 1 as Id FROM #ARTSCreditTransDemand ac INNER JOIN dbo.Employee e ON ac.EmployeeNo = e.EmployeeNo) A right join (select '?' as FullName, 1 as Id) B on A.Id=B.Id FOR XML PATH('AssociateID'), TYPE ) FOR XML PATH('Associate') , ELEMENTS , TYPE ) , ( SELECT ct.CustomerID ,( SELECT acn.Name FROM #ARTSCreditTransDemand acn FOR XML PATH(''), TYPE ) , ( SELECT ( SELECT [text()] = IsNull(IsNULL(CASE WHEN LTRIM(B.Latitude) ='' THEN NULL ELSE B.Latitude END, A.Latitude),'') FROM (SELECT oh.Latitude,td.TranDate, td.TranNumber, td.RegisterID FROM dbo.OrderHeader oh LEFT JOIN dbo.TranDetail td ON oh.OrderNumber = td.DocNumber inner JOIN dbo.TranTotal tt ON td.TranDate = tt.TranDate and td.TranNumber=TT.TranNumber and td.RegisterID=tt.RegisterID inner join #ARTSCreditTransDemand Ar on AR.TranDate = td.TranDate and ar.TranNumber=td.TranNumber WHERE td.PickTake = 'P') B left join (select SUBSTRING(tt.ShipMidInit, 1, CHARINDEX('|', tt.ShipMidInit) - 1) as Latitude, td.TranDate, td.TranNumber, td.RegisterID from dbo.TranDetail td inner join dbo.trantotal tt ON td.TranDate = tt.TranDate and td.TranNumber=TT.TranNumber and td.RegisterID=tt.RegisterID inner join #ARTSCreditTransDemand ar on AR.TranDate = td.TranDate and ar.TranNumber=td.TranNumber WHERE td.PickTake = 'P' and td.RegisterID=80) A on B.TranDate = A.TranDate and B.TranNumber=a.TranNumber and B.RegisterID=a.RegisterID FOR XML PATH('Latitude'), TYPE ) , ( SELECT [text()] = IsNull(IsNULL(CASE WHEN LTRIM(B.Length) ='' THEN NULL ELSE B.Length END, A.Latitude),'') FROM (SELECT oh.Length,td.TranDate, td.TranNumber, td.RegisterID FROM dbo.OrderHeader oh LEFT JOIN dbo.TranDetail td ON oh.OrderNumber = td.DocNumber inner JOIN dbo.TranTotal tt ON td.TranDate = tt.TranDate and td.TranNumber=TT.TranNumber and td.RegisterID=tt.RegisterID inner join #ARTSCreditTransDemand Ar on AR.TranDate = td.TranDate and ar.TranNumber=td.TranNumber WHERE td.PickTake = 'P') B left join (select SUBSTRING(tt.ShipMidInit, 1, CHARINDEX('|', tt.ShipMidInit) - 1) as Latitude, td.TranDate, td.TranNumber, td.RegisterID from dbo.TranDetail td inner join dbo.trantotal tt ON td.TranDate = tt.TranDate and td.TranNumber=TT.TranNumber and td.RegisterID=tt.RegisterID inner join #ARTSCreditTransDemand ar on AR.TranDate = td.TranDate and ar.TranNumber=td.TranNumber WHERE td.PickTake = 'P' and td.RegisterID=80) A on B.TranDate = A.TranDate and B.TranNumber=a.TranNumber and B.RegisterID=a.RegisterID FOR XML PATH('Longitude'), TYPE ) FOR XML PATH('Address') , ELEMENTS , TYPE ) , ( SELECT [text()] = CONVERT(DATETIME, ISNULL(( SELECT TOP (1) STUFF(CONVERT(varchar(25), GETDATE(), 120), 6,5, CONCAT(SUBSTRING(td.SerialNumber, 6, 2),'-', SUBSTRING(td.SerialNumber, 9, 2))) FROM #ARTSCreditTransDemand ac INNER JOIN dbo.TranDetail td ON ac.TranNumber = td.TranNumber WHERE LEN(td.SerialNumber) >= 10 AND SUBSTRING(td.SerialNumber, 6, 2) IS NOT NULL AND SUBSTRING(td.SerialNumber, 9, 2) IS NOT NULL AND ac.PickTake = 'P' ORDER BY ac.TranNumber ASC ), GETDATE())) FOR XML PATH('PreferredDateTime'), TYPE ) , ( SELECT [text()] = ISNULL(( SELECT TOP (1) @ConcatUPCs FROM #ARTSSaleDemandLineItemDemand item WHERE item.PickTake = 'D' ORDER BY item.PickTake ASC ), '?') FOR XML PATH('Notes'), TYPE ) FOR XML PATH('Delivery') , ELEMENTS , TYPE ), ct1.InvoiceNo AS 'Invoice', ( SELECT CASE WHEN td.SecCouponNo <> '' THEN (CONCAT(madre.PromoID, '-', RIGHT('00'+CAST(hija.CreditTerm AS VARCHAR(2)),2)) ) ELSE NULL END 'TEXT()' FROM TranDetail td INNER JOIN PromoDet pd ON pd.PromoRel = td.SecCouponNo INNER JOIN PromoHdr madre ON madre.PromoID = pd.PromoID INNER JOIN PromoHdr hija ON hija.PromoID = pd.PromoRel WHERE TD.RegisterID = SALE.RegisterID and td.TranDate = sale.trandate and sale.trannumber = td.TranNumber and sale.RecID = td.AutoSequence ) AS 'CredTermPromotion' FROM #ARTSSaleDemandLineItemDemand AS Sale WHERE Sale.PickTake = 'D' AND Sale.RecID = LineItem.RecID AND ct.TypeOfSale <> 88--Delivery Method FOR XML PATH('SaleForDelivery'), TYPE, ELEMENTS --FOR XML AUTO -- , TYPE ) , ( SELECT ReturnLineItem.ItemType AS '@ItemType' , ReturnLineItem.ItemID , ReturnLineItem.SpecialOrderNumber , ReturnLineItem.MerchandiseHierarchy171 AS 'MerchandiseHierarchy' , FORMAT(ReturnLineItem.UnitCostPrice, 'N2') AS 'UnitCostPrice' , FORMAT(ReturnLineItem.UnitListPrice, 'N2') AS 'UnitListPrice' , FORMAT(ReturnLineItem.RegularSalesUnitPrice, 'N2') AS 'RegularSalesUnitPrice' , FORMAT(ReturnLineItem.ActualSalesUnitPrice, 'N2') AS 'ActualSalesUnitPrice' , FORMAT(ReturnLineItem.ExtendedAmount, 'N2') AS 'ExtendedAmount' , FORMAT(ReturnLineItem.UnitDiscountAmount, 'N2') AS 'UnitDiscountAmount' , FORMAT(ReturnLineItem.ExtendedDiscountAmount, 'N2') AS 'ExtendedDiscountAmount' , ( SELECT CASE WHEN ReturnLineItem.IsAnAlternativeItem = 0 THEN ReturnLineItem.PrimaryUnits ELSE CASE WHEN ReturnLineItem.AlternativeUOMConvert IS NOT NULL AND ReturnLineItem.AlternativeUOMConvert<>0 THEN ReturnLineItem.Quantity /ReturnLineItem.AlternativeUOMConvert ELSE '' END END AS '@PrimaryUnits' , CASE WHEN ReturnLineItem.IsAnAlternativeItem = 0 THEN ReturnLineItem.UnitOfMeasureCode ELSE ReturnLineItem.PrimaryUOM END AS '@UnitOfMeasureCode' , CASE WHEN ReturnLineItem.IsAnAlternativeItem = 0 THEN ReturnLineItem.Units ELSE CASE WHEN ReturnLineItem.AlternativeUOMConvert IS NOT NULL AND ReturnLineItem.AlternativeUOMConvert<>0 THEN CAST(1 AS DECIMAL(8,4))/ReturnLineItem.AlternativeUOMConvert ELSE '' END END AS "@Units" , ReturnLineItem.Quantity AS "text()" FOR XML PATH('Quantity') , ELEMENTS , TYPE ) , ct1.InvoiceNo AS 'Invoice' FROM #ARTSSaleDemandLineItemDemand AS ReturnLineItem WHERE ReturnLineItem.RecID = LineItem.RecID AND ct.TypeOfSale = 88 FOR XML PATH('Return'), TYPE, ELEMENTS --FOR XML AUTO -- , TYPE ), ( ( SELECT i.VendorId SupplierID, CONCAT(REPLICATE('0',2-LEN(RTRIM(i.VendorSuffix))), RTRIM(i.VendorSuffix)) 'Sufix', ISNULL(v.VendorName,'') 'SupplierName' FROM ItemPlu i INNER JOIN Vendor v ON i.VendorId = v.VendorId AND V.VendorSuffix = i.VendorSuffix and i.sku = LineItem.SKU FOR XML PATH('SupplierInformation'), TYPE, ELEMENTS ) ) FROM #ARTSSaleDemandLineItemDemand LineItem WHERE LineItem.TranDate = ct.TranDate --AND LineItem.SpecialOrderNumber = 0 --Not a future order AND LineItem.TranNumber = ct.TranNumber AND LineItem.RegisterID = ct.TillID --WHERE LineItem.PickTake <> 'D' --Take method -- --AND LineItem.SpecialOrderNumber = 0 --Not a future order -- AND LineItem.TranDate = ct.TranDate -- AND LineItem.TranNumber = ct.TranNumber -- AND LineItem.RegisterID = ct.TillID --GROUP BY LineItem.TranNumber, LineItem.TranDate, LineItem.RegisterID FOR XML PATH('LineItem') , TYPE , ELEMENTS ) , ( SELECT @SalesSequenceNumber + od.OrdSeqNumber AS 'SequenceNumber' , ( SELECT od.OrderStatus AS '@Status' , ISNULL(ac.Deposits, ac.OrderTotal) AS 'Deposit' , od.ExtendedAmt AS 'ExtendedAmount' , od.SKU AS 'ItemID' , od.SKUDesc AS 'Description' , od.DeptNo + '-' + od.ClassNo + '-' + od.SubClass AS 'MerchandiseHierarchy' FOR XML PATH('Layaway') , TYPE , ELEMENTS ) FROM #ARTSCreditTransDemand ac JOIN dbo.OrderDetail od ON ac.OrderNumber = od.OrderNumber WHERE ac.TypeOfSale IN (44, 45, 46) AND ac.OrderType = 'S' AND ac.CopySource = 'A' FOR XML PATH('LineItem') ,TYPE , ELEMENTS ) , ( SELECT @POASequenceNumber AS 'SequenceNumber' , ( SELECT ISNULL(NULLIF(tp.RefNumber,''),'0') AS '@AccountCode' , tp.CCNumber AS 'AccountCardNumber' , tp.PaymentAmt AS 'Amount' FROM #ARTSCreditTransDemand ac INNER JOIN dbo.TranPayment tp ON ac.TranNumber = tp.TranNumber FOR XML PATH('PaymentOnAccount') , ELEMENTS , TYPE ) FROM #ARTSCreditTransDemand ac INNER JOIN dbo.TranPayment tp ON ac.TranNumber = tp.TranNumber FOR XML PATH('LineItem') , TYPE , ELEMENTS ) , ( SELECT @TaxSequenceNumber AS SequenceNumber , ( SELECT CASE WHEN x.TaxCode = 0 THEN 'Exempt' ELSE 'Standard' END AS 'TaxSubType' , CASE WHEN x.TaxCode = 1 THEN 'VAT' ELSE 'VAT' END AS 'TaxType' , CASE WHEN ( SELECT TOS.Description FROM dbo.TOS WHERE TOS.Code = x.TypeOfSale AND ( Sign = '+' OR Sign = 'E' ) ) IS NOT NULL THEN 'Sale' WHEN ( SELECT TOS.Description FROM dbo.TOS WHERE TOS.Code = x.TypeOfSale AND Sign = '-' ) IS NOT NULL THEN 'Refund' END AS 'TypeCode' , ( SELECT CASE WHEN x.TaxableSales > 0 THEN x.TaxableSales WHEN x.NonTaxSales > 0 THEN x.NonTaxSales ELSE 0 END AS 'TaxableAmount' , s.TaxAmount AS 'Amount' , ( SELECT tax.Rate FROM dbo.Tax WHERE Tax.TaxCode = x.TaxCode ) AS 'Percent' FROM dbo.TranTax s WHERE s.TranDate = tax.TranDate AND s.TranNumber = tax.TranNumber AND s.RegisterID = tax.RegisterID AND s.TaxCode = tax.TaxCode FOR XML PATH('') , TYPE , ELEMENTS ) FROM dbo.TranTax AS Tax WHERE Tax.TranDate = x.TranDate AND tax.TranNumber = x.TranNumber AND tax.RegisterID = x.RegisterID AND tax.TaxCode = x.TaxCode FOR XML AUTO , TYPE ) FROM dbo.TranTax x WHERE x.TranDate = ct1.TranDate AND x.TranNumber = ct1.TranNumber AND x.RegisterID = ct1.WorkstationID FOR XML PATH('LineItem') , TYPE ) , ( SELECT @TaxSequenceNumber + ROW_NUMBER() OVER(ORDER BY RegisterId DESC) AS 'SequenceNumber' , ( SELECT Tender.TenderCode AS 'TenderType' , CASE WHEN ( SELECT TOS.Description FROM dbo.TOS WHERE TOS.Code = x.TypeOfSale AND ( Sign = '+' OR Sign = 'E' ) ) IS NOT NULL THEN 'Sale' WHEN ( SELECT TOS.Description FROM dbo.TOS WHERE TOS.Code = x.TypeOfSale AND Sign = '-' ) IS NOT NULL THEN 'Refund' END AS 'TypeCode' , ( SELECT --n1.TranNumber AS 'TranNum', n1.TenderAmount AS 'Amount' , ( SELECT n1.TenderAmount AS 'RequestedAmount' , n1.TenderAuth AS 'AuthorizationCode' , ( SELECT ttd.MiscNumber FROM dbo.TranTendData ttd WHERE n1.TranNumber = ttd.TranNumber AND n1.TranDate = ttd.TranDate AND n1.RegisterID = ttd.RegisterID AND n1.SigSequence =ttd.SigSequence AND ttd.Sequence = 4 ) AS 'ReferenceNumber' , ( SELECT ttd.MiscNumber FROM dbo.TranTendData ttd WHERE n1.TranNumber = ttd.TranNumber AND n1.TranDate = ttd.TranDate AND n1.RegisterID = ttd.RegisterID AND n1.SigSequence =ttd.SigSequence AND ttd.Sequence = 3 ) AS 'MerchantNumber' , ( SELECT ttd.MiscNumber + '-' + n1.Xreference FROM dbo.TranTendData ttd WHERE n1.TranNumber = ttd.TranNumber AND n1.TranDate = ttd.TranDate AND n1.RegisterID = ttd.RegisterID AND n1.SigSequence =ttd.SigSequence AND ttd.Sequence = 2 ) AS 'ProviderID' , CONVERT(DATETIME, ISNULL(n1.CreditDate, GETDATE())) AS 'AuthorizationDateTime' WHERE n1.TenderAuth <> '' FOR XML PATH('Authorization') , TYPE ) , ( SELECT 'Credit' AS '@CardType' , CASE WHEN SUBSTRING(ttd.MiscNumber, 0, 2) = '37' THEN 'Amex' WHEN SUBSTRING(ttd.MiscNumber, 0, 2) IN ('36', '38') OR SUBSTRING(ttd.MiscNumber, 0, 3) IN ('300', '301', '302', '303', '304') THEN 'Diners' ELSE '' END AS '@TypeCode' , ttd.MiscNumber AS 'IssuerIdentificationNumber' , n1.MagStripe AS 'CardHolderName' , ttd.MiscNumber AS 'PrimaryAccountNumber' , n1.CardExpire AS 'ExpirationDate' FROM dbo.TranTendData ttd WHERE ttd.TranNumber = n1.TranNumber AND ttd.TranDate = n1.TranDate AND ttd.RegisterId = n1.RegisterId AND ttd.SigSequence = n1.SigSequence AND ttd.Sequence = 1 AND n1.TenderCode IN (3,4) FOR XML PATH('CreditDebit') , TYPE ) FROM dbo.TranTender n1 WHERE n1.TranDate = Tender.TranDate AND n1.TranNumber = Tender.TranNumber AND n1.RegisterID = Tender.RegisterID AND n1.TenderCode = Tender.TenderCode AND n1.SigSequence = Tender.SigSequence FOR XML RAW('') , TYPE , ELEMENTS ) FROM dbo.TranTender AS Tender INNER JOIN dbo.ARTS_TypeCode c ON Tender.TenderCode = c.POS_Code AND c.NAME = 'TenderType' AND Tender.TenderCode = x.TenderCode AND Tender.TranDate = x.TranDate AND Tender.TranNumber = x.TranNumber AND Tender.RegisterID = x.RegisterID AND Tender.SigSequence = x.SigSequence --AND tax.TaxCode = d.TaxCode FOR XML RAW('Tender') , TYPE ) FROM dbo.TranTender x WHERE x.TranDate = ct1.TranDate AND x.TranNumber = ct1.TranNumber AND x.RegisterID = ct1.TillID FOR XML PATH('LineItem') , TYPE ) , ( SELECT TOP (1) @CommentSequenceNumber AS 'SequenceNumber' , ac.OutsideSlpn AS 'Comment' FROM #ARTSCreditTransDemand ac WHERE ac.OutsideSlpn <> '' AND ac.OutsideSlpn IS NOT NULL ORDER BY ac.OutsideSlpn ASC FOR XML PATH('LineItem') , TYPE , ELEMENTS ) , ( SELECT CASE WHEN c.Type IN ('D', 'C') THEN 'true' ELSE 'false' END AS '@BusinessCustomerFlag' , ct1.CustomerID , ct1.NAME AS 'Name' , ( SELECT ct1.Telephone AS 'FullTelephoneNumber' WHERE ct1.Telephone <> '' AND ct1.Telephone IS NOT NULL FOR XML PATH('Telephone') , ELEMENTS , TYPE ) , ( SELECT REPLICATE('0',11-LEN(RTRIM(tt.AccountNo))) + RTRIM(tt.AccountNo) FROM #ARTSCreditTransDemand ac JOIN dbo.TranTotal tt ON ac.TranNumber = tt.TranNumber AND ac.TranDate = tt.TranDate and tt.RegisterID = ac.TillID ) AS 'AccountNumber' , ( SELECT ct1.Address AS 'AddressLine' , ct1.City AS 'City' , ct1.State , ct1.PostalCode AS 'PostalCode' FOR XML PATH('Address') , ELEMENTS , TYPE ) , ( SELECT TOP (1) 'PO' AS 'Name' , ac.PONumber AS 'Value' FROM #ARTSCreditTransDemand ac WHERE ac.PONumber <> '' ORDER BY ac.PONumber ASC FOR XML PATH('LocalRequirements') , TYPE ), ( SELECT TOP (1) 'SpecialOrderNumber' AS 'Name' , acL.OrderNumber AS 'Value' FROM #ARTSCreditTransDemand acL where acl.TypeOfSale IN (34,36) FOR XML PATH('LocalRequirements') , TYPE ) FROM dbo.Customer c WHERE c.CustomerID = ct1.CustomerID AND c.SubKey = ct1.SubKey FOR XML PATH('Customer') , TYPE ) , ct.TypeOfSale AS 'TypeOfSale' , ( SELECT 'GrossAmount' AS '@TotalType' , ct.Deposits AS "text()" FOR XML PATH('Total') , ELEMENTS , TYPE ) , ( SELECT 'NonSalesAmount' AS '@TotalType' , ISNULL(ct.PrintedTotal, 0) AS "text()" FOR XML PATH('Total') , ELEMENTS , TYPE ) , ( SELECT 'GrandAmount' AS '@TotalType' , ( SELECT CASE WHEN ct1.TypeOfSale IN (44, 45, 46) AND ct1.OrderType = 'S' AND ct1.CopySource = 'A' THEN ct1.OrderTotal ELSE ct1.TranTotal END AS "text()" ) FOR XML PATH('Total') , ELEMENTS , TYPE ) , ( SELECT 'TaxAmount' AS '@TotalType' , ct1.TaxAmount AS "text()" FOR XML PATH('Total') , ELEMENTS , TYPE ) , ( SELECT 'NetAmount' AS '@TotalType' , ct1.AppliedDep AS "text()" FOR XML PATH('Total') , ELEMENTS , TYPE ) , ( SELECT 'CostAmount' AS '@TotalType' ,ct.CostOfGoods AS "text()" FOR XML PATH('Total') , ELEMENTS , TYPE ), ( SELECT TOP (1) InvoiceNo AS 'BillNumber' FROM #ARTSCreditTransDemand ORDER BY InvoiceNo ASC FOR XML PATH('OfficialBill') , TYPE ) , ( SELECT TOP (1) RetailStoreID , WorkstationID , POSTVoidTran AS 'SequenceNumber' , BusinessDayDate , OperatorID FROM #ARTSCreditTransDemand WHERE ct.TypeOfSale = 88 ORDER BY RetailStoreID ASC FOR XML PATH('TransactionLink') , TYPE ) FROM #ARTSCreditTransDemand ct1 WHERE ct1.TranDate = ct.TranDate AND ct1.SequenceNumber = ct.SequenceNumber AND ct1.TillID = ct.TillID FOR XML PATH('RetailTransaction') , TYPE , ELEMENTS ) FROM #ARTSCreditTransDemand ct ORDER BY ct.TranDate , ct.TranNumber , ct.TillID FOR XML PATH('Transaction') , ROOT('POSLog') , TYPE );--Save output in memory xml file --Delete empty tags END; END; /*END REGION */ END TRY BEGIN CATCH DECLARE @err_message VARCHAR(max) SET @err_message = ERROR_MESSAGE(); print ERROR_MESSAGE(); PRINT @@ERROR END CATCH PRINT 'verify cREDIT XML'; BEGIN TRY IF @CREDIT_XML IS NOT NULL BEGIN DECLARE @intCreditErrorCode INT; SELECT @intCreditErrorCode = @@ERROR; -- HGU REFRESH --SELECT @CREDIT_XML AS Before; SET @Credit_XML.modify('delete //*[not(node())]'); -- HGU REFRESH --SELECT @CREDIT_XML AS After; SET @xmlDoc = CAST(@CREDIT_XML AS VARCHAR(max)); SET @WebSettingCode = 103; -- Insert into stagging table. -- Need to change Runsequence, processingcount when implement INSERT INTO dbo.OutboundIntegrationTransactions ( WebserviceSettingCode , WebServiceTypeCode , TypeCode , Payload , RunSequence , ProcessingCount , STATUS , DateCreated ) SELECT @WebSettingCode , '171' , 'Sale' , @xmlDoc , 1 , 1 , 1 , GETDATE(); END;--if @XML is not null END TRY BEGIN CATCH print ERROR_MESSAGE(); PRINT @@ERROR END CATCH END