CREATE PROCEDURE [dbo].[GetDispatchWorkflowBoard_GenerateView](
@DetailView VARCHAR(1),
@AirTrakControlFlag114 VARCHAR(50),
@AirTrakControlFlag277 VARCHAR(50),
@AirTrakControlFlag1205 VARCHAR(1),
@WebTrakControlFlag136 VARCHAR(50),
@WebTrakControlFlag137 VARCHAR(50),
@Columns AS VARCHAR(4000),
@GeneratedView AS VARCHAR(MAX) OUTPUT
)
AS
SET NOCOUNT ON

DECLARE @sqlDBcount varchar(20)
DECLARE @EmptyQuotes varchar(2)
SET @EmptyQuotes = ''''''
DECLARE @nolock varchar(20) = ' With(NOLOCK) ';

Declare @CF2227 varchar(1)
set @CF2227='N';
select @CF2227=ControlValue  from tbl_Control where PK_ControlNo =2227

DECLARE @MainQuery nvarchar(max)
SET @MainQuery = 'SELECT %DISTINCT% '

DECLARE @WorkflowWhereClause varchar(4000)
Set @WorkflowWhereClause = ' 1=1 '
--Set @WorkflowWhereClause = '(SH.ShipmentType = ''D'') AND (SH.ShipQuote = ''S'') AND (SH.Status = ''L'' OR (SH.Status=''H'' AND SH.ReadyDateTime > DateAdd(dd, -90, cast(getdate() as date))))'

DECLARE @UserHoursFromCorporate As integer
SET @UserHoursFromCorporate = 0

-- Determine Shipment View vs Detail View
DECLARE @APARDetailView bit
SET @APARDetailView = 0
if (@DetailView = 'Y') SET @APARDetailView = 1


--DECLARE @tbl_PriorityCode_Used bit
--SET @tbl_PriorityCode_Used = 0
DECLARE @tbl_Shipper_Used bit
SET @tbl_Shipper_Used = 0
DECLARE @tbl_Consignee_Used bit
SET @tbl_Consignee_Used = 0
DECLARE @tbl_ShipmentHeader_Equip_Used bit
SET @tbl_ShipmentHeader_Equip_Used = 0
--DECLARE @tbl_Milestone_Last_Used bit
--SET @tbl_Milestone_Last_Used = 0
--DECLARE @tbl_Milestone_Next_Used bit
--SET @tbl_Milestone_Next_Used = 0
--DECLARE @tbl_Customer_BillTo_Used bit
--SET @tbl_Customer_BillTo_Used = 0
--DECLARE @tbl_Customer_CtrlCust_Used bit
--SET @tbl_Customer_CtrlCust_Used = 0
--DECLARE @tbl_Modes_Used bit
--SET @tbl_Modes_Used = 0
--DECLARE @tbl_Stations_Used bit
--SET @tbl_Stations_Used = 0
--DECLARE @tbl_Airports_Src_Used bit
--SET @tbl_Airports_Src_Used = 0
--DECLARE @tbl_Airports_Dest_Used bit
--SET @tbl_Airports_Dest_Used = 0
----Start 25173
--DECLARE @tbl_Ports_Src_Used bit
--SET @tbl_Ports_Src_Used = 0
--DECLARE @tbl_Ports_Dest_Used bit
--SET @tbl_Ports_Dest_Used = 0
----End 25173
--DECLARE @tbl_Statuses_Last_Used bit
--SET @tbl_Statuses_Last_Used = 0
--DECLARE @tbl_Statuses_Next_Used bit
--SET @tbl_Statuses_Next_Used = 0
--DECLARE @tbl_Statuses_Desc_Used bit
--SET @tbl_Statuses_Desc_Used = 0
--DECLARE @tbl_ServiceLevels_Used bit
--SET @tbl_ServiceLevels_Used = 0
--DECLARE @tbl_Vendors_Used bit
--SET @tbl_Vendors_Used = 0
--DECLARE @tbl_Customers_Used bit
--SET @tbl_Customers_Used = 0
--DECLARE @tbl_ProjectCode_Used bit
--SET @tbl_ProjectCode_Used = 0
--DECLARE @tbl_Salesperson_Used bit
--SET @tbl_Salesperson_Used = 0
--DECLARE @tbl_ProductCode_Used bit
--SET @tbl_ProductCode_Used = 0
--DECLARE @tbl_VendorServices_Used bit
--SET @tbl_VendorServices_Used = 0
--DECLARE @tbl_WeightsQuery_Used bit
--SET @tbl_WeightsQuery_Used = 0
--DECLARE @tbl_ShipmentRatingDue_Used bit
--SET @tbl_ShipmentRatingDue_Used = 0
--DECLARE @tbl_ShipmentMilestoneByCategory_Used bit
--SET @tbl_ShipmentMilestoneByCategory_Used = 0
--DECLARE @tbl_ShipmentHeaderAux_Used bit
--SET @tbl_ShipmentHeaderAux_Used = 0
--DECLARE @tbl_References_Used bit --Ticket#22292
--SET @tbl_References_Used = 0
-- DECLARE @tbl_ContainerNumber_Used bit --Ticket#22357
--SET @tbl_ContainerNumber_Used = 0
--DECLARE @tbl_OSDStatus_Used bit --Ticket 23202
--SET @tbl_OSDStatus_Used = 0

--DECLARE @tbl_ShipmentAirImport_Used bit -- Ticket 24316
--SET @tbl_ShipmentAirImport_Used = 0
--DECLARE @tbl_ShipmentOceanImport_Used bit
--set @tbl_ShipmentOceanImport_Used = 0
--DECLARE @CF735 as char(2)
--select @CF735= ControlValue  from tbl_Control where PK_ControlNo =735 

