ECC Project Procurement, Approved Supplier List

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: po-item-asl
Query Procedure: PO_PPCC_ECC_UTIL_PVT.LOAD_ECC_DATA_FULL
Security Procedure: PO_PPCC_ECC_UTIL_PVT.GetFilterAttributeValues
Run ECC Project Procurement, Approved Supplier List and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Download
 
Related Blitz Reports
ECC Procurement, Agreements ECC Procurement, Purchase Orders GL Account Analysis GL Account Analysis (Distributions) PO Approved Supplier List INV Intercompany Invoice Reconciliation ECC Process Genealogy and Trace, Purchase Orders PO Receiving Transactions Pipeline EAM Assets
Blitz Report™

Blitz Report™ provides multiple benefits: