MSC_AP_INTERORG_SHIP_METHODS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
ORIGIN_TYPE DESTINATION_TYPE FROM_INTERNAL_LOCATION_ID FROM_INTERNAL_LOCATION_CODE TO_INTERNAL_LOCATION_ID TO_INTERNAL_LOCATION_CODE FROM_ZONE_ID FROM_ZONE_CODE ORG_ZON_COUNTRY_CODE ORIGIN_ZONE_TYPE TO_ZONE_ID TO_ZONE_CODE DEST_ZON_COUNTRY_CODE DEST_ZONE_TYPE FROM_LOCATION_ID FROM_LOCATION_CODE TO_LOCATION_ID TO_LOCATION_CODE FROM_REGION_ID FROM_REGION_CODE TO_REGION_ID TO_REGION_CODE ORG_REG_COUNTRY_CODE ORIGIN_REGION_TYPE ORIGIN_PARENT_REGION_CODE_1 ORIGIN_PARENT_REGION_CODE_2 ORIGIN_PARENT_REGION_CODE_3 ORIGIN_PARENT_REGION_CODE_4 ORIGIN_PARENT_REGION_CODE_5 ORIGIN_PARENT_REGION_CODE_6 ORIGIN_PARENT_REGION_CODE_7 ORIGIN_PARENT_REGION_CODE_8 DEST_REG_COUNTRY_CODE DEST_REGION_TYPE DEST_PARENT_REGION_CODE_1 DEST_PARENT_REGION_CODE_2 DEST_PARENT_REGION_CODE_3 DEST_PARENT_REGION_CODE_4 DEST_PARENT_REGION_CODE_5 DEST_PARENT_REGION_CODE_6 DEST_PARENT_REGION_CODE_7 DEST_PARENT_REGION_CODE_8 SHIP_METHOD CARRIER_ID CARRIER_NAME MODE_OF_TRANSPORT SERVICE_LEVEL TIME_UOM_CODE INTRANSIT_TIME LOAD_WEIGHT_UOM_CODE VOLUME_UOM_CODE CURRENCY_CODE DAILY_LOAD_WEIGHT_CAPACITY COST_PER_UNIT_LOAD_WEIGHT DAILY_VOLUME_CAPACITY DEFAULT_FLAG TRANSIT_TIME_ID WTT_ROW_ID WTTV_ROW_ID

Name

ORIGIN_TYPE

DESTINATION_TYPE

FROM_INTERNAL_LOCATION_ID

FROM_INTERNAL_LOCATION_CODE

TO_INTERNAL_LOCATION_ID

TO_INTERNAL_LOCATION_CODE

FROM_ZONE_ID

FROM_ZONE_CODE

ORG_ZON_COUNTRY_CODE

ORIGIN_ZONE_TYPE

TO_ZONE_ID

TO_ZONE_CODE

DEST_ZON_COUNTRY_CODE

DEST_ZONE_TYPE

FROM_LOCATION_ID

FROM_LOCATION_CODE

TO_LOCATION_ID

TO_LOCATION_CODE

FROM_REGION_ID

FROM_REGION_CODE

TO_REGION_ID

TO_REGION_CODE

ORG_REG_COUNTRY_CODE

ORIGIN_REGION_TYPE

ORIGIN_PARENT_REGION_CODE_1

ORIGIN_PARENT_REGION_CODE_2

ORIGIN_PARENT_REGION_CODE_3

ORIGIN_PARENT_REGION_CODE_4

ORIGIN_PARENT_REGION_CODE_5

ORIGIN_PARENT_REGION_CODE_6

ORIGIN_PARENT_REGION_CODE_7

ORIGIN_PARENT_REGION_CODE_8

DEST_REG_COUNTRY_CODE

DEST_REGION_TYPE

DEST_PARENT_REGION_CODE_1

DEST_PARENT_REGION_CODE_2

DEST_PARENT_REGION_CODE_3

DEST_PARENT_REGION_CODE_4

DEST_PARENT_REGION_CODE_5

DEST_PARENT_REGION_CODE_6

DEST_PARENT_REGION_CODE_7

DEST_PARENT_REGION_CODE_8

SHIP_METHOD

CARRIER_ID

CARRIER_NAME

MODE_OF_TRANSPORT

SERVICE_LEVEL

TIME_UOM_CODE

INTRANSIT_TIME

LOAD_WEIGHT_UOM_CODE

VOLUME_UOM_CODE

CURRENCY_CODE

DAILY_LOAD_WEIGHT_CAPACITY

COST_PER_UNIT_LOAD_WEIGHT

DAILY_VOLUME_CAPACITY

DEFAULT_FLAG

TRANSIT_TIME_ID