--FK_OrderNo
--ShipQuote
--ShipmentType
--SeqNo
--ManifestNo
--ManifestSeqNo
--HandlingStation
--ControllingStation
--DestAirport
--OrgAirport
--Housebill
--FK_DriverId
--FK_TractorCode
--FK_TrailerCode
--ShipperInfo
--ConsigneeInfo
--ReadyDateTime
--ReadyTime
--ServiceDate
--ScheduledDateTime
--ScheduledTime
--FK_ServiceLevelId
--FK_OrderStatusId
--BillNo
--CustName
--CHB
--MoveType
--Notes
--StatusDescription
--ServiceLevel
--TractorDispatched
--NextTrackingDateTime
--NextTrackingTZHoursAway
--Dispatch
--CompleteDispatch
--ClearDispatch
DECLARE @Field_FK_OrderNo varchar(300); SET @Field_FK_OrderNo = @EmptyQuotes; IF (CHARINDEX('FK_OrderNo',@Columns) <> 0) BEGIN SET @Field_FK_OrderNo = 'DB.FK_OrderNo' END 
DECLARE @Field_ShipQuote varchar(300); SET @Field_ShipQuote = @EmptyQuotes; IF (CHARINDEX('ShipQuote',@Columns) <> 0) BEGIN SET @Field_ShipQuote = 'DB.ShipQuote' END 
DECLARE @Field_ShipmentType varchar(300); SET @Field_ShipmentType = @EmptyQuotes; IF (CHARINDEX('ShipmentType',@Columns) <> 0) BEGIN SET @Field_ShipmentType = 'DB.ShipmentType' END 
DECLARE @Field_SeqNo varchar(300); SET @Field_SeqNo = @EmptyQuotes; BEGIN SET @Field_SeqNo = 'DB.SeqNo' END 
DECLARE @Field_ManifestNo varchar(300); SET @Field_ManifestNo = @EmptyQuotes; IF (CHARINDEX('ManifestNo',@Columns) <> 0) BEGIN SET @Field_ManifestNo = 'DB.ManifestNo' END 
DECLARE @Field_ManifestSeqNo varchar(300); SET @Field_ManifestSeqNo = @EmptyQuotes; IF (CHARINDEX('ManifestSeqNo',@Columns) <> 0) BEGIN SET @Field_ManifestSeqNo = 'DB.ManifestSeqNo' END 
DECLARE @Field_HandlingStation varchar(300); SET @Field_HandlingStation = @EmptyQuotes;  BEGIN SET @Field_HandlingStation = 'DB.HandlingStation' END 
DECLARE @Field_ControllingStation varchar(300); SET @Field_ControllingStation = @EmptyQuotes; BEGIN SET @Field_ControllingStation = 'DB.ControllingStation' END 
DECLARE @Field_DestAirport varchar(300); SET @Field_DestAirport = @EmptyQuotes; BEGIN SET @Field_DestAirport = 'DB.DestAirport' END 
DECLARE @Field_OrgAirport varchar(300); SET @Field_OrgAirport = @EmptyQuotes;  BEGIN SET @Field_OrgAirport = 'DB.OrgAirport' END 
DECLARE @Field_Housebill varchar(300); SET @Field_Housebill = @EmptyQuotes; IF (CHARINDEX('Housebill',@Columns) <> 0) BEGIN SET @Field_Housebill = 'DB.Housebill' END 
DECLARE @Field_FK_DriverId varchar(300); SET @Field_FK_DriverId = @EmptyQuotes; BEGIN SET @Field_FK_DriverId = 'DB.FK_DriverId' END 
DECLARE @Field_FK_TractorCode varchar(300); SET @Field_FK_TractorCode = @EmptyQuotes; IF (CHARINDEX('FK_TractorCode',@Columns) <> 0) BEGIN SET @Field_FK_TractorCode = 'DB.FK_TractorCode' END 
DECLARE @Field_FK_TrailerCode varchar(300); SET @Field_FK_TrailerCode = @EmptyQuotes; IF (CHARINDEX('FK_TrailerCode',@Columns) <> 0) BEGIN SET @Field_FK_TrailerCode = 'DB.FK_TrailerCode' END 
DECLARE @Field_ShipperInfo varchar(300); SET @Field_ShipperInfo = @EmptyQuotes; IF (CHARINDEX('ShipperInfo',@Columns) <> 0) BEGIN SET @Field_ShipperInfo = 'DB.ShipperInfo' END 
DECLARE @Field_ConsigneeInfo varchar(300); SET @Field_ConsigneeInfo = @EmptyQuotes; IF (CHARINDEX('ConsigneeInfo',@Columns) <> 0) BEGIN SET @Field_ConsigneeInfo = 'DB.ConsigneeInfo' END 
DECLARE @Field_ReadyDateTime varchar(300); SET @Field_ReadyDateTime = @EmptyQuotes; IF (CHARINDEX('ReadyDateTime',@Columns) <> 0) BEGIN SET @Field_ReadyDateTime = 'DB.ReadyDateTime' END 
DECLARE @Field_ReadyTime varchar(300); SET @Field_ReadyTime = @EmptyQuotes; IF (CHARINDEX('ReadyTime',@Columns) <> 0) BEGIN SET @Field_ReadyTime = 'DB.ReadyTime' END 
DECLARE @Field_ServiceDate varchar(300); SET @Field_ServiceDate = @EmptyQuotes; IF (CHARINDEX('ServiceDate',@Columns) <> 0) BEGIN SET @Field_ServiceDate = 'DB.ServiceDate' END 
DECLARE @Field_ScheduledDateTime varchar(300); SET @Field_ScheduledDateTime = @EmptyQuotes; IF (CHARINDEX('ScheduledDateTime',@Columns) <> 0) BEGIN SET @Field_ScheduledDateTime = 'DB.ScheduledDateTime' END 
DECLARE @Field_ScheduledTime varchar(300); SET @Field_ScheduledTime = @EmptyQuotes; IF (CHARINDEX('ScheduledTime',@Columns) <> 0) BEGIN SET @Field_ScheduledTime = 'DB.ScheduledTime' END 
DECLARE @Field_FK_ServiceLevelId varchar(300); SET @Field_FK_ServiceLevelId = @EmptyQuotes; IF (CHARINDEX('FK_ServiceLevelId',@Columns) <> 0) BEGIN SET @Field_FK_ServiceLevelId = 'DB.FK_ServiceLevelId' END 
DECLARE @Field_FK_OrderStatusId varchar(300); SET @Field_FK_OrderStatusId = @EmptyQuotes; IF (CHARINDEX('FK_OrderStatusId',@Columns) <> 0) BEGIN SET @Field_FK_OrderStatusId = 'DB.FK_OrderStatusId' END 
DECLARE @Field_BillNo varchar(300); SET @Field_BillNo = @EmptyQuotes; IF (CHARINDEX('BillNo',@Columns) <> 0) BEGIN SET @Field_BillNo = 'DB.BillNo' END 
DECLARE @Field_CustName varchar(300); SET @Field_CustName = @EmptyQuotes; IF (CHARINDEX('CustName',@Columns) <> 0) BEGIN SET @Field_CustName = 'DB.CustName' END 
DECLARE @Field_CHB varchar(300); SET @Field_CHB = @EmptyQuotes; IF (CHARINDEX('CHB',@Columns) <> 0) BEGIN SET @Field_CHB = 'DB.CHB' END 
DECLARE @Field_MoveType varchar(300); SET @Field_MoveType = @EmptyQuotes; IF (CHARINDEX('MoveType',@Columns) <> 0) BEGIN SET @Field_MoveType = 'DB.MoveType' END 
DECLARE @Field_Notes varchar(300); SET @Field_Notes = @EmptyQuotes; IF (CHARINDEX('Notes',@Columns) <> 0) BEGIN SET @Field_Notes = 'Cast(DB.Notes as Varchar(1000)) ' END 
DECLARE @Field_StatusDescription varchar(300); SET @Field_StatusDescription = @EmptyQuotes; IF (CHARINDEX('StatusDescription',@Columns) <> 0) BEGIN SET @Field_StatusDescription = 'DB.StatusDescription' END 
DECLARE @Field_ServiceLevel varchar(300); SET @Field_ServiceLevel = @EmptyQuotes; IF (CHARINDEX('ServiceLevel',@Columns) <> 0) BEGIN SET @Field_ServiceLevel = 'DB.ServiceLevel' END 
DECLARE @Field_TractorDispatched varchar(300); SET @Field_TractorDispatched = @EmptyQuotes; BEGIN SET @Field_TractorDispatched = 'DB.TractorDispatched' END 
DECLARE @Field_NextTrackingDateTime varchar(300); SET @Field_NextTrackingDateTime = @EmptyQuotes; IF (CHARINDEX('NextTrackingDateTime',@Columns) <> 0) BEGIN SET @Field_NextTrackingDateTime = 'DB.NextTrackingDateTime' END 
DECLARE @Field_NextTrackingTZHoursAway varchar(300); SET @Field_NextTrackingTZHoursAway = @EmptyQuotes; IF (CHARINDEX('NextTrackingTZHoursAway',@Columns) <> 0) BEGIN SET @Field_NextTrackingTZHoursAway = 'DB.NextTrackingTZHoursAway' END 
DECLARE @Field_Dispatch varchar(300); SET @Field_Dispatch = @EmptyQuotes; IF (CHARINDEX(',Dispatch',@Columns) <> 0) BEGIN SET @Field_Dispatch = 'DB.Dispatch' END 
DECLARE @Field_CompleteDispatch varchar(300); SET @Field_CompleteDispatch = @EmptyQuotes; IF (CHARINDEX(',CompleteDispatch',@Columns) <> 0) BEGIN SET @Field_CompleteDispatch = 'DB.CompleteDispatch' END 
DECLARE @Field_ClearDispatch varchar(300); SET @Field_ClearDispatch = @EmptyQuotes; IF (CHARINDEX(',ClearDispatch',@Columns) <> 0) BEGIN SET @Field_ClearDispatch = 'DB.ClearDispatch' END 

