VIEW: APPS.CSP_SHIPPING_DETAILS_V

Source

View Text - Preformatted

SELECT flvv.meaning, ism.ship_method, NVL(ism.cost_per_unit_load_weight,0), csp_part_search_pvt.get_arrival_time(ccdt.cutoff_time,ccdt.timezone_id,ccdt.lead_time,ccdt.lead_time_uom,ism.intransit_time,ccdt.delivery_time,ccdt.safety_zone,ism.to_location_id,'HR',ccdt.organization_id,ccdt.subinventory_code) arrival_date, haou.organization_id, ism.from_location_id, ism.to_location_id, ism.destination_type, ism.default_flag, ccdt.cutoff_time, ccdt.delivery_time, ccdt.lead_time, ccdt.lead_time_uom, ccdt.safety_zone, ism.intransit_time, 'HR', ccdt.distance, ccdt.distance_uom FROM mtl_interorg_ship_methods ism, fnd_lookup_values_vl flvv , hr_all_organization_units haou , csp_carrier_delivery_times ccdt WHERE ism.destination_type = 'L' AND haou.location_id = ism.from_location_id AND ccdt.organization_id = haou.organization_id AND ccdt.shipping_method = ism.ship_method AND flvv.lookup_type = 'SHIP_METHOD' AND flvv.lookup_code = ism.ship_method UNION ALL SELECT flvv.meaning, ism.ship_method shipping_method, NVL(ism.cost_per_unit_load_weight,0) shipping_cost, csp_part_search_pvt.get_arrival_time(ccdt.cutoff_time,ccdt.timezone_id,ccdt.lead_time,ccdt.lead_time_uom,ism.intransit_time,ccdt.delivery_time,ccdt.safety_zone,wlr.location_id,wlr.location_source,ccdt.organization_id,ccdt.subinventory_code) arrival_date, haou.organization_id organization_id, ism.from_location_id from_location_id, wlr.location_id to_location_id, ism.destination_type destination_type, ism.default_flag, ccdt.cutoff_time, ccdt.delivery_time, ccdt.lead_time, ccdt.lead_time_uom, ccdt.safety_zone, ism.intransit_time, wlr.location_source, ccdt.distance, ccdt.distance_uom FROM mtl_interorg_ship_methods ism, fnd_lookup_values_vl flvv, wsh_region_locations wlr, hr_all_organization_units haou, csp_carrier_delivery_times ccdt WHERE ism.destination_type = 'R' AND ism.to_region_id = wlr.region_id AND haou.location_id = ism.from_location_id AND ccdt.organization_id = haou.organization_id AND ccdt.shipping_method = ism.ship_method AND flvv.lookup_type = 'SHIP_METHOD' AND flvv.lookup_code = ism.ship_method UNION ALL SELECT flvv.meaning, ism.ship_method shipping_method, NVL(ism.cost_per_unit_load_weight,0) shipping_cost, csp_part_search_pvt.get_arrival_time(ccdt.cutoff_time,ccdt.timezone_id,ccdt.lead_time,ccdt.lead_time_uom,ism.intransit_time,ccdt.delivery_time,ccdt.safety_zone,wlr.location_id,wlr.location_source,ccdt.organization_id,ccdt.subinventory_code) arrival_date, haou.organization_id organization_id, ism.from_location_id from_location_id, wlr.location_id to_location_id, ism.destination_type destination_type, ism.default_flag, ccdt.cutoff_time, ccdt.delivery_time, ccdt.lead_time, ccdt.lead_time_uom, ccdt.safety_zone, ism.intransit_time, wlr.location_source, ccdt.distance, ccdt.distance_uom FROM mtl_interorg_ship_methods ism, fnd_lookup_values_vl flvv , wsh_region_locations wlr , wsh_zone_regions_v wzr , hr_all_organization_units haou , csp_carrier_delivery_times ccdt WHERE ism.destination_type ='Z' AND ism.to_region_id = wzr.zone_id AND wzr.region_id = wlr.region_id AND haou.location_id = ism.from_location_id AND ccdt.organization_id = haou.organization_id AND ccdt.shipping_method = ism.ship_method AND flvv.lookup_type = 'SHIP_METHOD' AND flvv.lookup_code = ism.ship_method

View Text - HTML Formatted

