VIEW: APPS.IBE_RETURN_DETAIL_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, 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.bom_item_type bom_item_type, 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(+)

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
, 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.BOM_ITEM_TYPE BOM_ITEM_TYPE
, 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(+)