VIEW: APPS.OE_DELIVERY_LINES_V

Source

View Text - Preformatted

SELECT ool.header_id , ooh.order_number , ooh.order_type_id , ott.name , ool.line_id , ool.line_number||'.'||ool.shipment_number||decode(ool.service_number,'',decode(ool.component_number,'',decode(ool.option_number,'','','.'),'.'),'.')||ool.option_number||decode(ool.service_number,'',decode(ool.component_number,'','','.'),'.')||ool.component_number||decode(ool.service_number,'','','.')||ool.service_number line_number , ool.org_id , ool.sold_to_org_id , ool.inventory_item_id , msi.description , ool.ship_from_org_id , ool.subinventory , ool.ship_to_org_id , ool.deliver_to_org_id , ool.ship_to_contact_id , ool.deliver_to_contact_id , ool.intmed_ship_to_org_id , ool.intmed_ship_to_contact_id , ool.ship_tolerance_above , ool.ship_tolerance_below , ool.ordered_quantity , ool.shipped_quantity , ool.fulfilled_quantity , ool.order_quantity_uom , ool.shipping_quantity_uom , ool.shipping_quantity , ool.schedule_ship_date , ool.shipping_method_code , ool.freight_carrier_code , ool.freight_terms_code , ool.shipment_priority_code , ool.fob_point_code , ool.item_identifier_type , ool.ordered_item_id , ool.request_date , ool.dep_plan_required_flag , ool.cust_production_seq_num , ool.customer_dock_code , ool.shipping_interfaced_flag , ool.ship_set_id , ool.ato_line_id , ool.ship_model_complete_flag , ool.top_model_line_id , ool.item_type_code , ool.cust_po_number , ool.arrival_set_id , ool.line_category_code , ool.source_type_code , ool.line_type_id , ool.project_id , ool.task_id , ool.shipping_instructions , ool.packing_instructions , mci.master_container_item_id , mci.detail_container_item_id , ool.preferred_grade , ool.ordered_quantity2 , ool.ordered_quantity_uom2 , ool.unit_list_price , ooh.transactional_curr_code , ool.end_item_unit_number , ool.tp_context , ool.tp_attribute1 , ool.tp_attribute2 , ool.tp_attribute3 , ool.tp_attribute4 , ool.tp_attribute5 , ool.tp_attribute6 , ool.tp_attribute7 , ool.tp_attribute8 , ool.tp_attribute9 , ool.tp_attribute10 , ool.tp_attribute11 , ool.tp_attribute12 , ool.tp_attribute13 , ool.tp_attribute14 , ool.tp_attribute15 , ooh.sold_to_contact_id , ool.customer_job , ool.customer_production_line , ool.cust_model_serial_number , ool.line_set_id , ool.source_document_type_id , ooh.order_date_type_code , ool.schedule_arrival_date , ool.promise_date , ool.earliest_acceptable_date , ool.latest_acceptable_date , ool.earliest_ship_date FROM OE_ORDER_LINES_ALL ool, OE_TRANSACTION_TYPES_TL ott, MTL_SYSTEM_ITEMS msi, MTL_CUSTOMER_ITEMS mci, OE_ORDER_HEADERS_ALL ooh WHERE ool.header_id = ooh.header_id and ott.transaction_type_id = ooh.order_type_id and ott.LANGUAGE = userenv('LANG') and ool.open_flag = 'Y' and ool.shipping_interfaced_flag = 'N' and ool.shippable_flag = 'Y' and nvl(ool.booked_flag,'N') = 'Y' and nvl(ool.cancelled_flag,'N') = 'N' and nvl(ool.authorized_to_ship_flag,'Y') = 'Y' and msi.organization_id = oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id) and msi.inventory_item_id = ool.inventory_item_id and mci.customer_item_id(+) = ool.ordered_item_id and ool.source_type_code <> 'EXTERNAL' and ool.line_category_code = 'ORDER'

View Text - HTML Formatted

