Report Designer Stored Procedure spcpSalesDocumentReport

Note: The following fields are required for proper function of the stored procedure:

cSalesDocument level required items are sd.Sales_Doc_Num & sd.Sales_Doc_Type

cSalesLineItem level required items are sd.Sales_Doc_Type , sd.Sales_Doc_Num , sli.Line_Num , sli.Component_Seq_Num

GO
/****** Object: StoredProcedure [dbo].[spcpSalesDocumentReport] Script Date: 07/16/2010 08:01:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter procedure [dbo].[spcpSalesDocumentReport]

@soptype as varchar(30) = null,

@sopnumbe as varchar(50) = null

as

-- exec spcpSalesDocumentReport 'ORDER', 'ORDST2239'
--// cSalesDocument

select Sales_Doc_Type, Sales_Doc_Num,

zShippingMethodDescription=sm.Description
,zSalesPersonDescription=sr.Sales_Person
,zSalesPersonEmail = a.INET1
,zWarehouse=wh.LOCNDSCR
,zShipAddress=
Case
When sd.address_line_2 = '' and sd.address_line_3 = '' Then rtrim(sd.address_line_1)
When sd.address_line_2 <> '' and sd.address_line_3 = '' Then rtrim(sd.address_line_1) + char (010) + char(013) + rtrim(sd.address_line_2)
When sd.address_line_2 <> '' and sd.address_line_3 <> '' Then rtrim(sd.address_line_1) + char (010) + char(013) + rtrim(sd.address_line_2) + char(010) + char(013) + rtrim(sd.address_line_3)
When sd.address_line_2 = '' and sd.address_line_3 <> '' Then rtrim(sd.address_line_1) + char (010) + char(013) + rtrim(sd.address_line_3)
Else sd.address_line_1
End
,zBillAddress=
Case
When ca.address_line_2 = '' and ca.address_line_3 = '' Then rtrim(ca.address_line_1)
When ca.address_line_2 <> '' and ca.address_line_3 = '' Then rtrim(ca.address_line_1) + char (010) + char(013) + rtrim(ca.address_line_2)
When ca.address_line_2 <> '' and ca.address_line_3 <> '' Then rtrim(ca.address_line_1) + char (010) + char(013) + rtrim(ca.address_line_2) + char(010) + char(013) + rtrim(ca.address_line_3)
When ca.address_line_2 = '' and ca.address_line_3 <> '' Then rtrim(ca.address_line_1) + char (010) + char(013) + rtrim(ca.address_line_3)
Else ca.address_line_1
End
--,SalesRep2 = sd.xSalesRep2
,zSubtotal = (select SUM((sl.Qty_Fulfilled * (sl.Unit_Price - sl.Markdown_Amount))) from spv3SalesLineItem as sl where sl.Sales_Doc_Num = @sopnumbe and sl.Sales_Doc_Type = @soptype)

from spv3SalesDocument as sd
left join SY01200 as a (nolock) on a.Master_ID = sd.Sales_Person_ID left join spvShippingMethod as sm with (nolock) on
sm.Shipping_Method=sd.Shipping_Method
left join spvSalesRep as sr with (nolock) on sr.Sales_Person_ID=sd.Sales_Person_ID
left join IV40700 as wh with (nolock) on sd.Warehouse_Code = wh.LOCNCODE left join spvCustomer as c with (nolock) on sd.customer_num = c.customer_num left join spvCustomerAddr as ca with (nolock) on sd.customer_num =
ca.customer_num and c.primary_bill_to_addr_code = ca.address_code
where Sales_Doc_Type=@soptype and Sales_Doc_Num=@sopnumbe

--// cSalesLineItem

select Sales_Doc_Type, Sales_Doc_Num, Line_Num, Component_Seq_Num

,zMarkdown = case when sl.Markdown_Amount = 0 then Round((sl.Unit_Price * (sl.Markdown_Pct / 100)/100),2) else sl.Markdown_Amount end
,zBin = iloc.Default_Bin

from spv3SalesLineItem as sl
left join spvInventoryByLoc as iloc on iloc.Item_Number=sl.Item_Number and iloc.Location=Warehouse_Code
where sl.Sales_Doc_Type = @soptype and sl.Sales_Doc_Num = @sopnumbe

-- exec spcpSalesDocumentReport 'ORDER', 'ORDST2239'

SET ANSI_NULLS OFF