VIEW: APPS.OKE_DTS_SHIPPING_V

Source

View Text - Preformatted

SELECT DLVDTL.SOURCE_HEADER_ID , DLVDTL.SOURCE_LINE_ID , DLV.NAME , NVL(DLVDTL.REQUESTED_QUANTITY,0) + NVL(DLVDTL.CANCELLED_QUANTITY,0) REQUESTED_QUANTITY , DLVDTL.REQUESTED_QUANTITY_UOM , DECODE(DLV.STATUS_CODE, 'CL', DLVDTL.SHIPPED_QUANTITY, 'CO', DLVDTL.SHIPPED_QUANTITY, 'IT',DLVDTL.SHIPPED_QUANTITY,0) SHIPPED_QUANTITY , DLVDTL.DELIVERED_QUANTITY , DLVDTL.CANCELLED_QUANTITY , DLVDTL.DATE_REQUESTED , DLVDTL.DATE_SCHEDULED , DLV.INITIAL_PICKUP_DATE , DLVDTL.SHIP_FROM_LOCATION_ID , OKE_UTILS.get_location_description(DLVDTL.SHIP_FROM_LOCATION_ID) , DLVDTL.SHIP_TO_LOCATION_ID , OKE_UTILS.get_location_description(DLVDTL.SHIP_TO_LOCATION_ID) , DLV.PLANNED_FLAG , DLVSTAT.MEANING , DLVDTL.TRACKING_NUMBER , DLV.WAYBILL , PACK.PACKING_SLIP_NUMBER , ( SELECT MAX(INS.SEQUENCE_NUMBER) FROM WSH_DOCUMENT_INSTANCES INS , WSH_DELIVERY_LEGS LEG WHERE LEG.DELIVERY_ID = DLV.DELIVERY_ID AND INS.ENTITY_ID = LEG.DELIVERY_LEG_ID AND INS.ENTITY_NAME = 'WSH_DELIVERY_LEGS' AND INS.DOCUMENT_TYPE = 'BOL' AND INS.STATUS <> 'CANCELLED' ) BILL_OF_LADING_NUMBER , ( SELECT MAX(FH.FORM_HEADER_NUMBER) FROM OKE_K_FORM_HEADERS FH , OKE_K_FORM_LINES FL WHERE FH.PRINT_FORM_CODE = 'DD250' AND FH.FORM_HEADER_ID = FL.FORM_HEADER_ID AND FH.K_HEADER_ID = DLVDTL.SOURCE_HEADER_ID AND FH.REFERENCE1 = DLV.DELIVERY_ID AND ( FH.REFERENCE3 = DLVDTL.SOURCE_LINE_ID OR FL.REFERENCE1 = DLVDTL.SOURCE_LINE_ID ) ) DD250_NUMBER, DLVDTL.DELIVERY_DETAIL_ID REQUEST_ID FROM WSH_NEW_DELIVERIES DLV , WSH_DELIVERY_ASSIGNMENTS_V DLVASGN , WSH_DELIVERY_DETAILS DLVDTL , FND_LOOKUP_VALUES_VL DLVSTAT , WSH_PACKING_SLIPS_DB_V PACK WHERE DLVDTL.SOURCE_CODE = 'OKE' AND DLV.DELIVERY_ID (+) = DLVASGN.DELIVERY_ID AND DLVDTL.DELIVERY_DETAIL_ID = DLVASGN.DELIVERY_DETAIL_ID AND DLVSTAT.LOOKUP_TYPE = 'PICK_STATUS' AND DLVSTAT.LOOKUP_CODE = NVL(DLVDTL.RELEASED_STATUS , 'X') AND PACK.DELIVERY_ID (+) = DLV.DELIVERY_ID

View Text - HTML Formatted

SELECT DLVDTL.SOURCE_HEADER_ID
, DLVDTL.SOURCE_LINE_ID
, DLV.NAME
, NVL(DLVDTL.REQUESTED_QUANTITY
, 0) + NVL(DLVDTL.CANCELLED_QUANTITY
, 0) REQUESTED_QUANTITY
, DLVDTL.REQUESTED_QUANTITY_UOM
, DECODE(DLV.STATUS_CODE
, 'CL'
, DLVDTL.SHIPPED_QUANTITY
, 'CO'
, DLVDTL.SHIPPED_QUANTITY
, 'IT'
, DLVDTL.SHIPPED_QUANTITY
, 0) SHIPPED_QUANTITY
, DLVDTL.DELIVERED_QUANTITY
, DLVDTL.CANCELLED_QUANTITY
, DLVDTL.DATE_REQUESTED
, DLVDTL.DATE_SCHEDULED
, DLV.INITIAL_PICKUP_DATE
, DLVDTL.SHIP_FROM_LOCATION_ID
, OKE_UTILS.GET_LOCATION_DESCRIPTION(DLVDTL.SHIP_FROM_LOCATION_ID)
, DLVDTL.SHIP_TO_LOCATION_ID
, OKE_UTILS.GET_LOCATION_DESCRIPTION(DLVDTL.SHIP_TO_LOCATION_ID)
, DLV.PLANNED_FLAG
, DLVSTAT.MEANING
, DLVDTL.TRACKING_NUMBER
, DLV.WAYBILL
, PACK.PACKING_SLIP_NUMBER
, ( SELECT MAX(INS.SEQUENCE_NUMBER)
FROM WSH_DOCUMENT_INSTANCES INS
, WSH_DELIVERY_LEGS LEG
WHERE LEG.DELIVERY_ID = DLV.DELIVERY_ID
AND INS.ENTITY_ID = LEG.DELIVERY_LEG_ID
AND INS.ENTITY_NAME = 'WSH_DELIVERY_LEGS'
AND INS.DOCUMENT_TYPE = 'BOL'
AND INS.STATUS <> 'CANCELLED' ) BILL_OF_LADING_NUMBER
, ( SELECT MAX(FH.FORM_HEADER_NUMBER)
FROM OKE_K_FORM_HEADERS FH
, OKE_K_FORM_LINES FL
WHERE FH.PRINT_FORM_CODE = 'DD250'
AND FH.FORM_HEADER_ID = FL.FORM_HEADER_ID
AND FH.K_HEADER_ID = DLVDTL.SOURCE_HEADER_ID
AND FH.REFERENCE1 = DLV.DELIVERY_ID
AND ( FH.REFERENCE3 = DLVDTL.SOURCE_LINE_ID OR FL.REFERENCE1 = DLVDTL.SOURCE_LINE_ID ) ) DD250_NUMBER
, DLVDTL.DELIVERY_DETAIL_ID REQUEST_ID
FROM WSH_NEW_DELIVERIES DLV
, WSH_DELIVERY_ASSIGNMENTS_V DLVASGN
, WSH_DELIVERY_DETAILS DLVDTL
, FND_LOOKUP_VALUES_VL DLVSTAT
, WSH_PACKING_SLIPS_DB_V PACK
WHERE DLVDTL.SOURCE_CODE = 'OKE'
AND DLV.DELIVERY_ID (+) = DLVASGN.DELIVERY_ID
AND DLVDTL.DELIVERY_DETAIL_ID = DLVASGN.DELIVERY_DETAIL_ID
AND DLVSTAT.LOOKUP_TYPE = 'PICK_STATUS'
AND DLVSTAT.LOOKUP_CODE = NVL(DLVDTL.RELEASED_STATUS
, 'X')
AND PACK.DELIVERY_ID (+) = DLV.DELIVERY_ID