VIEW: APPS.IBE_RETURNABLE_SERIAL_V

Source

View Text - Preformatted

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(msi.config_model_type,'N','N', 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

View Text - HTML Formatted

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(MSI.CONFIG_MODEL_TYPE
, 'N'
, 'N'
, 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