VIEW: APPS.WSH_DELIVERABLES_V

Source

View Text - Preformatted

SELECT to_number(null), to_number(null), wdd.rowid, decode(wdd.container_flag,'Y','C','C','C','L'), wdd.delivery_detail_id, to_number(decode(wdd.container_flag,'N',wdd.delivery_detail_id,NULL)), to_number(decode(wdd.container_flag,'Y',wdd.delivery_detail_id,'C',wdd.delivery_detail_id,NULL)), wdd.container_name, wda.delivery_id, wdd.source_code, flv_source.meaning, wdd.source_header_id, wdd.source_line_id, wdd.source_header_number, wdd.source_header_type_id, wdd.source_header_type_name, wdd.source_line_number, wdd.src_requested_quantity, wdd.src_requested_quantity_uom, wdd.customer_id, wdd.sold_to_contact_id, wdd.inventory_item_id, wdd.item_description, wdd.country_of_origin, wdd.classification, wdd.ship_from_location_id, wdd.ship_to_location_id, wdd.ship_to_contact_id, wdd.deliver_to_location_id, wdd.deliver_to_contact_id, wdd.intmed_ship_to_location_id, wdd.intmed_ship_to_contact_id, wdd.ship_tolerance_above, wdd.ship_tolerance_below, wdd.requested_quantity, wdd.shipped_quantity, wdd.delivered_quantity, wdd.cancelled_quantity, wdd.requested_quantity_uom, wdd.subinventory, wdd.revision, wdd.lot_number, wdd.customer_requested_lot_flag, wdd.serial_number, wdd.locator_id, wdd.date_requested, wdd.date_scheduled, wdd.master_container_item_id, wdd.detail_container_item_id, wdd.load_seq_number, wdd.ship_method_code, wdd.carrier_id, wdd.freight_terms_code, wdd.shipment_priority_code, wdd.fob_code, wdd.customer_item_id, wdd.dep_plan_required_flag, wdd.customer_prod_seq, wdd.customer_dock_code, wdd.unit_weight, wdd.unit_volume, wdd.wv_frozen_flag, wdd.gross_weight, (wdd.gross_weight-nvl(wdd.net_weight,0)), wdd.net_weight, wdd.weight_uom_code, wdd.filled_volume, wdd.volume, wdd.volume_uom_code, wdd.fill_percent, wdd.maximum_load_weight, wdd.maximum_volume, wdd.minimum_fill_percent, wdd.mvt_stat_status, wdd.released_status, decode(wdd.released_status, 'X', decode(wlpn.lpn_context, '9', flv_wms.meaning, '11', flv_wms.meaning, '12', flv_wms.meaning, (select meaning from fnd_lookup_values flv_released where flv_released.lookup_type = 'PICK_STATUS' and flv_released.lookup_code = wdd.released_status and flv_released.LANGUAGE = userenv('LANG') and flv_released.VIEW_APPLICATION_ID = 665 and flv_released.SECURITY_GROUP_ID = 0 )), (select meaning from fnd_lookup_values flv_released where flv_released.lookup_type = 'PICK_STATUS' and flv_released.lookup_code = wdd.released_status and flv_released.LANGUAGE = userenv('LANG') and flv_released.VIEW_APPLICATION_ID = 665 and flv_released.SECURITY_GROUP_ID = 0 )), wdd.organization_id, wdd.transaction_temp_id, wdd.ship_set_id, wdd.arrival_set_id, wdd.ship_model_complete_flag, wdd.top_model_line_id, wdd.hold_code, wdd.cust_po_number, wdd.ato_line_id, wdd.move_order_line_id, wdd.hazard_class_id, wdd.quality_control_quantity, wdd.cycle_count_quantity, wdd.tracking_number, wdd.movement_id, wdd.shipping_instructions, wdd.packing_instructions, wdd.project_id, wdd.task_id, wdd.org_id, wdd.oe_interfaced_flag, wdd.split_from_delivery_detail_id, wdd.inv_interfaced_flag, wda.parent_delivery_detail_id, wdd.master_serial_number, wdd.container_flag, wdd.container_type_code, wdd.seal_code, to_char(null), wdd.attribute_category, wdd.attribute1, wdd.attribute2, wdd.attribute3, wdd.attribute4, wdd.attribute5, wdd.attribute6, wdd.attribute7, wdd.attribute8, wdd.attribute9, wdd.attribute10, wdd.attribute11, wdd.attribute12, wdd.attribute13, wdd.attribute14, wdd.attribute15, wdd.tp_attribute_category, wdd.tp_attribute1, wdd.tp_attribute2, wdd.tp_attribute3, wdd.tp_attribute4, wdd.tp_attribute5, wdd.tp_attribute6, wdd.tp_attribute7, wdd.tp_attribute8, wdd.tp_attribute9, wdd.tp_attribute10, wdd.tp_attribute11, wdd.tp_attribute12, wdd.tp_attribute13, wdd.tp_attribute14, wdd.tp_attribute15, wdd.creation_date, wdd.created_by, wdd.last_update_date, wdd.last_updated_by, wdd.last_update_login, wdd.program_application_id, wdd.program_id, wdd.program_update_date, wdd.request_id, wdd.preferred_grade, wdd.shipped_quantity2, wdd.delivered_quantity2, wdd.cancelled_quantity2, wdd.requested_quantity2, wdd.requested_quantity_uom2, wdd.src_requested_quantity2, wdd.src_requested_quantity_uom2, wdd.quality_control_quantity2, wdd.cycle_count_quantity2, wdd.unit_number, wdd.unit_price, wdd.currency_code, wdd.freight_class_cat_id, wdd.commodity_code_cat_id, wdd.inspection_flag, wdd.pickable_flag, wdd.ship_to_site_use_id, wdd.deliver_to_site_use_id, wdd.to_serial_number, wdd.picked_quantity, wdd.picked_quantity2, wdd.customer_production_line, wdd.customer_job, wdd.cust_model_serial_number, wdd.received_quantity, wdd.received_quantity2, wdd.source_line_set_id, wdd.batch_id, wdd.shipment_batch_id, wdd.lpn_id, wdd.original_subinventory, wdd.service_level, wdd.mode_of_transport, wdd.ignore_for_planning, nvl(wdd.line_direction, 'O'), wdd.party_id, wdd.routing_req_id, wdd.shipping_control, wdd.source_blanket_reference_id, wdd.source_blanket_reference_num, wdd.po_shipment_line_id, wdd.po_shipment_line_number, wdd.returned_quantity, wdd.returned_quantity2, wdd.source_line_type_code, wdd.rcv_shipment_line_id, wdd.supplier_item_number, wdd.vendor_id, wdd.ship_from_site_id, wdd.earliest_pickup_date, wdd.latest_pickup_date, wdd.earliest_dropoff_date, wdd.latest_dropoff_date, wdd.request_date_type_code, wdd.tp_delivery_detail_id, wdd.source_document_type_id, wdd.po_revision_number, wdd.release_revision_number, wdd.replenishment_status, wdd.reference_number , wdd.reference_line_number, wdd.reference_line_quantity , wdd.reference_line_quantity_uom , wdd.original_locator_id , wdd.original_revision , wdd.original_lot_number, wdd.client_id, wdd.consignee_flag from wsh_delivery_details wdd, wsh_delivery_assignments wda, fnd_lookup_values flv_source, wms_license_plate_numbers wlpn, fnd_lookup_values flv_wms WHERE wda.delivery_detail_id = wdd.delivery_detail_id and flv_source.lookup_type = 'SOURCE_SYSTEM' and flv_source.lookup_code = wdd.source_code and nvl(wdd.line_direction, 'O') IN ('O', 'IO') and flv_source.LANGUAGE = userenv('LANG') and flv_source.VIEW_APPLICATION_ID = 665 and flv_source.SECURITY_GROUP_ID = 0 and flv_wms.lookup_type(+)='WMS_LPN_CONTEXT' and flv_wms.LANGUAGE(+) = userenv('LANG') and wdd.lpn_id = wlpn.lpn_id (+) and flv_wms.lookup_code (+) = to_char(wlpn.lpn_context) and flv_wms.VIEW_APPLICATION_ID(+) = 700 and flv_wms.SECURITY_GROUP_ID(+) = 0 and NVL(wda.type, 'S') IN ('S','C')

