VIEW: APPS.JAI_OM_WSH_DELIVERIES_V

Source

View Text - Preformatted

SELECT A.ROWID ROW_ID, A.source_header_number, A.source_header_type_name, A.delivery_detail_id, A.source_header_type_id, A.customer_id, A.ship_to_location_id, A.source_code, A.source_header_id, A.source_line_id, A.cust_po_number, A.sold_to_contact_id, A.inventory_item_id, A.ship_set_id, A.arrival_set_id, A.top_model_line_id, A.ato_line_id, A.hold_code, A.ship_model_complete_flag, A.hazard_class_id, A.country_of_origin, A.classification, A.ship_from_location_id, A.organization_id, A.ship_to_contact_id, A.deliver_to_location_id, A.deliver_to_contact_id, A.intmed_ship_to_location_id, A.intmed_ship_to_contact_id, A.ship_tolerance_above, A.ship_tolerance_below, A.src_requested_quantity, A.src_requested_quantity_uom, A.cancelled_quantity, A.requested_quantity, A.requested_quantity_uom, A.shipped_quantity, A.delivered_quantity, A.quality_control_quantity, A.cycle_count_quantity, A.move_order_line_id, A.subinventory, A.revision, A.lot_number, A.released_status, A.customer_requested_lot_flag, A.serial_number, A.locator_id, A.date_requested, A.date_scheduled, A.master_container_item_id, A.detail_container_item_id, A.load_seq_number, A.ship_method_code, A.carrier_id, A.freight_terms_code, A.shipment_priority_code, A.fob_code, A.customer_item_id, A.DEP_PLAN_REQUIRED_FLAG, A.CUSTOMER_PROD_SEQ, A.CUSTOMER_DOCK_CODE, A.NET_WEIGHT, A.WEIGHT_UOM_CODE, A.VOLUME, A.VOLUME_UOM_CODE, A.SHIPPING_INSTRUCTIONS, A.PACKING_INSTRUCTIONS, A.PROJECT_ID, A.TASK_ID, A.ORG_ID, A.OE_INTERFACED_FLAG, A.MVT_STAT_STATUS, A.TRACKING_NUMBER, A.TRANSACTION_TEMP_ID, A.TP_ATTRIBUTE_CATEGORY, A.TP_ATTRIBUTE1, A.TP_ATTRIBUTE2, A.TP_ATTRIBUTE3, A.TP_ATTRIBUTE4, A.TP_ATTRIBUTE5, A.TP_ATTRIBUTE6, A.TP_ATTRIBUTE7, A.TP_ATTRIBUTE8, A.TP_ATTRIBUTE9, A.TP_ATTRIBUTE10, A.TP_ATTRIBUTE11, A.TP_ATTRIBUTE12, A.TP_ATTRIBUTE13, A.TP_ATTRIBUTE14, A.TP_ATTRIBUTE15, A.ATTRIBUTE_CATEGORY, A.ATTRIBUTE1, A.ATTRIBUTE2, A.ATTRIBUTE3, A.ATTRIBUTE4, A.ATTRIBUTE5, A.ATTRIBUTE6, A.ATTRIBUTE7, A.ATTRIBUTE8, A.ATTRIBUTE9, A.ATTRIBUTE10, A.ATTRIBUTE11, A.ATTRIBUTE12, A.ATTRIBUTE13, A.ATTRIBUTE14, A.ATTRIBUTE15, A.CREATED_BY, A.LAST_UPDATE_LOGIN, A.PROGRAM_APPLICATION_ID, A.PROGRAM_ID, A.PROGRAM_UPDATE_DATE, A.request_id, A.movement_id, A.SPLIT_FROM_DELIVERY_DETAIL_ID, A.inv_interfaced_flag, A.SOURCE_LINE_NUMBER, A.seal_code, A.MINIMUM_FILL_PERCENT, A.maximum_volume, A.MAXIMUM_LOAD_WEIGHT, A.MASTER_SERIAL_NUMBER, A.gross_weight, A.fill_percent, A.container_name, A.CONTAINER_TYPE_CODE, A.CONTAINER_FLAG, A.preferred_grade, A.SRC_REQUESTED_QUANTITY2, A.SRC_REQUESTED_QUANTITY_uom2, A.REQUESTED_QUANTITY2, A.shipped_quantity2, A.DELIVERED_QUANTITY2, A.CANCELLED_QUANTITY2, A.QUALITY_CONTROL_QUANTITY2, A.CYCLE_COUNT_QUANTITY2, A.REQUESTED_QUANTITY_UOM2, A.SUBLOT_NUMBER, A.unit_price, A.CURRENCY_CODE, A.unit_number, A.FREIGHT_CLASS_CAT_ID, A.COMMODITY_CODE_CAT_ID, A.ITEM_DESCRIPTION, A.LAST_UPDATED_BY, A.creation_date, A.LAST_UPDATE_DATE, B.DELIVERY_ID, B.NAME DELIVERY_NAME, B.STATUS_CODE, B.WAYBILL, B.CONFIRM_DATE, B.DELIVERY_TYPE, B.CURRENCY_CODE FREIGHT_CURRENCY, C.DELIVERY_ASSIGNMENT_ID, C.PARENT_DELIVERY_ID, C.PARENT_DELIVERY_DETAIL_ID FROM WSH_NEW_DELIVERIES B, WSH_DELIVERY_ASSIGNMENTS C , WSH_DELIVERY_DETAILS A WHERE C.DELIVERY_ID = B.DELIVERY_ID AND C.DELIVERY_DETAIL_ID = A.DELIVERY_DETAIL_ID AND A.source_code = 'OE' AND A.container_flag = 'N' AND exists (SELECT '1' FROM WSH_DELIVERY_DETAILS p, WSH_DELIVERY_ASSIGNMENTS q WHERE p.delivery_detail_id = q.delivery_detail_id AND p.source_code = 'OE' AND p.container_flag = 'N' AND q.delivery_id = b.delivery_id GROUP BY p.source_header_id, q.delivery_id having min(p.rowid) = a.rowid)

