2LhyJjbe

1USE [EDW_ODS]
2GO
3/****** Object:  StoredProcedure [dbo].[AdHocSearchWithLimit45]    Script Date: 10/14/2016 10:22:54 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9ALTER procedure [dbo].[AdHocSearchWithLimit45] 
10(
11	@LogID INT,
12	--Override the Date Limit for >13 Month Reports
13	@OverrideStartDate SMALLDATETIME = NULL,
14	@OverrideEndDate SMALLDATETIME = NULL
15)
16AS 
17--SET @LogID=3522307
18/*****************************************************************************************************************************
19	Changes:
20		02/04/2011 - suggmic - added comment block, versioned sproc because of versioned ad hoc report sproc
21		03/01/2011 - ANDERMIC - Versioned because added LoadHotY to AdHoc
22		04/20/2011 - SUGGMIC - Versioned ad hoc report sproc so this has to be versioned
23		07/25/2011 - okoaset - versioned ad hoc report to AdHocReport6
24		08/02/2011 - Okoaset - Versioned report to AdHocReport7.  Added searching by @datetype EstimatedPickupDate and EstimatedDeliveryDate
25		09/06/2011 - suggmic - added unicode support for DimLocation
26		10/17/2011 - OKOASET -  Added EmptyReturnDate,LastFreeDate  Update SP to vs 8
27		10/19/2011 - andermic - Version 8, Custom Equip Type search
28		12/16/2011 - andermic - Version 9, RL fix
29		12/29/2011 - andermic - Version 10, SpoofBookedFlag
30		02/17/2012 - andermic - Version 11, No longer showing cross-border locations
31		07/13/2012 - okoaset - version 12 added PO related change to AdHocSearch_POWithLimit. so had to verstion this sproc
32		08/07/2012 - Added joins to SPOrder to filter our Master loads.
33		08/22/2012 - Versioned to because AdHocReport got versioned.  For navisphere release 7.0
34		10/17/2012 - andermic - multileg order changes
35		12/06/2012 - rescemi  - Added ability to search by multipule W Codes. Version 15, Release 7.3
36		12/07/2012 - reesfra - Also for Version 15, Release 7.3; Added call to new Item report, it gets class value differently for TFS 
37		12/27/2012 - rescemi - Added ability to search by Origin/Destination Country. Version 16, Release 7.4 - Updated to AdHocReport15
38		04/22/2013 - HABECHR - Added orion notes- Updated to AdHocReport16
39		05/10/2013 - suggmic - added ML logic
40		06/22/2013 - suggmic - Added ' ' to display load # for excel formatting
41		12/04/2013 - okoaset -  cast CHRNumber to string in final select statement.  I am doing so, because of data merge in code beween this result set and 
42					PXH adhoc result set. per requirement, PHX HbL Number with is a string should be displayed in CHRNumber column. 
43		12/12/2013 - legejoh - Versioned up exit sproc and PO and RL sprocs
44		01/31/2014 - SUGGMIC - versioned up exit SPROC
45		02/26/2014 - SUGGMIC - versioned up exit SPROC
46		03/10/2014 - suggmic - added Min/Max expected weight @ item level, split ISF ID # and ISF Date
47		03/17/2014 - BOHRJOS - Changing logic for how the #SpoofStatus table is joined to in the WHERE clause that inserts into the #Row table.
48		03/26/2014 - BOHRJOS - Filter out shipments that were in the wrong statuses.
49		04/07/2014 - BOHRJOS - Added support for multiple modes.
50		04/10/2014 - okoaset - added weight Actual Weight issue.  change from join from inner to left.   Updated to AdHocReport22
51		08/14/2014 - rescemi - Updated reference to AdHocReport23 Version 25
52		08/14/2014 - rescemi - Updated reference to AdHocReport24 Version 26
53		12/04/2014 - jewebra - Updated reference to AdHocReport25, Corrected AdHocSearch_RLWithLimit version and POWithLimit version
54		04/22/2015 - oneimas - Update reference to AdHocReport26, AdHocSearch_RLWithLimit24 and AdHocSearch_POWithLimit15
55		06/03/2015 - oneimas - Update to the Warehouse temp tables. Making the warehouse code a varchar instead of char. CHRCommon.dbo.Warehouses is now a varchar
56		07/02/2015 - oneimas - Updated reference to AdHocReport27
57		08/04/2015 - oneimas - Updating #Row OrigKeyLocationID and DestKeyLocationID to be populated from SPODLocation table instead of COLocation
58		08/11/2015 - oneimas - Updating multi-leg O/D location query to populate OrigKeyLocationID and DestKeyLocationID from SPODLocation, joining on Loadnumber with COLocation
59		08/19/2015 - oneimas - Fixing problem with StopNumber not being included in the join. Also adding OrigLoadNumber and DestLoadNumber to #Row for Multi-Leg Shipment Level
60								Also adding OrigSPODKeyLocationID and DestSPODKeyLocationID for orders that do not have a supplier
61								Updating to AdHocReport28
62		08/25/2015 - oneimas - Fixing issue where EuroLegacyWarehouseCodes were not being filtered on causing old Euro Express records to not be returned
63		08/28/2015 - rescemi - Merging GFO changes
64		09/08/2015 - oneimas - Updating reference to AdHocReport29
65		11/04/2015 - oneimas - Updating to AdHocReport30
66		11/16/2015 - oneimas - Updating Multileg to work with cross border picks and drops
67		11/17/2015 - oneimas - Updating reference to AdHocReport31
68		12/21/2015 - menaron - Updating reference to AdHocReport32
69		02/15/2016 - habechr - Updating reference to AdHocReport33
70		03/09/2016 - RESCEMI - Updating reference to AdHocReport34, RL30, PO21
71		03/22/2016 - RESCEMI - Updating reference to AdHocReport35, RL31, PO22
72		06/09/2016 - RESCEMI - Updating reference to AdHocReport36, RL32, PO23, Adding Datelimit Override
73		07/06/2016 - RESCMEI - Updating reference to AdHocReport37, RL33, PO24
74		08/23/2016 - PHILBEN - Update to return CIN. Updating reference to AdHocReport38, RL34, PO25
75		09/01/2016 - HAGEAAR - When searching by InvoiceDate...use Invoice Released Date. Updating reference to AdHocReport39, RL35, PO26
76
77******************************************************************************************************************************/
78
79BEGIN
80
81SET NOCOUNT ON
82
83--DROP TABLE #CustomEquipmentTypes
84--DROP TABLE #StatusBucket
85--DROP TABLE #CustRefNum
86--DROP TABLE #CHRReference
87--DROP TABLE #CarrierProNum
88--DROP TABLE #InvoiceNumber
89--DROP TABLE #ListBillNumber
90
91
92
93
94DECLARE @ReportLevel INT
95DECLARE @OverrideDateLimit BIT = 0
96
97--Override the Date Limit for >13 Month Reports
98IF EXISTS (SELECT 1 WHERE (@OverrideStartDate IS NOT NULL AND @OverrideEndDate IS NOT NULL))
99BEGIN
100	SELECT @OverrideDateLimit = 1
101END
102
103SELECT @ReportLevel = A.ReportLevel
104FROM CHRWOnline.dbo.AdHocReport AS A WITH(NOLOCK)
105WHERE A.LogID = @LogID
106
107IF @ReportLevel = 32 OR @ReportLevel = 64
108BEGIN
109	EXEC AdHocSearch_RLWithLimit35 @LogID
110	RETURN
111END
112
113IF @ReportLevel = 128 
114BEGIN
115	EXEC AdHocSearch_POWithLimit26 @LogID
116	RETURN
117END
118
119DECLARE 
120		@UserID INT
121		,@SVID INT
122		,@OriginWCode VARCHAR(MAX)
123		,@DestinationWCode VARCHAR(MAX)
124		,@DateType VARCHAR(50)
125		,@StartDate SMALLDATETIME
126		,@EndDate SMALLDATETIME
127		,@TimeFrame VARCHAR(50)
128		,@Mode VARCHAR(500)
129		,@RoutedShipments VARCHAR(10)
130		,@SCAC CHAR(4)
131		,@Invoiced BIT
132		,@Compliance BIT
133		,@Expedited BIT
134		,@NoBill BIT
135		,@CustomsOnly BIT
136		,@OverDimensional BIT
137		,@PartialRef BIT
138		,@Status varchar(375)
139		,@CustRefNums varchar(100)
140		,@CHRNums varchar(100)
141		,@CustomerOrderNums varchar(100)
142		,@LoadNums varchar(100)
143		,@CarrierProNums varchar(100)
144		,@InvoiceNumber varchar(100)
145		,@ListBillNumber varchar(100)
146		,@Routing VARCHAR(10)
147		,@SummarizeShipment BIT
148		,@SummarizeOcean BIT
149		,@RoutingFeature BIT
150		,@ReverseLogisticsFeature BIT
151		,@ServiceLevel VARCHAR(50)
152		,@OriginAirportCode CHAR(3)
153		,@DestinationAirportCode CHAR(3)
154		,@SearchRollupWarehouses BIT
155		,@CustomEquipmentTypeList VARCHAR(255)
156		,@SpoofBookedStatus BIT
157		,@OriginCountry VARCHAR(10)
158		,@DestinationCountry VARCHAR(10)
159		,@StatusSourceID_OMS INT
160		,@StatusSourceID_Express INT
161		
162DECLARE 	
163	@SQL nvarchar(MAX),
164	@Select varchar(MAX),
165	@From varchar(MAX),
166	@Where varchar(MAX),
167	@IsOrderLevel VARCHAR(MAX)
168
169CREATE TABLE #StatusBucket(StatusBucket VARCHAR(75))
170CREATE TABLE #CustRefNum(RefNumber VARCHAR(100))
171CREATE TABLE #CarrierProNum(RefNumber VARCHAR(100))
172CREATE TABLE #InvoiceNumber(InvoiceNumber BIGINT)
173CREATE TABLE #ListBillNumber(ListBillNumber BIGINT)
174
175CREATE TABLE [#COService_CustomerRef](COServiceID INT, RefType VARCHAR(50)) 
176CREATE TABLE [#COService_CHRRef](COServiceID INT) 
177CREATE TABLE [#COService_CarrierProNumber](COServiceID INT) 
178CREATE TABLE [#COService_InvoiceNumber](COServiceID INT) 
179CREATE TABLE [#COService_ListBillNumber](COServiceID INT) 
180CREATE TABLE [#Status](StatusID INT, StatusSourceID INT)
181CREATE TABLE [#SpoofStatus](StatusID INT, StatusSourceID INT)
182CREATE TABLE [#CustomEquipmentTypes](CETID INT)
183CREATE TABLE [#Modes](Mode VARCHAR(15))
184
185CREATE TABLE #ServiceDetailID_Capture  
186(  
187	COSDetailID INT,  
188	SPODetailID INT   
189)  
190
191CREATE TABLE #SPODLocation_Capture
192(
193	COSDetailID INT,
194	SPODetailID INT,
195	KeyLocationID INT,
196	LocationType VARCHAR(2),
197	LocationStyle VARCHAR(10),
198	LoadNumber INT,
199	StopNumber INT
200)
201
202CREATE TABLE #UserCCodes
203(
204	CompanyCode char(10),
205	WarehouseCode varchar(10)
206)
207
208CREATE TABLE #ChildWCodes
209(
210	CompanyCode char(10),
211	WarehouseCode varchar(10)
212)
213
214CREATE TABLE #ChildDESTWCodes
215(
216	CompanyCode char(10),
217	WarehouseCode varchar(10)
218)
219
220CREATE TABLE #OriginWCodes
221(
222	WCode varchar(10)
223)
224
225CREATE TABLE #DestinationWCodes
226(
227	WCode varchar(10)
228)
229
230CREATE TABLE #RowID
231(
232	 RecordID									INT IDENTITY(1,1)
233	,LoadNumber									INT
234	,PlannedLoadNumber							INT
235	,CustomerOrderNumber						INT
236	,COSGroupID									INT
237	,COSDetailID								INT
238	,SourceTypeID								TINYINT
239)
240
241CREATE INDEX IDX_ROWID ON #ROWID
242(LoadNumber								
243	,PlannedLoadNumber							
244	,CustomerOrderNumber					
245	,COSGroupID							
246	,COSDetailID		
247	,SourceTypeID)
248INCLUDE(RecordID)
249
250CREATE TABLE #Row 
251(
252	 RecordID									INT
253	,DeletedFlg									BIT 
254	,OriginStopNumber							INT
255	,DestStopNumber								INT
256	,OriginLoadNumber							INT
257	,DestLoadNumber								INT
258	,OriginCOLocationID							INT
259	,DestCOLocationID							INT	
260	,OriginKeyLocationID						INT
261	,DestKeyLocationID							INT
262	,OriginSPODKeyLocationID					INT
263	,DestSPODKeyLocationID						INT
264	,OriginLocationPartyNumber					VARCHAR(80)
265	,DestLocationPartyNumber					VARCHAR(80)
266	,COServiceID								INT
267	,COSDetailID								INT 
268	,LoadNumber									INT
269	,CustomerOrderNumber						INT
270	,PlannedLoadNumber							INT
271	,COSGroupID									INT
272	,CHRNumber									INT
273	,DisplayLoadNumber							VARCHAR(512)
274	,DisplayCustomerOrderNumber					VARCHAR(16)
275	,ShipmentType								VARCHAR(20)
276	,LevelNumber								INT
277	,DisplayServiceFlg							BIT
278	,ShipmentMode								VARCHAR(15)
279	,LogisticFlg								BIT
280	,CustomsCOServiceID							INT
281	,SourceTypeID								TINYINT
282	,COrderID									INT
283	,TicketServiceID							INT
284	,HotFlg										BIT
285	,ServiceLevel								VARCHAR(50)
286	,LoadID										INT
287	,POItemID									INT
288	,POID										INT
289	,COItemID									INT
290	,MultiLegFlg								BIT DEFAULT(0)
291	,IntlCHRRole								VARCHAR(1)
292	,StatusSourceID								INT
293	,HBLFlag									BIT
294)
295/*NEW OR CHANGED Jeremy Dixon 11/2013*/
296CREATE INDEX IDX_ROW ON #ROW
297(LoadNumber								
298	,PlannedLoadNumber							
299	,CustomerOrderNumber					
300	,COSGroupID							
301	,COSDetailID		
302	,SourceTypeID)
303INCLUDE(RecordID)
304
305CREATE TABLE #ML_ORDERS (CustomerOrderNumber INT)
306
307CREATE TABLE #ML_ROW (
308	RecordId				INT, 
309	COServiceID				INT, 
310	COSDetailID				INT, 
311	LoadNumber				INT, 
312	CustomerOrderNumber		INT, 
313	PlannedLoadNumber		INT, 
314	COSGroupID				INT, 
315	CHRNumber				INT, 
316	ShipmentType			VARCHAR(20), 
317	LevelNumber				INT, 
318	ShipmentMode			VARCHAR(15), 
319	LogisticFlg				BIT, 
320	SourceTypeId			TINYINT, 
321	COrderID				INT, 
322	HotFlg					BIT,
323	ServiceLevel			VARCHAR(50), 
324	LoadID					INT, 
325	MultiLegFlg				BIT DEFAULT(0)
326)
327
328/*<><><><><><><>*/
329/*NEW OR CHANGED Jeremy Dixon 11/2013*/
330CREATE TABLE #HoldForGoodServices
331	(COServiceID INT, SPODetailID INT)
332
333/*NEW OR CHANGED Jeremy Dixon 11/2013*/
334CREATE TABLE #ActivityForGoodServices
335(
336	COServiceID INT,
337	SPOrderID INT 
338)
339/*NEW OR CHANGED Jeremy Dixon 11/2013*/
340CREATE TABLE #GS_COService
341	(COServiceID INT)
342
343/*NEW OR CHANGED Jeremy Dixon 11/2013*/
344CREATE TABLE #SingleODLoad
345(
346	 LoadNumber									INT
347	,SourceTypeID								TINYINT			
348)
349
350CREATE CLUSTERED INDEX INX_RowService On #Row(COServiceID)  
351CREATE INDEX INX_RowSDetail On #Row(COSDetailID)  
352CREATE INDEX INX_RowCHRNumber ON #Row(CHRNumber)  
353
354/********************************************************************************************************/
355/******************** LOAD VARIABLES WITH REPORT CRITERIA************************************************/
356/********************************************************************************************************/
357
358SELECT   @UserID = A.UserID
359		 ,@SVID = A.SessionViewID
360		,@OriginWCode = A.OriginWCode
361		,@DestinationWCode = A.DestinationWCode
362		,@DateType = A.DateType
363		,@StartDate = A.StartDate
364		,@EndDate = A.EndDate
365		,@TimeFrame = A.TimeFrame
366		,@Mode = A.Mode
367		,@RoutedShipments = A.RoutedShipments
368		,@SCAC = A.SCAC
369		,@Invoiced = A.Invoiced
370		,@Compliance = A.Compliance
371		,@Expedited = A.Expedited
372		,@NoBill = A.NoBill
373		,@CustomsOnly = A.CustomsOnly
374		,@OverDimensional = A.OverDimensional
375		,@PartialRef = A.PartialRef
376		,@Status = A.Status
377		,@CustRefNums = A.CustRefNums
378		,@CHRNums = A.CHRNums
379		,@CustomerOrderNums = A.CHROrderNums
380		,@LoadNums = A.CHRLoadNums
381		,@CarrierProNums = A.CarrierProNums
382		,@InvoiceNumber = A.InvoiceNums
383		,@ListBillNumber = A.ListBillNums
384		,@ServiceLevel = A.ServiceLevel
385		,@OriginAirportCode = A.OriginAirportCode
386		,@DestinationAirportCode = A.DestinationAirportCode 
387		,@SearchRollupWarehouses = A.SearchRollupWarehouses
388		,@CustomEquipmentTypeList = A.CustomEquipmentTypeID 
389		,@SpoofBookedStatus = COALESCE(A.SpoofBookedStatus, 0)
390		,@OriginCountry = SUBSTRING(A.OriginCountry, 0, 3)
391		,@DestinationCountry = SUBSTRING(A.DestinationCountry, 0, 3)
392FROM CHRWOnline.dbo.AdHocReport AS A WITH(NOLOCK)
393WHERE A.LogID = @LogID
394
395
396IF @OverrideDateLimit = 1
397BEGIN
398	SELECT @StartDate = @OverrideStartDate
399	SELECT @EndDate = @OverrideEndDate
400END
401
402SELECT	@StatusSourceID_OMS = 1,
403		@StatusSourceID_Express = 2
404
405IF @SearchRollupWarehouses IS NULL
406	SET @SearchRollupWarehouses = 0
407-- shipment summary = 1
408-- put levels from flags enum in table?
409IF @ReportLevel & 1 = 1
410BEGIN
411	SET @SummarizeShipment = 1
412END
413ELSE
414BEGIN
415	SET @SummarizeShipment = 0
416END
417
418-- ocean summary = 4
419IF @ReportLevel & 4 = 4
420BEGIN
421	SET @SummarizeOcean = 1
422END
423ELSE
424BEGIN
425	SET @SummarizeOcean = 0
426END
427
428INSERT INTO #UserCCodes (CompanyCode, WarehouseCode)
429SELECT CompanyCode, WarehouseCode 
430FROM CHRWOnline.dbo.UT_SVItem AS SVI WITH (NOLOCK)
431WHERE SessionViewID = @SVID AND SVI.Enabled = 1
432
433IF @SearchRollupWarehouses = 1
434BEGIN
435
436	INSERT INTO #ChildWCodes(CompanyCode, WarehouseCode)
437	SELECT UC.CompanyCode, W.CompanyCode
438	FROM #UserCCodes AS UC
439    INNER JOIN CHRCommon.dbo.Warehouses AS W WITH (NOLOCK)
440	ON W.RollUpWarehouseCode = UC.WarehouseCode
441
442	INSERT INTO #UserCCodes (CompanyCode, WarehouseCode)
443	SELECT DISTINCT CompanyCode, WarehouseCode
444	FROM #ChildWCodes
445
446END
447
448IF EXISTS
449	(
450	SELECT 1 
451	FROM CHRWOnline.dbo.UT_AssignedFeature AS AF WITH(NOLOCK) 
452		INNER JOIN CHRWOnline.dbo.UT_Feature AS F WITH(NOLOCK)
453			ON AF.FeatureID = F.FeatureID
454	WHERE 
455			AF.AssignedToID = @UserID 
456		AND F.FeatureName = 'Routing Link Display')
457BEGIN
458	SET @RoutingFeature = 1
459END
460ELSE
461BEGIN
462	SET @RoutingFeature = 0
463END 
464
465IF EXISTS
466	(
467	SELECT 1 
468	FROM CHRWOnline.dbo.UT_AssignedFeature AS AF WITH(NOLOCK) 
469		INNER JOIN CHRWOnline.dbo.UT_Feature AS F WITH(NOLOCK)
470			ON AF.FeatureID = F.FeatureID
471	WHERE 
472			AF.AssignedToID = @UserID 
473		AND F.FeatureName = 'ReverseLogisticsAdHocReports')
474BEGIN
475	SET @ReverseLogisticsFeature = 1
476END
477ELSE
478BEGIN
479	SET @ReverseLogisticsFeature = 0
480END 
481
482IF @TimeFrame IS NOT NULL
483BEGIN
484	DECLARE @CurrentDate VARCHAR(64), @SlidingTimePoint VARCHAR(8), @SlidingTimeRef VARCHAR(2), @SlidingDirection VARCHAR(16), @TimeFrameSQL VARCHAR(1024)
485	SET @CurrentDate = DateAdd(dd, 0, DateDiff(dd, 0, GETDATE()))
486
487	CREATE TABLE #TEMP_TIMEFRAME (StartDate DATE, EndDate DATE)
488
489	IF @TimeFrame LIKE '%|%'
490	BEGIN
491		SET @SlidingTimePoint = dbo.udf_split(@TimeFrame,'|',2)
492		SET @SlidingTimeRef = dbo.udf_split(@TimeFrame,'|',3)
493		SET @SlidingDirection = dbo.udf_split(@TimeFrame,'|',4)
494	END
495	ELSE 
496	BEGIN
497		SET @SlidingTimePoint = '0'
498		SET @SlidingTimeRef = 'dd'
499		SET @SlidingDirection = ''
500	END
501
502	IF @TimeFrame LIKE 'Yesterday%'
503	BEGIN
504		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
505							SELECT 
506								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
507											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) -1)
508											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) -1 END,
509								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
510											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) -1)
511											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) -1 END'							
512	END
513	ELSE IF @TimeFrame LIKE 'Today%'
514	BEGIN
515		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
516							SELECT 
517								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
518											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,CAST(''' + @CurrentDate + ''' AS SMALLDATETIME))
519											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) END,
520								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
521											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',CAST(''' + @CurrentDate + ''' AS SMALLDATETIME))
522											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) END'											
523	END
524	ELSE IF @TimeFrame LIKE 'Tomorrow%'
525	BEGIN
526		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
527							SELECT 
528								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
529											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) +1)
530											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) +1 END,
531								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
532											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) +1)
533											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) +1 END'										
534	END
535	ELSE IF @TimeFrame LIKE 'LastWeek%'
536	BEGIN
537		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
538							SELECT 
539								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
540											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,DATEADD(wk, DATEDIFF(wk, 6, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 6) - 7)
541											ELSE DATEADD(wk, DATEDIFF(wk, 6, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 6) - 7 END,
542								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
543											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',DATEADD(wk, DATEDIFF(wk, 5, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 5) - 7)
544											ELSE DATEADD(wk, DATEDIFF(wk, 5, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 5) - 7 END'																			
545	END
546	ELSE IF @TimeFrame LIKE 'CurrentWeek%'
547	BEGIN					
548		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
549							SELECT 
550								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
551											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,DATEADD(wk, DATEDIFF(wk, 6, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 6))
552											ELSE DATEADD(wk, DATEDIFF(wk, 6, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 6) END,
553								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
554											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',DATEADD(wk, DATEDIFF(wk, 5, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 5))
555											ELSE DATEADD(wk, DATEDIFF(wk, 5, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 5) END'																	
556	END	
557	ELSE IF @TimeFrame LIKE 'NextWeek%'
558	BEGIN
559		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
560							SELECT 
561								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
562											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,DATEADD(wk, DATEDIFF(wk, 6, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 6)) + 7
563											ELSE DATEADD(wk, DATEDIFF(wk, 6, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 6) + 7 END,
564								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
565											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',DATEADD(wk, DATEDIFF(wk, 5, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 5)) + 7
566											ELSE DATEADD(wk, DATEDIFF(wk, 5, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 5) + 7 END'								
567	END
568	ELSE IF @TimeFrame LIKE 'LastMonth%'
569	BEGIN
570		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
571							SELECT 
572								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
573											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,DATEADD(m, -1, DATEADD(mm, DATEDIFF(m, 0, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 0)))
574											ELSE DATEADD(m, -1, DATEADD(mm, DATEDIFF(m, 0, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 0)) END,
575								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
576											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',DATEADD(mi, -1, DATEADD(mm, DATEDIFF(m, 0, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 0)))
577											ELSE DATEADD(mi, -1, DATEADD(mm, DATEDIFF(m, 0, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)), 0)) END'							
578										
579	END
580	ELSE IF @TimeFrame LIKE 'CurrentMonth%'
581	BEGIN
582		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
583							SELECT 
584								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
585											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,DATEADD(dd, -DAY(CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)) + 1, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)))
586											ELSE DATEADD(dd, -DAY(CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)) + 1, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)) END,
587								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
588											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',DATEADD(dd, -1, DATEADD(mm, 1, DATEADD(dd, -DAY(CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)) + 1, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)))))
589											ELSE DATEADD(dd, -1, DATEADD(mm, 1, DATEADD(dd, -DAY(CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)) + 1, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)))) END'															
590	END
591	ELSE IF @TimeFrame LIKE 'NextMonth%'
592	BEGIN
593		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
594							SELECT 
595								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
596											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,DATEADD(dd, -(DAY(DATEADD(mm, 1, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)))-1),DATEADD(mm,1,CAST(''' + @CurrentDate + '''AS DATETIME))))
597											ELSE DATEADD(dd, -(DAY(DATEADD(mm, 1, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)))-1),DATEADD(mm,1,CAST(''' + @CurrentDate + '''AS DATETIME))) END,
598								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
599											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm, 0, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)) + 2,0)))
600											ELSE DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm, 0, CAST(''' + @CurrentDate + ''' AS SMALLDATETIME)) + 2,0)) END'								
601	END
602	ELSE IF @TimeFrame LIKE 'Next30Days%'
603	BEGIN
604		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
605							SELECT 
606								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
607											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,CAST(''' + @CurrentDate + ''' AS SMALLDATETIME))
608											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) END,
609								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
610											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) + 30)
611											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) + 30 END'								
612	END
613	ELSE IF @TimeFrame LIKE 'Last30Days%'
614	BEGIN
615		SET @TimeFrameSQL = 'INSERT INTO #TEMP_TIMEFRAME
616							SELECT 
617								StartDate = CASE WHEN ''' + @SlidingDirection + ''' = ''before'' 
618											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ' * -1,CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) - 30)
619											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) - 30 END,
620								EndDate   = CASE WHEN ''' + @SlidingDirection + ''' = ''after'' 
621											THEN DATEADD(' + @SlidingTimeRef + ',' + @SlidingTimePoint + ',CAST(''' + @CurrentDate + ''' AS SMALLDATETIME))
622											ELSE CAST(''' + @CurrentDate + ''' AS SMALLDATETIME) END'
623	END
624	
625	EXEC(@TimeFrameSQL)
626
627	
628	SELECT @StartDate = StartDate, @EndDate = DATEADD(s, -60, DATEADD(dd, 1, CAST(CAST(EndDate AS DATE) AS SMALLDATETIME))) FROM #TEMP_TIMEFRAME
629END
630
631CREATE TABLE #PotentialGroups
632(
633	COServiceID INT,
634	HBLFlag BIT,
635	COSGroupID INT,
636	OrderNumber INT
637)
638
639/********************************************************************************************************/
640/******************** Populate Temp Tables WITH list searches********************************************/
641/********************************************************************************************************/
642IF @CustRefNums IS NOT NULL AND @PartialRef = 0
643BEGIN	 
644	
645	INSERT INTO #CustRefNum(RefNumber)
646	SELECT REFNUM.VCHAR FROM dbo.VARCHAR_ListParser(@CustRefNums + ',') AS REFNUM
647
648	INSERT INTO #COService_CustomerRef (COServiceID, RefType)			
649	SELECT 
650		COSR.COServiceID, COSR.ReferenceType
651	FROM
652		#CustRefNum AS REFNUM
653		INNER JOIN NTSR.dbo.DimReference AS REF WITH (NOLOCK)
654			ON REFNUM.RefNumber = REF.ReferenceNumber
655		INNER JOIN dbo.COServiceReference AS COSR WITH (NOLOCK)
656			ON REF.KeyReferenceID = COSR.KeyReferenceID
657	
658	DELETE FROM [#COService_CustomerRef]
659	WHERE RefType = 'PRONUMBER'
660		
661	INSERT INTO #COService_CustomerRef (COServiceID)	
662	SELECT 
663		COSD.COServiceID
664	FROM
665		#CustRefNum AS REFNUM 
666		INNER JOIN NTSR.dbo.DimReference AS REF WITH (NOLOCK)
667			ON REFNUM.RefNumber = REF.ReferenceNumber
668		INNER JOIN dbo.COSDetailReference AS COSDR WITH (NOLOCK)
669			ON REF.KeyReferenceID = COSDR.KeyReferenceID
670		INNER JOIN dbo.COSDetail AS COSD WITH(NOLOCK)
671			ON COSDR.COSDetailID = COSD.COSDetailID
672END
673
674IF @CHRNums IS NOT NULL OR @LoadNums IS NOT NULL OR @CustomerOrderNums IS NOT NULL
675BEGIN
676	IF @CHRNums IS NOT NULL
677	BEGIN
678		INSERT INTO #COService_CHRRef(COServiceID)			
679			SELECT 
680				COSN.COServiceID
681			FROM
682				(SELECT REFNUM.VCHAR AS RefNumber FROM dbo.VARCHAR_ListParser(@CHRNums + ',') REFNUM) AS CHRNUM 
683				INNER JOIN dbo.COSNumber AS COSN WITH (NOLOCK)
684					ON CHRNUM.RefNumber = COSN.CHRNumber
685	END
686
687	IF @LoadNums IS NOT NULL
688	BEGIN
689		INSERT INTO #COService_CHRRef(COServiceID)			
690			SELECT 
691				COSN.COServiceID
692			FROM
693				(SELECT REFNUM.VCHAR AS RefNumber FROM dbo.VARCHAR_ListParser(@LoadNums + ',') REFNUM) AS CHRNUM 
694				INNER JOIN dbo.COSNumber AS COSN WITH (NOLOCK)
695					ON CHRNUM.RefNumber = COSN.CHRNumber AND COSN.CHRNumberType = 'Load'
696				LEFT JOIN #COService_CHRRef T ON COSN.COServiceID = T.COServiceID
697				WHERE T.COServiceID IS NULL
698	END
699
700	IF @CustomerOrderNums IS NOT NULL
701	BEGIN
702		INSERT INTO #COService_CHRRef(COServiceID)			
703			SELECT 
704				COSN.COServiceID
705			FROM
706				(SELECT REFNUM.VCHAR AS RefNumber FROM dbo.VARCHAR_ListParser(@CustomerOrderNums + ',') REFNUM) AS CHRNUM 
707				INNER JOIN dbo.COSNumber AS COSN WITH (NOLOCK)
708					ON CHRNUM.RefNumber = COSN.CHRNumber AND COSN.CHRNumberType IN('OrionOrder', 'SubOrder')
709				LEFT JOIN #COService_CHRRef T ON COSN.COServiceID = T.COServiceID
710				WHERE T.COServiceID IS NULL
711	END
712END
713
714IF @CarrierProNums IS NOT NULL
715BEGIN
716
717	INSERT INTO #CarrierProNum(RefNumber)
718	SELECT REFNUM.VCHAR FROM dbo.VARCHAR_ListParser(@CarrierProNums + ',') AS REFNUM
719
720	INSERT INTO #COService_CarrierProNumber(COServiceID)			
721	SELECT 
722		COSR.COServiceID
723	FROM
724		#CarrierProNum AS CPNUM 
725		INNER JOIN NTSR.dbo.DimReference AS REF WITH (NOLOCK)
726			ON CPNUM.RefNumber = REF.ReferenceNumber
727		INNER JOIN dbo.COServiceReference AS COSR WITH (NOLOCK)
728			ON REF.KeyReferenceID = COSR.KeyReferenceID
729			AND COSR.ReferenceType = 'PRONUMBER'
730END
731
732IF @InvoiceNumber IS NOT NULL
733BEGIN
734
735	INSERT INTO #InvoiceNumber(InvoiceNumber)
736	SELECT REFNUM.BINT FROM dbo.BIGINT_ListParser(@InvoiceNumber + ',') AS REFNUM
737
738	INSERT INTO #COService_InvoiceNumber(COServiceID)			
739	SELECT 
740		COSI.COServiceID
741	FROM
742		#InvoiceNumber AS INUM 
743		INNER JOIN dbo.OrderInvoice AS OI WITH(NOLOCK)
744			ON INUM.InvoiceNumber = OI.InvoiceID
745		INNER JOIN dbo.COServiceInvoice AS COSI WITH (NOLOCK)
746			ON OI.OrderInvoiceID = COSI.OrderInvoiceID
747
748	UNION ALL
749
750	SELECT 
751		COSI.COServiceID
752	FROM
753		#InvoiceNumber AS INUM
754		INNER JOIN dbo.OrderInvoice AS OI WITH(NOLOCK)
755			ON INUM.InvoiceNumber = OI.InvoiceNumber
756		INNER JOIN dbo.COServiceInvoice AS COSI WITH (NOLOCK)
757			ON OI.OrderInvoiceID = COSI.OrderInvoiceID
758END
759
760IF @ListBillNumber IS NOT NULL
761BEGIN
762
763	INSERT INTO #ListBillNumber(ListBillNumber)
764	SELECT REFNUM.BINT FROM dbo.BIGINT_ListParser(@ListBillNumber + ',') AS REFNUM -- GANASAD 05/05/2009 - REFNUM.INTEGER to REFNUM.BINT to take into account ListBillNumber's new BIGINT status 
765
766	INSERT INTO #COService_ListBillNumber(COServiceID)			
767	SELECT 
768		COSI.COServiceID
769	FROM
770		#ListBillNumber AS ILBN 
771		INNER JOIN dbo.OrderInvoice AS OI WITH(NOLOCK)
772			ON ILBN.ListBillNumber = OI.ListBillID
773		INNER JOIN dbo.COServiceInvoice AS COSI WITH (NOLOCK)
774			ON OI.OrderInvoiceID = COSI.OrderInvoiceID
775END
776IF @Status IS NOT NULL
777BEGIN
778	-- Possibilities for @Status (CSV)
779	-- Incomplete/On Hold,Ordered,Booked/Tendered to Carrier,Loading/In Transit,Unloading/Delivered
780	INSERT INTO #StatusBucket(StatusBucket)
781	SELECT REFNUM.VCHAR FROM dbo.VARCHAR_ListParser(@Status + ',') AS REFNUM
782	
783	INSERT INTO #Status(StatusID, StatusSourceID)			
784	SELECT 
785		DS.KeyStatusID
786		,statusSource.SourceID
787	FROM
788		#StatusBucket AS SB
789		INNER JOIN CHRWOnline.dbo.UT_StatusMappingItem AS statusMap WITH(NOLOCK)
790			ON SB.StatusBucket = statusMap.ActivityBoardBucket
791		INNER JOIN CHRWOnline.dbo.UT_StatusSource AS statusSource WITH (NOLOCK)
792			ON statusMap.SourceSystem = statusSource.SourceID
793			AND  statusSource.SourceName='EXPRESS'
794		INNER JOIN NTSR.dbo.DimStatus AS DS WITH(NOLOCK)
795			ON statusMap.SourceSystemStatus = DS.LoadStatus
796		INNER JOIN CHRWOnline.dbo.UT_StatusMapping AS map WITH(NOLOCK)
797			ON statusMap.StatusMappingID = map.StatusMappingID
798		INNER JOIN CHRWOnline.dbo.UT_User AS U WITH(NOLOCK)
799			ON map.StatusMappingCode = U.StatusMappingCode
800	WHERE U.UserID = @UserID
801	UNION ALL
802	SELECT 
803		DS.KeyStatusID
804		,statusSource.SourceID
805	FROM
806		#StatusBucket AS SB
807		INNER JOIN CHRWOnline.dbo.UT_StatusMappingItem AS statusMap WITH(NOLOCK)
808			ON SB.StatusBucket = statusMap.ActivityBoardBucket
809		INNER JOIN CHRWOnline.dbo.UT_StatusSource AS statusSource WITH (NOLOCK)
810			ON statusMap.SourceSystem = statusSource.SourceID
811			AND  statusSource.SourceName='OMS'
812		INNER JOIN NTSR.dbo.DimStatus AS DS WITH(NOLOCK)
813			ON statusMap.SourceSystemStatus = DS.OrderStatus
814		INNER JOIN CHRWOnline.dbo.UT_StatusMapping AS map WITH(NOLOCK)
815			ON statusMap.StatusMappingID = map.StatusMappingID
816		INNER JOIN CHRWOnline.dbo.UT_User AS U WITH(NOLOCK)
817			ON map.StatusMappingCode = U.StatusMappingCode
818	WHERE U.UserID = @UserID
819	UNION ALL
820	SELECT
821		DS.KeyStatusID
822		, statusSource.SourceID
823	FROM
824		#StatusBucket AS SB
825		INNER JOIN CHRWOnline.dbo.UT_StatusMappingItem AS statusMap WITH(NOLOCK)
826			ON SB.StatusBucket = statusMap.ActivityBoardBucket
827		INNER JOIN CHRWOnline.dbo.UT_StatusSource AS statusSource WITH (NOLOCK)
828			ON statusMap.SourceSystem = statusSource.SourceID
829			AND  statusSource.SourceName='EXPRESS'
830		INNER JOIN NTSR.dbo.DimStatus AS DS WITH(NOLOCK)
831			ON statusMap.SourceSystemStatus = DS.OrderStatus
832		INNER JOIN CHRWOnline.dbo.UT_StatusMapping AS map WITH(NOLOCK)
833			ON statusMap.StatusMappingID = map.StatusMappingID
834		INNER JOIN CHRWOnline.dbo.UT_User AS U WITH(NOLOCK)
835			ON map.StatusMappingCode = U.StatusMappingCode
836	WHERE U.UserID = @UserID
837END
838
839IF NULLIF(@Mode,'') IS NOT NULL
840BEGIN
841
842	INSERT INTO #Modes(Mode)
843	SELECT ModeList.VCHAR FROM dbo.VARCHAR_ListParser(@Mode + ',') AS ModeList
844
845END
846
847-- create spoof status table
848IF @SpoofBookedStatus = 1 BEGIN	
849	-- Figured out candidates for spoofing (i.e. which records are in Ordered status)
850	INSERT INTO #SpoofStatus (StatusID, StatusSourceID) 
851		SELECT 
852			DS.KeyStatusID
853			,statusSource.SourceID
854		FROM CHRWOnline.dbo.UT_StatusMappingItem AS statusMap WITH(NOLOCK)				
855			INNER JOIN CHRWOnline.dbo.UT_StatusSource AS statusSource WITH (NOLOCK)
856				ON statusMap.SourceSystem = statusSource.SourceID
857				AND statusSource.SourceName = 'EXPRESS'
858			INNER JOIN NTSR.dbo.DimStatus AS DS WITH(NOLOCK)
859				ON statusMap.SourceSystemStatus = DS.LoadStatus
860			INNER JOIN CHRWOnline.dbo.UT_StatusMapping AS map WITH(NOLOCK)
861				ON statusMap.StatusMappingID = map.StatusMappingID
862			INNER JOIN CHRWOnline.dbo.UT_User AS U WITH(NOLOCK)
863				ON map.StatusMappingCode = U.StatusMappingCode
864		WHERE 
865			statusMap.ActivityBoardBucket = 'Ordered' AND
866			U.UserID = @UserID
867		UNION ALL
868		SELECT 
869			DS.KeyStatusID
870			,statusSource.SourceID
871		FROM CHRWOnline.dbo.UT_StatusMappingItem AS statusMap WITH(NOLOCK)					
872			INNER JOIN CHRWOnline.dbo.UT_StatusSource AS statusSource WITH (NOLOCK)
873				ON statusMap.SourceSystem = statusSource.SourceID
874				AND statusSource.SourceName = 'OMS'
875			INNER JOIN NTSR.dbo.DimStatus AS DS WITH(NOLOCK)
876				ON statusMap.SourceSystemStatus = DS.OrderStatus
877			INNER JOIN CHRWOnline.dbo.UT_StatusMapping AS map WITH(NOLOCK)
878				ON statusMap.StatusMappingID = map.StatusMappingID
879			INNER JOIN CHRWOnline.dbo.UT_User AS U WITH(NOLOCK)
880				ON map.StatusMappingCode = U.StatusMappingCode
881		WHERE 
882			statusMap.ActivityBoardBucket = 'Ordered' AND
883			U.UserID = @UserID	
884END
885
886
887IF @CustomEquipmentTypeList IS NOT NULL 
888	SET @CustomEquipmentTypeList = LTRIM(RTRIM(@CustomEquipmentTypeList))
889
890IF ISNULL(@CustomEquipmentTypeList, '') <> '' BEGIN
891	INSERT INTO #CustomEquipmentTypes(CETID) 
892		SELECT DISTINCT 
893			CETID.VCHAR 
894		FROM dbo.VARCHAR_ListParser(@CustomEquipmentTypeList + ',') AS CETID
895END
896
897IF @OriginWCode IS NOT NULL
898BEGIN
899	SET @OriginWCode = REPLACE(@OriginWCode, '|', ',')
900
901	INSERT INTO #OriginWCodes(WCode)
902	SELECT WCODE.CHAR10 FROM dbo.CHAR10_ListParser(@OriginWCode + ',') AS WCODE
903END
904
905IF @DestinationWCode IS NOT NULL
906BEGIN
907	
908	SET @DestinationWCode = REPLACE(@DestinationWCode, '|', ',')
909	INSERT INTO #DestinationWCodes(WCode)
910	SELECT WCODE.CHAR10 FROM dbo.CHAR10_ListParser(@DestinationWCode + ',') AS WCODE
911END
912
913/********************************************************************************************************/
914/******************** Construct Dynamic Seqarch Query****************************************************/
915/********************************************************************************************************/
916
917SET @IsOrderLevel = ' 1=0 '
918IF @ReportLevel & 2 = 2 OR (@SummarizeShipment = 0 OR @SummarizeOcean = 0)
919	SET @IsOrderLevel = ' 1=1 '
920
921----------------------------------------------------------------------------------------------------
922--Build Select Statement ---------------------------------------------------------------------------
923----------------------------------------------------------------------------------------------------
924-- 09/02/2008 GANASAD Changed CHRSubOrderNumber -> CustomerOrderNumber and added SourceTypeID 
925SET @Select   = '	INSERT INTO #Row 
926					(	
927						RecordID
928						,COServiceID
929						,COSDetailID
930						,LoadNumber	
931						,CustomerOrderNumber	
932						,PlannedLoadNumber
933						,COSGroupID
934						,CHRNumber
935						,ShipmentType
936						,LevelNumber
937						,ShipmentMode
938						,LogisticFlg	
939						,CustomsCOServiceID		
940						,SourceTypeID 	
941						,COrderID
942						,HotFlg
943						,ServiceLevel															
944						,LoadID															
945						,MultiLegFlg
946						,IntlCHRRole
947						,HBLFlag
948						,StatusSourceID
949					) 
950					SELECT DISTINCT
951						0 AS RecordID
952						,CS.COServiceID AS COServiceID
953						,COSD.COSDetailID AS COSDetailID 
954						,CS.FirstLoadNumber AS LoadNumber
955						,CS.CustomerOrderNumber AS CustomerOrderNumber
956						,CS.FirstPlannedLoadNumber AS PlannedLoadNumber
957						,CASE WHEN CS.CustomsFlg =1 THEN -1 ELSE ISNULL(CS.COSGroupID,-1) END AS COSGroupID
958						,CASE 
959							WHEN ' + @IsOrderLevel + ' AND CS.PooledFlg = 1 AND CS.SourceTypeID = 2 THEN CS.CustomerOrderNumber
960							WHEN CS.CustomerOrderNumber > 0 AND CS.FirstPlannedLoadNumber = -1 AND CS.FirstLoadNumber = -1 THEN CS.CustomerOrderNumber --Unplanned Suborder 
961							WHEN CS.CustomerOrderNumber > 0 AND CS.FirstLoadNumber > 0 AND (CS.PooledFlg =1 OR CS.CrossDockFlg=1) AND ' + CAST(@RoutingFeature AS VARCHAR(5)) + '=1 THEN CS.FirstLoadNumber --Pooled|CrossDock SubOrder with Routing Feature
962							WHEN CS.CustomerOrderNumber > 0 AND CS.FirstPlannedLoadNumber > 0 AND (CS.PooledFlg =1 OR CS.CrossDockFlg=1) AND ' + CAST(@RoutingFeature AS VARCHAR(5)) + '=0 THEN CS.CustomerOrderNumber --Pooled|CrossDock SubOrder without Routing Feature
963							WHEN CS.CustomerOrderNumber > 0 AND CS.FirstLoadNumber > 0 AND CS.PooledFlg=0 AND CS.CrossDockFlg=0 THEN CS.FirstLoadNumber --SubOrder on the load and one per OD pair option is selected. 10/23/2008 KRAVVYA Per meeting with Branch users for Non-pooled order they want to see LoadNumber on Commodity and OD level report. Old Code AND  SummarizeShipment =0
964							WHEN CS.CustomerOrderNumber =-1 AND CS.FirstLoadNumber > 0 THEN CS.FirstLoadNumber
965							WHEN CS.CustomerOrderNumber > 0 AND CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber =-1 THEN CS.CustomerOrderNumber --SubOrder is planned but load is not in Express
966						END CHRNumber
967						,CASE
968							WHEN CS.SourceTypeID = 7 AND CS.FirstPlannedLoadNumber < 0 AND CS.FirstLoadNumber < 0 THEN ''Multi Intl Load''
969							WHEN CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND CS.FirstPlannedLoadNumber = -1 AND CS.FirstLoadNumber = -1  THEN ''ORDER''
970							WHEN CS.PooledFlg =1 OR CS.CrossDockFlg=1 THEN ''Pooled''
971							WHEN CS.PooledFlg =0 AND CS.CrossDockFlg=0  AND CS.FirstLoadNumber > 0 AND ISNULL(L.MaxStopNbr,0) <=1 AND ISNULL(L.InternationalFlg,0) = 0 THEN ''Single OD LOAD''
972							WHEN CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber = -1 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) <=1 AND ISNULL(L.InternationalFlg,0) = 0 THEN ''Single OD Plan Load''
973							WHEN CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber = -1 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) > 1 THEN ''Multi OD Plan LOAD''
974							WHEN CS.CustomsFlg =1 THEN ''Customs Load''
975							WHEN ISNULL(L.InternationalFlg,0) = 1 and NULLIF(CS.COSGroupID,0) IS NULL THEN ''Single Intl Load''
976							WHEN CS.FirstLoadNumber >0 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) > 1 AND ISNULL(L.InternationalFlg,0) = 0 THEN ''Multi OD Load''
977							WHEN ISNULL(L.InternationalFlg,0) = 1 and NULLIF(CS.COSGroupID,0) IS NOT NULL AND CS.CustomsFlg =0 THEN ''Multi Intl Load''
978						END AS ShipmentType
979						,CASE
980							WHEN CS.SourceTypeID = 7 AND CS.FirstPlannedLoadNumber < 0 AND CS.FirstLoadNumber < 0 THEN 2 --''Multi Intl Load''
981							WHEN CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND CS.FirstPlannedLoadNumber = -1 AND CS.FirstLoadNumber = -1    THEN 1--Order
982							WHEN CS.PooledFlg =1 OR CS.CrossDockFlg=1 THEN 1--Pooled Load
983							WHEN CS.PooledFlg =0 AND CS.CrossDockFlg=0  AND CS.FirstLoadNumber > 0 AND ISNULL(L.MaxStopNbr,0) <=1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 1--Single OD Load
984							WHEN CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber = -1 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) <=1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 1 --Single OD Plan Load
985							WHEN CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber = -1 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) > 1 THEN 2--Multi OD Plan Load
986							WHEN CS.CustomsFlg =1 THEN 1 --Customs
987							WHEN ISNULL(L.InternationalFlg,0) = 1 and NULLIF(CS.COSGroupID,0) IS NULL THEN 1--Single Intl Load
988							WHEN CS.FirstLoadNumber >0 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) > 1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 2--Multi OD Load
989							WHEN ISNULL(L.InternationalFlg,0) = 1 and NULLIF(CS.COSGroupID,0) IS NOT NULL AND CS.CustomsFlg =0 THEN 2--Multi Intl Load
990						END AS LevelNumber
991						,CS.Mode AS ShipmentMode
992						,ISNULL(CS.LogisticFlg, 0) AS LogisticFlg
993						,NULL AS CustomsCOServiceID
994						,CS.SourceTypeID AS SourceTypeID 
995						,CS.COrderID
996						,CS.HotFlg
997						,CS.ServiceLevel
998						,L.LoadID
999						,CASE WHEN CS.PooledFlg = 1 AND CS.SourceTypeID = 2 THEN 1 ELSE 0 END
1000						,CS.IntlCHRRole
1001						,CS.HBLFlag
1002					'
1003
1004IF @Status IS NOT NULL
1005BEGIN
1006	SELECT @Select = @Select + ',status.StatusSourceID
1007					'
1008END
1009ELSE
1010BEGIN
1011	SELECT @Select = @Select + ',NULL
1012					'
1013END
1014
1015					--WHEN CS.CustomerOrderNumber > 0 AND CS.FirstLoadNumber > 0 AND CS.PooledFlg=0 AND CS.CrossDockFlg=0 AND ' + CAST(@SummarizeShipment AS VARCHAR(5)) + '=1 THEN CS.FirstLoadNumber--SubOrder on the load and summary option is selected
1016--------------------------------------------------------------------------------------------------
1017--Build From Statement ---------------------------------------------------------------------------
1018--------------------------------------------------------------------------------------------------
1019
1020/*NEW OR CHANGED Jeremy Dixon 11/2013*/
1021IF EXISTS (SELECT TOP 1 1 FROM #UserCCodes WHERE CompanyCode IS NULL)
1022BEGIN 
1023SET @From = ' FROM 
1024	        dbo.COService											AS		   CS		WITH(NOLOCK)
1025			INNER JOIN dbo.COSDetail								AS         COSD		WITH(NOLOCK) 
1026				ON CS.COServiceID = COSD.COServiceID
1027			LEFT OUTER JOIN dbo.[Load]								AS			L		WITH(NOLOCK)
1028				ON CS.FirstLoadNumber = L.LoadNumber
1029				AND CS.FirstPlannedLoadNumber = L.PlannedLoadNumber
1030				AND CS.SourceTypeID = L.SourceTypeID 
1031			INNER JOIN dbo.COSDetailAccess							AS		   CSDA	WITH(NOLOCK)
1032				ON COSD.COSDetailID = CSDA.COSDetailID 
1033			INNER JOIN #UserCCodes									AS			CC		
1034				ON CSDA.CustomerCode = ISNULL(CC.CompanyCode, CSDA.CustomerCode)
1035				AND ISNULL(CSDA.WarehouseCode,'''') = ISNULL(CC.WarehouseCode,'''')  '
1036END
1037ELSE 
1038BEGIN 
1039SET @From = ' FROM 
1040	        dbo.COService											AS		   CS		WITH(NOLOCK)
1041			INNER JOIN dbo.COSDetail								AS         COSD		WITH(NOLOCK) 
1042				ON CS.COServiceID = COSD.COServiceID
1043			LEFT OUTER JOIN dbo.[Load]								AS			L		WITH(NOLOCK)
1044				ON CS.FirstLoadNumber = L.LoadNumber
1045				AND CS.FirstPlannedLoadNumber = L.PlannedLoadNumber
1046				AND CS.SourceTypeID = L.SourceTypeID 
1047			INNER JOIN dbo.COSDetailAccess							AS		   CSDA	WITH(NOLOCK)
1048				ON COSD.COSDetailID = CSDA.COSDetailID 
1049			INNER JOIN #UserCCodes									AS			CC		
1050				ON CSDA.CustomerCode = CC.CompanyCode
1051				AND ISNULL(CSDA.WarehouseCode,'''') = ISNULL(CC.WarehouseCode,'''')  '
1052END
1053
1054IF @OriginWCode IS NOT NULL OR @OriginCountry IS NOT NULL OR @OriginAirportCode IS NOT NULL -- 09/16/2008 GANASAD LSXI 
1055BEGIN
1056	SET @From = @From + '
1057	INNER JOIN dbo.COLocation										AS			COLO	WITH(NOLOCK)
1058		ON COSD.COSDetailID = COLO.COSDetailID 
1059	INNER JOIN [NTSR].[dbo].[DimLocation]							AS			ORIG	WITH(NOLOCK) 
1060		ON COLO.KeyLocationID = ORIG.KeyLocationID
1061	'	
1062	IF @SearchRollupWarehouses = 1 AND (SELECT Count(1) from #UserCCodes WHERE WarehouseCode is not null) > 0
1063	BEGIN
1064		
1065		Truncate Table #ChildWCodes
1066
1067	INSERT INTO #ChildWCodes(CompanyCode, WarehouseCode)
1068	SELECT UC.CompanyCode, W.CompanyCode
1069	FROM #UserCCodes AS UC 
1070    INNER JOIN CHRCommon.dbo.Warehouses AS W WITH (NOLOCK)
1071	ON W.RollUpWarehouseCode = UC.WarehouseCode
1072	INNER JOIN #OriginWCodes AS OriginWCodes WITH (NOLOCK)
1073	ON UC.WarehouseCode = OriginWCodes.WCode
1074
1075		SET @From = @From + 'INNER JOIN #ChildWCodes AS	C 
1076			ON ORIG.WarehouseCode = CAST(C.WarehouseCode AS NVARCHAR(50)) AND (COLO.COLocationType = ''P'' OR (CS.SourceTypeID = 2 AND COLO.COLocationType = ''XP''))
1077		'	
1078	
1079	END
1080END
1081
1082IF @DestinationWCode IS NOT NULL OR @DestinationCountry IS NOT NULL OR @DestinationAirportCode IS NOT NULL -- 09/16/2008 GANASAD LSXI 
1083BEGIN
1084	SET @From = @From + ' 
1085	INNER JOIN dbo.COLocation AS COLD WITH	(NOLOCK)
1086		ON COSD.COSDetailID = COLD.COSDetailID 
1087	INNER JOIN [NTSR].[dbo].[DimLocation] AS DEST WITH(NOLOCK) 
1088		ON COLD.KeyLocationID = DEST.KeyLocationID
1089	'
1090IF @SearchRollupWarehouses = 1 AND (SELECT Count(1) from #UserCCodes WHERE WarehouseCode is not null) > 0
1091	BEGIN
1092		
1093	INSERT INTO #ChildDESTWCodes(CompanyCode, WarehouseCode)
1094	SELECT UC.CompanyCode, W.CompanyCode
1095	FROM #UserCCodes AS UC 
1096    INNER JOIN CHRCommon.dbo.Warehouses AS W WITH (NOLOCK)
1097	ON W.RollUpWarehouseCode = UC.WarehouseCode
1098	INNER JOIN #DestinationWCodes AS DestinationCodes WITH (NOLOCK)
1099	ON UC.WarehouseCode = DestinationCodes.WCode
1100
1101		SET @From = @From + 'INNER JOIN #ChildDESTWCodes AS	CD 
1102			ON DEST.WarehouseCode = CAST(CD.WarehouseCode AS NVARCHAR(50))  AND (COLD.COLocationType = ''D'' OR (CS.SourceTypeID = 2 AND COLD.COLocationType = ''XD''))
1103		'	
1104	END	
1105END
1106
1107IF @DateType IS NOT NULL AND
1108@StartDate IS NOT NULL AND
1109@EndDate IS NOT NULL
1110BEGIN
1111	IF @DateType = 'RequestedPickupDate' OR @DateType = 'RequestedDeliveryDate'
1112	BEGIN
1113		SET @From = @From + ' 
1114			INNER JOIN dbo.COLocation AS COL WITH(NOLOCK)
1115				ON COSD.COSDetailID = COL.COSDetailID 
1116			INNER JOIN dbo.COLocationDate AS D_E WITH(NOLOCK)
1117				ON COL.COLocationID = D_E.COLocationID
1118				AND D_E.DateValue >=' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@StartDate)) + '
1119			INNER JOIN dbo.COLocationDate AS D_L WITH(NOLOCK)
1120				ON COL.COLocationID = D_L.COLocationID 
1121				AND D_L.DateValue <=' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@EndDate)) 
1122		
1123	END
1124	IF @DateType = 'ScheduledPickupDate' OR @DateType = 'ScheduledDeliveryDate' OR @DateType = 'ActualPickupDate' OR @DateType = 'ActualDeliveryDate'
1125	BEGIN
1126		SET @From = @From + ' 
1127		INNER JOIN dbo.PDetail AS PD WITH(NOLOCK)
1128			ON COSD.COSDetailID = PD.COSDetailID
1129		INNER JOIN dbo.PDLocation AS PDL WITH(NOLOCK)
1130			ON PD.PDetailID = PDL.PDetailID 
1131		INNER JOIN dbo.SPODLocation AS SPODL WITH(NOLOCK)
1132			ON PDL.SPODLocationID = SPODL.SPODLocationID 
1133		INNER JOIN dbo.SPODLocationDate AS D_E WITH(NOLOCK)
1134			ON SPODL.SPODLocationID = D_E.SPODLocationID
1135			AND D_E.DateValue >=' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@StartDate)) + '
1136		INNER JOIN dbo.SPODLocationDate AS D_L WITH(NOLOCK)
1137			ON SPODL.SPODLocationID = D_L.SPODLocationID 
1138			AND D_L.DateValue <=' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@EndDate)) 
1139	END
1140	IF @DateType = 'EstimatedPortArrivalDate' OR @DateType = 'EstimatedPortDepartureDate' OR @DateType = 'ActualPortArrivalDate' OR @DateType = 'ActualPortDepartureDate' 
1141	BEGIN
1142		SET @From = @From + ' 
1143		INNER JOIN dbo.Activity AS A WITH(NOLOCK)
1144			ON COSD.COServiceID = A.COServiceID
1145		INNER JOIN dbo.SPOrder AS SPODR WITH (NOLOCK)
1146			ON A.SPOrderID = SPODR.SPOrderID AND ISNULL(SPODR.ShipmentType,''H'') = ''H''
1147		INNER JOIN dbo.SPOrderDate AS SPOD WITH(NOLOCK)
1148			ON A.SPOrderID = SPOD.SPOrderID 
1149			AND SPOD.DateValue BETWEEN ' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@StartDate)) + ' AND ' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@EndDate))
1150		
1151	END
1152	IF @DateType = 'CreatedDateTime' OR @DateType = 'TruckloadActivityDate' OR @DateType = 'EstimatedPickupDate' OR @DateType = 'EstimatedDeliveryDate'
1153	BEGIN
1154		SET @From = @From + ' 
1155		INNER JOIN dbo.COServiceDate AS CSD WITH(NOLOCK)
1156			ON COSD.COServiceID = CSD.COServiceID
1157			AND CSD.DateValue BETWEEN ' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@StartDate)) + ' AND ' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@EndDate))
1158	END
1159	IF @DateType = 'InvoicePrintDate'
1160	BEGIN
1161		SET @From = @From + ' 
1162		INNER JOIN dbo.COServiceInvoice AS CSI WITH(NOLOCK)
1163			ON COSD.COServiceID = CSI.COServiceID
1164		INNER JOIN dbo.OrderInvoice AS I WITH(NOLOCK)
1165			ON CSI.OrderInvoiceID = I.OrderInvoiceID
1166		INNER JOIN OrderInvoiceDate AS ID WITH (NOLOCK)
1167			ON ID.OrderInvoiceID = I.OrderInvoiceID
1168			AND ID.DateType = ''Invoice Released Date'''
1169		
1170		IF @StartDate = @EndDate
1171		BEGIN
1172			SET @From = @From + 'AND ID.DateValue =' +  convert(varchar(12),EDW_Stage.dbo.GetDateDimKeyID(@StartDate))
1173		END
1174		ELSE
1175		BEGIN
1176			SET @From = @From + 'AND ID.DateValue BETWEEN ' + 
1177				convert(varchar(12),EDW_Stage.dbo.GetDateDimKeyID(@StartDate)) + ' AND ' + convert(varchar(12),EDW_Stage.dbo.GetDateDimKeyID(@EndDate))
1178		END
1179	END
1180	IF @DateType = 'CustomsEntryDate' OR @DateType = 'CustomsReleaseDate'
1181	BEGIN
1182		SET @From = @From + ' 
1183		INNER JOIN dbo.Customs AS CU WITH(NOLOCK)
1184			ON COSD.COServiceID = CU.COServiceID '
1185	END
1186END
1187
1188IF @CustomsOnly IS NOT NULL
1189BEGIN
1190	SET @From = @From + ' 
1191	INNER JOIN dbo.COrder AS CO WITH(NOLOCK)
1192			ON CS.COrderID = CO.COrderID '
1193END
1194
1195IF @SCAC IS NOT NULL
1196BEGIN
1197	IF @DateType = 'EstimatedPortArrivalDate' OR @DateType = 'EstimatedPortDepartureDate' OR @DateType = 'ActualPortArrivalDate' OR @DateType = 'ActualPortDepartureDate' 
1198	BEGIN
1199		SET @From = @From + ' 
1200			INNER JOIN dbo.SPOParty AS SPOP WITH (NOLOCK)
1201				ON A.SPOrderID = SPOP.SPOrderID 
1202			INNER JOIN dbo.OrderParty AS SOP WITH(NOLOCK)
1203				ON SPOP.OrderPartyID = SOP.OrderPartyID '
1204	END
1205	ELSE
1206	BEGIN
1207		SET @From = @From + ' 
1208			INNER JOIN dbo.Activity AS A WITH(NOLOCK)
1209				ON COSD.COServiceID = A.COServiceID
1210			INNER JOIN dbo.SPOrder AS SPODR WITH (NOLOCK)
1211				ON A.SPOrderID = SPODR.SPOrderID AND ISNULL(SPODR.ShipmentType,''H'') = ''H''
1212			INNER JOIN dbo.SPOParty AS SPOP WITH (NOLOCK)
1213				ON A.SPOrderID = SPOP.SPOrderID 
1214			INNER JOIN dbo.OrderParty AS SOP WITH(NOLOCK)
1215				ON SPOP.OrderPartyID = SOP.OrderPartyID '
1216	END
1217END
1218IF @CustRefNums IS NOT NULL AND @PartialRef = 0
1219BEGIN
1220	SET @From = @From + ' INNER JOIN #COService_CustomerRef AS CustomerRef	ON COSD.COServiceID = CustomerRef.COServiceID '
1221END
1222IF @CHRNums IS NOT NULL OR @LoadNums IS NOT NULL OR @CustomerOrderNums IS NOT NULL
1223BEGIN
1224	SET @From = @From + ' INNER JOIN #COService_CHRRef AS CHRRef ON COSD.COServiceID = CHRRef.COServiceID '
1225END
1226IF @CarrierProNums IS NOT NULL
1227BEGIN
1228	SET @From = @From + ' INNER JOIN #COService_CarrierProNumber AS CarrierProNumber ON COSD.COServiceID = CarrierProNumber.COServiceID '
1229END
1230IF @InvoiceNumber IS NOT NULL
1231BEGIN
1232	SET @From = @From + ' INNER JOIN #COService_InvoiceNumber AS InvoiceNumber ON COSD.COServiceID = InvoiceNumber.COServiceID '
1233END
1234IF @ListBillNumber IS NOT NULL
1235BEGIN
1236	SET @From = @From + ' INNER JOIN #COService_ListBillNumber AS ListBillNumber ON COSD.COServiceID = ListBillNumber.COServiceID '
1237END
1238
1239IF @Status IS NOT NULL 
1240BEGIN
1241	-- check if we need to include Ordered records in our search for "Booked" records, due to spoofing
1242	IF @SpoofBookedStatus IS NOT NULL AND @SpoofBookedStatus = 1 AND 
1243		EXISTS (SELECT StatusID FROM #SpoofStatus) AND
1244		EXISTS (SELECT TOP 1 * FROM #StatusBucket WHERE StatusBucket = 'Booked/Tendered to Carrier')
1245	BEGIN				
1246		-- filter on UNION
1247		SET @From = @From + ' INNER JOIN (SELECT StatusID, StatusSourceID FROM #Status UNION SELECT StatusID, StatusSourceID FROM #SpoofStatus) AS Status ' + 
1248								'ON	CS.KeyStatusID = status.StatusID '
1249	END
1250	ELSE BEGIN
1251		SET @From = @From + ' INNER JOIN #Status AS Status ON	CS.KeyStatusID = status.StatusID '	
1252	END
1253
1254	SET @From = @From + ' and status.StatusSourceID = 
1255						(CASE 
1256						 WHEN (CS.CustomerOrderNumber > 0 AND CS.FirstLoadNumber < 0 AND CS.SourceTypeID = 7) THEN 2
1257						 WHEN (' + convert(varchar, @SummarizeShipment) + ' = 1 AND (CASE WHEN CS.PooledFlg = 1 AND CS.SourceTypeID = 2 THEN 1 ELSE 0 END) = 1) THEN 2 
1258						 WHEN (CS.CustomerOrderNumber > 0 AND CS.PooledFlg = 0 AND CS.FirstLoadNumber > 0) THEN 2 
1259						 WHEN (CS.CustomerOrderNumber > 0 OR (' + convert(varchar, @SummarizeShipment) + ' = 0 AND (CASE WHEN CS.PooledFlg = 1 AND CS.SourceTypeID = 2 THEN 1 ELSE 0 END) = 1)) THEN 1 
1260						 ELSE 2 
1261						 END) '
1262
1263END
1264
1265IF NULLIF(@Mode,'') IS NOT NULL
1266BEGIN
1267
1268	SET @From = @From + 'INNER JOIN #Modes AS MD ON CS.Mode = MD.Mode '
1269
1270END
1271
1272IF ISNULL(@CustomEquipmentTypeList, '') <> '' BEGIN
1273	SET @From = @From + ' INNER JOIN #CustomEquipmentTypes AS CET ON CET.CETID = L.CustomEquipmentTypeID '
1274END
1275
1276IF ISNULL(@OriginWCode, '') <> '' BEGIN
1277	SET @From = @From + ' INNER JOIN #OriginWCodes AS OriginWCodes ON OriginWCodes.WCode = ORIG.WarehouseCode AND (COLO.COLocationType = ''P'' OR (CS.SourceTypeID = 2 AND COLO.COLocationType = ''XP''))'
1278END
1279
1280IF ISNULL(@DestinationWCode, '') <> '' BEGIN
1281	SET @From = @From + ' INNER JOIN #DestinationWCodes AS DestinationWCodes ON DestinationWCodes.WCode = DEST.WarehouseCode  AND (COLD.COLocationType = ''D'' OR (CS.SourceTypeID = 2 AND COLD.COLocationType = ''XD''))'
1282END
1283
1284 --Build Where Statement ---------------------------------------------------------------------------
1285SET @Where = ' Where 1=1 '
1286
1287IF @RoutedShipments = 'CHR' 
1288BEGIN
1289	SET @Where = @Where + ' AND CS.OMSRouting = 0 '
1290END
1291ELSE IF @RoutedShipments = 'Non-CHR'
1292BEGIN
1293	SET @Where = @Where + ' AND CS.OMSRouting = 1 '
1294END
1295
1296IF @OriginCountry IS NOT NULL 
1297BEGIN
1298	SET @Where = @Where + ' AND ORIG.Country = N''' + CAST(@OriginCountry AS NVARCHAR(100)) + ''' AND (COLO.COLocationType = ''P'' OR (CS.SourceTypeID = 2 AND COLO.COLocationType = ''XP''))'
1299END
1300
1301IF @DestinationCountry IS NOT NULL 
1302BEGIN
1303	SET @Where = @Where + ' AND DEST.Country = N''' + CAST(@DestinationCountry AS NVARCHAR(100)) + '''  AND (COLD.COLocationType = ''D'' OR (CS.SourceTypeID = 2 AND COLD.COLocationType = ''XD''))'
1304END
1305
1306-- 09/16/2008 GANASAD LSXI 
1307IF @OriginAirportCode IS NOT NULL 
1308BEGIN 
1309	SET @Where = @Where + ' AND ORIG.AirportCode = N''' + CAST(@OriginAirportCode AS NVARCHAR(5)) + ''' AND (COLO.COLocationType = ''P'' OR (CS.SourceTypeID = 2 AND COLO.COLocationType = ''XP''))'
1310END 
1311
1312-- 09/16/2008 GANASAD LSXI 
1313IF @DestinationAirportCode IS NOT NULL 
1314BEGIN 
1315	SET @Where = @Where + ' AND DEST.AirportCode = N''' + CAST(@DestinationAirportCode AS NVARCHAR(50)) + ''' AND (COLD.COLocationType = ''D'' OR (CS.SourceTypeID = 2 AND COLD.COLocationType = ''XD''))'
1316END 
1317
1318-- 09/16/2008 GANASAD LSXI 
1319IF @ServiceLevel IS NOT NULL
1320BEGIN 
1321	SET @Where = @Where + ' AND CS.ServiceLevel = ''' + @ServiceLevel + ''''
1322END 
1323
1324IF @DateType IS NOT NULL AND
1325@StartDate IS NOT NULL AND
1326@EndDate IS NOT NULL
1327BEGIN
1328	IF @DateType = 'RequestedPickupDate'
1329	BEGIN
1330		SET @Where = @Where + ' AND COL.COLocationType=''P'' AND D_E.DateType = ''Earliest Requested Pickup Date'' AND (D_L.DateType = ''Latest Requested Pickup Date'' OR D_L.DateType = ''Earliest Requested Pickup Date'')'
1331	END
1332	IF @DateType = 'RequestedDeliveryDate'
1333	BEGIN
1334		SET @Where = @Where + ' AND COL.COLocationType=''D'' AND D_E.DateType = ''Earliest Requested Delivery Date'' AND (D_L.DateType = ''Latest Requested Delivery Date'' OR D_L.DateType = ''Earliest Requested Delivery Date'')'
1335	END
1336	IF @DateType = 'ScheduledPickupDate'
1337	BEGIN
1338		SET @Where = @Where + ' AND PDL.LocationType = ''P'' AND D_E.DateType = ''Earliest Scheduled Pickup Date'' AND (D_L.DateType = ''Latest Scheduled Pickup Date'' OR D_L.DateType = ''Earliest Scheduled Pickup Date'')'
1339	END
1340	IF @DateType = 'ScheduledDeliveryDate'
1341	BEGIN
1342		SET @Where = @Where + ' AND PDL.LocationType = ''D'' AND  D_E.DateType = ''Earliest Scheduled Delivery Date'' AND (D_L.DateType = ''Latest Scheduled Delivery Date'' OR D_L.DateType = ''Earliest Scheduled Delivery Date'')'
1343	END
1344	IF @DateType = 'EstimatedPortArrivalDate'
1345	BEGIN
1346		SET @Where = @Where + ' AND SPOD.DateType = ''Port ETA Date'''
1347	END
1348	IF @DateType = 'EstimatedPortDepartureDate'
1349	BEGIN
1350		SET @Where = @Where + ' AND SPOD.DateType = ''Port ETD Date'''
1351	END
1352	IF @DateType = 'ActualPickupDate'
1353	BEGIN
1354		SET @Where = @Where + ' AND PDL.LocationType = ''P'' AND (D_E.DateType = ''Earliest Actual Pickup Date'' OR D_E.DateType = ''Latest Actual Pickup Date'') AND (D_L.DateType = ''Latest Actual Pickup Date'' OR D_L.DateType = ''Earliest Actual Pickup Date'')'
1355	END
1356	IF @DateType = 'ActualDeliveryDate'
1357	BEGIN
1358		SET @Where = @Where + ' AND PDL.LocationType = ''D'' AND (D_E.DateType = ''Earliest Actual Delivery Date'' OR D_E.DateType = ''Latest Actual Delivery Date'') AND (D_L.DateType = ''Latest Actual Delivery Date'' OR D_L.DateType = ''Earliest Actual Delivery Date'')'
1359	END
1360	IF @DateType = 'ActualPortArrivalDate'
1361	BEGIN
1362		SET @Where = @Where + ' AND SPOD.DateType = ''Actual Port Arrival Date'''
1363	END
1364	IF @DateType = 'ActualPortDepartureDate'
1365	BEGIN
1366		SET @Where = @Where + ' AND SPOD.DateType = ''Actual Port Departure Date'''
1367	END
1368	IF @DateType = 'CreatedDateTime'
1369	BEGIN
1370		SET @Where = @Where + ' AND CSD.DateType = ''Enter Date'''
1371	END
1372	IF @DateType = 'TruckloadActivityDate'
1373	BEGIN
1374		SET @Where = @Where + ' AND CSD.DateType = ''Truckload Activity Date'''
1375	END
1376	IF @DateType = 'EstimatedPickupDate'
1377	BEGIN
1378		SET @Where = @Where + ' AND CSD.DateType = ''Ready By Date'''
1379	END
1380	IF @DateType = 'EstimatedDeliveryDate'
1381	BEGIN
1382		SET @Where = @Where + ' AND CSD.DateType = ''Deliver By Date'''
1383	END
1384	IF @DateType = 'CustomsEntryDate'
1385	BEGIN
1386		-- 04/24/2008 GANASAD EntryDate is a smalldatetime, not an int
1387		SET @Where = @Where + ' AND CU.EntryDate BETWEEN ''' + CAST(@StartDate AS VARCHAR) + ''' AND ''' + CAST(@EndDate AS VARCHAR) + ''''
1388--		SET @Where = @Where + ' AND CU.EntryDate BETWEEN ' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@StartDate)) + ' AND ' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@EndDate))
1389	END
1390	IF @DateType = 'CustomsReleaseDate'
1391	BEGIN
1392		-- 04/24/2008 GANASAD ReleaseDate is a smalldatetime, not an int
1393		SET @Where = @Where + ' AND CU.ReleaseDate BETWEEN ''' + CAST(@StartDate AS VARCHAR) + ''' AND ''' + CAST(@EndDate AS VARCHAR) + ''''
1394--		SET @Where = @Where + ' AND CU.ReleaseDate BETWEEN ' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@StartDate)) + ' AND ' + convert(varchar(12), EDW_Stage.dbo.GetDateDimKeyID(@EndDate))
1395	END
1396END
1397IF @SCAC IS NOT NULL
1398BEGIN
1399	SET @Where = @Where + ' AND SOP.SCAC = ''' + @SCAC + ''''
1400END
1401IF @Invoiced = 0
1402BEGIN
1403	SET @Where = @Where + ' AND NOT EXISTS (SELECT 1 FROM dbo.COServiceInvoice AS COSI WITH(NOLOCK) WHERE COSD.COServiceID = COSI.COServiceID)'
1404END
1405IF @Invoiced = 1
1406BEGIN
1407	SET @Where = @Where + ' AND EXISTS (SELECT 1 FROM dbo.COServiceInvoice AS COSI WITH(NOLOCK) WHERE COSD.COServiceID = COSI.COServiceID)'
1408END
1409IF @Compliance IS NOT NULL
1410BEGIN
1411	SET @Where = @Where + ' AND CS.ComplianceFlg = ' + CAST(@Compliance AS CHAR(2))
1412END
1413IF @Expedited IS NOT NULL
1414BEGIN
1415	SET @Where = @Where + ' AND CS.ExpeditedFlg = ' + CAST(@Expedited AS CHAR(2))
1416END
1417IF @NoBill IS NOT NULL
1418BEGIN
1419	SET @Where = @Where + ' AND CS.NoBillFlg = ' + CAST(@NoBill AS CHAR(2))
1420END
1421IF @CustomsOnly IS NOT NULL
1422BEGIN
1423	SET @Where = @Where + ' AND CO.CustomsOnlyFlg = ' + CAST(@CustomsOnly AS CHAR(2))
1424END
1425IF @Routing IS NOT NULL
1426BEGIN
1427	SET @Where = @Where + CASE @Routing WHEN 'CHR' THEN ' AND CS.OMSRouting = 0' WHEN 'NON-CHR' THEN ' AND CS.OMSRouting = 1' END
1428END
1429
1430IF ISNULL(@CustomEquipmentTypeList, '') <> '' BEGIN
1431	SET @Where = @Where + ' AND L.LoadID IS NOT NULL '
1432END
1433
1434SET @SQL = @Select + @From + @Where
1435--PRINT @Select
1436--PRINT @From
1437--PRINT @Where
1438			
1439-- about a second faster
1440EXECUTE sp_executesql @SQL  --sp_executesql faster?
1441
1442-- ====================================================================
1443-- Time for some mind-bending GFO grouping logic!
1444-- ====================================================================
1445
1446INSERT INTO #PotentialGroups
1447SELECT DISTINCT
1448	Everybody.COServiceID, Everybody.HBLFlag, Everybody.COSGroupID, Everybody.CustomerOrderNumber
1449FROM
1450	#Row R
1451	INNER JOIN COSGroup AS COSG WITH (NOLOCK)
1452		ON R.COSGroupID = COSG.COSGroupID
1453	INNER JOIN COService AS Everybody WITH (NOLOCK)
1454		ON Everybody.COSGroupID = COSG.COSGroupID
1455WHERE R.SourceTypeID = 7 AND R.LoadNumber < 0
1456
1457;WITH grps(COSGroupID,Number) AS   
1458(  
1459	SELECT COSGroupID, COUNT(HBLFlag) AS Number
1460	FROM #PotentialGroups PG
1461	WHERE PG.HBLFlag = 0
1462	GROUP BY HBLFlag, COSGroupID
1463)
1464Update #Row
1465SET
1466	ShipmentType = 'Single Intl Load',
1467	LevelNumber = 1
1468WHERE COSGroupID IN(Select COSGroupID FROM grps WHERE Number < 2) AND HBLFlag = 1
1469
1470;WITH grps(COSGroupID,Number) AS   
1471(  
1472	SELECT COSGroupID, COUNT(HBLFlag) AS Number
1473	FROM #Row R
1474	WHERE R.HBLFlag = 1
1475	GROUP BY HBLFlag, COSGroupID  
1476)
1477DELETE R FROM #Row AS R
1478WHERE R.COSGroupID IN(Select COSGroupID FROM grps WHERE Number > 1) AND HBLFlag = 0
1479
1480;WITH grps(COSGroupID,Number) AS   
1481(  
1482	SELECT COSGroupID, COUNT(HBLFlag) AS Number
1483	FROM #Row R
1484	WHERE R.HBLFlag = 0 AND R.SourceTypeID = 7
1485	GROUP BY HBLFlag, COSGroupID
1486)
1487Update R
1488SET
1489	R.CustomerOrderNumber = Parent.CustomerOrderNumber,
1490	R.CHRNumber = Parent.CHRNumber
1491FROM grps G, #Row R
1492INNER JOIN #Row AS Parent
1493	ON Parent.COSGroupID = R.COSGroupID
1494WHERE R.COSGroupID IN(Select COSGroupID FROM grps WHERE Number > 1) AND Parent.COSGroupID IN(Select COSGroupID FROM grps WHERE Number > 1) AND R.HBLFlag = 0
1495
1496;WITH grps(COSGroupID,Number) AS   
1497(
1498	SELECT COSGroupID, COUNT(HBLFlag) AS Number
1499	FROM #Row R
1500	WHERE R.HBLFlag = 0
1501	GROUP BY HBLFlag, COSGroupID
1502)
1503DELETE R FROM #Row AS R
1504WHERE R.COSGroupID IN(Select COSGroupID FROM grps WHERE Number > 1) AND HBLFlag = 1
1505
1506-- ====================================================================
1507-- Remove loads that are older than 13 months
1508-- ====================================================================
1509
1510--SUGGMIC - ADD LEGS FOR MULTI-LEG BASED OFF OF ORIGINAL SEARCH RESULTS
1511IF EXISTS(SELECT TOP 1 1 FROM #Row WHERE MultiLegFlg = 1)
1512BEGIN
1513
1514	INSERT INTO #ML_ORDERS (CustomerOrderNumber)
1515		SELECT DISTINCT CustomerOrderNumber FROM #Row WHERE MultiLegFlg = 1
1516
1517	IF @SummarizeShipment = 1
1518	BEGIN
1519		DELETE R
1520		FROM #Row R
1521		INNER JOIN #ML_ORDERS O ON R.CustomerOrderNumber = O.CustomerOrderNumber
1522
1523		INSERT INTO #ML_ROW(RecordId, COServiceID, COSDetailID, LoadNumber, CustomerOrderNumber, PlannedLoadNumber, 
1524						COSGroupID, CHRNumber, ShipmentType, LevelNumber, ShipmentMode, LogisticFlg, 
1525						SourceTYpeId, COrderID, HotFlg, ServiceLevel, LoadID, MultiLegFlg)
1526				SELECT DISTINCT
1527					0 AS RecordId,
1528					CS.COServiceID AS COServiceID,
1529					COSD.COSDetailID AS COSDetailId,
1530					O.LoadNumber AS LoadNumber,
1531					CS.CustomerOrderNumber AS CustomerOrderNumber,
1532					-1 AS PlannedLoadNumber,
1533					-1 AS COSGroupID,
1534					CASE WHEN O.LoadNumber IS NOT NULL AND O.LoadNumber > 0 THEN O.LoadNumber ELSE CS.CustomerOrderNumber END,
1535					'Pooled' AS ShipmentType,
1536					1 AS ShipmentLevel,
1537					CS.Mode AS ShipmentMode,
1538					ISNULL(CS.LogisticFlg, 0) AS LogisticFlg,
1539					2 AS SourceTypeId,
1540					CS.COrderID AS COOrderID,
1541					CS.HotFlg AS HotFlg,
1542					CS.ServiceLevel AS ServiceLevel,
1543					L.LoadID AS LoadId,
1544					1 AS MultiLegFlg
1545				FROM #ML_ORDERS R
1546				INNER JOIN dbo.COService AS CS WITH(NOLOCK)	ON R.CustomerOrderNumber = CS.CustomerOrderNumber 
1547				INNER JOIN dbo.Activity	AS A WITH(NOLOCK) ON CS.COServiceID = A.COServiceID 
1548				INNER JOIN dbo.SPOrder AS O WITH(NOLOCK) ON A.SPOrderID = O.SPOrderID 
1549				INNER JOIN dbo.COSDetail AS COSD WITH(NOLOCK) ON CS.COServiceID = COSD.COServiceID
1550				INNER JOIN dbo.COSDetailAccess AS CSDA	WITH(NOLOCK) ON COSD.COSDetailID = CSDA.COSDetailID 
1551				INNER JOIN #UserCCodes AS CC ON CSDA.CustomerCode = ISNULL(CC.CompanyCode, CSDA.CustomerCode) AND ISNULL(CSDA.WarehouseCode,'') = ISNULL(CC.WarehouseCode,'')  
1552				LEFT OUTER JOIN dbo.[Load] AS L WITH(NOLOCK) ON CS.FirstLoadNumber = L.LoadNumber AND CS.FirstPlannedLoadNumber = L.PlannedLoadNumber AND CS.SourceTypeID = L.SourceTypeID 
1553
1554		SET @Select = '
1555		INSERT INTO #ROW(RecordId, COServiceID, COSDetailID, LoadNumber, CustomerOrderNumber, PlannedLoadNumber, 
1556				COSGroupID, CHRNumber, ShipmentType, LevelNumber, ShipmentMode, LogisticFlg, 
1557				SourceTYpeId, COrderID, HotFlg, ServiceLevel, LoadID, MultiLegFlg)
1558		SELECT
1559			R.RecordId, 
1560			R.COServiceID, 
1561			R.COSDetailID, 
1562			R.LoadNumber, 
1563			R.CustomerOrderNumber, 
1564			R.PlannedLoadNumber, 
1565			R.COSGroupID, 
1566			R.CHRNumber, 
1567			R.ShipmentType, 
1568			R.LevelNumber, 
1569			R.ShipmentMode, 
1570			R.LogisticFlg, 
1571			R.SourceTYpeId, 
1572			R.COrderID, 
1573			R.HotFlg, 
1574			R.ServiceLevel, 
1575			R.LoadID, 
1576			R.MultiLegFlg
1577		'
1578		SET @From = ' FROM #ML_ROW R
1579		'
1580				
1581		IF ISNULL(@OriginWCode, '') <> ''
1582		BEGIN
1583			SET @From = @From + '
1584				INNER JOIN dbo.COLocation COLO (NOLOCK) ON COLO.COSDetailID = R.COSDetailID AND R.LoadNumber = COLO.LoadNumber
1585				INNER JOIN NTSR.dbo.DimLocation ORIG (NOLOCK) ON ORIG.KeyLocationID = COLO.KeyLocationID
1586				INNER JOIN #OriginWCodes OW ON OW.WCode = ORIG.WarehouseCode AND COLO.LocationType IN (''P'', ''XP'')
1587			'
1588		END
1589
1590		IF ISNULL(@DestinationWCode, '') <> ''
1591		BEGIN
1592			SET @From = @From + '
1593				INNER JOIN dbo.COLocation COLD (NOLOCK) ON COLD.COSDetailID = R.COSDetailID AND R.LoadNumber = COLD.LoadNumber
1594				INNER JOIN NTSR.dbo.DimLocation DEST (NOLOCK) ON DEST.KeyLocationID = COLD.KeyLocationID
1595				INNER JOIN #DestinationWCodes DW ON DW.WCode = DEST.WarehouseCode AND COLD.LocationType IN (''D'', ''XD'')
1596			'
1597		END
1598
1599		SET @SQL = @Select + @From
1600		EXECUTE sp_executesql @SQL
1601	END
1602	ELSE IF @SummarizeShipment = 0 AND EXISTS(SELECT TOP 1 1 FROM #Row WHERE MultiLegFlg = 1)
1603	BEGIN
1604		--ADD LOGIC TO FIGURE OUT WHEN THIS SHOULD RUN
1605		UPDATE #Row
1606			SET DisplayLoadNumber = STUFF(
1607								(	
1608									SELECT DISTINCT
1609										', ' + CAST(O.LoadNumber AS VARCHAR(32))
1610									FROM #ML_ORDERS M
1611									INNER JOIN dbo.COService AS CS WITH(NOLOCK)	ON R.CustomerOrderNumber = CS.CustomerOrderNumber 
1612									INNER JOIN dbo.Activity	AS A WITH(NOLOCK) ON CS.COServiceID = A.COServiceID 
1613									INNER JOIN dbo.SPOrder AS O WITH(NOLOCK) ON A.SPOrderID = O.SPOrderID 
1614									WHERE M.CustomerOrderNumber = R.CustomerOrderNumber AND O.LoadNumber > 0 AND O.LoadNumber IS NOT NULL
1615									FOR XML PATH(''), TYPE).value('.', 'VARCHAR(512)'), 1, 1, ''
1616								) 
1617							FROM #Row R
1618							WHERE R.MultiLegFlg = 1
1619	END
1620
1621	IF @CustomerOrderNums IS NOT NULL
1622	BEGIN
1623		DELETE R 
1624		FROM #ROW R
1625		LEFT JOIN (SELECT REFNUM.VCHAR AS RefNumber FROM dbo.VARCHAR_ListParser(@CustomerOrderNums + ',') REFNUM) AS CHRNUM ON R.CustomerOrderNumber = CHRNUM.RefNumber
1626		WHERE CHRNUM.RefNumber IS NULL
1627	END
1628	
1629	IF @LoadNums IS NOT NULL
1630	BEGIN
1631		DELETE R
1632		FROM #ROW R
1633		LEFT JOIN (SELECT REFNUM.VCHAR AS RefNumber FROM dbo.VARCHAR_ListParser(@LoadNums + ',') REFNUM) AS CHRNUM ON R.LoadNumber = CHRNUM.RefNumber
1634		WHERE CHRNUM.RefNumber IS NULL
1635	END
1636END
1637
1638CREATE TABLE #GoodServices
1639(
1640	COServiceID INT 
1641)
1642
1643DECLARE 
1644	@CutoffDate DATETIME,
1645	@CutoffDateINT INT,
1646	@MaxDate DATETIME,
1647	@MaxDateINT INT 
1648
1649SELECT
1650	@CutoffDate = DATEADD(mm, -13, GETDATE()),
1651	@CutoffDateINT = EDW_Stage.dbo.GetDateDimKeyId(@CutoffDate),
1652	@MaxDate = DATEADD(YY, 10, GETDATE()),
1653	@MaxDateINT = EDW_Stage.dbo.GetDateDimKeyId(@MaxDate)
1654
1655--Override the Date Limit for >13 Month Reports
1656IF @OverrideDateLimit = 1
1657BEGIN
1658	SELECT @CutoffDate = 0
1659END
1660
1661--Bypass this for GFO records
1662INSERT INTO 
1663       #GoodServices 
1664SELECT 
1665       R.COServiceID 
1666FROM 
1667       #Row AS R 
1668WHERE
1669       R.SOURCETYPEID = 7
1670
1671-- COServiceDate 
1672INSERT INTO 
1673	#GoodServices 
1674SELECT 
1675	R.COServiceID
1676FROM 
1677	#Row AS R 
1678INNER JOIN 
1679	COServiceDate AS D WITH(NOLOCK)
1680		ON R.COServiceID = D.COServiceID 
1681GROUP BY 
1682	R.COServiceID 
1683HAVING 
1684	MAX(D.DateValue) >= @CutoffDateINT 
1685	
1686-- COLocationDate 
1687INSERT INTO 
1688	#GoodServices 
1689SELECT 
1690	R.COServiceID
1691FROM 
1692	#Row AS R 
1693INNER JOIN 
1694	COLocation AS L WITH(NOLOCK)
1695		ON R.COSDetailID = L.COSDetailID 
1696INNER JOIN 
1697	COLocationDate AS D WITH(NOLOCK)
1698		ON L.COLocationID = D.COLocationID 
1699LEFT OUTER JOIN 
1700	#GoodServices AS G 
1701		ON R.COServiceID = G.COServiceID 
1702WHERE 
1703	G.COServiceID IS NULL 
1704GROUP BY 
1705	R.COServiceID 
1706HAVING 
1707	MAX(D.DateValue) >= @CutoffDateINT
1708	
1709-- LoadDate
1710
1711
1712/*NEW OR CHANGED Jeremy Dixon 11/2013*/
1713INSERT INTO #ActivityForGoodServices
1714(
1715	COServiceID,
1716	SPOrderID
1717)
1718SELECT 
1719	R.COServiceID, A.SPOrderID
1720FROM 
1721	#Row AS R
1722INNER JOIN 
1723	Activity AS A WITH(NOLOCK)
1724		ON R.COServiceID = A.COServiceID 
1725
1726/*NEW OR CHANGED Jeremy Dixon 11/2013*/
1727INSERT INTO 
1728	#GoodServices 
1729SELECT 
1730	R.COServiceID
1731FROM #ActivityForGoodServices R
1732INNER JOIN 
1733	SPOrder AS O WITH(NOLOCK)
1734		ON R.SPOrderID = O.SPOrderID 
1735INNER JOIN 
1736	[Load] AS L WITH(NOLOCK)
1737		ON O.LoadNumber = L.LoadNumber 
1738		AND O.SourceTypeID = L.SourceTypeID 
1739		AND O.LoadNumber > -1
1740INNER JOIN 
1741	LoadDate AS D WITH(NOLOCK)
1742		ON L.LoadID = D.LoadID 
1743LEFT OUTER JOIN 
1744	#GoodServices AS G 
1745		ON R.COServiceID = G.COServiceID 
1746WHERE 
1747	G.COServiceID IS NULL 
1748GROUP BY 
1749	R.COServiceID 
1750HAVING 
1751	MAX(D.DateValue) >= @CutoffDateINT 
1752
1753--INSERT INTO 
1754--	#GoodServices 
1755--SELECT 
1756--	R.COServiceID
1757--FROM 
1758--	#Row AS R
1759--INNER JOIN 
1760--	Activity AS A WITH(NOLOCK)
1761--		ON R.COServiceID = A.COServiceID 
1762--INNER JOIN 
1763--	SPOrder AS O WITH(NOLOCK)
1764--		ON A.SPOrderID = O.SPOrderID 
1765--INNER JOIN 
1766--	[Load] AS L WITH(NOLOCK)
1767--		ON O.LoadNumber = L.LoadNumber 
1768--		AND O.SourceTypeID = L.SourceTypeID 
1769--		AND O.LoadNumber > -1
1770--INNER JOIN 
1771--	LoadDate AS D WITH(NOLOCK)
1772--		ON L.LoadID = D.LoadID 
1773--LEFT OUTER JOIN 
1774--	#GoodServices AS G 
1775--		ON R.COServiceID = G.COServiceID 
1776--WHERE 
1777--	G.COServiceID IS NULL 
1778--GROUP BY 
1779--	R.COServiceID 
1780--HAVING 
1781--	MAX(D.DateValue) >= 20121015--@CutoffDateINT 
1782
1783-- SPOrderDate
1784
1785/*NEW OR CHANGED Jeremy Dixon 11/2013*/
1786INSERT INTO 
1787	#GoodServices 
1788SELECT 
1789	R.COServiceID
1790FROM #ActivityForGoodServices AS R
1791INNER JOIN 
1792	SPOrderDate AS D WITH(NOLOCK)
1793		ON R.SPOrderID = D.SPOrderID 
1794LEFT OUTER JOIN 
1795	#GoodServices AS G 
1796		ON R.COServiceID = G.COServiceID 
1797WHERE 
1798	G.COServiceID IS NULL 		
1799GROUP BY 
1800	R.COServiceID 
1801HAVING 
1802	MAX(D.DateValue) >= @CutoffDateINT 
1803
1804
1805--INSERT INTO 
1806--	#GoodServices 
1807--SELECT 
1808--	R.COServiceID
1809--FROM 
1810--	#Row AS R
1811--INNER JOIN 
1812--	Activity AS A WITH(NOLOCK)
1813--		ON R.COServiceID = A.COServiceID 
1814--INNER JOIN 
1815--	SPOrderDate AS D WITH(NOLOCK)
1816--		ON A.SPOrderID = D.SPOrderID 
1817--LEFT OUTER JOIN 
1818--	#GoodServices AS G 
1819--		ON R.COServiceID = G.COServiceID 
1820--WHERE 
1821--	G.COServiceID IS NULL 		
1822--GROUP BY 
1823--	R.COServiceID 
1824--HAVING 
1825--	MAX(D.DateValue) >= @CutoffDateINT 
1826
1827
1828-- SPODLocationDate <><><><><><><><><><>
1829
1830--INSERT INTO 
1831--	#GoodServices 
1832
1833/*NEW OR CHANGED Jeremy Dixon 11/2013*/
1834INSERT INTO #HoldForGoodServices
1835	(COServiceID , SPODetailID)
1836SELECT 
1837	R.COServiceID, P.SPODetailID
1838FROM 
1839	#Row AS R 
1840INNER JOIN 
1841	PDetail AS P WITH(NOLOCK)
1842		ON R.COSDetailID = P.COSDetailID 
1843
1844/*NEW OR CHANGED Jeremy Dixon 11/2013*/
1845INSERT INTO 
1846	#GoodServices 
1847SELECT P.COServiceID
1848FROM #HoldForGoodServices AS P
1849INNER JOIN 
1850	SPODLocation AS L WITH(NOLOCK)
1851		ON P.SPODetailID = L.SPODetailID 
1852INNER JOIN 
1853	SPODLocationDate AS D WITH(NOLOCK)
1854		ON L.SPODLocationID = D.SPODLocationID 
1855LEFT OUTER JOIN 
1856	#GoodServices AS G 
1857		ON P.COServiceID = G.COServiceID 
1858WHERE 
1859	G.COServiceID IS NULL 
1860GROUP BY 
1861	P.COServiceID 
1862HAVING 
1863	MAX(D.DateValue) >= @CutoffDateINT --20121015--
1864
1865--INSERT INTO 
1866--	#GoodServices 
1867--SELECT 
1868--	R.COServiceID
1869--FROM 
1870--	#Row AS R 
1871--INNER JOIN 
1872--	PDetail AS P WITH(NOLOCK)
1873--		ON R.COSDetailID = P.COSDetailID 
1874--INNER JOIN 
1875--	SPODLocation AS L WITH(NOLOCK)
1876--		ON P.SPODetailID = L.SPODetailID 
1877--INNER JOIN 
1878--	SPODLocationDate AS D WITH(NOLOCK)
1879--		ON L.SPODLocationID = D.SPODLocationID 
1880--LEFT OUTER JOIN 
1881--	#GoodServices AS G 
1882--		ON R.COServiceID = G.COServiceID 
1883--WHERE 
1884--	G.COServiceID IS NULL 
1885--GROUP BY 
1886--	R.COServiceID 
1887--HAVING 
1888--	MAX(D.DateValue) >= @CutoffDateINT 
1889
1890
1891
1892/*NEW OR CHANGED Jeremy Dixon 11/2013*/
1893INSERT #GS_COService
1894	(COServiceID)
1895SELECT 
1896		R.COServiceID
1897FROM #Row AS R
1898	INNER JOIN COSAlert AS CA WITH(NOLOCK)
1899			ON R.COServiceID = CA.COServiceID 
1900	INNER JOIN 
1901		Alert AS A WITH(NOLOCK)
1902			ON CA.AlertID = A.AlertID 
1903GROUP BY 
1904		R.COServiceID 
1905HAVING 
1906		MAX(A.ClosedDate) >= @CutoffDate 
1907/*NEW OR CHANGED Jeremy Dixon 11/2013*/		
1908INSERT #GS_COService
1909	(COServiceID)
1910SELECT 
1911		R.COServiceID
1912FROM #Row AS R
1913	INNER JOIN COSAlert AS CA WITH(NOLOCK)
1914			ON R.COServiceID = CA.COServiceID 
1915	INNER JOIN 
1916		Alert AS A WITH(NOLOCK)
1917			ON CA.AlertID = A.AlertID
1918GROUP BY 
1919		R.COServiceID 
1920HAVING 
1921		MAX(A.EnteredDate) >= @CutoffDate 
1922
1923-- Alerts
1924/*NEW OR CHANGED Jeremy Dixon 11/2013*/
1925INSERT INTO 
1926	#GoodServices 
1927SELECT
1928	R.COServiceID
1929FROM 
1930	#Row AS R 
1931		INNER JOIN (SELECT DISTINCT COServiceID 
1932					FROM #GS_COService) AS COSA
1933			ON COSA.COServiceID = R.COServiceID
1934	LEFT OUTER JOIN #GoodServices AS G 
1935		ON R.COServiceID = G.COServiceID 
1936WHERE G.COServiceID IS NULL 
1937
1938--INSERT INTO 
1939--	#GoodServices 
1940--SELECT
1941--	R.COServiceID
1942--FROM 
1943--	#Row AS R 
1944--LEFT OUTER JOIN 
1945--	#GoodServices AS G 
1946--		ON R.COServiceID = G.COServiceID 
1947--WHERE G.COServiceID IS NULL AND R.COServiceID IN 
1948--(
1949--	SELECT 
1950--		CA.COServiceID
1951--	FROM 
1952--		COSAlert AS CA WITH(NOLOCK)
1953--	INNER JOIN 
1954--		Alert AS A WITH(NOLOCK)
1955--			ON CA.AlertID = A.AlertID 
1956--	WHERE 
1957--		R.COServiceID = CA.COServiceID 
1958--	GROUP BY 
1959--		CA.COServiceID 
1960--	HAVING 
1961--		MAX(A.ClosedDate) >= @CutoffDate 
1962		
1963--	UNION 
1964	
1965--	SELECT 
1966--		CA.COServiceID
1967--	FROM 
1968--		COSAlert AS CA WITH(NOLOCK)
1969--	INNER JOIN 
1970--		Alert AS A WITH(NOLOCK)
1971--			ON CA.AlertID = A.AlertID 
1972--	WHERE 
1973--		R.COServiceID = CA.COServiceID 
1974--	GROUP BY 
1975--		CA.COServiceID 
1976--	HAVING 
1977--		MAX(A.EnteredDate) >= @CutoffDate 
1978--)
1979
1980-- OrderInvoice
1981INSERT INTO 
1982	#GoodServices 
1983SELECT
1984	R.COServiceID
1985FROM 
1986	#Row AS R
1987INNER JOIN 
1988	COServiceInvoice AS CI WITH(NOLOCK)
1989		ON R.COServiceID = CI.COServiceID 
1990INNER JOIN 
1991	OrderInvoice AS OI WITH(NOLOCK)
1992		ON CI.OrderInvoiceID = OI.OrderInvoiceID 
1993LEFT OUTER JOIN 
1994	#GoodServices AS G 
1995		ON R.COServiceID = G.COServiceID 
1996WHERE 
1997	G.COServiceID IS NULL 
1998GROUP BY 
1999	R.COServiceID 
2000HAVING 
2001	MAX(OI.InvoiceDate) >= @CutoffDate 
2002
2003-- COSGroup
2004INSERT INTO 
2005	#GoodServices 
2006SELECT
2007	R.COServiceID
2008FROM 
2009	#Row AS R
2010LEFT OUTER JOIN 
2011	#GoodServices AS G 
2012		ON R.COServiceID = G.COServiceID 
2013WHERE G.COServiceID IS NULL AND R.COSGroupID IN 
2014(
2015	SELECT 
2016		G.COSGroupID 
2017	FROM 
2018		COSGroup AS G WITH(NOLOCK)
2019	WHERE 
2020		R.COSGroupID = G.COSGroupID 
2021	GROUP BY 
2022		G.COSGroupID  
2023	HAVING 
2024		MAX(G.EstimatedDeliveryDate) > @CutoffDate 
2025		
2026	UNION 
2027	
2028	SELECT 
2029		G.COSGroupID 
2030	FROM 
2031		COSGroup AS G WITH(NOLOCK)
2032	WHERE 
2033		R.COSGroupID = G.COSGroupID 
2034	GROUP BY 
2035		G.COSGroupID 
2036	HAVING 
2037		MAX(G.EstimatedPickupDate) > @CutoffDate 
2038)
2039
2040/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2041DELETE #GS_COService
2042/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2043INSERT #GS_COService
2044	(COServiceID)
2045SELECT 
2046		R.COServiceID
2047FROM #Row AS R
2048	INNER JOIN Activity AS A WITH(NOLOCK)
2049		ON R.COServiceID = A.COServiceID 
2050	INNER JOIN 
2051		DriverLog AS DL WITH(NOLOCK)
2052			ON A.SPOrderID = DL.SPOrderID 
2053GROUP BY 
2054		R.COServiceID 
2055HAVING 
2056		MAX(DL.EventDate) > @CutoffDate 
2057/*NEW OR CHANGED Jeremy Dixon 11/2013*/		
2058INSERT #GS_COService
2059	(COServiceID)
2060	
2061SELECT 
2062		R.COServiceID
2063FROM #Row AS R
2064	INNER JOIN
2065		Activity AS A WITH(NOLOCK)
2066		ON R.COServiceID = A.COServiceID 
2067	INNER JOIN 
2068		DriverLog AS DL WITH(NOLOCK)
2069			ON A.SPOrderID = DL.SPOrderID 
2070GROUP BY 
2071		R.COServiceID 
2072HAVING 
2073		MAX(DL.RailETA) > @CutoffDate 
2074-- DriverLog 
2075/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2076INSERT INTO 
2077	#GoodServices 
2078SELECT
2079	R.COServiceID
2080FROM 
2081	#Row AS R
2082INNER JOIN (SELECT DISTINCT COServiceID FROM #GS_COService) AS GS
2083	ON GS.COServiceID = R.COServiceID
2084LEFT OUTER JOIN 
2085	#GoodServices AS G 
2086		ON R.COServiceID = G.COServiceID 
2087WHERE G.COServiceID IS NULL 
2088
2089--INSERT INTO 
2090--	#GoodServices 
2091--SELECT
2092--	R.COServiceID
2093--FROM 
2094--	#Row AS R
2095--LEFT OUTER JOIN 
2096--	#GoodServices AS G 
2097--		ON R.COServiceID = G.COServiceID 
2098--WHERE G.COServiceID IS NULL AND R.COServiceID IN 
2099--(
2100--	SELECT 
2101--		A.COServiceID
2102--	FROM 
2103--		Activity AS A WITH(NOLOCK)
2104--	INNER JOIN 
2105--		DriverLog AS DL WITH(NOLOCK)
2106--			ON A.SPOrderID = DL.SPOrderID 
2107--	WHERE 
2108--		R.COServiceID = A.COServiceID 
2109--	GROUP BY 
2110--		A.COServiceID 
2111--	HAVING 
2112--		MAX(DL.EventDate) > @CutoffDate 
2113		
2114--	UNION 
2115	
2116--	SELECT 
2117--		A.COServiceID
2118--	FROM 
2119--		Activity AS A WITH(NOLOCK)
2120--	INNER JOIN 
2121--		DriverLog AS DL WITH(NOLOCK)
2122--			ON A.SPOrderID = DL.SPOrderID 
2123--	WHERE 
2124--		R.COServiceID = A.COServiceID 
2125--	GROUP BY 
2126--		A.COServiceID 
2127--	HAVING 
2128--		MAX(DL.RailETA) > @CutoffDate 
2129--)
2130-- Customs
2131-- TODO Fix -- probably not performant
2132INSERT INTO 
2133	#GoodServices 
2134SELECT
2135	R.COServiceID
2136FROM 
2137	#Row AS R 
2138LEFT OUTER JOIN 
2139	#GoodServices AS G 
2140		ON R.COServiceID = G.COServiceID 
2141WHERE G.COServiceID IS NULL AND R.COServiceID IN 
2142(
2143	SELECT 
2144		C.COServiceID
2145	FROM 
2146		Customs AS C WITH(NOLOCK)
2147	WHERE 
2148		R.COServiceID = C.COServiceID 
2149	GROUP BY 
2150		C.COServiceID 
2151	HAVING 
2152		MAX(C.CustomsHoldDate) > @CutoffDate 
2153		
2154	UNION 
2155	
2156	SELECT 
2157		C.COServiceID
2158	FROM 
2159		Customs AS C WITH(NOLOCK)
2160	WHERE 
2161		R.COServiceID = C.COServiceID 
2162	GROUP BY 
2163		C.COServiceID 
2164	HAVING 
2165		MAX(C.DispatchedDate) > @CutoffDate 
2166		
2167	UNION 
2168	
2169	SELECT 
2170		C.COServiceID
2171	FROM 
2172		Customs AS C WITH(NOLOCK)
2173	WHERE 
2174		R.COServiceID = C.COServiceID 
2175	GROUP BY 
2176		C.COServiceID 
2177	HAVING 
2178		MAX(C.EntryDate) > @CutoffDate 
2179		
2180	UNION 
2181	
2182	SELECT 
2183		C.COServiceID
2184	FROM 
2185		Customs AS C WITH(NOLOCK)
2186	WHERE 
2187		R.COServiceID = C.COServiceID 
2188	GROUP BY 
2189		C.COServiceID 
2190	HAVING 
2191		MAX(C.ExamRequiredDate) > @CutoffDate 
2192		
2193	UNION 
2194	
2195	SELECT 
2196		C.COServiceID
2197	FROM 
2198		Customs AS C WITH(NOLOCK)
2199	WHERE 
2200		R.COServiceID = C.COServiceID 
2201	GROUP BY 
2202		C.COServiceID 
2203	HAVING 
2204		MAX(C.ITDate) > @CutoffDate 
2205		
2206	UNION 
2207	
2208	SELECT 
2209		C.COServiceID
2210	FROM 
2211		Customs AS C WITH(NOLOCK)
2212	WHERE 
2213		R.COServiceID = C.COServiceID 
2214	GROUP BY 
2215		C.COServiceID 
2216	HAVING 
2217		MAX(C.OGAHoldDate) > @CutoffDate 
2218		
2219	UNION 
2220	
2221	SELECT 
2222		C.COServiceID
2223	FROM 
2224		Customs AS C WITH(NOLOCK)
2225	WHERE 
2226		R.COServiceID = C.COServiceID 
2227	GROUP BY 
2228		C.COServiceID 
2229	HAVING 
2230		MAX(C.OGAReleaseDate) > @CutoffDate 
2231		
2232	UNION 
2233	
2234	SELECT 
2235		C.COServiceID
2236	FROM 
2237		Customs AS C WITH(NOLOCK)
2238	WHERE 
2239		R.COServiceID = C.COServiceID 
2240	GROUP BY 
2241		C.COServiceID 
2242	HAVING 
2243		MAX(C.ReleaseDate) > @CutoffDate 
2244		
2245	UNION 
2246	
2247	SELECT 
2248		C.COServiceID
2249	FROM 
2250		Customs AS C WITH(NOLOCK)
2251	WHERE 
2252		R.COServiceID = C.COServiceID 
2253	GROUP BY 
2254		C.COServiceID 
2255	HAVING 
2256		MAX(C.SSLReleaseDate) > @CutoffDate 
2257		
2258	UNION 
2259	
2260	SELECT 
2261		C.COServiceID
2262	FROM 
2263		Customs AS C WITH(NOLOCK)
2264	WHERE 
2265		R.COServiceID = C.COServiceID 
2266	GROUP BY 
2267		C.COServiceID 
2268	HAVING 
2269		MAX(C.TurnedOverDate) > @CutoffDate 
2270)
2271
2272-- RLTicketDate
2273INSERT INTO 
2274	#GoodServices 
2275SELECT 
2276	R.COServiceID 
2277FROM 
2278	#Row AS R 
2279INNER JOIN 
2280	RLTicket AS T WITH(NOLOCK)
2281		ON R.COServiceID = T.COServiceID 
2282INNER JOIN 
2283	RLTicketDate AS D WITH(NOLOCK)
2284		ON T.RLTicketID = D.RLTicketID 
2285LEFT OUTER JOIN 
2286	#GoodServices AS G 
2287		ON R.COServiceID = G.COServiceID 
2288WHERE 
2289	G.COServiceID IS NULL 
2290GROUP BY 
2291	R.COServiceID 
2292HAVING 
2293	MAX(D.DateValue) >= @CutoffDateINT 
2294
2295
2296
2297/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2298DELETE #GS_COService
2299/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2300INSERT #GS_COService
2301	(COServiceID)
2302SELECT DISTINCT
2303	R.COServiceID
2304FROM 
2305	#Row AS R 
2306LEFT OUTER JOIN 
2307	#GoodServices AS S 
2308		ON R.COServiceID = S.COServiceID 
2309WHERE 
2310	S.COServiceID IS NULL 
2311/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2312DELETE R
2313FROM #Row AS R	
2314	INNER JOIN #GS_COService AS GS
2315		ON GS.COServiceID = R.COServiceID
2316
2317--DELETE 
2318--	R
2319--FROM 
2320--	#Row AS R 
2321--LEFT OUTER JOIN 
2322--	#GoodServices AS S 
2323--		ON R.COServiceID = S.COServiceID 
2324--WHERE 
2325--	S.COServiceID IS NULL 
2326
2327IF @CustRefNums IS NOT NULL AND @PartialRef = 1
2328BEGIN	 
2329	DELETE FROM #Row WHERE COServiceID NOT IN
2330	(
2331		SELECT 
2332			CS.COServiceID
2333		FROM
2334			#Row AS CS 
2335		INNER JOIN dbo.COServiceReference AS COSR WITH (NOLOCK)
2336			ON CS.COServiceID = COSR.COServiceID
2337		INNER JOIN NTSR.dbo.DimReference AS REF WITH (NOLOCK)
2338			ON COSR.KeyReferenceID = REF.KeyReferenceID 				
2339		WHERE
2340			REF.ReferenceNumber like '%' + @CustRefNums + '%'
2341		
2342		UNION ALL
2343		
2344		SELECT 
2345			CS.COServiceID
2346		FROM
2347			#Row AS CS 
2348		INNER JOIN dbo.COSDetail AS COSD WITH(NOLOCK)
2349			ON CS.COServiceID = COSD.COServiceID
2350		INNER JOIN dbo.COSDetailReference AS COSDR WITH (NOLOCK)
2351			ON COSD.COSDetailID = COSDR.COSDetailID
2352		INNER JOIN NTSR.dbo.DimReference AS REF WITH (NOLOCK)
2353			ON COSDR.KeyReferenceID = REF.KeyReferenceID
2354		WHERE
2355			REF.ReferenceNumber like '%' + @CustRefNums + '%'
2356	)
2357	
2358	DELETE FROM #Row WHERE COServiceID IN
2359	(
2360		SELECT 
2361			CS.COServiceID
2362		FROM
2363			#Row AS CS 
2364		INNER JOIN dbo.COServiceReference AS COSR WITH (NOLOCK)
2365			ON CS.COServiceID = COSR.COServiceID
2366		INNER JOIN NTSR.dbo.DimReference AS REF WITH (NOLOCK)
2367			ON COSR.KeyReferenceID = REF.KeyReferenceID 				
2368		WHERE
2369			COSR.[ReferenceType] = 'PRONUMBER' AND NOT EXISTS 
2370			( -- 09/03/09 GANASAD PRB40913
2371				SELECT 
2372					1
2373				FROM
2374					COServiceReference AS COSR2 WITH(NOLOCK)
2375				WHERE 
2376					COSR2.COServiceID = CS.COServiceID AND 
2377					COSR2.ReferenceType <> 'PRONUMBER'
2378			)
2379	)
2380END
2381
2382IF @OverDimensional = 1
2383BEGIN
2384	DELETE FROM #Row WHERE COServiceID NOT IN
2385	(SELECT R.COServiceID
2386	FROM #Row AS R 
2387		INNER JOIN dbo.COItem AS I WITH (NOLOCK)
2388			ON R.COSDetailID = I.COSDetailID
2389		INNER JOIN dbo.COItemFlatbed AS F WITH (NOLOCK)
2390			ON F.COItemID = I.COItemID
2391	WHERE F.ODFlg = 1)
2392END
2393ELSE IF @OverDimensional = 0
2394BEGIN
2395	DELETE FROM #Row WHERE COServiceID IN
2396	(SELECT R.COServiceID
2397	FROM #Row AS R 
2398		INNER JOIN dbo.COItem AS I WITH (NOLOCK)
2399			ON R.COSDetailID = I.COSDetailID
2400		INNER JOIN dbo.COItemFlatbed AS F WITH (NOLOCK)
2401			ON F.COItemID = I.COItemID
2402	WHERE F.ODFlg = 1)
2403END
2404
2405--We need to go back and make sure that we get Customs section of loads when search is done using dates thta go after location dates. 
2406--Since Customs do not have locations, they have no way of getting in
2407	INSERT INTO #Row 
2408	(	
2409		RecordID		
2410		,COServiceID
2411		,COSDetailID
2412		,LoadNumber	
2413		,CustomerOrderNumber	-- 09/02/2008 GANASAD Updated from CHRSubOrderNumber
2414		,PlannedLoadNumber
2415		,COSGroupID
2416		,CHRNumber
2417		,ShipmentType
2418		,LevelNumber
2419		,ShipmentMode
2420		,LogisticFlg					
2421		,SourceTypeID			-- 09/02/2008 GANASAD Added
2422		,HotFlg
2423	) 
2424	SELECT DISTINCT
2425		0 AS RecordID
2426		,CS.COServiceID AS COServiceID
2427		,COSD.COSDetailID AS COSDetailID 
2428		,CS.FirstLoadNumber AS LoadNumber
2429		,CS.CustomerOrderNumber AS CustomerOrderNumber 
2430		,CS.FirstPlannedLoadNumber AS PlannedLoadNumber
2431		,-1 AS COSGroupID
2432		,CS.FirstLoadNumber AS CHRNumber
2433		,'Customs Load' AS ShipmentType
2434		,1 AS LevelNumber
2435		,CS.Mode AS ShipmentMode
2436		,ISNULL(CS.LogisticFlg, 0) AS LogisticFlg
2437		,CS.SourceTypeID AS SourceTypeID
2438		,CS.HotFlg
2439	FROM #Row	AS R 
2440		INNER JOIN dbo.COService AS CS WITH(NOLOCK)
2441			ON R.LoadNumber = CS.FirstLoadNumber
2442			AND R.SourceTypeID = CS.SourceTypeID 
2443			AND CS.CustomsFlg =1
2444		-- 04/04/08 GANASAD Added COSDetail so we can get the 
2445		-- COSDetailID
2446		INNER JOIN COSDetail AS COSD WITH(NOLOCK)
2447			ON CS.COServiceID = COSD.COServiceID
2448    WHERE NOT EXISTS(SELECT 1 FROM #Row	AS R1 WHERE R1.COServiceID=CS.COServiceID)
2449    
2450--We need to go back and make sure that we get INTL loads for Customs sections when search is done using dates that go after customs dates. 
2451
2452	INSERT INTO #Row 
2453	(	
2454		RecordID		
2455		,COServiceID
2456		,COSDetailID
2457		,LoadNumber	
2458		,CustomerOrderNumber	-- 09/02/2008 GANASAD Updated From CHRSubOrderNumber
2459		,PlannedLoadNumber
2460		,COSGroupID
2461		,CHRNumber
2462		,ShipmentType
2463		,LevelNumber
2464		,ShipmentMode
2465		,LogisticFlg	
2466		,CustomsCOServiceID	
2467		,SourceTypeID			-- 09/02/2008 GANASAD Added		
2468		,HotFlg	
2469	) 
2470	SELECT DISTINCT
2471		0 AS RecordID
2472		,CS.COServiceID AS COServiceID
2473		,COSD.COSDetailID AS COSDetailID 
2474		,CS.FirstLoadNumber AS LoadNumber
2475		,CS.CustomerOrderNumber AS CustomerOrderNumber	-- 09/02/2008 GANASAD Updated from CHRSubOrder
2476		,CS.FirstPlannedLoadNumber AS PlannedLoadNumber
2477		,CASE WHEN CS.CustomsFlg =1 THEN -1 ELSE ISNULL(CS.COSGroupID,-1) END AS COSGroupID
2478		,CASE 
2479			WHEN CS.CustomerOrderNumber > 0 AND CS.FirstPlannedLoadNumber = -1 AND CS.FirstLoadNumber = -1 THEN CS.CustomerOrderNumber --Unplanned Suborder 
2480			WHEN CS.CustomerOrderNumber > 0 AND CS.FirstLoadNumber > 0 AND (CS.PooledFlg =1 OR CS.CrossDockFlg=1) AND @RoutingFeature=1 THEN CS.FirstLoadNumber --Pooled|CrossDock SubOrder with Routing Feature
2481			WHEN CS.CustomerOrderNumber > 0 AND CS.FirstPlannedLoadNumber > 0 AND (CS.PooledFlg =1 OR CS.CrossDockFlg=1) AND @RoutingFeature=0 THEN CS.CustomerOrderNumber --Pooled|CrossDock SubOrder without Routing Feature
2482			WHEN CS.CustomerOrderNumber > 0 AND CS.FirstLoadNumber > 0 AND CS.PooledFlg=0 AND CS.CrossDockFlg=0 AND @SummarizeShipment=0 THEN CS.CustomerOrderNumber --SubOrder on the load and one per OD pair option is selected
2483			WHEN CS.CustomerOrderNumber > 0 AND CS.FirstLoadNumber > 0 AND CS.PooledFlg=0 AND CS.CrossDockFlg=0 AND @SummarizeShipment=1 THEN CS.FirstLoadNumber--SubOrder on the load and summary option is selected
2484			WHEN CS.CustomerOrderNumber =-1 AND CS.FirstLoadNumber > 0 THEN CS.FirstLoadNumber
2485			WHEN CS.CustomerOrderNumber > 0 AND CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber =-1 THEN CS.CustomerOrderNumber --SubOrder is planned but load is not in Express
2486		END CHRNumber
2487		,CASE
2488			WHEN CS.SourceTypeID = 7 AND CS.FirstPlannedLoadNumber < 0 AND CS.FirstLoadNumber < 0 THEN 'Multi Intl Load'
2489			WHEN CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND CS.FirstPlannedLoadNumber = -1 AND CS.FirstLoadNumber = -1  THEN 'ORDER'
2490			WHEN CS.PooledFlg =1 OR CS.CrossDockFlg=1 THEN 'Pooled'
2491			WHEN CS.PooledFlg =0 AND CS.CrossDockFlg=0  AND CS.FirstLoadNumber > 0 AND ISNULL(L.MaxStopNbr,0) <=1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 'Single OD LOAD'
2492			WHEN CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber = -1 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) <=1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 'Single OD Plan Load'
2493			WHEN CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber = -1 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) > 1 THEN 'Multi OD Plan LOAD'
2494			WHEN CS.CustomsFlg =1 THEN 'Customs Load'
2495			WHEN ISNULL(L.InternationalFlg,0) = 1 and NULLIF(CS.COSGroupID,0) IS NULL THEN 'Single Intl Load'
2496			WHEN CS.FirstLoadNumber >0 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) > 1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 'Multi OD Load'
2497			WHEN ISNULL(L.InternationalFlg,0) = 1 and NULLIF(CS.COSGroupID,0) IS NOT NULL AND CS.CustomsFlg =0 THEN 'Multi Intl Load'
2498		END AS ShipmentType
2499		,CASE
2500			WHEN CS.SourceTypeID = 7 AND CS.FirstPlannedLoadNumber < 0 AND CS.FirstLoadNumber < 0 THEN 2 -- 'Multi Intl Load'
2501			WHEN CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND CS.FirstPlannedLoadNumber = -1 AND CS.FirstLoadNumber = -1    THEN 1--Order
2502			WHEN CS.PooledFlg =1 OR CS.CrossDockFlg=1 THEN 1--Pooled Load
2503			WHEN CS.PooledFlg =0 AND CS.CrossDockFlg=0  AND CS.FirstLoadNumber > 0 AND ISNULL(L.MaxStopNbr,0) <=1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 1--Single OD Load
2504			WHEN CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber = -1 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) <=1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 1 --Single OD Plan Load
2505			WHEN CS.FirstPlannedLoadNumber >0 AND CS.FirstLoadNumber = -1 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) > 1 THEN 2--Multi OD Plan Load
2506			WHEN CS.CustomsFlg =1 THEN 1 --Customs
2507			WHEN ISNULL(L.InternationalFlg,0) = 1 and NULLIF(CS.COSGroupID,0) IS NULL THEN 1--Single Intl Load
2508			WHEN CS.FirstLoadNumber >0 AND CS.PooledFlg =0 AND CS.CrossDockFlg=0 AND ISNULL(L.MaxStopNbr,0) > 1 AND ISNULL(L.InternationalFlg,0) = 0 THEN 2--Multi OD Load
2509			WHEN ISNULL(L.InternationalFlg,0) = 1 and NULLIF(CS.COSGroupID,0) IS NOT NULL AND CS.CustomsFlg =0 THEN 2--Multi Intl Load
2510		END AS LevelNumber
2511		,CS.Mode AS ShipmentMode
2512		,ISNULL(CS.LogisticFlg, 0) AS LogisticFlg
2513		,R.COServiceID AS CustomsCOServiceID
2514		,CS.SourceTypeID AS SourceTypeID -- 09/02/2008 Added 
2515		,CS.HotFlg
2516	FROM #Row	AS R
2517		INNER JOIN dbo.COService AS CS WITH(NOLOCK)
2518			ON R.LoadNumber = CS.FirstLoadNumber
2519			AND R.SourceTypeID = CS.SourceTypeID 
2520		INNER JOIN COSDetail AS COSD WITH(NOLOCK)
2521			ON CS.COServiceID = COSD.COServiceID
2522		LEFT OUTER JOIN dbo.[Load]								AS			L		WITH(NOLOCK)
2523			ON CS.FirstLoadNumber = L.LoadNumber
2524			AND CS.FirstPlannedLoadNumber = L.PlannedLoadNumber
2525			AND CS.SourceTypeID = L.SourceTypeID 
2526		INNER JOIN dbo.COSDetailAccess							AS		   CSDA	WITH(NOLOCK)
2527			ON COSD.COSDetailID = CSDA.COSDetailID 
2528		INNER JOIN #UserCCodes									AS			CC		
2529			ON CSDA.CustomerCode = CC.CompanyCode 
2530			AND ISNULL(CSDA.WarehouseCode,'') = ISNULL(CC.WarehouseCode,'')  
2531    WHERE R.ShipmentType = 'Customs Load' AND NOT EXISTS(SELECT 1 FROM #Row	AS R1 WHERE R1.COServiceID=CS.COServiceID)
2532    
2533	/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2534	UPDATE R SET 
2535		R.ShipmentType='Multi OD Load',
2536		R.LevelNumber=2
2537	FROM #Row AS R 
2538		INNER JOIN (SELECT LoadNumber, SourceTypeID
2539					FROM #Row AS R2 
2540					WHERE R2.ShipmentType='Single OD Load' 
2541					GROUP BY LoadNumber, SourceTypeID 
2542					HAVING COUNT(1) > 1
2543					) AS R2
2544			ON R.LoadNumber = R2.LoadNumber 
2545				AND R.SourceTypeID = R2.SourceTypeID
2546					
2547
2548	-- Steve to Scott - do not delete this 
2549	--UPDATE R SET 
2550	--	ShipmentType='Multi OD Load',
2551	--	LevelNumber=2
2552	--FROM #Row AS R 
2553	--WHERE EXISTS 
2554	--(
2555	--	SELECT 1 FROM #Row AS R2 
2556	--	WHERE R.LoadNumber = R2.LoadNumber AND R.SourceTypeID = R2.SourceTypeID AND R2.ShipmentType='Single OD Load' 
2557	--	GROUP BY R2.LoadNumber, R2.SourceTypeID 
2558	--	HAVING COUNT(1) > 1
2559	--)
2560
2561
2562/********************************************************************************************************/
2563	/******************** LOAD #ROW table with related data in case if user selected Summary OD Pair option***/
2564	/********************************************************************************************************/
2565	IF @SummarizeShipment = 0
2566	BEGIN
2567		UPDATE #Row SET LevelNumber = 1, ShipmentType='Single OD Load' WHERE LevelNumber = 2 AND ShipmentType='Multi OD Load'
2568		UPDATE #Row SET LevelNumber = 1, ShipmentType='Single OD Plan Load' WHERE LevelNumber = 2 AND ShipmentType='Multi OD Plan Load'
2569	END
2570	ELSE
2571	BEGIN
2572		INSERT INTO #Row 
2573		(	
2574			RecordID
2575			,COServiceID
2576			,COSDetailID
2577			,LoadNumber	
2578			,CustomerOrderNumber
2579			,PlannedLoadNumber
2580			,COSGroupID
2581			,CHRNumber
2582			,ShipmentType
2583			,LevelNumber
2584			,ShipmentMode
2585			,LogisticFlg
2586			,SourceTypeID
2587			,COrderID
2588			,HotFlg
2589		)
2590		SELECT DISTINCT
2591			0 AS RecordID
2592			,CS.COServiceID AS COServiceID
2593			,CSD.COSDetailID AS COSDetailID
2594			,CS.FirstLoadNumber AS LoadNumber
2595			,CS.CustomerOrderNumber AS CustomerOrderNumber
2596			,CS.FirstPlannedLoadNumber AS PlannedLadNumber
2597			,CASE WHEN CS.CustomsFlg =1 THEN -1 ELSE ISNULL(CS.COSGroupID,-1) END AS COSGroupID
2598			,CS.FirstLoadNumber AS CHRNumber
2599			,'Multi OD Load' AS ShipmentType
2600			,2 AS LevelNumber
2601			,CS.Mode AS ShipmentMode
2602			,ISNULL(CS.LogisticFlg, 0) AS LogisticFlg
2603			,CS.SourceTypeID
2604			,CS.COrderID
2605			,CS.HotFlg
2606		FROM #Row AS R 
2607			INNER JOIN dbo.COService AS CS WITH(NOLOCK)	
2608				ON R.LoadNumber = CS.FirstLoadNumber
2609				AND R.SourceTypeID = CS.SourceTypeID 
2610			INNER JOIN dbo.COSDetail AS CSD WITH(NOLOCK)
2611				ON CS.COServiceID = CSD.COServiceID
2612			INNER JOIN dbo.COSDetailAccess AS CSDA WITH(NOLOCK)
2613				ON CSD.COSDetailID = CSDA.COSDetailID
2614			INNER JOIN #UserCCodes AS CC 
2615				ON CSDA.CustomerCode = CC.CompanyCode 
2616				AND ISNULL(CSDA.WarehouseCode,'''') = ISNULL(CC.WarehouseCode,'''') 
2617		WHERE R.ShipmentType = 'Multi OD Load' 
2618			AND CS.PooledFlg=0 
2619			AND CS.CrossDockFlg=0 
2620			AND CS.FirstLoadNumber > 0 
2621			AND NOT EXISTS 
2622			(
2623				SELECT 1 
2624				FROM #Row AS l 
2625				WHERE l.COServiceID=CS.COServiceID
2626			)
2627			
2628		UNION
2629		
2630		SELECT DISTINCT
2631			0 AS RecordID
2632			,CS.COServiceID AS COServiceID
2633			,CSD.COSDetailID AS COSDetailID
2634			,CS.FirstLoadNumber AS LoadNumber
2635			,CS.CustomerOrderNumber AS CustomerOrderNumber
2636			,CS.FirstPlannedLoadNumber AS PlannedLadNumber
2637			,CASE WHEN CS.CustomsFlg =1 THEN -1 ELSE ISNULL(CS.COSGroupID,-1) END AS COSGroupID
2638			,CS.CustomerOrderNumber AS CHRNumber
2639			,'Multi OD Plan Load' AS ShipmentType
2640			,2 AS LevelNumber
2641			,CS.Mode AS ShipmentMode
2642			,ISNULL(CS.LogisticFlg, 0) AS LogisticFlg
2643			,CS.SourceTypeID
2644			,CS.COrderID
2645			,CS.HotFlg
2646		FROM #Row AS R 
2647			INNER JOIN dbo.COService AS CS WITH(NOLOCK)	
2648				ON R.PlannedLoadNumber = CS.FirstPlannedLoadNumber
2649			INNER JOIN dbo.COSDetail AS CSD WITH(NOLOCK)
2650				ON CS.COServiceID = CSD.COServiceID
2651			INNER JOIN dbo.COSDetailAccess AS CSDA WITH(NOLOCK)
2652				ON CSD.COSDetailID = CSDA.COSDetailID
2653			INNER JOIN #UserCCodes AS CC 
2654				ON CSDA.CustomerCode = CC.CompanyCode 
2655				AND ISNULL(CSDA.WarehouseCode,'''') = ISNULL(CC.WarehouseCode,'''') 
2656		WHERE 
2657			R.ShipmentType = 'Multi OD Plan Load' 
2658			AND CS.PooledFlg=0 
2659			AND CS.CrossDockFlg=0 
2660			AND CS.FirstLoadNumber = -1 
2661			AND NOT EXISTS 
2662			(
2663				SELECT 1 
2664				FROM #Row AS l 
2665				WHERE l.COServiceID=CS.COServiceID
2666			)
2667
2668
2669		/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2670
2671		INSERT INTO #SingleODLoad
2672		(
2673			 LoadNumber	
2674			,SourceTypeID			
2675		)
2676		SELECT LoadNumber, 
2677			SourceTypeID 
2678		FROM #Row 
2679		WHERE ShipmentType = 'Multi OD Load' 
2680			AND LoadNumber > 1 
2681		GROUP BY LoadNumber, SourceTypeID
2682		HAVING COUNT(1) = 1
2683
2684		/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2685		UPDATE R
2686		SET R.ShipmentType='Single OD Load', 
2687			R.LevelNumber=1
2688		FROM #Row AS R
2689			INNER JOIN #SingleODLoad AS R2
2690				ON R2.LoadNumber = R.LoadNumber
2691					AND R2.SourceTypeID = R.SourceTypeID
2692		--UPDATE row
2693		--	SET ShipmentType='Single OD Load', LevelNumber=1
2694		--FROM #Row AS row
2695		--WHERE 
2696	 --        EXISTS (SELECT 1 FROM #Row AS row2
2697		--				WHERE row.LoadNumber = row2.LoadNumber AND 
2698		--					row.SourceTypeID = row2.SourceTypeID AND 
2699		--					row2.ShipmentType = 'Multi OD Load' AND 
2700		--					row2.LoadNumber > 1 
2701		--				GROUP BY row2.LoadNumber, row2.SourceTypeID HAVING COUNT(1) = 1)
2702
2703		/*NEW OR CHANGED Jeremy Dixon 11/2013*/
2704		UPDATE R
2705		SET R.ShipmentType='Single OD Plan Load', 
2706			R.LevelNumber=1
2707		FROM #Row AS R
2708			INNER JOIN (
2709						SELECT PlannedLoadNumber 
2710						FROM #Row 
2711						WHERE ShipmentType = 'Multi OD Plan Load' 
2712							AND LoadNumber = -1 
2713							AND PlannedLoadNumber > 1 
2714						GROUP BY PlannedLoadNumber
2715						HAVING COUNT(1) = 1
2716						) AS R2
2717				ON R2.PlannedLoadNumber = R.PlannedLoadNumber
2718	
2719
2720		--UPDATE row
2721		--	SET ShipmentType='Single OD Plan Load', LevelNumber=1
2722		--FROM #Row AS row
2723		--WHERE 
2724	 --        EXISTS (SELECT 1 FROM #Row AS row2 
2725		--				WHERE row.PlannedLoadNumber = row2.PlannedLoadNumber AND 
2726		--					row2.ShipmentType = 'Multi OD Plan Load' AND 
2727		--					row2.LoadNumber = -1 AND
2728		--					row2.PlannedLoadNumber > 1 
2729		--				GROUP BY row2.PlannedLoadNumber HAVING COUNT(1) = 1)
2730	END
2731
2732	/************************************************************************************************************/
2733	/******************** LOAD #ROW table with related data in case if user selected Summary Container option ***/
2734	/************************************************************************************************************/
2735	IF @SummarizeOcean = 0
2736	BEGIN
2737		UPDATE #Row SET LevelNumber = 1, ShipmentType='Single Intl Load' WHERE LevelNumber = 2 and ShipmentType='Multi Intl Load'
2738	END
2739	ELSE
2740	BEGIN
2741		INSERT INTO #Row 
2742		(	
2743			RecordID		
2744			--,OriginStopNumber	
2745			--,DestStopNumber
2746			,COServiceID
2747			,COSDetailID
2748			,LoadNumber	
2749			,CustomerOrderNumber		-- 09/02/2008 GANASAD Updated from CHRSubOrderNumber
2750			,PlannedLoadNumber
2751			,COSGroupID
2752			,CHRNumber
2753			,ShipmentType
2754			,LevelNumber
2755			,ShipmentMode
2756			,LogisticFlg
2757			,SourceTypeID				-- 09/02/2008 GANASAD Added
2758			,HotFlg
2759			,IntlCHRRole
2760		)
2761		
2762		SELECT DISTINCT
2763			0 AS RecordID
2764			--,CLP.StopNumber AS OriginStopNumber
2765			--,CLD.StopNumber AS DestStopNumber
2766			,CS.COServiceID AS COServiceID
2767			,CSD.COSDetailID AS COSDetailID
2768			,CS.FirstLoadNumber AS LoadNumber
2769			,CS.CustomerOrderNumber AS CustomerOrderNumber
2770			,CS.FirstPlannedLoadNumber AS PlannedLoadNumber
2771			,CASE WHEN CS.CustomsFlg =1 THEN -1 ELSE ISNULL(CS.COSGroupID,-1) END AS COSGroupID
2772			,CS.FirstLoadNumber AS CHRNumber
2773			,'Multi Intl Load' AS ShipmentType
2774			,2 AS LevelNumber
2775			,CS.Mode AS ShipmentMode
2776			,ISNULL(CS.LogisticFlg, 0) AS LogisticFlg
2777			,CS.SourceTypeID AS SourceTypeID 
2778			,CS.HotFlg
2779			,CS.IntlCHRRole
2780		FROM #Row AS R
2781			INNER JOIN dbo.COService AS CS WITH(NOLOCK)	
2782				ON R.COSGroupID = CS.COSGroupID
2783			INNER JOIN dbo.COSDetail AS CSD WITH(NOLOCK)
2784				ON CS.COServiceID = CSD.COServiceID
2785			INNER JOIN dbo.COSDetailAccess AS CSDA WITH(NOLOCK)
2786				ON CSD.COSDetailID = CSDA.COSDetailID
2787			INNER JOIN #UserCCodes AS CC
2788				ON CSDA.CustomerCode = CC.CompanyCode 
2789				AND ISNULL(CSDA.WarehouseCode,'''') = ISNULL(CC.WarehouseCode,'''') 
2790		WHERE 
2791			R.ShipmentType = 'Multi Intl Load' AND CS.CustomsFlg=0
2792			AND NOT EXISTS 
2793			(
2794				SELECT 1 
2795				FROM #Row AS l 
2796				WHERE l.COServiceID=CS.COServiceID
2797			)
2798
2799		UPDATE row
2800		SET ShipmentType='Single Intl Load', LevelNumber=1
2801		FROM #Row AS row
2802		WHERE 
2803	         EXISTS (SELECT 1 FROM #Row AS row2 
2804						WHERE row.COSGroupID = row2.COSGroupID AND 
2805							row.SourceTypeID = row2.SourceTypeID AND 
2806							row2.ShipmentType = 'Multi Intl Load' AND 
2807							row2.LoadNumber > 1 AND
2808							row2.COSGroupID > 1 AND
2809							row.ShipmentType = 'Multi Intl Load'
2810						GROUP BY row2.COSGroupID HAVING COUNT(1) = 1)
2811	END
2812
2813	/********************************************************************************************************/
2814	/******************** Update ROW table with Origin and Destination Numbers**********************************/
2815	/********************************************************************************************************/
2816	INSERT INTO #ServiceDetailID_Capture(COSDetailID, SPODetailID)  
2817	SELECT R.COSDetailID, P.SPODetailID  
2818	FROM #Row AS R 
2819	INNER JOIN dbo.PDetail AS P WITH(NOLOCK) ON R.COSDetailID = P.COSDetailID  
2820  
2821	INSERT INTO #SPODLocation_Capture (COSDetailID, SPODetailID, KeyLocationID, LocationType, LocationStyle, LoadNumber, StopNumber)
2822	SELECT 
2823		SD.COSDetailID,
2824		SL.SPODetailID,
2825		SL.KeyLocationID,
2826		SL.LocationType,
2827		SL.LocationStyle,
2828		SL.LoadNumber,
2829		SL.StopNumber
2830	FROM #ServiceDetailID_Capture SD (NOLOCK)
2831	INNER JOIN dbo.SPODLocation SL (NOLOCK) ON SL.SPODetailID = SD.SPODetailID
2832
2833	UPDATE R 
2834	SET 
2835		OriginLoadNumber = CL.LoadNumber,
2836		OriginStopNumber = CL.StopNumber,
2837		OriginCOLocationID = CL.COLocationID,
2838		OriginLocationPartyNumber = CL.LocationPartyNumber,
2839		OriginKeyLocationID = CL.KeyLocationID
2840	FROM #Row AS R 
2841    INNER JOIN dbo.COLocation AS CL (NOLOCK) ON R.COSDetailID = CL.COSDetailID
2842		AND ((CL.COLocationType = 'P' AND CL.LocationStyle <> 'X') OR (R.SourceTypeID = 2 AND CL.COLocationType = 'XP'))
2843	WHERE R.MultiLegFlg = 0
2844
2845	UPDATE R 
2846	SET 
2847		DestLoadNumber = CL.LoadNumber,
2848		DestStopNumber = CL.StopNumber,
2849		DestCOLocationID = CL.COLocationID,
2850		DestLocationPartyNumber = CL.LocationPartyNumber,
2851		DestKeyLocationID = CL.KeyLocationID
2852	FROM #Row AS R 
2853    INNER JOIN dbo.COLocation AS CL (NOLOCK) ON R.COSDetailID = CL.COSDetailID
2854		AND ((CL.COLocationType = 'D' AND CL.LocationStyle <> 'X') OR (R.SourceTypeID = 2 AND CL.COLocationType = 'XD'))
2855	WHERE R.MultiLegFlg = 0
2856
2857	/* Get KeyLocationID from the SPODLocation Table*/
2858	Update R
2859	SET
2860		OriginSPODKeyLocationID = 
2861			CASE 
2862				WHEN (SL.LocationType = 'P' AND SL.LocationStyle <> 'X') THEN SL.KeyLocationID 
2863				WHEN (R.SourceTypeID = 2 AND SL.LocationType = 'XP') THEN SL.KeyLocationID 
2864				ELSE NULL
2865			END
2866	FROM #Row R
2867	INNER JOIN #SPODLocation_Capture SL (NOLOCK) ON SL.COSDetailID = R.COSDetailID AND SL.StopNumber = R.OriginStopNumber
2868	WHERE R.MultiLegFlg = 0 
2869
2870	Update R
2871	SET
2872		DestSPODKeyLocationID =
2873			CASE 
2874				WHEN (SL.LocationType = 'D' AND SL.LocationStyle <> 'X') THEN SL.KeyLocationID
2875				WHEN (R.SourceTypeID = 2 AND SL.LocationType = 'XD') THEN SL.KeyLocationID
2876				ELSE NULL
2877			END
2878	FROM #Row R
2879	INNER JOIN #SPODLocation_Capture SL (NOLOCK) ON SL.COSDetailID = R.COSDetailID AND SL.StopNumber = R.DestStopNumber
2880	WHERE R.MultiLegFlg = 0
2881
2882
2883	--SUGGMIC - ML SHIPMENTS ONLY
2884	IF EXISTS(SELECT TOP 1 1 FROM #Row WHERE MultiLegFlg = 1)
2885	BEGIN
2886		IF @SummarizeShipment = 1
2887		BEGIN
2888			UPDATE R 
2889			SET 
2890				OriginLoadNumber = CL.LoadNumber,
2891				OriginStopNumber = CL.StopNumber,
2892				OriginCOLocationID = CL.COLocationID,
2893				OriginLocationPartyNumber = CL.LocationPartyNumber,
2894				OriginKeyLocationID = CL.KeyLocationID
2895			FROM #Row AS R 
2896			INNER JOIN dbo.COLocation AS CL (NOLOCK) ON R.COSDetailID = CL.COSDetailID 
2897				AND CL.COLocationType IN ('P', 'XP') AND CL.LocationStyle <> 'X' AND R.LoadNumber = CL.LoadNumber
2898			WHERE R.MultiLegFlg = 1
2899
2900			UPDATE R 
2901			SET 
2902				OriginSPODKeyLocationID =
2903				CASE
2904					WHEN SL.LocationType IN ('P', 'XP') AND SL.LocationStyle <> 'X' THEN SL.KeyLocationID
2905					ELSE NULL
2906				END
2907			FROM #Row AS R 
2908			INNER JOIN #SPODLocation_Capture SL (NOLOCK) ON SL.COSDetailID = R.COSDetailID 
2909			AND R.LoadNumber = SL.LoadNumber AND SL.StopNumber = R.OriginStopNumber
2910			WHERE R.MultiLegFlg = 1 
2911
2912			UPDATE R 
2913			SET 
2914				DestLoadNumber = CL.LoadNumber,
2915				DestStopNumber = CL.StopNumber,
2916				DestCOLocationID = CL.COLocationID,
2917				DestLocationPartyNumber = CL.LocationPartyNumber,
2918				DestKeyLocationID = CL.KeyLocationID
2919			FROM #Row AS R 
2920			INNER JOIN dbo.COLocation AS CL (NOLOCK) ON R.COSDetailID = CL.COSDetailID 
2921				AND CL.COLocationType IN ('D', 'XD') AND CL.LocationStyle <> 'X' AND R.LoadNumber = CL.LoadNumber
2922			WHERE R.MultiLegFlg = 1
2923
2924			UPDATE R 
2925			SET 
2926				DestSPODKeyLocationID =
2927				CASE 
2928					WHEN SL.LocationType IN ('D', 'XD') AND SL.LocationStyle <> 'X' THEN SL.KeyLocationID
2929					ELSE NULL
2930				END
2931			FROM #Row AS R 
2932			INNER JOIN #SPODLocation_Capture SL (NOLOCK) ON SL.COSDetailID = R.COSDetailID
2933				AND R.LoadNumber = SL.LoadNumber AND SL.StopNumber = R.DestStopNumber
2934			WHERE R.MultiLegFlg = 1 
2935		END
2936		ELSE 
2937		BEGIN
2938			UPDATE R 
2939			SET 
2940				OriginLoadNumber = CL.LoadNumber,
2941				OriginStopNumber = CL.StopNumber,
2942				OriginCOLocationID = CL.COLocationID,
2943				OriginLocationPartyNumber = CL.LocationPartyNumber,
2944				OriginKeyLocationID = CL.KeyLocationID
2945			FROM #Row AS R
2946			INNER JOIN dbo.COLocation AS CL (NOLOCK) ON CL.COSDetailID = R.COSDetailID 
2947			AND (CL.COLocationType = 'P') AND CL.LocationStyle <> 'X' 
2948			WHERE R.MultiLegFlg = 1
2949
2950			UPDATE R 
2951			SET 
2952                OriginSPODKeyLocationID =
2953				CASE 
2954					WHEN SLP.LocationType = 'P' AND SLP.LocationStyle <> 'X' THEN SLP.KeyLocationID
2955					ELSE NULL
2956				END
2957			FROM #Row AS R
2958			INNER JOIN #SPODLocation_Capture SLP WITH (NOLOCK) ON SLP.COSDetailID = R.COSDetailID  
2959			AND SLP.LoadNumber = R.OriginLoadNumber AND R.OriginStopNumber = SLP.StopNumber
2960			WHERE R.MultiLegFlg = 1
2961
2962			UPDATE R 
2963			SET
2964				DestLoadNumber = CL.LoadNumber,
2965				DestStopNumber = CL.StopNumber,
2966				DestCOLocationID = CL.COLocationID,
2967				DestLocationPartyNumber = CL.LocationPartyNumber,
2968				DestKeyLocationID = CL.KeyLocationID
2969			FROM #Row AS R
2970			INNER JOIN dbo.COLocation AS CL (NOLOCK) ON CL.COSDetailID = R.COSDetailID 
2971			AND (CL.COLocationType = 'D') AND CL.LocationStyle <> 'X'
2972			WHERE R.MultiLegFlg = 1
2973
2974			UPDATE R 
2975			SET
2976				DestSPODKeyLocationID =
2977				CASE 
2978					WHEN SLD.LocationType = 'D' AND SLD.LocationStyle <> 'X'  THEN SLD.KeyLocationID
2979					ELSE NULL
2980				END
2981			FROM #Row AS R
2982			INNER JOIN #SPODLocation_Capture SLD WITH (NOLOCK) ON SLD.COSDetailID = R.COSDetailID    
2983			AND SLD.LoadNumber = R.DestLoadNumber AND R.DestStopNumber = SLD.StopNumber
2984			WHERE R.MultiLegFlg = 1
2985		END
2986	END
2987
2988	/********************************************************************************************************/
2989	/******************** Update ROW table with CustomsCOService*********************************************/
2990	/********************************************************************************************************/
2991	
2992	UPDATE R SET CustomsCOServiceID = R1.COServiceID
2993	FROM #Row AS R
2994		INNER JOIN #Row AS R1
2995			ON R.[LoadNumber] = R1.[LoadNumber]
2996			AND R.SourceTypeID = R1.SourceTypeID 
2997			AND R1.[ShipmentType] = 'Customs Load'
2998	WHERE R.[LoadNumber] > 0 AND R.[ShipmentType] <> 'Customs Load' 
2999		
3000	DELETE FROM R 
3001	FROM #Row AS R
3002		INNER JOIN #Row AS R1 WITH(NOLOCK)
3003			ON R.COServiceID = R1.CustomsCOServiceID
3004
3005	IF @ReverseLogisticsFeature = 1
3006	BEGIN
3007		UPDATE R SET TicketServiceID = TicketService.COServiceID
3008		FROM #Row AS R 
3009			INNER JOIN dbo.COService AS TicketService WITH (NOLOCK)
3010				ON TicketService.COrderID = R.COrderID
3011				AND TicketService.SourceTypeID = 5
3012			INNER JOIN dbo.RLTicket AS T WITH (NOLOCK)
3013				ON T.COServiceID = TicketService.COServiceID
3014		WHERE R.CustomerOrderNumber > -1 AND T.TicketStatus <> 'Open'
3015	END
3016	
3017	
3018	/********************************************************************************************************/
3019	/******************** Build RecordIDs for ROW table******************************************************/
3020	/********************************************************************************************************/
3021
3022	INSERT INTO #RowID(
3023		 LoadNumber			
3024		,PlannedLoadNumber	
3025		,CustomerOrderNumber	-- 09/02/2008 GANASAD Updated from CHRSubOrderNumber
3026		,COSGroupID
3027		,COSDetailID			-- 04/11/08 GANASAD We need to add this field to RowID in order to handle multi stop loads. Unless we can give them different RecordIds, dates will do a max and not show true picture on OD level reports
3028		,SourceTypeID			-- 09/02/2008 GANASAD Added
3029	)
3030	SELECT 
3031		R.LoadNumber			
3032		,R.PlannedLoadNumber	
3033		,R.CustomerOrderNumber	
3034		,R.COSGroupID 
3035		,R.COSDetailID
3036		,R.SourceTypeID
3037	FROM #Row AS R
3038	WHERE LevelNumber=1
3039	UNION ALL
3040	SELECT DISTINCT
3041		CASE R.ShipmentType WHEN 'Multi OD Load' THEN R.LoadNumber ELSE -1 END AS LoadNumber			
3042		,CASE R.ShipmentType WHEN 'Multi OD Plan Load' THEN R.PlannedLoadNumber	 ELSE -1 END AS PlannedLoadNumber
3043		,-1 AS CustomerOrderNumber	
3044		,CASE R.ShipmentType WHEN 'Multi Intl Load' THEN R.COSGroupID ELSE -1 END AS COSGroupID		
3045		,-1 AS COSDetailID 
3046		,R.SourceTypeID AS SourceTypeID 
3047	FROM #Row AS R 
3048	WHERE LevelNumber=2 
3049
3050	UPDATE R SET RecordID = I.RecordID
3051	FROM #Row AS R 
3052		INNER JOIN #RowID AS I
3053			ON R.LoadNumber	= I.LoadNumber	
3054			AND R.PlannedLoadNumber = I.PlannedLoadNumber	
3055			AND R.CustomerOrderNumber =  I.CustomerOrderNumber
3056			AND R.COSGroupID =  I.COSGroupID
3057			AND R.COSDetailID = I.COSDetailID
3058			AND R.SourceTypeID = I.SourceTypeID
3059	WHERE R.LevelNumber=1 
3060	
3061	UPDATE R SET RecordID = I.RecordID
3062	FROM #Row AS R 
3063		INNER JOIN #RowID AS I
3064			ON R.LoadNumber	= I.LoadNumber		
3065			AND R.SourceTypeID = I.SourceTypeID 
3066	WHERE R.LevelNumber=2 AND R.ShipmentType='Multi OD Load'
3067
3068	UPDATE R SET RecordID = I.RecordID
3069	FROM #Row AS R 
3070		INNER JOIN #RowID AS I 
3071			ON R.PlannedLoadNumber = I.PlannedLoadNumber	
3072	WHERE R.LevelNumber=2 AND R.ShipmentType='Multi OD Plan Load'
3073
3074	UPDATE R SET RecordID = I.RecordID
3075	FROM #Row AS R 
3076		INNER JOIN #RowID AS I 
3077			ON R.COSGroupID	= I.COSGroupID	
3078	WHERE R.LevelNumber=2 AND R.ShipmentType='Multi Intl Load' 
3079
3080	-- Remove the spoofed records that are non-SA and had to be included in the booked search
3081	-- this is done to make the "Booked" filter work correctly with filtered loads
3082	IF @SpoofBookedStatus = 1 
3083	  AND EXISTS(SELECT StatusBucket FROM #StatusBucket WHERE StatusBucket = 'Booked/Tendered to Carrier') 
3084	  AND NOT EXISTS(SELECT StatusBucket FROM #StatusBucket WHERE StatusBucket = 'Ordered') 
3085	BEGIN
3086		DELETE R FROM #Row AS R 
3087		 INNER JOIN dbo.COService AS CS
3088		  ON R.COServiceID = CS.COServiceID 
3089		 INNER JOIN #SpoofStatus AS SpoofStatus
3090		  ON CS.KeyStatusID = SpoofStatus.StatusID 
3091		WHERE R.LogisticFlg = 1
3092	END
3093	
3094	-- Moved them up for performance reasons
3095	--CREATE CLUSTERED INDEX INX_RowService On #Row(COServiceID)
3096	--CREATE INDEX INX_RowSDetail On #Row(COSDetailID)
3097	--CREATE INDEX INX_RowCHRNumber ON #Row(CHRNumber)
3098	--PRINT 'About to exec'
3099	EXEC dbo.AdHocReport39 @LogID
3100	
3101SET NOCOUNT OFF
3102
3103END