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