Report Designer Stored Procedure spcpPurchaseOrderReport

Example spcpPurchaseOrderReport

A software product created by SalesPad Solutions, LLC

Copyright 2004-2011 www.cavallo.com

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

cSalesDocument level required items are cPurchaseOrder.PO_Type & cPurchaseOrder.PO_Number

cSalesLineItem level required items are cPurchaseLineItems.PO_Type , cPurchaseLineItems.PO_Number , cPurchaseLineItems.Line_Seq

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[spcpPurchaseOrderReport]
@potype as varchar(30) = null,
@ponumber as varchar(50) = null
as
--exec spcpPurchaseOrderReport @potype='STANDARD', @ponumber='PO2058'

--// cSalesDocument

select PO_Type
, PO_Number
, zVendorAddr1 =
RTRIM(Vendor_Name) + CHAR (010) + CHAR(013) +
Case
When Vendor_Address_Line_2 = '' and Vendor_Address_Line_3 = '' Then RTRIM(Vendor_Address_Line_1) +CHAR (010) + CHAR(013)
when Vendor_Address_Line_2 <> '' and Vendor_Address_Line_3 = '' then RTRIM(Vendor_Address_Line_1) +char (010) + char(013) + RTRIM(Vendor_Address_Line_2) + CHAR (010) + CHAR(013)
when Vendor_Address_Line_2 <> '' and Vendor_Address_Line_3 <> '' then RTRIM(Vendor_Address_Line_1)+ char (010) + char(013) + RTRIM(Vendor_Address_Line_2)
+ char (010) + char(013) + RTRIM(Vendor_Address_Line_3) +CHAR (010) + CHAR(013)
when Vendor_Address_Line_2 = '' and Vendor_Address_Line_3 <> '' then RTRIM(Vendor_Address_Line_1) +char (010) + char(013) + RTRIM(Vendor_Address_Line_3) + CHAR (010) + CHAR(013)
else Vendor_Address_Line_1 + CHAR (010) + CHAR(013)
end
+ RTRIM(Vendor_City) + CHAR(44) + CHAR(032) + RTRIM(Vendor_State) + CHAR
(032) + RTRIM(Vendor_Zip) +CHAR (010) + CHAR(013)
+ CHAR(80) + CHAR(58) + RTRIM(Vendor_Phone_1)
+ CHAR (010) + CHAR(013) + CHAR(70) + CHAR(58) + RTRIM(Vendor_Fax)
,zVendorAccount = pm.ACNMVNDR
from spvPurchaseOrder as cPurchaseOrder with (nolock)
left join PM00200 as pm on pm.VENDORID = cPurchaseOrder.Vendor_ID where cPurchaseOrder.PO_Type=@potype and cPurchaseOrder.PO_Number=@ponumber

--// cSalesLineItem

select cPurchaseLineItems.PO_Type
, cPurchaseLineItems.PO_Number
, cPurchaseLineItems.Line_Seq
, zBinLocation=iloc.Default_Bin
, zItemOnHandQty=iloc.Onhand
, zListPrice = iv.LISTPRCE
from dbo.spvPurchaseLineItem as cPurchaseLineItems
left join spvInventoryByLoc as iloc on iloc.Item_Number=cPurchaseLineItems
.Item_Number and iloc.Location=cPurchaseLineItems.Location
left join IV00105 as iv with (nolock) on iv.ITEMNMBR = cPurchaseLineItems.Item_Number
where cPurchaseLineItems.PO_Type=@potype and cPurchaseLineItems.PO_Number=@ponumber SET ANSI_NULLS OFF
GO