Product: | JA - Asia/Pacific Localizations |
---|---|
Description: | |
Implementation/DBA Data: | APPS.JAI_OM_WSH_DELIVERIES_V |
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)