CREATE OR REPLACE FORCE VIEW REGAL.RHC_ORDERS_BY_ITEM_VL
(INVENTORY_ITEM_ID,
 ORGANIZATION_ID,
 UNIT_SELLING_PRICE, 
 ITEM_COST, ITEM,
 ITEM_DESCRIPTION,
 ITEM_UNIT_LENGTH,
 ITEM_UNIT_WIDTH,
 ITEM_UNIT_HEIGHT,
 ITEM_UNIT_WEIGHT,
 ITEM_UNIT_VOLUME,
 ITEM_STYLE,
 ITEM_CUSTOMER,
 ITEM_SIZE,
 ITEM_COLOUR, 
 ORDER_NUMBER,
 ORDER_TYPE,
 ORDERED_DATE,
 CUSTOMER_NUMBER,
 CUSTOMER_NAME,
 ORDERED_QUANTITY,
 CANCELLED_QUANTITY,
 RELEASED_STATUS,
 SHIPPED_QUANTITY,
 BACKORDER_QUANTITY, 
 PICKRELEASED_QUANTITY,
 QUANTITY_OPEN,
 ONHAND_QUANTITY,
 CANCEL_DATE,
 SHIP_DATE, 
 SHIP_TO_DC,
 SHIP_TO_CONS,
 CUSTOMER_PO_NUMBER,
 SHIP_TO_STORE,
 SHIP_TO_ADDRESS1, 
 SHIP_TO_ADDRESS2,
 SHIP_TO_ADDRESS3,
 SHIP_TO_CITY,
 SHIP_TO_STATE,
 SHIP_TO_ZIP, 
 PACK_SIZE,
 ORDER_SOURCE_LINE_REFERENCE)
AS 
SELECT 
  mtl_v.inventory_item_id, 
  mtl_v.organization_id, 
  x.item_price unit_selling_price, 
  ROUND(ctc.item_cost,  3) item_cost, 
  mtl_v.concatenated_segments  item, 
  mtl_v.description item_description, 
  mtl_v.unit_length item_unit_length, 
  mtl_v.unit_width item_unit_width, 
  mtl_v.unit_height item_unit_height, 
  mtl_v.unit_weight item_unit_weight, 
  mtl_v.unit_volume item_unit_volume, 
  mtl_v.segment1 item_style, 
  mtl_v.segment2 item_customer, 
  mtl_v.segment3 item_size, 
  mtl_v.segment4  item_colour, 
  null order_number, 
  sot.NAME order_type, 
  TO_DATE(x.date_ordered,'DD-MON-YYYY') ordered_date, 
  rac.customer_number, 
  rac.customer_name, 
  x.ITEM_ORDER_QTY ordered_quantity, 
  null cancelled_quantity, 
  null released_status, 
  null shipped_quantity, 
  null backorder_quantity, 
  null pickreleased_quantity, 
  null quantity_open, 
  null On_hand_quantity,	 --NVL(apps.rhc_shared_pkg.get_onhand(mtl.inventory_item_id,  mtl.organization_id),  0) onhand_quantity, 
  TO_DATE(x.REQ_CANCEL_DATE,  'DD-MON-RRRR') cancel_date, 
  TO_DATE(x.REQ_SHIP_DATE,  'DD-MON-RRRR') ship_date , 
  x.SHIP_TO_DC Ship_to_DC, 
  x.SHIP_TO_CONS ship_to_cons, 
  x.PURCHASE_ORDER_NUM customer_po_number, 
  rsus.LOCATION  ship_to_store, 
  rad.address1 ship_to_address1, 
  rad.address2 ship_to_address2, 
  rad.address3 ship_to_address3, 
  rad.city ship_to_city, 
  rad.state ship_to_state, 
  rad.postal_code ship_to_zip, 
  c.conversion_rate pack_size , 
  x.ORIGINAL_SYSTEM_LINE_REF 
FROM 
  apps.so_order_types_all		 sot, 
  apps.mtl_system_items           	 mtl, 
  apps.mtl_system_items_kfv	         mtl_v, 
  apps.mtl_uom_class_conversions  	 c, 
  apps.ra_customers			 rac, 
  apps.ra_site_uses_all                  rsus , 
  apps.ra_addresses_all 		 rad, 
  regal.REGAL_OM_EDI850_WORKTAB_TEMP	 x, 
  apps.cst_item_costs                    ctc 
WHERE 
  x.INVENTORY_ITEM_ID       = mtl.inventory_item_id          		AND 
  x.ORDER_TYPE_ID 	    = sot.order_type_id				AND 
  mtl.organization_id       =81				    		AND 	
  mtl_v.inventory_item_id   = mtl.inventory_item_id           		AND 
  mtl_v.organization_id     = mtl.organization_id  		 	AND 
  mtl_v.inventory_item_id   = ctc.inventory_item_id           		AND 
  mtl_v.organization_id     = ctc.organization_id  		 	AND 
  ctc.cost_type_id = 2                                  		AND 
  mtl_v.organization_id     = mtl.organization_id  		 	AND 
  --rac.customer_id           = x.SHIP_TO_ORG_ID  			AND 	
  rad.address_id = rsus.address_id 					AND 
  rsus.site_use_code = 'SHIP_TO' 					AND 
  rsus.status = 'A' 							AND 
  rsus.org_id = 81							AND       
  rad.customer_id = rac.customer_id 					AND 
  c.from_uom_code(+) = mtl_v.primary_uom_code 				AND 
  mtl_v.inventory_item_id = c.inventory_item_id(+) 			AND 
  mtl_v.inventory_item_status_code = 'Active' 				AND 
  c.disable_date IS NULL  						AND 
  x.SHIP_TO_ORG_ID = rsus.site_use_id;