SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[dnn_Smith_SA_ListExportOrders]

@PortalId int

AS

select distinct o.orderid,o.orderdate,o.status,convert(varchar,o.taxtotal) as taxtotal,o.shipmethod,o.shipdate,o.trackingnumber,

convert(varchar,o.shippingtotal) as shippingtotal,convert(varchar,o.handlingcharge) as handlingcharge,

convert(varchar,o.discount) as discount, o.affiliateid,o.specialinstructions,

c.customerid,c.firstname,c.lastname,c.email,c.Address1,c.address2,c.City,c.state,c.country,

c.zip,c.homephone,c.workphone,c.companyname,

min(od.productid) as productid, min(od.productsku) as productsku, min(od.ProductName) as productname,

ph.billingaddress,ph.billingzip,ph.acctno,ph.exproute,convert(varchar,ph.amount) as amount,ph.transid,

ph.payhistid,ph.authcode,ph.notes,ph.status as paystatus,ph.billingname,ph.billingcity,ph.billingstate,ph.billingcountry

from dnn_smith_storeorders o

inner join dnn_smith_customer c on

o.customerid = c.customerid

left outer join dnn_smith_storeorderdetails od on

o.orderid = od.orderid

inner join dnn_smith_payhist ph on

o.payhistid = ph.payhistid

where o.portalid = @PortalId

group by c.customerid,c.firstname,c.lastname,c.email,c.Address1,c.address2,c.City,c.state,c.country,

c.zip,c.homephone,c.workphone,c.companyname,o.orderid,o.orderdate,o.status,o.taxtotal,o.shipmethod,o.shipdate,o.trackingnumber,o.specialinstructions,

o.shippingtotal,o.handlingcharge,o.discount,o.affiliateid,ph.billingaddress,ph.billingzip,ph.acctno,ph.exproute,ph.amount,ph.transid,

ph.payhistid,ph.authcode,ph.notes,ph.status,ph.billingname,ph.billingcity,ph.billingstate,ph.billingcountry

order by o.orderid desc

Make sure that if your using an object quilifier you change it before running this stored procedure!

Thanks,

Christian