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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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 ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",ATTRIBUTE1 "LATE_DEMANDS_PENALTY",ATTRIBUTE15 "INVOICE_UOM",ATTRIBUTE14 "GRAPHICAL_LINK_FOR_WEB_REQS",ATTRIBUTE2 "MATERIAL_OVER_CAPACITY_PENALTY",(DECODE(ATTRIBUTE_CATEGORY,'Chargeable Patient Item',ATTRIBUTE12,NULL)) "CHARGE_DESCRIPTION_MASTER",(DECODE(ATTRIBUTE_CATEGORY,'Golden Tax',ATTRIBUTE12,NULL)) "ITEM_MODEL",(DECODE(ATTRIBUTE_CATEGORY,'Golden Tax',ATTRIBUTE13,NULL)) "TAX_DENOMINATION",ATTRIBUTE1||'-'||ATTRIBUTE15||'-'||ATTRIBUTE14||'-'||ATTRIBUTE2||'-'||(DECODE(ATTRIBUTE_CATEGORY,'Chargeable Patient Item',ATTRIBUTE_CATEGORY||'-'||ATTRIBUTE12,'Golden Tax',ATTRIBUTE_CATEGORY||'-'||ATTRIBUTE12||'-'||ATTRIBUTE13,NULL)) "CONCATENATED_SEGMENTS" from MTL_SYSTEM_ITEMS_B ) ) 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