Product: | IBE - iStore |
---|---|
Description: | Stores information about return order detail. |
Implementation/DBA Data: | APPS.IBE_RETURN_DETAIL_V |
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
, OL.LINE_CATEGORY_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
, OL1.ORDERED_QUANTITY
, OL.SHIPPED_QUANTITY
, OL.CANCELLED_QUANTITY
, OL.UNIT_LIST_PRICE
, OL.UNIT_SELLING_PRICE
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OL.HEADER_ID
, OL.LINE_ID
, 'ALL') EXTENDED_PRICE
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OL.HEADER_ID
, OL.LINE_ID
, 'LINES') LINES_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OL.HEADER_ID
, OL.LINE_ID
, 'TAXES') TAXES_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OL.HEADER_ID
, OL.LINE_ID
, 'CHARGES') CHARGES_TOTAL
, OL.SCHEDULE_SHIP_DATE
, OL.REQUEST_DATE
, OL.PROMISE_DATE
, OL.FLOW_STATUS_CODE
, OLKP1.MEANING FLOW_STATUS_DESC
, OL.SHIPMENT_NUMBER
, OL.OPTION_NUMBER
, OL.COMPONENT_NUMBER
, OL.SERVICE_NUMBER
, OH.SOLD_TO_ORG_ID
, OL.INVOICE_TO_CONTACT_ID
, OL.INVOICE_TO_ORG_ID
, OL.SHIP_TO_CONTACT_ID
, OL.SHIP_TO_ORG_ID
, OL.RETURN_CONTEXT
, OH1.ORDER_NUMBER
, DECODE(OL.RETURN_CONTEXT
, 'ORDER'
, OL.RETURN_ATTRIBUTE1) SOURCE_HEADER_ID
, DECODE(OL.RETURN_CONTEXT
, 'ORDER'
, OL.RETURN_ATTRIBUTE2) SOURCE_LINE_ID
, OH1.ORDER_CATEGORY_CODE
, MSI.RETURNABLE_FLAG
, OL.RETURN_REASON_CODE
, OL1.ITEM_TYPE_CODE
, OL1.TOP_MODEL_LINE_ID
, OL1.LINK_TO_LINE_ID
, OH.FLOW_STATUS_CODE
FROM OE_ORDER_LINES_ALL OL
, OE_ORDER_HEADERS_ALL OH
, MTL_SYSTEM_ITEMS_VL MSI
, OE_LOOKUPS OLKP1
, MTL_UNITS_OF_MEASURE_TL MSIT
, OE_ORDER_LINES_ALL OL1
, OE_ORDER_HEADERS_ALL OH1
WHERE OL.HEADER_ID = OH.HEADER_ID
AND OL.LINE_CATEGORY_CODE = 'RETURN'
AND OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OE_PROFILE.VALUE('OE_ORGANIZATION_ID'
, OL.ORG_ID)
AND OL.FLOW_STATUS_CODE = OLKP1.LOOKUP_CODE
AND OLKP1.LOOKUP_TYPE = 'LINE_FLOW_STATUS'
AND OL.ORDER_QUANTITY_UOM = MSIT.UOM_CODE
AND MSIT.LANGUAGE = USERENV('LANG')
AND OL.RETURN_ATTRIBUTE1 = OL1.HEADER_ID(+)
AND OL.RETURN_ATTRIBUTE2 = OL1.LINE_ID(+)
AND OL1.HEADER_ID = OH1.HEADER_ID(+)