Below is the code:
Select
soh.so_number "Sales Order"
,sod.sod_auto_key
-- ,sod.route_code
,soh.company_ref_number "Cust PO Number"
,pnm.pn "Part Number"
,pnm.description "Item Description"
,cmp.company_name "Customer Name"
,sod.next_ship_date "Projected Date"
,sod.due_date "Promise Date"
,sod.so_udf_002 "RLSE Prod"
,sod.so_udf_003 "EST Ship"
,sod.so_udf_004 "Expedite Requested"
,CAST(sod.notes AS VARCHAR2(255)) "Notes"
,soh.entry_date "SO Date"
,NVL(sod.qty_ordered,0) "SalesOrder Ordered"
,NVL(sod.qty_invoiced,0) "Qty Shipped"
,NVL(sod.qty_ordered,0) - NVL(sod.qty_invoiced,0) "Qty To Ship"
,NVL(sod.unit_price,0) "Qty Unit_Price"
,(NVL(sod.qty_ordered,0) - NVL(sod.qty_invoiced,0)) * NVL(sod.unit_price,0) "Total Sales"
,case
when NVL(sod.unit_cost,0) <> 0 then NVL(sod.unit_cost,0)
when NVL(sod.unit_cost,0) = 0 and NVL(sod.est_cost,0) <> 0 then NVL(sod.est_cost,0)
when NVL(sod.unit_cost,0) = 0 and NVL(sod.est_cost,0) = 0 and NVL(cqd.unit_cost,0) <> 0 then NVL(cqd.unit_cost,0)
else 0
end "Unit Cost"
-- ,decode(NVL(sod.unit_cost,0),0,NVL(cqd.unit_cost,0),NVL(sod.unit_cost,0)) "Unit Cost"
-- ,NVL(cqd.unit_cost,0) "Est Cost"
-- ,decode(NVL(sod.unit_cost,0),0,NVL(cqd.unit_cost,0),NVL(sod.unit_cost,0)) * NVL(sod.qty_ordered,0) "COGS"
,case
when NVL(sod.unit_cost,0) <> 0 then NVL(sod.unit_cost,0) * (NVL(sod.qty_ordered,0) - NVL(sod.qty_invoiced,0))
when NVL(sod.unit_cost,0) = 0 and NVL(sod.est_cost,0) <> 0 then NVL(sod.est_cost,0) * (NVL(sod.qty_ordered,0) - NVL(sod.qty_invoiced,0))
when NVL(sod.unit_cost,0) = 0 and NVL(sod.est_cost,0) = 0 and NVL(cqd.unit_cost,0) <> 0 then NVL(cqd.unit_cost,0) * (NVL(sod.qty_ordered,0) - NVL(sod.qty_invoiced,0))
else 0
end "COGS"
--,(NVL(sod.unit_price,0) - decode(NVL(sod.unit_cost,0),0,NVL(cqd.unit_cost,0),NVL(sod.unit_cost,0))) * NVL(sod.qty_ordered,0) "Gross Profit"
,case
when NVL(sod.unit_cost,0) <> 0 then (NVL(sod.unit_price,0) - NVL(sod.unit_cost,0)) * (NVL(sod.qty_ordered,0) - NVL(sod.qty_invoiced,0))
when NVL(sod.unit_cost,0) = 0 and NVL(sod.est_cost,0) <> 0 then (NVL(sod.unit_price,0) -NVL(sod.est_cost,0)) * (NVL(sod.qty_ordered,0) - NVL(sod.qty_invoiced,0))
when NVL(sod.unit_cost,0) = 0 and NVL(sod.est_cost,0) = 0 and NVL(cqd.unit_cost,0) <> 0 then (NVL(sod.unit_price,0) - NVL(cqd.unit_cost,0)) * (NVL(sod.qty_ordered,0) - NVL(sod.qty_invoiced,0))
else 0
end "Gross Profit"
,case when (NVL(sod.qty\_ordered,0) - NVL(sod.qty\_invoiced,0)) \* NVL(sod.unit\_price,0) = 0 then 0
when NVL(sod.unit\_cost,0) \<> 0 then ROUND(((NVL(sod.unit\_price,0) - NVL(sod.unit\_cost,0)) \* (NVL(sod.qty\_ordered,0) - NVL(sod.qty\_invoiced,0)))/(NVL(sod.qty\_ordered,0) \* NVL(sod.unit\_price,0)),4) \* 100
when NVL(sod.unit\_cost,0) = 0 and NVL(sod.est\_cost,0) \<> 0 then ROUND(((NVL(sod.unit\_price,0) - NVL(sod.est\_cost,0)) \* (NVL(sod.qty\_ordered,0) - NVL(sod.qty\_invoiced,0)))/((NVL(sod.qty\_ordered,0) - NVL(sod.qty\_invoiced,0)) \* NVL(sod.unit\_price,0)),4) \* 100
when NVL(sod.unit\_cost,0) = 0 and NVL(sod.est\_cost,0) = 0 and NVL(cqd.unit\_cost,0) \<> 0 then ROUND(((NVL(sod.unit\_price,0) - NVL(cqd.unit\_cost,0)) \* (NVL(sod.qty\_ordered,0) - NVL(sod.qty\_invoiced,0)))/((NVL(sod.qty\_ordered,0) - NVL(sod.qty\_invoiced,0)) \* NVL(sod.unit\_price,0)),4) \* 100
-- ROUND(((NVL(sod.unit\_price,0) - decode(NVL(sod.unit\_cost,0),0,NVL(cqd.unit\_cost,0),NVL(sod.unit\_cost,0))) \* NVL(sod.qty\_ordered,0))/(NVL(sod.qty\_ordered,0) \* NVL(sod.unit\_price,0)),4) \* 100
else 0
end "GP%"
,case when sod.priority = 0 then 'On Time'
when sod.priority = 1 then 'Late'
when sod.priority = 2 then 'AOG'
end "Category"
/\*
, case when (NVL(pnm.qty\_available,0) + NVL((Select sum(qty\_reserved) from stock\_reservations where sod\_auto\_key = sod.sod\_auto\_key),0)) \< NVL(sod.qty\_ordered,0) - NVL(sod.qty\_invoiced,0) then 'Low Stock'
when (NVL(pnm.qty\_available,0) + NVL((Select sum(qty\_reserved) from stock\_reservations where sod\_auto\_key = sod.sod\_auto\_key),0)) >= NVL(sod.qty\_ordered,0) - NVL(sod.qty\_invoiced,0) then 'On Hand'
end "Stock"
\*/
,sms.status\_code "Stock"
,smh.sm\_number
**,uda.auto\_key**
**,uda.BDG\_Notes**
**,uda.uda\_auto\_key**
**,uda1.auto\_key**
**,uda1.GSG\_Notes**
**,uda1.uda\_auto\_key**
**,uda2.auto\_key**
**,uda2.Production\_Notes**
**,uda2.uda\_auto\_key**
**,uda3.auto\_key**
**,uda3.QA\_Notes**
**,uda3.uda\_auto\_key**
**,uda4.auto\_key**
**,uda4.Controller\_Notes**
**,uda4.uda\_auto\_key**
**,uda5.auto\_key**
**,uda5.Low\_Stock\_PN**
**,uda5.uda\_auto\_key**
**,uda6.auto\_key**
**,uda6.Vendor\_Tied\_Low\_Stock**
**,uda6.uda\_auto\_key**
**,uda7.auto\_key**
**,uda7.Low\_Stock\_Due\_Date**
**,uda7.uda\_auto\_key**
**,uda8.auto\_key**
**,uda8.Low\_Stock\_Tracking**
**,uda8.uda\_auto\_key**
From so_header soh, so_detail sod, parts_master pnm, companies cmp, cq_detail cqd, sm_header smh, sm_detail smd, sm_status sms,
(select auto_key,attribute_value BDG_Notes, uda_auto_key from uda_checked where uda_auto_key = 97 ) uda,
(select auto_key,attribute_value GSG_Notes , uda_auto_key from uda_checked where uda_auto_key = 98 ) uda1 ,
(select auto_key,attribute_value Production_Notes , uda_auto_key from uda_checked where uda_auto_key = 99 ) uda2,
(select auto_key,attribute_value QA_Notes , uda_auto_key from uda_checked where uda_auto_key = 100 ) uda3,
(select auto_key,attribute_value Controller_Notes , uda_auto_key from uda_checked where uda_auto_key =101 ) uda4,
(select auto_key,attribute_value Low_Stock_PN , uda_auto_key from uda_checked where uda_auto_key =109 ) uda5,
(select auto_key,attribute_value Vendor_Tied_Low_Stock , uda_auto_key from uda_checked where uda_auto_key =112 ) uda6,
(select auto_key,attribute_value Low_Stock_Due_Date , uda_auto_key from uda_checked where uda_auto_key =113 ) uda7,
(select auto_key,attribute_value Low_Stock_Tracking , uda_auto_key from uda_checked where uda_auto_key =114 ) uda8
where soh.soh_auto_key = sod.soh_auto_key
and soh.cmp_auto_key = cmp.cmp_auto_key
and sod.pnm_auto_key = pnm.pnm_auto_key
and sod.cqd_auto_key = cqd.cqd_auto_key(+)
and sod.sod_auto_key = smd.sod_auto_key(+)
and smd.smh_auto_key = smh.smh_auto_key(+)
and smh.sms_auto_key = sms.sms_auto_key(+)
and soh.open_flag = 'T'
and NVL(sod.qty_ordered,0) > NVL(sod.qty_invoiced,0)
and sod.route_code not in ('X','M','F')
and uda.auto_key (+)=sod.sod_auto_key
and uda1.auto_key (+)=sod.sod_auto_key
and uda2.auto_key (+)=sod.sod_auto_key
and uda3.auto_key (+)=sod.sod_auto_key
and uda4.auto_key (+)=sod.sod_auto_key
and uda5.auto_key (+)=sod.sod_auto_key
and uda6.auto_key (+)=sod.sod_auto_key
and uda7.auto_key (+)=sod.sod_auto_key
and uda8.auto_key (+)=sod.sod_auto_key
This works in Crystal Reports and when I copied it over to Tableau that's when I get the error. The text in bold is what I added for the attribute fields in our ERP system.
Any help is appreciated.