DECLARE @Field_ShipName varchar(20)
SET @Field_ShipName = @EmptyQuotes
IF (CHARINDEX(',ShipName',@Columns) <> 0)
	BEGIN
		SET @Field_ShipName = 'ShipName'
		SET @tbl_Shipper_Used = 1
	END

DECLARE @Field_ConName varchar(20)
SET @Field_ConName = @EmptyQuotes
IF (CHARINDEX(',ConName',@Columns) <> 0)
	BEGIN
		SET @Field_ConName = 'ConName'
		SET @tbl_Consignee_Used = 1
	END

DECLARE @Field_ShipperTooltip varchar(200)
SET @Field_ShipperTooltip = @EmptyQuotes
DECLARE @Field_ShipAlpha varchar(20)
SET @Field_ShipAlpha = @EmptyQuotes
IF (CHARINDEX(',ShipAlpha',@Columns) <> 0)
	BEGIN
		SET @Field_ShipAlpha = 'SHIP.ShipAlpha'
		SET @Field_ShipperTooltip = 'SHIP.ShipName+''\n''+SHIP.ShipAddress1+''\n''+CASE WHEN SHIP.ShipAddress2 IS NULL THEN '''' ELSE SHIP.ShipAddress2+''\n'' END+SHIP.ShipCity+'', ''+SHIP.FK_ShipState+'' ''+SHIP.ShipZip'

		SET @tbl_Shipper_Used = 1
	END

DECLARE @Field_ShipperCity varchar(50)
SET @Field_ShipperCity = @EmptyQuotes
IF (CHARINDEX(',ShipCity',@Columns) <> 0)
	BEGIN
		SET @Field_ShipperCity = ' SHIP.ShipCity'

		SET @tbl_Shipper_Used = 1
	END

DECLARE @Field_ShipperState varchar(50)
SET @Field_ShipperState = @EmptyQuotes
IF (CHARINDEX(',ShipState',@Columns) <> 0)
	BEGIN
		SET @Field_ShipperState = ' SHIP.FK_ShipState'

		SET @tbl_Shipper_Used = 1
	END

DECLARE @Field_ConCity varchar(50)
SET @Field_ConCity = @EmptyQuotes
IF (CHARINDEX(',ConCity',@Columns) <> 0)
	BEGIN
		SET @Field_ConCity = ' CON.ConCity'

		SET @tbl_Consignee_Used = 1
	END

DECLARE @Field_ConState varchar(50)
SET @Field_ConState = @EmptyQuotes
IF (CHARINDEX(',ConState',@Columns) <> 0)
	BEGIN
		SET @Field_ConState = ' CON.FK_ConState'

		SET @tbl_Consignee_Used = 1
	END
DECLARE @Field_ConsigneeTooltip varchar(175)
SET @Field_ConsigneeTooltip = @EmptyQuotes
DECLARE @Field_ConAlpha varchar(20)
SET @Field_ConAlpha = @EmptyQuotes
IF (CHARINDEX(',ConAlpha',@Columns) <> 0)
	BEGIN
		SET @Field_ConAlpha = 'CON.ConAlpha'
		SET @Field_ConsigneeTooltip = 'CON.ConName+''\n''+CON.ConAddress1+''\n''+CASE WHEN CON.ConAddress2 IS NULL THEN '''' ELSE CON.ConAddress2 + ''\n'' END+CON.ConCity+'', ''+CON.FK_ConState+'' ''+CON.ConZip'

		SET @tbl_Consignee_Used = 1
	END

