SQL View of ConnectShip order data

CONNECTSHIP_ORDERINFO

SELECT
	dbo.SYSOENT.ORDER_STATUS,
	dbo.SHIPTO.SHIPTO_ID,
	dbo.SHIPTO.ORDER_NO,
	RTRIM(dbo.SHIPTO.SHIPMETH_CODE) AS SHIPMETH_CODE,
	RTRIM(dbo.SHIPTO.COMPANY_NAME) AS SHIPTO_COMPANY_NAME,
	RTRIM(dbo.SHIPTO.LAST_NAME) AS SHIPTO_LAST_NAME,
	RTRIM(dbo.SHIPTO.FIRST_NAME) AS SHIPTO_FIRST_NAME,
	RTRIM(dbo.SHIPTO.ADDRESS_LINE1) AS SHIPTO_ADDRESS_LINE1,
	
	-- Secondary PO Boxes indicate distressed address.  Remove PO Box.
	CASE
		WHEN (dbo.SHIPTO.ADDRESS_LINE2 LIKE '% BOX %' AND dbo.SHIPTO.ADDRESS_LINE2 LIKE 'P%')
		OR dbo.SHIPTO.ADDRESS_LINE2 LIKE 'REF #%'
		THEN ''
		ELSE RTRIM(dbo.SHIPTO.ADDRESS_LINE2)
	END AS SHIPTO_ADDRESS_LINE2,

	-- Freight Forwarders add Ref# to 	
	CASE
		WHEN dbo.SHIPTO.ADDRESS_LINE2 LIKE 'REF #%'
		THEN dbo.SHIPTO.ADDRESS_LINE2
		ELSE dbo.SHIPTO.ADDRESS_LINE3
	END AS SHIPTO_ADDRESS_LINE3,

	RTRIM(dbo.SHIPTO.CITY) AS SHIPTO_CITY,
	dbo.SHIPTO.STATE AS SHIPTO_STATE,
	dbo.SHIPTO.ZIPCODE AS SHIPTO_ZIPCODE,
	
	-- Alter Puerto Rico
	CASE
		WHEN dbo.SHIPTO.STATE = 'PR'
		THEN 'PR'
		ELSE RTRIM(dbo.COUNTRY.A1)
	END AS SHIPTO_COUNTRY,
	
	dbo.SHIPTO.PHONE_AREA AS SHIPTO_PHONE_AREA,
	dbo.SHIPTO.PHONE_EXCH AS SHIPTO_PHONE_EXCH,
	dbo.SHIPTO.PHONE_EXT AS SHIPTO_PHONE_EXT,
	RTRIM(dbo.SYSCUST.EMAIL1) AS BILLTO_EMAIL,
	
	-- Email Toggle Column. Not really used unless we used Connectship's email platform
	CASE RTRIM(SYSCUST.EMAIL1)
		WHEN '' 
			THEN 'F'
		ELSE 'T'
	END AS doEMAIL,

	-- No company name means this is residential
	 CASE RTRIM(SHIPTO.company_Name)
		WHEN '' 
			THEN 'T'
			ELSE 'F'
	END AS isRESI,

	-- ShipMethod Configurator
	CASE
		-- Force all PO Boxes to go US Mail
		WHEN SHIPTO.ADDRESS_LINE1 LIKE '% BOX %' AND SHIPTO.ADDRESS_LINE1 LIKE 'P%' 
			THEN 'MailOnly'

		-- Secondary PO Box only ship UPS
		WHEN dbo.SHIPTO.ADDRESS_LINE2 LIKE '% BOX %' AND dbo.SHIPTO.ADDRESS_LINE2 LIKE 'P%'
			THEN 'UPSGround'
			
		-- PRICE THRESHOLDS FOR FREE SHIPPING

		-- Free shipping and sale is less than $200 = Economy
		WHEN SHIPTO.SHIPMETH_CODE = '11'
			AND SYSOENT.SHIP_CHARGE = 0
			AND SYSOENT.GROSS < '199.95'
			AND COUNTRY.A1 = 'US' THEN 'Economy'
			
		-- ANY shipping charge = Ground
		WHEN SHIPTO.SHIPMETH_CODE = '11'
			AND SYSOENT.SHIP_CHARGE <> 0
			AND COUNTRY.A1 = 'US' THEN 'Ground'

		-- Free shipping, more than $200, and in US = Ground
		WHEN SHIPTO.SHIPMETH_CODE = '11'
			AND SYSOENT.SHIP_CHARGE = 0
			AND SYSOENT.GROSS >= '199.95'
			AND COUNTRY.A1 = 'US' THEN 'Ground'

		-- Express Methods
		WHEN SHIPTO.SHIPMETH_CODE = '1' THEN 'NextDay'
		WHEN SHIPTO.SHIPMETH_CODE = '2' THEN 'Saturday'
		WHEN SHIPTO.SHIPMETH_CODE = '5' OR dbo.SHIPTO.STATE = 'PR' THEN '2ndDay'
		WHEN SHIPTO.SHIPMETH_CODE = '3' THEN '3Day'

		-- US Mail only
		WHEN SHIPTO.SHIPMETH_CODE = 'USP' THEN 'MailOnly'
		WHEN SHIPTO.SHIPMETH_CODE = 'USM' THEN 'MailOnly'

		-- UPS only           
		WHEN SHIPTO.SHIPMETH_CODE = 'UPS' THEN 'UPSGround'
		WHEN SHIPTO.SHIPMETH_CODE = 'NO MAIL' THEN 'UPSGround'

		-- International
		WHEN SHIPTO.SHIPMETH_CODE = 'FIE' THEN 'WorldwideEconomy'
		WHEN SHIPTO.SHIPMETH_CODE = 'FCI' THEN 'WorldwideEconomy'
		WHEN SHIPTO.SHIPMETH_CODE = 'WEX' THEN 'WorldwideEconomy'
		WHEN SHIPTO.SHIPMETH_CODE = 'WEP' THEN 'WorldwideEconomy'
		WHEN SHIPTO.SHIPMETH_CODE = 'WWE' THEN 'WorldwideEconomy'

		-- If not US, ship international anyway
		WHEN RTRIM(COUNTRY.A1) <> 'US' THEN 'WorldwideEconomy'

		-- Error should throw here
		ELSE 'UNKNOWN'

	END AS SHIPMETHOD,
	dbo.SYSOENT.DIVISION,
	dbo.SYSOENT.SALESMAN_ID,
	dbo.MIMPORT.TRACKING_NO,
	dbo.MIMPORT.STATUS AS IMPORT_STATUS,
	RTRIM(dbo.SYSOENT.PO_NUMBER) AS PO_NUMBER,
	dbo.SYSCUST.PHONE_AREA AS BILLTO_CUST_PHONE_AREA,
	dbo.SYSCUST.PHONE_EXCH AS BILLTO_CUST_PHONE_EXCH,
	dbo.SYSCUST.PHONE_EXT AS BILLTO_CUST_PHONE_EXT,
	
	-- Fix for order gross not populating correctly
	CASE
		WHEN SYSOENT.GROSS > '0' THEN SYSOENT.GROSS ELSE '0'
	END AS ORDER_VALUE,
	
	dbo.SYSOENT.SHIP_CHARGE,
	RTRIM(dbo.DIVISION.DIVISION_DESC) AS LABEL_COMPANY_NAME,
	RTRIM(dbo.DIVISION.CS_PHONE) AS LABEL_COMPANY_PHONE,
	RTRIM(dbo.DIVISION_EX.LABEL_ATTENTION) AS LABEL_ATTENTION,
	RTRIM(dbo.DIVISION_EX.LABEL_ADDRESS_LINE1) AS LABEL_ADDRESS_LINE1,
	RTRIM(dbo.DIVISION_EX.LABEL_ADDRESS_LINE2) AS LABEL_ADDRESS_LINE2,
	RTRIM(dbo.DIVISION_EX.LABEL_ADDRESS_LINE3) AS LABEL_ADDRESS_LINE3,
	RTRIM(dbo.DIVISION_EX.LABEL_ADDRESS_ZIP) AS LABEL_ADDRESS_ZIP,
	RTRIM(dbo.DIVISION_EX.LABEL_ADDRESS_STATE) AS LABEL_ADDRESS_STATE,
	RTRIM(dbo.DIVISION_EX.LABEL_ADDRESS_COUNTRY) AS LABEL_ADDRESS_COUNTRY,
	RTRIM(dbo.DIVISION_EX.LABEL_ADDRESS_CITY) AS LABEL_ADDRESS_CITY,

	-- Declare Value
	CASE
		WHEN SYSOENT.GROSS > 290 
			THEN 'Y' ELSE 'N'
	END AS doDeclareValue,

	-- Require Signature
	CASE
		WHEN SYSOENT.GROSS > 1000 
		THEN 'Y' ELSE 'N'
	END AS doRequireSignature

