Yellow Banner SQL Script Salespad.net

This is a modified version of spvCustomer_base from salespad.net that will display a yellow banner message


USE [MCHN]
GO

/****** Object: View [dbo].[spvCustomer_base] Script Date: 08/01/2016 14:12:44 ******/
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(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)
,[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([CPRCSTNM])>0) and (Len(x.[xDealerinfo])>0)
then
'Dealer Info: ' + x.[xDealerinfo] + '' + ' and the parent account is ' + [CPRCSTNM]

when
(Len([CPRCSTNM])>0) and (Len(x.[xDealer])=0)
then
'The parent account is ' + [CPRCSTNM]

when
(Len([CPRCSTNM])=0) and (Len(x.[xDealerinfo])>0)
then
'Dealer Info: ' + x.[xDealerinfo]
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
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 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
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

here are some other changes we have made

[Message] = 
		case 
		when 
			(Len(x.[xDealer])>0) 
		then 
			'This customer is assigned to ' + x.[xDealer]
		else 
			''
		end,

Leave a Reply