VIEW: APPS.IBE_RETURNABLE_LINES_V

Source

View Text - Preformatted

SELECT ol.line_id, ol.header_id, oh.order_number , ol.line_number, ol.top_model_line_id, ol.link_to_line_id, ol.ato_line_id, oh.org_id, oh.order_category_code, oh.flow_status_code, ol.line_category_code, ol.flow_status_code, oh.transactional_curr_code, ol.inventory_item_id, ol.item_type_code, msi.concatenated_segments item_number, msi.description item_description, msi.attribute7 product_description, ol.order_quantity_uom, msit.unit_of_measure_tl UOM_NAME, ol.ordered_quantity, ol.shipped_quantity, ol.cancelled_quantity, ol.unit_list_price, ol.unit_selling_price, ol.schedule_ship_date, ol.request_date, ol.promise_date, ol.SHIPMENT_NUMBER, ol.OPTION_NUMBER, ol.COMPONENT_NUMBER, ol.SERVICE_NUMBER, decode(msi.config_model_type,'N','N', decode(ol.top_model_line_id,ol.line_id,'Y', decode(NVL(msi.returnable_flag,'Y'),'N','N', decode(ol.item_type_code,'SERVICE','N', decode(ol.line_category_code,'RETURN','N', decode(oh.flow_status_code, 'ENTERED','N','CANCELLED','N','Y') ) ) ) ) ) line_returnable_flag, oh.sold_to_org_id, oh.created_by, ol.booked_flag, ol.cancelled_flag, decode(msi.serial_number_control_code,2,'Y',5,'Y',6,'Y','N') SERIAL_CONTROL_FLAG FROM oe_order_lines_all ol, mtl_system_items_vl msi, mtl_units_of_measure_tl msit, oe_order_headers_all oh WHERE ol.inventory_item_id = msi.inventory_item_Id AND msi.organization_id = oe_profile.value('OE_ORGANIZATION_ID', ol.org_id) and ol.order_quantity_uom = msit.uom_code and msit.language = userenv('LANG') and oh.header_id = ol.header_id

View Text - HTML Formatted

SELECT OL.LINE_ID
, OL.HEADER_ID
, OH.ORDER_NUMBER
, OL.LINE_NUMBER
, OL.TOP_MODEL_LINE_ID
, OL.LINK_TO_LINE_ID
, OL.ATO_LINE_ID
, OH.ORG_ID
, OH.ORDER_CATEGORY_CODE
, OH.FLOW_STATUS_CODE
, OL.LINE_CATEGORY_CODE
, OL.FLOW_STATUS_CODE
, OH.TRANSACTIONAL_CURR_CODE
, OL.INVENTORY_ITEM_ID
, OL.ITEM_TYPE_CODE
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.ATTRIBUTE7 PRODUCT_DESCRIPTION
, OL.ORDER_QUANTITY_UOM
, MSIT.UNIT_OF_MEASURE_TL UOM_NAME
, OL.ORDERED_QUANTITY
, OL.SHIPPED_QUANTITY
, OL.CANCELLED_QUANTITY
, OL.UNIT_LIST_PRICE
, OL.UNIT_SELLING_PRICE
, OL.SCHEDULE_SHIP_DATE
, OL.REQUEST_DATE
, OL.PROMISE_DATE
, OL.SHIPMENT_NUMBER
, OL.OPTION_NUMBER
, OL.COMPONENT_NUMBER
, OL.SERVICE_NUMBER
, DECODE(MSI.CONFIG_MODEL_TYPE
, 'N'
, 'N'
, DECODE(OL.TOP_MODEL_LINE_ID
, OL.LINE_ID
, 'Y'
, DECODE(NVL(MSI.RETURNABLE_FLAG
, 'Y')
, 'N'
, 'N'
, DECODE(OL.ITEM_TYPE_CODE
, 'SERVICE'
, 'N'
, DECODE(OL.LINE_CATEGORY_CODE
, 'RETURN'
, 'N'
, DECODE(OH.FLOW_STATUS_CODE
, 'ENTERED'
, 'N'
, 'CANCELLED'
, 'N'
, 'Y') ) ) ) ) ) LINE_RETURNABLE_FLAG
, OH.SOLD_TO_ORG_ID
, OH.CREATED_BY
, OL.BOOKED_FLAG
, OL.CANCELLED_FLAG
, DECODE(MSI.SERIAL_NUMBER_CONTROL_CODE
, 2
, 'Y'
, 5
, 'Y'
, 6
, 'Y'
, 'N') SERIAL_CONTROL_FLAG
FROM OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_UNITS_OF_MEASURE_TL MSIT
, OE_ORDER_HEADERS_ALL OH
WHERE OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OE_PROFILE.VALUE('OE_ORGANIZATION_ID'
, OL.ORG_ID)
AND OL.ORDER_QUANTITY_UOM = MSIT.UOM_CODE
AND MSIT.LANGUAGE = USERENV('LANG')
AND OH.HEADER_ID = OL.HEADER_ID