Overview

Users may use SQL to add a customized banner to the Customer Card to alert the user of a particular characteristic of the customer, defined in a user defined field. For example, the banner can be used to indicate the customer’s discount percentage, or to indicate that a customer is assigned to a particular dealer. A yellow banner will appear across the top of a Customer Card if the user field called on in the SQL script is populated:

Note: Completing a database update will clear the customizations made to the views. You must reapply changes to the views after any SalesPad database updates.

Use

You will need to modify the (spvCustomer_base) and change the line ( [Message] = '', ). As an example, we are going to look at a customer and see if they have a discount set up in the customer user field. So our spvCustomer_base has been changed to this:

,[Message] = CASE
WHEN (Len(x.[xCustomerDiscount]) > 0)
THEN 'This customer discount is ' + x.[xCustomerDiscount]
ELSE ''
END

We then also had to join to the spxCustomer table to pull this field, so we added this line:

left join spxCustomer as x (nolock) on x.Customer_Num=RM00101.CUSTNMBR

When added to the view from v.4.6.3.4:

USE [TWO]
GO
/****** Object: View [dbo].[spvCustomer_base] Script Date: 9/20/2018 3:57:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* View -spvCustomer_base */
ALTER view [dbo].[spvCustomer_base]
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] = UPPER(RM00101.ADRSCODE)
,[Primary_Bill_To_Addr_Code] = UPPER(PRBTADCD)
,[Primary_Ship_To_Addr_Code] = UPPER(PRSTADCD)
,[Statement_To_Addr_Code] = UPPER(STADDRCD)
,[Sales_Person_ID] = SLPRSNID
,[Sales_Territory] = SALSTERR
,[Payment_Terms] = upper(PYMTRMID)
,[Shipping_Method] = SHIPMTHD
,[Price_Level] = isnull(PRCLEVEL, '')
,[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)
,[Note] = ISNULL(TXTFIELD, '')
,[Currency_ID] = isnull(isnull(custCurr.CURNCYID, funcCurr.CURNCYID), '')
,[Currency_Dec] = isnull(isnull(custCurr.DECPLCUR, funcCurr.DECPLCUR), 3) -1
,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
,Credit_Limit_Type = CRLMTTYP
,Finance_Charge_Type = FNCHATYP
,Finance_Charge_Amt = FINCHDLR
,Finance_Charge_Pct = FNCHPCNT
,Min_Pmt_Type = MINPYTYP
,Min_Pmt_Amt = MINPYDLR
,Min_Pmt_Pct = MINPYPCT
,Balance_Type = BALNCTYP
,Max_Writeoff_Type = MXWOFTYP
,Max_Writeoff_Amt = MXWROFAM
,Last_Pay_Date = LASTPYDT
,Last_Pay_Amt = LPYMTAMT
,First_Invoice_Date = cs.FRSTINDT
,Last_Invoice_Date = LSTTRXDT
,Last_Invoice_Amt = LSTTRXAM
,Last_Stmt_Date = LASTSTDT
,Last_Stmt_Amt = LSTSTAMT
,Life_Avg_Days = AVDTPLIF
,Year_Avg_Days = AVGDTPYR
,Total_Amt_NSF_Checks_YTD = cast(TNSFCYTD AS NUMERIC(19, 5))
,Num_NSF_Checks_YTD = cast(NONSFYTD AS INT)
,[Tax_Schedule] = TAXSCHID
,[Ship_Complete] = Cast(SHIPCOMPLETE AS BIT)
,[Stmt_Email_To] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 1)
,[Stmt_Email_CC] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 2)
,[Stmt_Email_BCC] = dbo.spfGetCustomerEmail([RM00101].CUSTNMBR, 3)
,[Email_To] = email.EmailToAddress
,[Email_CC] = email.EmailCcAddress
,[Email_BCC] = email.EmailBccAddress
,[Message] = CASE
WHEN (Len(x.[xCustomerDiscount]) > 0)
THEN 'This customer discount is ' + x.[xCustomerDiscount]
ELSE ''
END
,--Used for FB 2388 ORD011021 -R Jorgenson Banner Custom
USERDEF1
,USERDEF2
,[Trade_Discount] = (cast(CUSTDISC AS NUMERIC(19, 5)) / 100)
,[Master_Distributor] = ISNULL(c.[Master_Distributor], '')
,[Method_Of_Billing] = ISNULL(c.[Method_Of_Billing], 0)
,[Send_Email_Statements] = ISNULL(Send_Email_Statements, 0)
,[Created_On] = [RM00101].CREATDDT
,[Changed_On] = [RM00101].MODIFDT
,[Promotions_Applied_Customer]
,[DEX_ROW_TS] = [RM00101].DEX_ROW_TS
,[On_Order_Amount] = cs.ONORDAMT
,[Language_ID] = USERLANG
,[Combine_Split_Docs] = c.Combine_Split_Docs
FROM RM00101(NOLOCK)
LEFT JOIN spCustomer(NOLOCK) AS c ON c.[Customer_Num] = RM00101.CUSTNMBR
LEFT JOIN SY03900(NOLOCK) ON SY03900.NOTEINDX = RM00101.NOTEINDX
LEFT JOIN RM00103 AS cs(NOLOCK) ON cs.CUSTNMBR = RM00101.CUSTNMBR
LEFT JOIN DYNAMICS16..MC40200 AS custCurr(NOLOCK) ON custCurr.CURNCYID = RM00101.CURNCYID
LEFT JOIN MC40000 AS currSetup(NOLOCK) ON 1 = 1
LEFT JOIN DYNAMICS16..MC40200 AS funcCurr(NOLOCK) ON funcCurr.CURNCYID = currSetup.FUNLCURR
LEFT JOIN SY01200 AS email(NOLOCK) ON RM00101.CUSTNMBR = email.Master_ID
AND email.ADRSCODE = RM00101.ADRSCODE
AND email.Master_Type = 'CUS'
LEFT JOIN spxCustomer AS x(NOLOCK) ON x.Customer_Num = RM00101.CUSTNMBR

GO

After updating the view, when we log in and open a Customer Card that has a customer discount applied to it, the custom banner will appear: