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
Trackbacks & Pingbacks