View: OE_ITEMS_ORD_MV

Product:	ONT - Order Management
Description:	This view is based on the materialized view OE_ITEMS_MV. This view displays all the Orderable items after taking the Orderability Rules into account.
Implementation/DBA Data:	APPS.OE_ITEMS_ORD_MV

View Text

SELECT MV.ITEM
, MV.ITEM_ID
, MV.ITEM_DESCRIPTION
, MV.INVENTORY_ITEM_ID
, MV.INVENTORY_ITEM
, MV.ITEM_IDENTIFIER_TYPE
, MV.SOLD_TO_ORG_ID
, MV.ORGANIZATION_ID
, MV.ITEM_STATUS
, MV.CROSS_REF_STATUS
, MV.ADDRESS
, MV.CUST_ADDRESS
, MV.ITEM_DEFINITION_LEVEL
, MV.RANK
, MV.SITE_USE_ID
, MV.ITEM_DEFINITION_LEVEL_CODE
, MV.LANGUAGE
, MV.CATEGORY_ID
, MV.ORG_INDEPENDENT_FLAG
, MV.CROSS_REF_ORG_ID FROM OE_ITEM_ORDERABILITY IO_HDR
, OE_ITEMS_MV MV
WHERE IO_HDR.ORG_ID = OE_ITORD_UTIL.GET_OPERATING_UNIT_ID AND IO_HDR.ITEM_LEVEL = 'I' AND IO_HDR.GENERALLY_AVAILABLE = 'N' AND IO_HDR.ENABLE_FLAG = 'Y' AND EXISTS ( SELECT 1 FROM OE_ITEM_ORDERABILITY_RULES IO_RULES WHERE IO_RULES.ORDERABILITY_ID = IO_HDR.ORDERABILITY_ID AND IO_RULES.ENABLE_FLAG = 'Y' AND (IO_RULES.CUSTOMER_ID = OE_ITORD_UTIL.GET_CUSTOMER_ID OR IO_RULES.CUSTOMER_CLASS_ID = OE_ITORD_UTIL.GET_CUSTOMER_CLASS_ID OR IO_RULES.CUSTOMER_CATEGORY_CODE = OE_ITORD_UTIL.GET_CUSTOMER_CATEGORY_CODE OR INSTR(OE_ITORD_UTIL.GET_REGION_IDS
, '
, '||TO_CHAR(IO_RULES.REGION_ID)||'
, ' )!= 0 OR IO_RULES.ORDER_TYPE_ID = OE_ITORD_UTIL.GET_ORDER_TYPE_ID OR IO_RULES.SHIP_TO_LOCATION_ID = OE_ITORD_UTIL.GET_SHIP_TO_ORG_ID OR IO_RULES.SALES_CHANNEL_CODE = OE_ITORD_UTIL.GET_SALES_CHANNEL_CODE OR IO_RULES.SALES_PERSON_ID = OE_ITORD_UTIL.GET_SALESREP_ID OR IO_RULES.END_CUSTOMER_ID = OE_ITORD_UTIL.GET_END_CUSTOMER_ID OR IO_RULES.BILL_TO_LOCATION_ID = OE_ITORD_UTIL.GET_INVOICE_TO_ORG_ID OR IO_RULES.DELIVER_TO_LOCATION_ID = OE_ITORD_UTIL.GET_DELIVER_TO_ORG_ID ) ) AND MV.ORGANIZATION_ID = OE_ITORD_UTIL.GET_ITEM_VALIDATION_ORG_ID AND MV.LANGUAGE = USERENV('LANG') AND MV.INVENTORY_ITEM_ID = IO_HDR.INVENTORY_ITEM_ID UNION ALL SELECT MV.ITEM
, MV.ITEM_ID
, MV.ITEM_DESCRIPTION
, MV.INVENTORY_ITEM_ID
, MV.INVENTORY_ITEM
, MV.ITEM_IDENTIFIER_TYPE
, MV.SOLD_TO_ORG_ID
, MV.ORGANIZATION_ID
, MV.ITEM_STATUS
, MV.CROSS_REF_STATUS
, MV.ADDRESS
, MV.CUST_ADDRESS
, MV.ITEM_DEFINITION_LEVEL
, MV.RANK
, MV.SITE_USE_ID
, MV.ITEM_DEFINITION_LEVEL_CODE
, MV.LANGUAGE
, MV.CATEGORY_ID
, MV.ORG_INDEPENDENT_FLAG
, MV.CROSS_REF_ORG_ID FROM OE_ITEM_ORDERABILITY IO_HDR
, OE_ITEMS_MV MV
WHERE IO_HDR.ORG_ID = OE_ITORD_UTIL.GET_OPERATING_UNIT_ID AND IO_HDR.ITEM_LEVEL = 'C' AND IO_HDR.GENERALLY_AVAILABLE = 'N' AND IO_HDR.ENABLE_FLAG = 'Y' AND EXISTS ( SELECT 1 FROM OE_ITEM_ORDERABILITY_RULES IO_RULES WHERE IO_RULES.ORDERABILITY_ID = IO_HDR.ORDERABILITY_ID AND IO_RULES.ENABLE_FLAG = 'Y' AND (IO_RULES.CUSTOMER_ID = OE_ITORD_UTIL.GET_CUSTOMER_ID OR IO_RULES.CUSTOMER_CLASS_ID = OE_ITORD_UTIL.GET_CUSTOMER_CLASS_ID OR IO_RULES.CUSTOMER_CATEGORY_CODE = OE_ITORD_UTIL.GET_CUSTOMER_CATEGORY_CODE OR INSTR(OE_ITORD_UTIL.GET_REGION_IDS
, '
, '||TO_CHAR(IO_RULES.REGION_ID)||'
, ' )!= 0 OR IO_RULES.ORDER_TYPE_ID = OE_ITORD_UTIL.GET_ORDER_TYPE_ID OR IO_RULES.SHIP_TO_LOCATION_ID = OE_ITORD_UTIL.GET_SHIP_TO_ORG_ID OR IO_RULES.SALES_CHANNEL_CODE = OE_ITORD_UTIL.GET_SALES_CHANNEL_CODE OR IO_RULES.SALES_PERSON_ID = OE_ITORD_UTIL.GET_SALESREP_ID OR IO_RULES.END_CUSTOMER_ID = OE_ITORD_UTIL.GET_END_CUSTOMER_ID OR IO_RULES.BILL_TO_LOCATION_ID = OE_ITORD_UTIL.GET_INVOICE_TO_ORG_ID OR IO_RULES.DELIVER_TO_LOCATION_ID = OE_ITORD_UTIL.GET_DELIVER_TO_ORG_ID ) ) AND MV.ORGANIZATION_ID = OE_ITORD_UTIL.GET_ITEM_VALIDATION_ORG_ID AND MV.LANGUAGE = USERENV('LANG') AND MV.CATEGORY_ID = IO_HDR.ITEM_CATEGORY_ID UNION ALL SELECT MV.ITEM
, MV.ITEM_ID
, MV.ITEM_DESCRIPTION
, MV.INVENTORY_ITEM_ID
, MV.INVENTORY_ITEM
, MV.ITEM_IDENTIFIER_TYPE
, MV.SOLD_TO_ORG_ID
, MV.ORGANIZATION_ID
, MV.ITEM_STATUS
, MV.CROSS_REF_STATUS
, MV.ADDRESS
, MV.CUST_ADDRESS
, MV.ITEM_DEFINITION_LEVEL
, MV.RANK
, MV.SITE_USE_ID
, MV.ITEM_DEFINITION_LEVEL_CODE
, MV.LANGUAGE
, MV.CATEGORY_ID
, MV.ORG_INDEPENDENT_FLAG
, MV.CROSS_REF_ORG_ID FROM OE_ITEMS_MV MV
, OE_ITEM_ORDERABILITY IO_HDR WHERE IO_HDR.ORG_ID = OE_ITORD_UTIL.GET_OPERATING_UNIT_ID AND IO_HDR.ITEM_LEVEL = 'I' AND IO_HDR.GENERALLY_AVAILABLE = 'Y' AND IO_HDR.ENABLE_FLAG = 'Y' AND NOT EXISTS ( SELECT 1 FROM OE_ITEM_ORDERABILITY_RULES IO_RULES WHERE IO_RULES.ORDERABILITY_ID = IO_HDR.ORDERABILITY_ID AND IO_RULES.ENABLE_FLAG = 'Y' AND (IO_RULES.CUSTOMER_ID = OE_ITORD_UTIL.GET_CUSTOMER_ID OR IO_RULES.CUSTOMER_CLASS_ID = OE_ITORD_UTIL.GET_CUSTOMER_CLASS_ID OR IO_RULES.CUSTOMER_CATEGORY_CODE = OE_ITORD_UTIL.GET_CUSTOMER_CATEGORY_CODE OR INSTR(OE_ITORD_UTIL.GET_REGION_IDS
, '
, '||TO_CHAR(IO_RULES.REGION_ID)||'
, ' )!= 0 OR IO_RULES.ORDER_TYPE_ID = OE_ITORD_UTIL.GET_ORDER_TYPE_ID OR IO_RULES.SHIP_TO_LOCATION_ID = OE_ITORD_UTIL.GET_SHIP_TO_ORG_ID OR IO_RULES.SALES_CHANNEL_CODE = OE_ITORD_UTIL.GET_SALES_CHANNEL_CODE OR IO_RULES.SALES_PERSON_ID = OE_ITORD_UTIL.GET_SALESREP_ID OR IO_RULES.END_CUSTOMER_ID = OE_ITORD_UTIL.GET_END_CUSTOMER_ID OR IO_RULES.BILL_TO_LOCATION_ID = OE_ITORD_UTIL.GET_INVOICE_TO_ORG_ID OR IO_RULES.DELIVER_TO_LOCATION_ID = OE_ITORD_UTIL.GET_DELIVER_TO_ORG_ID ) ) AND MV.ORGANIZATION_ID = OE_ITORD_UTIL.GET_ITEM_VALIDATION_ORG_ID AND MV.LANGUAGE = USERENV('LANG') AND MV.INVENTORY_ITEM_ID = IO_HDR.INVENTORY_ITEM_ID UNION ALL SELECT MV.ITEM
, MV.ITEM_ID
, MV.ITEM_DESCRIPTION
, MV.INVENTORY_ITEM_ID
, MV.INVENTORY_ITEM
, MV.ITEM_IDENTIFIER_TYPE
, MV.SOLD_TO_ORG_ID
, MV.ORGANIZATION_ID
, MV.ITEM_STATUS
, MV.CROSS_REF_STATUS
, MV.ADDRESS
, MV.CUST_ADDRESS
, MV.ITEM_DEFINITION_LEVEL
, MV.RANK
, MV.SITE_USE_ID
, MV.ITEM_DEFINITION_LEVEL_CODE
, MV.LANGUAGE
, MV.CATEGORY_ID
, MV.ORG_INDEPENDENT_FLAG
, MV.CROSS_REF_ORG_ID FROM OE_ITEMS_MV MV
, OE_ITEM_ORDERABILITY IO_HDR WHERE IO_HDR.ORG_ID = OE_ITORD_UTIL.GET_OPERATING_UNIT_ID AND IO_HDR.ITEM_LEVEL = 'C' AND IO_HDR.GENERALLY_AVAILABLE = 'Y' AND IO_HDR.ENABLE_FLAG = 'Y' AND NOT EXISTS ( SELECT 1 FROM OE_ITEM_ORDERABILITY_RULES IO_RULES WHERE IO_RULES.ORDERABILITY_ID = IO_HDR.ORDERABILITY_ID AND IO_RULES.ENABLE_FLAG = 'Y' AND (IO_RULES.CUSTOMER_ID = OE_ITORD_UTIL.GET_CUSTOMER_ID OR IO_RULES.CUSTOMER_CLASS_ID = OE_ITORD_UTIL.GET_CUSTOMER_CLASS_ID OR IO_RULES.CUSTOMER_CATEGORY_CODE = OE_ITORD_UTIL.GET_CUSTOMER_CATEGORY_CODE OR INSTR(OE_ITORD_UTIL.GET_REGION_IDS
, '
, '||TO_CHAR(IO_RULES.REGION_ID)||'
, ' )!= 0 OR IO_RULES.ORDER_TYPE_ID = OE_ITORD_UTIL.GET_ORDER_TYPE_ID OR IO_RULES.SHIP_TO_LOCATION_ID = OE_ITORD_UTIL.GET_SHIP_TO_ORG_ID OR IO_RULES.SALES_CHANNEL_CODE = OE_ITORD_UTIL.GET_SALES_CHANNEL_CODE OR IO_RULES.SALES_PERSON_ID = OE_ITORD_UTIL.GET_SALESREP_ID OR IO_RULES.END_CUSTOMER_ID = OE_ITORD_UTIL.GET_END_CUSTOMER_ID OR IO_RULES.BILL_TO_LOCATION_ID = OE_ITORD_UTIL.GET_INVOICE_TO_ORG_ID OR IO_RULES.DELIVER_TO_LOCATION_ID = OE_ITORD_UTIL.GET_DELIVER_TO_ORG_ID ) ) AND MV.ORGANIZATION_ID = OE_ITORD_UTIL.GET_ITEM_VALIDATION_ORG_ID AND MV.LANGUAGE = USERENV('LANG') AND MV.CATEGORY_ID = IO_HDR.ITEM_CATEGORY_ID UNION ALL SELECT MV.ITEM
, MV.ITEM_ID
, MV.ITEM_DESCRIPTION
, MV.INVENTORY_ITEM_ID
, MV.INVENTORY_ITEM
, MV.ITEM_IDENTIFIER_TYPE
, MV.SOLD_TO_ORG_ID
, MV.ORGANIZATION_ID
, MV.ITEM_STATUS
, MV.CROSS_REF_STATUS
, MV.ADDRESS
, MV.CUST_ADDRESS
, MV.ITEM_DEFINITION_LEVEL
, MV.RANK
, MV.SITE_USE_ID
, MV.ITEM_DEFINITION_LEVEL_CODE
, MV.LANGUAGE
, MV.CATEGORY_ID
, MV.ORG_INDEPENDENT_FLAG
, MV.CROSS_REF_ORG_ID
FROM OE_ITEMS_MV MV
WHERE MV.ORGANIZATION_ID = OE_ITORD_UTIL.GET_ITEM_VALIDATION_ORG_ID
AND MV.LANGUAGE = USERENV('LANG')
AND NOT EXISTS ( SELECT 1
FROM OE_ITEM_ORDERABILITY IO_HDR
WHERE IO_HDR.INVENTORY_ITEM_ID = MV.INVENTORY_ITEM_ID
AND IO_HDR.ORG_ID = OE_ITORD_UTIL.GET_OPERATING_UNIT_ID
AND IO_HDR.ITEM_LEVEL = 'I'
AND IO_HDR.ENABLE_FLAG = 'Y' UNION ALL SELECT 1
FROM OE_ITEM_ORDERABILITY IO_HDR
WHERE IO_HDR.ITEM_CATEGORY_ID = MV.CATEGORY_ID
AND IO_HDR.ORG_ID = OE_ITORD_UTIL.GET_OPERATING_UNIT_ID
AND IO_HDR.ITEM_LEVEL = 'C'
AND IO_HDR.ENABLE_FLAG = 'Y' )

Columns