with c_edi_pyo as 
(select etd.tp_header_id,emap.map_code 
  from ece_tp_details etd,ece_mappings emap 
 where etd.map_id=emap.map_id 
   and nvl(etd.edi_flag,'N')='Y' 
   and emap.map_code='EC_PYO_FF'), 
c_edi_asni as 
(select etd.tp_header_id,emap.map_code 
  from ece_tp_details etd,ece_mappings emap 
 where etd.map_id=emap.map_id 
   and nvl(etd.edi_flag,'N')='Y' 
   and emap.map_code='EC_ASNI_FF'), 
c_edi_ini as 
(select etd.tp_header_id,emap.map_code 
  from ece_tp_details etd,ece_mappings emap 
 where etd.map_id=emap.map_id 
   and nvl(etd.edi_flag,'N')='Y' 
   and emap.map_code='EC_INI_FF'), 
c_edi_poo as 
(select etd.tp_header_id,emap.map_code 
  from ece_tp_details etd,ece_mappings emap 
 where etd.map_id=emap.map_id 
   and nvl(etd.edi_flag,'N')='Y' 
   and emap.map_code='EC_POO_FF') 
select asup.vendor_name Supplier_name, 
      asup.segment1 supplier_number, 
      assa.vendor_site_code supplier_site_code, 
      assa.EDI_ID_NUMBER EDI_ID, 
      decode(pyo.tp_header_id, NULL, 'No', 'Yes') EDI_820_PAYMENT_OUT, 
      decode(poo.tp_header_id, NULL, 'No', 'Yes') EDI_850_PO_OUT, 
      decode(asni.tp_header_id, NULL, 'No', 'Yes') EDI_856_ASNI_IN, 
      decode(ini.tp_header_id, NULL, 'No', 'Yes') EDI_810_INV_IN 
 from ece_tp_headers        eth, 
      ap_supplier_sites_all assa, 
      ap_suppliers          asup, 
      c_edi_pyo             pyo, 
      c_edi_poo             poo, 
      c_edi_asni            asni, 
      c_edi_ini             ini 
where eth.tp_header_id = assa.tp_header_id 
  and assa.vendor_id = asup.vendor_id 
  and eth.tp_header_id = pyo.tp_header_id(+) 
  and eth.tp_header_id = poo.tp_header_id(+) 
  and eth.tp_header_id = asni.tp_header_id(+) 
  and eth.tp_header_id = ini.tp_header_id(+);