CREATE VIEW [VIEW1] AS
SELECT  DISTINCT OLN.ORD_NUM,
    ORD.ORDR_DTE,
    OLN.PMO_CDE,
    ORD.CTM_NBR,
    OLN.SHP_CTM
FROM PROOLN_M AS OLN
LEFT JOIN PROORD_M AS ORD ON OLN.ORD_NUM=ORD.ORD_NUM
WHERE ITM_NUM='WTG11C'


CREATE VIEW [BILLTOCTM] AS
SELECT DISTINCT VIEW1.ORD_NUM,
    VIEW1.CTM_NBR,
    ADR.CMP_NME,
    ((ADR.ATN_1ST)+(ADR.ATN_MID)+(ADR.ATN_END)) AS 'ATN_NME',
    ADR.STR_1ST,
    ADR.STR_2ND,
    ADR.CTM_CTY,
    ADR.CTM_STE,
    ADR.ZIP_CDE,
    ADR.CUN_TYP,
    CTM.CTM_TYP,
    EML.ADR_EMAL,
    EML.PMO_EML
FROM VIEW1
LEFT JOIN CDSADR_M AS ADR ON VIEW1.CTM_NBR=ADR.CTM_NBR
LEFT OUTER JOIN CDSCTM_M AS CTM ON VIEW1.CTM_NBR=CTM.CTM_NBR
LEFT OUTER JOIN CDSEML_M AS EML ON VIEW1.CTM_NBR=EML.CTM_NBR


CREATE VIEW [SHIPTOCTM] AS
SELECT DISTINCT VIEW1.ORD_NUM,
    VIEW1.SHP_CTM,
    ADR.CMP_NME,
    ((ADR.ATN_1ST)+(ADR.ATN_MID)+(ADR.ATN_END)) AS 'ATN_NME',
    ADR.STR_1ST,
    ADR.STR_2ND,
    ADR.CTM_CTY,
    ADR.CTM_STE,
    ADR.ZIP_CDE,
    ADR.CUN_TYP,
    CTM.CTM_TYP,
    EML.ADR_EMAL,
    EML.PMO_EML
FROM VIEW1
LEFT JOIN CDSADR_M AS ADR ON VIEW1.SHP_CTM=ADR.CTM_NBR
LEFT OUTER JOIN CDSCTM_M AS CTM ON VIEW1.SHP_CTM=CTM.CTM_NBR
LEFT OUTER JOIN CDSEML_M AS EML ON VIEW1.SHP_CTM=EML.CTM_NBR


CREATE TABLE #LIST (
ORD_NUM <VARCHAR>,
ORDR_DTE <DATETIME>,
PMO_CDE <VARCHAR>,
BCTM_NBR <VARCHAR>,
BCMP_NME <VARCHAR>,
BATN_NME <VARCHAR>,
BSTR_1ST <VARCHAR>,
BSTR_2ND <VARCHAR>,
BCTM_CTY <VARCHAR>,
BCTM_STE <VARCHAR>,
BZIP_CDE <VARCHAR>,
BCUN_TYP <VARCHAR>,
BCTM_TYP <VARCHAR>,
BADR_EMAL <VARCHAR>,
BPMO_EML <VARCHAR>,
SCTM_NBR <VARCHAR>,
SCMP_NME <VARCHAR>,
SATN_NME <VARCHAR>,
SSTR_1ST <VARCHAR>,
SSTR_2ND <VARCHAR>,
SCTM_CTY <VARCHAR>,
SCTM_STE <VARCHAR>,
SZIP_CDE <VARCHAR>,
SCUN_TYPE <VARCHAR>,
SCTM_TYP <VARCHAR>,
SADR_EMAL <VARCHAR>,
SPMO_EML <VARCHAR>
)


INSERT INTO
    #LIST
SELECT DISTINCT VIEW1.ORD_NUM AS 'ORD_NUM',
    VIEW1.ORDR_DTE AS 'ORDR_DTE',
    VIEW1.PMO_CDE AS 'PMO_CDE',
    VIEW1.CTM_NBR AS 'BCTM_NBR',
    BILL.CMP_NME AS 'BCMP_NME',
    BILL.ATN_NME AS 'BATN_NME',
    BILL.STR_1ST AS 'BSTR_1ST',
    BILL.STR_2ND AS 'BSTR_2ND',
    BILL.CTM_CTY AS 'BCTM_CTY',
    BILL.CTM_STE AS 'BCTM_STE',
    BILL.ZIP_CDE AS 'BZIP_CDE',
    BILL.CUN_TYP AS 'BCUN_TYP',
    BILL.CTM_TYP AS 'BCTM_TYP',
    BILL.ADR_EMAL AS 'BADR_EMAL',
    BILL.PMO_EML AS 'BPMO_EML',
    VIEW1.SHP_CTM AS 'SCTM_NBR',
    SHIP.CMP_NME AS 'SCMP_NME',
    SHIP.ATN_NME AS 'SATN_NME',
    SHIP.STR_1ST AS 'SSTR_1ST',
    SHIP.STR_2ND AS 'SSTR_2ND',
    SHIP.CTM_CTY AS 'SCTM_CTY',
    SHIP.CTM_STE AS 'SCTM_STE',
    SHIP.ZIP_CDE AS 'SZIP_CDE',
    SHIP.CUN_TYP AS 'SCUN_TYPE',
    SHIP.CTM_TYP AS 'SCTM_TYP',
    SHIP.ADR_EMAL AS 'SADR_EMAL',
    SHIP.PMO_EML AS 'SPMO_EML'
FROM VIEW1
LEFT JOIN BILLTOCTM AS BILL ON VIEW1.ORD_NUM=BILL.ORD_NUM
    AND VIEW1.CTM_NBR=BILL.CTM_NBR
LEFT OUTER JOIN SHIPTOCTM AS SHIP ON VIEW1.ORD_NUM=SHIP.ORD_NUM
    AND VIEW1.SHP_CTM=SHIP.SHP_CTM
ORDER BY VIEW1.ORD_NUM