I typically wind up using SQL Views, SmartList Builder and now SmartList Designer to create custom built SmartLists for these modules. In this post, I have included a SQL Script, which can be used to create a SQL View as the foundation for SmartLists or SQL Server Reporting Services (SSRS) Reports for Engineering Change Requests. Here it is:
Create VIEW
Engineering_Change_Request
AS
select
DATEENTERED_I Date_of_Request,
CASE WHEN CUST.CUSTNMBR is not null then CUST.CUSTNAME
Else ''
End Customer_Name,
CASE WHEN CUST.CUSTNMBR is not null then CUST.CUSTNMBR
ELSE ''
End Customer_ID,
ECMH.ENDDATE
ECR_Complete_by_Date,
ECMH.ITEMNMBR
Part_Number,
ECMH.ITEMDESC
Part_Description,
'' Old_Rev,
ECMH.REVISIONLEVEL_I
New_Rev,
ECMH.ECM_Short_Description
ECR_Description,
DoEC.text1
BOM_Changes,
RFC.text2
Reason_for_Change,
NCO.text3
Notify_Customer,
EI.text4
Expected_Impact,
ECML.DISPOSITIONNOTES_I
WIP_Instructions
from EC010031 ECMH
inner join EC050031 ECML on ECMH.ECNumber = ECML.ECNumber
left join RM00101 CUST on ECMH.CUSTNMBR = CUST.CUSTNMBR
left join EC010100 DoEC on ECMH.ECNumber = DoEC.ECNumber
left join EC010200 RFC on ECMH.ECNumber = RFC.ECNumber
left join EC010300 NCO on ECMH.ECNumber = NCO.ECNumber
left join EC010400 EI on ECMH.ECNumber = EI.ECNumber
GO
Grant Select on Engineering_Change_Request to DYNGRP