SQL View–Sales Document Holds
The following view shows all Sales Documents with active or inactive holds.
This view is also a modification of the spvSalesDocumentHolds view from Salespad (www.salespad.net).
You can name the view whatever you want by change the [tspvSalesDocumentHolds] below. Also the USE [TSP] tells the script to only execute on my database named TSP. You will need to modify the [TSP] to be whatever your database name is.
USE [TSP]
GO
/****** Object: View [dbo].[tspvSalesDocumentHold] Script Date: 04/28/2011 14:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[tspvSalesDocumentHold]
as
select
[Sales_Doc_Type]=CASE SOPTYPE when 1 then ‘QUOTE’ when 2 then ‘ORDER’ when 3 then ‘INVOICE’ when 4 then ‘RETURN’ when 5 then ‘BACKORDER’ end,
[Sales_Doc_Num]=SOPNUMBE,
[Hold_Code]=SOP10104.PRCHLDID,
[Is_Deleted]=cast(DELETE1 as bit),
[User_ID]=USERID,
[Last_Update_On]=cast(convert(char(11), HOLDDATE, 1) + convert(varchar, TIME1, 14) as smalldatetime),
[Cant_Transfer]=cast(XFERPHOL as bit),
[Cant_Post]=cast(POSTPHOL as bit),
[Cant_Fulfill]=cast(FUFIPHOL as bit),
[Cant_Print]=cast(PRINPHOL as bit)
from SOP10104 (nolock) join SOP00100 as h (nolock) on h.PRCHLDID = SOP10104.PRCHLDID
Trackbacks & Pingbacks