DBA Data[Home] [Help]

VIEW: APPS.MST_WSH_DEL_DET_NOTPACKED_V

Source

View Text - Preformatted

SELECT wdd.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID , wdd.SPLIT_FROM_DELIVERY_DETAIL_ID SPLIT_FROM_DELIVERY_DETAIL_ID , nvl(wdd.CONTAINER_FLAG,'N') CONTAINER_FLAG , wdd.VENDOR_ID VENDOR_ID , wdd.ORGANIZATION_ID ORGANIZATION_ID , wdd.SHIP_FROM_LOCATION_ID SHIP_FROM_LOCATION_ID , decode(wdd.SOURCE_DOCUMENT_TYPE_ID,10,ploc.DESTINATION_ORGANIZATION_ID,NULL) DESTINATION_ORGANIZATION_ID , wdd.CUSTOMER_ID CUSTOMER_ID , nvl(ploc.location_id,wdd.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID , wdd.EARLIEST_PICKUP_DATE EARLIEST_AVAILABLE_TO_SHIP , wdd.LATEST_PICKUP_DATE LATEST_AVAILABLE_TO_SHIP , wdd.EARLIEST_DROPOFF_DATE EARLIEST_DELIVERY_DATE , wdd.LATEST_DROPOFF_DATE LATEST_DELIVERY_DATE , wdd.NET_WEIGHT NET_WEIGHT , wdd.GROSS_WEIGHT GROSS_WEIGHT , wdd.VOLUME VOLUME , wdd.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , wdd.SHIP_SET_ID SHIP_SET_ID , wdd.ARRIVAL_SET_ID ARRIVAL_SET_ID , wdd.SHIP_MODEL_COMPLETE_FLAG SHIP_MODEL_COMPLETE_FLAG , wdd.TOP_MODEL_LINE_ID TOP_MODEL_LINE_ID , wdd.ORG_ID ORG_ID , wdd.FREIGHT_CLASS_CAT_ID FREIGHT_CLASS_CAT_ID , wdd.COMMODITY_CODE_CAT_ID COMMODITY_CODE_CAT_ID , wdd.SOURCE_HEADER_NUMBER SOURCE_HEADER_NUMBER , wdd.SOURCE_LINE_NUMBER SOURCE_LINE_NUMBER , decode(wdd.SOURCE_CODE,'OE', wdd.CUST_PO_NUMBER, NULL) REFERENCE_HEADER_NUMBER , to_char(NULL) REFERENCE_LINE_NUMBER , decode(wdd.SOURCE_CODE, 'OE', decode(wdd.CUST_PO_NUMBER, NULL, NULL, 'PO') , NULL ) REFERENCE_SOURCE_TYPE , nvl(wdd.SHIPPED_QUANTITY , nvl(wdd.PICKED_QUANTITY, wdd.REQUESTED_QUANTITY)) TP_QUANTITY , decode(wdd.SHIPPED_QUANTITY , null, decode(wdd.PICKED_QUANTITY, null, 'REQUESTED', 'PICKED'), 'SHIPPED') TP_QUANTITY_TYPE , wdd.SOURCE_CODE SOURCE_CODE , wdd.SOURCE_DOCUMENT_TYPE_ID SOURCE_DOCUMENT_TYPE_ID , RELEASED_STATUS RELEASED_STATUS, CONTAINER_TYPE_CODE CONTAINER_TYPE_CODE , wdd.created_by CREATED_BY , wdd.last_updated_by LAST_UPDATED_BY , wdd.weight_uom_code WEIGHT_UOM_CODE , wdd.volume_uom_code VOLUME_UOM_CODE , wdd.ignore_for_planning ignore_for_planning , wdd.line_direction line_direction , wdd.UNIT_PRICE UNIT_PRICE , wdd.CURRENCY_CODE CURRENCY_CODE , decode(wdd.SOURCE_LINE_TYPE_CODE, 'GB_OSP','Y','N') OSP_FLAG , wdd.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE , 3 DETAIL_TYPE , wdd.PO_SHIPMENT_LINE_NUMBER PO_SHIPMENT_LINE_NUMBER , to_number(NULL) ALLOCATED_COST , wdd.ITEM_DESCRIPTION ITEM_DESCRIPTION , wdd.SRC_REQUESTED_QUANTITY SRC_REQUESTED_QUANTITY , wdd.SRC_REQUESTED_QUANTITY_UOM SRC_REQUESTED_QUANTITY_UOM FROM WSH_DELIVERY_DETAILS wdd , MST_PO_LOCATION_ASSO_V ploc WHERE CONTAINER_FLAG = 'N' AND nvl(wdd.ignore_for_planning, 'N') = 'N' and wdd.ship_to_location_id = ploc.cust_location_id (+) AND NOT EXISTS ( SELECT 1 from WSH_DELIVERY_ASSIGNMENTS assg WHERE assg.delivery_detail_id = wdd.delivery_detail_id AND assg.parent_delivery_detail_id is not null) AND NOT EXISTS ( SELECT 1 from wsh_Delivery_assignments assg, MST_WSH_NEW_DELIVERIES_V del WHERE wdd.delivery_detail_id = assg.delivery_Detail_id AND assg.delivery_id = del.delivery_id AND del.planned_flag in ('Y', 'F') )
View Text - HTML Formatted

SELECT WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
, WDD.SPLIT_FROM_DELIVERY_DETAIL_ID SPLIT_FROM_DELIVERY_DETAIL_ID
, NVL(WDD.CONTAINER_FLAG
, 'N') CONTAINER_FLAG
, WDD.VENDOR_ID VENDOR_ID
, WDD.ORGANIZATION_ID ORGANIZATION_ID
, WDD.SHIP_FROM_LOCATION_ID SHIP_FROM_LOCATION_ID
, DECODE(WDD.SOURCE_DOCUMENT_TYPE_ID
, 10
, PLOC.DESTINATION_ORGANIZATION_ID
, NULL) DESTINATION_ORGANIZATION_ID
, WDD.CUSTOMER_ID CUSTOMER_ID
, NVL(PLOC.LOCATION_ID
, WDD.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID
, WDD.EARLIEST_PICKUP_DATE EARLIEST_AVAILABLE_TO_SHIP
, WDD.LATEST_PICKUP_DATE LATEST_AVAILABLE_TO_SHIP
, WDD.EARLIEST_DROPOFF_DATE EARLIEST_DELIVERY_DATE
, WDD.LATEST_DROPOFF_DATE LATEST_DELIVERY_DATE
, WDD.NET_WEIGHT NET_WEIGHT
, WDD.GROSS_WEIGHT GROSS_WEIGHT
, WDD.VOLUME VOLUME
, WDD.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, WDD.SHIP_SET_ID SHIP_SET_ID
, WDD.ARRIVAL_SET_ID ARRIVAL_SET_ID
, WDD.SHIP_MODEL_COMPLETE_FLAG SHIP_MODEL_COMPLETE_FLAG
, WDD.TOP_MODEL_LINE_ID TOP_MODEL_LINE_ID
, WDD.ORG_ID ORG_ID
, WDD.FREIGHT_CLASS_CAT_ID FREIGHT_CLASS_CAT_ID
, WDD.COMMODITY_CODE_CAT_ID COMMODITY_CODE_CAT_ID
, WDD.SOURCE_HEADER_NUMBER SOURCE_HEADER_NUMBER
, WDD.SOURCE_LINE_NUMBER SOURCE_LINE_NUMBER
, DECODE(WDD.SOURCE_CODE
, 'OE'
, WDD.CUST_PO_NUMBER
, NULL) REFERENCE_HEADER_NUMBER
, TO_CHAR(NULL) REFERENCE_LINE_NUMBER
, DECODE(WDD.SOURCE_CODE
, 'OE'
, DECODE(WDD.CUST_PO_NUMBER
, NULL
, NULL
, 'PO')
, NULL ) REFERENCE_SOURCE_TYPE
, NVL(WDD.SHIPPED_QUANTITY
, NVL(WDD.PICKED_QUANTITY
, WDD.REQUESTED_QUANTITY)) TP_QUANTITY
, DECODE(WDD.SHIPPED_QUANTITY
, NULL
, DECODE(WDD.PICKED_QUANTITY
, NULL
, 'REQUESTED'
, 'PICKED')
, 'SHIPPED') TP_QUANTITY_TYPE
, WDD.SOURCE_CODE SOURCE_CODE
, WDD.SOURCE_DOCUMENT_TYPE_ID SOURCE_DOCUMENT_TYPE_ID
, RELEASED_STATUS RELEASED_STATUS
, CONTAINER_TYPE_CODE CONTAINER_TYPE_CODE
, WDD.CREATED_BY CREATED_BY
, WDD.LAST_UPDATED_BY LAST_UPDATED_BY
, WDD.WEIGHT_UOM_CODE WEIGHT_UOM_CODE
, WDD.VOLUME_UOM_CODE VOLUME_UOM_CODE
, WDD.IGNORE_FOR_PLANNING IGNORE_FOR_PLANNING
, WDD.LINE_DIRECTION LINE_DIRECTION
, WDD.UNIT_PRICE UNIT_PRICE
, WDD.CURRENCY_CODE CURRENCY_CODE
, DECODE(WDD.SOURCE_LINE_TYPE_CODE
, 'GB_OSP'
, 'Y'
, 'N') OSP_FLAG
, WDD.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE
, 3 DETAIL_TYPE
, WDD.PO_SHIPMENT_LINE_NUMBER PO_SHIPMENT_LINE_NUMBER
, TO_NUMBER(NULL) ALLOCATED_COST
, WDD.ITEM_DESCRIPTION ITEM_DESCRIPTION
, WDD.SRC_REQUESTED_QUANTITY SRC_REQUESTED_QUANTITY
, WDD.SRC_REQUESTED_QUANTITY_UOM SRC_REQUESTED_QUANTITY_UOM
FROM WSH_DELIVERY_DETAILS WDD
, MST_PO_LOCATION_ASSO_V PLOC
WHERE CONTAINER_FLAG = 'N'
AND NVL(WDD.IGNORE_FOR_PLANNING
, 'N') = 'N'
AND WDD.SHIP_TO_LOCATION_ID = PLOC.CUST_LOCATION_ID (+)
AND NOT EXISTS ( SELECT 1
FROM WSH_DELIVERY_ASSIGNMENTS ASSG
WHERE ASSG.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND ASSG.PARENT_DELIVERY_DETAIL_ID IS NOT NULL)
AND NOT EXISTS ( SELECT 1
FROM WSH_DELIVERY_ASSIGNMENTS ASSG
, MST_WSH_NEW_DELIVERIES_V DEL
WHERE WDD.DELIVERY_DETAIL_ID = ASSG.DELIVERY_DETAIL_ID
AND ASSG.DELIVERY_ID = DEL.DELIVERY_ID
AND DEL.PLANNED_FLAG IN ('Y'
, 'F') )