VIEW: APPS.MST_OD_DELIVERY_DETAILS_V

Source

View Text - Preformatted

SELECT mdd.plan_id , decode(mdd.container_flag,1,null,mdd.source_code) , mdd.source_header_number , decode(mdd.split_from_delivery_detail_id,null,mdd.sr_delivery_detail_id,mdd.delivery_detail_id) , mdd.ship_from_location_id , mdd.ship_to_location_id , substr(mst_wb_util.get_name(mdd.ship_from_location_id),1,80) , flp1.facility_code , flp1.description , wl1.city , wl1.state , wl1.postal_code , wl1.country , substr(mst_wb_util.get_name(mdd.ship_to_location_id),1,80) , flp2.facility_code , flp2.description , wl2.city , wl2.state , wl2.postal_code , wl2.country , msikfv.concatenated_segments , msitl.description , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.earliest_pickup_date),1,40) , mdd.earliest_pickup_date , substr(mst_wb_util.get_local_chardtzone(mdd.ship_from_location_id, mdd.latest_pickup_date),1,40) , mdd.latest_pickup_date , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.earliest_acceptable_date),1,40) , mdd.earliest_acceptable_date , substr(mst_wb_util.get_local_chardtzone(mdd.ship_to_location_id, mdd.latest_acceptable_date),1,40) , mdd.latest_acceptable_date , nvl(mdd.net_weight,0) , nvl(mdd.volume,0) , round(nvl(mdd.number_of_pallets,0)) , nvl(mdd.requested_quantity,0) , mp.weight_uom , mp.volume_uom , md.delivery_id , md.delivery_number from mst_plans mp , mst_delivery_details mdd , mst_delivery_assignments mda , mst_deliveries md , wsh_locations wl1 , wsh_locations wl2 , fte_location_parameters flp1 , fte_location_parameters flp2 , mtl_system_items_kfv msikfv , mtl_system_items_tl msitl WHERE mdd.plan_id = mp.plan_id and mdd.plan_id = mda.plan_id and mdd.delivery_detail_id = mda.delivery_detail_id and mda.plan_id = md.plan_id and mda.delivery_id = md.delivery_id and mdd.ship_from_location_id = wl1.wsh_location_id and mdd.ship_to_location_id = wl2.wsh_location_id and mdd.ship_from_location_id = flp1.location_id and mdd.ship_to_location_id = flp2.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
, DECODE(MDD.CONTAINER_FLAG
, 1
, NULL
, MDD.SOURCE_CODE)
, MDD.SOURCE_HEADER_NUMBER
, DECODE(MDD.SPLIT_FROM_DELIVERY_DETAIL_ID
, NULL
, MDD.SR_DELIVERY_DETAIL_ID
, MDD.DELIVERY_DETAIL_ID)
, MDD.SHIP_FROM_LOCATION_ID
, MDD.SHIP_TO_LOCATION_ID
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_FROM_LOCATION_ID)
, 1
, 80)
, FLP1.FACILITY_CODE
, FLP1.DESCRIPTION
, WL1.CITY
, WL1.STATE
, WL1.POSTAL_CODE
, WL1.COUNTRY
, SUBSTR(MST_WB_UTIL.GET_NAME(MDD.SHIP_TO_LOCATION_ID)
, 1
, 80)
, FLP2.FACILITY_CODE
, FLP2.DESCRIPTION
, WL2.CITY
, WL2.STATE
, WL2.POSTAL_CODE
, WL2.COUNTRY
, MSIKFV.CONCATENATED_SEGMENTS
, MSITL.DESCRIPTION
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.EARLIEST_PICKUP_DATE)
, 1
, 40)
, MDD.EARLIEST_PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_FROM_LOCATION_ID
, MDD.LATEST_PICKUP_DATE)
, 1
, 40)
, MDD.LATEST_PICKUP_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.EARLIEST_ACCEPTABLE_DATE)
, 1
, 40)
, MDD.EARLIEST_ACCEPTABLE_DATE
, SUBSTR(MST_WB_UTIL.GET_LOCAL_CHARDTZONE(MDD.SHIP_TO_LOCATION_ID
, MDD.LATEST_ACCEPTABLE_DATE)
, 1
, 40)
, MDD.LATEST_ACCEPTABLE_DATE
, NVL(MDD.NET_WEIGHT
, 0)
, NVL(MDD.VOLUME
, 0)
, ROUND(NVL(MDD.NUMBER_OF_PALLETS
, 0))
, NVL(MDD.REQUESTED_QUANTITY
, 0)
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
, MD.DELIVERY_ID
, MD.DELIVERY_NUMBER
FROM MST_PLANS MP
, MST_DELIVERY_DETAILS MDD
, MST_DELIVERY_ASSIGNMENTS MDA
, MST_DELIVERIES MD
, WSH_LOCATIONS WL1
, WSH_LOCATIONS WL2
, FTE_LOCATION_PARAMETERS FLP1
, FTE_LOCATION_PARAMETERS FLP2
, MTL_SYSTEM_ITEMS_KFV MSIKFV
, MTL_SYSTEM_ITEMS_TL MSITL
WHERE MDD.PLAN_ID = MP.PLAN_ID
AND MDD.PLAN_ID = MDA.PLAN_ID
AND MDD.DELIVERY_DETAIL_ID = MDA.DELIVERY_DETAIL_ID
AND MDA.PLAN_ID = MD.PLAN_ID
AND MDA.DELIVERY_ID = MD.DELIVERY_ID
AND MDD.SHIP_FROM_LOCATION_ID = WL1.WSH_LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = WL2.WSH_LOCATION_ID
AND MDD.SHIP_FROM_LOCATION_ID = FLP1.LOCATION_ID
AND MDD.SHIP_TO_LOCATION_ID = FLP2.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')