CREATE view [dbo].[vw_DispatchBoardAssigned] as
select PK_OrderNo as FK_OrderNo,ShipQuote,ShipmentType,isnull(SeqNo,0) as SeqNo,isnull(ManifestNo,0) as ManifestNo,isnull(ManifestSeqNo,0) as ManifestSeqNo,HandlingStation,ControllingStation,DestAirport,OrgAirport,Housebill,isnull(FK_DriverId,'') as FK_DriverId, a.FK_TractorCode, a.FK_TrailerCode,
	case
		when (select isnull(ControlValue,'') from tbl_Control where PK_ControlNo=1680) = 'Y' then concat(ShipName,'{13}',ShipAddress1,'{13}',ShipAddress2,'{13}',ShipCity,' ',FK_ShipState,', ',ShipZip)
		when a.Type='PU' or a.Type='RE' then concat(ShipName,'{13}',ShipAddress1,'{13}',ShipAddress2,'{13}',ShipCity,' ',FK_ShipState,', ',ShipZip)
		when a.Type='DR' or a.Type='DL' then concat(StationName,'{13}',StationAddress1,'{13}',StationAddress2,'{13}',StationCity,' ',FK_StationState,', ',StationZip)
		else concat(ShipName,'{13}',ShipAddress1,'{13}',ShipAddress2,'{13}',ShipCity,' ',FK_ShipState,', ',ShipZip)
	end as ShipperInfo,
	case
		when (select isnull(ControlValue,'') from tbl_Control where PK_ControlNo=1680) = 'Y' then concat(ConName,'{13}',ConAddress1,'{13}',ConAddress2,'{13}',ConCity,' ',FK_ConState,', ',ConZip)
		when a.Type='PU' or a.Type='RE' then concat(StationName,'{13}',StationAddress1,'{13}',StationAddress2,'{13}',StationCity,' ',FK_StationState,', ',StationZip)
		when a.Type='DR' or a.Type='DL' then concat(ConName,'{13}',ConAddress1,'{13}',ConAddress2,'{13}',ConCity,' ',FK_ConState,', ',ConZip)
		else concat(ConName,'{13}',ConAddress1,'{13}',ConAddress2,'{13}',ConCity,' ',FK_ConState,', ',ConZip)
	end as ConsigneeInfo,	
	ReadyDateTime,CONVERT(VARCHAR(5), ReadyDateTime,108) AS ReadyTime,isnull(ServiceDate,getdate()) as ServiceDate,isnull(h.ScheduledDateTime,getdate()) as ScheduledDateTime,CONVERT(VARCHAR(5), isnull(h.ScheduledDateTime,getdate()) ,108) AS ScheduledTime, FK_ServiceLevelId,FK_OrderStatusId,BillNo,isnull(CustName,'') as CustName,isnull(ReferenceNo,'') as CHB,isnull(a.Type,'') as MoveType,isnull(i.Note,'') as Notes,isnull(s.Status,'') as StatusDescription,isnull(ServiceLevel,'') as ServiceLevel,
	isnull(a.TractorDispatched,'') as TractorDispatched,h.NextTrackingDateTime,
	isnull((SELECT TOP (1) HoursAway
                                 FROM            dbo.tbl_TimeZoneMaster
                                 WHERE        (PK_TimeZoneCode = h.NextTrackingDateTimeZone)), 0) AS NextTrackingTZHoursAway,
								 concat(PK_OrderNo,'||',isnull(SeqNo,0),'||',isnull(FK_DriverId,'')) AS Dispatch,
								 concat(PK_OrderNo,'||',isnull(SeqNo,0),'||',isnull(FK_DriverId,'')) AS CompleteDispatch,
								 concat(PK_OrderNo,'||',isnull(SeqNo,0),'||',isnull(FK_DriverId,'')) AS ClearDispatch

	from tbl_ShipmentHeader h with (nolock) left join tbl_ShipmentDrivers a with (nolock) on PK_OrderNo=a.FK_OrderNo
	left join tbl_Shipper with (nolock) on PK_OrderNo=FK_ShipOrderNo
	left join tbl_Consignee with (nolock) on PK_OrderNo=FK_ConOrderNo
	left join tbl_Stations with (nolock) on h.HandlingStation=PK_StationId
	left join tbl_Customers with (nolock) on PK_CustNo=BillNo
	left join tbl_References r with (nolock) on PK_OrderNo=r.FK_OrderNo and CustomerType='S' and CHBRefTypeId=FK_RefTypeId
	left join tbl_Instructions i with (nolock) on PK_OrderNo=i.FK_OrderNo and i.Type='S'
	left join tbl_Statuses s with (nolock) on PK_OrderSTatusId=FK_OrderStatusId
	left join tbl_ServiceLevels with (nolock) on PK_ServiceLevelId=FK_ServiceLevelId
	left join tbl_ShipmentHeaderAUX aux with (nolock) on PK_OrderNo=aux.FK_OrderNo
	where ShipQuote='S' and h.Status='L' and ShowDriverFields='Y' and AssignToDispatch='Y' 



GO