WTT_ROW_ID

WTTV_ROW_ID

Query

SQL_Statement
SELECT WTT.ORIGIN_TYPE, WTT.DESTINATION_TYPE, WTT.FROM_LOCATION_ID FROM_INTERNAL_LOCATION_ID, TO_CHAR(FROMINTLOC.LOCATION_ID) FROM_INTERNAL_LOCATION_CODE, WTT.TO_LOCATION_ID TO_INTERNAL_LOCATION_ID, TO_CHAR(TOINTLOC.LOCATION_ID) TO_INTERNAL_LOCATION_CODE, WTT.FROM_ZONE_ID, FROMZON.ZONE_NAME FROM_ZONE_CODE, FROMZON.COUNTRY_CODE ORG_ZON_COUNTRY_CODE, FROMZON.ZONE_TYPE ORIGIN_ZONE_TYPE, WTT.TO_ZONE_ID, TOZON.ZONE_NAME TO_ZONE_CODE, TOZON.COUNTRY_CODE DEST_ZON_COUNTRY_CODE, TOZON.ZONE_TYPE DEST_ZONE_TYPE, WTT.FROM_LOCATION_ID, TO_CHAR(FROMLOC.LOCATION_ID) FROM_LOCATION_CODE, WTT.TO_LOCATION_ID, TO_CHAR(TOLOC.LOCATION_ID) TO_LOCATION_CODE, WTT.FROM_REGION_ID, FROMREG.GEOGRAPHY_NAME FROM_REGION_CODE, WTT.TO_REGION_ID, TOREG.GEOGRAPHY_NAME TO_REGION_CODE, FROMREG.COUNTRY_CODE ORG_REG_COUNTRY_CODE, FROMREG.GEOGRAPHY_TYPE ORIGIN_REGION_TYPE, FROMREG.geography_element2 ORIGIN_PARENT_REGION_CODE_1, FROMREG.geography_element3 ORIGIN_PARENT_REGION_CODE_2, FROMREG.geography_element4 ORIGIN_PARENT_REGION_CODE_3, FROMREG.geography_element5 ORIGIN_PARENT_REGION_CODE_4, FROMREG.geography_element6 ORIGIN_PARENT_REGION_CODE_5, FROMREG.geography_element7 ORIGIN_PARENT_REGION_CODE_6, FROMREG.geography_element8 ORIGIN_PARENT_REGION_CODE_7, FROMREG.geography_element9 ORIGIN_PARENT_REGION_CODE_8, TOREG.COUNTRY_CODE DEST_REG_COUNTRY_CODE, TOREG.GEOGRAPHY_TYPE DEST_REGION_TYPE, TOREG.geography_element2 DEST_PARENT_REGION_CODE_1, TOREG.geography_element3 DEST_PARENT_REGION_CODE_2, TOREG.geography_element4 DEST_PARENT_REGION_CODE_3, TOREG.geography_element5 DEST_PARENT_REGION_CODE_4, TOREG.geography_element6 DEST_PARENT_REGION_CODE_5, TOREG.geography_element7 DEST_PARENT_REGION_CODE_6, TOREG.geography_element8 DEST_PARENT_REGION_CODE_7, TOREG.geography_element9 DEST_PARENT_REGION_CODE_8, NULL SHIP_METHOD, WTTV.CARRIER_ID, HZP.party_name CARRIER_NAME, MOT.MEANING MODE_OF_TRANSPORT, SRL.MEANING SERVICE_LEVEL, WTTV.TIME_UOM_CODE, WTTV.INTRANSIT_TIME, WTTV.LOAD_WEIGHT_UOM_CODE, WTTV.VOLUME_UOM_CODE, WTTV.CURRENCY_CODE, WTTV.DAILY_LOAD_WEIGHT_CAPACITY, WTTV.COST_PER_UNIT_LOAD_WEIGHT, WTTV.DAILY_VOLUME_CAPACITY, WTTV.DEFAULT_FLAG, WTTV.TRANSIT_TIME_ID, WTT.rowid wtt_row_id, WTTV.rowid wttv_row_id FROM WSH_TRANSIT_TIMES WTT, WSH_TRANSIT_TIME_VALUES WTTV, HR_LOCATIONS_ALL_F_VL FROMINTLOC, HR_LOCATIONS_ALL_F_VL TOINTLOC, HZ_GEOGRAPHIES FROMREG, HZ_GEOGRAPHIES TOREG, MSC_AP_ZONES_V FROMZON, MSC_AP_ZONES_V TOZON, HZ_LOCATIONS FROMLOC, HZ_LOCATIONS TOLOC, HZ_PARTIES HZP, FND_LOOKUP_VALUES_TL MOT, FND_LOOKUP_VALUES_TL SRL WHERE WTT.TRANSIT_TIME_ID = WTTV.TRANSIT_TIME_ID AND FROMINTLOC.LOCATION_ID (+) = WTT.FROM_LOCATION_ID AND TOINTLOC.LOCATION_ID (+) = WTT.TO_LOCATION_ID AND FROMREG.GEOGRAPHY_ID (+) = WTT.FROM_REGION_ID AND TOREG.GEOGRAPHY_ID (+) = WTT.TO_REGION_ID AND FROMZON.SR_ZONE_ID (+) = WTT.FROM_ZONE_ID AND TOZON.SR_ZONE_ID (+) = WTT.TO_ZONE_ID AND FROMLOC.LOCATION_ID(+) = WTT.FROM_LOCATION_ID AND TOLOC.LOCATION_ID(+) = WTT.TO_LOCATION_ID AND HZP.PARTY_ID(+) = WTTV.CARRIER_ID AND MOT.LOOKUP_CODE(+) = WTTV.MODE_OF_TRANSPORT AND MOT.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT' AND MOT.LANGUAGE(+) = USERENV('LANG') AND SRL.LOOKUP_CODE(+) = WTTV.SERVICE_LEVEL AND SRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS' AND SRL.LANGUAGE(+) = USERENV('LANG') AND EXISTS(SELECT 1 FROM WSH_ORG_CARRIER_SERVICES shipmethod WHERE shipmethod.MODE_OF_TRANSPORT = WTTV.MODE_OF_TRANSPORT AND shipmethod.SERVICE_LEVEL = WTTV.SERVICE_LEVEL AND shipmethod.CARRIER_ID = WTTV.CARRIER_ID AND shipmethod.ENABLED_FLAG = 'Y') and (TRUNC(sysdate) BETWEEN FROMINTLOC.effective_start_date(+) AND FROMINTLOC.effective_end_date(+) OR FROMINTLOC.effective_end_date(+) IS NULL) and (TRUNC(sysdate) BETWEEN TOINTLOC.effective_start_date(+) AND TOINTLOC.effective_end_date(+) OR TOINTLOC.effective_end_date(+) IS NULL)

