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