SELECT OOL.HEADER_ID
, OOH.ORDER_NUMBER
, OOH.ORDER_TYPE_ID
, OTT.NAME
, OOL.LINE_ID
, OOL.LINE_NUMBER||'.'||OOL.SHIPMENT_NUMBER||DECODE(OOL.SERVICE_NUMBER
, ''
, DECODE(OOL.COMPONENT_NUMBER
, ''
, DECODE(OOL.OPTION_NUMBER
, ''
, ''
, '.')
, '.')
, '.')||OOL.OPTION_NUMBER||DECODE(OOL.SERVICE_NUMBER
, ''
, DECODE(OOL.COMPONENT_NUMBER
, ''
, ''
, '.')
, '.')||OOL.COMPONENT_NUMBER||DECODE(OOL.SERVICE_NUMBER
, ''
, ''
, '.')||OOL.SERVICE_NUMBER LINE_NUMBER
, OOL.ORG_ID
, OOL.SOLD_TO_ORG_ID
, OOL.INVENTORY_ITEM_ID
, MSI.DESCRIPTION
, OOL.SHIP_FROM_ORG_ID
, OOL.SUBINVENTORY
, OOL.SHIP_TO_ORG_ID
, OOL.DELIVER_TO_ORG_ID
, OOL.SHIP_TO_CONTACT_ID
, OOL.DELIVER_TO_CONTACT_ID
, OOL.INTMED_SHIP_TO_ORG_ID
, OOL.INTMED_SHIP_TO_CONTACT_ID
, OOL.SHIP_TOLERANCE_ABOVE
, OOL.SHIP_TOLERANCE_BELOW
, OOL.ORDERED_QUANTITY
, OOL.SHIPPED_QUANTITY
, OOL.FULFILLED_QUANTITY
, OOL.ORDER_QUANTITY_UOM
, OOL.SHIPPING_QUANTITY_UOM
, OOL.SHIPPING_QUANTITY
, OOL.SCHEDULE_SHIP_DATE
, OOL.SHIPPING_METHOD_CODE
, OOL.FREIGHT_CARRIER_CODE
, OOL.FREIGHT_TERMS_CODE
, OOL.SHIPMENT_PRIORITY_CODE
, OOL.FOB_POINT_CODE
, OOL.ITEM_IDENTIFIER_TYPE
, OOL.ORDERED_ITEM_ID
, OOL.REQUEST_DATE
, OOL.DEP_PLAN_REQUIRED_FLAG
, OOL.CUST_PRODUCTION_SEQ_NUM
, OOL.CUSTOMER_DOCK_CODE
, OOL.SHIPPING_INTERFACED_FLAG
, OOL.SHIP_SET_ID
, OOL.ATO_LINE_ID
, OOL.SHIP_MODEL_COMPLETE_FLAG
, OOL.TOP_MODEL_LINE_ID
, OOL.ITEM_TYPE_CODE
, OOL.CUST_PO_NUMBER
, OOL.ARRIVAL_SET_ID
, OOL.LINE_CATEGORY_CODE
, OOL.SOURCE_TYPE_CODE
, OOL.LINE_TYPE_ID
, OOL.PROJECT_ID
, OOL.TASK_ID
, OOL.SHIPPING_INSTRUCTIONS
, OOL.PACKING_INSTRUCTIONS
, MCI.MASTER_CONTAINER_ITEM_ID
, MCI.DETAIL_CONTAINER_ITEM_ID
, OOL.PREFERRED_GRADE
, OOL.ORDERED_QUANTITY2
, OOL.ORDERED_QUANTITY_UOM2
, OOL.UNIT_LIST_PRICE
, OOH.TRANSACTIONAL_CURR_CODE
, OOL.END_ITEM_UNIT_NUMBER
, OOL.TP_CONTEXT
, OOL.TP_ATTRIBUTE1
, OOL.TP_ATTRIBUTE2
, OOL.TP_ATTRIBUTE3
, OOL.TP_ATTRIBUTE4
, OOL.TP_ATTRIBUTE5
, OOL.TP_ATTRIBUTE6
, OOL.TP_ATTRIBUTE7
, OOL.TP_ATTRIBUTE8
, OOL.TP_ATTRIBUTE9
, OOL.TP_ATTRIBUTE10
, OOL.TP_ATTRIBUTE11
, OOL.TP_ATTRIBUTE12
, OOL.TP_ATTRIBUTE13
, OOL.TP_ATTRIBUTE14
, OOL.TP_ATTRIBUTE15
, OOH.SOLD_TO_CONTACT_ID
, OOL.CUSTOMER_JOB
, OOL.CUSTOMER_PRODUCTION_LINE
, OOL.CUST_MODEL_SERIAL_NUMBER
, OOL.LINE_SET_ID
, OOL.SOURCE_DOCUMENT_TYPE_ID
, OOH.ORDER_DATE_TYPE_CODE
, OOL.SCHEDULE_ARRIVAL_DATE
, OOL.PROMISE_DATE
, OOL.EARLIEST_ACCEPTABLE_DATE
, OOL.LATEST_ACCEPTABLE_DATE
, OOL.EARLIEST_SHIP_DATE
FROM OE_ORDER_LINES_ALL OOL
, OE_TRANSACTION_TYPES_TL OTT
, MTL_SYSTEM_ITEMS MSI
, MTL_CUSTOMER_ITEMS MCI
, OE_ORDER_HEADERS_ALL OOH
WHERE OOL.HEADER_ID = OOH.HEADER_ID
AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID
AND OTT.LANGUAGE = USERENV('LANG')
AND OOL.OPEN_FLAG = 'Y'
AND OOL.SHIPPING_INTERFACED_FLAG = 'N'
AND OOL.SHIPPABLE_FLAG = 'Y'
AND NVL(OOL.BOOKED_FLAG
, 'N') = 'Y'
AND NVL(OOL.CANCELLED_FLAG
, 'N') = 'N'
AND NVL(OOL.AUTHORIZED_TO_SHIP_FLAG
, 'Y') = 'Y'
AND MSI.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
, OOL.ORG_ID)
AND MSI.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID
AND MCI.CUSTOMER_ITEM_ID(+) = OOL.ORDERED_ITEM_ID
AND OOL.SOURCE_TYPE_CODE <> 'EXTERNAL'
AND OOL.LINE_CATEGORY_CODE = 'ORDER'