SQL_Statement

SELECT WTT.ORIGIN_TYPE,

WTT.DESTINATION_TYPE,

WTT.FROM_LOCATION_ID FROM_INTERNAL_LOCATION_ID,

TO_CHAR(FROMINTLOC.LOCATION_ID) FROM_INTERNAL_LOCATION_CODE,

WTT.TO_LOCATION_ID TO_INTERNAL_LOCATION_ID,

TO_CHAR(TOINTLOC.LOCATION_ID) TO_INTERNAL_LOCATION_CODE,

WTT.FROM_ZONE_ID,

FROMZON.ZONE_NAME FROM_ZONE_CODE,

FROMZON.COUNTRY_CODE ORG_ZON_COUNTRY_CODE,

FROMZON.ZONE_TYPE ORIGIN_ZONE_TYPE,

WTT.TO_ZONE_ID,

TOZON.ZONE_NAME TO_ZONE_CODE,

TOZON.COUNTRY_CODE DEST_ZON_COUNTRY_CODE,

TOZON.ZONE_TYPE DEST_ZONE_TYPE,

WTT.FROM_LOCATION_ID,

TO_CHAR(FROMLOC.LOCATION_ID) FROM_LOCATION_CODE,

WTT.TO_LOCATION_ID,

TO_CHAR(TOLOC.LOCATION_ID) TO_LOCATION_CODE,

WTT.FROM_REGION_ID,

FROMREG.GEOGRAPHY_NAME FROM_REGION_CODE,

WTT.TO_REGION_ID,

TOREG.GEOGRAPHY_NAME TO_REGION_CODE,

FROMREG.COUNTRY_CODE ORG_REG_COUNTRY_CODE,

FROMREG.GEOGRAPHY_TYPE ORIGIN_REGION_TYPE,

FROMREG.geography_element2 ORIGIN_PARENT_REGION_CODE_1,

FROMREG.geography_element3 ORIGIN_PARENT_REGION_CODE_2,

FROMREG.geography_element4 ORIGIN_PARENT_REGION_CODE_3,

FROMREG.geography_element5 ORIGIN_PARENT_REGION_CODE_4,

FROMREG.geography_element6 ORIGIN_PARENT_REGION_CODE_5,

FROMREG.geography_element7 ORIGIN_PARENT_REGION_CODE_6,

FROMREG.geography_element8 ORIGIN_PARENT_REGION_CODE_7,

FROMREG.geography_element9 ORIGIN_PARENT_REGION_CODE_8,

