ALTER PROCEDURE [dbo].[Report_Activity_Shipments]
	@CustomerID int,
	@Period int=-1,
	@ExceptionsOnly int=0,
	@PurchaseOrderID int

As

Set NoCount On
Declare @CountStatement varchar(8000)
Declare @ExecStatement varchar(8000)
Declare @WhereStatement varchar(8000)
Declare @RecCount int
Declare @ColCount int

DECLARE @colID varchar(3)
DECLARE @colName VARCHAR(255)
DECLARE @colDesc VARCHAR(255)
DECLARE @ASIndex bigint
DECLARE @SELECTCOLS VARCHAR(8000)

Set @colID = ''
SET	@colName = ''
SET @colDesc = ''
SET @SELECTCOLS = ''

Set @ColCount = (Select count(*) From [Columns] inner join Relate_Customers_Columns ON ColumnID=RCC_Column_Link Where CM_Table='Shipments' and RCC_Customer_Link=@CustomerID)
If @ColCount > 0
BEGIN

	WHILE @colID IS NOT NULL
	BEGIN

		Select @colID = MIN(ColumnID) From [Columns] inner join Relate_Customers_Columns ON ColumnID=RCC_Column_Link Where CM_Table='Shipments' and RCC_Customer_Link=@CustomerID AND ColumnID > @colID

		IF	@colID IS NOT NULL
		BEGIN
			Select @colDesc = CM_Description From [Columns] Where ColumnID = @colID
			Select @colName = CM_Mapping From [Columns] Where ColumnID = @colID
			
			--Replace AS statement with Column Desc
			Set @ASIndex = CHARINDEX('AS',@colName)
			--print @ASIndex
			if @ASIndex > 0
			Begin
				--Contains as - cut and append
				Set @colName = substring(@colName, 0, @ASIndex)
				Select @SELECTCOLS = @SELECTCOLS + @colName + 'AS [' + @colDesc + '], '
			End
			Else
			Begin
				--Does not contain as -- append
				Select @SELECTCOLS = @SELECTCOLS + @colName + ' AS [' + @colDesc + '], '
			End
		END
	END

	--Build Exec Statement
	Set @CountStatement = 'select Count(*) From Shipments'
	Set @ExecStatement = 'ShipmentID, (Select Top 1 (SS_Description+'' ('' + SS_Code + '') - ''+SS_Comments) as Status_Desc From ShipmentStatus inner join [Status] on StatusID=SS_Status_Link Where SS_Shipment_Link=ShipmentID Order By SS_Date DESC) as SH_LatestStatus_Desc, SH_Record_Flag, ' + left(@SELECTCOLS,len(@SELECTCOLS)-1) + ' 
		From Shipments'
	Set @WhereStatement = ' Where ShipmentID in (SELECT ROS_ShipmentLink FROM Relate_Orders_Shipments WHERE ROS_OrderLink=' + convert(varchar,@PurchaseOrderID) + ')'

	--Append Where Statement
	If @Period>-1
	Begin
		Set @WhereStatement = @WhereStatement + ' AND (
			(
				SH_ETA_Revised2 is null and SH_ETA_Revised1 is null and (DATEDIFF(day, getDate(), SH_ETA) >= 0) and (DATEDIFF(day, getDate(), SH_ETA) <= ' + convert(varchar,@Period) + ')
			)
			OR
			(
				SH_ETA_Revised2 is not null and (DATEDIFF(day, getDate(), SH_ETA_Revised2) >= 0) and (DATEDIFF(day, getDate(), SH_ETA_Revised2) <= ' + convert(varchar,@Period) + ')
			)
			OR
			(
				SH_ETA_Revised2 is null and SH_ETA_Revised1 is not null and (DATEDIFF(day, getDate(), SH_ETA_Revised1) >= 0) and (DATEDIFF(day, getDate(), SH_ETA_Revised1) <= ' + convert(varchar,@Period) + ')
			)
		)'
	End

	PRINT @CountStatement
	PRINT '-----------------------------------------'
	PRINT @ExecStatement
	PRINT '-----------------------------------------'
	PRINT @WhereStatement

create table #TempRecCount (RecordCount int)
insert into #TempRecCount exec (@CountStatement + @WhereStatement)
Set @RecCount = (Select RecordCount From #TempRecCount)
drop table #TempRecCount

	If @RecCount = 0
	Begin
		Select -1 as ReturnValue, 'EMPTY: No data could be found for the selected criteria.' as ErrorMessage
		Return(-1)
	End
	Else
	Begin
		Set @ExecStatement = 'Select 0 as ReturnValue, ' + convert(varchar,@RecCount) + ' as RecordCount, ' + @ExecStatement + @WhereStatement + ' Order By SH_ETD ASC'
		exec(@ExecStatement)

		return(0)
	End
END
ELSE
BEGIN
	--No Columns For Cust
	Select -2 as ReturnValue, 'ERROR: No columns were linked for the selected client.' as ErrorMessage
	Return(-2)
END