View Text - HTML Formatted

SELECT A.ROWID ROW_ID
, A.SOURCE_HEADER_NUMBER
, A.SOURCE_HEADER_TYPE_NAME
, A.DELIVERY_DETAIL_ID
, A.SOURCE_HEADER_TYPE_ID
, A.CUSTOMER_ID
, A.SHIP_TO_LOCATION_ID
, A.SOURCE_CODE
, A.SOURCE_HEADER_ID
, A.SOURCE_LINE_ID
, A.CUST_PO_NUMBER
, A.SOLD_TO_CONTACT_ID
, A.INVENTORY_ITEM_ID
, A.SHIP_SET_ID
, A.ARRIVAL_SET_ID
, A.TOP_MODEL_LINE_ID
, A.ATO_LINE_ID
, A.HOLD_CODE
, A.SHIP_MODEL_COMPLETE_FLAG
, A.HAZARD_CLASS_ID
, A.COUNTRY_OF_ORIGIN
, A.CLASSIFICATION
, A.SHIP_FROM_LOCATION_ID
, A.ORGANIZATION_ID
, A.SHIP_TO_CONTACT_ID
, A.DELIVER_TO_LOCATION_ID
, A.DELIVER_TO_CONTACT_ID
, A.INTMED_SHIP_TO_LOCATION_ID
, A.INTMED_SHIP_TO_CONTACT_ID
, A.SHIP_TOLERANCE_ABOVE
, A.SHIP_TOLERANCE_BELOW
, A.SRC_REQUESTED_QUANTITY
, A.SRC_REQUESTED_QUANTITY_UOM
, A.CANCELLED_QUANTITY
, A.REQUESTED_QUANTITY
, A.REQUESTED_QUANTITY_UOM
, A.SHIPPED_QUANTITY
, A.DELIVERED_QUANTITY
, A.QUALITY_CONTROL_QUANTITY
, A.CYCLE_COUNT_QUANTITY
, A.MOVE_ORDER_LINE_ID
, A.SUBINVENTORY
, A.REVISION
, A.LOT_NUMBER
, A.RELEASED_STATUS
, A.CUSTOMER_REQUESTED_LOT_FLAG
, A.SERIAL_NUMBER
, A.LOCATOR_ID
, A.DATE_REQUESTED
, A.DATE_SCHEDULED
, A.MASTER_CONTAINER_ITEM_ID
, A.DETAIL_CONTAINER_ITEM_ID
, A.LOAD_SEQ_NUMBER
, A.SHIP_METHOD_CODE
, A.CARRIER_ID
, A.FREIGHT_TERMS_CODE
, A.SHIPMENT_PRIORITY_CODE
, A.FOB_CODE
, A.CUSTOMER_ITEM_ID
, A.DEP_PLAN_REQUIRED_FLAG
, A.CUSTOMER_PROD_SEQ
, A.CUSTOMER_DOCK_CODE
, A.NET_WEIGHT
, A.WEIGHT_UOM_CODE
, A.VOLUME
, A.VOLUME_UOM_CODE
, A.SHIPPING_INSTRUCTIONS
, A.PACKING_INSTRUCTIONS
, A.PROJECT_ID
, A.TASK_ID
, A.ORG_ID
, A.OE_INTERFACED_FLAG
, A.MVT_STAT_STATUS
, A.TRACKING_NUMBER
, A.TRANSACTION_TEMP_ID
, A.TP_ATTRIBUTE_CATEGORY
, A.TP_ATTRIBUTE1
, A.TP_ATTRIBUTE2
, A.TP_ATTRIBUTE3
, A.TP_ATTRIBUTE4
, A.TP_ATTRIBUTE5
, A.TP_ATTRIBUTE6
, A.TP_ATTRIBUTE7
, A.TP_ATTRIBUTE8
, A.TP_ATTRIBUTE9
, A.TP_ATTRIBUTE10
, A.TP_ATTRIBUTE11
, A.TP_ATTRIBUTE12
, A.TP_ATTRIBUTE13
, A.TP_ATTRIBUTE14
, A.TP_ATTRIBUTE15
, A.ATTRIBUTE_CATEGORY
, A.ATTRIBUTE1
, A.ATTRIBUTE2
, A.ATTRIBUTE3
, A.ATTRIBUTE4
, A.ATTRIBUTE5
, A.ATTRIBUTE6
, A.ATTRIBUTE7
, A.ATTRIBUTE8
, A.ATTRIBUTE9
, A.ATTRIBUTE10
, A.ATTRIBUTE11
, A.ATTRIBUTE12
, A.ATTRIBUTE13
, A.ATTRIBUTE14
, A.ATTRIBUTE15
, A.CREATED_BY
, A.LAST_UPDATE_LOGIN
, A.PROGRAM_APPLICATION_ID
, A.PROGRAM_ID
, A.PROGRAM_UPDATE_DATE
, A.REQUEST_ID
, A.MOVEMENT_ID
, A.SPLIT_FROM_DELIVERY_DETAIL_ID
, A.INV_INTERFACED_FLAG
, A.SOURCE_LINE_NUMBER
, A.SEAL_CODE
, A.MINIMUM_FILL_PERCENT
, A.MAXIMUM_VOLUME
, A.MAXIMUM_LOAD_WEIGHT
, A.MASTER_SERIAL_NUMBER
, A.GROSS_WEIGHT
, A.FILL_PERCENT
, A.CONTAINER_NAME
, A.CONTAINER_TYPE_CODE
, A.CONTAINER_FLAG
, A.PREFERRED_GRADE
, A.SRC_REQUESTED_QUANTITY2
, A.SRC_REQUESTED_QUANTITY_UOM2
, A.REQUESTED_QUANTITY2
, A.SHIPPED_QUANTITY2
, A.DELIVERED_QUANTITY2
, A.CANCELLED_QUANTITY2
, A.QUALITY_CONTROL_QUANTITY2
, A.CYCLE_COUNT_QUANTITY2
, A.REQUESTED_QUANTITY_UOM2
, A.SUBLOT_NUMBER
, A.UNIT_PRICE
, A.CURRENCY_CODE
, A.UNIT_NUMBER
, A.FREIGHT_CLASS_CAT_ID
, A.COMMODITY_CODE_CAT_ID
, A.ITEM_DESCRIPTION
, A.LAST_UPDATED_BY
, A.CREATION_DATE
, A.LAST_UPDATE_DATE
, B.DELIVERY_ID
, B.NAME DELIVERY_NAME
, B.STATUS_CODE
, B.WAYBILL
, B.CONFIRM_DATE
, B.DELIVERY_TYPE
, B.CURRENCY_CODE FREIGHT_CURRENCY
, C.DELIVERY_ASSIGNMENT_ID
, C.PARENT_DELIVERY_ID
, C.PARENT_DELIVERY_DETAIL_ID
FROM WSH_NEW_DELIVERIES B
, WSH_DELIVERY_ASSIGNMENTS C
, WSH_DELIVERY_DETAILS A
WHERE C.DELIVERY_ID = B.DELIVERY_ID
AND C.DELIVERY_DETAIL_ID = A.DELIVERY_DETAIL_ID
AND A.SOURCE_CODE = 'OE'
AND A.CONTAINER_FLAG = 'N'
AND EXISTS (SELECT '1'
FROM WSH_DELIVERY_DETAILS P
, WSH_DELIVERY_ASSIGNMENTS Q
WHERE P.DELIVERY_DETAIL_ID = Q.DELIVERY_DETAIL_ID
AND P.SOURCE_CODE = 'OE'
AND P.CONTAINER_FLAG = 'N'
AND Q.DELIVERY_ID = B.DELIVERY_ID GROUP BY P.SOURCE_HEADER_ID
, Q.DELIVERY_ID HAVING MIN(P.ROWID) = A.ROWID)