DECLARE @Field_FK_EquipmentCode varchar(20)
SET @Field_FK_EquipmentCode = @EmptyQuotes
IF (CHARINDEX(',FK_EquipmentCode',@Columns) <> 0)
	BEGIN
		SET @Field_FK_EquipmentCode = 'SH.FK_EquipmentCode'
		SET @tbl_ShipmentHeader_Equip_Used = 1
	END

DECLARE @Field_DangerousGoods varchar(20)
SET @Field_DangerousGoods = @EmptyQuotes
IF (CHARINDEX(',DangerousGoods',@Columns) <> 0)
	BEGIN
		SET @Field_DangerousGoods = 'SH.DangerousGoods'
		SET @tbl_ShipmentHeader_Equip_Used = 1
	END

DECLARE @Field_ShipmentOnConsol varchar(200)
SET @Field_ShipmentOnConsol = @EmptyQuotes
--IF (CHARINDEX(',ShipmentOnConsol',@Columns) <> 0)
	BEGIN
		SET @Field_ShipmentOnConsol = 'CASE WHEN EXISTS (SELECT TOP 1 cd1.FK_OrderNo FROM tbl_ConsolDetail cd1' + @nolock + ' WHERE cd1.FK_OrderNo = SH.PK_OrderNo) THEN ''Y'' ELSE ''N'' END'
		SET @tbl_ShipmentHeader_Equip_Used = 1
	END

DECLARE @Field_CostsOrChargesFinalized varchar(250)
SET @Field_CostsOrChargesFinalized = @EmptyQuotes
--IF (CHARINDEX(',CostsOrChargesFinalized',@Columns) <> 0)
	BEGIN
		SET @Field_CostsOrChargesFinalized = 'CASE WHEN EXISTS (SELECT TOP 1 APAR.FK_OrderNo FROM tbl_ShipmentAPAR AS APAR ' + @nolock + ' WHERE APAR.FK_OrderNo = SH.PK_OrderNo AND APAR.Finalize = ''Y'' AND APAR.SeqNo <> 9999) THEN ''Y'' ELSE ''N'' END'
		SET @tbl_ShipmentHeader_Equip_Used = 1
	END

DECLARE @Field_InvoicesPrinted varchar(220)
SET @Field_InvoicesPrinted = @EmptyQuotes
--IF (CHARINDEX(',InvoicesPrinted',@Columns) <> 0)
	BEGIN
		SET @Field_InvoicesPrinted = 'CASE WHEN EXISTS (SELECT TOP 1 APAR2.FK_OrderNo FROM tbl_ShipmentAPAR AS APAR2 ' + @nolock + ' WHERE APAR2.FK_OrderNo = SH.PK_OrderNo AND APAR2.InvPrinted = ''Y'') THEN ''Y'' ELSE ''N'' END'
		SET @tbl_ShipmentHeader_Equip_Used = 1
	END

--start 10/23/2023 26730
DECLARE @Field_Hazmat varchar(220)
SET @Field_Hazmat = @EmptyQuotes
--IF (CHARINDEX(',Hazmat',@Columns) <> 0)
	BEGIN
		SET @Field_Hazmat = ' Case When Exists (SELECT Top 1 [Hazmat] FROM [tbl_ShipmentDesc] ' + @nolock + '  Where [FK_OrderNo] = SH.PK_OrderNo And Hazmat = ''Y'') Then ''Y'' Else ''N'' End '
	END
--end 10/23/2023 26730

DECLARE @Field_InUseUser varchar(350)
SET @Field_InUseUser = @EmptyQuotes
--IF (CHARINDEX(',InUseUser',@Columns) <> 0)
--	BEGIN
--		SET @Field_InUseUser = 'CASE WHEN (SELECT TOP 1 siu1.FK_UserId FROM tbl_ShipmentsInUse siu1' + @nolock + ' WHERE siu1.FK_OrderNo = DB.FK_OrderNo ORDER BY siu1.OpenedDateTime) IS NULL THEN '''' ELSE (SELECT Substring((Select '', '' + U.FK_UserId AS [text()] From dbo.tbl_ShipmentsInUse as U ' + @nolock + ' WHERE (U.FK_OrderNo=DB.FK_OrderNo) For XML PATH ('''')),2,100)) END'
--	END

