ALTER PROCEDURE [dbo].[getECOsByOrderNumLineNew]
	 -- Add the parameters for the stored procedure here
	 @OrderNum VARCHAR(80)=NULL,
	 @OrderLine INT=NULL,
     @PartNum VARCHAR(30)=NULL,
     @SplitLineitems INT=0,
	 @ECODataTable VARCHAR(80)
AS
BEGIN
     SET NOCOUNT ON;

	 DECLARE @SQL NVARCHAR(MAX)
	 DECLARE @out_var NVARCHAR(MAX)
	 DECLARE @CURRECONUM INT
     DECLARE @ECODescription VARCHAR(200)
     DECLARE @ECOInitiator VARCHAR(80)
     DECLARE @InitiationDate VARCHAR(20)
	 DECLARE @TEMPSQL VARCHAR(500)
	 DECLARE @Status VARCHAR(80)
	 DECLARE @Critical VARCHAR(1)

	 -- Temp table to hold all material parts for the given SO number and line or top level part number
     CREATE TABLE #BOM (
          OrderNum VARCHAR(80),
          OrderLine INT,
          PartNum VARCHAR(50),
          MtlPartNum VARCHAR(50),
		  PartDescription VARCHAR(MAX)
     )

     IF (@OrderNum IS NULL AND @OrderLine IS NULL AND @PartNum IS NULL)
     BEGIN
          RAISERROR ('Please provide a Sales Order number, a Sales Order and Line number or a top level part number ',16,1);
		  RETURN
     END

	 -- When Part number isn't provided, we are working with the order number and line
	 IF @PartNum IS NULL
     BEGIN
	      DECLARE @CurrPartNum VARCHAR(80)
		  DECLARE @CurrLine VARCHAR(80)

	      IF CURSOR_STATUS('global','TOPLEVEL_CURSOR') = 1 DEALLOCATE TOPLEVEL_CURSOR;

		  -- Create cursor based on order number and line
	      IF @OrderLine IS NOT NULL
		       DECLARE TOPLEVEL_CURSOR CURSOR FOR SELECT DISTINCT Orderline,PartNum FROM EP_OpenSO WHERE OrderNum=@Ordernum AND @OrderLine=@Orderline
		  ELSE -- Create cursor based on order number only
		       DECLARE TOPLEVEL_CURSOR CURSOR FOR SELECT DISTINCT OrderLine,PartNum FROM EP_OpenSO WHERE OrderNum=@Ordernum

		 OPEN TOPLEVEL_CURSOR

		 FETCH NEXT FROM TOPLEVEL_CURSOR INTO @CurrLine,@CurrPartNum

		 WHILE @@FETCH_STATUS = 0
         BEGIN
		      INSERT INTO #BOM (PartNum,MtlPartNum,PartDescription) EXEC CreateBOMTree @PartNum=@CurrPartNum,@ExpandUncoated=1

			  IF @OrderLine IS NOT NULL
		           UPDATE #BOM SET OrderNum=@OrderNum,OrderLine=@OrderLine WHERE OrderNum IS NULL
	          ELSE
		           UPDATE #BOM SET OrderNum=@OrderNum,OrderLine=@CurrLine WHERE OrderNum IS NULL

			  FETCH NEXT FROM TOPLEVEL_CURSOR INTO @CurrLine,@CurrPartNum
	     END

		 CLOSE TOPLEVEL_CURSOR
         DEALLOCATE TOPLEVEL_CURSOR
	 END
	 ELSE
	      SELECT GETDATE()
	      INSERT INTO #BOM (PartNum,MtlPartNum,PartDescription) EXEC CreateBOMTree @PartNum=@PartNum,@ExpandUncoated=1
          SELECT GETDATE()

	 -- When @SplitLineitems is 0, only get the ECO #s based on the Order number. Otherwise get the ECO based on Order number and line number
     IF @SplitLineitems=0
     BEGIN
          -- Get the ECO number for any parts in the current sales order by joining with ECOLookup based on SO number. 66 Assemblies have the postfix (everything from the last dash to the end of the partnum) removed
		  SET @SQL='SELECT @out_var=(SELECT STUFF((SELECT DISTINCT '','' + CONVERT(VARCHAR(10),ECONum) FROM #BOM JOIN ' + @ECODataTable + ' ON ' + @ECODataTable + '.PartNum LIKE CASE WHEN SUBSTRING(#BOM.MtlPartnum,1,3)=''64-'' OR SUBSTRING(#BOM.MtlPartnum,1,3)=''65-'' OR SUBSTRING(#BOM.MtlPartnum,1,3)=''66-'' THEN REVERSE(SUBSTRING(REVERSE(#BOM.MtlPartnum), CHARINDEX(REVERSE(''-''), REVERSE(#BOM.MtlPartnum)) + DATALENGTH(''-''), DATALENGTH(#BOM.MtlPartnum))) ELSE #BOM.MtlPartnum END + ''%'' FOR XML PATH('''')),1,1,''''))'
          EXEC sp_executesql @SQL,N'@out_var varchar(max) OUTPUT',@out_var=@out_var OUTPUT

          SELECT @out_var AS ImpactedECOs
     END
	 ELSE
     BEGIN
          -- Get the ECO number for any parts in the current sales order by joining with ECOLookup based on SO number and line. 66 Assemblies have the postfix (everything from the last dash to the end of the partnum) removed
          SET @SQL='INSERT INTO #ECOLookup (OrderNum,OrderLine,PartNum,MtlPartNum,ECONum) (SELECT DISTINCT OrderNum,OrderLine,#BOM.PartNum,MtlPartNum,ECONum FROM #BOM JOIN ' + @ECODataTable + ' ON ' + @ECODataTable + '.PartNum LIKE CASE WHEN SUBSTRING(#BOM.MtlPartnum,1,3)=''64-'' OR SUBSTRING(#BOM.MtlPartnum,1,3)=''65-'' OR SUBSTRING(#BOM.MtlPartnum,1,3)=''66-'' THEN REVERSE(SUBSTRING(REVERSE(#BOM.MtlPartnum), CHARINDEX(REVERSE(''-''), REVERSE(#BOM.MtlPartnum)) + DATALENGTH(''-''), DATALENGTH(#BOM.MtlPartnum))) ELSE #BOM.MtlPartnum END + ''%'')'
		  EXEC(@SQL)

          -- If nothing was returned, we have nothing else to do. This check prevents an error that will occur if you try to create a cursor on empty temp table
          IF (SELECT COUNT(*) FROM #ECOLOOKUP) = 0
               RETURN

          IF CURSOR_STATUS('global','ECO_CURSOR') = 1
          BEGIN
               CLOSE ECO_CURSOR;
               DEALLOCATE ECO_CURSOR;
          END

          -- Loop through each eco num in #ECOLookup
          DECLARE ECO_CURSOR CURSOR LOCAL FOR SELECT DISTINCT ECONum FROM #ECOLookup

          OPEN ECO_CURSOR

          FETCH NEXT FROM ECO_CURSOR INTO @CURRECONUM

          DECLARE @DocumentID INT
          DECLARE @DocumentRev INT

          IF @SplitLineitems = 2 -- Per Reeves on 03-21-16, Added this Split line item to only get MtlPartNum and ECO num so it can be displayed on the RO page
               SELECT DISTINCT MtlPartNum,ECONum FROM #ECOLookup
          ELSE -- @SplitLineitems = 1
          BEGIN
               WHILE @@FETCH_STATUS = 0
               BEGIN
                    -- Because of a distributed transaction error that occurs when executing a stored procedure over a linked server, I use queries instead to get the document id for the current ECO and get the individual data card items based on the doc id
                    SET @TEMPSQL='SELECT DocumentID,Documents.LatestRevisionNo,Status.Name AS Status FROM [ACME_DATA].[Vault].dbo.Documents LEFT JOIN [ACME_DATA].[Vault].dbo.Status ON Status.StatusID=Documents.CurrentStatusID WHERE Filename=''' + CONVERT(VARCHAR(10),@CURRECONUM) + '.doc''' + ' OR Filename=''' + CONVERT(VARCHAR(10),@CURRECONUM) + '.docx'''

                    DECLARE @ECODTL_CURSOR as cursor

                    DECLARE @vsql as nvarchar(max)

                    SET @vsql='SET @cursor = CURSOR FORWARD_ONLY STATIC FOR ' + @TEMPSQL + ' OPEN @cursor;'

                    -- CREATES THE CURSOR
                    EXEC sys.sp_executesql @vsql,N'@cursor CURSOR OUTPUT',@ECODTL_CURSOR output

                    -- FETCH NEXT FROM @ECODTL_CURSOR INTO @ECODescription,@Initiator,@InitiationDate,@Critical,@Status
                    FETCH NEXT FROM @ECODTL_CURSOR INTO @DocumentID,@DocumentRev,@Status

                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                         SET @ECODescription=(SELECT TOP 1 ValueText FROM [ACME_DATA].[Vault].dbo.VariableValue LEFT JOIN [ACME_DATA].[Vault].dbo.Variable ON Variable.VariableID=VariableValue.VariableID WHERE DocumentID=@DocumentID AND RevisionNo<=@DocumentRev AND VariableName='Description' ORDER BY RevisionNo DESC)
                         SET @ECOInitiator=(SELECT TOP 1 ValueText FROM [ACME_DATA].[Vault].dbo.VariableValue LEFT JOIN [ACME_DATA].[Vault].dbo.Variable ON Variable.VariableID=VariableValue.VariableID WHERE DocumentID=@DocumentID AND RevisionNo<=@DocumentRev AND VariableName='Author' ORDER BY RevisionNo DESC)
                         SET @InitiationDate=(SELECT TOP 1 ValueText FROM [ACME_DATA].[Vault].dbo.VariableValue LEFT JOIN [ACME_DATA].[Vault].dbo.Variable ON Variable.VariableID=VariableValue.VariableID WHERE DocumentID=@DocumentID AND RevisionNo<=@DocumentRev AND VariableName='Date' ORDER BY RevisionNo DESC)

                         SET @InitiationDate=SUBSTRING(CONVERT(VARCHAR(20),@InitiationDate),1,10)

                         SET @Critical=(SELECT TOP 1 ValueText FROM [ACME_DATA].[Vault].dbo.VariableValue LEFT JOIN [ACME_DATA].[Vault].dbo.Variable ON Variable.VariableID=VariableValue.VariableID WHERE DocumentID=@DocumentID AND RevisionNo<=@DocumentRev AND VariableName='Critical' ORDER BY RevisionNo DESC)

                         UPDATE #ECOLookup SET ECODescription=@ECODescription,ECOInitiator=@ECOInitiator,InitiationDate=@InitiationDate,Critical=@Critical,ECOStatus=@Status WHERE ECoNum=@CURRECONUM

                         FETCH NEXT FROM @ECODTL_CURSOR INTO @DocumentID,@DocumentRev,@Status
                    END

                    CLOSE @ECODTL_CURSOR
                    DEALLOCATE @ECODTL_CURSOR

                    FETCH NEXT FROM ECO_CURSOR INTO @CURRECONUM
               END

               CLOSE ECO_CURSOR
               DEALLOCATE ECO_CURSOR
          END

          IF @SplitLineitems = 1
		       -- Added WHERE CLAUSE ON 05-01-19. If this causes other issues remove it
			   -- 06-19-19 Commented out where clause because it was causing ECOs to be excluded
			   SELECT * FROM #ECOLookup --WHERE PartNum LIKE CASE WHEN SUBSTRING(@Partnum,1,3)='64-' OR SUBSTRING(@Partnum,1,3)='65-' OR SUBSTRING(@Partnum,1,3)='66-' THEN REVERSE(SUBSTRING(REVERSE(@Partnum), CHARINDEX(REVERSE('-'), REVERSE(@Partnum)) + DATALENGTH('-'), DATALENGTH(@Partnum))) ELSE @Partnum END + '%' ORDER BY OrderNum,OrderLine,ECONum
     END
END