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