USP Example

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [usp_name]
AS
BEGIN
		SELECT TOP 1
			[activitygroup_base]
			,[wh_id]
			,[facilityid]
			,[systype]
			,[version]
			,[vendor]
		INTO #company_default
		FROM [ADS].[dbo].[ADS_company_Default]


		SELECT
			[bo].[ID] AS [OrderID]
			, [bo].[ShipDate]
		INTO #shipped_orders
		FROM [SC].[dbo].[bvc_Order] bo
		INNER JOIN [SC].[dbo].[bvc_OrderItem] boi
			ON [bo].[ID] = [boi].[OrderID]
		FULL JOIN [ADS].[dbo].[ADS_company] adr
			ON [bo].[ID] = [adr].[aux3]
		WHERE 1=1
			AND [adr].[wh_id] IS NULL
			AND [bo].[ShipDate] BETWEEN @startDate AND @endDate
			AND [bo].[TimeOfOrder] > GETDATE() - 120
			AND [bo].[StatusCode]				= 3
			AND [bo].[PaymentStatus]			= 30
			AND [bo].[ShippingStatus]			= 3
			AND [bo].[DropShipStatus]			= 0
			AND [boi].[ShipFromWareHouseID]		= 176
			AND [bo].[InvoicePrinted]			= 1
		GROUP BY [bo].[ID], [bo].[ShipDate]




		SELECT
			[wbm].[OrderID]
			, MAX( [wbm].[WarehouseBinID] ) AS [WarehouseBinID]
			, MAX( [bcu].[UserID] )		AS [Name]
			, COUNT(*)						AS [BinActivityCount]
			, MAX( [sho].[ShipDate] )		AS [ShipDate]
			, MAX( [bcusup].[FirstName] ) + ' ' + MAX( [bcusup].[LastName] )		AS [Supervisor]
		INTO #order_bin_data
		FROM #shipped_orders sho
		FULL JOIN [SC].[dbo].[WarehouseBinMovement] wbm
			ON [wbm].[OrderID] = [sho].[OrderID]
		FULL JOIN [SC].[dbo].[bvc_Client_User] bcu
			ON [wbm].[ActivityByUserID] = [bcu].[UserID]
		FULL JOIN [SC].[dbo].[bvc_Client_User] bcusup
			ON [bcu].[Supervisor] = [bcusup].[UserID]
		WHERE 1=1
			-- Filter by requirements
			AND [wbm].[MovementType] ='0'
			AND [sho].[OrderID] IS NOT NULL
		GROUP BY [wbm].[OrderID]


		SELECT
			[bop].[OrderID]
			, COUNT(*) AS [PackageCount]
		INTO #package_count
		FROM #shipped_orders sho
		INNER JOIN [SC].[dbo].[bvc_Order_Package] bop
			ON [sho].[OrderID] = [bop].[OrderID]
		GROUP BY [bop].[OrderID]



		SELECT
			[obd].[OrderID]
			, [obd].[WarehouseBinID]
			, [obd].[Name]
			, [obd].[BinActivityCount]
			, [pkc].[PackageCount]
			, [obd].[ShipDate]
			, [obd].[Supervisor]
		INTO #transaction_data
		FROM #order_bin_data obd
		INNER JOIN #package_count pkc
			ON [obd].[OrderID] = [pkc].[OrderID]

		SELECT
			[bo].[ID]		AS [OrderID]
			, [boi].[ProductID]
			, CAST(
				 ( ( [boi].[Qty] * [bppa].[ShippingWeight] ) / 16 )
				AS DECIMAL( 18,1))
			AS [Line_ShippingWeight]
			, CAST(
				(
				(
					( [bppa].[ShippingLength] * [bppa].[ShippingWidth] * [bppa].[ShippingHeight] )
					* [boi].[Qty]
					)
					/ (12 * 12 * 12 )
				)
				AS decimal(18,4))
				 AS [Line_CubicFeet]
			, [bo].[ProductIDHash]
			, CAST( [bppd].[PACKEDSHIPPABLE] AS INTEGER )			AS [PACKEDSHIPPABLE]
		INTO #order_data_raw
		FROM #transaction_data tda
		INNER JOIN [SC].[dbo].[bvc_Order] bo
			ON [tda].[OrderID] = [bo].[ID]
		INNER JOIN  [SC].[dbo].[bvc_OrderItem] boi
			ON [bo].[ID] = [boi].[OrderID]
		INNER JOIN  [SC].[dbo].[bvc_Product] bp
			ON [boi].[ProductID] = [bp].[ID]
		INNER JOIN [SC].[dbo].[bvc_Product_Properties_Amazon] bppa
			ON [boi].[ProductID] = [bppa].[ProductID]
		INNER JOIN [SC].[dbo].[bvc_Product_Properties_UserDefined] bppd
			ON [boi].[ProductID] = [bppd].[ProductID]
		WHERE 1=1
		;


		SELECT
			[OrderID]
			, SUM( [Line_ShippingWeight] )	AS [ShippingWeight]
			, SUM( [Line_CubicFeet] )		AS [CubicFeet]
			, MAX( [ProductIDHash] )		AS [ProductIDHash]
			, MAX( [PACKEDSHIPPABLE] )		AS [PACKEDSHIPPABLE]
		INTO #order_data_grouped
		FROM #order_data_raw
		GROUP BY [OrderID]
		;


