SELECT
	vue_die_composite.work_order,
	vue_die_composite.frm_type,
	vue_die_composite.customer_number,
	vue_customers.name,
	territory_code = vue_customers.sales_person,
	COALESCE(sales_p.full_name,'Terr-' + vue_customers.sales_person) AS sales_assoc_resp,
	vue_die_composite.po,
	vue_die_composite.customer_die_number,
	COALESCE(sales_p.full_name,'Terr-' + vue_customers.sales_person) AS territory_sales,
	vue_die_composite.date_entered,
	COALESCE(entered_p.full_name,'Emp-' + vue_die_composite.sales_person) AS entered_by,
	terms,
	terms_data,
	vue_die_composite.invoice_amount,
	@QUOTE_AMOUNT AS quote_amount,
	vue_die_composite.retool,
	vue_die_composite.scheduled_ship_date,
	COALESCE(ship_list.description,'NOT FOUND') AS ship_via,
	COALESCE(ship_list2.description, ship_list.description,'NOT FOUND') AS ship_via2,
	CASE COALESCE(vue_die_composite.ship_cod, 0)
			WHEN 1 THEN 'COD'
			ELSE ''
		END AS ship_cod,
	CASE COALESCE(vue_die_composite.ship_sat, 0)
			WHEN 1 THEN 'SAT'
			ELSE ''
		END AS ship_sat,
	vue_die_composite.insurance_code,
	vue_die_composite.value_amount,
	vue_die_composite.ship_priority,
	vue_die_composite.complex_die,
	vue_die_composite.number_of_inches,
	(RTRIM(address) + CHAR(13) + CHAR(10) + RTRIM(city) + ', ' + RTRIM(state) + ' ' + zip_code) AS address,
	tab_util_address.country,
	/* Begin Die Specific Stuff*/
	COALESCE(tab_cu_press_information.roll_drawing_number, vue_die_composite.roll_drawing_number, '') AS roll_drawing_number,
	COALESCE(tab_cu_press_information.pitch, vue_die_composite.pitch, '') AS pitch,
	COALESCE(tab_cu_press_information.degrees, vue_die_composite.degrees, '') AS degrees,
	COALESCE(tab_rd_press_description.press_description, 'UNAVAILABLE') AS press_description,
	number_of_teeth,
	pd,
	CASE gear
			WHEN 0 THEN (pd * 3.14159)
			ELSE wildat.dbo.udf_circ_from_teeth(number_of_teeth, COALESCE(tab_cu_press_information.pitch, vue_die_composite.pitch, '1/8CP')) --can't really have no pitch, so assume for some old cases
		END AS circumference,
	part_number,
	steel_type,
	COALESCE(shape_list.description,'NOT FOUND') AS shape,
	corner_radius,
	inches_across,
	inches_around,
	number_across,
	number_around,
	space_between_across,
	CASE nested
			WHEN 0 THEN CONVERT(CHAR,space_between_around)
			ELSE 'NESTED'
		END AS space_between_around,
	plies,
	tab_die_ed.liner,
	tab_die_ed.middle,
	tab_die_ed.face,
	COALESCE(applic.description,'NOT FOUND') AS application,
	chrome,
	COALESCE(ae_type_list.description,'NOT FOUND') AS air_eject_type,
	air_eject_type AS ae_code,
	perforator,
	perf_a,
	perf_b,
	perf_c,
	micro_perf,
	COALESCE(cuts_to.description,'NOT FOUND') AS cuts_to,
	COALESCE(matl_stat.description,'NOT FOUND') AS material_status,
	engraving_depth,
	offset_towards_gear,
	CONVERT(CHAR(1024),COALESCE(c1.comment,'')) AS work_order_comment,
	CONVERT(CHAR(1024),COALESCE(c2.comment,'')) AS material_status_comment,
	CONVERT(CHAR(1024),COALESCE(c3.comment,'')) AS tag_comment,
	CONVERT(CHAR(1024),COALESCE(c4.comment,'')) AS qc_comment,
	CONVERT(CHAR(1024),COALESCE(c5.comment,'')) AS roll_drawing_comment,
	blade_angle,
	nested,
	breaker_bar,
	breaker_bars_per_cavity,
	new_box,
	new_gear,
	vue_die_composite.sales_person,
	COALESCE(ordered_by,'N/A') AS ordered_by,
	fax,
	gear,
	COALESCE(drives_from,2) AS drives_from,  --0=front, 1=rear, 2=n/a
	plasma_coat,
	machine_sharpened,
	CASE cryogenic_treat
		WHEN 0 THEN 'NO '
		ELSE 'YES'
	END AS cryogenic_treat,
	c1_station,
	vue_die_composite.roll_drawing_number as test,
	total_balance,
	credit_limit,
	COALESCE(oldest_days,0) AS oldest_days,
	plasma_inhouse,
	stagger,
	shape AS shape_code,
	CASE COALESCE(drives_from,2)
			WHEN 0 THEN 'F'
			WHEN 1 THEN 'R'
			WHEN 2 THEN 'U'
		END AS press_runs,
	CASE 
			WHEN CHARINDEX('UC', cuts_to) > 0 THEN 'U'
			WHEN cuts_to = 'MMU' THEN 'U'
			WHEN cuts_to = 'FB-C' THEN 'U'
			WHEN cuts_to = 'FI-C' THEN 'U'
			WHEN cuts_to = 'F-C' THEN 'U'
			ELSE 'T'
		END AS top_under_cutter,
	COALESCE(tab_cust_material.customer_stock_number, '') AS customer_stock_number,
	cust_stock_width,
	between_bear_dim - (COALESCE(bear_width_adjust, 0)*2) AS between_bear_dim
