Thursday, September 6, 2012

Query to get the list of Item Orderability Rules defined in Order Management

SELECT oio.item_level,
       'Inventory Item' item_level_dsp,
       b.concatenated_segments item,
       t.description item_description,
       oio.item_category_id,
       oio.generally_available,
       oio.enable_flag,
       oior.rule_level,
       oior.enable_flag rule_enable_flag,
       DECODE (rule_level,

               'CUSTOMER', 'Customer',
               'CUST_CLASS', 'Customer Class',
               'CUST_CATEGORY', 'Customer Category',
               'END_CUST', 'End Customer',
               'ORDER_TYPE', 'Order Type',
               'SALES_CHANNEL', 'Sales Channel',
               'SALES_REP', 'Sales Person',
               'SHIP_TO_LOC', 'Ship to Location',
               'BILL_TO_LOC', 'Bill To Location',
               'DELIVER_TO_LOC', 'Deliver To location',
               'REGIONS', 'Regions')
          rule_level_dsp,
       customer_id,
       customer_class_id,
       customer_category_code,
       end_customer_id,
       order_type_id,
       sales_channel_code,
       sales_person_id,
       ship_to_location_id,
       bill_to_location_id,
       deliver_to_location_id,
       region_id,
       NULL C_CATEGORY_FLEXDATA
  FROM oe_item_orderability oio,
       mtl_system_items_tl t,
       mtl_system_items_b_kfv b,
       oe_item_orderability_rules oior
 WHERE oio.orderability_id = oior.orderability_id(+)
       AND oio.inventory_item_id = b.inventory_item_id
       AND t.organization_id =
              oe_sys_parameters.VALUE ('MASTER_ORGANIZATION_ID')
       AND b.inventory_item_id = t.inventory_item_id
       AND b.organization_id = t.organization_id
       AND t.LANGUAGE = USERENV ('LANG')
UNION ALL
SELECT oio.item_level,
       'Item Category' item_level_dsp,
       NULL item,
       NULL item_description,
       oio.item_category_id,
       oio.generally_available,
       oio.enable_flag,
       oior.rule_level,
       oior.enable_flag rule_enable_flag,
       DECODE (rule_level,
               'CUSTOMER', 'Customer',
               'CUST_CLASS', 'Customer Class',
               'CUST_CATEGORY', 'Customer Category',
               'END_CUST', 'End Customer',
               'ORDER_TYPE', 'Order Type',
               'SALES_CHANNEL', 'Sales Channel',
               'SALES_REP', 'Sales Person',
               'SHIP_TO_LOC', 'Ship to Location',
               'BILL_TO_LOC', 'Bill To Location',
               'DELIVER_TO_LOC', 'Deliver To location',
               'REGIONS', 'Regions')
          rule_level_dsp,
       customer_id,
       customer_class_id,
       customer_category_code,
       end_customer_id,
       order_type_id,
       sales_channel_code,
       sales_person_id,
       ship_to_location_id,
       bill_to_location_id,
       deliver_to_location_id,
       region_id
  FROM oe_item_orderability oio,
       mtl_categories_b mcb,
       oe_item_orderability_rules oior
 WHERE oio.orderability_id = oior.orderability_id(+)
       AND oio.item_category_id = mcb.category_id