CREATE OR REPLACE FORCE VIEW V8_MAILING_CUSTOMERS
AS 
SELECT
  xcms.xcms_id,
  decode(xcms.xcms_type, 1, xcms.xcms_name1, 2, '') AS CompanyName1,
  xcil.xcil_primary,
  xcid_individual.XCID_DEPARTMENT,
  xcid_individual.XCID_POSITION,
  xcid_individual.XCID_TITLE,
  xcid_individual.XCID_SEX,
  xadr.xadr_zip zip,
  xadr.xadr_city city,
  xadr.xadr_street1 street1,
  xadr.xadr_street2 street2,
  xadr.xadr_street3 street3,
  xcou.xcou_id,
  xcou.xcou_longdesc country,
  xcou.xcou_custshortdesc,
  Decode(xcms.xcms_type, 1, xcms.xcms_name2, 2, '') AS CompanyName2,
  Decode(xcms.xcms_type, 1, xcms.xcms_name3, 2, '') AS CompanyName3,
  xsta.xsta_shortdesc,
  xsta.xsta_longdesc,
  xcms.xcms_type,
  xcms.xcms_active,
  xcms.xcms_nomailing,
  xcms.xcms_noemailing,
  DECODE(xcms.xcms_type, 1, xcid_contact.xcid_lettgreet||' '||xcms_contact.xcms_name1,
                         2, xcid_individual.xcid_lettgreet||' '||xcms.xcms_name1) lettergreeting,
  DECODE(xcms.xcms_type, 1, xcid_contact.xcid_alt_lettgreet||' '||xcms_contact.xcms_name1,
                         2, xcid_individual.xcid_alt_lettgreet||' '||xcms.xcms_name1) alt_lettergreeting,
  DECODE(xcms.xcms_type, 1, xcid_contact.xcid_addrgreet,
                         2, xcid_individual.xcid_addrgreet) addressgreetingshort,
  DECODE(xcms.xcms_type, 1, DECODE(NVL(xcid_contact.xcid_addrgreet, ''), '', '',  xcid_contact.xcid_addrgreet||' ')||DECODE(NVL(xcms_contact.xcms_name3, ''), '', '', xcms_contact.xcms_name3||' ')||DECODE(NVL(xcms_contact.xcms_name2, ''), '', '', xcms_contact.xcms_name2||' ')||DECODE(NVL(xcms_contact.xcms_name1, ''), '', '', xcms_contact.xcms_name1),
                         2, DECODE(NVL(xcid_individual.xcid_addrgreet, ''), '', '',  xcid_individual.xcid_addrgreet||' ')||DECODE(NVL(xcms.xcms_name3, ''), '', '', xcms.xcms_name3||' ')||DECODE(NVL(xcms.xcms_name2, ''), '', '', xcms.xcms_name2||' ')||DECODE(NVL(xcms.xcms_name1, ''), '', '', xcms.xcms_name1)) addressgreetingfull,
  DECODE(xcms.xcms_type, 1, DECODE(NVL(xcms_contact.xcms_name3, ''), '', '', xcms_contact.xcms_name3||' ')||DECODE(NVL(xcms_contact.xcms_name2, ''), '', '', xcms_contact.xcms_name2||' ')||DECODE(NVL(xcms_contact.xcms_name1, ''), '', '', xcms_contact.xcms_name1),
                         2, DECODE(NVL(xcms.xcms_name3, ''), '', '', xcms.xcms_name3||' ')||DECODE(NVL(xcms.xcms_name2, ''), '', '', xcms.xcms_name2||' ')||DECODE(NVL(xcms.xcms_name1, ''), '', '', xcms.xcms_name1)) addressgreeting,
  xcms.xcms_xcca_id,
  DECODE(xcms.xcms_type, 2, xcid_individual.xcid_birthtime) birthdate,
  DECODE(xcms.xcms_type, 2, xcid_individual.xcid_weddingday) xcid_weddingday,
  (SELECT TRUNC(w.wmlg_time_creation) FROM wmlg w WHERE UPPER(w.wmlg_tablename)='XCMS' AND w.wmlg_table_id=xcms.xcms_id AND ROWNUM=1) ProfileCreationTime,
