select distinct invoice.vendor_name, invoice.vendor_name_alt, invoice.vendor_type_lookup_code, invoice.vendor_number
, invoice.set_of_books, invoice.set_of_books_id
, decode(invoice.total, 0, 'No', 'Yes') invoices
, org.user_name, org.email_address
from (
select pv.vendor_name vendor_name 
, pv.vendor_name_alt vendor_name_alt
, pv.vendor_type_lookup_code vendor_type_lookup_code
, pv.segment1 vendor_number 
, hou.name set_of_books 
, aia.set_of_books_id set_of_books_id
, aia.org_id org_id
, count(*) total 
-- select aia.* 
from po_vendors pv , ap_invoices_all aia, hr_operating_units hou --po_vendor_sites_all pvsa, 
where pv.vendor_id = aia.vendor_id 
--and pv.vendor_name like 'TTS CARGO%' --= 'TTS CARGO.' -- 
and (aia.amount_paid != aia.invoice_amount or aia.amount_paid is null) 
and aia.cancelled_date is null 
and hou.organization_id = aia.org_id
and hou.set_of_books_id = aia.set_of_books_id 
group by pv.vendor_name, pv.vendor_name_alt, pv.vendor_type_lookup_code, pv.segment1, hou.name 
, aia.org_id, aia.set_of_books_id 
) invoice
, (
select frt.responsibility_name, fu.user_name, frt.responsibility_name, fu.description, fu.employee_id, fu.email_address
, fpov.profile_option_value 
from FND_PROFILE_OPTION_values fpov, FND_RESPONSIBILITY_VL frt, FND_USER_RESP_GROUPS_DIRECT furgd, fnd_user fu
--FND_RESPONSIBILITY_TL
where fpov.profile_option_id = 1201 -- = 'GL_SET_OF_BKS_ID'
and frt.responsibility_id = furgd.responsibility_id 
and fpov.level_value = frt.responsibility_id 
and furgd.user_id = fu.user_id 
--and frt.language = 'US'
and fu.end_date is null 
and furgd.end_date is null 
and substr(fu.description,1,2) = substr(frt.responsibility_name,1,2)
--and frt.responsibility_name like substr(fu.description,1,2) || '%AP%Super User - Corpo'
--and frt.responsibility_name like '%AP%Super User - Corpo'
and frt.menu_id in (76138, 76394, 70778, 76138, 76115) -- , 68030)
--and frt.responsibility_name like '%AP%Super%Corpo%'
) set_of_books 
, (
select frt.responsibility_name, fu.user_name, frt.responsibility_name, fu.description, fu.employee_id, fu.email_address
, fpov.profile_option_value 
from FND_PROFILE_OPTION_values fpov, FND_RESPONSIBILITY_VL frt, FND_USER_RESP_GROUPS_DIRECT furgd, fnd_user fu
--FND_RESPONSIBILITY_TL
where fpov.profile_option_id = 1991 -- 'ORG_ID' -- = 1201
and frt.responsibility_id = furgd.responsibility_id 
and fpov.level_value = frt.responsibility_id 
and furgd.user_id = fu.user_id 
--and frt.language = 'US'
and fu.end_date is null 
and furgd.end_date is null 
and substr(fu.description,1,2) = substr(frt.responsibility_name,1,2)
and frt.menu_id in (76138, 76394, 70778, 76138, 76115) -- , 68030)
--and frt.responsibility_name like substr(fu.description,1,2) || '%AP%Super User - Corpo'
--and frt.responsibility_name like '%AP%Super User - Corpo'
) org 
where set_of_books.profile_option_value = invoice.set_of_books_id 
and org.profile_option_value = invoice.org_id