Query: Organization Manager

select
    org_tl.name                                                         "Organization",
    ppf.full_name                                                       "Manager",
    fnd_date.canonical_to_date (org_info2.org_information3)             "Start Date",
    fnd_date.canonical_to_date (org_info2.org_information4)             "End Date",
    --
    org.organization_id,
    org.business_group_id,
    ppf.person_id
from
    hr_all_organization_units org,
    hr_all_organization_units_tl org_tl,
    hr_organization_information org_info2,
    per_all_people_f ppf
where
    1=1
and org_info2.organization_id = org.organization_id
and org_info2.org_information_context = 'Organization Name Alias'
and org_info2.org_information2 = to_char (ppf.person_id(+))
and org.organization_id = org_tl.organization_id
and org_tl.language = userenv ('LANG')
and exists (select null
    from hr_org_info_types_by_class oitbc, hr_organization_information org_info
    where org_info.organization_id = org.organization_id
    and org_info.org_information_context = 'CLASS'
    and org_info.org_information2 = 'Y'
    and oitbc.org_classification = org_info.org_information1
    and oitbc.org_information_type = 'Organization Name Alias')
and (decode(hr_security.view_all,
        'Y', 'TRUE',
        hr_security.show_record('HR_ALL_ORGANIZATION_UNITS',org.organization_id)) = 'TRUE'
    and decode (hr_general.get_xbg_profile,
            'Y', org.business_group_id,
            hr_general.get_business_group_id) = org.business_group_id
    or decode (hr_security.view_all,
            'Y', 'TRUE',
            hr_security.show_record ('PER_ALL_PEOPLE_F',
                               ppf.person_id,
                               ppf.person_type_id,
                               ppf.employee_number,
                               ppf.applicant_number)) = 'TRUE'
        and decode(hr_general.get_xbg_profile,
                'Y', ppf.business_group_id,
                hr_general.get_business_group_id) = ppf.business_group_id)
--
and org_tl.name = 'Sales'
order by
    1,2