SET @MainQuery = @MainQuery 
			IF @Field_FK_OrderNo <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_FK_OrderNo + ' As FK_OrderNo, ' 
			IF @Field_ShipQuote <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ShipQuote + ' As ShipQuote, ' 
			IF @Field_ShipmentType <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ShipmentType + ' As ShipmentType, '
			IF @Field_SeqNo <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_SeqNo + ' As SeqNo, '
			IF @Field_ManifestNo <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ManifestNo + ' As ManifestNo, ' 
			--IF @Field_HandlingStation <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_HandlingStation + ' As HandlingStation, '
			--IF @Field_ControllingStation <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ControllingStation + ' As ControllingStation, '
			--IF @Field_DestAirport <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_DestAirport + ' As DestAirport, '
			--IF @Field_OrgAirport <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_OrgAirport + ' As OrgAirport, '
			IF @Field_Housebill <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_Housebill + ' As Housebill, '
			--IF @Field_FK_DriverId <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_FK_DriverId + ' As FK_DriverId, '
			IF @Field_FK_TractorCode <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_FK_TractorCode + ' As FK_TractorCode, '
			IF @Field_FK_TrailerCode <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_FK_TrailerCode + ' As FK_TrailerCode, '
			IF @Field_ShipperInfo <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ShipperInfo + ' As ShipperInfo, '
			IF @Field_ConsigneeInfo <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ConsigneeInfo + ' As ConsigneeInfo, '
			IF @Field_ReadyDateTime <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ReadyDateTime + ' As ReadyDateTime, '
			IF @Field_ReadyTime <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ReadyTime + ' As ReadyTime, '
			IF @Field_ServiceDate <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ServiceDate + ' As ServiceDate, '
			IF @Field_ScheduledDateTime <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ScheduledDateTime + ' As ScheduledDateTime, '
			IF @Field_ScheduledTime <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ScheduledTime + ' As ScheduledTime, '
			IF @Field_FK_ServiceLevelId <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_FK_ServiceLevelId + ' As FK_ServiceLevelId, '
			IF @Field_FK_OrderStatusId <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_FK_OrderStatusId + ' As FK_OrderStatusId, '
			IF @Field_BillNo <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_BillNo + ' As BillNo, '
			IF @Field_CustName <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_CustName + ' As CustName, '
			IF @Field_CHB <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_CHB + ' As CHB, '
			IF @Field_MoveType <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_MoveType + ' As MoveType, '
			IF @Field_Notes <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_Notes + ' As Notes, '
			IF @Field_StatusDescription <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_StatusDescription + ' As StatusDescription, '
			IF @Field_ServiceLevel <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ServiceLevel + ' As ServiceLevel, '
			--IF @Field_TractorDispatched <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_TractorDispatched + ' As TractorDispatched, '
			IF @Field_NextTrackingDateTime <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_NextTrackingDateTime + ' As NextTrackingDateTime, '
			IF @Field_NextTrackingTZHoursAway <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_NextTrackingTZHoursAway + ' As NextTrackingTZHoursAway, '
			IF @Field_ShipName <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ShipName + ' As ShipName, '
			IF @Field_ConName <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ConName + ' As ConName, '
			IF @Field_ShipAlpha <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ShipAlpha + ' As ShipAlpha, '
			IF @Field_ConAlpha <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ConAlpha + ' As ConAlpha, '
			IF @Field_ShipperTooltip <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ShipperTooltip + ' As ShipperTooltip, '
			IF @Field_ConsigneeTooltip <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ConsigneeTooltip + ' As ConsigneeTooltip, '
			IF @Field_FK_EquipmentCode <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_FK_EquipmentCode + ' As FK_EquipmentCode, '

			IF @Field_Hazmat <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_Hazmat + ' As Hazmat, ' --10/23/2023 26730

			IF @Field_DangerousGoods <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_DangerousGoods + ' As DangerousGoods, '

			IF @Field_ShipperCity <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ShipperCity + ' As ShipCity, '
			IF @Field_ShipperState <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ShipperState + ' As ShipState, '
			IF @Field_ConCity <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ConCity + ' As ConCity, '
			IF @Field_ConState <> @EmptyQuotes SET @MainQuery = @MainQuery + @Field_ConState + ' As ConState, '

			SET @MainQuery = @MainQuery + ' DB.FK_OrderNo AS PK_OrderNo,  ' + ' DB.FK_OrderNo As DispatchX, DB.FK_OrderNo As CompleteDispatchX, DB.FK_OrderNo As ClearDispatchX,  '
			SET @MainQuery = @MainQuery + ' '''' as SelectCommand,'''' as CostBillCommand,'''' as DocCommand,'''' as MilestoneCommand, Cast(DB.FK_OrderNo as Varchar(20)) + ''|'' + Cast(DB.SeqNo as Varchar(20)) as E1, '
			SET @MainQuery = @MainQuery + @Field_TractorDispatched + ' As TractorDispatched, ' 
			SET @MainQuery = @MainQuery + @Field_FK_DriverId + ' As FK_DriverId, '
			SET @MainQuery = @MainQuery + @Field_HandlingStation + ' As HandlingStation, '
			SET @MainQuery = @MainQuery + @Field_ControllingStation + ' As ControllingStation, '
			SET @MainQuery = @MainQuery + @Field_DestAirport + ' As DestAirport, '
			SET @MainQuery = @MainQuery + @Field_OrgAirport + ' As OrgAirport, '

			SET @MainQuery = @MainQuery + @Field_ShipmentOnConsol + ' As ShipmentOnConsol, '
			SET @MainQuery = @MainQuery + @Field_CostsOrChargesFinalized + ' As CostsOrChargesFinalized, '
			SET @MainQuery = @MainQuery + @Field_InvoicesPrinted + ' As InvoicesPrinted '

