MSC_ATP_SHIP_NETWORK

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
PLAN_ID ORIGIN_ID ORIGIN_TYPE DESTINATION_ID DESTINATION_TYPE FROMLANECODE TOLANECODE DEFAULTTRANSPORTMODE

Name

PLAN_ID

ORIGIN_ID

ORIGIN_TYPE

DESTINATION_ID

DESTINATION_TYPE

FROMLANECODE

TOLANECODE

DEFAULTTRANSPORTMODE

Query

SQL_Statement
SELECT ship_net.plan_id, ship_net.origin_id, ship_net.origin_type, ship_net.destination_id, ship_net.destination_type, ship_net.fromlanecode, ship_net.tolanecode, decode(def_shp.carrier_id \|\| ':' \|\| def_shp.mode_of_transport \|\| ':' \|\| def_shp.service_level,'::',to_char(null), def_shp.carrier_id \|\| ':' \|\| def_shp.mode_of_transport \|\| ':' \|\| def_shp.service_level) defaulttransportmode FROM ( /* This will have lanes from Internal Org to Internal Org / SELECT DISTINCT mism.plan_id, mism.origin_id, mism.origin_type, mism.destination_id, mism.destination_type, mlaSrc.site_id fromlanecode, mlaDest.site_id tolanecode FROM msc_interorg_ship_methods mism, msc_location_associations mlaSrc, msc_location_associations mlaDest WHERE mism.plan_id = -1 AND mism.origin_type = 'IE' AND mlaSrc.site_type =3 AND mlaSrc.default_flag =1 AND mlaSrc.location_id = mism.origin_id AND mism.destination_type = 'IE' AND mlaDest.site_type =3 AND mlaDest.default_flag =1 AND mlaDest.location_id = mism.destination_id UNION ALL / This will have lanes from (External Org/Supplier Region/Supplier Zone) to (External Org/Customer region/zone) / SELECT DISTINCT mism.plan_id, mism.origin_id, mism.origin_type, mism.destination_id, mism.destination_type, mism.origin_id fromlanecode, mism.destination_id tolanecode FROM msc_interorg_ship_methods mism WHERE mism.plan_id = -1 AND mism.origin_type IN ('LE','R', 'Z') AND mism.destination_type IN('LE','R', 'Z') UNION ALL / This will have lanes from (Internal Org ) to (External Org/Customer region/zone) / SELECT DISTINCT mism.plan_id, mism.origin_id, mism.origin_type, mism.destination_id, mism.destination_type, mlaSrc.site_id fromlanecode, mism.destination_id tolanecode FROM msc_interorg_ship_methods mism, msc_location_associations mlaSrc WHERE mism.plan_id = -1 AND mism.origin_type = 'IE' AND mism.destination_type IN('LE','R', 'Z') AND mlaSrc.site_type =3 AND mlaSrc.default_flag =1 AND mlaSrc.location_id = mism.origin_id UNION ALL / This will have lanes from (External org/Supplier Region/zone) to Extrenal org(Customer) */ SELECT DISTINCT mism.plan_id, mism.origin_id, mism.origin_type, mism.destination_id, mism.destination_type, mism.origin_id fromlanecode, mlaDest.site_id tolanecode FROM msc_interorg_ship_methods mism, msc_location_associations mlaDest WHERE mism.plan_id = -1 AND mism.origin_type IN ('LE','R','Z') AND mism.destination_type ='IE' AND mlaDest.site_type =3 AND mlaDest.default_flag =1 AND mlaDest.location_id = mism.destination_id ) ship_net, msc_interorg_ship_methods def_shp WHERE ship_net.plan_id = def_shp.plan_id(+) AND ship_net.origin_id = def_shp.origin_id(+) AND ship_net.origin_type = def_shp.origin_type(+) AND ship_net.destination_id = def_shp.destination_id(+) AND ship_net.destination_type = def_shp.destination_type(+) AND def_shp.default_flag(+) = 1

SQL_Statement

SELECT ship_net.plan_id,

ship_net.origin_id,

ship_net.origin_type,

ship_net.destination_id,

ship_net.destination_type,

ship_net.fromlanecode,

ship_net.tolanecode,

decode(def_shp.carrier_id || ':' ||

def_shp.mode_of_transport || ':' ||

def_shp.service_level,'::',to_char(null),

def_shp.carrier_id || ':' ||

def_shp.mode_of_transport || ':' ||

def_shp.service_level) defaulttransportmode

FROM (

/* This will have lanes from Internal Org to Internal Org */

SELECT DISTINCT mism.plan_id,

mism.origin_id,

mism.origin_type,

mism.destination_id,

mism.destination_type,

mlaSrc.site_id fromlanecode,

mlaDest.site_id tolanecode

FROM msc_interorg_ship_methods mism,

msc_location_associations mlaSrc,

msc_location_associations mlaDest

WHERE mism.plan_id = -1

AND mism.origin_type = 'IE'

AND mlaSrc.site_type =3

AND mlaSrc.default_flag =1

AND mlaSrc.location_id = mism.origin_id

AND mism.destination_type = 'IE'

AND mlaDest.site_type =3

AND mlaDest.default_flag =1

AND mlaDest.location_id = mism.destination_id

UNION ALL

/* This will have lanes from (External Org/Supplier Region/Supplier Zone) to (External Org/Customer region/zone) */

SELECT DISTINCT mism.plan_id,

mism.origin_id,

mism.origin_type,

mism.destination_id,

mism.destination_type,

mism.origin_id fromlanecode,

mism.destination_id tolanecode

FROM msc_interorg_ship_methods mism

WHERE mism.plan_id = -1

AND mism.origin_type IN ('LE','R', 'Z')

AND mism.destination_type IN('LE','R', 'Z')

UNION ALL

/* This will have lanes from (Internal Org ) to (External Org/Customer region/zone) */

SELECT DISTINCT mism.plan_id,

mism.origin_id,

mism.origin_type,

mism.destination_id,

mism.destination_type,

mlaSrc.site_id fromlanecode,

mism.destination_id tolanecode

FROM msc_interorg_ship_methods mism,

msc_location_associations mlaSrc

WHERE mism.plan_id = -1

AND mism.origin_type = 'IE'

AND mism.destination_type IN('LE','R', 'Z')

AND mlaSrc.site_type =3

AND mlaSrc.default_flag =1

AND mlaSrc.location_id = mism.origin_id

UNION ALL

/* This will have lanes from (External org/Supplier Region/zone) to Extrenal org(Customer) */

SELECT DISTINCT mism.plan_id,

mism.origin_id,

mism.origin_type,

mism.destination_id,

mism.destination_type,

mism.origin_id fromlanecode,

mlaDest.site_id tolanecode

FROM msc_interorg_ship_methods mism,

msc_location_associations mlaDest

WHERE mism.plan_id = -1

AND mism.origin_type IN ('LE','R','Z')

AND mism.destination_type ='IE'

AND mlaDest.site_type =3

AND mlaDest.default_flag =1

AND mlaDest.location_id = mism.destination_id

) ship_net,

msc_interorg_ship_methods def_shp

WHERE ship_net.plan_id = def_shp.plan_id(+)

AND ship_net.origin_id = def_shp.origin_id(+)

AND ship_net.origin_type = def_shp.origin_type(+)

AND ship_net.destination_id = def_shp.destination_id(+)

AND ship_net.destination_type = def_shp.destination_type(+)

AND def_shp.default_flag(+) = 1