SELECT
	'SHIPPING'												AS [activity]
	, [rde].[activitygroup_base] + 'SHIPPING'				AS [activitygroup]
	, [tda].[PackageCount]									AS [qty]
	, 'BOXES_SHIPPED'										AS [uom]
	, ''													AS [step]
	, '0'													AS [stepsequence]
	, [tda].[Name]											AS [userid]
	, [tda].[OrderID]										AS [grouping]
	, LEFT(RTRIM(CONVERT(DATETIMEOFFSET, [tda].[ShipDate])), 19)	AS [actdate]
	, [tda].[OrderID]										AS [hostid]
	, [rde].[wh_id]
	, [rde].[facilityid]
	, [rde].[systype]
	, [rde].[version]
	, '1' 													AS [essentialflg]
	, [rde].[vendor]
	, [odg].[ShippingWeight]								AS [aux1]			 	-- Weight
	, [odg].[CubicFeet]										AS [aux2]				-- DIMS
	, [tda].[OrderID]										AS [aux3]		 		-- ORDER NUMBER
	, REPLACE([odg].[ProductIDHash],'~','..')				AS [aux4] 				-- SKU
	, [tda].[OrderID]										AS [aux5]	 			-- TRANSACTION_ID
	, [wbi].[LocationNotes]									AS [aux6] 				-- FROM_LOCATION
	, [wbi].[LocationNotes]									AS [aux7] 				-- TO_LOCATION
	, [wbi].[BinName]										AS [aux8]	 			-- BIN
	, ISNULL( [tda].[Supervisor], '' )						AS [aux9]		 		-- SUPERVISOR
	, ISNULL( [bopud].[SCANCONFIRMATIONSTATIONNAME], '')	AS [aux10]		 		-- STATION_ID
	, ''													AS [aux11]	 			-- NOTE_CONTAINS
	, ''													AS [aux12] 				-- POID
	, CASE
		WHEN [odg].[PACKEDSHIPPABLE] = 0 THEN ''
		ELSE 'Overboxed'
	END														AS [aux13] 				-- OVERBOXED
	, ''													AS [aux14] 				-- PLACEHOLDER
	, ''													AS [aux15] 				-- PLACEHOLDER
	, ''													AS [aux16] 				-- PLACEHOLDER
	, ''													AS [aux17] 				-- PLACEHOLDER
	, ''													AS [aux18]	 			-- SYSTEM_STATUS
	, ''													AS [aux19]		 		-- SYSTEM_NOTE
	, ''													AS [aux20]				-- MISC

FROM #transaction_data tda
INNER JOIN #order_data_grouped odg
	ON [tda].[OrderID] = [odg].[OrderID]
INNER JOIN [SC].[dbo].[WarehouseBin] wbi
	ON [tda].[WarehouseBinID] = [wbi].[ID]
INNER JOIN [SC].[dbo].[bvc_Order_Property_UserDefined] bopud
	ON [bopud].[OrderID] = [tda].[OrderID]
INNER JOIN #company_default rde
	ON 1 = 1
GO

END
GO