TOREG.COUNTRY_CODE DEST_REG_COUNTRY_CODE,

TOREG.GEOGRAPHY_TYPE DEST_REGION_TYPE,

TOREG.geography_element2 DEST_PARENT_REGION_CODE_1,

TOREG.geography_element3 DEST_PARENT_REGION_CODE_2,

TOREG.geography_element4 DEST_PARENT_REGION_CODE_3,

TOREG.geography_element5 DEST_PARENT_REGION_CODE_4,

TOREG.geography_element6 DEST_PARENT_REGION_CODE_5,

TOREG.geography_element7 DEST_PARENT_REGION_CODE_6,

TOREG.geography_element8 DEST_PARENT_REGION_CODE_7,

TOREG.geography_element9 DEST_PARENT_REGION_CODE_8,

NULL SHIP_METHOD,

WTTV.CARRIER_ID,

HZP.party_name CARRIER_NAME,

MOT.MEANING MODE_OF_TRANSPORT,

SRL.MEANING SERVICE_LEVEL,

WTTV.TIME_UOM_CODE,

WTTV.INTRANSIT_TIME,

WTTV.LOAD_WEIGHT_UOM_CODE,

WTTV.VOLUME_UOM_CODE,

WTTV.CURRENCY_CODE,

WTTV.DAILY_LOAD_WEIGHT_CAPACITY,

WTTV.COST_PER_UNIT_LOAD_WEIGHT,

WTTV.DAILY_VOLUME_CAPACITY,

WTTV.DEFAULT_FLAG,

WTTV.TRANSIT_TIME_ID,

WTT.rowid wtt_row_id,

WTTV.rowid wttv_row_id

FROM WSH_TRANSIT_TIMES WTT,

WSH_TRANSIT_TIME_VALUES WTTV,

HR_LOCATIONS_ALL_F_VL FROMINTLOC,

HR_LOCATIONS_ALL_F_VL TOINTLOC,

HZ_GEOGRAPHIES FROMREG,

HZ_GEOGRAPHIES TOREG,

MSC_AP_ZONES_V FROMZON,

MSC_AP_ZONES_V TOZON,

HZ_LOCATIONS FROMLOC,

HZ_LOCATIONS TOLOC,

HZ_PARTIES HZP,

FND_LOOKUP_VALUES_TL MOT,

FND_LOOKUP_VALUES_TL SRL

WHERE WTT.TRANSIT_TIME_ID = WTTV.TRANSIT_TIME_ID

AND FROMINTLOC.LOCATION_ID (+) = WTT.FROM_LOCATION_ID

AND TOINTLOC.LOCATION_ID (+) = WTT.TO_LOCATION_ID

AND FROMREG.GEOGRAPHY_ID (+) = WTT.FROM_REGION_ID

AND TOREG.GEOGRAPHY_ID (+) = WTT.TO_REGION_ID

AND FROMZON.SR_ZONE_ID (+) = WTT.FROM_ZONE_ID

AND TOZON.SR_ZONE_ID (+) = WTT.TO_ZONE_ID

AND FROMLOC.LOCATION_ID(+) = WTT.FROM_LOCATION_ID

AND TOLOC.LOCATION_ID(+) = WTT.TO_LOCATION_ID

AND HZP.PARTY_ID(+) = WTTV.CARRIER_ID

AND MOT.LOOKUP_CODE(+) = WTTV.MODE_OF_TRANSPORT

AND MOT.LOOKUP_TYPE(+) = 'WSH_MODE_OF_TRANSPORT'

AND MOT.LANGUAGE(+) = USERENV('LANG')

AND SRL.LOOKUP_CODE(+) = WTTV.SERVICE_LEVEL

AND SRL.LOOKUP_TYPE(+) = 'WSH_SERVICE_LEVELS'

AND SRL.LANGUAGE(+) = USERENV('LANG')

AND EXISTS(SELECT 1 FROM WSH_ORG_CARRIER_SERVICES shipmethod

WHERE shipmethod.MODE_OF_TRANSPORT = WTTV.MODE_OF_TRANSPORT

AND shipmethod.SERVICE_LEVEL = WTTV.SERVICE_LEVEL

AND shipmethod.CARRIER_ID = WTTV.CARRIER_ID

AND shipmethod.ENABLED_FLAG = 'Y')

and (TRUNC(sysdate) BETWEEN FROMINTLOC.effective_start_date(+) AND FROMINTLOC.effective_end_date(+) OR FROMINTLOC.effective_end_date(+) IS NULL)

and (TRUNC(sysdate) BETWEEN TOINTLOC.effective_start_date(+) AND TOINTLOC.effective_end_date(+) OR TOINTLOC.effective_end_date(+) IS NULL)