ECC Project Procurement, Approved Supplier List

select
x.*
from
(
SELECT * FROM (
  SELECT /*+ leading(apl) cardinality(apl 10) index(apl PO_APPROVED_SUPPLIER_LIST_EN1) */
						DISTINCT
							'ASL_LIST' RECORD_TYPE,
							pov.segment1 || ' - ' || msi.concatenated_segments || psa.vendor_site_code ECC_SPEC_ID,
							pov.segment1 || ' - ' || msi.concatenated_segments || psa.vendor_site_code RECORD_IDENTIFIER,
							pov.segment1  supplier_number,
							pov.vendor_name supplier_name,
							psa.vendor_site_code supplier_site,
							msi.concatenated_segments  item_number,
							msi.description item_description,
							cat.concatenated_segments category_item,
							plc.meaning business_type,
							pst.status asl_status,
							apl.primary_vendor_item supplier_item,
							mma.manufacturer_name manufacturer,
							ORG.ORGANIZATION_ID as ORG_ID,
							1 amount9,
              lang.language_code language
						from
							MTL_SYSTEM_ITEMS_B_KFV msi,
							MTL_CATEGORIES_B_KFV cat,
							po_approved_supplier_list apl,
							AP_SUPPLIERS pov,
							AP_SUPPLIER_SITES_ALL psa,
							mtl_manufacturers mma,
							FND_LOOKUP_VALUES plc,
							po_asl_statuses pst,
							hz_parties hp,
							HZ_PARTY_SITES HPS,
							hz_organization_profiles org_profile	,
							HZ_LOCATIONS HL,
							hz_geo_struct_map_dtl dtl,
							hz_geo_struct_map map,
							HR_ALL_ORGANIZATION_UNITS ORG,
              FND_LANGUAGES lang
						where
							pst.status_id = apl.asl_status_id
							and plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
							and plc.lookup_code = apl.vendor_business_type
							and plc.VIEW_APPLICATION_ID = 201
              and lang.installed_flag in ('I', 'B')
							and nvl(plc.language, lang.language_code) = lang.language_code
							and SECURITY_GROUP_ID = fnd_global.lookup_security_group(plc.LOOKUP_TYPE,plc.VIEW_APPLICATION_ID)
							and mma.manufacturer_id (+) = apl.manufacturer_id
							and psa.vendor_site_id = apl.vendor_site_id
							and pov.vendor_id = apl.vendor_id
							and psa.org_id = org.organization_id(+)
							and cat.CATEGORY_ID (+) = apl.category_id
							and msi.inventory_item_id = apl.item_id
							and msi.INVENTORY_ITEM_ID(+)  = apl.ITEM_ID
							and msi.ORGANIZATION_ID(+) = apl.OWNING_ORGANIZATION_ID
							AND hp.party_id = pov.party_id
							AND HPS.PARTY_SITE_ID(+) = psa.PARTY_SITE_ID
							AND org_profile.party_id(+)           = pov.party_id
							AND org_profile.effective_end_date(+) IS NULL
							AND dtl.map_id(+)                         = map.map_id
							AND NVL(hl.address_style, 'XX')        = NVL(map.address_style, NVL(hl.address_style, 'XX'))
							AND NVL(map.loc_tbl_name, 'HZ_LOCATIONS')                   = 'HZ_LOCATIONS'
							AND map.country_code(+)                   = hl.country
							AND nvl(dtl.loc_seq_num, 2)                    = 2
							AND HL.LOCATION_ID(+) = HPS.LOCATION_ID
			        AND  LANG.LANGUAGE_CODE in ('US')
				) PIVOT (
				 MAX(BUSINESS_TYPE) AS BUSINESS_TYPE
				for LANGUAGE in ('US' "US")
				 )
) x
where
2=2