FROM
	vue_die_composite JOIN tab_die_ed ON vue_die_composite.work_order = tab_die_ed.work_order 
		JOIN tab_roll_drawings ON vue_die_composite.roll_drawing_number = tab_roll_drawings.roll_drawing_number 
		JOIN vue_customers ON vue_die_composite.customer_number = vue_customers.customer_number 
		LEFT JOIN tab_cu_ar_balances (NOLOCK) ON vue_customers.customer_number = tab_cu_ar_balances.customer_number
		LEFT JOIN tab_util_address ON vue_die_composite.address_id = tab_util_address.counter 
		LEFT JOIN tab_die_lists ship_list ON vue_die_composite.ship_via = ship_list.code AND ship_list.category = 'SHIP'
		LEFT JOIN tab_die_lists ship_list2 ON vue_die_composite.ship_via2 = ship_list2.code AND ship_list2.category = 'SHIP'
		LEFT JOIN tab_die_lists applic ON tab_die_ed.application = applic.code AND applic.category = 'APPLIC'
		LEFT JOIN tab_die_lists ae_type_list ON tab_die_ed.air_eject_type = ae_type_list.code AND ae_type_list.category = 'MISC_BORE'
		LEFT JOIN tab_die_lists matl_stat ON tab_die_ed.material_status = matl_stat.code AND matl_stat.category = 'MATL_STAT'
		LEFT JOIN tab_die_lists cuts_to ON tab_die_ed.cuts_to = cuts_to.code AND cuts_to.category = 'CUTS_TO'
		LEFT JOIN tab_die_lists shape_list ON vue_die_composite.shape = shape_list.code AND shape_list.category = 'SHAPE'
		LEFT JOIN tab_cust_material ON tab_die_ed.material_counter = tab_cust_material.counter 
		LEFT JOIN tab_territory_assignment sales_p ON vue_customers.sales_person = sales_p.territory AND sales_p.primary_contact = 1 
		LEFT JOIN tab_territory_assignment entered_p ON vue_die_composite.sales_person = entered_p.login_id AND  vue_customers.sales_person = entered_p.territory 
		LEFT JOIN tab_cu_press_information ON vue_die_composite.press_counter = tab_cu_press_information.counter
		LEFT JOIN tab_rd_press_description ON tab_cu_press_information.press_id = tab_rd_press_description.counter
		LEFT JOIN tab_comment c1 ON vue_die_composite.work_order_com_id = c1.comid  
		LEFT JOIN tab_comment c2 ON tab_die_ed.material_status_com_id = c2.comid 
		LEFT JOIN tab_comment c3 ON vue_die_composite.com_id = c3.comid  
		LEFT JOIN tab_comment c4 ON vue_die_composite.qc_com_id = c4.comid 
		LEFT JOIN tab_comment c5 ON tab_roll_drawings.comid = c5.comid 
WHERE
	vue_die_composite.work_order = @WORK_ORDER