create or replace view V_LegalEntity as
select pl.plant_id, pl.code PlantCode, pl.name PlantName, pl.phoneno Plantphoneno, 
pl.faxno Plantfaxno, pl.email Plantemail, pl.website Plantwebsite, pl.logo PlantLogo,
pad.street Plantstreet, pad.street2 Plantstreet2, pad.zipcode Plantzipcode, 
pad.city Plantcity, pad.state Plantstate, pad.county Plantcounty,
bu.businessunit_id, bu.code BU_code, bu.description BU_desc, 
bu.internalvatnumber BU_internalvatnr, bu.vatnumber BU_vatnr, 
le.legalentity_id, le.code legalentity_code, le.name legalentity_name, 
le.legalname legalentity_legalname, le.cocnumber legalentity_cocnr, 
le.vatnumber legalentity_vatnr, le.phone legalentity_phone, 
le.fax legalentity_fax, le.email legalentity_email, le.website legalentity_website, 
siad.street SendInv_street, siad.street2 SendInv_street2, siad.zipcode SendInv_zipcode, 
siad.city SendInv_city, siad.state SendInv_state, siad.county SendInv_county, 
ad.street LegEntAd_street, ad.street2 LegEntAd_street2, ad.zipcode LegEntAd_zipcode, 
ad.city LegEntAd_city, ad.state LegEntAd_state, ad.county LegEntAd_county, 
bi1.description bankinst_name1, bi1.internalidcode internalidcode1, 
bi1.laundrybankaccount laundrybankaccount1, bi1.ibanno ibanno1,
bi2.description bankinst_name2, bi2.internalidcode internalidcode2, 
bi2.laundrybankaccount laundrybankaccount2, bi2.ibanno ibanno2
from businessunit bu
     inner join legalentity le
           left outer join bankinstitution bi2 on le.bankinstitution2_id = bi2.bankinstitution_id
           left outer  join address siad on le.sendinvoicetoaddress_id = siad.address_id
           inner join bankinstitution bi1 on le.bankinstitution_id = bi1.bankinstitution_id
           inner join address ad on le.address_id = ad.address_id
     on bu.legalentity_id = le.legalentity_id
     inner join plant pl 
           inner join address pad on pl.address_id = pad.address_id
     on bu.plant_id = pl.plant_id
/
