/****** Object:  StoredProcedure [dbo].[usp_OutboundIntegrationStageSalesOnDemand]    Script Date: 5/19/2020 6:19:45 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		OMTV
-- Create date: 22/06/2017
-- Description:	Crear IF001 e IF002 para una transacción o ajuste en especifico
-- =============================================

-- Change History: 
---------------------------------------------------------------
-- Date        Author            Description
-- ----------- ----------------- ------------------------------
-- 2018-Abr-24 Isaí Yepez, se agrega valor 21 en CustTypeNo para considerar clientes oficios en línea 829

ALTER  PROCEDURE [dbo].[usp_OutboundIntegrationStageSalesOnDemand]
(
	@TranNumber INT = NULL
    , @TranDate DATETIME = NULL
    , @RegisterID SMALLINT = NULL
)
AS
BEGIN


-- Create temp tables that will be shared across the extracts.
IF OBJECT_ID('tempdb..#ARTSSaleDemand') IS NOT NULL
    DROP TABLE #ARTSSaleDemand;

IF OBJECT_ID('tempdb..#ARTSRetailPriceModifierDemand') IS NOT NULL
    DROP TABLE #ARTSRetailPriceModifierDemand;

IF OBJECT_ID('tempdb..#ARTSSaleDemandLineItemDemand') IS NOT NULL
    DROP TABLE #ARTSSaleDemandLineItemDemand;

IF OBJECT_ID('tempdb..#Temp1Demand') IS NOT NULL
    DROP TABLE #Temp1Demand;

BEGIN TRY
    DECLARE @PROC_NAME VARCHAR(256)
        , @START_TIME DATETIME
        --, @ERROR_NOT_PROVIDED INT
        --, @ERROR_INVALID INT
        --, @ERROR_FAILED_IN_CALL INT
        , @ERROR_FAILED_WITH_MSG INT
        --, @SQL VARCHAR(max);
    DECLARE @time VARCHAR(14)
        --, @dummy VARCHAR(max)
        , @err_message VARCHAR(max)
        --, @err_number INT
        , @xmlDoc VARCHAR(max)
        , @WebSettingCode INT
        , @XML XML		
		, @EsOrdenDeEcommerce				BIT
		, @OrderNumber INT
		, @EcommOrderNumber INT
		, @IsDevolution BIT = 0
		, @OrderDevolution INT = NULL;

    -- Set constants.
    SET @PROC_NAME = OBJECT_NAME(@@PROCID);
    SET @START_TIME = CURRENT_TIMESTAMP;
    --SET @ERROR_NOT_PROVIDED = 50001;
    --SET @ERROR_INVALID = 50002;
    --SET @ERROR_FAILED_IN_CALL = 50013;
    SET @ERROR_FAILED_WITH_MSG = 50014;
    
    --
    --  Load raw data into temp tables in ARTS format to prepare for XML Payload.
    PRINT 'Retrieving Sales Transaction Header...';   

	
	--get trantotal
    SELECT TOP (1)
		t.TranDate
        , t.TranNumber
        , CASE t.OperatMode
            WHEN 1
                THEN 'true'
            ELSE 'false'
          END AS 'TrainingModeFlag'
        , t.AccountNo AS 'CustomerID'
        , t.SubAccount AS 'SubKey'
        , t.StoreNumber AS 'RetailStoreID'
        , t.RegisterID AS 'WorkstationID'
        , t.RegisterID AS 'TillID'
        , t.SoldToAdd1 AS 'Address'
        , --may be calculate field
        t.SoldToAdd2
        , t.SoldToCity AS 'City'
        , t.SoldToState AS 'State'
        , t.SoldToZip AS 'PostalCode'
        , 'USA' AS 'CountryCode'
        , --calculate field
        t.SoldBusName AS 'Name'
        , t.ShipToAdd1
        , t.ShipToAdd2
        , --needed for neighborhood tag in Delivery method
        t.ShipToCity
        , t.ShipToState
        , t.ShipToZip
        , t.ShipBusName
        ,      
        '' AS 'CustomerType'
        ,        
        'true' AS 'BusinessCustomerFlag'
        , t.PhoneNumber AS 'Telephone'
        , t.TranNumber AS 'SequenceNumber'
        , CONVERT(DATETIME, t.TranDate, 0) + CONVERT(DATETIME, t.StartTime, 0) AS 'BeginDateTime'
        , CONVERT(DATETIME, t.TranDate, 0) + CONVERT(DATETIME, t.EndTime, 0) AS 'EndDateTime'
        , CONVERT(DATE, t.TranDate, 0) AS 'BusinessDayDate'
        , CONVERT(DATE, t.SystemDate, 0) AS 'CreateDate'
        , t.EmployeeNo AS 'OperatorID'
        , t.InvLines AS 'TransactionCount'
        , t.TxnTotal AS 'TranTotal'
        , t.TaxAmount AS 'TaxAmount'
		, t.TypeOfSale AS 'TranTotalTypeOfSale'
		, t.InvoiceNo
		, t.PostVoidTran
		, t.CostOfGoods ---------------------------------------------------------------------------------------------------------------------------------------------------------
		, t.LoyaltyCard ---------------------------------------------------------------------------------------------------------------------------------------------------------
		, t.RecType ---------------------------------------------------------------------------------------------------------------------------------------------------------
		, t.ReferenceNo__3 ---------------------------------------------------------------------------------------------------------------------------------------------------------
		, t.DocNumber AS 'TranTotalDocNumber'
		, t.RegisterID AS 'TranTotalRegisterID'
		, t.ShipMidInit AS 'TranTotalShipMidInit'
		, t.RecType AS 'TranTotalRecType'
		--, ISNULL(NULLIF(LTRIM(RTRIM(t.ShipFirstName + ' ' + ShipLastName)),''),' ') AS 'NameDP'
		, t.SystemDate AS 'TranTotalSystemDate'
		, t.TranNumber as 'TranTotalTranNumberTest'
		, t.TranDate as 'TranTotalTranDateTest'
		, t.RegisterID as 'TranTotalRegisterIDTest'
		, t.AccountNo AS 'TranTotalAccountNoAccountNumber'
    INTO #ARTSSaleDemand
    FROM 
		dbo.TranTotal t 
	WHERE t.TranNumber = @TranNumber
	AND t.TranDate = @TranDate
	AND t.RegisterID = @RegisterID
				
    --Create Sales Transaction Detail
    SELECT 
		d.TranDate
        , d.TranNumber
        , d.RegisterID
        , d.PickTake
        , d.SeqNo AS 'SequenceNumber'
        , 0 AS 'PriceModSeqNum'
        , d.AutoSequence AS 'RecID'
        , d.SKU AS 'SKU'
        , d.PromoNumb AS 'PromoNumb'
        , d.PriceCode AS 'PriceCode'
        , d.PriceAllow AS 'PriceAllow'
        , d.CouponNumber AS 'CouponNumber'
        , d.SecCouponNo AS 'SecCouponNo'
        , d.OrderDisc AS 'OrderDisc'
        , d.RetailPrice AS 'RetailPrice'
        , d.DocNumber AS 'SpecialOrderNumber'
        , ABS(d.LineDiscount + d.SpreadDisc + d.PriceAllow + d.OrderDisc) AS 'ExtendedDiscountAmount'
        , CAST(ABS(d.LineDiscount + d.SpreadDisc + d.PriceAllow + d.OrderDisc) / d.Quantity AS decimal(19,2)) AS 'UnitDiscountAmount'
        , CASE 
            WHEN d.Type = 1
                THEN d.MiscAcct
            ELSE d.SKU
            END AS 'ItemID'
        , d.SubClass AS 'MerchandiseHierarchy'
        , CONCAT(d.DeptNo,'-',d.ClassNo,'-',d.SubClass) AS 'MerchandiseHierarchy171'
        , d.UnitCost AS 'UnitCostPrice'
        , d.SKU AS 'AlternateItemID'
        , d.UnitPrice AS 'UnitListPrice'
        , CASE 
            WHEN d.Type = 0
                THEN i.RetailPrice
            WHEN d.Type = 1
                AND d.ExtendedAmt <> 0
                THEN d.ExtendedAmt
            ELSE 0
            END AS 'RegularSalesUnitPrice'
        , d.ExtendedAmt + d.LineDiscount + d.SpreadDisc + d.PriceAllow + d.OrderDisc AS 'ExtendedAmount'
        , ABS(d.ExtendedAmt + d.LineDiscount + d.SpreadDisc + d.PriceAllow + d.OrderDisc) AS 'AbsoluteExtendedAmount'
        , CASE 
            WHEN d.Type = 1
                THEN CAST(d.ExtendedAmt AS decimal(19,2))
            ELSE CAST(ABS(d.ExtendedAmt / d.Quantity) AS decimal(19,2))
            END AS 'ActualSalesUnitPrice'
        , CASE d.Type
            WHEN 1
                THEN 'Fee'
            ELSE 'Stock'
            END AS 'ItemType'
        , ISNULL(i.UOMConvert, 1) AS 'Units'
        , CASE WHEN d.UOM <> '' THEN d.UOM ELSE 'EA' END AS 'UnitOfMeasureCode'
        , CASE 
            WHEN d.Quantity <> 0
                THEN d.Quantity
            WHEN d.Quantity = 0
                THEN NULL
			ELSE NULL
          END AS 'PrimaryUnits'
		, CASE 
            WHEN d.Quantity <> 0
                THEN CAST( d.Quantity AS VARCHAR(13))
            WHEN d.Quantity = 0
                THEN '?'
			ELSE (
					SELECT TOP (1)
						CAST ((d.Quantity / i.UOMConvert) AS VARCHAR(13))
					FROM
						ItemReplen A
					WHERE
						A.SKU = d.SKU
						AND i.UniqItmCode = 'A'
				 )
          END AS 'PrimaryUnitsCustomerOrder'
        , d.Quantity AS 'Quantity'
        , CASE WHEN i.UOM <> '' THEN i.UOM ELSE 'EA' END AS 'PrimaryUnitOfMeasureCode'
		, d.PriceOvrType
		, d.TypeOfSale----------------------------------------------------------------------------------------------------------------------------------------------------------
		--, j.Quantity AS 'QuantityTDDOM'---------------------------------------------------------------------------------------------------------------------------------------------------------
		--, j.DocNumber AS 'DocNumberTDDOM'---------------------------------------------------------------------------------------------------------------------------------------------------------
		--, j.TypeOfSale AS 'TypeOfSaleTDDOM'---------------------------------------------------------------------------------------------------------------------------------------------------------
		--, j.PickTake AS 'PickTakeTDDOM'---------------------------------------------------------------------------------------------------------------------------------------------------------
		, h.ReferenceNo__3	---------------------------------------------------------------------------------------------------------------------------------------------------------
		, h.TranTotalTypeOfSale---------------------------------------------------------------------------------------------------------------------------------------------------------
		, h.Telephone as 'TranTotalTelephone'
		, h.CustomerID as 'TranTotalAccountNo'
		, h.TranTotalDocNumber
		, h.TranTotalRegisterID
		, d.SerialNumber
		, h.TranTotalShipMidInit
		, d.[Type] as 'TranDetailType'
		, NULLIF(LTRIM(RTRIM(d.UOM)),'') AS UOM
		, NULLIF(LTRIM(RTRIM(i.UOMConvert)),'') AS UOMConvert
		--, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNumber
		, d.SeqNo AS RowNumber
		, d.SeqNo AS 'TranDetailSequenceNo'
		, d.CSAAreaNo
		, h.TranTotalRecType
		, d.ExtendedAmt
		, d.MiscAcct AS 'TranDetailMiscAcct'
		, h.TranTotalSystemDate
		, h.TranTotalTranNumberTest
		, h.TranTotalTranDateTest
		, h.TranTotalRegisterIDTest
		, d.DocNumber AS 'TranDetailDocNumberTest'
		, d.SeqNo AS 'TranDetailSequenceNoTest'
		, d.SKU AS 'TranDetailSKUTest'
		, d.TranDate AS 'TranDetailTranDateTest'
		, h.TranTotalAccountNoAccountNumber
		, CASE WHEN i.UniqItmCode = 'A' THEN 1 ELSE 0 END AS IsAnAlternativeItem
		, ir.AltRelatSKU AS 'PrimarySKU'
		, i.UOMConvert AS 'AlternativeUOMConvert'
		, i2.UOM AS 'PrimaryUOM'
		, d.SequenceNo
		, i.Description AS 'ItemDescription'
    INTO #ARTSSaleDemandLineItemDemand
    FROM 
		dbo.TranDetail d
		INNER JOIN #ARTSSaleDemand h        
			 ON d.TranDate = h.TranDate
			AND d.TranNumber = h.SequenceNumber
			AND d.RegisterID = h.TillID
		LEFT JOIN dbo.ItemPLU i
			ON d.SKU = i.SKU		
		LEFT JOIN dbo.ItemReplen ir
			ON ir.SKU = i.SKU
		LEFT JOIN dbo.ItemPlu i2
			ON i2.SKU = ir.AltRelatSKU


    SELECT 
		AutoSequence AS 'RecID'
        , ROW_NUMBER() OVER (
								PARTITION BY 
									TranDate
									, TranNumber
									, RegisterID 
								ORDER BY 
									TranNumber
        ) AS SequenceNumber
    INTO #Temp1Demand
    FROM dbo.TranDetail d1;


	SET @OrderNumber = (SELECT TOP 1 TranTotalDocNumber FROM #ARTSSaleDemand)
	IF EXISTS(SELECT 1 FROM OrderEcom WHERE OrderNo = @OrderNumber)
	BEGIN
		SET @EcommOrderNumber = (SELECT TOP 1 DocNumber FROM OrderEcom WHERE OrderNo = @OrderNumber)
		SET @EsOrdenDeEcommerce = 1
	END
	ELSE	
		SET @EsOrdenDeEcommerce = 0

	IF(@OrderNumber > 0) AND EXISTS(SELECT 1 FROM OrderHeader WHERE OrderType = 'R' AND ORDERNUMBER = @OrderNumber)
	BEGIN
		SET @IsDevolution = 1
		SELECT top 1 @OrderDevolution = d.docnumber 
		FROM CUSTRETURN c 
		INNER JOIN OrderDetail od ON c.OrderNo = od.OrderNumber
		INNER JOIN TranDetail d 
			ON  c.SaleRegID = d.RegisterID
			AND c.SaleDate = d.Trandate
			AND c.saleTrannum = d.TranNumber
			AND d.DocNumber <> 0
			AND od.SKU = d.SKU
		WHERE ORDERNO = @OrderNumber
	END
    CREATE TABLE #ARTSRetailPriceModifierDemand  
	( 
		ID int IDENTITY(1,1) NOT NULL,
		RecID int ,
		PromotionId int,
		SequenceNumber int,
		MethodCode VARCHAR(50),
		Amount DECIMAL(7,2),
		[Action] VARCHAR(20),
		NewPrice DECIMAL(7,2),
		PreviousPrice DECIMAL(7,2),
		ReasonCode VARCHAR(1),
		PromoCondition VARCHAR(1)
	)
    --Create RetailPriceModifier
    --INSERT INTO #ARTSRetailPriceModifierDemand
    INSERT INTO #ARTSRetailPriceModifierDemand (
	RecID,
	PromotionId,
	SequenceNumber,
	MethodCode,
	Amount,
	[Action],
	NewPrice,
	PreviousPrice,
	ReasonCode,
	PromoCondition
    )
    SELECT  
		d.RecID AS 'RecID'
        , CASE 
			WHEN EXISTS(
						SELECT TOP (1) 
							PromoID 
						FROM 
							dbo.apthdr 
						WHERE 
							apthdr.AdNumber = d.PromoNumb 
							AND apthdr.PromoID <> ''
						ORDER BY
							PromoID ASC
					   ) THEN 
							(
								SELECT TOP (1)
									PromoID 
								FROM 
									dbo.apthdr
								WHERE 
									apthdr.AdNumber = d.PromoNumb 
									AND apthdr.PromoID <> ''
								ORDER BY
									PromoID ASC
							)
			ELSE 
				(
					SELECT TOP (1)
						AdNumber 
					FROM 
						dbo.apthdr 
					WHERE 
						apthdr.AdNumber = d.PromoNumb
					ORDER BY
						AdNumber ASC
				)
	       END AS 'PromotionID'
		, d.SequenceNumber + 1 AS 'SequenceNumber'
        , 'PriceOverride'AS 'MethodCode'
        , ABS(d.RetailPrice - d.UnitListPrice) AS 'Amount'
        , CASE 
			WHEN d.RetailPrice - d.UnitListPrice = 0 THEN 'Replace'
			WHEN d.RetailPrice - d.UnitListPrice > 0 THEN 'Subtract'
			ELSE 'Add' 
		  END AS 'Action'
        , d.UnitListPrice AS 'NewPrice'
        , d.RetailPrice AS 'PreviousPrice'
        , '1' AS 'ReasonCode'
		, '1' AS 'PromoCondition' 
    FROM 
		#ARTSSaleDemandLineItemDemand d
    WHERE 
		(d.PriceCode <> '')
    
	UNION ALL

    SELECT 
		d.RecID AS 'RecID'
        , CASE 
			WHEN EXISTS(
						SELECT TOP (1)
							PromoID 
						FROM 
							dbo.apthdr 
						WHERE 
							apthdr.AdNumber = d.PromoNumb 
							AND apthdr.PromoID <> ''
						ORDER BY
							PromoID ASC
					   ) THEN 
							(
								SELECT TOP (1)
									PromoID 
								FROM 
									dbo.apthdr 
								WHERE 
									apthdr.AdNumber = d.PromoNumb 
									AND apthdr.PromoID <> ''
								ORDER BY
									PromoID ASC
							)
			ELSE 
				(
					SELECT TOP (1)
						AdNumber 
					FROM 
						dbo.apthdr 
					WHERE 
						apthdr.AdNumber = d.PromoNumb
					ORDER BY
						AdNumber ASC
				)
	      END AS 'PromotionID'
		, d.SequenceNumber + 1 AS 'SequenceNumber'
        , 'Promotion' AS 'MethodCode'
        , ABS(d.RetailPrice - d.UnitListPrice) AS 'Amount'
        , CASE 
			WHEN d.RetailPrice - d.UnitListPrice = 0 THEN 'Replace'
			WHEN d.RetailPrice - d.UnitListPrice > 0 THEN 'Add'
			ELSE 'Subtract' 
		  END AS 'Action'
        , d.UnitListPrice AS 'NewPrice'
        , d.RetailPrice AS 'PreviousPrice'
        , '1' AS 'ReasonCode'
		, '2' AS 'PromoCondition' 
    FROM 
		#ARTSSaleDemandLineItemDemand d
    WHERE 
		(d.PromoNumb <> 0)

    UNION ALL 
    
	SELECT 
		d.RecID AS 'RecID'
        , CASE 
			WHEN EXISTS(
						SELECT TOP (1)
							PromoID 
						FROM 
							dbo.apthdr 
						WHERE 
							apthdr.AdNumber = d.PromoNumb 
							AND apthdr.PromoID <> ''
						ORDER BY
							PromoID ASC
					   ) THEN 
							(
								SELECT TOP (1)
									PromoID 
								FROM 
									dbo.apthdr 
								WHERE 
									apthdr.AdNumber = d.PromoNumb 
									AND apthdr.PromoID <> ''
								ORDER BY
									PromoID ASC
							)
			ELSE 
				(
					SELECT TOP (1) 
						AdNumber 
					FROM 
						dbo.apthdr 
					WHERE 
						apthdr.AdNumber = d.PromoNumb
					ORDER BY
						AdNumber ASC
				)
	      END AS 'PromotionID'
		, d.SequenceNumber + 1 AS 'SequenceNumber'
        , 'Promotion'AS 'MethodCode'
        , d.PriceAllow AS 'Amount'
        , CASE 
			WHEN d.PriceAllow > 0 THEN 'Add'
			ELSE 'Subtract'
	      END AS 'Action'
        , CASE 
			WHEN d.UnitListPrice <> 0 THEN d.UnitListPrice + d.PriceAllow 
			ELSE 0 
		  END AS 'NewPrice'
        , d.RetailPrice AS 'PreviousPrice'
        , '1' AS 'ReasonCode'
		, '3' AS 'PromoCondition'
	FROM 
		#ARTSSaleDemandLineItemDemand d
	WHERE 
		(d.PriceAllow <> 0)

     UNION ALL
    
	SELECT 
		d.RecID AS 'RecID'
        , CASE 
			WHEN EXISTS(
						SELECT TOP (1) 
							PromoID 
						FROM 
							dbo.apthdr 
						WHERE 
							apthdr.AdNumber = d.PromoNumb 
							AND apthdr.PromoID <> ''
						ORDER BY
							PromoID ASC
					   ) THEN 
							(
								SELECT TOP (1) 
									PromoID 
								FROM 
									dbo.apthdr 
								WHERE 
									apthdr.AdNumber = d.PromoNumb 
									AND apthdr.PromoID <> ''
								ORDER BY
									PromoID ASC
							)
			ELSE 
				(
					SELECT TOP (1) 
						AdNumber 
					FROM 
						dbo.apthdr 
					WHERE 
						apthdr.AdNumber = d.PromoNumb
					ORDER BY
						AdNumber ASC
				)
	      END AS 'PromotionID'
		, d.SequenceNumber + 1 AS 'SequenceNumber'
        , CASE 
            WHEN d.PriceCode <> '' THEN 'PriceOverride'
            ELSE 'Promotion'
          END AS 'MethodCode'
        , CASE 
			WHEN EXISTS(
						SELECT TOP (1) 
							PromoID 
						FROM 
							dbo.PromoHdr ph 
						WHERE 
							ph.PromoID = d.CouponNumber 
							AND ph.PromoType = 8
						ORDER BY
							PromoID ASC
					   ) THEN 0
			WHEN EXISTS(
						SELECT TOP (1) 
							PromoID 
						FROM 
							dbo.PromoHdr ph 
						WHERE 
							ph.PromoID = d.CouponNumber
						ORDER BY
							PromoID ASC
						) THEN  d.OrderDisc
			ELSE 0
		  END   AS 'Amount'
        , CASE 
			WHEN EXISTS(
						SELECT TOP (1)
							PromoID 
						FROM 
							dbo.PromoHdr ph 
						WHERE 
							ph.PromoID = d.CouponNumber 
							AND ph.PromoType = 8
						ORDER BY
							PromoID ASC
					   ) THEN 'Replace'
			WHEN EXISTS(
						SELECT TOP (1)
							PromoID 
						FROM 
							dbo.PromoHdr ph 
						WHERE 
							ph.PromoID = d.CouponNumber
						ORDER BY
							PromoID ASC
					   ) 
					   AND d.OrderDisc > 0 THEN 'Add'
			WHEN EXISTS(
						SELECT TOP (1) 
							PromoID 
						FROM 
							dbo.PromoHdr ph 
						WHERE 
							ph.PromoID = d.CouponNumber
						ORDER BY
							PromoID ASC
						) 
						AND d.OrderDisc < 0 THEN 'Subtract'
			ELSE 'Replace'
		  END AS 'Action'
        , CASE 
			WHEN EXISTS(
						SELECT TOP (1) 
							PromoID 
						FROM 
							dbo.PromoHdr ph 
						WHERE 
							ph.PromoID = d.CouponNumber 
							AND ph.PromoType = 8
						ORDER BY
							PromoID ASC
					   ) THEN 0
			WHEN EXISTS(
						SELECT TOP (1) 
							PromoID 
						FROM 
							dbo.PromoHdr ph 
						WHERE 
							ph.PromoID = d.CouponNumber
						ORDER BY
							PromoID ASC
					   ) THEN  d.RetailPrice + d.OrderDisc
			ELSE 0
		  END  AS 'NewPrice'
        , d.RetailPrice AS 'PreviousPrice'
        , '1' AS 'ReasonCode'
		, '4' AS 'PromoCondition'
	FROM 
		#ARTSSaleDemandLineItemDemand d
	WHERE 
		(d.CouponNumber <> '')

     UNION ALL
    
	SELECT 
		d.RecID AS 'RecID'
        , CASE 
			WHEN EXISTS(
						SELECT TOP (1)
							PromoID 
						FROM 
							dbo.apthdr 
						WHERE 
							apthdr.AdNumber = d.PromoNumb 
							AND apthdr.PromoID <> ''
						ORDER BY
							PromoID ASC
					   ) THEN 
							(
								SELECT TOP (1)
									PromoID 
								FROM 
									dbo.apthdr 
								WHERE 
									apthdr.AdNumber = d.PromoNumb 
									AND apthdr.PromoID <> ''
								ORDER BY
									PromoID ASC
							)
			ELSE 
				(
					SELECT TOP (1)
						AdNumber 
					FROM 
						dbo.apthdr 
					WHERE 
						apthdr.AdNumber = d.PromoNumb
					ORDER BY
						AdNumber ASC
				)
	      END AS 'PromotionID'
		, d.SequenceNumber + 1 AS 'SequenceNumber'
        , CASE 
            WHEN d.PriceCode <> '' THEN 'PriceOverride'
            ELSE 'Promotion'
          END AS 'MethodCode'
        , ABS(d.RetailPrice - d.UnitListPrice) AS 'Amount'
        , CASE 
			WHEN d.RetailPrice - d.UnitListPrice = 0 THEN 'Replace'
			WHEN d.RetailPrice - d.UnitListPrice > 0 THEN 'Add'
			ELSE 'Subtract' 
		  END AS 'Action'
        , d.RetailPrice AS 'NewPrice'
        , d.RetailPrice AS 'PreviousPrice'
        , '1' AS 'ReasonCode'
	    --INTO #ARTSRetailPriceModifierDemand
		, '5' AS 'PromoCondition'
	FROM 
		#ARTSSaleDemandLineItemDemand d
	WHERE 
		(d.SecCouponNo <> '')
	

    --DECLARE @SaleType AS VARCHAR(10),
    DECLARE 
		--@PriceModifierItemCnt AS INT
        @SaleItemsCnt AS INT
        , @TaxItemCnt AS INT
        --, @TenderItemCnt AS INT;

    SELECT SequenceNumber
        , PriceModSeqNum
    FROM #ARTSSaleDemandLineItemDemand;
   
    SET @SaleItemsCnt = (
            SELECT COUNT(A.ItemID)
            FROM #ARTSSaleDemandLineItemDemand A
            );
    SET @TaxItemCnt = (
            SELECT COUNT(t.TranNumber)
            FROM 
				dbo.TranTax t
				INNER JOIN #ARTSSaleDemand s
					ON t.TranNumber = s.TranNumber
						AND t.TranDate = s.TranDate
						AND t.RegisterID = s.WorkstationID
            );
    SET @time = CONVERT(VARCHAR(14), CURRENT_TIMESTAMP - @START_TIME, 14);

   
    SELECT 
		A.TranNumber
    FROM 
		#ARTSSaleDemand A;

    SELECT 
		A.TranNumber
    FROM 
		#ARTSSaleDemandLineItemDemand A;

    SELECT 
		A.ID
    FROM 
		#ARTSRetailPriceModifierDemand A;

	-----------------------------------------------------------------------------------------------------
	-----------------------------------------------------------------------------------------------------
	DECLARE @SysConfigTemp TABLE(
		Info01 NVARCHAR(512) NOT NULL
	)

	INSERT INTO @SysConfigTemp
	SELECT
		A.ColumnResult
	FROM
		BreakStringIntoRows(
								(
								SELECT
									LEFT(
										STUFF(
												(
													SELECT
														CASE
															WHEN A.Info01 <> '' THEN
																',' + A.Info01 
															ELSE NULL
														END
													FROM
														dbo.SysConfig A WHERE
														A.Id = 'Exc_Cuen'
														AND A.[key] = '1'
													FOR XML PATH('')
												)
										,1,1,'')
									,512)
								)
							) A

	--optimizacion para JOIN con Customer
	DECLARE @CustomerTemp TABLE(
		CustTypeNo NVARCHAR(3) NOT NULL
	)

	INSERT INTO @CustomerTemp
	SELECT 
		A.CustTypeNo
	FROM
		dbo.Customer A
    INNER JOIN #ARTSSaleDemand B ON A.CustomerID = B.CustomerID /* 2017-02-25 JACG: Se agrega condición para obtener el cliente de la transacción */
	-----------------------------------------------------------------------------------------------------
	-----------------------------------------------------------------------------------------------------
	DECLARE @TipoDeCliente AS VARCHAR(10) = '';
	DECLARE @BusinessCustomerFlagValue AS VARCHAR(5) = '';
	DECLARE @ReferenceNo__3 AS VARCHAR(5) = '';

	SET @ReferenceNo__3 = (SELECT TOP 1 ReferenceNo__3 FROM #ARTSSaleDemand)
	IF EXISTS(
				SELECT TOP (1)
					A.CustomerID
				FROM
					dbo.Customer A
					INNER JOIN #ARTSSaleDemand B ON A.CustomerID = B.CustomerID
										  AND A.Type IN('D','C')
				ORDER BY
					A.CustomerID ASC
			 )
	
		SET @TipoDeCliente = 'PRO'
	ELSE
	BEGIN
		SET @TipoDeCliente = 'PISO'
	END


	IF EXISTS(
				SELECT TOP (1)
					A.Id
				FROM
					dbo.SysConfig A
				WHERE
					A.Id = 'Exc_Cuen'
					AND A.[key] = '1'
				ORDER BY
					A.Id ASC
			 )
	BEGIN
		IF EXISTS(
					SELECT TOP (1)
						A.CustTypeNo
					FROM
						@CustomerTemp A
						INNER JOIN @SysConfigTemp B ON A.CustTypeNo = B.Info01
					WHERE
						A.CustTypeNo IN (1,7,10,11,15,18,19,20,21)
					ORDER BY
						A.CustTypeNo ASC
				 )
		BEGIN
			SET @TipoDeCliente = 'PISO'
		END
	END

	SET @BusinessCustomerFlagValue = 
									CASE
										WHEN @TipoDeCliente = 'PRO' THEN 'true'
										ELSE 'false'
									END

	
	---------------------------------------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------------------------------------------------------------------------------------------------------------------------

     SET @XML = (
            SELECT 
				h.TrainingModeFlag AS '@TrainingModeFlag'
                , h.RetailStoreID
                , h.WorkstationID
                , h.TillID
				, h.SequenceNumber
                , CONVERT(VARCHAR(30), h.BeginDateTime, 126) + '-05:00' AS 'BeginDateTime'				
				,(----------------------------------------------------------------------------------------------------------------------------------------------------
					SELECT
						DATEPART(DW,h.TranDate)
					FOR XML PATH('BusinessDay')
					, ELEMENTS
					, TYPE
				)
				, CONVERT(VARCHAR(30), h.EndDateTime, 126) + '-05:00' AS 'EndDateTime'			
				, CASE 
					WHEN ISDATE(LTRIM(RTRIM(h.BusinessDayDate))) = 1 THEN
						CAST(h.BusinessDayDate AS VARCHAR(20)) + '-05:00'
					ELSE
						NULL
				  END	AS 'BusinessDayDate'
                , (-------------------------------------------------------------------------------------------------------------------------------------------------
					SELECT
						(
							SELECT
								(A.FirstName + ' ' +A.MiddleName + ' ' + A.LastName) as '@OperatorName'
								,h.OperatorID AS "text()"
							FOR XML PATH('OperatorID')
							, ELEMENTS
							, TYPE
						)
					FROM
						dbo.Employee A
					WHERE
						A.EmployeeNo = h.OperatorID
					FOR XML PATH('')
					, ELEMENTS
					, TYPE
				
				)----------------------------------------------------------------------------------------------------------------------------------------------------
				-----Verificar que algun detalle de la partida cumpla con las condiciones de cambios a USP...------------------------------------------------------------------------------------------------------
                , (
                    SELECT
						--h1.RecType AS '@TransactionStatus' 
						(
							SELECT
								CASE
									WHEN h1.TranTotalTypeOfSale = 88 THEN
										'PostVoided'
								END
						) AS '@TransactionStatus' 
						, h1.TransactionCount
						
						------------------------------------------------------------------------------------------------------------------------------------------
                        , (
                            SELECT IIF(@EsOrdenDeEcommerce = 1, LineItem.SequenceNo, LineItem.TranDetailSequenceNo) AS 'SequenceNumber'
							-----------------------------------------------------------------------------------------------------------------------------------------
							,(							
							SELECT
								CAST(
									CASE
										WHEN (	
												(LineItem.TypeOfSale NOT IN(11, 12, 13, 14, 15, 16, 21, 24, 25, 26, 36, 38, 43, 46, 48) OR--- Remove PostVoid Validation, TypeOfSale 88
												 @ReferenceNo__3 <> '')
												OR (LineItem.TypeOfSale IN(21) AND LineItem.QUANTITY > 0) 
											 ) 
											 AND LineItem.SKU > 0
											 AND (LineItem.PickTake = 'P' OR LineItem.PickTake = 'D')
											 AND LineItem.SpecialOrderNumber <> 0 THEN 
												CASE
													WHEN @ReferenceNo__3 <> '' AND LineItem.PickTake = 'P' THEN
															( -- <CustomerOrderForDelivery> Devolucion
																SELECT
																	@BusinessCustomerFlagValue
																	AS '@BusinessSaleFlag'
																	, CASE WHEN LineItem.TypeOfSale = 88 OR LineItem.Quantity   < 0 THEN 'Return' ELSE 'Active' END AS '@ItemStatus'
																	, CASE
																		WHEN LineItem.TranDetailType = 1 THEN 'Fee'
																		ELSE 'Stock'
																	 END as '@ItemType'
																	, 'Completed' AS '@OrderStatus'
																	, CASE WHEN LineItem.TypeOfSale = 88 OR LineItem.Quantity   < 0 THEN 'ReturnOrExchange' ELSE 'Invoice' END AS '@OrderType'
																	,(
																		SELECT
																			CASE WHEN LineItem.IsAnAlternativeItem = 0 
																			THEN LineItem.ItemID
																			ELSE LineItem.PrimarySKU
																			END AS 'text()'
																		FOR XML PATH('ItemID')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			CASE 
																				WHEN ISDATE(LTRIM(RTRIM(LineItem.TranTotalSystemDate))) = 1 THEN
																					CAST( CONVERT( DATE, LineItem.TranTotalSystemDate, 111) AS VARCHAR(25)) + 'T00:00:00-05:00'
																				ELSE
																					NULL
																			END	AS '@CreateDate'
																			, IIF(@EsOrdenDeEcommerce = 1, @EcommOrderNumber, IIF(@IsDevolution = 1, @OrderDevolution,LineItem.SpecialOrderNumber)) AS 'text()'
																		FOR XML PATH('SpecialOrderNumber')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																				LineItem.RowNumber AS 'text()'																			
																		FOR XML PATH('SpecialOrderSequenceNumber')
																		, ELEMENTS
																		, TYPE
																	)
																	,(
																		SELECT
																			--'VALOR DE ITEMID' AS 'text()'
																			LineItem.MerchandiseHierarchy AS 'text()'
																		FOR XML PATH('MerchandiseHierarchy')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			CASE WHEN LineItem.IsAnAlternativeItem = 1
																			THEN (SELECT LineItem.SKU AS 'text()' FOR XML PATH('AlternateItemID')
																			, ELEMENTS
																			,TYPE)
																			END																			
																	)
																	--ELEMENTO DESCRIPTION
																	,(
																		SELECT
																			ISNULL(
																					(
																						SELECT TOP (1)
																							NULLIF(LTRIM(RTRIM(B.SKUDesc)),'')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC
																					)
																			,	(
																					SELECT
																						CASE
																							WHEN LineItem.TranDetailType = 0 THEN 
																																	(
																																		SELECT TOP (1)
																																			NULLIF(LTRIM(RTRIM(A.SpanishDesc)),'')
																																		FROM
																																			ItemReplen A
																																		WHERE
																																			A.SKU = LineItem.SKU
																																		ORDER BY
																																			A.SpanishDesc ASC
																																	)
																							ELSE
																								(
																									SELECT TOP(1)
																										NULLIF(LTRIM(RTRIM(A.[Description])),'')
																									FROM
																										Fee A
																									WHERE
																										A.FeeCode = LineItem.TranDetailMiscAcct
																									ORDER BY
																										A.[Description] ASC
																								)
																						END
																				)
																			) AS 'text()'
																		FOR XML PATH('Description')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.UnitCostPrice AS 'text()'
																		FOR XML PATH ('UnitCostPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.UnitListPrice AS 'text()'
																		FOR XML PATH ('UnitListPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			ABS(LineItem.ExtendedAmt) AS 'text()'
																		FOR XML PATH ('RegularSalesUnitPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			ABS(LineItem.ExtendedAmt) AS 'text()'
																		FOR XML PATH ('ActualSalesUnitPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			--'VALOR DE ITEMID' AS 'text()'
																			LineItem.AbsoluteExtendedAmount AS 'text()'
																		FOR XML PATH('ExtendedAmount')
																		, ELEMENTS
																		,TYPE
																	)
																	
																	,(
																		SELECT
																			LineItem.UnitDiscountAmount AS 'text()'
																		FOR XML PATH ('UnitDiscountAmount')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.ExtendedDiscountAmount AS 'text()'
																		FOR XML PATH ('ExtendedDiscountAmount')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																		CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		  ELSE LineItem.PrimaryUOM END AS '@PrimaryUnitOfMeasureCode'
																		
																		, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN ABS(CAST(LineItem.PrimaryUnitsCustomerOrder as Decimal (10,4)))
																			  ELSE 
																				 CASE WHEN LineItem.AlternativeUOMConvert IS NOT NULL AND LineItem.AlternativeUOMConvert<>0 
																				 THEN  ABS(CAST((LineItem.Quantity /LineItem.AlternativeUOMConvert) as decimal (10,4)))
																				 ELSE 0--''
																				 END 
																			  END AS '@PrimaryUnits'
																		, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOMConvert 
																		  ELSE 
																				CASE WHEN LineItem.AlternativeUOMConvert IS NOT NULL AND LineItem.AlternativeUOMConvert<>0 
																				THEN  CAST(CAST(1 AS DECIMAL(8,4))/LineItem.AlternativeUOMConvert AS DECIMAL(25,10))
																				ELSE 0--''
																				END 
																		  END AS '@Units'
																		, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		  ELSE LineItem.UOM
																		  END
																		  AS '@UnitOfMeasureCode'
																		, ABS(LineItem.Quantity) AS 'text()'
																	   FOR XML PATH('Quantity')
																	   ,ELEMENTS
																	   ,TYPE
																	),
																	
																	(
																		SELECT CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		  ELSE LineItem.UOM
																		  END
																		  AS '@UnitOfMeasureCode',
																		  0.00
																		  AS '@Units',
																			(
																				SELECT
																					(
																						SELECT
																								(
																									SELECT
																										ISNULL(
																												(
																													SELECT TOP (1)
																														ISNULL(
																																(
																																	SELECT TOP (1)
																																		(
																																			SELECT dbo.fnIF194_EvaluarCantidadPendiente(E.DocNumber,E.SequenceNo,E.SKU, @EsOrdenDeEcommerce)
																																		) AS FunctionResult
																																	FROM
																																		dbo.OrderDetail B																																		
																																		INNER JOIN dbo.OrderHeader C ON C.OrderNumber = B.OrderNumber																																		
																																		INNER JOIN dbo.TranTotalDOM D ON D.StoreNumber = C.OrigStore
																																									AND D.TranDate = C.OrigDate
																																									AND D.RegisterID = C.OrigRegID
																																									AND D.TranNumber = C.OrigTranNo																																	
																																		INNER JOIN dbo.TranDetailDOM E ON E.DocNumber = D.DocNumber
																																									AND E.SKU = B.SKU
																																									AND E.SequenceNo = (CASE
																																														WHEN B.AtForQty <> 0 THEN B.AtForQty
																																														ELSE B.OrdSeqNumber
																																														END)

																																	WHERE
																																		--
																																		B.OrderNumber = LineItem.SpecialOrderNumber  
																																		AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																																		AND B.SKU = LineItem.SKU

																																		AND(
																																				LineItem.TranTotalTypeOfSale = 88
																																				OR LineItem.TranTotalTypeOfSale = 21
																																			)
																																		AND (LineItem.ReferenceNo__3 <> '')																																		
																																		AND C.OrigStore <> 0
																																		AND C.OrigRegID <> 0
																																		AND C.OrigTranNo <> 0
																																	ORDER BY
																																		E.DocNumber ASC
																																)
																														,0.00) AS QResult
																													FROM
																														dbo.OrderDetail B
																													WHERE
																														--Posteriormente, buscar el primer registro de OrderDetail en donde:
																														B.OrderNumber = LineItem.SpecialOrderNumber
																														AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																														AND B.SKU = LineItem.SKU

																														AND
																															(
																																LineItem.TranTotalTypeOfSale = 88
																																OR LineItem.TranTotalTypeOfSale = 21
																															)
																														AND LineItem.ReferenceNo__3 <> ''
																													ORDER BY
																														QResult ASC
																												)
																											, 0.00) --asignar el valor de TranDetail.PickTake a ORDERQUANTITY

																									WHERE
																										(
																										LineItem.Quantity < 0
																										AND LineItem.SpecialOrderNumber <> 0
																										AND ( LineItem.TypeOfSale > 10
																											AND LineItem.TypeOfSale < 20 )
																										)
																										OR( 
																											LineItem.Quantity < 0
																											AND LineItem.SpecialOrderNumber <> 0
																											AND ( LineItem.TypeOfSale = 88
																												OR LineItem.TypeOfSale = 21 )
																										)
																								)
																					)
																				WHERE
																					EXISTS
																					(
																						SELECT TOP (1)
																							A.TypeOfSale
																						FROM
																							dbo.TranDetailDOM A
																						WHERE
																							----START of Match the LineItem
																							A.TranNumber = LineItem.TranNumber
																							AND A.TranDate = LineItem.TranDate
																							AND A.RegisterID = LineItem.RegisterID
																							AND A.SKU = LineItem.SKU
																							----END of Match the LineItem
																							AND (A.Quantity <> 0)
																							AND (A.DocNumber <> 0)
																							AND (A.TypeOfSale <= 10
																								OR A.TypeOfSale >= 20
																								AND A.TypeOfSale <> 88)
																							AND (A.PickTake = 'D'
																								OR A.PickTake = 'P')
																							OR (
																								(A.TypeOfSale = 88
																								AND LineItem.ReferenceNo__3 <> '')
																								OR
																								(A.TypeOfSale = 21
																								AND LineItem.ReferenceNo__3 <> '')
																								)
																							AND LineItem.RegisterID <> 81
																							AND (LineItem.PickTake = 'D'
																								OR LineItem.PickTake = 'P')
																						ORDER BY
																							A.TranNumber ASC
																					)

																			)
																		FOR XML PATH('OrderQuantity')
																		, ELEMENTS
																		, TYPE
																	),(
																		SELECT
																			'false' AS '@PackDownFlag'
																			,ISNULL(
																				(
																					SELECT TOP (1)
																						A.ZoneId + ' ' + A.Bays
																					FROM
																						LocationSKU A
																					WHERE 
																						A.SKU = LineItem.SKU
																				)
																			, '0')
																		FOR XML PATH ('SellingLocation')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			
																			(
																				SELECT TOP (1)
																					(
																						SELECT
																							ISNULL(
																									(
																										SELECT TOP (1)
																											C.FirstName + ' ' + C.LastName
																										FROM
																											Employee C
																										WHERE
																											C.EmployeeNo = O.EmployeeNo
																									)
																							,'?')
																					) AS '@OperatorName'
																					, O.EmployeeNo AS 'text()'
																				FROM
																					OrderHeader O
																				WHERE
																					O.OrderNumber = LineItem.SpecialOrderNumber
																				ORDER BY O.EmployeeNo ASC
																				FOR XML PATH ('AssociateID')
																				,ELEMENTS
																				,TYPE
																			)
																		FOR XML PATH ('Associate')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			@BusinessCustomerFlagValue AS '@BusinessCustomerFlag'
																			,(
																				SELECT TOP (1)
																					CASE
																						WHEN A.[Type] = 'D' THEN 'A/R'
																						ELSE ' '
																					END
																				FROM
																					Customer A
																				WHERE
																					A.CustomerID = LineItem.TranTotalAccountNo
																					--
																				ORDER BY
																					A.CustomerID ASC
																			) AS '@CustomerType'
																			,(
																				SELECT
																					CASE	
																						WHEN LineItem.TranTotalRegisterID = 80 THEN '11110000004'
																						ELSE LineItem.TranTotalAccountNo
																					END
																				FOR XML PATH('CustomerID')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					ISNULL(NULLIF(LTRIM(RTRIM(
																												(
																													SELECT TOP (1)
																														A.SoldBusName
																													FROM
																														OrderHeader A
																													WHERE
																														A.OrderNumber = LineItem.SpecialOrderNumber
																												)
																											 )
																										)
																								,'')
																							,CASE WHEN h.Name != '' and h.Name is not null THEN h.Name ELSE '*SIN NOMBRE*' END)
																					AS 'text()'
																				FOR XML PATH('Name')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					CASE
																						WHEN  NULLIF(LTRIM(RTRIM(LineItem.TranTotalTelephone)),'') IS NOT NULL THEN
																							(
																								SELECT
																									'Home' AS '@TypeCode'
																									,(
																										SELECT
																											LineItem.TranTotalTelephone as "text()" 
																										FOR XML PATH('FullTelephoneNumber')
																										, ELEMENTS
																										, TYPE
																									)
																								FOR XML PATH('Telephone')
																								, ELEMENTS
																								, TYPE
																							)
																					END
																			)
																			,(
																			--Verificar que el hijo retorne un resultado, de lo contrario no mostrar padre e Hijo
																				SELECT
																					CASE
																						WHEN NULLIF(LTRIM(RTRIM(
																							(
																								SELECT TOP (1)
																									A.AltPhoneNumb as "text()"
																								FROM
																									dbo.OrderHeader A
																									INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																								WHERE
																									B.OrderNumber = LineItem.SpecialOrderNumber
																									AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																									AND B.SKU = LineItem.SKU
																								ORDER BY
																									B.OrdSeqNumber ASC
																							) 
																							)),'') IS NOT NULL
																							THEN
																								(
																									SELECT
																										'Office' AS '@TypeCode'
																										,(
																											SELECT
																												(
																													SELECT TOP (1)
																														A.AltPhoneNumb as "text()"
																													FROM
																														dbo.OrderHeader A
																														INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																													WHERE
																														B.OrderNumber = LineItem.SpecialOrderNumber
																														AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																														AND B.SKU = LineItem.SKU
																													ORDER BY
																														B.OrdSeqNumber ASC
																												) 
																											FOR XML PATH('FullTelephoneNumber')
																											, ELEMENTS
																											, TYPE
																										)
																									FOR XML PATH('Telephone')
																									, ELEMENTS
																									, TYPE
																								)
																					END
																			)
																			,(
																				SELECT
																					(		
																						SELECT
																							ISNULL(NULLIF(LTRIM(RTRIM(
																														(
																															SELECT TOP (1)
																																A.ShipBusName
																															FROM
																																OrderHeader A
																															WHERE
																																A.OrderNumber = LineItem.SpecialOrderNumber
																														)
																													 )
																												)
																										,'')
																									,CASE WHEN h.ShipBusName != '' and h.ShipBusName is not null THEN h.ShipBusName ELSE '*SIN NOMBRE*' END)
																							AS 'text()'
																						FOR XML PATH('Name')																						
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																								ISNULL(NULLIF(LTRIM(RTRIM(
																															(
																																SELECT TOP (1)
																																	A.ReceiveName
																																FROM
																																	OrderHeader A
																																WHERE
																																	A.OrderNumber = LineItem.SpecialOrderNumber
																															)
																														 )
																													)
																											,'')
																										,CASE WHEN h.ShipBusName != '' and h.ShipBusName is not null THEN h.ShipBusName ELSE '*SIN NOMBRE*' END)
																								AS 'text()'
																						FOR XML PATH('AlternateName')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToAdd1
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToAdd1 END as "text()"
																						FOR XML PATH('AddressLine')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0 AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToCity
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToCity END as "text()"
																						FOR XML PATH('City')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0 AND h.TillID != 80  THEN 
																							(SELECT TOP (1)
																										E.ShipToAdd2
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToAdd2 END as "text()"
																						FOR XML PATH('Neighborhood')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0 AND h.TillID != 80  THEN 
																							(SELECT TOP (1)
																										E.ShipToState
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToState END as "text()" 
																						FOR XML PATH('State')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0 AND h.TillID != 80  THEN 
																							(SELECT TOP (1)
																										E.ShipToZip
																									FROM
																										OrderDetail D
																										INNER JOIN OrderHeader E ON D.OrderNumber = E.OrderNumber
																									WHERE
																										D.OrderNumber = LineItem.SpecialOrderNumber
																										AND D.OrdSeqNumber = LineItem.TranDetailSequenceNo
																										AND D.SKU = LineItem.SKU)
																							ELSE h.ShipToZip END as "text()" 
																						FOR XML PATH('PostalCode')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE
																								WHEN EXISTS
																								(
																									SELECT TOP (1)
																										CASE
																											WHEN LineItem.RegisterID = 80 THEN 
																												SUBSTRING(LineItem.TranTotalShipMidInit,1, (CHARINDEX('|', LineItem.TranTotalShipMidInit) - 1))
																											ELSE
																												--ISNULL(
																														(
																															SELECT TOP (1)
																																E.Latitude
																															FROM
																															    OrderHeader E
																															WHERE
																																E.OrderNumber = LineItem.SpecialOrderNumber
																															ORDER BY
																																E.Latitude ASC
																														)
																												--, ' ')
																										END
																								) THEN
																									(
																										SELECT
																											(
																												SELECT TOP (1)
																													CASE
																														WHEN LineItem.RegisterID = 80 THEN 
																															SUBSTRING(LineItem.TranTotalShipMidInit,1, (CHARINDEX('|', LineItem.TranTotalShipMidInit) - 1))
																														ELSE
																															--ISNULL(
																																	(
																																		SELECT TOP (1)
																																			E.Latitude
																																		FROM
																																			OrderHeader E
																																		WHERE
																																			E.OrderNumber = LineItem.SpecialOrderNumber
																																		ORDER BY
																																			E.Latitude ASC
																																	)
																															--, ' ')
																													END
																											)
																										FOR XML PATH('Latitude')
																										, ELEMENTS
																										, TYPE
																									)
																							END
																					)
																					,(
																						SELECT
																							(
																								SELECT
																									CASE
																										WHEN EXISTS
																											(
																												SELECT TOP (1)
																													CASE
																														WHEN LineItem.RegisterID = 80 THEN 
																															SUBSTRING(LineItem.TranTotalShipMidInit, CHARINDEX('|', LineItem.TranTotalShipMidInit) + 1, LEN(LineItem.TranTotalShipMidInit))
																														ELSE
																															--ISNULL(
																																	(
																																		SELECT TOP (1)
																																			E.[Length]
																																		FROM
																																			OrderHeader E
																																		WHERE
																																			E.OrderNumber = LineItem.SpecialOrderNumber
																																		ORDER BY
																																			E.Latitude ASC
																																	)
																															--, ' ')
																													END
																											) THEN
																												(
																													SELECT TOP (1)
																														CASE
																															WHEN LineItem.RegisterID = 80 THEN 
																																SUBSTRING(LineItem.TranTotalShipMidInit, CHARINDEX('|', LineItem.TranTotalShipMidInit) + 1, LEN(LineItem.TranTotalShipMidInit))
																															ELSE
																																--ISNULL(
																																		(
																																			SELECT TOP (1)
																																				E.[Length]
																																			FROM
																																				OrderHeader E
																																			WHERE
																																				E.OrderNumber = LineItem.SpecialOrderNumber
																																			ORDER BY
																																				E.Latitude ASC
																																		)
																																--, ' ')
																														END
																												)
																									END
																							)
																						FOR XML PATH('Longitude')
																						, ELEMENTS
																						, TYPE
																					)

																				FOR XML PATH('Address')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT CASE WHEN LineItem.RegisterId = 80 THEN
																					(
																						SELECT
																							CASE ISDATE( SUBSTRING(LineItem.SerialNumber, 1, 4) + '-' + SUBSTRING(LineItem.SerialNumber, 6, 2) + '-' + SUBSTRING(LineItem.SerialNumber, 9, 2))
																								WHEN 1 THEN	
																									SUBSTRING(LineItem.SerialNumber, 1, 4) + '-' + SUBSTRING(LineItem.SerialNumber, 6, 2) + '-' + SUBSTRING(LineItem.SerialNumber, 9, 2) + 'T00:00:00'
																								ELSE
																									CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')
																							END
																					)
																					ELSE
																					(
																						SELECT
																						ISNULL((
																						SELECT TOP (1)
																							CONCAT(ISNULL(B.DELDATE,CAST(GETDATE() AS DATE)), 'T00:00:00')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.SequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC)
																						,CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')	)
																					)
																					END
																				FOR XML PATH('PreferredDateTime')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					LineItem.PickTake as "text()"
																				FOR XML PATH('Method')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT
																							ISNULL(
																								SUBSTRING(
																											STUFF(
																													(
																														SELECT
																															', ' + A.VendorUPC
																														FROM
																															ItemXref A
																														WHERE
																															A.SKU = LineItem.SKU
																														FOR XML PATH('')
																													)
																											, 1,2,''),
																								1, 256)
																							,' ')
																					)
																				FOR XML PATH('Notes')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT
																							ISNULL(
																									(
																										SELECT TOP (1)
																											C.StoreDelivery
																										FROM
																											DomFulf C
																											INNER JOIN Store D ON C.StoreRem = D.StoreNumber
																										WHERE
																											C.OrderNo = LineItem.SpecialOrderNumber
																										ORDER BY
																											C.StoreRem ASC
																									)
																							, 
																								(
																									SELECT TOP (1)
																										A.StoreNumber
																									FROM
																										Store A
																									WHERE
																										A.StoreType = 'true'
																									ORDER BY
																										A.StoreNumber ASC
																								)
																							)
																					)
																				FOR XML PATH('CustomerOrderStoreID')
																				, ELEMENTS
																				, TYPE
																			)
																		FOR XML PATH('Pickup')
																		, ELEMENTS
																		, TYPE
																	)																
																	
																FOR XML PATH('CustomerOrderForPickup') -- FIN CustomerOrderForPickup Devolucion
															)
															
													WHEN @ReferenceNo__3 <> '' AND LineItem.PickTake = 'D' THEN
															(--CustomerOrderForDelivery Devolucion
																SELECT
																	@BusinessCustomerFlagValue
																	AS '@BusinessSaleFlag'
																	, CASE WHEN LineItem.TypeOfSale = 88 OR LineItem.Quantity   < 0 THEN 'Return' ELSE 'Active' END AS '@ItemStatus'
																	, CASE
																		WHEN LineItem.TranDetailType = 1 THEN 'Fee'
																		ELSE 'Stock'
																	 END as '@ItemType'
																	, 'Completed' AS '@OrderStatus'
																	, CASE WHEN LineItem.TypeOfSale = 88 OR LineItem.Quantity   < 0 THEN 'ReturnOrExchange' ELSE 'Invoice' END AS '@OrderType'
																	,(
																		SELECT
																			CASE WHEN LineItem.IsAnAlternativeItem = 0 
																			THEN LineItem.ItemID
																			ELSE LineItem.PrimarySKU
																			END AS 'text()'
																		FOR XML PATH('ItemID')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			CASE 
																				WHEN ISDATE(LTRIM(RTRIM(LineItem.TranTotalSystemDate))) = 1 THEN
																					CAST( CONVERT( DATE, LineItem.TranTotalSystemDate, 111) AS VARCHAR(25)) + 'T00:00:00-05:00'
																				ELSE
																					NULL
																			END	AS '@CreateDate'
																			, IIF(@EsOrdenDeEcommerce = 1, @EcommOrderNumber, IIF(@IsDevolution = 1, @OrderDevolution,LineItem.SpecialOrderNumber)) AS 'text()'
																		FOR XML PATH('SpecialOrderNumber')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																				LineItem.RowNumber AS 'text()'																			
																		FOR XML PATH('SpecialOrderSequenceNumber')
																		, ELEMENTS
																		, TYPE
																	)
																	,(
																		SELECT
																			--'VALOR DE ITEMID' AS 'text()'
																			LineItem.MerchandiseHierarchy AS 'text()'
																		FOR XML PATH('MerchandiseHierarchy')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
                                    SELECT
  																	CASE WHEN LineItem.IsAnAlternativeItem = 1
  																	THEN (SELECT ABS(LineItem.SKU) AS 'text()' FOR XML PATH('AlternateItemID')
  																	, ELEMENTS
  																	,TYPE)
  																	END																			
  																)
																	--ELEMENTO DESCRIPTION
																	,(
																		SELECT
																			ISNULL(
																					(
																						SELECT TOP (1)
																							NULLIF(LTRIM(RTRIM(B.SKUDesc)),'')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC
																					)
																			,	(
																					SELECT
																						CASE
																							WHEN LineItem.TranDetailType = 0 THEN 
																																	(
																																		SELECT TOP (1)
																																			NULLIF(LTRIM(RTRIM(A.SpanishDesc)),'')
																																		FROM
																																			ItemReplen A
																																		WHERE
																																			A.SKU = LineItem.SKU
																																		ORDER BY
																																			A.SpanishDesc ASC
																																	)
																							ELSE
																								(
																									SELECT TOP(1)
																										NULLIF(LTRIM(RTRIM(A.[Description])),'')
																									FROM
																										Fee A
																									WHERE
																										A.FeeCode = LineItem.TranDetailMiscAcct
																									ORDER BY
																										A.[Description] ASC
																								)
																						END
																				)
																			) AS 'text()'
																		FOR XML PATH('Description')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.UnitCostPrice AS 'text()'
																		FOR XML PATH ('UnitCostPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.UnitListPrice AS 'text()'
																		FOR XML PATH ('UnitListPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			ABS(LineItem.ExtendedAmt) AS 'text()'
																		FOR XML PATH ('RegularSalesUnitPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			ABS(LineItem.ExtendedAmt) AS 'text()'
																		FOR XML PATH ('ActualSalesUnitPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			--'VALOR DE ITEMID' AS 'text()'
																			LineItem.AbsoluteExtendedAmount AS 'text()'
																		FOR XML PATH('ExtendedAmount')
																		, ELEMENTS
																		,TYPE
																	)
																	
																	,(
																		SELECT
																			LineItem.UnitDiscountAmount AS 'text()'
																		FOR XML PATH ('UnitDiscountAmount')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.ExtendedDiscountAmount AS 'text()'
																		FOR XML PATH ('ExtendedDiscountAmount')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		  ELSE LineItem.PrimaryUOM END AS '@PrimaryUnitOfMeasureCode'
																			, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN ABS(CAST(LineItem.PrimaryUnitsCustomerOrder as Decimal (10,4)))
																			  ELSE 
																				 CASE WHEN LineItem.AlternativeUOMConvert IS NOT NULL AND LineItem.AlternativeUOMConvert<>0 
																				 THEN  ABS(CAST((LineItem.Quantity /LineItem.AlternativeUOMConvert) as decimal (10,4)))
																				 ELSE 0--''
																				 END 
																			  END AS '@PrimaryUnits'
																			, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOMConvert 
																			  ELSE 
																				 CASE WHEN LineItem.AlternativeUOMConvert IS NOT NULL AND LineItem.AlternativeUOMConvert<>0 
																				 THEN  CAST(CAST(1 AS DECIMAL(8,4))/LineItem.AlternativeUOMConvert AS DECIMAL(25,10))
																				 ELSE 0--''
																				 END 
																			  END AS '@Units'
																			, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																			  ELSE LineItem.UOM
																			  END AS '@UnitOfMeasureCode'
																			, ABS(LineItem.Quantity) AS 'text()'
																	   FOR XML PATH('Quantity')
																	   , ELEMENTS
																	   , TYPE
																	),
																	(
																		SELECT CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		  ELSE LineItem.UOM
																		  END
																		  AS '@UnitOfMeasureCode',
																		  0.00
																		  AS '@Units',
																			(
																				SELECT
																					(
																						SELECT
																								(
																									SELECT
																										ISNULL(
																												(
																													SELECT TOP (1)
																														ISNULL(
																																(
																																	SELECT TOP (1)
																																		(
																																			SELECT dbo.fnIF194_EvaluarCantidadPendiente(E.DocNumber,E.SequenceNo,E.SKU, @EsOrdenDeEcommerce)
																																		) AS FunctionResult
																																	FROM
																																		dbo.OrderDetail B																																		
																																		INNER JOIN dbo.OrderHeader C ON C.OrderNumber = B.OrderNumber																																		
																																		INNER JOIN dbo.TranTotalDOM D ON D.StoreNumber = C.OrigStore
																																									AND D.TranDate = C.OrigDate
																																									AND D.RegisterID = C.OrigRegID
																																									AND D.TranNumber = C.OrigTranNo																																		
																																		INNER JOIN dbo.TranDetailDOM E ON E.DocNumber = D.DocNumber
																																									AND E.SKU = B.SKU
																																									AND E.SequenceNo = (CASE
																																														WHEN B.AtForQty <> 0 THEN B.AtForQty
																																														ELSE B.OrdSeqNumber
																																														END)

																																	WHERE
																																		--
																																		B.OrderNumber = LineItem.SpecialOrderNumber
																																		AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																																		AND B.SKU = LineItem.SKU

																																		AND(
																																				LineItem.TranTotalTypeOfSale = 88
																																				OR LineItem.TranTotalTypeOfSale = 21
																																			)
																																		AND (LineItem.ReferenceNo__3 <> '')																																		
																																		AND C.OrigStore <> 0
																																		AND C.OrigRegID <> 0
																																		AND C.OrigTranNo <> 0
																																	ORDER BY
																																		E.DocNumber ASC
																																)
																														,0.00) AS QResult
																													FROM
																														dbo.OrderDetail B
																													WHERE
																														--Posteriormente, buscar el primer registro de OrderDetail en donde:
																														B.OrderNumber = LineItem.SpecialOrderNumber
																														AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																														AND B.SKU = LineItem.SKU

																														AND
																															(
																																LineItem.TranTotalTypeOfSale = 88
																																OR LineItem.TranTotalTypeOfSale = 21
																															)
																														AND LineItem.ReferenceNo__3 <> ''
																													ORDER BY
																														QResult ASC
																												)
																											, 0.00) --asignar el valor de TranDetail.PickTake a ORDERQUANTITY

																									WHERE
																										(
																										LineItem.Quantity < 0
																										AND LineItem.SpecialOrderNumber <> 0
																										AND ( LineItem.TypeOfSale > 10
																											AND LineItem.TypeOfSale < 20 )
																										)
																										OR( 
																											LineItem.Quantity < 0
																											AND LineItem.SpecialOrderNumber <> 0
																											AND ( LineItem.TypeOfSale = 88
																												OR LineItem.TypeOfSale = 21 )
																										)
																								)
																					)
																				WHERE
																					EXISTS
																					(
																						SELECT TOP (1)
																							A.TypeOfSale
																						FROM
																							dbo.TranDetailDOM A
																						WHERE
																							----START of Match the LineItem
																							A.TranNumber = LineItem.TranNumber
																							AND A.TranDate = LineItem.TranDate
																							AND A.RegisterID = LineItem.RegisterID
																							AND A.SKU = LineItem.SKU
																							----END of Match the LineItem
																							AND (A.Quantity <> 0)
																							AND (A.DocNumber <> 0)
																							AND (A.TypeOfSale <= 10
																								OR A.TypeOfSale >= 20
																								AND A.TypeOfSale <> 88)
																							AND (A.PickTake = 'D'
																								OR A.PickTake = 'P')
																							OR (
																								(A.TypeOfSale = 88
																								AND LineItem.ReferenceNo__3 <> '')
																								OR
																								(A.TypeOfSale = 21
																								AND LineItem.ReferenceNo__3 <> '')
																								)
																							AND LineItem.RegisterID <> 81
																							AND (LineItem.PickTake = 'D'
																								OR LineItem.PickTake = 'P')
																						ORDER BY
																							A.TranNumber ASC
																					)																					
																			)
																		FOR XML PATH('OrderQuantity')
																		, ELEMENTS
																		, TYPE
																	)
																	,(
																		SELECT
																			'false' AS '@PackDownFlag'
																			,ISNULL(
																				(
																					SELECT TOP (1)
																						A.ZoneId + ' ' + A.Bays
																					FROM
																						LocationSKU A
																					WHERE 
																						A.SKU = LineItem.SKU
																				)
																			, '0')
																		FOR XML PATH ('SellingLocation')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			
																			(
																				SELECT TOP (1)
																					(
																						SELECT
																							ISNULL(
																									(
																										SELECT TOP (1)
																											C.FirstName + ' ' + C.LastName
																										FROM
																											Employee C
																										WHERE
																											C.EmployeeNo = O.EmployeeNo
																									)
																							,'?')
																					) AS '@OperatorName'
																					, O.EmployeeNo AS 'text()'
																				FROM
																					OrderHeader O
																				WHERE
																					O.OrderNumber = LineItem.SpecialOrderNumber
																				ORDER BY O.EmployeeNo ASC
																				FOR XML PATH ('AssociateID')
																				,ELEMENTS
																				,TYPE
																			)
																		FOR XML PATH ('Associate')
																		,ELEMENTS
																		,TYPE
																	)

																	,(
																		SELECT
																			@BusinessCustomerFlagValue AS '@BusinessCustomerFlag'
																			,(
																				SELECT TOP (1)
																					CASE
																						WHEN A.[Type] = 'D' THEN 'A/R'
																						ELSE ' '
																					END
																				FROM
																					Customer A
																				WHERE
																					A.CustomerID = LineItem.TranTotalAccountNo
																					--
																				ORDER BY
																					A.CustomerID ASC
																			) AS '@CustomerType'
																			,(
																				SELECT
																					ISNULL(
																							(
																								SELECT TOP (1)
																									'0'
																								FROM
																									OrderDetail A
																								WHERE
																									A.OrderNumber = LineItem.SpecialOrderNumber AND A.OrdSeqNumber = LineItem.SequenceNo
																									AND A.DeliveryType = 0
																								ORDER BY
																									A.DeliveryType ASC
																							)
																					,'1')
																			) AS '@DirectFlag'
																			,(
																				SELECT
																					CASE	
																						WHEN LineItem.TranTotalRegisterID = 80 THEN '11110000004'
																						ELSE LineItem.TranTotalAccountNo
																					END
																				FOR XML PATH('CustomerID')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					ISNULL(NULLIF(LTRIM(RTRIM(
																												(
																													SELECT TOP (1)
																														A.SoldBusName
																													FROM
																														OrderHeader A
																													WHERE
																														A.OrderNumber = LineItem.SpecialOrderNumber
																												)
																											 )
																										)
																								,'')
																							,CASE WHEN h.Name != '' and h.Name is not null THEN h.Name ELSE '*SIN NOMBRE*' END)
																					AS 'text()'
																				FOR XML PATH('Name')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					CASE
																						WHEN  NULLIF(LTRIM(RTRIM(LineItem.TranTotalTelephone)),'') IS NOT NULL THEN
																							(
																								SELECT
																									'Home' AS '@TypeCode'
																									,(
																										SELECT
																											LineItem.TranTotalTelephone as "text()" 
																										FOR XML PATH('FullTelephoneNumber')
																										, ELEMENTS
																										, TYPE
																									)
																								FOR XML PATH('Telephone')
																								, ELEMENTS
																								, TYPE
																							)
																					END
																			)
																			,(
																			--Verificar que el hijo retorne un resultado, de lo contrario no mostrar padre e Hijo
																				SELECT
																					CASE
																						WHEN NULLIF(LTRIM(RTRIM(
																							(
																								SELECT TOP (1)
																									A.AltPhoneNumb as "text()"
																								FROM
																									dbo.OrderHeader A
																									INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																								WHERE
																									B.OrderNumber = LineItem.SpecialOrderNumber
																									AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																									AND B.SKU = LineItem.SKU
																								ORDER BY
																									B.OrdSeqNumber ASC
																							) 
																							)),'') IS NOT NULL
																							THEN
																								(
																									SELECT
																										'Office' AS '@TypeCode'
																										,(
																											SELECT
																												(
																													SELECT TOP (1)
																														A.AltPhoneNumb as "text()"
																													FROM
																														dbo.OrderHeader A
																														INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																													WHERE
																														B.OrderNumber = LineItem.SpecialOrderNumber
																														AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																														AND B.SKU = LineItem.SKU
																													ORDER BY
																														B.OrdSeqNumber ASC
																												) 
																											FOR XML PATH('FullTelephoneNumber')
																											, ELEMENTS
																											, TYPE
																										)
																									FOR XML PATH('Telephone')
																									, ELEMENTS
																									, TYPE
																								)
																					END
																			)
																			,(
																				SELECT
																					(		
																						SELECT
																							ISNULL(NULLIF(LTRIM(RTRIM(
																														(
																															SELECT TOP (1)
																																A.ShipBusName
																															FROM
																																OrderHeader A
																															WHERE
																																A.OrderNumber = LineItem.SpecialOrderNumber
																														)
																													 )
																												)
																										,'')
																									,CASE WHEN h.ShipBusName != '' and h.ShipBusName is not null THEN h.ShipBusName ELSE '*SIN NOMBRE*' END)
																							AS 'text()'
																						FOR XML PATH('Name')																						
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																								ISNULL(NULLIF(LTRIM(RTRIM(
																															(
																																SELECT TOP (1)
																																	A.ReceiveName
																																FROM
																																	OrderHeader A
																																WHERE
																																	A.OrderNumber = LineItem.SpecialOrderNumber
																															)
																														 )
																													)
																											,'')
																										,CASE WHEN h.ShipBusName != '' and h.ShipBusName is not null THEN h.ShipBusName ELSE '*SIN NOMBRE*' END)
																								AS 'text()'
																						FOR XML PATH('AlternateName')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToAdd1
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToAdd1 END as "text()"
																						FOR XML PATH('AddressLine')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToCity
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToCity END as "text()"
																						FOR XML PATH('City')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToAdd2
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToAdd2 END as "text()"
																						FOR XML PATH('Neighborhood')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0 AND h.TillID != 80  THEN 
																							(SELECT TOP (1)
																										E.ShipToState
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToState END as "text()" 
																						FOR XML PATH('State')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToZip
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToZip END as "text()" 
																						FOR XML PATH('PostalCode')
																						, ELEMENTS
																						, TYPE
																					)
																			
																					,(
																						SELECT
																							CASE
																								WHEN EXISTS
																								(
																									SELECT TOP (1)
																										CASE
																											WHEN LineItem.RegisterID = 80 THEN 
																												SUBSTRING(LineItem.TranTotalShipMidInit,1, (CHARINDEX('|', LineItem.TranTotalShipMidInit) - 1))
																											ELSE
																												--ISNULL(
																														(
																															SELECT TOP (1)
																																E.Latitude
																															FROM
																																OrderHeader E
																															WHERE
																																E.OrderNumber = LineItem.SpecialOrderNumber
																															ORDER BY
																																E.Latitude ASC
																														)
																												--, ' ')
																										END
																								) THEN
																									(
																										SELECT
																											(
																												SELECT TOP (1)
																													CASE
																														WHEN LineItem.RegisterID = 80 THEN 
																															SUBSTRING(LineItem.TranTotalShipMidInit,1, (CHARINDEX('|', LineItem.TranTotalShipMidInit) - 1))
																														ELSE
																															--ISNULL(
																																	(
																																		SELECT TOP (1)
																																			E.Latitude
																																		FROM
																																			OrderHeader E
																																		WHERE
																																			E.OrderNumber = LineItem.SpecialOrderNumber
																																		ORDER BY
																																			E.Latitude ASC
																																	)
																													END
																											)
																										FOR XML PATH('Latitude')
																										, ELEMENTS
																										, TYPE
																									)
																							END
																					)
																					,(
																						SELECT
																							(
																								SELECT
																									CASE
																										WHEN EXISTS
																											(
																												SELECT TOP (1)
																													CASE
																														WHEN LineItem.RegisterID = 80 THEN 
																															SUBSTRING(LineItem.TranTotalShipMidInit, CHARINDEX('|', LineItem.TranTotalShipMidInit) + 1, LEN(LineItem.TranTotalShipMidInit))

																														ELSE
																															--ISNULL(
																																	(
																																		SELECT TOP (1)
																																			E.[Length]
																																		FROM
																																			OrderHeader E
																																		WHERE
																																			E.OrderNumber = LineItem.SpecialOrderNumber
																																		ORDER BY
																																			E.Latitude ASC
																																	)
																															--, ' ')
																													END
																											) THEN
																												(
																													SELECT TOP (1)
																														CASE
																															WHEN LineItem.RegisterID = 80 THEN 
																																SUBSTRING(LineItem.TranTotalShipMidInit, CHARINDEX('|', LineItem.TranTotalShipMidInit) + 1, LEN(LineItem.TranTotalShipMidInit))
																															ELSE
																																--ISNULL(
																																		(
																																			SELECT TOP (1)
																																				E.[Length]
																																			FROM
																																				OrderHeader E
																																			WHERE
																																				E.OrderNumber = LineItem.SpecialOrderNumber
																																			ORDER BY
																																				E.Latitude ASC
																																		)
																																--, ' ')
																														END
																												)
																									END
																							)
																						FOR XML PATH('Longitude')
																						, ELEMENTS
																						, TYPE
																					)
																				FOR XML PATH('Address')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT CASE WHEN LineItem.RegisterId = 80 THEN
																					(
																						SELECT
																							CASE ISDATE( SUBSTRING(LineItem.SerialNumber, 1, 4) + '-' + SUBSTRING(LineItem.SerialNumber, 6, 2) + '-' + SUBSTRING(LineItem.SerialNumber, 9, 2))
																								WHEN 1 THEN	
																									SUBSTRING(LineItem.SerialNumber, 1, 4) + '-' + SUBSTRING(LineItem.SerialNumber, 6, 2) + '-' + SUBSTRING(LineItem.SerialNumber, 9, 2) + 'T00:00:00'
																								ELSE
																									CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')
																							END
																					)
																					ELSE
																					(
																						SELECT
																						ISNULL((
																						SELECT TOP (1)
																							CONCAT(ISNULL(B.DELDATE,CAST(GETDATE() AS DATE)), 'T00:00:00')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.SequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC)
																						,CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')	)
																					)
																					END
																				FOR XML PATH('PreferredDateTime')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					LineItem.PickTake as "text()"
																				FOR XML PATH('Method')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT --validar que el maximo de caracteres sea 256 y agregar espacio despues de cada coma
																					(
																						SELECT
																							ISNULL(
																								SUBSTRING(
																											STUFF(
																													(
																														SELECT
																															', ' + A.VendorUPC
																														FROM
																															ItemXref A
																														WHERE
																															A.SKU = LineItem.SKU
																														FOR XML PATH('')
																													)
																											, 1,2,''),
																								1, 256)
																							,' ')
																					)
																				FOR XML PATH('Notes')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT TOP (1)
																							B.UnitPrice AS 'text()'
																						FROM
																							OrderDetail B 
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.SKU IS NULL --
																							AND CHARINDEX('(FMS)',B.SKUDesc) > 0 --
																							AND B.MiscAcct = 1 --
																						ORDER BY
																							B.OrdSeqNumber ASC
																					) 
																				FOR XML PATH('ShippingFee')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT TOP (1)
																							A.DistanceShip as "text()"
																						FROM
																							dbo.OrderHeader A
																							INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.OrdSeqNumber ASC
																					) 
																				FOR XML PATH('DeliveryDistance')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT TOP (1)
																							B.UnitPrice AS 'text()'
																						FROM
																							OrderDetail B 
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.SKU IS NULL --
																							AND CHARINDEX('(FMS)',B.SKUDesc) > 0 --
																							AND B.MiscAcct = 2 --
																						ORDER BY
																							B.OrdSeqNumber ASC
																					) 
																				FOR XML PATH('HandlingFee')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					CASE
																						WHEN
																						(
																							SELECT TOP (1)
																								NULLIF(LTRIM(RTRIM(A.ReferenceNo__1)),'')
																							FROM
																								OrderHeader A
																								INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																							WHERE
																								B.OrderNumber = LineItem.SpecialOrderNumber
																								AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																								AND B.SKU = LineItem.SKU
																							ORDER BY
																								B.OrdSeqNumber ASC
																						) IS NOT NULL THEN
																							(
																								SELECT TOP (1)
																									A.ReferenceNo__2 AS '@Details'
																									, A.ReferenceNo__1 as 'text()'
																								FROM
																									OrderHeader A
																									INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																								WHERE
																									B.OrderNumber = LineItem.SpecialOrderNumber
																									AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																									AND B.SKU = LineItem.SKU
																								ORDER BY
																									B.OrdSeqNumber ASC
																								FOR XML PATH('Zone')
																								, ELEMENTS
																								, TYPE
																							)
																						ELSE
																							(
																								SELECT
																									'SIN ZONA'
																								FOR XML PATH('Zone')
																								, ELEMENTS
																								, TYPE
																							)
																					END
																			)
																			,(
																				SELECT
																					(
																						SELECT
																							ISNULL(
																									(
																										SELECT TOP (1)
																											C.StoreDelivery
																										FROM
																											DomFulf C
																											INNER JOIN Store D ON C.StoreRem = D.StoreNumber
																										WHERE
																											C.OrderNo = LineItem.SpecialOrderNumber
																										ORDER BY
																											C.StoreRem ASC
																									)
																							, 
																								(
																									SELECT TOP (1)
																										A.StoreNumber
																									FROM
																										Store A
																									WHERE
																										A.StoreType = 'true'
																									ORDER BY
																										A.StoreNumber ASC
																								)
																							)
																					)
																				FOR XML PATH('CustomerOrderStoreID')
																				, ELEMENTS
																				, TYPE
																			)
																		FOR XML PATH('Delivery')
																		, ELEMENTS
																		, TYPE
																	)																	
																FOR XML PATH('CustomerOrderForDelivery')
															)
													WHEN LineItem.PickTake = 'P' THEN
															(
																SELECT
																	@BusinessCustomerFlagValue
																	AS '@BusinessSaleFlag'
																	, CASE WHEN LineItem.TypeOfSale = 88 THEN 'Return' ELSE 'Active' END AS '@ItemStatus'
																	, CASE
																		WHEN LineItem.TranDetailType = 1 THEN 'Fee'
																		ELSE 'Stock'
																	 END as '@ItemType'
																	, 'Completed' AS '@OrderStatus'
																	,(
																		CASE
																			WHEN LineItem.RegisterID = 80 THEN 'Invoice'
																			ELSE
																				(
																					SELECT TOP (1)
																						CASE
																							-- HGU INI 2016-08-26 Se cambió la lógica de COD
																							--WHEN A.OrderType = 'C' THEN 'COD'
																							WHEN A.OrderType = 'C' THEN
																								CASE LineItem.TypeOfSale
																									WHEN  3 THEN	'VALIDACION COD'
																									WHEN 40 THEN	'COD'
																									ELSE			'NO DEFINIDO'
																								END
																							-- HGU FIN 2016-08-26
																							WHEN A.OrderType = 'E' THEN 'Estimate'
																							WHEN A.OrderType = 'I' THEN 'Invoice'
																							WHEN A.OrderType = 'P' THEN 'CEO'
																							WHEN A.OrderType = 'Q' THEN 'Quote'
																							WHEN A.OrderType = 'R' THEN 'ReturnOrExchange'
																							WHEN A.OrderType = 'S' THEN 'SpecialOrder'
																							WHEN A.OrderType = 'T' THEN 'SpecialOrderReturn'
																							WHEN A.OrderType = 'Z' THEN 'Consignation'
																							WHEN A.OrderType = 'Y' THEN 'Estimate'
																							ELSE 'Estimate'
																						END
																					FROM
																						OrderHeader A
																						INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																					WHERE
																						B.OrderNumber = LineItem.SpecialOrderNumber
																						-- HGU INI 2016-11-09 Se cambió el criterio del filtro
																						--AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo--LineItem.SequenceNumber
																						AND B.OrdSeqNumber = LineItem.SequenceNo
																						-- HGU FIN 2016-11-09
																						AND B.SKU = LineItem.SKU
																					ORDER BY A.OrderType ASC
																				)
																		END
																	) AS '@OrderType'
																	,(
																		SELECT
																			CASE WHEN LineItem.IsAnAlternativeItem = 0 
																			THEN LineItem.ItemID
																			ELSE LineItem.PrimarySKU
																			END AS 'text()'
																		FOR XML PATH('ItemID')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			CASE 
																				WHEN ISDATE(LTRIM(RTRIM(LineItem.TranTotalSystemDate))) = 1 THEN
																					CAST( CONVERT( DATE, LineItem.TranTotalSystemDate, 111) AS VARCHAR(25)) + 'T00:00:00-05:00'
																				ELSE
																					NULL
																			END	AS '@CreateDate'
																			, IIF(@EsOrdenDeEcommerce = 1, @EcommOrderNumber, IIF(@IsDevolution = 1, @OrderDevolution,LineItem.SpecialOrderNumber)) AS 'text()'
																		FOR XML PATH('SpecialOrderNumber')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																				LineItem.RowNumber AS 'text()'																			
																		FOR XML PATH('SpecialOrderSequenceNumber')
																		, ELEMENTS
																		, TYPE
																	)
																	,(
																		SELECT
																			--'VALOR DE ITEMID' AS 'text()'
																			LineItem.MerchandiseHierarchy AS 'text()'
																		FOR XML PATH('MerchandiseHierarchy')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			CASE WHEN LineItem.IsAnAlternativeItem = 1
																			THEN (SELECT LineItem.SKU AS 'text()' FOR XML PATH('AlternateItemID')
																			, ELEMENTS
																			,TYPE)
																			END																			
																	)
																	--ELEMENTO DESCRIPTION
																	,(
																		SELECT
																			ISNULL(
																					(
																						SELECT TOP (1)
																							NULLIF(LTRIM(RTRIM(B.SKUDesc)),'')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC
																					)
																			,	(
																					SELECT
																						CASE
																							WHEN LineItem.TranDetailType = 0 THEN 
																																	(
																																		SELECT TOP (1)
																																			NULLIF(LTRIM(RTRIM(A.SpanishDesc)),'')
																																		FROM
																																			ItemReplen A
																																		WHERE
																																			A.SKU = LineItem.SKU
																																		ORDER BY
																																			A.SpanishDesc ASC
																																	)
																							ELSE
																								(
																									SELECT TOP(1)
																										NULLIF(LTRIM(RTRIM(A.[Description])),'')
																									FROM
																										Fee A
																									WHERE
																										A.FeeCode = LineItem.TranDetailMiscAcct
																									ORDER BY
																										A.[Description] ASC
																								)
																						END
																				)
																			) AS 'text()'
																		FOR XML PATH('Description')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.UnitCostPrice AS 'text()'
																		FOR XML PATH ('UnitCostPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.UnitListPrice AS 'text()'
																		FOR XML PATH ('UnitListPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			ABS(LineItem.ExtendedAmt) AS 'text()'
																		FOR XML PATH ('RegularSalesUnitPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			ABS(LineItem.ExtendedAmt) AS 'text()'
																		FOR XML PATH ('ActualSalesUnitPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			--'VALOR DE ITEMID' AS 'text()'
																			LineItem.AbsoluteExtendedAmount AS 'text()'
																		FOR XML PATH('ExtendedAmount')
																		, ELEMENTS
																		,TYPE
																	)
																	
																	,(
																		SELECT
																			LineItem.UnitDiscountAmount AS 'text()'
																		FOR XML PATH ('UnitDiscountAmount')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.ExtendedDiscountAmount AS 'text()'
																		FOR XML PATH ('ExtendedDiscountAmount')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																		CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		ELSE LineItem.PrimaryUOM END AS '@PrimaryUnitOfMeasureCode'
																		, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN ABS(CAST(LineItem.PrimaryUnitsCustomerOrder as Decimal (10,4)))
																			  ELSE 
																				 CASE WHEN LineItem.AlternativeUOMConvert IS NOT NULL AND LineItem.AlternativeUOMConvert<>0 
																				 THEN  ABS(CAST((LineItem.Quantity /LineItem.AlternativeUOMConvert) as decimal (10,4)))
																				 ELSE 0--''
																				 END 
																			  END AS '@PrimaryUnits'
																		, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOMConvert 
																		  ELSE 
																				CASE WHEN LineItem.AlternativeUOMConvert IS NOT NULL AND LineItem.AlternativeUOMConvert<>0 
																				THEN  CAST(CAST(1 AS DECIMAL(8,4))/LineItem.AlternativeUOMConvert AS DECIMAL(25,10))
																				ELSE 0-- ''
																				END 
																		  END AS '@Units'
																		, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		  ELSE LineItem.UOM
																		  END
																		  AS '@UnitOfMeasureCode'
																		, ABS(LineItem.Quantity) AS 'text()'
																	   FOR XML PATH('Quantity')
																	   ,ELEMENTS
																	   ,TYPE
																	)
																	,
																	(
																		SELECT CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		  ELSE LineItem.UOM
																		  END
																		  AS '@UnitOfMeasureCode',
																		  0.00
																		  AS '@Units',
																			(
																				SELECT
																					(
																						SELECT
																								(
																									SELECT
																										ISNULL(
																												(
																													SELECT TOP (1)
																														ISNULL(
																																(
																																	SELECT TOP (1)
																																		(
																																			SELECT dbo.fnIF194_EvaluarCantidadPendiente(E.DocNumber,E.SequenceNo,E.SKU, @EsOrdenDeEcommerce)
																																		) AS FunctionResult
																																	FROM
																																		dbo.OrderDetail B
																																		
																																		INNER JOIN dbo.OrderHeader C ON C.OrderNumber = B.OrderNumber																																		
																																		INNER JOIN dbo.TranTotalDOM D ON D.StoreNumber = C.OrigStore
																																									AND D.TranDate = C.OrigDate
																																									AND D.RegisterID = C.OrigRegID
																																									AND D.TranNumber = C.OrigTranNo																																		
																																		INNER JOIN dbo.TranDetailDOM E ON E.DocNumber = D.DocNumber
																																									AND E.SKU = B.SKU
																																									AND E.SequenceNo = (CASE
																																														WHEN B.AtForQty <> 0 THEN B.AtForQty
																																														ELSE B.OrdSeqNumber
																																														END)

																																	WHERE
																																		--
																																		B.OrderNumber = LineItem.SpecialOrderNumber  
																																		AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																																		AND B.SKU = LineItem.SKU

																																		AND(
																																				LineItem.TranTotalTypeOfSale = 88
																																				OR LineItem.TranTotalTypeOfSale = 21
																																			)
																																		AND (LineItem.ReferenceNo__3 <> '')
																																		
																																		AND C.OrigStore <> 0
																																		AND C.OrigRegID <> 0
																																		AND C.OrigTranNo <> 0
																																	ORDER BY
																																		E.DocNumber ASC
																																)
																														,0.0) AS QResult
																													FROM
																														dbo.OrderDetail B
																													WHERE
																														--Posteriormente, buscar el primer registro de OrderDetail en donde:
																														B.OrderNumber = LineItem.SpecialOrderNumber
																														AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																														AND B.SKU = LineItem.SKU

																														AND
																															(
																																LineItem.TranTotalTypeOfSale = 88
																																OR LineItem.TranTotalTypeOfSale = 21
																															)
																														AND LineItem.ReferenceNo__3 <> ''
																													ORDER BY
																														QResult ASC
																												)
																											, 0.0) --asignar el valor de TranDetail.PickTake a ORDERQUANTITY

																									WHERE
																										(
																										LineItem.Quantity < 0
																										AND LineItem.SpecialOrderNumber <> 0
																										AND ( LineItem.TypeOfSale > 10
																											AND LineItem.TypeOfSale < 20 )
																										)
																										OR( 
																											LineItem.Quantity < 0
																											AND LineItem.SpecialOrderNumber <> 0
																											AND ( LineItem.TypeOfSale = 88
																												OR LineItem.TypeOfSale = 21 )
																										)
																								)
																					)
																				WHERE
																					EXISTS
																					(
																						SELECT TOP (1)
																							A.TypeOfSale
																						FROM
																							dbo.TranDetailDOM A
																						WHERE
																							----START of Match the LineItem
																							A.TranNumber = LineItem.TranNumber
																							AND A.TranDate = LineItem.TranDate
																							AND A.RegisterID = LineItem.RegisterID
																							AND A.SKU = LineItem.SKU
																							----END of Match the LineItem
																							AND (A.Quantity <> 0)
																							AND (A.DocNumber <> 0)
																							AND (A.TypeOfSale <= 10
																								OR A.TypeOfSale >= 20
																								AND A.TypeOfSale <> 88)
																							AND (A.PickTake = 'D'
																								OR A.PickTake = 'P')
																							OR (
																								(A.TypeOfSale = 88
																								AND LineItem.ReferenceNo__3 <> '')
																								OR
																								(A.TypeOfSale = 21
																								AND LineItem.ReferenceNo__3 <> '')
																								)
																							AND LineItem.RegisterID <> 81
																							AND (LineItem.PickTake = 'D'
																								OR LineItem.PickTake = 'P')
																						ORDER BY
																							A.TranNumber ASC
																					)

																			)
																		FOR XML PATH('OrderQuantity')
																		, ELEMENTS
																		, TYPE
																	)
																	,(
																		SELECT
																			'false' AS '@PackDownFlag'
																			,ISNULL(
																				(
																					SELECT TOP (1)
																						A.ZoneId + ' ' + A.Bays
																					FROM
																						LocationSKU A
																					WHERE 
																						A.SKU = LineItem.SKU
																				)
																			, '0')
																		FOR XML PATH ('SellingLocation')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			
																			(
																				SELECT TOP (1)
																					(
																						SELECT
																							ISNULL(
																									(
																										SELECT TOP (1)
																											C.FirstName + ' ' + C.LastName
																										FROM
																											Employee C
																										WHERE
																											C.EmployeeNo = O.EmployeeNo
																									)
																							,'?')
																					) AS '@OperatorName'
																					, O.EmployeeNo AS 'text()'
																				FROM
																					OrderHeader O
																				WHERE
																					O.OrderNumber = LineItem.SpecialOrderNumber
																				ORDER BY O.EmployeeNo ASC
																				FOR XML PATH ('AssociateID')
																				,ELEMENTS
																				,TYPE
																			)
																		FOR XML PATH ('Associate')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			@BusinessCustomerFlagValue AS '@BusinessCustomerFlag'
																			,(
																				SELECT TOP (1)
																					CASE
																						WHEN A.[Type] = 'D' THEN 'A/R'
																						ELSE ' '
																					END
																				FROM
																					Customer A
																				WHERE
																					A.CustomerID = LineItem.TranTotalAccountNo
																					--
																				ORDER BY
																					A.CustomerID ASC
																			) AS '@CustomerType'
																			,(
																				SELECT
																					CASE	
																						WHEN LineItem.TranTotalRegisterID = 80 THEN '11110000004'
																						ELSE LineItem.TranTotalAccountNo
																					END
																				FOR XML PATH('CustomerID')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					ISNULL(NULLIF(LTRIM(RTRIM(
																												(
																													SELECT TOP (1)
																														A.SoldBusName
																													FROM
																														OrderHeader A
																													WHERE
																														A.OrderNumber = LineItem.SpecialOrderNumber
																												)
																											 )
																										)
																								,'')
																							,CASE WHEN h.Name != '' and h.Name is not null THEN h.Name ELSE '*SIN NOMBRE*' END)
																					AS 'text()'
																				FOR XML PATH('Name')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					CASE
																						WHEN  NULLIF(LTRIM(RTRIM(LineItem.TranTotalTelephone)),'') IS NOT NULL THEN
																							(
																								SELECT
																									'Home' AS '@TypeCode'
																									,(
																										SELECT
																											LineItem.TranTotalTelephone as "text()" 
																										FOR XML PATH('FullTelephoneNumber')
																										, ELEMENTS
																										, TYPE
																									)
																								FOR XML PATH('Telephone')
																								, ELEMENTS
																								, TYPE
																							)
																					END
																			)
																			,(
																			--Verificar que el hijo retorne un resultado, de lo contrario no mostrar padre e Hijo
																				SELECT
																					CASE
																						WHEN NULLIF(LTRIM(RTRIM(
																							(
																								SELECT TOP (1)
																									A.AltPhoneNumb as "text()"
																								FROM
																									dbo.OrderHeader A
																									INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																								WHERE
																									B.OrderNumber = LineItem.SpecialOrderNumber
																									AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																									AND B.SKU = LineItem.SKU
																								ORDER BY
																									B.OrdSeqNumber ASC
																							) 
																							)),'') IS NOT NULL
																							THEN
																								(
																									SELECT
																										'Office' AS '@TypeCode'
																										,(
																											SELECT
																												(
																													SELECT TOP (1)
																														A.AltPhoneNumb as "text()"
																													FROM
																														dbo.OrderHeader A
																														INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																													WHERE
																														B.OrderNumber = LineItem.SpecialOrderNumber
																														AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																														AND B.SKU = LineItem.SKU
																													ORDER BY
																														B.OrdSeqNumber ASC
																												) 
																											FOR XML PATH('FullTelephoneNumber')
																											, ELEMENTS
																											, TYPE
																										)
																									FOR XML PATH('Telephone')
																									, ELEMENTS
																									, TYPE
																								)
																					END
																			)
																				,(
																				SELECT
																					(		
																						SELECT
																							ISNULL(NULLIF(LTRIM(RTRIM(
																														(
																															SELECT TOP (1)
																																A.ShipBusName
																															FROM
																																OrderHeader A
																															WHERE
																																A.OrderNumber = LineItem.SpecialOrderNumber
																														)
																													 )
																												)
																										,'')
																									,CASE WHEN h.ShipBusName != '' and h.ShipBusName is not null THEN h.ShipBusName ELSE '*SIN NOMBRE*' END)
																							AS 'text()'
																						FOR XML PATH('Name')																						
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																								ISNULL(NULLIF(LTRIM(RTRIM(
																															(
																																SELECT TOP (1)
																																	A.ReceiveName
																																FROM
																																	OrderHeader A
																																WHERE
																																	A.OrderNumber = LineItem.SpecialOrderNumber
																															)
																														 )
																													)
																											,'')
																										,CASE WHEN h.ShipBusName != '' and h.ShipBusName is not null THEN h.ShipBusName ELSE '*SIN NOMBRE*' END)
																								AS 'text()'
																						FOR XML PATH('AlternateName')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToAdd1
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToAdd1 END as "text()"
																						FOR XML PATH('AddressLine')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToCity
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToCity END as "text()"
																						FOR XML PATH('City')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToAdd2
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToAdd2 END as "text()"
																						FOR XML PATH('Neighborhood')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToState
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToState END as "text()" 
																						FOR XML PATH('State')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToZip
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToZip END as "text()" 
																						FOR XML PATH('PostalCode')
																						, ELEMENTS
																						, TYPE
																					)
																			
																					,(
																						SELECT
																							CASE
																								WHEN EXISTS
																								(
																									SELECT TOP (1)
																										CASE
																											WHEN LineItem.RegisterID = 80 THEN 
																												SUBSTRING(LineItem.TranTotalShipMidInit,1, (CHARINDEX('|', LineItem.TranTotalShipMidInit) - 1))
																											ELSE
																												--ISNULL(
																														(
																															SELECT TOP (1)
																																E.Latitude
																															FROM
																																OrderHeader E
																															WHERE
																																E.OrderNumber = LineItem.SpecialOrderNumber
																															ORDER BY
																																E.Latitude ASC
																														)
																												--, ' ')
																										END
																								) THEN
																									(
																										SELECT
																											(
																												SELECT TOP (1)
																													CASE
																														WHEN LineItem.RegisterID = 80 THEN 
																															SUBSTRING(LineItem.TranTotalShipMidInit,1, (CHARINDEX('|', LineItem.TranTotalShipMidInit) - 1))
																														ELSE
																															--ISNULL(
																																	(
																																		SELECT TOP (1)
																																			E.Latitude
																																		FROM
																																			OrderHeader E
																																		WHERE
																																			E.OrderNumber = LineItem.SpecialOrderNumber
																																		ORDER BY
																																			E.Latitude ASC
																																	)
																															--, ' ')
																													END
																											)
																										FOR XML PATH('Latitude')
																										, ELEMENTS
																										, TYPE
																									)
																							END
																					)
																					,(
																						SELECT
																							(
																								SELECT
																									CASE
																										WHEN EXISTS
																											(
																												SELECT TOP (1)
																													CASE
																														WHEN LineItem.RegisterID = 80 THEN 
																															SUBSTRING(LineItem.TranTotalShipMidInit, CHARINDEX('|', LineItem.TranTotalShipMidInit) + 1, LEN(LineItem.TranTotalShipMidInit))
																														ELSE
																															--ISNULL(
																																	(
																																		SELECT TOP (1)
																																			E.[Length]
																																		FROM
																																			OrderHeader E
																																		WHERE
																																			E.OrderNumber = LineItem.SpecialOrderNumber
																																		ORDER BY
																																			E.Latitude ASC
																																	)
																															--, ' ')
																													END
																											) THEN
																												(
																													SELECT TOP (1)
																														CASE
																															WHEN LineItem.RegisterID = 80 THEN 
																																SUBSTRING(LineItem.TranTotalShipMidInit, CHARINDEX('|', LineItem.TranTotalShipMidInit) + 1, LEN(LineItem.TranTotalShipMidInit))
																															ELSE
																																--ISNULL(
																																		(
																																			SELECT TOP (1)
																																				E.[Length]
																																			FROM
																																				OrderHeader E
																																			WHERE
																																				E.OrderNumber = LineItem.SpecialOrderNumber
																																			ORDER BY
																																				E.Latitude ASC
																																		)
																																--, ' ')
																														END
																												)
																									END
																							)
																						FOR XML PATH('Longitude')
																						, ELEMENTS
																						, TYPE
																					)

																				FOR XML PATH('Address')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT CASE WHEN LineItem.RegisterId = 80 THEN
																					(
																						SELECT
																							CASE ISDATE( SUBSTRING(LineItem.SerialNumber, 1, 4) + '-' + SUBSTRING(LineItem.SerialNumber, 6, 2) + '-' + SUBSTRING(LineItem.SerialNumber, 9, 2))
																								WHEN 1 THEN	
																									SUBSTRING(LineItem.SerialNumber, 1, 4) + '-' + SUBSTRING(LineItem.SerialNumber, 6, 2) + '-' + SUBSTRING(LineItem.SerialNumber, 9, 2) + 'T00:00:00'
																								ELSE
																									CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')
																							END
																					)
																					ELSE
																					(
																						SELECT
																						ISNULL((
																						SELECT TOP (1)
																							CONCAT(ISNULL(B.DELDATE,CAST(GETDATE() AS DATE)), 'T00:00:00')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.SequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC)
																						,CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')	)
																					)
																					END
																				FOR XML PATH('PreferredDateTime')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					LineItem.PickTake as "text()"
																				FOR XML PATH('Method')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT
																							ISNULL(
																								SUBSTRING(
																											STUFF(
																													(
																														SELECT
																															', ' + A.VendorUPC
																														FROM
																															ItemXref A
																														WHERE
																															A.SKU = LineItem.SKU
																														FOR XML PATH('')
																													)
																											, 1,2,''),
																								1, 256)
																							,' ')
																					)
																				FOR XML PATH('Notes')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT
																							ISNULL(
																									(
																										SELECT TOP (1)
																											C.StoreDelivery
																										FROM
																											DomFulf C
																											INNER JOIN Store D ON C.StoreRem = D.StoreNumber
																										WHERE
																											C.OrderNo = LineItem.SpecialOrderNumber
																										ORDER BY
																											C.StoreRem ASC
																									)
																							, 
																								(
																									SELECT TOP (1)
																										A.StoreNumber
																									FROM
																										Store A
																									WHERE
																										A.StoreType = 'true'
																									ORDER BY
																										A.StoreNumber ASC
																								)
																							)
																					)
																				FOR XML PATH('CustomerOrderStoreID')
																				, ELEMENTS
																				, TYPE
																			)
																		FOR XML PATH('Pickup')
																		, ELEMENTS
																		, TYPE
																	)
																	
																FOR XML PATH('CustomerOrderForPickup')
															)
															
													WHEN LineItem.PickTake = 'D' THEN
															(
																SELECT
																	@BusinessCustomerFlagValue
																	AS '@BusinessSaleFlag'
																	, CASE WHEN LineItem.TypeOfSale = 88 THEN 'Return' ELSE 'Active' END AS '@ItemStatus'
																	, CASE
																		WHEN LineItem.TranDetailType = 1 THEN 'Fee'
																		ELSE 'Stock'
																	 END as '@ItemType'
																	, 'Completed' AS '@OrderStatus'
																	,CASE
																		WHEN LineItem.TranTotalRecType = 'V' AND LineItem.TranTotalTypeOfSale = 3 THEN 'VALIDACION COD'
																		WHEN LineItem.RegisterID = 80 THEN 'Invoice'
																		ELSE
																			(
																				SELECT TOP (1)
																					CASE
																						-- HGU INI 2016-08-26 Se cambió la lógica de COD
																						--WHEN A.OrderType = 'C' THEN 'COD'
																						WHEN A.OrderType = 'C' THEN
																							CASE LineItem.TypeOfSale
																								WHEN  3 THEN	'VALIDACION COD'
																								WHEN 40 THEN	'COD'
																								ELSE			'NO DEFINIDO'
																							END
																						-- HGU FIN 2016-08-26
																						WHEN A.OrderType = 'E' THEN 'Estimate'
																						WHEN A.OrderType = 'I' THEN 'Invoice'
																						WHEN A.OrderType = 'P' THEN 'CEO'
																						WHEN A.OrderType = 'Q' THEN 'Quote'
																						WHEN A.OrderType = 'R' THEN 'ReturnOrExchange'
																						WHEN A.OrderType = 'S' THEN 'SpecialOrder'
																						WHEN A.OrderType = 'T' THEN 'SpecialOrderReturn'
																						WHEN A.OrderType = 'Z' THEN 'Consignation'
																						WHEN A.OrderType = 'Y' THEN 'Estimate'
																						ELSE 'Estimate'
																					END
																				FROM
																					OrderHeader A
																					INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																				WHERE
																					B.OrderNumber = LineItem.SpecialOrderNumber
																					-- HGU INI 2016-11-09 Se cambió el criterio del filtro
																					--AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo--LineItem.SequenceNumber
																					AND B.OrdSeqNumber = LineItem.SequenceNo
																					-- HGU FIN 2016-11-09
																					AND B.SKU = LineItem.SKU
																				ORDER BY A.OrderType ASC
																			) 
																	END	AS '@OrderType'
																	,(
																		SELECT
																			CASE WHEN LineItem.IsAnAlternativeItem = 0 
																			THEN LineItem.ItemID
																			ELSE LineItem.PrimarySKU
																			END AS 'text()'
																		FOR XML PATH('ItemID')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			CASE 
																				WHEN ISDATE(LTRIM(RTRIM(LineItem.TranTotalSystemDate))) = 1 THEN
																					CAST( CONVERT( DATE, LineItem.TranTotalSystemDate, 111) AS VARCHAR(25)) + 'T00:00:00-05:00'
																				ELSE
																					NULL
																			END	AS '@CreateDate'
																			, IIF(@EsOrdenDeEcommerce = 1, @EcommOrderNumber, IIF(@IsDevolution = 1, @OrderDevolution,LineItem.SpecialOrderNumber)) AS 'text()'
																		FOR XML PATH('SpecialOrderNumber')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																				LineItem.RowNumber AS 'text()'
																		
																		FOR XML PATH('SpecialOrderSequenceNumber')
																		, ELEMENTS
																		, TYPE
																	)
																	,(
																		SELECT
																			--'VALOR DE ITEMID' AS 'text()'
																			LineItem.MerchandiseHierarchy AS 'text()'
																		FOR XML PATH('MerchandiseHierarchy')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
                                    SELECT
  																	CASE WHEN LineItem.IsAnAlternativeItem = 1
  																	THEN (SELECT ABS(LineItem.SKU) AS 'text()' FOR XML PATH('AlternateItemID')
  																	, ELEMENTS
  																	,TYPE)
  																	END																			
  																)
																	--ELEMENTO DESCRIPTION
																	,(
																		SELECT
																			ISNULL(
																					(
																						SELECT TOP (1)
																							NULLIF(LTRIM(RTRIM(B.SKUDesc)),'')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC
																					)
																			,	(
																					SELECT
																						CASE
																							WHEN LineItem.TranDetailType = 0 THEN 
																																	(
																																		SELECT TOP (1)
																																			NULLIF(LTRIM(RTRIM(A.SpanishDesc)),'')
																																		FROM
																																			ItemReplen A
																																		WHERE
																																			A.SKU = LineItem.SKU
																																		ORDER BY
																																			A.SpanishDesc ASC
																																	)
																							ELSE
																								(
																									SELECT TOP(1)
																										NULLIF(LTRIM(RTRIM(A.[Description])),'')
																									FROM
																										Fee A
																									WHERE
																										A.FeeCode = LineItem.TranDetailMiscAcct
																									ORDER BY
																										A.[Description] ASC
																								)
																						END
																				)
																			) AS 'text()'
																		FOR XML PATH('Description')
																		, ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.UnitCostPrice AS 'text()'
																		FOR XML PATH ('UnitCostPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.UnitListPrice AS 'text()'
																		FOR XML PATH ('UnitListPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			ABS(LineItem.ExtendedAmt) AS 'text()'
																		FOR XML PATH ('RegularSalesUnitPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			ABS(LineItem.ExtendedAmt) AS 'text()'
																		FOR XML PATH ('ActualSalesUnitPrice')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			--'VALOR DE ITEMID' AS 'text()'
																			LineItem.AbsoluteExtendedAmount AS 'text()'
																		FOR XML PATH('ExtendedAmount')
																		, ELEMENTS
																		,TYPE
																	)
																	
																	,(
																		SELECT
																			LineItem.UnitDiscountAmount AS 'text()'
																		FOR XML PATH ('UnitDiscountAmount')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			LineItem.ExtendedDiscountAmount AS 'text()'
																		FOR XML PATH ('ExtendedDiscountAmount')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																			ELSE LineItem.PrimaryUOM END AS '@PrimaryUnitOfMeasureCode'
																			, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN ABS(CAST(LineItem.PrimaryUnitsCustomerOrder as Decimal (10,4)))
																			  ELSE 
																				 CASE WHEN LineItem.AlternativeUOMConvert IS NOT NULL AND LineItem.AlternativeUOMConvert<>0 
																				 THEN  ABS(CAST((LineItem.Quantity /LineItem.AlternativeUOMConvert) as decimal (10,4)))
																				 ELSE 0--''
																				 END 
																			  END AS '@PrimaryUnits'
																			, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOMConvert 
																			  ELSE 
																				 CASE WHEN LineItem.AlternativeUOMConvert IS NOT NULL AND LineItem.AlternativeUOMConvert<>0 
																				 THEN  CAST(CAST(1 AS DECIMAL(8,4))/LineItem.AlternativeUOMConvert AS DECIMAL(25,10))
																				 ELSE 0--''
																				 END 
																			  END AS '@Units'
																			, CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																			  ELSE LineItem.UOM
																			  END AS '@UnitOfMeasureCode'
																			, ABS(LineItem.Quantity) AS 'text()'
																	   FOR XML PATH('Quantity')
																	   , ELEMENTS
																	   , TYPE
																	)
																	,(
																		SELECT
																			'false' AS '@PackDownFlag'
																			,ISNULL(
																				(
																					SELECT TOP (1)
																						A.ZoneId + ' ' + A.Bays
																					FROM
																						LocationSKU A
																					WHERE 
																						A.SKU = LineItem.SKU
																				)
																			, '0')
																		FOR XML PATH ('SellingLocation')
																		,ELEMENTS
																		,TYPE
																	)
																	,(
																		SELECT
																			
																			(
																				SELECT TOP (1)
																					(
																						SELECT
																							ISNULL(
																									(
																										SELECT TOP (1)
																											C.FirstName + ' ' + C.LastName
																										FROM
																											Employee C
																										WHERE
																											C.EmployeeNo = O.EmployeeNo
																									)
																							,'?')
																					) AS '@OperatorName'
																					, O.EmployeeNo AS 'text()'
																				FROM
																					OrderHeader O
																				WHERE
																					O.OrderNumber = LineItem.SpecialOrderNumber
																				ORDER BY O.EmployeeNo ASC
																				FOR XML PATH ('AssociateID')
																				,ELEMENTS
																				,TYPE
																			)
																		FOR XML PATH ('Associate')
																		,ELEMENTS
																		,TYPE
																	)

																	,(
																		SELECT
																			@BusinessCustomerFlagValue AS '@BusinessCustomerFlag'
																			,(
																				SELECT TOP (1)
																					CASE
																						WHEN A.[Type] = 'D' THEN 'A/R'
																						ELSE ' '
																					END
																				FROM
																					Customer A
																				WHERE
																					A.CustomerID = LineItem.TranTotalAccountNo
																					--
																				ORDER BY
																					A.CustomerID ASC
																			) AS '@CustomerType'
																			,(
																				SELECT
																					ISNULL(
																							(
																								SELECT TOP (1)
																									'0'
																								FROM
																									OrderDetail A
																								WHERE
																									A.OrderNumber = LineItem.SpecialOrderNumber AND A.OrdSeqNumber = LineItem.SequenceNo
																									AND A.DeliveryType = 0
																								ORDER BY
																									A.DeliveryType ASC
																							)
																					,'1')
																			) AS '@DirectFlag'
																			,(
																				SELECT
																					CASE	
																						WHEN LineItem.TranTotalRegisterID = 80 THEN '11110000004'
																						ELSE LineItem.TranTotalAccountNo
																					END
																				FOR XML PATH('CustomerID')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					ISNULL(NULLIF(LTRIM(RTRIM(
																												(
																													SELECT TOP (1)
																														A.SoldBusName
																													FROM
																														OrderHeader A
																													WHERE
																														A.OrderNumber = LineItem.SpecialOrderNumber
																												)
																											 )
																										)
																								,'')
																							,CASE WHEN h.Name != '' and h.Name is not null THEN h.Name ELSE '*SIN NOMBRE*' END)
																					AS 'text()'
																				FOR XML PATH('Name')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					CASE
																						WHEN  NULLIF(LTRIM(RTRIM(LineItem.TranTotalTelephone)),'') IS NOT NULL THEN
																							(
																								SELECT
																									'Home' AS '@TypeCode'
																									,(
																										SELECT
																											LineItem.TranTotalTelephone as "text()" 
																										FOR XML PATH('FullTelephoneNumber')
																										, ELEMENTS
																										, TYPE
																									)
																								FOR XML PATH('Telephone')
																								, ELEMENTS
																								, TYPE
																							)
																					END
																			)
																			,(
																			--Verificar que el hijo retorne un resultado, de lo contrario no mostrar padre e Hijo
																				SELECT
																					CASE
																						WHEN NULLIF(LTRIM(RTRIM(
																							(
																								SELECT TOP (1)
																									A.AltPhoneNumb as "text()"
																								FROM
																									dbo.OrderHeader A
																									INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																								WHERE
																									B.OrderNumber = LineItem.SpecialOrderNumber
																									AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																									AND B.SKU = LineItem.SKU
																								ORDER BY
																									B.OrdSeqNumber ASC
																							) 
																							)),'') IS NOT NULL
																							THEN
																								(
																									SELECT
																										'Office' AS '@TypeCode'
																										,(
																											SELECT
																												(
																													SELECT TOP (1)
																														A.AltPhoneNumb as "text()"
																													FROM
																														dbo.OrderHeader A
																														INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																													WHERE
																														B.OrderNumber = LineItem.SpecialOrderNumber
																														AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																														AND B.SKU = LineItem.SKU
																													ORDER BY
																														B.OrdSeqNumber ASC
																												) 
																											FOR XML PATH('FullTelephoneNumber')
																											, ELEMENTS
																											, TYPE
																										)
																									FOR XML PATH('Telephone')
																									, ELEMENTS
																									, TYPE
																								)
																					END
																			)
																			,(
																				SELECT
																					(		
																						SELECT
																							ISNULL(NULLIF(LTRIM(RTRIM(
																														(
																															SELECT TOP (1)
																																A.ShipBusName
																															FROM
																																OrderHeader A
																															WHERE
																																A.OrderNumber = LineItem.SpecialOrderNumber
																														)
																													 )
																												)
																										,'')
																									,CASE WHEN h.ShipBusName != '' and h.ShipBusName is not null THEN h.ShipBusName ELSE '*SIN NOMBRE*' END)
																							AS 'text()'
																						FOR XML PATH('Name')																						
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																								ISNULL(NULLIF(LTRIM(RTRIM(
																															(
																																SELECT TOP (1)
																																	A.ReceiveName
																																FROM
																																	OrderHeader A
																																WHERE
																																	A.OrderNumber = LineItem.SpecialOrderNumber
																															)
																														 )
																													)
																											,'')
																										,CASE WHEN h.ShipBusName != '' and h.ShipBusName is not null THEN h.ShipBusName ELSE '*SIN NOMBRE*' END)
																								AS 'text()'
																						FOR XML PATH('AlternateName')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToAdd1
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToAdd1 END as "text()"
																						FOR XML PATH('AddressLine')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToCity
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToCity END as "text()"
																						FOR XML PATH('City')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToAdd2
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToAdd2 END as "text()"
																						FOR XML PATH('Neighborhood')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToState
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToState END as "text()" 
																						FOR XML PATH('State')
																						, ELEMENTS
																						, TYPE
																					)
																					,(
																						SELECT
																							CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToZip
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h.ShipToZip END as "text()" 
																						FOR XML PATH('PostalCode')
																						, ELEMENTS
																						, TYPE
																					)
																			
																					,(
																						SELECT
																							CASE
																								WHEN EXISTS
																								(
																									SELECT TOP (1)
																										CASE
																											WHEN LineItem.RegisterID = 80 THEN 
																												SUBSTRING(LineItem.TranTotalShipMidInit,1, (CHARINDEX('|', LineItem.TranTotalShipMidInit) - 1))
																											ELSE
																												--ISNULL(
																														(
																															SELECT TOP (1)
																																E.Latitude
																															FROM
																																OrderHeader E
																															WHERE
																																E.OrderNumber = LineItem.SpecialOrderNumber
																															ORDER BY
																																E.Latitude ASC
																														)
																												--, ' ')
																										END
																								) THEN
																									(
																										SELECT
																											(
																												SELECT TOP (1)
																													CASE
																														WHEN LineItem.RegisterID = 80 THEN 
																															SUBSTRING(LineItem.TranTotalShipMidInit,1, (CHARINDEX('|', LineItem.TranTotalShipMidInit) - 1))
																														ELSE
																															--ISNULL(
																																	(
																																		SELECT TOP (1)
																																			E.Latitude
																																		FROM
																																			OrderHeader E
																																		WHERE
																																			E.OrderNumber = LineItem.SpecialOrderNumber
																																		ORDER BY
																																			E.Latitude ASC
																																	)
																															--, ' ')
																													END
																											)
																										FOR XML PATH('Latitude')
																										, ELEMENTS
																										, TYPE
																									)
																							END
																					)
																					,(
																						SELECT
																							(
																								SELECT
																									CASE
																										WHEN EXISTS
																											(
																												SELECT TOP (1)
																													CASE
																														WHEN LineItem.RegisterID = 80 THEN 
																															SUBSTRING(LineItem.TranTotalShipMidInit, CHARINDEX('|', LineItem.TranTotalShipMidInit) + 1, LEN(LineItem.TranTotalShipMidInit))

																														ELSE
																															--ISNULL(
																																	(
																																		SELECT TOP (1)
																																			E.[Length]
																																		FROM
																																			OrderHeader E
																																		WHERE
																																			E.OrderNumber = LineItem.SpecialOrderNumber
																																		ORDER BY
																																			E.Latitude ASC
																																	)
																															--, ' ')
																													END
																											) THEN
																												(
																													SELECT TOP (1)
																														CASE
																															WHEN LineItem.RegisterID = 80 THEN 
																																SUBSTRING(LineItem.TranTotalShipMidInit, CHARINDEX('|', LineItem.TranTotalShipMidInit) + 1, LEN(LineItem.TranTotalShipMidInit))
																															ELSE
																																--ISNULL(
																																		(
																																			SELECT TOP (1)
																																				E.[Length]
																																			FROM
																																				OrderHeader E
																																			WHERE
																																				E.OrderNumber = LineItem.SpecialOrderNumber
																																			ORDER BY
																																				E.Latitude ASC
																																		)
																																--, ' ')
																														END
																												)
																									END
																							)
																						FOR XML PATH('Longitude')
																						, ELEMENTS
																						, TYPE
																					)
																				FOR XML PATH('Address')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT CASE WHEN LineItem.RegisterId = 80 THEN
																					(
																						SELECT
																							CASE ISDATE( SUBSTRING(LineItem.SerialNumber, 1, 4) + '-' + SUBSTRING(LineItem.SerialNumber, 6, 2) + '-' + SUBSTRING(LineItem.SerialNumber, 9, 2))
																								WHEN 1 THEN	
																									SUBSTRING(LineItem.SerialNumber, 1, 4) + '-' + SUBSTRING(LineItem.SerialNumber, 6, 2) + '-' + SUBSTRING(LineItem.SerialNumber, 9, 2) + 'T00:00:00'
																								ELSE
																									CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')
																							END
																					)
																					ELSE
																					(
																							
																						SELECT
																						ISNULL((
																						SELECT TOP (1)
																							CONCAT(ISNULL(B.DELDATE,CAST(GETDATE() AS DATE)), 'T00:00:00')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.SequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC)
																						,CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')	)
																					)
																					END
																					FOR XML PATH('PreferredDateTime')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					LineItem.PickTake as "text()"
																				FOR XML PATH('Method')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT --validar que el maximo de caracteres sea 256 y agregar espacio despues de cada coma
																					(
																						SELECT
																							ISNULL(
																								SUBSTRING(
																											STUFF(
																													(
																														SELECT
																															', ' + A.VendorUPC
																														FROM
																															ItemXref A
																														WHERE
																															A.SKU = LineItem.SKU
																														FOR XML PATH('')
																													)
																											, 1,2,''),
																								1, 256)
																							,' ')
																					)
																				FOR XML PATH('Notes')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT TOP (1)
																							B.UnitPrice AS 'text()'
																						FROM
																							OrderDetail B 
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.SKU IS NULL --
																							AND CHARINDEX('(FMS)',B.SKUDesc) > 0 --
																							AND B.MiscAcct = 1 --
																						ORDER BY
																							B.OrdSeqNumber ASC
																					) 
																				FOR XML PATH('ShippingFee')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT TOP (1)
																							A.DistanceShip as "text()"
																						FROM
																							dbo.OrderHeader A
																							INNER JOIN dbo.OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.OrdSeqNumber ASC
																					) 
																				FOR XML PATH('DeliveryDistance')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					(
																						SELECT TOP (1)
																							B.UnitPrice AS 'text()'
																						FROM
																							OrderDetail B 
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							--AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																							--AND ISNULL(B.SKU,0) = LineItem.SKU

																							AND B.SKU IS NULL --
																							AND CHARINDEX('(FMS)',B.SKUDesc) > 0 --
																							AND B.MiscAcct = 2 --
																						ORDER BY
																							B.OrdSeqNumber ASC
																					) 
																				FOR XML PATH('HandlingFee')
																				, ELEMENTS
																				, TYPE
																			)
																			,(
																				SELECT
																					CASE
																						WHEN
																						(
																							SELECT TOP (1)
																								NULLIF(LTRIM(RTRIM(A.ReferenceNo__1)),'')
																							FROM
																								OrderHeader A
																								INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																							WHERE
																								B.OrderNumber = LineItem.SpecialOrderNumber
																								AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																								AND B.SKU = LineItem.SKU
																							ORDER BY
																								B.OrdSeqNumber ASC
																						) IS NOT NULL THEN
																							(
																								SELECT TOP (1)
																									A.ReferenceNo__2 AS '@Details'
																									, A.ReferenceNo__1 as 'text()'
																								FROM
																									OrderHeader A
																									INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																								WHERE
																									B.OrderNumber = LineItem.SpecialOrderNumber
																									AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																									AND B.SKU = LineItem.SKU
																								ORDER BY
																									B.OrdSeqNumber ASC
																								FOR XML PATH('Zone')
																								, ELEMENTS
																								, TYPE
																							)
																						ELSE
																							(
																								SELECT
																									'SIN ZONA'
																								FOR XML PATH('Zone')
																								, ELEMENTS
																								, TYPE
																							)
																					END
																			)
																			,(
																				SELECT
																					(
																						SELECT
																							ISNULL(
																									(
																										SELECT TOP (1)
																											C.StoreDelivery
																										FROM
																											DomFulf C
																											INNER JOIN Store D ON C.StoreRem = D.StoreNumber
																										WHERE
																											C.OrderNo = LineItem.SpecialOrderNumber
																										ORDER BY
																											C.StoreRem ASC
																									)
																							, 
																								(
																									SELECT TOP (1)
																										A.StoreNumber
																									FROM
																										Store A
																									WHERE
																										A.StoreType = 'true'
																									ORDER BY
																										A.StoreNumber ASC
																								)
																							)
																					)
																				FOR XML PATH('CustomerOrderStoreID')
																				, ELEMENTS
																				, TYPE
																			)
																		FOR XML PATH('Delivery')
																		, ELEMENTS
																		, TYPE
																	)																	
																FOR XML PATH('CustomerOrderForDelivery')
															)
													ELSE NULL
											

												END
											
												
										ELSE
											(
												SELECT 
													(
													SELECT 
														( 
															-- HGU INI 2016-08-26 Se cambió la lógica de COD
															--SELECT 'COD' AS '@OrderType'
															SELECT
																CASE LineItem.TypeOfSale
																	WHEN  3 THEN	'VALIDACION COD'
																	WHEN 40 THEN	'COD'
																	ELSE			'NO DEFINIDO'
																END
																AS '@OrderType'
															-- HGU FIN 2016-08-26
																, (
																	SELECT
																				LineItem.RowNumber AS 'text()'
																			
																		FOR XML PATH('SpecialOrderSequenceNumber')
																		, ELEMENTS
																		, TYPE
																)
																, (SELECT
																			CASE WHEN sale.IsAnAlternativeItem = 0 
																			THEN sale.ItemID
																			ELSE sale.PrimarySKU
																			END AS 'text()'
																		FOR XML PATH('ItemID')
																		, ELEMENTS
																		,TYPE
																	)
																, Sale.MerchandiseHierarchy
																,(SELECT
																	CASE WHEN sale.IsAnAlternativeItem = 1
																	THEN (SELECT sale.SKU AS 'text()' FOR XML PATH('AlternateItemID')
																	, ELEMENTS
																	,TYPE)
																	END																			
																)
																, Sale.UnitCostPrice
																, Sale.UnitListPrice
																, Sale.RegularSalesUnitPrice
																, Sale.ActualSalesUnitPrice
																, Sale.ExtendedAmount
																, Sale.UnitDiscountAmount
																, Sale.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 0
																			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(CAST(1 AS DECIMAL(8,4))/LineItem.AlternativeUOMConvert AS DECIMAL(25,10))
																			  ELSE 0
																			  END 
																		  END AS "@Units"
																		, Sale.Quantity AS "text()"
																	FOR XML PATH('Quantity')
																	, ELEMENTS
																	, TYPE
																),
																
																	(
																		SELECT CASE WHEN LineItem.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		  ELSE LineItem.UOM
																		  END
																		  AS '@UnitOfMeasureCode',
																		  0.00
																		  AS '@Units',
																			(
																				SELECT
																					(
																						SELECT
																								(
																									SELECT
																										ISNULL(
																												(
																													SELECT TOP (1)
																														ISNULL(
																																(
																																	SELECT TOP (1)
																																		(
																																			SELECT dbo.fnIF194_EvaluarCantidadPendiente(E.DocNumber,E.SequenceNo,E.SKU, @EsOrdenDeEcommerce)
																																		) AS FunctionResult
																																	FROM
																																		dbo.OrderDetail B
																																		
																																		INNER JOIN dbo.OrderHeader C ON C.OrderNumber = B.OrderNumber
																																		
																																		INNER JOIN dbo.TranTotalDOM D ON D.StoreNumber = C.OrigStore
																																									AND D.TranDate = C.OrigDate
																																									AND D.RegisterID = C.OrigRegID
																																									AND D.TranNumber = C.OrigTranNo
																																		
																																		INNER JOIN dbo.TranDetailDOM E ON E.DocNumber = D.DocNumber
																																									AND E.SKU = B.SKU
																																									AND E.SequenceNo = (CASE
																																														WHEN B.AtForQty <> 0 THEN B.AtForQty
																																														ELSE B.OrdSeqNumber
																																														END)

																																	WHERE
																																		--
																																		B.OrderNumber = LineItem.SpecialOrderNumber
																																		AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																																		AND B.SKU = LineItem.SKU

																																		AND(
																																				LineItem.TranTotalTypeOfSale = 88
																																				OR LineItem.TranTotalTypeOfSale = 21
																																			)
																																		AND (LineItem.ReferenceNo__3 <> '')																																		
																																		AND C.OrigStore <> 0
																																		AND C.OrigRegID <> 0
																																		AND C.OrigTranNo <> 0
																																	ORDER BY
																																		E.DocNumber ASC
																																)
																														,0.0) AS QResult
																													FROM
																														dbo.OrderDetail B
																													WHERE
																														--Posteriormente, buscar el primer registro de OrderDetail en donde:
																														B.OrderNumber = LineItem.SpecialOrderNumber
																														AND B.OrdSeqNumber = LineItem.TranDetailSequenceNo
																														AND B.SKU = LineItem.SKU

																														AND
																															(
																																LineItem.TranTotalTypeOfSale = 88
																																OR LineItem.TranTotalTypeOfSale = 21
																															)
																														AND LineItem.ReferenceNo__3 <> ''
																													ORDER BY
																														QResult ASC
																												)
																											, 0.0) --asignar el valor de TranDetail.PickTake a ORDERQUANTITY

																									WHERE
																										(
																										LineItem.Quantity < 0
																										AND LineItem.SpecialOrderNumber <> 0
																										AND ( LineItem.TypeOfSale > 10
																											AND LineItem.TypeOfSale < 20 )
																										)
																										OR( 
																											LineItem.Quantity < 0
																											AND LineItem.SpecialOrderNumber <> 0
																											AND ( LineItem.TypeOfSale = 88
																												OR LineItem.TypeOfSale = 21 )
																										)
																								)
																					)
																				WHERE
																					EXISTS
																					(
																						SELECT TOP (1)
																							A.TypeOfSale
																						FROM
																							dbo.TranDetailDOM A
																						WHERE
																							----START of Match the LineItem
																							A.TranNumber = LineItem.TranNumber
																							AND A.TranDate = LineItem.TranDate
																							AND A.RegisterID = LineItem.RegisterID
																							AND A.SKU = LineItem.SKU
																							----END of Match the LineItem
																							AND (A.Quantity <> 0)
																							AND (A.DocNumber <> 0)
																							AND (A.TypeOfSale <= 10
																								OR A.TypeOfSale >= 20
																								AND A.TypeOfSale <> 88)
																							AND (A.PickTake = 'D'
																								OR A.PickTake = 'P')
																							OR (
																								(A.TypeOfSale = 88
																								AND LineItem.ReferenceNo__3 <> '')
																								OR
																								(A.TypeOfSale = 21
																								AND LineItem.ReferenceNo__3 <> '')
																								)
																							AND LineItem.RegisterID <> 81
																							AND (LineItem.PickTake = 'D'
																								OR LineItem.PickTake = 'P')
																						ORDER BY
																							A.TranNumber ASC
																					)
																					
																			)
																		FOR XML PATH('OrderQuantity')
																		, ELEMENTS
																		, TYPE
																	)
																, (
																	SELECT 
																		m.MethodCode AS '@MethodCode'
																		, m.ID AS 'SequenceNumber'
																		, m.PreviousPrice
																		, m.NewPrice
																		, CASE 
																			WHEN LineItem.PriceAllow <> 0 THEN 
																				CASE 
																					WHEN EXISTS( 
																								SELECT TOP (1) 
																									PromoID 
																								FROM 
																									dbo.PromoItem pi 
																									JOIN dbo.Coupon c ON pi.PromoID = c.CouponID
																									JOIN dbo.ItemPLU ip	ON pi.SKU = ip.SKU
																								WHERE 
																									c.CouponType = 10 
																									AND pi.StatusCode = 'A' 
																									AND LineItem.SKU = pi.SKU
																								ORDER BY
																									PromoID ASC
																							   ) THEN 
																									( 
																										SELECT TOP (1) 
																											PromoID 
																										FROM 
																											dbo.PromoItem pi 
																											JOIN dbo.Coupon c ON pi.PromoID = c.CouponID
																											JOIN dbo.ItemPLU ip ON pi.SKU = ip.SKU
																										WHERE 
																											c.CouponType = 10 
																											AND pi.StatusCode = 'A' 
																											AND	LineItem.SKU = pi.SKU
																										ORDER BY
																											PromoID ASC
																									)
																					ELSE NULL 
																				END
																			WHEN LineItem.CouponNumber <> '' THEN 
																				CASE 
																					WHEN EXISTS( 
																								SELECT TOP (1) 
																									PromoID 
																								FROM 
																									dbo.PromoHdr 
																								WHERE 
																									PromoID = LineItem.CouponNumber
																								ORDER BY
																									PromoID ASC
																								) THEN 
																									( 
																										SELECT TOP (1) 
																											PromoID 
																										FROM 
																											dbo.PromoHdr 
																										WHERE 
																											PromoID = LineItem.CouponNumber
																										ORDER BY
																											PromoID ASC
																									)
																					ELSE NULL 
																				END
																			WHEN LineItem.SecCouponNo <> '' THEN 
																				CASE 
																					WHEN EXISTS(
																								SELECT TOP (1) 
																									PromoID 
																								FROM 
																									dbo.PromoDet 
																								WHERE 
																									LineItem.SecCouponNo = PromoDet.PromoRel
																								ORDER BY
																									PromoID ASC
																								) THEN 
																									CASE 
																										WHEN EXISTS(
																													SELECT TOP (1) 
																														PromoID 
																													FROM 
																														dbo.PromoHdr 
																													WHERE 
																														PromoID = (
																																	SELECT TOP (1) 
																																		PromoID 
																																	FROM 
																																		dbo.PromoDet 
																																	WHERE 
																																		LineItem.SecCouponNo = PromoDet.PromoRel
																																	ORDER BY
																																		PromoID ASC
																																	)
																													ORDER BY
																														PromoID ASC
																													) THEN 
																														(
																															SELECT TOP (1) 
																																PromoID 
																															FROM 
																																dbo.PromoHdr 
																															WHERE 
																																PromoID = (
																																			SELECT TOP (1) 
																																				PromoID 
																																			FROM 
																																				dbo.PromoDet 
																																			WHERE 
																																				LineItem.SecCouponNo = PromoDet.PromoRel
																																			ORDER BY
																																				PromoID ASC
																																			)
																															ORDER BY
																																PromoID ASC
																														)
																										ELSE NULL 
																									END
																					ELSE NULL 
																				END
																			ELSE m.PromotionID
																		  END AS 'PromotionID'
																		, CASE 
																			WHEN Sale.PriceOvrType > 0 THEN Sale.PriceOvrType
																			ELSE NULL 
																		  END 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 = m.RecID
																								  --AND m.SequenceNumber = RetailPriceModifier.SequenceNumber
																	WHERE 
																		m.RecID = Sale.RecID
																	FOR XML Path('RetailPriceModifier')
																	,TYPE
																	--FOR XML AUTO
																	--    , TYPE
																)
																,(	
																	SELECT 
																		REPLICATE('0',11-LEN(RTRIM(h.CustomerID))) + RTRIM(h.CustomerID) AS 'CustomerID'
																		 , CASE 
																			WHEN h.Name <> '' THEN h.Name 
																			ELSE '.' 
																		   END  AS 'Name'
																		 , (
																			SELECT 
																				'Home' AS '@TypeCode'
																				, h1.Telephone AS 'FullTelephoneNumber'
																			WHERE 
																				h1.Telephone <> '' AND h1.Telephone IS NOT NULL
																			FOR XML PATH('Telephone')
																			, ELEMENTS
																			, TYPE
																		)
																		, (
																			SELECT 
																				CASE WHEN LineItem.SpecialOrderNumber >0   AND h.TillID != 80 
																				  THEN 
																						(SELECT TOP (1)
																									E.ShipBusName
																								FROM OrderHeader E 
																								WHERE
																									E.OrderNumber = LineItem.SpecialOrderNumber )
																				  ELSE  h1.ShipBusName
																				  END AS 'Name'  
																				, CASE WHEN LineItem.SpecialOrderNumber >0   AND h.TillID != 80 
																				  THEN 
																						(SELECT TOP (1)
																									E.ShipToAdd1
																								FROM OrderHeader E 
																								WHERE
																									E.OrderNumber = LineItem.SpecialOrderNumber )


																				  ELSE  h1.Address 
																				  END AS 'AddressLine'
																					
																				, CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToCity
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																					ELSE h1.City
																				  END AS 'City'
																				, CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80  THEN 
																							(SELECT TOP (1)
																										E.ShipToState
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h1.State
																				  END AS 'State'
																				
																				, CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToZip
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h1.PostalCode
																				  END AS 'PostalCode'
																				  
																					
																			WHERE 
																				h1.Address <> ''
																			FOR XML PATH('Address')
																			, ELEMENTS
																			, TYPE
																		)
																		, (
																				
																			SELECT CASE WHEN LineItem.RegisterId = 80 THEN
																					(
																						SELECT [text()] = CONVERT(DATETIME, ISNULL(
																													(
																														SELECT TOP (1) 
																															STUFF(CONVERT(varchar(25), GETDATE(), 120), 6,5, CONCAT(SUBSTRING(td.SerialNumber, 9, 2),'-', SUBSTRING(td.SerialNumber, 6, 2))) AS Result
																														FROM 
																															#ARTSSaleDemand arts 
																															INNER JOIN dbo.TranDetail td ON arts.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 sale.PickTake = 'D'
																														ORDER BY
																															Result ASC
																													)
																												   , GETDATE())
																												)
																												
																					)
																					ELSE
																					(
																							
																						SELECT
																						ISNULL((
																						SELECT TOP (1)
																							CONCAT(ISNULL(B.DELDATE,CAST(GETDATE() AS DATE)), 'T00:00:00')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.SequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC)
																						,CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')	)
																					)
																					END
																			FOR XML PATH('PreferredDateTime')
																			, TYPE 
																		)
																		, 'Delivery' AS 'Method'
																		, (
																				SELECT
																					(
																						SELECT
																							ISNULL(
																								SUBSTRING(
																											STUFF(
																													(
																														SELECT
																															', ' + A.VendorUPC
																														FROM
																															ItemXref A
																														WHERE
																															A.SKU = Sale.SKU
																														FOR XML PATH('')
																													)
																											, 1,2,''),
																								1, 256)
																							,' ')
																					)
																				FOR XML PATH('Notes')
																				, ELEMENTS
																				, TYPE
																			)
																	    , (
																			SELECT TOP (1) 
																				DistanceShip --Se agrego TOP 1 ya que regresaba mas de un registro
																			FROM 
																				dbo.OrderHeader oh 
																			WHERE 
																				h1.TranNumber = oh.FirstTranNo 
																			ORDER BY
																				DistanceShip ASC
																		) AS 'DeliveryDistance' 
																		, (
																			SELECT 
																				CASE 
																					WHEN oh.ReferenceNo__2 <> '' THEN oh.ReferenceNo__2 
																					ELSE 'SIN ZONA' 
																				END AS '@Details'
																				, CASE 
																					WHEN oh.ReferenceNo__1 <> '' THEN oh.ReferenceNo__1 
																					ELSE 'SIN ZONA' 
																				  END AS "text()"
																			FROM 
																				dbo.OrderHeader oh 
																			WHERE 
																				Sale.SpecialOrderNumber = oh.OrderNumber
																			FOR XML PATH('Zone')
																			, TYPE
																		) 
																	FOR XML PATH('Delivery')
																	, ELEMENTS
																	, TYPE
																)
																, h.InvoiceNo AS 'Invoice' 
															FROM 
																#ARTSSaleDemandLineItemDemand AS Sale
															WHERE 
																Sale.PickTake = 'D' 
																AND Sale.RecID = LineItem.RecID--Delivery Method
															FOR XML PATH('SaleForDelivery')
															, TYPE
															, ELEMENTS
														)
														,-----------------------------------------
														( 
															SELECT 
																(
																	SELECT
																			LineItem.RowNumber AS 'text()'
																	FOR XML PATH('SpecialOrderSequenceNumber')
																	, ELEMENTS
																	, TYPE
																)
																, (SELECT
																			CASE WHEN sale.IsAnAlternativeItem = 0 
																			THEN sale.ItemID
																			ELSE sale.PrimarySKU
																			END AS 'text()'
																		FOR XML PATH('ItemID')
																		, ELEMENTS
																		,TYPE
																	)
																, Sale.MerchandiseHierarchy
																,(SELECT
																	CASE WHEN sale.IsAnAlternativeItem = 1
																	THEN (SELECT sale.SKU AS 'text()' FOR XML PATH('AlternateItemID')
																	, ELEMENTS
																	,TYPE)
																	END																			
																)
																, Sale.UnitCostPrice
																, Sale.UnitListPrice
																, Sale.RegularSalesUnitPrice
																, Sale.ActualSalesUnitPrice
																, Sale.ExtendedAmount
																, Sale.UnitDiscountAmount
																, Sale.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 0
																			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(CAST(1 AS DECIMAL(8,4))/LineItem.AlternativeUOMConvert AS DECIMAL(25,10))
																			  ELSE 0--''
																			  END 
																		  END AS "@Units"
																		, Sale.Quantity AS "text()"
																	FOR XML PATH('Quantity')
																	, ELEMENTS
																	, TYPE
																)
																, (
																	SELECT 
																		m.MethodCode AS '@MethodCode'
																		, m.ID AS 'SequenceNumber'
																		, m.PreviousPrice
																		, m.NewPrice
																		, CASE 
																			WHEN LineItem.PriceAllow <> 0 THEN 
																				CASE 
																					WHEN EXISTS( 
																								SELECT TOP (1)
																									PromoID 
																								FROM 
																									dbo.PromoItem pi 
																									JOIN dbo.Coupon c ON pi.PromoID = c.CouponID
																									JOIN dbo.ItemPLU ip ON pi.SKU = ip.SKU
																								WHERE 
																									c.CouponType = 10 
																									AND pi.StatusCode = 'A' 
																									AND LineItem.SKU = pi.SKU
																								ORDER BY
																									PromoID ASC
																							   ) THEN 
																									( 
																										SELECT TOP (1) 
																											PromoID 
																										FROM 
																											dbo.PromoItem pi 
																											JOIN dbo.Coupon c ON pi.PromoID = c.CouponID
																											JOIN dbo.ItemPLU ip ON pi.SKU = ip.SKU
																										WHERE 
																											c.CouponType = 10 
																											AND pi.StatusCode = 'A' 
																											AND LineItem.SKU = pi.SKU
																										ORDER BY
																											PromoID ASC
																									)
																					ELSE NULL 
																				END
																			WHEN LineItem.CouponNumber <> '' THEN 
																				CASE 
																					WHEN EXISTS( 
																								SELECT TOP (1) 
																									PromoID 
																								FROM 
																									dbo.PromoHdr 
																								WHERE 
																									PromoID = LineItem.CouponNumber
																								ORDER BY
																									PromoID ASC
																							   ) THEN 
																									( 
																										SELECT TOP (1) 
																											PromoID 
																										FROM 
																											dbo.PromoHdr 
																										WHERE 
																											PromoID = LineItem.CouponNumber
																										ORDER BY
																											PromoID ASC
																									)
																					ELSE NULL 
																				END
																			WHEN LineItem.SecCouponNo <> '' THEN 
																				CASE 
																					WHEN EXISTS(
																								SELECT TOP (1) 
																									PromoID 
																								FROM 
																									dbo.PromoDet 
																								WHERE 
																									LineItem.SecCouponNo = PromoDet.PromoRel
																								ORDER BY
																									PromoID ASC
																							   ) THEN 
																									CASE 
																										WHEN EXISTS(
																													SELECT TOP (1) 
																														PromoID 
																													FROM 
																														dbo.PromoHdr 
																													WHERE 
																														PromoID = (
																																	SELECT TOP (1) 
																																		PromoID 
																																	FROM 
																																		dbo.PromoDet 
																																	WHERE 
																																		LineItem.SecCouponNo = PromoDet.PromoRel
																																	ORDER BY
																																		PromoID ASC
																																  )
																													ORDER BY
																														PromoID ASC
																												   ) THEN 
																														(
																															SELECT TOP (1) 
																																PromoID 
																															FROM 
																																dbo.PromoHdr 
																															WHERE 
																																PromoID = (
																																			SELECT TOP (1) 
																																				PromoID 
																																			FROM 
																																				dbo.PromoDet 
																																			WHERE 
																																				LineItem.SecCouponNo = PromoDet.PromoRel
																																			ORDER BY
																																				PromoID ASC
																																		  )
																															ORDER BY
																																PromoID ASC
																														)
																										ELSE NULL 
																									END
																					ELSE NULL 
																				END
																			ELSE m.PromotionID
																		  END AS 'PromotionID'
																		, CASE 
																			WHEN Sale.PriceOvrType > 0 THEN Sale.PriceOvrType
																			ELSE NULL 
																		  END 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 = m.RecID
																								  --AND m.SequenceNumber = RetailPriceModifier.SequenceNumber
																	WHERE 
																		m.RecID = Sale.RecID
																	FOR XML Path('RetailPriceModifier')
																	,TYPE
																	--FOR XML AUTO
																	--    , TYPE
																)
																,(
																	SELECT 
																		REPLICATE('0',11-LEN(RTRIM(h.CustomerID))) + RTRIM(h.CustomerID) AS 'CustomerID'
																		, CASE 
																			WHEN h.Name <> '' THEN h.Name 
																			ELSE '.' 
																		END  AS 'Name'
																		, (
																			SELECT 
																				'Home' AS '@TypeCode'
																				, h1.Telephone AS 'FullTelephoneNumber'
																			WHERE 
																				h1.Telephone <> '' 
																				AND h1.Telephone IS NOT NULL
																			FOR XML PATH('Telephone')
																			, ELEMENTS
																			, TYPE
																		)
																		, (
																			SELECT 
																				CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 
																				  THEN 
																						(SELECT TOP (1)
																									E.ShipBusName
																								FROM OrderHeader E 
																								WHERE
																									E.OrderNumber = LineItem.SpecialOrderNumber )
																				  ELSE  h1.ShipBusName
																				  END AS 'Name'  
																				, CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 
																				  THEN 
																						(SELECT TOP (1)
																									E.ShipToAdd1
																								FROM OrderHeader E 
																								WHERE
																									E.OrderNumber = LineItem.SpecialOrderNumber )


																				  ELSE  h1.Address 
																				  END AS 'AddressLine'
																					
																				, CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToCity
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																					ELSE h1.City
																				  END AS 'City'
																				, CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToState
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h1.State
																				  END AS 'State'
																				
																				, CASE WHEN LineItem.SpecialOrderNumber >0  AND h.TillID != 80 THEN 
																							(SELECT TOP (1)
																										E.ShipToZip
																									FROM OrderHeader E 
																									WHERE
																										E.OrderNumber = LineItem.SpecialOrderNumber )
																							ELSE h1.PostalCode
																				  END AS 'PostalCode'
																			WHERE 
																				h1.Address <> ''
																			FOR XML PATH('Address')
																			, ELEMENTS
																			, TYPE
																		)
																		, (
																				
																			SELECT CASE WHEN LineItem.RegisterId = 80 THEN
																					(
																						SELECT [text()] = CONVERT(DATETIME, ISNULL(
																													(
																														SELECT TOP (1) 
																															STUFF(CONVERT(varchar(25), GETDATE(), 120), 6,5, CONCAT(SUBSTRING(td.SerialNumber, 9, 2),'-', SUBSTRING(td.SerialNumber, 6, 2))) AS Result
																														FROM 
																															#ARTSSaleDemand arts 
																															INNER JOIN dbo.TranDetail td ON arts.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 sale.PickTake = 'D'
																														ORDER BY
																															Result ASC
																													)
																												   , GETDATE())
																												)
																												
																					)
																					ELSE
																					(
																						SELECT
																						ISNULL((
																						SELECT TOP (1)
																							CONCAT(ISNULL(B.DELDATE,CAST(GETDATE() AS DATE)), 'T00:00:00')
																						FROM
																							OrderHeader A
																							INNER JOIN OrderDetail B ON A.OrderNumber = B.OrderNumber
																						WHERE
																							B.OrderNumber = LineItem.SpecialOrderNumber
																							AND B.OrdSeqNumber = LineItem.SequenceNo
																							AND B.SKU = LineItem.SKU
																						ORDER BY
																							B.SKUDesc ASC)
																						,CONCAT(CAST(GETDATE() AS DATE), 'T00:00:00')	)
																					)
																					END
																			FOR XML PATH('PreferredDateTime')
																			, TYPE 
																		)
																		, 'PickUp' AS 'Method'
																		,(
																				SELECT
																					(
																						SELECT
																							ISNULL(
																								SUBSTRING(
																											STUFF(
																													(
																														SELECT
																															', ' + A.VendorUPC
																														FROM
																															ItemXref A
																														WHERE
																															A.SKU = Sale.SKU
																														FOR XML PATH('')
																													)
																											, 1,2,''),
																								1, 256)
																							,' ')
																					)
																				FOR XML PATH('Notes')
																				, ELEMENTS
																				, TYPE
																			)
																	FOR XML PATH('Pickup')
																	, ELEMENTS
																	, TYPE
																 )  
															FROM 
																#ARTSSaleDemandLineItemDemand AS Sale
															WHERE 
																Sale.PickTake = 'P' 
																AND Sale.RecID = LineItem.RecID--Pickup Method
															FOR XML PATH('SaleForPickup')
															, TYPE
															, ELEMENTS
														)
														FOR XML PATH('')
														--,ROOT('')
													)
											)
									END 
									AS XML)
									
								FOR XML PATH('')
								, ELEMENTS
								, TYPE ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
								)
                                , 
								(	
									SELECT
										CASE
											WHEN (LineItem.Quantity < 0 AND NOT (LineItem.TypeOfSale = 88 AND @IsDevolution = 1)) OR (LineItem.Quantity > 0 AND LineItem.TypeOfSale = 88 AND @IsDevolution = 1)  THEN
												(----INICIA <Return>
													SELECT 
														Sale.ItemType AS '@ItemType'
														, (
															SELECT 
																 (SELECT
																			CASE WHEN sale.IsAnAlternativeItem = 0 
																			THEN ABS(sale.ItemID)
																			ELSE ABS(sale.PrimarySKU)
																			END AS 'text()'
																		FOR XML PATH('ItemID')
																		, ELEMENTS
																		,TYPE
																	)
																, Sale.MerchandiseHierarchy
																,(SELECT
																	CASE WHEN sale.IsAnAlternativeItem = 1
																	THEN (SELECT ABS(sale.SKU) AS 'text()' FOR XML PATH('AlternateItemID')
																	, ELEMENTS
																	,TYPE)
																	END																			
																)
																, ABS(Sale.UnitCostPrice) AS 'UnitCostPrice'
																, ABS(Sale.UnitListPrice) AS 'UnitListPrice'
																, ABS(Sale.RegularSalesUnitPrice) AS 'RegularSalesUnitPrice'
																, ABS(Sale.ActualSalesUnitPrice) AS 'ActualSalesUnitPrice'
																, ABS(Sale.ExtendedAmount) AS 'ExtendedAmount'
																, ABS(Sale.UnitDiscountAmount) AS 'UnitDiscountAmount'
																, ABS(Sale.ExtendedDiscountAmount) AS 'ExtendedDiscountAmount'
																, (
																	SELECT
																		CASE WHEN Sale.IsAnAlternativeItem = 0 THEN ABS(Sale.PrimaryUnits)
																		ELSE 
																			CASE WHEN Sale.AlternativeUOMConvert IS NOT NULL AND Sale.AlternativeUOMConvert<>0 
																			THEN  ABS(Sale.Quantity /Sale.AlternativeUOMConvert)
																			ELSE 0
																			END 
																		END AS "@PrimaryUnits"
																		, CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.UnitOfMeasureCode
																		  ELSE Sale.PrimaryUOM
																		  END AS "@UnitOfMeasureCode"
																		, CASE WHEN Sale.IsAnAlternativeItem = 0 THEN ABS(Sale.Units) 
																		  ELSE 
																			  CASE WHEN Sale.AlternativeUOMConvert IS NOT NULL AND Sale.AlternativeUOMConvert<>0 
																			 THEN  CAST(ABS(CAST(1 AS DECIMAL(8,4))/LineItem.AlternativeUOMConvert) AS DECIMAL(25,10))
																			  ELSE 0--''
																			  END 
																		  END AS "@Units"
																		, ABS(Sale.Quantity) AS "text()"
																	FOR XML PATH('Quantity')
																	, ELEMENTS
																	, TYPE
																  )
																, (
																	SELECT 
																		m.MethodCode AS '@MethodCode'
																		, ABS(m.ID) AS 'SequenceNumber'
																		, ABS(m.PreviousPrice) AS 'PreviousPrice'
																		, ABS(m.NewPrice) AS 'NewPrice'
																		, CASE 
																			WHEN LineItem.PriceAllow <> 0 THEN 
																				CASE 
																					WHEN EXISTS( 
																								SELECT TOP (1) 
																									PromoID 
																								FROM 
																									dbo.PromoItem pi 
																									JOIN dbo.Coupon c ON pi.PromoID = c.CouponID
																									JOIN dbo.ItemPLU ip ON pi.SKU = ip.SKU
																								WHERE 
																									c.CouponType = 10 
																									AND pi.StatusCode = 'A' 
																									AND LineItem.SKU = pi.SKU
																								ORDER BY
																									PromoID ASC
																							   ) THEN 
																									( 
																										SELECT TOP (1) 
																											PromoID 
																										FROM 
																											dbo.PromoItem pi 
																											JOIN dbo.Coupon c ON pi.PromoID = c.CouponID
																											JOIN dbo.ItemPLU ip ON pi.SKU = ip.SKU
																										WHERE 
																											c.CouponType = 10 
																											AND pi.StatusCode = 'A' 
																											AND LineItem.SKU = pi.SKU
																										ORDER BY
																											PromoID ASC
																									)
																					ELSE NULL 
																				END
																			WHEN LineItem.CouponNumber <> '' THEN 
																				CASE 
																					WHEN EXISTS( 
																									SELECT TOP (1) 
																										PromoID 
																									FROM 
																										dbo.PromoHdr 
																									WHERE 
																										PromoID = LineItem.CouponNumber
																									ORDER BY
																										PromoID ASC
																							   ) THEN 
																									( 
																										SELECT TOP (1) 
																											PromoID 
																										FROM 
																											dbo.PromoHdr 
																										WHERE 
																											PromoID = LineItem.CouponNumber
																										ORDER BY
																											PromoID ASC
																									)
																					ELSE NULL 
																				END
																			WHEN LineItem.SecCouponNo <> '' THEN 
																				CASE 
																					WHEN EXISTS(
																									SELECT TOP (1) 
																										PromoID 
																									FROM 
																										dbo.PromoDet 
																									WHERE 
																										LineItem.SecCouponNo = PromoDet.PromoRel
																									ORDER BY
																										PromoID ASC
																								) THEN 
																									CASE 
																										WHEN EXISTS(
																														SELECT TOP (1) 
																															PromoID 
																														FROM 
																															dbo.PromoHdr 
																														WHERE 
																															PromoID = (
																																		SELECT TOP (1)
																																			PromoID 
																																		FROM 
																																			dbo.PromoDet 
																																		WHERE 
																																			LineItem.SecCouponNo = PromoDet.PromoRel
																																		ORDER BY
																																			PromoID ASC
																																	   )
																														ORDER BY
																															PromoID ASC
																													) THEN 
																														(
																															SELECT TOP (1) 
																																PromoID 
																															FROM 
																																dbo.PromoHdr 
																															WHERE 
																																PromoID = (
																																			SELECT TOP (1) 
																																				PromoID 
																																			FROM 
																																				dbo.PromoDet 
																																			WHERE 
																																				LineItem.SecCouponNo = PromoDet.PromoRel
																																			ORDER BY
																																				PromoID ASC
																																		  )
																															ORDER BY
																																PromoID ASC
																														)
																										ELSE NULL 
																									END
																					ELSE NULL 
																				END
																			ELSE ABS(m.PromotionID)
																		  END AS 'PromotionID'
																		, CASE 
																			WHEN Sale.PriceOvrType > 0 THEN ABS(Sale.PriceOvrType)
																			ELSE NULL 
																		END 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 = m.RecID
																					--AND m.SequenceNumber = RetailPriceModifier.SequenceNumber
																	WHERE 
																		m.RecID = Sale.RecID
																	FOR XML Path('RetailPriceModifier')
																	,TYPE
																	--FOR XML AUTO
																	--    , TYPE
																	)
														FOR XML PATH('')
														, TYPE
														, ELEMENTS
														)
												FROM 
													#ARTSSaleDemandLineItemDemand AS Sale          
												WHERE 
													Sale.PickTake = 'T' 
													AND Sale.RecID = LineItem.RecID--Take Method
												FOR XML PATH('Return')
												, TYPE
												, ELEMENTS
												
												)----TERMINA </Return>
											ELSE
												(
													-----INICIA <Sale>
													SELECT 
														Sale.ItemType AS '@ItemType'
														, (
															SELECT
															   (SELECT
																			CASE WHEN sale.IsAnAlternativeItem = 0 
																			THEN sale.ItemID
																			ELSE sale.PrimarySKU
																			END AS 'text()'
																		FOR XML PATH('ItemID')
																		, ELEMENTS
																		,TYPE
																	)
																, Sale.MerchandiseHierarchy
																,(SELECT
																	CASE WHEN sale.IsAnAlternativeItem = 1
																	THEN (SELECT sale.SKU AS 'text()' FOR XML PATH('AlternateItemID')
																	, ELEMENTS
																	,TYPE)
																	END																			
																)
																, Sale.UnitCostPrice
																, Sale.UnitListPrice
																, Sale.RegularSalesUnitPrice
																, Sale.ActualSalesUnitPrice
																, Sale.ExtendedAmount
																, Sale.UnitDiscountAmount
																, Sale.ExtendedDiscountAmount
																, (
																	SELECT
																		CASE WHEN Sale.IsAnAlternativeItem = 0 THEN LineItem.UOM 
																		ELSE Sale.PrimaryUOM END AS '@PrimaryUnitOfMeasureCode',
																		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 0 --''
																			END 
																		END AS "@PrimaryUnits"
																		, CASE WHEN Sale.IsAnAlternativeItem = 0 THEN Sale.UnitOfMeasureCode
																		  ELSE Sale.UOM
																		  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(CAST(1 AS DECIMAL(8,4))/LineItem.AlternativeUOMConvert AS DECIMAL(25,10))
																			  ELSE 0--''
																			  END 
																		  END AS "@Units"
																		, Sale.Quantity AS "text()"
																	FOR XML PATH('Quantity')
																	, ELEMENTS
																	, TYPE


																	)
																, (
																	SELECT 
																		m.MethodCode AS '@MethodCode'
																		, m.ID AS 'SequenceNumber'
																		, m.PreviousPrice
																		, m.NewPrice
																		, CASE 
																			WHEN LineItem.PriceAllow <> 0 THEN 
																				CASE 
																					WHEN EXISTS( 
																								SELECT TOP (1) 
																									PromoID 
																								FROM 
																									dbo.PromoItem pi 
																									JOIN dbo.Coupon c ON pi.PromoID = c.CouponID
																									JOIN dbo.ItemPLU ip ON pi.SKU = ip.SKU
																								WHERE 
																									c.CouponType = 10 
																									AND pi.StatusCode = 'A' 
																									AND LineItem.SKU = pi.SKU
																								ORDER BY
																									PromoID ASC
																							   ) THEN 
																									( 
																										SELECT TOP (1) 
																											PromoID 
																										FROM 
																											dbo.PromoItem pi 
																											JOIN dbo.Coupon c ON pi.PromoID = c.CouponID
																											JOIN dbo.ItemPLU ip ON pi.SKU = ip.SKU
																										WHERE 
																											c.CouponType = 10 
																											AND pi.StatusCode = 'A' 
																											AND LineItem.SKU = pi.SKU
																										ORDER BY
																											PromoID ASC
																									)
																					ELSE NULL 
																				END
																			WHEN LineItem.CouponNumber <> '' THEN 
																				CASE 
																					WHEN EXISTS( 
																									SELECT TOP (1) 
																										PromoID 
																									FROM 
																										dbo.PromoHdr 
																									WHERE 
																										PromoID = LineItem.CouponNumber
																									ORDER BY
																										PromoID ASC
																							   ) THEN 
																									( 
																										SELECT TOP (1) 
																											PromoID 
																										FROM 
																											dbo.PromoHdr 
																										WHERE 
																											PromoID = LineItem.CouponNumber
																										ORDER BY
																											PromoID ASC
																									)
																					ELSE NULL 
																				END
																			WHEN LineItem.SecCouponNo <> '' THEN 
																				CASE 
																					WHEN EXISTS(
																									SELECT TOP (1) 
																										PromoID 
																									FROM 
																										dbo.PromoDet 
																									WHERE 
																										LineItem.SecCouponNo = PromoDet.PromoRel
																									ORDER BY
																										PromoID ASC
																								) THEN 
																									CASE 
																										WHEN EXISTS(
																														SELECT TOP (1) 
																															PromoID 
																														FROM 
																															dbo.PromoHdr 
																														WHERE 
																															PromoID = (
																																		SELECT TOP (1)
																																			PromoID 
																																		FROM 
																																			dbo.PromoDet 
																																		WHERE 
																																			LineItem.SecCouponNo = PromoDet.PromoRel
																																		ORDER BY
																																			PromoID ASC
																																	   )
																														ORDER BY
																															PromoID ASC
																													) THEN 
																														(
																															SELECT TOP (1) 
																																PromoID 
																															FROM 
																																dbo.PromoHdr 
																															WHERE 
																																PromoID = (
																																			SELECT TOP (1) 
																																				PromoID 
																																			FROM 
																																				dbo.PromoDet 
																																			WHERE 
																																				LineItem.SecCouponNo = PromoDet.PromoRel
																																			ORDER BY
																																				PromoID ASC
																																		  )
																															ORDER BY
																																PromoID ASC
																														)
																										ELSE NULL 
																									END
																					ELSE NULL 
																				END
																			ELSE m.PromotionID
																		  END AS 'PromotionID'
																		, CASE 
																			WHEN Sale.PriceOvrType > 0 THEN Sale.PriceOvrType
																			ELSE NULL 
																		END 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 = m.RecID
																					--AND m.SequenceNumber = RetailPriceModifier.SequenceNumber
																	WHERE 
																		m.RecID = Sale.RecID
																	FOR XML Path('RetailPriceModifier')
																	,TYPE
																	--FOR XML AUTO
																	--    , TYPE
																	)
														FOR XML PATH('')
														, TYPE
														, ELEMENTS
														)
												FROM 
													#ARTSSaleDemandLineItemDemand AS Sale          
												WHERE 
													Sale.PickTake = 'T' 
													AND Sale.RecID = LineItem.RecID--Take Method
												FOR XML PATH('Sale')
												, TYPE
												, ELEMENTS
												)---TERMINA </Sale>
												
										END
								)
												
				 
                        FROM 
							#ARTSSaleDemandLineItemDemand LineItem
                        WHERE 
							LineItem.TranDate = h.TranDate
							AND LineItem.TranNumber = h.TranNumber
							AND LineItem.RegisterID = h.TillID
                        --GROUP BY LineItem.TranNumber, LineItem.TranDate, LineItem.RegisterID
                        FOR XML PATH('LineItem')
                        , TYPE
                        , ELEMENTS
                        )
                        ,
                        -- Pickup Method
                        (
                            SELECT 
								ROW_NUMBER() OVER (
													PARTITION BY 
														x.TranDate
														, x.TranNumber
														, x.RegisterID 
													ORDER BY 
														x.TaxCode
												  ) + @SaleItemsCnt 
								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'
											ELSE NULL
                                          END AS 'TypeCode'
                                        , (
                                            SELECT 
												CASE 
                                                    WHEN x.TaxableSales > 0 THEN x.TaxableSales
                                                    WHEN x.NonTaxSales > 0 THEN x.NonTaxSales
                                                    ELSE 0
                                                END AS 'TaxableAmount'
                                                ,
													CASE
														WHEN  s.TaxAmount <> 0 THEN s.TaxAmount
														ELSE 0
													END
													AS 'Amount' 
                                                , ISNULL(
													(
														SELECT TOP (1)
															tax.Rate
														FROM 
															dbo.Tax
														WHERE 
															Tax.TaxCode = x.TaxCode
													)
                                                ,'0') 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 = h1.TranDate
                                AND x.TranNumber = h1.TranNumber
                                AND x.RegisterID = h1.WorkstationID
                            FOR XML PATH('LineItem')
                            , TYPE
                        )
                        , (
                            SELECT 
								ROW_NUMBER() OVER (
													PARTITION BY 
														x.TranDate
														, x.TranNumber
														, x.RegisterID 
													ORDER BY 
														x.TenderCode
                                ) + @SaleItemsCnt + @TaxItemCnt AS 'SequenceNumber'
                                , (
                                    SELECT c.ARTS_Code AS 'TenderType'
										, Tender.TenderCode AS 'TenderCode'----------------------------------------------------------------------------------------------------------------------------------------------
                                        , 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'
											ELSE NULL
                                            END AS 'TypeCode'
                                        , (
											SELECT Tender.TenderAmount AS "text()"
												FOR XML PATH('Amount'), TYPE
										)
                                        , (
                                                SELECT Tender.TenderAmount AS 'RequestedAmount'
                                                , Tender.TenderAuth AS 'AuthorizationCode'
                                                , CONVERT(datetime, Tender.CreditDate) AS 'AuthorizationDateTime'
												WHERE Tender.TenderAuth <> ''
                                                FOR XML PATH('Authorization')
                                                , TYPE
                                        )
										, (
											SELECT TOP (1) 
												'Credit' AS '@CardType'
												, CASE 
													WHEN SUBSTRING(ttd.MiscNumber, 1, 1) = '4' THEN 'Visa'
													WHEN SUBSTRING(ttd.MiscNumber, 1, 2) IN ('51', '52', '53', '54', '55') THEN 'MasterCard'
													WHEN SUBSTRING(ttd.MiscNumber, 1, 2) = '37' THEN 'Amex'
													WHEN SUBSTRING(ttd.MiscNumber, 1, 2) = '65' THEN 'Discover'
													WHEN SUBSTRING(ttd.MiscNumber, 1, 2) IN ('36', '38') OR SUBSTRING(ttd.MiscNumber, 1, 3) IN ('300', '301', '302', '303', '304', '305') THEN 'Diners'
													ELSE 'Fleet'
												  END AS '@TypeCode'
												, ttd.MiscNumber AS 'IssuerIdentificationNumber'
												, CASE WHEN ISNULL(Tender.MagStripe,'') = '' THEN NULL ELSE Tender.MagStripe END  AS 'CardHolderName'
												, ttd.MiscNumber AS 'PrimaryAccountNumber'
												, CASE 
													WHEN Tender.CardExpire <> 0 THEN Tender.CardExpire 
													ELSE '1601' 
												  END AS 'ExpirationDate'
												FROM 
													dbo.TranTendData ttd										
												WHERE 
													Tender.TranNumber = ttd.TranNumber 
													AND Tender.TranDate = ttd.TranDate  
													AND Tender.TenderCode IN (3,4) 
													AND ttd.Sequence = 1
													AND Tender.SigSequence = ttd.SigSequence
												ORDER BY
													ttd.TranNumber ASC
											FOR XML PATH('CreditDebit')
											, TYPE
										)
									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 = h1.TranDate
                                AND x.TranNumber = h1.TranNumber
                                AND x.RegisterID = h1.TillID
                            FOR XML PATH('LineItem')
                            , TYPE
                        )
                        , (
                            SELECT TOP 1 
								@BusinessCustomerFlagValue AS '@BusinessCustomerFlag'
                                , h1.CustomerID
								,  CASE WHEN h1.Name <> '' THEN h1.Name
								       WHEN h1.ShipBusName <> '' THEN h1.ShipBusName  
								       ELSE '*SIN NOMBRE *' END AS 'Name'
                                , (
                                    SELECT 
										h1.Telephone AS 'FullTelephoneNumber'
									WHERE 
										h1.Telephone <> '' 
										AND h1.Telephone IS NOT NULL
									FOR XML PATH('Telephone')
                                        , ELEMENTS
                                        , TYPE
                                    )
								,
									(
										SELECT 
											h1.TranTotalAccountNoAccountNumber AS 'text()'
										FOR XML PATH('AccountNumber')
											, ELEMENTS
											, TYPE
									)
                                , (
                                    SELECT CASE WHEN h1.Address <> '' THEN h1.Address ELSE NULL END AS 'AddressLine'
                                        , CASE WHEN h1.City <> '' THEN h1.City ELSE NULL END AS 'City'
                                        , CASE WHEN h1.State <> '' THEN h1.State ELSE NULL END AS 'State'
                                        , CASE WHEN h1.PostalCode <> '' THEN h1.PostalCode ELSE NULL END AS 'PostalCode'
									WHERE 
										h1.Address <> ''
                                    FOR XML PATH('Address')
                                    , ELEMENTS
                                    , TYPE
                                    )
                            FROM 
								dbo.Customer c
                            WHERE 
								(c.CustomerID = h1.CustomerID
                                AND c.SubKey = h1.SubKey) 
								OR h.TillID = 80
                            FOR XML PATH('Customer')
                            , TYPE
                        )
                        , (
                            SELECT 
								'GrandAmount' AS '@TotalType'
                                , h1.TranTotal AS "text()"
                            FOR XML PATH('Total')
                            , ELEMENTS
                            , TYPE
                        )
                        , (
                            SELECT 
								'TaxAmount' AS '@TotalType'
                                , h1.TaxAmount AS "text()"
                            FOR XML PATH('Total')
                            , ELEMENTS
                            , TYPE
                            )
						, (----------------------------------------------------------------------------------------------------------------------------------------
                            SELECT 
								'CostAmount' AS '@TotalType'
								,h1.CostOfGoods AS "text()"
                            FOR XML PATH('Total')
                            , ELEMENTS
                            , TYPE
                            )--------------------------------------------------------------------------------------------------------------------------------------
						, (
							SELECT TOP (1)  
								h.RetailStoreID
								, h.WorkstationID
								, h.PostVoidTran AS 'SequenceNumber'
								, h.BusinessDayDate
								, h.OperatorID
							WHERE 
								h.TranTotalTypeOfSale = 88
							ORDER BY
								h.RetailStoreID ASC
							FOR XML PATH('TransactionLink')
							, TYPE
						   )
						   , 
								(
									SELECT TOP (1)
										(
											SELECT
												S.StoreNumber
											FROM
												Store S
											WHERE
												S.StoreType = 1
										) AS 'RetailStoreID'
										, Y.RegisterID AS 'WorkStationID'
										, Y.TranNumber AS 'SequenceNumber'
										, Y.TranDate AS 'BusinessDayDate'
										, h.OperatorID AS 'OperatorID' --TRanTotal.EmployeeNo
									FROM
										OrderEcom X
										INNER JOIN TranOrderEcom Y ON X.DocNumber = Y.DocNumber
																  AND X.OrderNo = Y.OrderNo
									WHERE
										h.TranTotalTypeOfSale = 45
										AND h.TranTotalRegisterID <> 80

										AND h.TranTotalDocNumber = X.OrderNo
			
										AND Y.TranNumber = h.TranNumber
										AND Y.TranDate = h.TranDate
										AND Y.RegisterID = Y.RegisterID
						
									FOR XML PATH('TransactionLink')
									, TYPE
								)
						, (
							SELECT
								CASE
									WHEN NULLIF(LTRIM(RTRIM(h1.LoyaltyCard)),'') IS NOT NULL THEN
										(
											SELECT
												(
													SELECT
														(
															SELECT
																h1.LoyaltyCard AS 'text()'
															FOR XML PATH('PrimaryAccountNumber')
															, ELEMENTS
															, TYPE
														)
													FOR XML PATH('LoyaltyCard')
													, ELEMENTS
													, TYPE
												)
											FOR XML PATH('LoyaltyAccount')
											, ELEMENTS
											, TYPE
										)
								END
						)
						, (----------------------------------------------------------------------------------------------------------------------------------------
                            SELECT 
								CAST(h1.TranTotalTypeOfSale AS VARCHAR(3)) AS 'text()'
                            FOR XML PATH('TypeOfSale')
                            , ELEMENTS
                            , TYPE
                        )--------------------------------------------------------------------------------------------------------------------------------------
						
                    FROM 
						#ARTSSaleDemand h1
                    WHERE 
						h1.TranDate = h.TranDate
                        AND h1.SequenceNumber = h.SequenceNumber
                        AND h1.TillID = h.TillID
                    FOR XML PATH('RetailTransaction')
                    , TYPE
                    , ELEMENTS
                    )
            FROM 
				#ARTSSaleDemand h
            ORDER BY 
				h.TranDate
                , h.TranNumber
                , h.TillID
            FOR XML PATH('Transaction')
            , ROOT('POSLog')
            , TYPE
            );--Save output to in memory xml file
        --Delete empty tags

		

    IF @XML IS NOT NULL
    BEGIN
        DECLARE @intErrorCode INT;

        SELECT @intErrorCode = @@ERROR;

        --SELECT @XML AS Before;

        SET @Xml.modify('delete //*[not(node())]');

        --SELECT @XML AS After;

        SET @xmlDoc = CAST(@XML AS VARCHAR(max));
        SET @WebSettingCode = 1;

        -- 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
		    , '002'
            , NULL
            , @xmlDoc
            , 1
            , 1
            , 1
            , GETDATE();
    END;--if @XML is not null

	--**Executing Interface 171 **
	
	EXEC dbo.usp_OutboundIntegrationStageSalesCreditonDemand
			  @TaxSequenceNumber = NULL
			, @TenderSequenceNumber = NULL


    --** End of Interface 171 execution **

END TRY

BEGIN CATCH
    SET @err_message = ERROR_MESSAGE();
	print @err_message
   
END CATCH;

END

GO