Organization Manager Script

Organization Manager Script

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