PA Organization Listing

SELECT
	substr(p.name,1,60) organization_name, 
	hr.meaning type,
	pal2.meaning internal_external,
	substr(hrltl.location_code,1,20) location
FROM
	hr_lookups hr,
	hr_lookups pal2,
	hr_locations_all hrl,
                  hr_locations_all_tl hrltl,
	pa_implementations pi,
	hr_organization_units p
WHERE
	p.business_group_id = pi.business_group_id
and  	pal2.lookup_type (+) = 'INTL_EXTL'
and     pal2.lookup_code (+) = p.internal_external_flag
and 	hrl.location_id (+) = p.location_id
and     hrltl.location_id(+) = hrl.location_id
and     hrltl.language(+) = userenv('LANG') 
and     hr.lookup_code (+) = p.type
and     hr.lookup_type (+) = 'ORG_TYPE'
and     ((:p_type is null) or p.type = nvl(:p_type,p.type))
ORDER BY 
        p.name