View Text - HTML Formatted

SELECT TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, WDD.ROWID
, DECODE(WDD.CONTAINER_FLAG
, 'Y'
, 'C'
, 'C'
, 'C'
, 'L')
, WDD.DELIVERY_DETAIL_ID
, TO_NUMBER(DECODE(WDD.CONTAINER_FLAG
, 'N'
, WDD.DELIVERY_DETAIL_ID
, NULL))
, TO_NUMBER(DECODE(WDD.CONTAINER_FLAG
, 'Y'
, WDD.DELIVERY_DETAIL_ID
, 'C'
, WDD.DELIVERY_DETAIL_ID
, NULL))
, WDD.CONTAINER_NAME
, WDA.DELIVERY_ID
, WDD.SOURCE_CODE
, FLV_SOURCE.MEANING
, WDD.SOURCE_HEADER_ID
, WDD.SOURCE_LINE_ID
, WDD.SOURCE_HEADER_NUMBER
, WDD.SOURCE_HEADER_TYPE_ID
, WDD.SOURCE_HEADER_TYPE_NAME
, WDD.SOURCE_LINE_NUMBER
, WDD.SRC_REQUESTED_QUANTITY
, WDD.SRC_REQUESTED_QUANTITY_UOM
, WDD.CUSTOMER_ID
, WDD.SOLD_TO_CONTACT_ID
, WDD.INVENTORY_ITEM_ID
, WDD.ITEM_DESCRIPTION
, WDD.COUNTRY_OF_ORIGIN
, WDD.CLASSIFICATION
, WDD.SHIP_FROM_LOCATION_ID
, WDD.SHIP_TO_LOCATION_ID
, WDD.SHIP_TO_CONTACT_ID
, WDD.DELIVER_TO_LOCATION_ID
, WDD.DELIVER_TO_CONTACT_ID
, WDD.INTMED_SHIP_TO_LOCATION_ID
, WDD.INTMED_SHIP_TO_CONTACT_ID
, WDD.SHIP_TOLERANCE_ABOVE
, WDD.SHIP_TOLERANCE_BELOW
, WDD.REQUESTED_QUANTITY
, WDD.SHIPPED_QUANTITY
, WDD.DELIVERED_QUANTITY
, WDD.CANCELLED_QUANTITY
, WDD.REQUESTED_QUANTITY_UOM
, WDD.SUBINVENTORY
, WDD.REVISION
, WDD.LOT_NUMBER
, WDD.CUSTOMER_REQUESTED_LOT_FLAG
, WDD.SERIAL_NUMBER
, WDD.LOCATOR_ID
, WDD.DATE_REQUESTED
, WDD.DATE_SCHEDULED
, WDD.MASTER_CONTAINER_ITEM_ID
, WDD.DETAIL_CONTAINER_ITEM_ID
, WDD.LOAD_SEQ_NUMBER
, WDD.SHIP_METHOD_CODE
, WDD.CARRIER_ID
, WDD.FREIGHT_TERMS_CODE
, WDD.SHIPMENT_PRIORITY_CODE
, WDD.FOB_CODE
, WDD.CUSTOMER_ITEM_ID
, WDD.DEP_PLAN_REQUIRED_FLAG
, WDD.CUSTOMER_PROD_SEQ
, WDD.CUSTOMER_DOCK_CODE
, WDD.UNIT_WEIGHT
, WDD.UNIT_VOLUME
, WDD.WV_FROZEN_FLAG
, WDD.GROSS_WEIGHT
, (WDD.GROSS_WEIGHT-NVL(WDD.NET_WEIGHT
, 0))
, WDD.NET_WEIGHT
, WDD.WEIGHT_UOM_CODE
, WDD.FILLED_VOLUME
, WDD.VOLUME
, WDD.VOLUME_UOM_CODE
, WDD.FILL_PERCENT
, WDD.MAXIMUM_LOAD_WEIGHT
, WDD.MAXIMUM_VOLUME
, WDD.MINIMUM_FILL_PERCENT
, WDD.MVT_STAT_STATUS
, WDD.RELEASED_STATUS
, DECODE(WDD.RELEASED_STATUS
, 'X'
, DECODE(WLPN.LPN_CONTEXT
, '9'
, FLV_WMS.MEANING
, '11'
, FLV_WMS.MEANING
, '12'
, FLV_WMS.MEANING
, (SELECT MEANING
FROM FND_LOOKUP_VALUES FLV_RELEASED
WHERE FLV_RELEASED.LOOKUP_TYPE = 'PICK_STATUS'
AND FLV_RELEASED.LOOKUP_CODE = WDD.RELEASED_STATUS
AND FLV_RELEASED.LANGUAGE = USERENV('LANG')
AND FLV_RELEASED.VIEW_APPLICATION_ID = 665
AND FLV_RELEASED.SECURITY_GROUP_ID = 0 ))
, (SELECT MEANING
FROM FND_LOOKUP_VALUES FLV_RELEASED
WHERE FLV_RELEASED.LOOKUP_TYPE = 'PICK_STATUS'
AND FLV_RELEASED.LOOKUP_CODE = WDD.RELEASED_STATUS
AND FLV_RELEASED.LANGUAGE = USERENV('LANG')
AND FLV_RELEASED.VIEW_APPLICATION_ID = 665
AND FLV_RELEASED.SECURITY_GROUP_ID = 0 ))
, WDD.ORGANIZATION_ID
, WDD.TRANSACTION_TEMP_ID
, WDD.SHIP_SET_ID
, WDD.ARRIVAL_SET_ID
, WDD.SHIP_MODEL_COMPLETE_FLAG
, WDD.TOP_MODEL_LINE_ID
, WDD.HOLD_CODE
, WDD.CUST_PO_NUMBER
, WDD.ATO_LINE_ID
, WDD.MOVE_ORDER_LINE_ID
, WDD.HAZARD_CLASS_ID
, WDD.QUALITY_CONTROL_QUANTITY
, WDD.CYCLE_COUNT_QUANTITY
, WDD.TRACKING_NUMBER
, WDD.MOVEMENT_ID
, WDD.SHIPPING_INSTRUCTIONS
, WDD.PACKING_INSTRUCTIONS
, WDD.PROJECT_ID
, WDD.TASK_ID
, WDD.ORG_ID
, WDD.OE_INTERFACED_FLAG
, WDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, WDD.INV_INTERFACED_FLAG
, WDA.PARENT_DELIVERY_DETAIL_ID
, WDD.MASTER_SERIAL_NUMBER
, WDD.CONTAINER_FLAG
, WDD.CONTAINER_TYPE_CODE
, WDD.SEAL_CODE
, TO_CHAR(NULL)
, WDD.ATTRIBUTE_CATEGORY
, WDD.ATTRIBUTE1
, WDD.ATTRIBUTE2
, WDD.ATTRIBUTE3
, WDD.ATTRIBUTE4
, WDD.ATTRIBUTE5
, WDD.ATTRIBUTE6
, WDD.ATTRIBUTE7
, WDD.ATTRIBUTE8
, WDD.ATTRIBUTE9
, WDD.ATTRIBUTE10
, WDD.ATTRIBUTE11
, WDD.ATTRIBUTE12
, WDD.ATTRIBUTE13
, WDD.ATTRIBUTE14
, WDD.ATTRIBUTE15
, WDD.TP_ATTRIBUTE_CATEGORY
, WDD.TP_ATTRIBUTE1
, WDD.TP_ATTRIBUTE2
, WDD.TP_ATTRIBUTE3
, WDD.TP_ATTRIBUTE4
, WDD.TP_ATTRIBUTE5
, WDD.TP_ATTRIBUTE6
, WDD.TP_ATTRIBUTE7
, WDD.TP_ATTRIBUTE8
, WDD.TP_ATTRIBUTE9
, WDD.TP_ATTRIBUTE10
, WDD.TP_ATTRIBUTE11
, WDD.TP_ATTRIBUTE12
, WDD.TP_ATTRIBUTE13
, WDD.TP_ATTRIBUTE14
, WDD.TP_ATTRIBUTE15
, WDD.CREATION_DATE
, WDD.CREATED_BY
, WDD.LAST_UPDATE_DATE
, WDD.LAST_UPDATED_BY
, WDD.LAST_UPDATE_LOGIN
, WDD.PROGRAM_APPLICATION_ID
, WDD.PROGRAM_ID
, WDD.PROGRAM_UPDATE_DATE
, WDD.REQUEST_ID
, WDD.PREFERRED_GRADE
, WDD.SHIPPED_QUANTITY2
, WDD.DELIVERED_QUANTITY2
, WDD.CANCELLED_QUANTITY2
, WDD.REQUESTED_QUANTITY2
, WDD.REQUESTED_QUANTITY_UOM2
, WDD.SRC_REQUESTED_QUANTITY2
, WDD.SRC_REQUESTED_QUANTITY_UOM2
, WDD.QUALITY_CONTROL_QUANTITY2
, WDD.CYCLE_COUNT_QUANTITY2
, WDD.UNIT_NUMBER
, WDD.UNIT_PRICE
, WDD.CURRENCY_CODE
, WDD.FREIGHT_CLASS_CAT_ID
, WDD.COMMODITY_CODE_CAT_ID
, WDD.INSPECTION_FLAG
, WDD.PICKABLE_FLAG
, WDD.SHIP_TO_SITE_USE_ID
, WDD.DELIVER_TO_SITE_USE_ID
, WDD.TO_SERIAL_NUMBER
, WDD.PICKED_QUANTITY
, WDD.PICKED_QUANTITY2
, WDD.CUSTOMER_PRODUCTION_LINE
, WDD.CUSTOMER_JOB
, WDD.CUST_MODEL_SERIAL_NUMBER
, WDD.RECEIVED_QUANTITY
, WDD.RECEIVED_QUANTITY2
, WDD.SOURCE_LINE_SET_ID
, WDD.BATCH_ID
, WDD.SHIPMENT_BATCH_ID
, WDD.LPN_ID
, WDD.ORIGINAL_SUBINVENTORY
, WDD.SERVICE_LEVEL
, WDD.MODE_OF_TRANSPORT
, WDD.IGNORE_FOR_PLANNING
, NVL(WDD.LINE_DIRECTION
, 'O')
, WDD.PARTY_ID
, WDD.ROUTING_REQ_ID
, WDD.SHIPPING_CONTROL
, WDD.SOURCE_BLANKET_REFERENCE_ID
, WDD.SOURCE_BLANKET_REFERENCE_NUM
, WDD.PO_SHIPMENT_LINE_ID
, WDD.PO_SHIPMENT_LINE_NUMBER
, WDD.RETURNED_QUANTITY
, WDD.RETURNED_QUANTITY2
, WDD.SOURCE_LINE_TYPE_CODE
, WDD.RCV_SHIPMENT_LINE_ID
, WDD.SUPPLIER_ITEM_NUMBER
, WDD.VENDOR_ID
, WDD.SHIP_FROM_SITE_ID
, WDD.EARLIEST_PICKUP_DATE
, WDD.LATEST_PICKUP_DATE
, WDD.EARLIEST_DROPOFF_DATE
, WDD.LATEST_DROPOFF_DATE
, WDD.REQUEST_DATE_TYPE_CODE
, WDD.TP_DELIVERY_DETAIL_ID
, WDD.SOURCE_DOCUMENT_TYPE_ID
, WDD.PO_REVISION_NUMBER
, WDD.RELEASE_REVISION_NUMBER
, WDD.REPLENISHMENT_STATUS
, WDD.REFERENCE_NUMBER
, WDD.REFERENCE_LINE_NUMBER
, WDD.REFERENCE_LINE_QUANTITY
, WDD.REFERENCE_LINE_QUANTITY_UOM
, WDD.ORIGINAL_LOCATOR_ID
, WDD.ORIGINAL_REVISION
, WDD.ORIGINAL_LOT_NUMBER
, WDD.CLIENT_ID
, WDD.CONSIGNEE_FLAG
FROM WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS WDA
, FND_LOOKUP_VALUES FLV_SOURCE
, WMS_LICENSE_PLATE_NUMBERS WLPN
, FND_LOOKUP_VALUES FLV_WMS
WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND FLV_SOURCE.LOOKUP_TYPE = 'SOURCE_SYSTEM'
AND FLV_SOURCE.LOOKUP_CODE = WDD.SOURCE_CODE
AND NVL(WDD.LINE_DIRECTION
, 'O') IN ('O'
, 'IO')
AND FLV_SOURCE.LANGUAGE = USERENV('LANG')
AND FLV_SOURCE.VIEW_APPLICATION_ID = 665
AND FLV_SOURCE.SECURITY_GROUP_ID = 0
AND FLV_WMS.LOOKUP_TYPE(+)='WMS_LPN_CONTEXT'
AND FLV_WMS.LANGUAGE(+) = USERENV('LANG')
AND WDD.LPN_ID = WLPN.LPN_ID (+)
AND FLV_WMS.LOOKUP_CODE (+) = TO_CHAR(WLPN.LPN_CONTEXT)
AND FLV_WMS.VIEW_APPLICATION_ID(+) = 700
AND FLV_WMS.SECURITY_GROUP_ID(+) = 0
AND NVL(WDA.TYPE
, 'S') IN ('S'
, 'C')