VIEW: APPS.MSC_SUPPLIERORG_SHIP_METHODS_V

Source

View Text - Preformatted

SELECT reg.sr_instance_id from_sr_instance_id, /*vendor-org*/ reg.sr_instance_id to_sr_instance_id, reg.VENDOR_SITE_ID FROM_SUPPLIER_SITE_ID, reg.sr_tp_id TO_ORGANIZATION_ID, to_number(NULL) FROM_ORGANIZATION_ID, reg.TO_LOCATION_ID, reg.SHIP_METHOD, reg.INTRANSIT_TIME, reg.DEFAULT_FLAG, reg.region_id 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.leadtime_variability from ( select mtps.sr_tp_id , b.VENDOR_SITE_ID , region_type, region_id, min(REGION_TYPE) over ( partition by mism.sr_instance_id, TO_LOCATION_ID, b.VENDOR_SITE_ID,SHIP_METHOD) as min_reg_type, RANK() over ( partition by mism.sr_instance_id, TO_LOCATION_ID, b.VENDOR_SITE_ID,SHIP_METHOD ORDER BY default_flag) as rank, mism.transaction_id, mism.from_organization_id, mism.to_organization_id, mism.sr_instance_id, mism.sr_instance_id2, mism.ship_method, mism.time_uom_code, mism.default_flag, mism.from_location_id, mism.to_location_id, 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, mism.leadtime_variability from msc_interorg_ship_methods mism , msc_region_sites b, msc_trading_partner_sites mtps where mism.sr_instance_id = b.sr_instance_id and mism.from_region_id = b.region_id and mism.plan_id = -1 and mism.to_location_id <> -1 and mism.sr_instance_id = mtps.sr_instance_id and mism.to_location_id = mtps.sr_tp_site_id and mtps.partner_type = 3 order by b.region_type ) 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
, /*VENDOR-ORG*/ REG.SR_INSTANCE_ID TO_SR_INSTANCE_ID
, REG.VENDOR_SITE_ID FROM_SUPPLIER_SITE_ID
, REG.SR_TP_ID TO_ORGANIZATION_ID
, TO_NUMBER(NULL) FROM_ORGANIZATION_ID
, REG.TO_LOCATION_ID
, REG.SHIP_METHOD
, REG.INTRANSIT_TIME
, REG.DEFAULT_FLAG
, REG.REGION_ID 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.LEADTIME_VARIABILITY
FROM ( SELECT MTPS.SR_TP_ID
, B.VENDOR_SITE_ID
, REGION_TYPE
, REGION_ID
, MIN(REGION_TYPE) OVER ( PARTITION BY MISM.SR_INSTANCE_ID
, TO_LOCATION_ID
, B.VENDOR_SITE_ID
, SHIP_METHOD) AS MIN_REG_TYPE
, RANK() OVER ( PARTITION BY MISM.SR_INSTANCE_ID
, TO_LOCATION_ID
, B.VENDOR_SITE_ID
, SHIP_METHOD ORDER BY DEFAULT_FLAG) AS RANK
, MISM.TRANSACTION_ID
, MISM.FROM_ORGANIZATION_ID
, MISM.TO_ORGANIZATION_ID
, MISM.SR_INSTANCE_ID
, MISM.SR_INSTANCE_ID2
, MISM.SHIP_METHOD
, MISM.TIME_UOM_CODE
, MISM.DEFAULT_FLAG
, MISM.FROM_LOCATION_ID
, MISM.TO_LOCATION_ID
, 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
, MISM.LEADTIME_VARIABILITY
FROM MSC_INTERORG_SHIP_METHODS MISM
, MSC_REGION_SITES B
, MSC_TRADING_PARTNER_SITES MTPS
WHERE MISM.SR_INSTANCE_ID = B.SR_INSTANCE_ID
AND MISM.FROM_REGION_ID = B.REGION_ID
AND MISM.PLAN_ID = -1
AND MISM.TO_LOCATION_ID <> -1
AND MISM.SR_INSTANCE_ID = MTPS.SR_INSTANCE_ID
AND MISM.TO_LOCATION_ID = MTPS.SR_TP_SITE_ID
AND MTPS.PARTNER_TYPE = 3 ORDER BY B.REGION_TYPE ) REG
WHERE REG.MIN_REG_TYPE = REG.REGION_TYPE
AND REG.RANK = 1