FROM dbo.DIVISION_EX WITH(NOLOCK)
	RIGHT OUTER JOIN dbo.DIVISION WITH(NOLOCK) ON dbo.DIVISION_EX.DIVISION = dbo.DIVISION.DIVISION
	RIGHT OUTER JOIN dbo.SHIPTO WITH(NOLOCK)
		INNER JOIN dbo.COUNTRY WITH(NOLOCK) ON dbo.SHIPTO.COUNTRY = dbo.COUNTRY.COUNTRY_NAME_INVERSE_KEY
		INNER JOIN dbo.SYSOENT WITH(NOLOCK) ON dbo.SHIPTO.ORDER_NO = dbo.SYSOENT.ORDER_NO
		INNER JOIN dbo.SYSCUST WITH(NOLOCK) ON dbo.SYSOENT.CUSTOMER_ID = dbo.SYSCUST.CUSTOMER_ID
	LEFT OUTER JOIN dbo.MIMPORT WITH(NOLOCK) ON dbo.SYSOENT.ORDER_NO = dbo.MIMPORT.ORDER_NO
		INNER JOIN dbo.LINEITEM WITH(NOLOCK) ON dbo.SYSOENT.ORDER_NO = dbo.LINEITEM.ORDER_NO
		INNER JOIN dbo.INVENTOR WITH(NOLOCK) ON dbo.LINEITEM.ITEM_ID = dbo.INVENTOR.ITEM_ID 
	ON dbo.DIVISION.DIVISION = dbo.SYSOENT.DIVISION

