View: IBE_RETURNABLE_SERIAL_V

Product:	IBE - iStore
Description:	Stores information about order line with the serial numbers of the ordered items
Implementation/DBA Data:	APPS.IBE_RETURNABLE_SERIAL_V

View Text

SELECT DISTINCT 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(OL.TOP_MODEL_LINE_ID
, OL.LINE_ID
, 'Y'
, DECODE(OL.SHIPPABLE_FLAG
, 'N'
, 'N'
, 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
, MSN.SERIAL_NUMBER
, MSN.SERIAL_NUMBER
FROM OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_UNITS_OF_MEASURE_TL MSIT
, OE_ORDER_HEADERS_ALL OH
, MTL_SERIAL_NUMBERS MSN
, MTL_MATERIAL_TRANSACTIONS MMT
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
AND MMT.TRX_SOURCE_LINE_ID = OL.LINE_ID
AND MMT.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2
AND MMT.TRANSACTION_TYPE_ID = 33
AND DECODE(MSI.SERIAL_NUMBER_CONTROL_CODE
, 2
, 'Y'
, 5
, 'Y'
, 6
, 'Y'
, 'N') = 'Y'
AND MMT.TRANSACTION_ID = MSN.LAST_TRANSACTION_ID

Columns