--@Field_InUseUser + ' As InUseUser,' +
--@Field_Dispatch + ' As Dispatch, ' + 
--@Field_CompleteDispatch + ' As CompleteDispatch, ' + 
--@Field_ClearDispatch + ' As ClearDispatch, ' +  CONVERT(NVARCHAR(20), FK_OrderNo)
--' FK_OrderNo AS PK_OrderNo,  ' + ' CONVERT(NVARCHAR(20), FK_OrderNo) As DispatchX, CONVERT(NVARCHAR(20), FK_OrderNo) As CompleteDispatchX, CONVERT(NVARCHAR(20), FK_OrderNo) As ClearDispatchX'
	--IF (@APARDetailView > 0)
	--	BEGIN
	--		--SET @MainQuery = @MainQuery + ',SA.APARCode '
	--	END
--	SET @MainQuery = @MainQuery + ' %FROM% ' + '%WHERE% ' + '%GROUPBY%'
	SET @MainQuery = @MainQuery + ' %FROM% ' + '%WHERE% '

DECLARE @DistinctValue varchar(8)
DECLARE @FromValue varchar(5500)
DECLARE @GroupByValue varchar(5500)

IF (@APARDetailView > 0)
	BEGIN
		SET @DistinctValue = 'DISTINCT'
		SET @FromValue = ' FROM  [dbo].[vw_DispatchBoard]  AS DB WITH (NOLOCK)'
		if Convert(varchar ,@CF2227 ) ='Y'
			begin
				SET @FromValue = ' FROM  [dbo].[vw_DispatchBoardAssigned]  AS DB WITH (NOLOCK)'
			END
		
		IF @tbl_Shipper_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Shipper AS SHIP WITH (NOLOCK) ON DB.FK_OrderNo = SHIP.FK_ShipOrderNo'
		IF @tbl_Consignee_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Consignee AS CON WITH (NOLOCK) ON DB.FK_OrderNo = CON.FK_ConOrderNo'
		IF @tbl_ShipmentHeader_Equip_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ShipmentHeader as SH WITH (NOLOCK)  ON DB.FK_OrderNo = SH.PK_OrderNo '
		SET @GroupByValue = ''
	END
ELSE
	BEGIN
		SET @DistinctValue = 'DISTINCT'
		SET @FromValue = ' FROM  [dbo].[vw_DispatchBoard]  AS DB WITH (NOLOCK)'
		if Convert(varchar ,@CF2227 ) ='Y'
			begin
				SET @FromValue = ' FROM  [dbo].[vw_DispatchBoardAssigned]  AS DB WITH (NOLOCK)'
			END

		IF @tbl_Shipper_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Shipper AS SHIP WITH (NOLOCK) ON DB.FK_OrderNo = SHIP.FK_ShipOrderNo'
		IF @tbl_Consignee_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Consignee AS CON WITH (NOLOCK) ON DB.FK_OrderNo = CON.FK_ConOrderNo'
		IF @tbl_ShipmentHeader_Equip_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ShipmentHeader as SH WITH (NOLOCK)  ON DB.FK_OrderNo = SH.PK_OrderNo '

		SET @GroupByValue = 'GROUP BY '
			IF @Field_FK_OrderNo <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_FK_OrderNo
			IF @Field_ShipQuote <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ShipQuote
			IF @Field_ShipmentType <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ShipmentType
			IF @Field_SeqNo <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_SeqNo
			IF @Field_ManifestNo <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ManifestNo
			IF @Field_ManifestSeqNo <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ManifestSeqNo
			SET @GroupByValue = @GroupByValue + @Field_HandlingStation
			SET @GroupByValue = @GroupByValue + @Field_ControllingStation
			SET @GroupByValue = @GroupByValue + @Field_DestAirport
			SET @GroupByValue = @GroupByValue + @Field_OrgAirport
			IF @Field_Housebill <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_Housebill
			SET @GroupByValue = @GroupByValue + @Field_FK_DriverId
			SET @GroupByValue = @GroupByValue + @Field_FK_TractorCode
			IF @Field_FK_TrailerCode <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_FK_TrailerCode
			IF @Field_ShipperInfo <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ShipperInfo
			IF @Field_ConsigneeInfo <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ConsigneeInfo
			IF @Field_ReadyDateTime <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ReadyDateTime
			IF @Field_ReadyTime <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ReadyTime
			IF @Field_ServiceDate <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ServiceDate
			IF @Field_ScheduledDateTime <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ScheduledDateTime
			IF @Field_ScheduledTime <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ScheduledTime
			IF @Field_FK_ServiceLevelId <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_FK_ServiceLevelId
			IF @Field_FK_OrderStatusId <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_FK_OrderStatusId
			IF @Field_BillNo <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_BillNo
			IF @Field_CustName <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_CustName
			IF @Field_CHB <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_CHB
			IF @Field_MoveType <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_MoveType
			IF @Field_Notes <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_Notes
			IF @Field_StatusDescription <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_StatusDescription
			IF @Field_ServiceLevel <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ServiceLevel
			IF @Field_TractorDispatched <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_TractorDispatched
			IF @Field_NextTrackingDateTime <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_NextTrackingDateTime
			IF @Field_NextTrackingTZHoursAway <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_NextTrackingTZHoursAway
			IF @Field_Dispatch <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_Dispatch
			IF @Field_CompleteDispatch <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_CompleteDispatch
			IF @Field_ClearDispatch <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ClearDispatch

			IF @Field_ShipName <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ShipName
			IF @Field_ConName <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ConName
			IF @Field_ShipAlpha <> @EmptyQuotes SET @GroupByValue = @GroupByValue + ',' + @Field_ShipAlpha
			IF @Field_ConAlpha <> @EmptyQuotes SET @GroupByValue = @GroupByValue + ',' + @Field_ConAlpha
			IF @Field_ShipperTooltip <> @EmptyQuotes SET @GroupByValue = @GroupByValue + ',ShipName,ShipAddress1,ShipAddress2,ShipCity,FK_ShipState,ShipZip'
		    IF @Field_ConsigneeTooltip <> @EmptyQuotes SET @GroupByValue = @GroupByValue + ',ConName,ConAddress1,ConAddress2,ConCity,FK_ConState,ConZip'
			IF @Field_FK_EquipmentCode <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_FK_EquipmentCode

			IF @Field_Hazmat <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_Hazmat  --10/23/2023 26730

			IF @Field_DangerousGoods <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_DangerousGoods

			IF @Field_ShipperCity <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ShipperCity
			IF @Field_ShipperState <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ShipperState
			IF @Field_ConCity <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ConCity
			IF @Field_ConState <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ConState

			--IF @Field_ConState <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_ShipmentOnConsol
			--IF @Field_ConState <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_CostsOrChargesFinalized
			--IF @Field_ConState <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_InvoicesPrinted

			IF @Field_InUseUser <> @EmptyQuotes SET @GroupByValue = @GroupByValue + @Field_InUseUser
			--
	END