(SELECT wlan_longdesc FROM wlan WHERE xcms.xcms_wlan_id=wlan_id) wlan_longdesc, 
(select v.Address from v8_rep_fulladdress_xadr v where v.xadr_id=xadr.xadr_id) Address, 
DECODE(xcms.xcms_type, 1, xcms.xcms_name1||' '||xcms.xcms_name2||' '||xcms.xcms_name3, 2, '' ) NAME,
  xcrl.xcrl_yrch_id RateCodeId,
  (SELECT yrch_shortdesc FROM yrch WHERE yrch_id=xcrl_yrch_id) yrch_shortdesc,
  (SELECT yrch_longdesc FROM yrch WHERE yrch_id=xcrl_yrch_id) yrch_longdesc,
  (SELECT wphs.WPHS_NAME FROM wphs WHERE wphs.WPHS_ID = xcms.XCMS_ORG_WPHS_ID) OrigHotel,
  xcms.XCMS_ORG_WPHS_ID, 
  DECODE(xcms.xcms_type, 1, '', 2, xcms.xcms_name1) IndividualName1,
  DECODE(xcms.xcms_type, 1, '', 2, xcms.xcms_name2) IndividualName2,
  DECODE(xcms.xcms_type, 1, '', 2, xcms.xcms_name3) IndividualName3,
  DECODE(xcms.xcms_type, 1, '', 2, xcid_individual.xcid_fam_addrgreet) famaddressgreetingshort,
  xmty_shortdesc,
  xmty_longdesc,
  xmnr.XMNR_EXPIRYDATE,
  (select xced_initials from xced where xced_id=xecl.xecl_emp_xcms_id) xced_initials,
  xmnr.xmnr_value,
  (select decode(xcid_individual.xcid_sex, 2, 
    xcid_individual.xcid_lettgreet||' '||xcms.xcms_name1||', '||v8_lower_firstchar(partner.xcid_lettgreet)||' '||partnerxcms.xcms_name1, 
    partner.xcid_lettgreet||' '||partnerxcms.xcms_name1||', '||v8_lower_firstchar(xcid_individual.xcid_lettgreet)||' '||xcms.xcms_name1)
  from xiil, xclr, xcid partner, xcms partnerxcms 
  where xiil_xclr_id=xclr_id and xclr_type in (1,5) 
    and (xiil_from_xcms_id=xcms.xcms_id  and xiil_to_xcms_id=partner.xcid_id or xiil_to_xcms_id=xcms.xcms_id and xiil_from_xcms_id=partner.xcid_id) 
    and(xcid_individual.xcid_sex=2 and partner.xcid_sex=1 or xcid_individual.xcid_sex=1 and partner.xcid_sex=2) 
    and partner.xcid_id=partnerxcms.xcms_id
    and rownum=1
  ) SHARE_LETTER_GREETING,
   (select decode(xcid_individual.xcid_sex, 2, 
    DECODE(NVL(xcid_individual.xcid_lettgreet, ''), '', '',  xcid_individual.xcid_lettgreet||' ')||xcms.xcms_name1||CHR(13)||CHR(10)||
    DECODE(NVL(partner.xcid_lettgreet, ''), '', '',  partner.xcid_lettgreet||' ')||partnerxcms.xcms_name1,  
    DECODE(NVL(partner.xcid_lettgreet, ''), '', '',  partner.xcid_lettgreet||' ')||partnerxcms.xcms_name1||CHR(13)||CHR(10)||
    DECODE(NVL(xcid_individual.xcid_lettgreet, ''), '', '',  xcid_individual.xcid_lettgreet||' ')||xcms.xcms_name1
    )  
  from xiil, xclr, xcid partner, xcms partnerxcms 
  where xiil_xclr_id=xclr_id and xclr_type in (1,5) 
    and (xiil_from_xcms_id=xcms.xcms_id  and xiil_to_xcms_id=partner.xcid_id or xiil_to_xcms_id=xcms.xcms_id and xiil_from_xcms_id=partner.xcid_id) 
    and(xcid_individual.xcid_sex=2 and partner.xcid_sex=1 or xcid_individual.xcid_sex=1 and partner.xcid_sex=2) 
    and partner.xcid_id=partnerxcms.xcms_id
    and rownum=1
  ) ALT_SHARE_LETTER_GREETING,
   (select decode(xcid_individual.xcid_sex, 2, 
    DECODE(NVL(xcid_individual.xcid_addrgreet, ''), '', '',  xcid_individual.xcid_addrgreet||' ')||DECODE(NVL(xcms.xcms_name3, ''), '', '', xcms.xcms_name3||' ')||DECODE(NVL(xcms.xcms_name2, ''), '', '', xcms.xcms_name2||' ')||DECODE(NVL(xcms.xcms_name1, ''), '', '', xcms.xcms_name1)||CHR(13)||CHR(10)||
    DECODE(NVL(partner.xcid_addrgreet, ''), '', '',  partner.xcid_addrgreet||' ')||DECODE(NVL(partnerxcms.xcms_name3, ''), '', '', partnerxcms.xcms_name3||' ')||DECODE(NVL(partnerxcms.xcms_name2, ''), '', '', partnerxcms.xcms_name2||' ')||DECODE(NVL(partnerxcms.xcms_name1, ''), '', '', partnerxcms.xcms_name1),  
    DECODE(NVL(partner.xcid_addrgreet, ''), '', '',  partner.xcid_addrgreet||' ')||DECODE(NVL(partnerxcms.xcms_name3, ''), '', '', partnerxcms.xcms_name3||' ')||DECODE(NVL(partnerxcms.xcms_name2, ''), '', '', partnerxcms.xcms_name2||' ')||DECODE(NVL(partnerxcms.xcms_name1, ''), '', '', partnerxcms.xcms_name1)||CHR(13)||CHR(10)||
    DECODE(NVL(xcid_individual.xcid_addrgreet, ''), '', '',  xcid_individual.xcid_addrgreet||' ')||DECODE(NVL(xcms.xcms_name3, ''), '', '', xcms.xcms_name3||' ')||DECODE(NVL(xcms.xcms_name2, ''), '', '', xcms.xcms_name2||' ')||DECODE(NVL(xcms.xcms_name1, ''), '', '', xcms.xcms_name1)
    )||CHR(13)||CHR(10)||(select v.Address from v8_rep_fulladdress_xadr v where v.xadr_id=xadr.xadr_id)  
  from xiil, xclr, xcid partner, xcms partnerxcms 
  where xiil_xclr_id=xclr_id and xclr_type in (1,5) 
    and (xiil_from_xcms_id=xcms.xcms_id  and xiil_to_xcms_id=partner.xcid_id or xiil_to_xcms_id=xcms.xcms_id and xiil_from_xcms_id=partner.xcid_id) 
    and(xcid_individual.xcid_sex=2 and partner.xcid_sex=1 or xcid_individual.xcid_sex=1 and partner.xcid_sex=2) 
    and partner.xcid_id=partnerxcms.xcms_id
    and rownum=1
  ) SHARE_ADDRESS_GREETING   
