ECC iStore, iStore Search

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: ibe-search
Query Procedure: IBE_ECC_UTIL_PVT.get_ecc_data_load_info
Security Procedure: ibe_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC iStore, iStore Search and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM
		(SELECT ECC_SPEC_ID,
		itemdfv.* ,
		IBESEARCH_V.MINISITE_ID,
		IBESEARCH_V.MINISITE_NAME,
		IBESEARCH_V.MINISITE_DESCRIPTION,
		IBESEARCH_V.SECTION_ID,
		IBESEARCH_V.SECTION_NAME,
		IBESEARCH_V.SECTION_DESCRIPTION,
		IBESEARCH_V.ORGANIZATION_ID,
		IBESEARCH_V.INVENTORY_ITEM_ID,
		IBESEARCH_V.ITEM_CODE,
		IBESEARCH_V.ITEM_DESCRIPTION,
		IBESEARCH_V.ITEM_LONG_DESCRIPTION,
		IBESEARCH_V.ITEM_RETAIL_PRICE,
		IBESEARCH_V.ITEM_YOUR_PRICE,
		IBESEARCH_V.ITEM_RETAIL_PRICE_RANGE,
		IBESEARCH_V.ITEM_PRICE_RANGE,
		IBESEARCH_V.YOUR_PRICE_RANGE,
		IBESEARCH_V.SHIPPABLE_ITEM_FLAG,
		IBESEARCH_V.CUSTOMER_ORDER_FLAG,
		IBESEARCH_V.INTERNAL_ORDER_FLAG,
		IBESEARCH_V.SERVICE_ITEM_FLAG,
		IBESEARCH_V.INVENTORY_ITEM_FLAG,
		IBESEARCH_V.PURCHASING_ENABLED_FLAG,
		IBESEARCH_V.CUSTOMER_ORDER_ENABLED_FLAG,
		IBESEARCH_V.INTERNAL_ORDER_ENABLED_FLAG,
		IBESEARCH_V.RETURNABLE_FLAG,
		IBESEARCH_V.ITEM_TYPE,
		IBESEARCH_V.ITEM_LENGTH,
		IBESEARCH_V.ITEM_WIDTH,
		IBESEARCH_V.ITEM_HEIGHT,
		IBESEARCH_V.ITEM_WEIGHT,
		IBESEARCH_V.WEIGHT_UOM_CODE,
		IBESEARCH_V.WEIGHT,
		IBESEARCH_V.ITEM_VOLUME,
		IBESEARCH_V.VOLUME_UOM_CODE,
		IBESEARCH_V.VOLUME,
		IBESEARCH_V.MINISITE_ID_TEXT,
		IBESEARCH_V.SECTION_ID_TEXT,
		IBESEARCH_V.INVENTORY_ITEM_ID_TEXT,
		IBESEARCH_V.ORGANIZATION_ID_TEXT,
		IBESEARCH_V.PRICE_LIST_ID,
		IBESEARCH_V.ITEM_PRIMARY_UOM_CODE,
		IBESEARCH_V.CURRENCY_CODE,
		IBESEARCH_V.ISCONFIG,
		IBESEARCH_V.ISREADYTOBUY,
		IBESEARCH_V.ITEM_RETAIL_PRICE_DISPLAY,
		IBESEARCH_V.ITEM_YOUR_PRICE_DISPLAY,
		IBESEARCH_V.PARTY_ID,
		IBESEARCH_V.CUST_ACCT_ID,
		IBESEARCH_V.CROSS_REFERENCE_TYPE,
		IBESEARCH_V.CROSS_REFERENCE_NUMBER,
		IBESEARCH_V.CROSS_REFERENCE_DESCRIPTION,
		IBESEARCH_V.CUSTOMER_ITEM_NUMBER,
		IBESEARCH_V.CUSTOMER_ID,
		IBESEARCH_V.CUSTOMER_ITEM_DESCRIPTION,
		IBESEARCH_V.ECC_LAST_UPDATE_DATE,
		IBESEARCH_V.LANGUAGE_CODE,
		IBESEARCH_V.CONTENT_ITEM_KEY,
		IBESEARCH_V.OBJECT_ID,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE1,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE2,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE3,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE4,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE5,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE6,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE7,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE8,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE9,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE10,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE11,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE12,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE13,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE14,
		IBESEARCH_V.IBE_CUSTOM_ATTRIBUTE15,
		IBESEARCH_V.ISLIVEPRICE,
		IBESEARCH_V.ISOFFLINEPRICE
		FROM IBE_ECC_SEARCH_V IBESEARCH_V
			, (select ROW_ID "'IBEITEM_ROW_ID'",CONTEXT "'IBEITEM_CONTEXT'",to_char(LATE_DEMANDS_PENALTY) "'IBEITEM_LATE_DEMANDS_PENALTY'",FND_ECC_DFF_UTIL.get_vset_values('MTL_SYSTEM_ITEMS','INVOICE_UOM',INVOICE_UOM, NULL ,NULL,401) "'IBEITEM_INVOICE_UOM'",GRAPHICAL_LINK_FOR_WEB_REQS "'IBEITEM_GRAPHICAL_LINK_FOR_WEB_REQS'",to_char(MATERIAL_OVER_CAPACITY_PENALTY) "'IBEITEM_MATERIAL_OVER_CAPACITY_PENALTY'",FND_ECC_DFF_UTIL.get_vset_values('MTL_SYSTEM_ITEMS','CHARGE_DESCRIPTION_MASTER',CHARGE_DESCRIPTION_MASTER, NULL ,NULL,401) "'IBEITEM_CHARGE_DESCRIPTION_MASTER'",ITEM_MODEL "'IBEITEM_ITEM_MODEL'",TAX_DENOMINATION "'IBEITEM_TAX_DENOMINATION'",CONCATENATED_SEGMENTS "'IBEITEM_CONCATENATED_SEGMENTS'",INVOICE_UOM "'IBEITEM_INVOICE_UOM_EDOC'",CHARGE_DESCRIPTION_MASTER "'IBEITEM_CHARGE_DESCRIPTION_MASTER_EDOC'" from MTL_SYSTEM_ITEMS_B_DFV ) itemdfv
		WHERE
			1 = 1
			 and  ibesearch_v.row_id=itemdfv."'IBEITEM_ROW_ID'"(+)  
		)PIVOT
		(max(MINISITE_NAME) as MINISITE_NAME,
		 max(MINISITE_DESCRIPTION) as MINISITE_DESCRIPTION,
		 max(SECTION_NAME) as SECTION_NAME,
		 max(SECTION_DESCRIPTION) as SECTION_DESCRIPTION,
		 max(ITEM_DESCRIPTION) as ITEM_DESCRIPTION,
		 max(ITEM_LONG_DESCRIPTION) as ITEM_LONG_DESCRIPTION,
         max(ITEM_RETAIL_PRICE_DISPLAY) as ITEM_RETAIL_PRICE_DISPLAY,
         max(ITEM_YOUR_PRICE_DISPLAY) as ITEM_YOUR_PRICE_DISPLAY
		 for LANGUAGE_CODE in ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Organization Code
x.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:organization_code)
LOV