VIEW: APPS.MSC_ORGCUSTOMER_SHIP_METHODS_V

Source

View Text - Preformatted

SELECT reg.sr_instance_id from_sr_instance_id, reg.sr_instance_id to_sr_instance_id, to_number(NULL) FROM_SUPPLIER_SITE_ID, to_number(NULL) TO_ORGANIZATION_ID, reg.sr_tp_id FROM_ORGANIZATION_ID, reg.TO_LOCATION_ID, reg.SHIP_METHOD, reg.INTRANSIT_TIME, reg.DEFAULT_FLAG, reg.region_id, reg.transaction_id, reg.time_uom_code, reg.from_location_id, reg.weight_capacity, reg.weight_uom, reg.volume_capacity, reg.volume_uom, reg.cost_per_weight_unit, reg.cost_per_volume_unit, reg.from_location_name, reg.to_location_name, reg.from_location_descr, reg.to_location_descr, reg.transport_cap_over_util_cost,reg.partner_id, reg.tp_site_id from ( select /*+ ORDERED*/ mtps.sr_tp_id, mism.from_location_id, b.LOCATION_ID TO_LOCATION_ID, region_type, region_id, min(REGION_TYPE) over ( partition by mism.sr_instance_id, FROM_LOCATION_ID , b.LOCATION_ID,SHIP_METHOD) as min_reg_type, RANK() over ( partition by mism.sr_instance_id, FROM_LOCATION_ID, b.LOCATION_ID,SHIP_METHOD ORDER BY default_flag) as rank, mism.transaction_id, mism.sr_instance_id, mism.sr_instance_id2, mism.ship_method, mism.time_uom_code, mism.default_flag, mism.weight_capacity, mism.weight_uom, mism.volume_capacity, mism.volume_uom, mism.cost_per_weight_unit, mism.cost_per_volume_unit, mism.from_location_name, mism.to_location_name, mism.from_location_descr, mism.to_location_descr, mism.intransit_time, mism.transport_cap_over_util_cost, dest.customer_id partner_id, dest.ship_to_site_id tp_site_id from msc_planning_orgs dest, msc_tp_site_id_lid loc, msc_region_locations_v b, msc_interorg_ship_methods mism, msc_trading_partner_sites mtps, msc_planning_orgs sr where sr.source_locations = 1 and mtps.sr_tp_id= sr.organization_id and mtps.sr_instance_id = sr.sr_instance_id and mtps.partner_type = 3 and mism.plan_id = -1 and mism.sr_instance_id = mtps.sr_instance_id and mism.from_location_id <> -1 and mism.from_location_id = mtps.sr_tp_site_id and b.region_id = mism.to_region_id and b.sr_instance_id =mism.sr_instance_id and loc.location_id = b.location_id and loc.sr_instance_id = b.sr_instance_id and loc.partner_type = 2 and dest.source_locations = 2 and dest.ship_to_site_id = loc.tp_site_id ) reg where reg.min_reg_type = reg.region_type and reg.rank = 1

View Text - HTML Formatted

SELECT REG.SR_INSTANCE_ID FROM_SR_INSTANCE_ID
, REG.SR_INSTANCE_ID TO_SR_INSTANCE_ID
, TO_NUMBER(NULL) FROM_SUPPLIER_SITE_ID
, TO_NUMBER(NULL) TO_ORGANIZATION_ID
, REG.SR_TP_ID FROM_ORGANIZATION_ID
, REG.TO_LOCATION_ID
, REG.SHIP_METHOD
, REG.INTRANSIT_TIME
, REG.DEFAULT_FLAG
, REG.REGION_ID
, REG.TRANSACTION_ID
, REG.TIME_UOM_CODE
, REG.FROM_LOCATION_ID
, REG.WEIGHT_CAPACITY
, REG.WEIGHT_UOM
, REG.VOLUME_CAPACITY
, REG.VOLUME_UOM
, REG.COST_PER_WEIGHT_UNIT
, REG.COST_PER_VOLUME_UNIT
, REG.FROM_LOCATION_NAME
, REG.TO_LOCATION_NAME
, REG.FROM_LOCATION_DESCR
, REG.TO_LOCATION_DESCR
, REG.TRANSPORT_CAP_OVER_UTIL_COST
, REG.PARTNER_ID
, REG.TP_SITE_ID
FROM ( SELECT /*+ ORDERED*/ MTPS.SR_TP_ID
, MISM.FROM_LOCATION_ID
, B.LOCATION_ID TO_LOCATION_ID
, REGION_TYPE
, REGION_ID
, MIN(REGION_TYPE) OVER ( PARTITION BY MISM.SR_INSTANCE_ID
, FROM_LOCATION_ID
, B.LOCATION_ID
, SHIP_METHOD) AS MIN_REG_TYPE
, RANK() OVER ( PARTITION BY MISM.SR_INSTANCE_ID
, FROM_LOCATION_ID
, B.LOCATION_ID
, SHIP_METHOD ORDER BY DEFAULT_FLAG) AS RANK
, MISM.TRANSACTION_ID
, MISM.SR_INSTANCE_ID
, MISM.SR_INSTANCE_ID2
, MISM.SHIP_METHOD
, MISM.TIME_UOM_CODE
, MISM.DEFAULT_FLAG
, MISM.WEIGHT_CAPACITY
, MISM.WEIGHT_UOM
, MISM.VOLUME_CAPACITY
, MISM.VOLUME_UOM
, MISM.COST_PER_WEIGHT_UNIT
, MISM.COST_PER_VOLUME_UNIT
, MISM.FROM_LOCATION_NAME
, MISM.TO_LOCATION_NAME
, MISM.FROM_LOCATION_DESCR
, MISM.TO_LOCATION_DESCR
, MISM.INTRANSIT_TIME
, MISM.TRANSPORT_CAP_OVER_UTIL_COST
, DEST.CUSTOMER_ID PARTNER_ID
, DEST.SHIP_TO_SITE_ID TP_SITE_ID
FROM MSC_PLANNING_ORGS DEST
, MSC_TP_SITE_ID_LID LOC
, MSC_REGION_LOCATIONS_V B
, MSC_INTERORG_SHIP_METHODS MISM
, MSC_TRADING_PARTNER_SITES MTPS
, MSC_PLANNING_ORGS SR
WHERE SR.SOURCE_LOCATIONS = 1
AND MTPS.SR_TP_ID= SR.ORGANIZATION_ID
AND MTPS.SR_INSTANCE_ID = SR.SR_INSTANCE_ID
AND MTPS.PARTNER_TYPE = 3
AND MISM.PLAN_ID = -1
AND MISM.SR_INSTANCE_ID = MTPS.SR_INSTANCE_ID
AND MISM.FROM_LOCATION_ID <> -1
AND MISM.FROM_LOCATION_ID = MTPS.SR_TP_SITE_ID
AND B.REGION_ID = MISM.TO_REGION_ID
AND B.SR_INSTANCE_ID =MISM.SR_INSTANCE_ID
AND LOC.LOCATION_ID = B.LOCATION_ID
AND LOC.SR_INSTANCE_ID = B.SR_INSTANCE_ID
AND LOC.PARTNER_TYPE = 2
AND DEST.SOURCE_LOCATIONS = 2
AND DEST.SHIP_TO_SITE_ID = LOC.TP_SITE_ID ) REG
WHERE REG.MIN_REG_TYPE = REG.REGION_TYPE
AND REG.RANK = 1