FROM
  xcms,
  xcca,
  (select * from xcil where nvl(xcil_primary, 0)=1) xcil,
  (select* from xcms where XCMS.XCMS_ACTIVE=1) xcms_contact,
  xcid xcid_contact,
  xcid xcid_individual,
  xsta,
  xadr,
  xcou,
  xcrl,
  xmnr, xmty, 
  xecl
  WHERE
  xcms.xcms_id=xcil.xcil_company_xcms_id(+) AND
  xcil.xcil_individual_xcms_id=xcms_contact.xcms_id(+) AND
  xcms_contact.xcms_id = xcid_contact.xcid_id(+) AND
  xcms.xcms_id = xcid_individual.xcid_id(+) AND
  xcms.xcms_xcca_id=xcca.xcca_id(+) AND
  xcms.xcms_id=xadr.xadr_xcms_id(+) AND
  xadr.xadr_xcou_id=xcou.xcou_id(+) AND
  xadr.xadr_xsta_id=xsta.xsta_id(+) AND
  xcms.xcms_id=xcrl.xcrl_xcms_id(+) AND
  xcms.xcms_id=xmnr.xmnr_xcms_id(+) AND xmnr.xmnr_xmty_id=xmty_id(+) AND
  xcms.xcms_id=xecl_cust_xcms_id(+);
