SQL View – Customers

The following view gives you customer information from the RM00101 and RM00103 (A/R table) tables.  It also includes any Notes for the customer. 

*Thanks to Rockfield who gave me the idea of adding the Notes field.

This view is also a modification of the spvCustomer view from Salespad (www.salespad.net).

You can name the view whatever you want by change the [tspvCustomer] 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].[tspvCustomer]    Script Date: 06/23/2010 16:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[tspvCustomer]
AS
select
    [Customer_Num]=RM00101.CUSTNMBR,
    [Customer_Name]=CUSTNAME,
    [Customer_Class]=CUSTCLAS,
    [Corporate_Customer_Num]=CAST(CPRCSTNM AS VARCHAR(25)),
    [Short_Name]=SHRTNAME,
    [Statement_Name]=STMTNAME,
    [Primary_Addr_Code]=ADRSCODE,
    [Primary_Bill_To_Addr_Code]=PRBTADCD,
    [Primary_Ship_To_Addr_Code]=PRSTADCD,
    [Statement_To_Addr_Code]=STADDRCD,
    [Sales_Person_ID]=SLPRSNID,
    [Sales_Territory]=SALSTERR,
    [Payment_Terms]=upper(PYMTRMID),
    [Shipping_Method]=SHIPMTHD,
    [Tax_Schedule]=taxschid,
    [Address1]=address1,
    [Address2]=address2,
    [Address3]=address3,
    [City]=city,
    [State]=state,
    [Zip]=zip,
    [Phone1]=phone1,
    [Phone2]=phone2,
    [Phone3]=phone3,
    [Fax]=fax,
    [Price_Level]= isnull(case when PRCLEVEL!=” then PRCLEVEL else (select PRCLEVEL from RM40101 where SETUPKEY=1) end,”),
    [User_Def_1]=USERDEF1,
    [User_Def_2]=USERDEF2,
    [Tax_Exempt_1] = TAXEXMT1,
    [Tax_Exempt_2] = TAXEXMT2,
    [Tax_Registration_Num] = TXRGNNUM,
    [Comment_1] = COMMENT1,
    [Comment_2] = COMMENT2,
    [IntegrationSource] = INTEGRATIONSOURCE,
    [Inactive] = cast(INACTIVE as bit),
    [On_Hold]=cast(HOLD as bit),
    [Customer_Note]=ISNULL(TXTFIELD, ”),
    [Currency_ID]= isnull(isnull(custCurr.CURNCYID, funcCurr.CURNCYID),”),
    [Currency_Dec]= isnull(isnull(custCurr.DECPLCUR, funcCurr.DECPLCUR),3) – 1,
    [Credit_Limit_Type]=case when crlmttyp=’0′ then ‘No Credit’ when crlmttyp=’1′ then ‘Unlimited’ when crlmttyp=’2′ then ‘Amount’ end,
    [Credit_Limit_Amnt]=crlmtamt,
    Last_Aged=LASTAGED,
    Balance=CUSTBLNC,
    Unapplied_Amount = (select sum(curtrxam) from RM20101 as ar (nolock) where ar.CUSTNMBR=RM00101.CUSTNMBR and RMDTYPAL=9 and VOIDSTTS=0 and curtrxam>0),
    Customer_Credit_Limit=CRLMTAMT,
    Last_Pay_Date=LASTPYDT,
    Last_Pay_Amt=LPYMTAMT,
    Last_Invoice_Date=LSTTRXDT,
    Last_Invoice_Amt=LSTTRXAM, 
    Last_Stmt_Date=LASTSTDT,
    Last_Stmt_Amt=LSTSTAMT,
    Life_Avg_Days=AVDTPLIF,
    Year_Avg_Days=AVGDTPYR,
    [Ship_Complete] = Cast(SHIPCOMPLETE as bit),
    USERDEF1, USERDEF2,
    [Created_Date]=creatddt,
    [Modified_Date]=modifdt,
    [Notes]=TXTFIELD
from RM00101 (nolock)
    left join SY03900 (nolock) on SY03900.NOTEINDX=RM00101.NOTEINDX
    left join RM00103 as cs (nolock) on cs.CUSTNMBR=RM00101.CUSTNMBR
    left join DYNAMICS..MC40200 as custCurr (nolock) on custCurr.CURNCYID=RM00101.CURNCYID
    left join MC40000 as currSetup (nolock) on 1=1
    left join DYNAMICS..MC40200 as funcCurr (nolock) on funcCurr.CURNCYID=currSetup.FUNLCURR