SELECT FLVV.MEANING
, ISM.SHIP_METHOD
, NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT
, 0)
, CSP_PART_SEARCH_PVT.GET_ARRIVAL_TIME(CCDT.CUTOFF_TIME
, CCDT.TIMEZONE_ID
, CCDT.LEAD_TIME
, CCDT.LEAD_TIME_UOM
, ISM.INTRANSIT_TIME
, CCDT.DELIVERY_TIME
, CCDT.SAFETY_ZONE
, ISM.TO_LOCATION_ID
, 'HR'
, CCDT.ORGANIZATION_ID
, CCDT.SUBINVENTORY_CODE) ARRIVAL_DATE
, HAOU.ORGANIZATION_ID
, ISM.FROM_LOCATION_ID
, ISM.TO_LOCATION_ID
, ISM.DESTINATION_TYPE
, ISM.DEFAULT_FLAG
, CCDT.CUTOFF_TIME
, CCDT.DELIVERY_TIME
, CCDT.LEAD_TIME
, CCDT.LEAD_TIME_UOM
, CCDT.SAFETY_ZONE
, ISM.INTRANSIT_TIME
, 'HR'
, CCDT.DISTANCE
, CCDT.DISTANCE_UOM
FROM MTL_INTERORG_SHIP_METHODS ISM
, FND_LOOKUP_VALUES_VL FLVV
, HR_ALL_ORGANIZATION_UNITS HAOU
, CSP_CARRIER_DELIVERY_TIMES CCDT
WHERE ISM.DESTINATION_TYPE = 'L'
AND HAOU.LOCATION_ID = ISM.FROM_LOCATION_ID
AND CCDT.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND CCDT.SHIPPING_METHOD = ISM.SHIP_METHOD
AND FLVV.LOOKUP_TYPE = 'SHIP_METHOD'
AND FLVV.LOOKUP_CODE = ISM.SHIP_METHOD UNION ALL SELECT FLVV.MEANING
, ISM.SHIP_METHOD SHIPPING_METHOD
, NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT
, 0) SHIPPING_COST
, CSP_PART_SEARCH_PVT.GET_ARRIVAL_TIME(CCDT.CUTOFF_TIME
, CCDT.TIMEZONE_ID
, CCDT.LEAD_TIME
, CCDT.LEAD_TIME_UOM
, ISM.INTRANSIT_TIME
, CCDT.DELIVERY_TIME
, CCDT.SAFETY_ZONE
, WLR.LOCATION_ID
, WLR.LOCATION_SOURCE
, CCDT.ORGANIZATION_ID
, CCDT.SUBINVENTORY_CODE) ARRIVAL_DATE
, HAOU.ORGANIZATION_ID ORGANIZATION_ID
, ISM.FROM_LOCATION_ID FROM_LOCATION_ID
, WLR.LOCATION_ID TO_LOCATION_ID
, ISM.DESTINATION_TYPE DESTINATION_TYPE
, ISM.DEFAULT_FLAG
, CCDT.CUTOFF_TIME
, CCDT.DELIVERY_TIME
, CCDT.LEAD_TIME
, CCDT.LEAD_TIME_UOM
, CCDT.SAFETY_ZONE
, ISM.INTRANSIT_TIME
, WLR.LOCATION_SOURCE
, CCDT.DISTANCE
, CCDT.DISTANCE_UOM
FROM MTL_INTERORG_SHIP_METHODS ISM
, FND_LOOKUP_VALUES_VL FLVV
, WSH_REGION_LOCATIONS WLR
, HR_ALL_ORGANIZATION_UNITS HAOU
, CSP_CARRIER_DELIVERY_TIMES CCDT
WHERE ISM.DESTINATION_TYPE = 'R'
AND ISM.TO_REGION_ID = WLR.REGION_ID
AND HAOU.LOCATION_ID = ISM.FROM_LOCATION_ID
AND CCDT.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND CCDT.SHIPPING_METHOD = ISM.SHIP_METHOD
AND FLVV.LOOKUP_TYPE = 'SHIP_METHOD'
AND FLVV.LOOKUP_CODE = ISM.SHIP_METHOD UNION ALL SELECT FLVV.MEANING
, ISM.SHIP_METHOD SHIPPING_METHOD
, NVL(ISM.COST_PER_UNIT_LOAD_WEIGHT
, 0) SHIPPING_COST
, CSP_PART_SEARCH_PVT.GET_ARRIVAL_TIME(CCDT.CUTOFF_TIME
, CCDT.TIMEZONE_ID
, CCDT.LEAD_TIME
, CCDT.LEAD_TIME_UOM
, ISM.INTRANSIT_TIME
, CCDT.DELIVERY_TIME
, CCDT.SAFETY_ZONE
, WLR.LOCATION_ID
, WLR.LOCATION_SOURCE
, CCDT.ORGANIZATION_ID
, CCDT.SUBINVENTORY_CODE) ARRIVAL_DATE
, HAOU.ORGANIZATION_ID ORGANIZATION_ID
, ISM.FROM_LOCATION_ID FROM_LOCATION_ID
, WLR.LOCATION_ID TO_LOCATION_ID
, ISM.DESTINATION_TYPE DESTINATION_TYPE
, ISM.DEFAULT_FLAG
, CCDT.CUTOFF_TIME
, CCDT.DELIVERY_TIME
, CCDT.LEAD_TIME
, CCDT.LEAD_TIME_UOM
, CCDT.SAFETY_ZONE
, ISM.INTRANSIT_TIME
, WLR.LOCATION_SOURCE
, CCDT.DISTANCE
, CCDT.DISTANCE_UOM
FROM MTL_INTERORG_SHIP_METHODS ISM
, FND_LOOKUP_VALUES_VL FLVV
, WSH_REGION_LOCATIONS WLR
, WSH_ZONE_REGIONS_V WZR
, HR_ALL_ORGANIZATION_UNITS HAOU
, CSP_CARRIER_DELIVERY_TIMES CCDT
WHERE ISM.DESTINATION_TYPE ='Z'
AND ISM.TO_REGION_ID = WZR.ZONE_ID
AND WZR.REGION_ID = WLR.REGION_ID
AND HAOU.LOCATION_ID = ISM.FROM_LOCATION_ID
AND CCDT.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND CCDT.SHIPPING_METHOD = ISM.SHIP_METHOD
AND FLVV.LOOKUP_TYPE = 'SHIP_METHOD'
AND FLVV.LOOKUP_CODE = ISM.SHIP_METHOD