Yes you're right Lynn so I have posted it below, thanks - I have already stripped out the functions for temp tables. I would appreciate you taking a peek. 🙂
SET STATISTICS IO ON
SET STATISTICS TIME ON
SET DATEFORMAT dmy;
GO
DECLARE
@paramCompanyvarchar(3),
@paramSalesCategoryvarchar(max),
@paramSalesStatusvarchar(100),
@paramSitevarchar(max),
@paramVehicleTypevarchar(max),
@paramActShipDateFromdatetime,
@paramActShipDateTodatetime
SET @paramCompany='abb'
SET @paramSalesCategory='All' --,001,002,003,004,005,006,100,200,250,300,350,Classical,Ebay,Ebay SM,Landscape,OrdrSelect,Paver,Stonemarkt,StoneShip'
SET @paramSalesStatus='99' --,Open order,Delivered'
SET @paramSite='All' --,ABR,ALC,BBD,BFT,BLD,CAN,CAR,CHE,EAG,FAL,FEB,FLX,GEE,GGR,GMS,HAM,HDV,HEA,HIP,HLI,HLL,HPO,LLY,LSH,MAN,MLT,MRL,MSQ,MSS,MSU,NPT,NSH,NWL,PEN,PON,PTR,RBT,REG,RYT,SAN,SAW,SHN,SIT,SND,STN,STV,THL,WAF,WLA,ZZZ'
SET @paramVehicleType='All' --@VehicleType
SET @paramActShipDateFrom='01/01/2013' --@ActShipDateFrom
SET @paramActShipDateTo='21/03/2013' --@ActShipDateTo
--Create SalesCategory temp table variable
DECLARE @SalesCategoryTable TABLE
(
SalesCategory VARCHAR(100)
)
INSERT INTO @SalesCategoryTable (SalesCategory)
SELECT item FROM dbo.udf_MultiValueParameterHandlingString(@paramSalesCategory)
--Create SalesStatus temp table variable
DECLARE @SalesStatusTable TABLE
(
SalesStatus VARCHAR(100)
)
INSERT INTO @SalesStatusTable (SalesStatus)
SELECT item FROM dbo.udf_MultiValueParameterHandlingString(@paramSalesStatus)
--Create Site temp table variable
DECLARE @SiteTable TABLE
(
[SITE] VARCHAR(100)
)
INSERT INTO @SiteTable ([SITE])
SELECT item FROM dbo.udf_MultiValueParameterHandlingString(@paramSite)
--Create VehicleType temp table variable
DECLARE @VehicleTypeTable TABLE
(
VehicleType VARCHAR(100)
)
INSERT INTO @VehicleTypeTable (VehicleType)
SELECT item FROM dbo.udf_MultiValueParameterHandlingString(@paramVehicleType)
SELECT
st.SALESID as 'SalesOrder',
st.MARSALESCATEGORY as 'SalesCategory',
st.MARVEHICLETYPE as 'VehicleType',
st.MARSPECINST1 as 'SpecialInstructions1',
st.MARSPECINST2 as 'SpecialInstructions2',
st.SHIPPINGDATEREQUESTED as 'ExpShipDate',
sohead.ShippedDate as 'ActShipDate',
st.RECEIPTDATEREQUESTED as 'DelDate',
st.CUSTACCOUNT as 'CustomerId',
st.SALESNAME as 'CustomerName',
st.DELIVERYNAME as 'DeliveryName',
st.DELIVERYADDRESS as 'DeliveryAddress',
st.DELIVERYZIPCODE as 'PostCode',
ctt.Telephone,
CASE WHEN jz.ItemId is null THEN 'No Delivery Item' ELSE jz.ItemId END as 'ItemId',
CASE WHEN jz.ItemDescription is null THEN 'No Delivery Item' ELSE jz.ItemDescription END as 'ItemDescription',
jz.[Site],
jz.Warehouse,
ABS(isnull(jz.NoOfPallets,0)) as 'NoOfPallets',
ABS(isnull(jz.NoOfQtrPallets,0)) as 'NoOfQtrPallets',
'' as 'PalletsPicked',
'' as 'QtrPalletsPicked',
ABS(isnull(jz.Cost,0)) as 'Cost',
'' as 'CostPayable',
'' as 'Carrier',
'' as 'Notes',
CASEWHEN st.SALESTYPE = 4 THEN 'Return Order'
WHEN (isnull(sline.MinSalesQty,0) < 0 or isnull(sline.MinSalesQty,0) < 0) THEN 'Return Order'
ELSE 'SalesOrder' END as 'SalesType',
CASEWHEN st.SALESTYPE = 4 THEN 'COLLECT'
WHEN (isnull(sline.MinSalesQty,0) < 0 or isnull(sline.MinSalesQty,0) < 0) THEN 'COLLECT'
ELSE 'DELIVER' END as 'Del/Col'
FROM
DynamicsV5Realtime.dbo.SALESTABLE st
INNER JOIN
(SELECT distinct
tr.TRANSREFID as 'SalesId',
tr.DATAAREAID,
tr.DATEPHYSICAL as 'ShippedDate'
FROM
DynamicsV5Realtime.dbo.INVENTTRANS tr
WHERE
tr.DATEPHYSICAL between @paramActShipDateFrom and @paramActShipDateTo
and tr.DATAAREAID = @paramCompany) sohead
ON
sohead.SalesId = st.SALESID
and sohead.DATAAREAID = st.DATAAREAID
INNER JOIN
(SELECT
sl.SALESID as 'SalesId',
sl.DATAAREAID,
MIN(sl.SALESQTY) as 'MinSalesQty'
FROM
DynamicsV5Realtime.dbo.SALESLINE sl
WHERE
sl.DATAAREAID = @paramCompany
and (sl.SALESSTATUS IN(select SalesStatus from @SalesStatusTable) or @paramSalesStatus = '99')
and sl.SALESSTATUS IN(2,3)
GROUP BY
sl.SALESID,
sl.DATAAREAID ) sline
ON
sline.SalesId = st.SALESID
and sline.DATAAREAID = st.DATAAREAID
LEFT OUTER JOIN
(SELECT
sl.DATAAREAID,
sl.SALESID as 'SalesId',
sl.ITEMID as 'ItemId',
sl.NAME as 'ItemDescription',
id.INVENTSITEID as 'Site',
id.INVENTLOCATIONID as 'Warehouse',
CASE WHEN it.MARITEMFIXINGCODE = '001' THEN ABS(sum(tr.QTY)) ELSE 0 END as 'NoOfPallets',
CASE WHEN it.MARITEMFIXINGCODE = '002' THEN ABS(sum(tr.QTY)) ELSE 0 END as 'NoOfQtrPallets',
ABS(Sum(tr.QTY * itm.PRICE / CASE WHEN itm.PRICEUNIT = 0 THEN 1 ELSE itm.PRICEUNIT END)) as 'Cost',
tr.DATEPHYSICAL as 'ShippedDate'
FROM
DynamicsV5Realtime.dbo.SALESLINE sl
INNER JOIN
DynamicsV5Realtime.dbo.SALESTABLE st
ON
st.SALESID = sl.SALESID
and st.DATAAREAID = sl.DATAAREAID
INNER JOIN
DynamicsV5Realtime.dbo.INVENTTABLE it
ON
it.ITEMID = sl.ITEMID
and it.DATAAREAID = sl.DATAAREAID
INNER JOIN
DynamicsV5Realtime.dbo.INVENTTABLEMODULE itm
ON
itm.ITEMID = sl.ITEMID
and itm.DATAAREAID = sl.DATAAREAID
and itm.MODULETYPE = 1 -- purchase
LEFT OUTER JOIN
DynamicsV5Realtime.dbo.INVENTDIM id
ON
id.INVENTDIMID = sl.INVENTDIMID
and id.DATAAREAID = sl.DATAAREAID
LEFT OUTER JOIN
DynamicsV5Realtime.dbo.INVENTTRANS tr
ON
tr.INVENTTRANSID = sl.INVENTTRANSID
and tr.DATAAREAID = sl.DATAAREAID
WHERE
sl.DATAAREAID = @paramCompany
and sl.ITEMID like 'JZ%'
and st.SALESTYPE in(3,4)
and (st.SALESSTATUS IN(select SalesStatus from @SalesStatusTable) or @paramSalesStatus = '99')
and st.SALESSTATUS IN(2,3)
and (sl.SALESSTATUS IN(select SalesStatus from @SalesStatusTable) or @paramSalesStatus = '99')
and sl.SALESSTATUS IN(2,3)
and (st.MARSALESCATEGORY IN(select SalesCategory from @SalesCategoryTable) or @paramSalesCategory = 'All')
and (id.INVENTSITEID IN(select [SITE] from @SiteTable) or @paramSite = 'All')
and (tr.DATEPHYSICAL between @paramActShipDateFrom and @paramActShipDateTo)
GROUP BY
sl.DATAAREAID,
sl.SALESID,
sl.ITEMID,
sl.NAME,
id.INVENTSITEID,
id.INVENTLOCATIONID,
it.MARITEMFIXINGCODE,
tr.QTY,
tr.DATEPHYSICAL) jz
ON
jz.DATAAREAID = st.DATAAREAID
and jz.SalesId = st.SALESID
and jz.ShippedDate = sohead.ShippedDate
INNER JOIN
(SELECT
ct.ACCOUNTNUM,
ct.DATAAREAID,
CASE WHEN ct.PHONE = '' THEN '' ELSE 'Tel: ' + ct.PHONE + ' ' END
+
CASE WHEN ct.CELLULARPHONE = '' THEN '' ELSE 'Mob: ' + ct.CELLULARPHONE END as 'Telephone'
FROM
DynamicsV5Realtime.dbo.CUSTTABLE ct
WHERE
ct.DATAAREAID = @paramCompany) ctt
ON
ctt.DATAAREAID = st.DATAAREAID
and ctt.ACCOUNTNUM = st.CUSTACCOUNT
WHERE
st.DATAAREAID = @paramCompany
and st.SALESTYPE IN(3,4)
and (st.SALESSTATUS IN(select SalesStatus from @SalesStatusTable) or @paramSalesStatus = '99')
and st.SALESSTATUS IN(2,3)
and (st.MARSALESCATEGORY IN(select SalesCategory from @SalesCategoryTable) or @paramSalesCategory = 'All')
and (st.INVENTSITEID IN(select [SITE] from @SiteTable) or @paramSite = 'All')
and (st.MARVEHICLETYPE IN(select VehicleType from @VehicleTypeTable) or @paramVehicleType = 'All')
ORDER BY
3,6,1,14
SET STATISTICS IO OFF
SET STATISTICS TIME OFF