WHERE
	-- Only W and Q
	(dbo.SHIPTO.STATUS IN ('w','q'))
	-- No Tracking ID in MIMPORT
	AND (dbo.MIMPORT.TRACKING_NO IS NULL)
	-- Ship Method is not pickup or email
	AND (dbo.SHIPTO.SHIPMETH_CODE NOT IN ('PU','EMAIL'))

GROUP BY
	dbo.SHIPTO.SHIPTO_ID,
	dbo.SYSOENT.ORDER_STATUS,
	dbo.SHIPTO.ORDER_NO,
	dbo.SHIPTO.SHIPMETH_CODE,
	dbo.SHIPTO.COMPANY_NAME,
	dbo.SHIPTO.LAST_NAME,
	dbo.SHIPTO.FIRST_NAME,
	dbo.SHIPTO.ADDRESS_LINE1,
	dbo.SHIPTO.ADDRESS_LINE2,
	dbo.SHIPTO.ADDRESS_LINE3,
	dbo.SHIPTO.CITY,
	dbo.SHIPTO.STATE,
	dbo.SHIPTO.ZIPCODE,
	dbo.COUNTRY.A1,
	dbo.SHIPTO.PHONE_AREA,
	dbo.SHIPTO.PHONE_EXCH,
	dbo.SHIPTO.PHONE_EXT,
	dbo.SYSCUST.EMAIL1,
	dbo.SYSOENT.SHIPMETH_CODE,
	dbo.MIMPORT.TRACKING_NO,
	dbo.MIMPORT.STATUS,
	dbo.SYSOENT.GROSS,
	dbo.SYSOENT.SHIP_CHARGE,
	dbo.SYSOENT.PO_NUMBER,
	dbo.SYSCUST.PHONE_AREA,
	dbo.SYSCUST.PHONE_EXCH,
	dbo.SYSCUST.PHONE_EXT,
	dbo.SYSOENT.DIVISION,
	dbo.SYSOENT.SALESMAN_ID,
	dbo.DIVISION.DIVISION_DESC,
	dbo.DIVISION.CS_PHONE,
	dbo.DIVISION_EX.LABEL_ATTENTION,
	dbo.DIVISION_EX.LABEL_ADDRESS_LINE1,
	dbo.DIVISION_EX.LABEL_ADDRESS_LINE2,
	dbo.DIVISION_EX.LABEL_ADDRESS_LINE3,
	dbo.DIVISION_EX.LABEL_ADDRESS_ZIP,
	dbo.DIVISION_EX.LABEL_ADDRESS_STATE,
	dbo.DIVISION_EX.LABEL_ADDRESS_COUNTRY,
	dbo.DIVISION_EX.LABEL_ADDRESS_CITY;