---- Update Joins
--IF @tbl_ShipmentMilestoneByCategory_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ShipmentMilestoneByCategory AS SMC WITH (NOLOCK) ON SH.PK_OrderNo = SMC.FK_OrderNo'
--IF @tbl_PriorityCode_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_PriorityCode AS PC WITH (NOLOCK) ON SH.PriorityCodeId = PC.PriorityCodeId'
--IF @tbl_Shipper_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Shipper AS SHIP WITH (NOLOCK) ON SH.PK_OrderNo = SHIP.FK_ShipOrderNo'
--IF @tbl_Consignee_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Consignee AS CON WITH (NOLOCK) ON SH.PK_OrderNo = CON.FK_ConOrderNo'
--IF @tbl_ShipmentMilestoneByCategory_Used > 0
--	BEGIN
--		IF @tbl_Milestone_Last_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Milestone AS MSLAST WITH (NOLOCK) ON SMC.LastMilestone = MSLAST.PK_SeqNo'
--		IF @tbl_Milestone_Next_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Milestone AS MSNEXT WITH (NOLOCK) ON SMC.NextMilestone = MSNEXT.PK_SeqNo'
--	END
--ELSE
--	BEGIN
--		IF @tbl_Milestone_Last_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Milestone AS MSLAST WITH (NOLOCK) ON SH.LastMilestone = MSLAST.PK_SeqNo'
--		IF @tbl_Milestone_Next_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Milestone AS MSNEXT WITH (NOLOCK) ON SH.NextMilestone = MSNEXT.PK_SeqNo'
--	END
--IF @tbl_Customer_CtrlCust_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Customers AS CTRLCUST WITH (NOLOCK) ON SH.FK_ControlCustomerNo = CTRLCUST.PK_CustNo'
--IF @tbl_Customer_BillTo_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Customers AS CTRLBILLTO WITH (NOLOCK) ON SH.BillNo = CTRLBILLTO.PK_CustNo'
--IF @tbl_VendorServices_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_VendorServices AS VENDSERV WITH (NOLOCK) ON SA.FK_ServiceId = VENDSERV.PK_ServiceId'
--IF @tbl_Modes_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Modes AS MODE WITH (NOLOCK) ON RTRIM(SH.FK_ModeId) = MODE.PK_ModeId'
--IF @tbl_WeightsQuery_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN (
--SELECT FK_OrderNo,
--sum(Pieces) AS Pieces,sum(Skids) AS Skids,
--sum(CASE WHEN Weight IS NULL THEN 0 ELSE Weight END) AS Weight, ' +
--'sum(CASE WHEN WeightKilo IS NULL THEN 0 ELSE WeightKilo END) AS WeightKilo, ' + --23404
--'Min(TargetPayRateMin) AS TargetPayRateMin, ' + --21455
--'Max(TargetPayRateMax) AS TargetPayRateMax, ' + --21455
--'CASE WHEN sum(CASE WHEN Weight IS NULL THEN 0 ELSE Weight END) > sum(CASE WHEN DimWeight IS NULL THEN 0 ELSE DimWeight END) THEN sum(CASE WHEN Weight IS NULL THEN 0 ELSE Weight END) ELSE sum(CASE WHEN DimWeight IS NULL THEN 0 ELSE DimWeight END) END AS ChargeableWeight, ' +
--'CASE WHEN sum(CASE WHEN WeightKilo IS NULL THEN 0 ELSE WeightKilo END) > sum(CASE WHEN DimWeightKilo IS NULL THEN 0 ELSE DimWeightKilo END) THEN sum(CASE WHEN WeightKilo IS NULL THEN 0 ELSE WeightKilo END) ELSE sum(CASE WHEN DimWeightKilo IS NULL THEN 0 ELSE DimWeightKilo END) END AS ChargeableWeightKilo ' +  --23404
--															'FROM tbl_ShipmentDesc GROUP BY FK_OrderNo) AS WEIGHTS ON SH.PK_OrderNo = WEIGHTS.FK_OrderNo'
--IF @tbl_Stations_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Stations AS STN WITH (NOLOCK) ON SH.HandlingStation = STN.PK_StationId'
--IF @tbl_Airports_Src_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Airports AS AIRORG WITH (NOLOCK) ON SH.OrgAirport = AIRORG.PK_AirportId'
--IF @tbl_Airports_Dest_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Airports AS AIRDEST WITH (NOLOCK) ON SH.DestAirport = AIRDEST.PK_AirportId'
----start 25173
--IF @tbl_Ports_Src_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Ports AS PORTORG WITH (NOLOCK) ON SH.OrgAirport = PORTORG.PK_PortCode'
--IF @tbl_Ports_Dest_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Ports AS PORTDEST WITH (NOLOCK) ON SH.DestAirport = PORTDEST.PK_PortCode'
---- end 25173
--IF @tbl_Statuses_Desc_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Statuses AS STSORD WITH (NOLOCK) ON SH.FK_OrderStatusId = STSORD.PK_OrderStatusId'
--IF @tbl_Statuses_Last_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Statuses AS STSLAST WITH (NOLOCK) ON MSLAST.FK_OrderStatusId = STSLAST.PK_OrderStatusId'
--IF @tbl_Statuses_Next_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Statuses AS STSNEXT WITH (NOLOCK) ON MSNEXT.FK_OrderStatusId = STSNEXT.PK_OrderStatusId'
--IF @tbl_ServiceLevels_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ServiceLevels AS SL WITH (NOLOCK) ON SH.FK_ServiceLevelId = SL.PK_ServiceLevelId'
--IF @tbl_Vendors_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Vendors AS V WITH (NOLOCK) ON SA.FK_VendorId = V.VendorId'
--IF @tbl_ProjectCode_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ProjectCode AS PROJ WITH (NOLOCK) ON SH.FK_ProjectCode = PROJ.PK_ProjectCode'
--IF @tbl_Salesperson_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_Salesperson AS SALES WITH (NOLOCK) ON SH.FK_Salesperson = SALES.PK_SalespersonId'
--IF @tbl_ProductCode_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ProductCode AS PROD WITH (NOLOCK) ON SH.FK_ProductCode = PROD.PK_ProductCode'
--IF @tbl_ShipmentRatingDue_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ShipmentRatingDue AS SRD WITH (NOLOCK) ON SH.PK_OrderNo = SRD.FK_OrderNo'
--IF @tbl_ShipmentHeaderAux_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ShipmentHeaderAux AS SHA WITH (NOLOCK) ON SH.PK_OrderNo = SHA.FK_OrderNo'
--IF @tbl_References_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_References AS R WITH (NOLOCK) ON SH.PK_OrderNo = R.FK_OrderNo'
--IF @tbl_ContainerNumber_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ContainerNumber AS C WITH (NOLOCK) ON SH.PK_OrderNo = C.FK_OrderNo' --22357
--IF @tbl_OSDStatus_Used > 0 SET @FromValue = @FromValue + ' LEFT OUTER JOIN vw_ShipmentOSDStatus AS OSD ON SH.PK_OrderNo = OSD.FK_OrderNo' --23202
----start 24316
--IF @tbl_ShipmentAirImport_Used > 0 
--begin
--	--SET @FromValue = @FromValue + ' LEFT OUTER JOIN ( select top 1 FK_OrderNo,MAWB as ACMAWB, ConsolNo as ACConsolNo from tbl_AirCost WITH (NOLOCK) where isnull(ConsolNo,0)=0) AS AC ON SH.PK_OrderNo = AC.FK_OrderNo ' --24316
--	--SET @FromValue = @FromValue + ' LEFT OUTER JOIN ( select top 1 FK_OrderNo,MAWB as ACMAWB, ConsolNo as ACConsolNo from tbl_AirCost WITH (NOLOCK) where isnull(ConsolNo,0)<>0 ) AS AC1 ON SH.PK_OrderNo = AC1.FK_OrderNo ' --24316
--	SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ShipmentAirImport AS SAI WITH (NOLOCK) ON SH.PK_OrderNo = SAI.FK_OrderNo'
--end
--IF @tbl_ShipmentOceanImport_Used > 0 
--begin
--	--SET @FromValue = @FromValue + ' LEFT OUTER JOIN ( select top 1 FK_OrderNo,MOBL as OCMOBL, ConsolNo as OCConsolNo from tbl_OceanCost WITH (NOLOCK) where isnull(ConsolNo,0)=0 ) AS OC ON SH.PK_OrderNo = OC.FK_OrderNo' --24316
--	--SET @FromValue = @FromValue + ' LEFT OUTER JOIN ( select top 1 FK_OrderNo,MOBL as OCMOBL, ConsolNo as OCConsolNo from tbl_OceanCost WITH (NOLOCK) where isnull(ConsolNo,0)<>0 ) AS OC1 ON SH.PK_OrderNo = OC1.FK_OrderNo' --24316
--	SET @FromValue = @FromValue + ' LEFT OUTER JOIN tbl_ShipmentOceanImport AS SOI WITH (NOLOCK) ON SH.PK_OrderNo = SOI.FK_OrderNo'
--end
---- end 24316
----start 07/06/2021 23749
--IF (CHARINDEX(',HDStatusID',@Columns) <> 0 Or CHARINDEX(',HDStatusDescription',@Columns) <> 0 Or CHARINDEX(',HDApptDate',@Columns) <> 0 Or CHARINDEX(',HDApptDateTime',@Columns) <> 0 Or CHARINDEX(',HDApptDateTimeRange',@Columns) <> 0)
--	BEGIN
--		SET @FromValue = @FromValue + ' LEFT OUTER JOIN vw_HDWorkflow AS HDW WITH (NOLOCK) ON SH.PK_OrderNo = HDW.FK_OrderNo'
--	END
----end 07/06/2021 23749

SET @MainQuery = REPLACE(@MainQuery, '%DISTINCT%', @DistinctValue)
SET @MainQuery = REPLACE(@MainQuery, '%FROM%', @FromValue)
SET @MainQuery = REPLACE(@MainQuery, '%GROUPBY%', @GroupByValue)
SET @MainQuery = REPLACE(@MainQuery, '%WHERE%', CASE WHEN LEN(@WorkflowWhereClause) > 0 THEN 'WHERE ' + @WorkflowWhereClause ELSE '' END)

 --PRINT SUBSTRING(@MainQuery,0,4000)
 --PRINT SUBSTRING(@MainQuery,4000,4000)
 --PRINT SUBSTRING(@MainQuery,8000,4000)
 --PRINT SUBSTRING(@MainQuery,12000,4000)
 --PRINT SUBSTRING(@MainQuery,16000,4000)
 --PRINT SUBSTRING(@MainQuery,20000,4000)
 --PRINT SUBSTRING(@MainQuery,24000,4000)
 --PRINT SUBSTRING(@MainQuery,28000,4000)

SET @GeneratedView = @MainQuery





























GO