VIEW: APPS.MST_CUST_CUST_INCOMP_DEL_V

Source

View Text - Preformatted

SELECT mdd.plan_id , md.delivery_id , md.delivery_number , decode(mdd.split_from_delivery_detail_id,null,mdd.sr_delivery_detail_id,mdd.delivery_detail_id) , mdd.customer_id , msikfv.concatenated_segments , msitl.description , substr(mst_wb_util.get_name(mdd.ship_from_location_id),1,80) , wl1.address1 , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mdd.ship_to_location_id),1,80) , wl2.address1 , wl2.city , wl2.state , wl2.postal_code , wl2.country , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.earliest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.latest_pickup_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.earliest_acceptable_date),1,40) , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.latest_acceptable_date),1,40) , mdd.earliest_pickup_date , mdd.latest_pickup_date , mdd.earliest_acceptable_date , mdd.latest_acceptable_date , substr(mst_wb_util.get_local_chardtzone(md.pickup_location_id, md.pickup_date),1,40) , md.pickup_date , substr(mst_wb_util.get_local_chardtzone(md.dropoff_location_id, md.dropoff_date),1,40) , md.dropoff_date , nvl(mdd.gross_weight, 0) , mp.weight_uom , nvl(mdd.volume, 0) , mp.volume_uom , round(nvl(mdd.number_of_pallets, 0)) , nvl(mdd.requested_quantity, 0) , decode(mdd.container_flag,1,null,mdd.source_code) , mdd.source_header_number , mdd.allocated_cost , mp.currency_uom from mst_delivery_details mdd , mst_delivery_assignments mda , mst_deliveries md , wsh_locations wl1 , wsh_locations wl2 , mtl_system_items_kfv msikfv , mtl_system_items_tl msitl , mst_plans mp WHERE mp.plan_id = md.plan_id and md.plan_id = mda.plan_id and md.delivery_id = mda.delivery_id and mda.plan_id = mdd.plan_id and mda.delivery_detail_id = mdd.delivery_detail_id and mdd.ship_from_location_id = wl1.wsh_location_id and mdd.ship_to_location_id = wl2.wsh_location_id and mdd.inventory_item_id = msikfv.inventory_item_id and mdd.organization_id = msikfv.organization_id and mdd.inventory_item_id = msitl.inventory_item_id and mdd.organization_id = msitl.organization_id and msitl.language = userenv('LANG')

View Text - HTML Formatted

SELECT MDD.PLAN_ID
, MD.DELIVERY_ID
, MD.DELIVERY_NUMBER
, DECODE(MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, NULL
, MDD.SR_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID)
, MDD.CUSTOMER_ID
, MSIKFV.CONCATENATED_SEGMENTS
, MSITL.DESCRIPTION
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_FROM_LOCATION_ID)
, 1
, 80)
, WL1.ADDRESS1
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_TO_LOCATION_ID)
, 1
, 80)
, WL2.ADDRESS1
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.LATEST_PICKUP_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MDD.EARLIEST_PICKUP_DATE
, MDD.LATEST_PICKUP_DATE
, MDD.EARLIEST_ACCEPTABLE_DATE
, MDD.LATEST_ACCEPTABLE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.PICKUP_LOCATION_ID
, MD.PICKUP_DATE)
, 1
, 40)
, MD.PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MD.DROPOFF_LOCATION_ID
, MD.DROPOFF_DATE)
, 1
, 40)
, MD.DROPOFF_DATE
, NVL(MDD.GROSS_WEIGHT
, 0)
, MP.WEIGHT_UOM
, NVL(MDD.VOLUME
, 0)
, MP.VOLUME_UOM
, ROUND(NVL(MDD.NUMBER_OF_PALLETS
, 0))
, NVL(MDD.REQUESTED_QUANTITY
, 0)
, DECODE(MDD.CONTAINER_FLAG
, 1
, NULL
, MDD.SOURCE_CODE)
, MDD.SOURCE_HEADER_NUMBER
, MDD.ALLOCATED_COST
, MP.CURRENCY_UOM
FROM MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERIES MD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, MTL_SYSTEM_ITEMS_TL MSITL
, MST_PLANS MP
WHERE MP.PLAN_ID = MD.PLAN_ID
AND MD.PLAN_ID = MDA.PLAN_ID
AND MD.DELIVERY_ID = MDA.DELIVERY_ID
AND MDA.PLAN_ID = MDD.PLAN_ID
AND MDA.DELIVERY_DETAIL_ID = MDD.DELIVERY_DETAIL_ID
AND MDD.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MDD.INVENTORY_ITEM_ID = MSIKFV.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSIKFV.ORGANIZATION_ID
AND MDD.INVENTORY_ITEM_ID = MSITL.INVENTORY_ITEM_ID
AND MDD.ORGANIZATION_ID = MSITL.ORGANIZATION_ID
AND MSITL.LANGUAGE = USERENV('LANG')