VIEW: APPS.ICX_WEBSTORE_SEARCH_ITEMS_V

Source

View Text - Preformatted

SELECT DISTINCT ICRT.CONID , ICRT.SCORE , PLL.PRICE_LIST_LINE_ID , PLL.INVENTORY_ITEM_ID , MUOM.UNIT_OF_MEASURE , PLL.LIST_PRICE , MSI.CONCATENATED_SEGMENTS , MSI.DESCRIPTION , MSI.ATTRIBUTE14 , PLL.PRICE_LIST_ID , PLC.CURRENCY_CODE , MIC.CATEGORY_ID , MIC.ORGANIZATION_ID , MIC.CATEGORY_SET_ID , MSI.BOM_ITEM_TYPE , MCIS.CUSTOMER_ID , MCIS.ADDRESS_ID , MCIX.CUSTOMER_ITEM_ID , MCIS.CUSTOMER_ITEM_NUMBER , MCIS.CUSTOMER_CATEGORY_CODE , MCIS.ITEM_DEFINITION_LEVEL , MTP.MASTER_ORGANIZATION_ID , PLL.START_DATE_ACTIVE , PLL.END_DATE_ACTIVE FROM MTL_ITEM_CATEGORIES MIC, SO_PRICE_LIST_LINES PLL, MTL_CUSTOMER_ITEMS MCIS, MTL_CUSTOMER_ITEM_XREFS MCIX, MTL_UNITS_OF_MEASURE MUOM, SO_PRICE_LISTS PLC, MTL_PARAMETERS MTP, MTL_SYSTEM_ITEMS_KFV MSI, FND_PROFILE_OPTION_VALUES FPOV, FND_PROFILE_OPTION_VALUES FPOV2, FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTIONS FPO2, ICX_CONTEXT_RESULTS_TEMP ICRT WHERE MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND PLL.UNIT_CODE = MUOM.UOM_CODE AND PLL.PRICE_LIST_ID = PLC.PRICE_LIST_ID AND MSI.INVENTORY_ITEM_ID = PLL.INVENTORY_ITEM_ID AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MIC.ORGANIZATION_ID = FPOV2.PROFILE_OPTION_VALUE AND MCIX.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID AND MTP.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND NVL(MCIX.MASTER_ORGANIZATION_ID, MTP.MASTER_ORGANIZATION_ID) = MTP.MASTER_ORGANIZATION_ID AND MCIS.CUSTOMER_ITEM_ID (+)= MCIX.CUSTOMER_ITEM_ID AND MSI.ORGANIZATION_ID = FPOV2.PROFILE_OPTION_VALUE AND MSI.BOM_ITEM_TYPE IN (4,1) AND ((FPOV.PROFILE_OPTION_VALUE = 'Y' AND MSI.CUSTOMER_ORDER_ENABLED_FLAG = 'Y') OR FPOV.PROFILE_OPTION_VALUE = 'N') AND FPO.START_DATE_ACTIVE <= SYSDATE AND NVL(FPO.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND NVL(PLL.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(PLL.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID AND MCIX.INACTIVE_FLAG(+) = 'N' AND FPO2.START_DATE_ACTIVE <= SYSDATE AND NVL(FPO2.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND FPO2.PROFILE_OPTION_ID = FPOV2.PROFILE_OPTION_ID AND FPOV2.APPLICATION_ID = 300 AND FPOV2.LEVEL_ID = 10001 AND FPOV2.LEVEL_VALUE = 0 AND FPOV.APPLICATION_ID = 300 AND FPOV.LEVEL_ID = 10002 AND FPOV.LEVEL_VALUE = 300 AND FPO2.PROFILE_OPTION_NAME = 'SO_ORGANIZATION_ID' AND FPO2.APPLICATION_ID = 300 AND FPO.PROFILE_OPTION_NAME = 'OE_VALIDATE_STANDARD_LINE_ITEM' AND ICRT.TEXTKEY = MSI.INVENTORY_ITEM_ID AND ICRT.TEXTKEY2 = MSI.ORGANIZATION_ID UNION SELECT DISTINCT ICRT.CONID , ICRT.SCORE , PLL.PRICE_LIST_LINE_ID , PLL.INVENTORY_ITEM_ID , MUOM.UNIT_OF_MEASURE , PLL.LIST_PRICE , MSI.CONCATENATED_SEGMENTS , MSI.DESCRIPTION , MSI.ATTRIBUTE14 , PLL.PRICE_LIST_ID , PLC.CURRENCY_CODE , MIC.CATEGORY_ID , MIC.ORGANIZATION_ID , MIC.CATEGORY_SET_ID , MSI.BOM_ITEM_TYPE , MCIS.CUSTOMER_ID , MCIS.ADDRESS_ID , MCIX.CUSTOMER_ITEM_ID , MCIS.CUSTOMER_ITEM_NUMBER , MCIS.CUSTOMER_CATEGORY_CODE , MCIS.ITEM_DEFINITION_LEVEL , MTP.MASTER_ORGANIZATION_ID , PLL.START_DATE_ACTIVE , PLL.END_DATE_ACTIVE FROM MTL_ITEM_CATEGORIES MIC, SO_PRICE_LIST_LINES PLL, MTL_CUSTOMER_ITEMS MCIS, MTL_CUSTOMER_ITEM_XREFS MCIX, MTL_UNITS_OF_MEASURE MUOM, SO_PRICE_LISTS PLC, MTL_PARAMETERS MTP, MTL_SYSTEM_ITEMS_KFV MSI, FND_PROFILE_OPTION_VALUES FPOV, FND_PROFILE_OPTION_VALUES FPOV2, FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTIONS FPO2, ICX_CONTEXT_RESULTS_TEMP ICRT, SO_PRICE_LISTS SPLC WHERE MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND PLL.UNIT_CODE = MUOM.UOM_CODE AND PLL.PRICE_LIST_ID = PLC.PRICE_LIST_ID AND MSI.INVENTORY_ITEM_ID = PLL.INVENTORY_ITEM_ID AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MIC.ORGANIZATION_ID = FPOV2.PROFILE_OPTION_VALUE AND MCIX.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID AND MTP.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND NVL(MCIX.MASTER_ORGANIZATION_ID, MTP.MASTER_ORGANIZATION_ID) = MTP.MASTER_ORGANIZATION_ID AND MCIS.CUSTOMER_ITEM_ID (+)= MCIX.CUSTOMER_ITEM_ID AND MSI.ORGANIZATION_ID = FPOV2.PROFILE_OPTION_VALUE AND MSI.BOM_ITEM_TYPE IN (4,1) AND ((FPOV.PROFILE_OPTION_VALUE = 'Y' AND MSI.CUSTOMER_ORDER_ENABLED_FLAG = 'Y') OR FPOV.PROFILE_OPTION_VALUE = 'N') AND FPO.START_DATE_ACTIVE <= SYSDATE AND NVL(FPO.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND NVL(PLL.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(PLL.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID AND MCIX.INACTIVE_FLAG(+) = 'N' AND FPO2.START_DATE_ACTIVE <= SYSDATE AND NVL(FPO2.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND FPO2.PROFILE_OPTION_ID = FPOV2.PROFILE_OPTION_ID AND FPOV2.APPLICATION_ID = 300 AND FPOV2.LEVEL_ID = 10001 AND FPOV2.LEVEL_VALUE = 0 AND FPOV.APPLICATION_ID = 300 AND FPOV.LEVEL_ID = 10002 AND FPOV.LEVEL_VALUE = 300 AND FPO2.PROFILE_OPTION_NAME = 'SO_ORGANIZATION_ID' AND FPO2.APPLICATION_ID = 300 AND FPO.PROFILE_OPTION_NAME = 'OE_VALIDATE_STANDARD_LINE_ITEM' AND ICRT.TEXTKEY = MSI.INVENTORY_ITEM_ID AND ICRT.TEXTKEY2 = MSI.ORGANIZATION_ID AND PLL.PRICE_LIST_ID = PLC.SECONDARY_PRICE_LIST_ID AND SPLC.PRICE_LIST_ID = PLL.PRICE_LIST_ID AND NOT EXISTS ( SELECT '1' FROM SO_PRICE_LIST_LINES PLL2 WHERE PLL2.PRICE_LIST_ID = PLC.PRICE_LIST_ID AND PLL2.INVENTORY_ITEM_ID = PLL.INVENTORY_ITEM_ID AND PLL2.UNIT_CODE = PLL.UNIT_CODE )

View Text - HTML Formatted

SELECT DISTINCT ICRT.CONID
, ICRT.SCORE
, PLL.PRICE_LIST_LINE_ID
, PLL.INVENTORY_ITEM_ID
, MUOM.UNIT_OF_MEASURE
, PLL.LIST_PRICE
, MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.ATTRIBUTE14
, PLL.PRICE_LIST_ID
, PLC.CURRENCY_CODE
, MIC.CATEGORY_ID
, MIC.ORGANIZATION_ID
, MIC.CATEGORY_SET_ID
, MSI.BOM_ITEM_TYPE
, MCIS.CUSTOMER_ID
, MCIS.ADDRESS_ID
, MCIX.CUSTOMER_ITEM_ID
, MCIS.CUSTOMER_ITEM_NUMBER
, MCIS.CUSTOMER_CATEGORY_CODE
, MCIS.ITEM_DEFINITION_LEVEL
, MTP.MASTER_ORGANIZATION_ID
, PLL.START_DATE_ACTIVE
, PLL.END_DATE_ACTIVE
FROM MTL_ITEM_CATEGORIES MIC
, SO_PRICE_LIST_LINES PLL
, MTL_CUSTOMER_ITEMS MCIS
, MTL_CUSTOMER_ITEM_XREFS MCIX
, MTL_UNITS_OF_MEASURE MUOM
, SO_PRICE_LISTS PLC
, MTL_PARAMETERS MTP
, MTL_SYSTEM_ITEMS_KFV MSI
, FND_PROFILE_OPTION_VALUES FPOV
, FND_PROFILE_OPTION_VALUES FPOV2
, FND_PROFILE_OPTIONS FPO
, FND_PROFILE_OPTIONS FPO2
, ICX_CONTEXT_RESULTS_TEMP ICRT
WHERE MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND PLL.UNIT_CODE = MUOM.UOM_CODE
AND PLL.PRICE_LIST_ID = PLC.PRICE_LIST_ID
AND MSI.INVENTORY_ITEM_ID = PLL.INVENTORY_ITEM_ID
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = FPOV2.PROFILE_OPTION_VALUE
AND MCIX.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID
AND MTP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND NVL(MCIX.MASTER_ORGANIZATION_ID
, MTP.MASTER_ORGANIZATION_ID) = MTP.MASTER_ORGANIZATION_ID
AND MCIS.CUSTOMER_ITEM_ID (+)= MCIX.CUSTOMER_ITEM_ID
AND MSI.ORGANIZATION_ID = FPOV2.PROFILE_OPTION_VALUE
AND MSI.BOM_ITEM_TYPE IN (4
, 1)
AND ((FPOV.PROFILE_OPTION_VALUE = 'Y'
AND MSI.CUSTOMER_ORDER_ENABLED_FLAG = 'Y') OR FPOV.PROFILE_OPTION_VALUE = 'N')
AND FPO.START_DATE_ACTIVE <= SYSDATE
AND NVL(FPO.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND NVL(PLL.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(PLL.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND MCIX.INACTIVE_FLAG(+) = 'N'
AND FPO2.START_DATE_ACTIVE <= SYSDATE
AND NVL(FPO2.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND FPO2.PROFILE_OPTION_ID = FPOV2.PROFILE_OPTION_ID
AND FPOV2.APPLICATION_ID = 300
AND FPOV2.LEVEL_ID = 10001
AND FPOV2.LEVEL_VALUE = 0
AND FPOV.APPLICATION_ID = 300
AND FPOV.LEVEL_ID = 10002
AND FPOV.LEVEL_VALUE = 300
AND FPO2.PROFILE_OPTION_NAME = 'SO_ORGANIZATION_ID'
AND FPO2.APPLICATION_ID = 300
AND FPO.PROFILE_OPTION_NAME = 'OE_VALIDATE_STANDARD_LINE_ITEM'
AND ICRT.TEXTKEY = MSI.INVENTORY_ITEM_ID
AND ICRT.TEXTKEY2 = MSI.ORGANIZATION_ID UNION SELECT DISTINCT ICRT.CONID
, ICRT.SCORE
, PLL.PRICE_LIST_LINE_ID
, PLL.INVENTORY_ITEM_ID
, MUOM.UNIT_OF_MEASURE
, PLL.LIST_PRICE
, MSI.CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.ATTRIBUTE14
, PLL.PRICE_LIST_ID
, PLC.CURRENCY_CODE
, MIC.CATEGORY_ID
, MIC.ORGANIZATION_ID
, MIC.CATEGORY_SET_ID
, MSI.BOM_ITEM_TYPE
, MCIS.CUSTOMER_ID
, MCIS.ADDRESS_ID
, MCIX.CUSTOMER_ITEM_ID
, MCIS.CUSTOMER_ITEM_NUMBER
, MCIS.CUSTOMER_CATEGORY_CODE
, MCIS.ITEM_DEFINITION_LEVEL
, MTP.MASTER_ORGANIZATION_ID
, PLL.START_DATE_ACTIVE
, PLL.END_DATE_ACTIVE
FROM MTL_ITEM_CATEGORIES MIC
, SO_PRICE_LIST_LINES PLL
, MTL_CUSTOMER_ITEMS MCIS
, MTL_CUSTOMER_ITEM_XREFS MCIX
, MTL_UNITS_OF_MEASURE MUOM
, SO_PRICE_LISTS PLC
, MTL_PARAMETERS MTP
, MTL_SYSTEM_ITEMS_KFV MSI
, FND_PROFILE_OPTION_VALUES FPOV
, FND_PROFILE_OPTION_VALUES FPOV2
, FND_PROFILE_OPTIONS FPO
, FND_PROFILE_OPTIONS FPO2
, ICX_CONTEXT_RESULTS_TEMP ICRT
, SO_PRICE_LISTS SPLC
WHERE MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND PLL.UNIT_CODE = MUOM.UOM_CODE
AND PLL.PRICE_LIST_ID = PLC.PRICE_LIST_ID
AND MSI.INVENTORY_ITEM_ID = PLL.INVENTORY_ITEM_ID
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = FPOV2.PROFILE_OPTION_VALUE
AND MCIX.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID
AND MTP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND NVL(MCIX.MASTER_ORGANIZATION_ID
, MTP.MASTER_ORGANIZATION_ID) = MTP.MASTER_ORGANIZATION_ID
AND MCIS.CUSTOMER_ITEM_ID (+)= MCIX.CUSTOMER_ITEM_ID
AND MSI.ORGANIZATION_ID = FPOV2.PROFILE_OPTION_VALUE
AND MSI.BOM_ITEM_TYPE IN (4
, 1)
AND ((FPOV.PROFILE_OPTION_VALUE = 'Y'
AND MSI.CUSTOMER_ORDER_ENABLED_FLAG = 'Y') OR FPOV.PROFILE_OPTION_VALUE = 'N')
AND FPO.START_DATE_ACTIVE <= SYSDATE
AND NVL(FPO.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND NVL(PLL.START_DATE_ACTIVE
, SYSDATE) <= SYSDATE
AND NVL(PLL.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND MCIX.INACTIVE_FLAG(+) = 'N'
AND FPO2.START_DATE_ACTIVE <= SYSDATE
AND NVL(FPO2.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
AND FPO2.PROFILE_OPTION_ID = FPOV2.PROFILE_OPTION_ID
AND FPOV2.APPLICATION_ID = 300
AND FPOV2.LEVEL_ID = 10001
AND FPOV2.LEVEL_VALUE = 0
AND FPOV.APPLICATION_ID = 300
AND FPOV.LEVEL_ID = 10002
AND FPOV.LEVEL_VALUE = 300
AND FPO2.PROFILE_OPTION_NAME = 'SO_ORGANIZATION_ID'
AND FPO2.APPLICATION_ID = 300
AND FPO.PROFILE_OPTION_NAME = 'OE_VALIDATE_STANDARD_LINE_ITEM'
AND ICRT.TEXTKEY = MSI.INVENTORY_ITEM_ID
AND ICRT.TEXTKEY2 = MSI.ORGANIZATION_ID
AND PLL.PRICE_LIST_ID = PLC.SECONDARY_PRICE_LIST_ID
AND SPLC.PRICE_LIST_ID = PLL.PRICE_LIST_ID
AND NOT EXISTS ( SELECT '1'
FROM SO_PRICE_LIST_LINES PLL2
WHERE PLL2.PRICE_LIST_ID = PLC.PRICE_LIST_ID
AND PLL2.INVENTORY_ITEM_ID = PLL.INVENTORY_ITEM_ID
AND PLL2.UNIT_CODE = PLL.UNIT_CODE )