CREATE VIEW [VIEW1] AS
SELECT OLN.ORD_NUM AS 'ORD_NUM',
    ORD.CTM_NBR AS 'CTM_NBR'
FROM PROOLN_M AS OLN
LEFT JOIN PROORD_M AS ORD ON OLN.ORD_NUM=ORD.ORD_NUM
WHERE OLN.ITM_NUM='BPOFE'

CREATE VIEW [V2] AS
SELECT V1.CTM_NBR AS 'CTM_NBR',
    ((ADR.ATN_1ST)+(ADR.ATN_MID)+(ADR.ATN_END)) AS 'ATN_NME',
    ADR.CMP_NME AS 'CMP_NME'
FROM [VIEW1] AS V1
LEFT JOIN CDSADR_M AS ADR ON V1.CTM_NBR=ADR.CTM_NBR
WHERE ADR.ADR_FLG='0'

SELECT DISTINCT V2.CTM_NBR AS 'Customer Number',
    V2.ATN_NME AS 'Name',
    V2.CMP_NME AS 'Company Name',
    EML.ADR_EML AS 'Email Address',
    EML.PMO_EML AS 'Promote by Email'
FROM V2
LEFT JOIN CDSEML_M AS EML ON V2.CTM_NBR=EML.CTM_NBR
WHERE EML.PMO_EML='Y'