VIEW: APPS.MSC_SHIPMENTS_V

Source

View Text - Preformatted

SELECT msp.rowid, mfq.query_id, msp.SHIPMENT_ID, msp.plan_id , msp.ship_date, msp.dock_date, msp.from_organization_id, msp.sr_instance_id, msc_get_name.org_code(msp.from_organization_id, msp.sr_instance_id), msp.to_organization_id, msp.to_sr_instance_id, msc_get_name.org_code(msp.to_organization_id, msp.to_sr_instance_id), decode(msp.trip_name,null,2,1), msp.trip_name, nvl(msp.weight,0), nvl(msp.volume,0), nvl(mfq.number5,0), msp.WEIGHT_UOM_CODE, msp.VOLUME_UOM_CODE, msp.WEIGHT_CAPACITY - msp.weight, msp.VOLUME_CAPACITY - msp.volume, decode(nvl(msp.weight_capacity, 0), 0, to_number(null), round(msp.weight/msp.WEIGHT_CAPACITY *100,2)), decode(nvl(msp.volume_capacity, 0), 0, to_number(null), round(msp.volume/msp.VOLUME_CAPACITY *100,2)), msp.SHIP_METHOD, msc_get_name.lookup_meaning1('WSH_MODE_OF_TRANSPORT', MCS.mode_of_transport, 665, 0), mcs.SERVICE_LEVEL, mcs.carrier_id, mtp.PARTNER_NAME, decode(msp.released_flag, 'S', 1,2), decode(msp.released_flag, 'R', 1,2), decode(msp.released_flag, 'R', msp.LAST_UPDATE_DATE,to_date(null)), null, nvl(msp.firm_flag,2), msp.LAST_UPDATE_DATE , msp.LAST_UPDATED_BY , msp.CREATION_DATE , msp.CREATED_BY , msp.LAST_UPDATE_LOGIN, msp.status, msp.applied, msp.lead_time, mfq.date1, mfq.date2, msc_drp_util.cost_under_util(msp.plan_id,msp.weight_capacity,msp.volume_capacity,msp.weight,msp.volume,msp.from_organization_id,msp.sr_instance_id,msp.to_organization_id,msp.to_sr_instance_id,msp.SHIP_METHOD), decode(nvl(msp.weight_capacity, 0), 0, to_number(null), (msp.weight_capacity-msp.weight)/msp.WEIGHT_CAPACITY *100), decode(nvl(msp.volume_capacity, 0), 0, to_number(null), (msp.volume_capacity-msp.volume)/msp.VOLUME_CAPACITY *100), msp.WEIGHT_CAPACITY, msp.VOLUME_CAPACITY, mcs.MODE_OF_TRANSPORT FROM msc_form_query mfq, msc_shipments msp, msc_carrier_services mcs, msc_trading_partners mtp WHERE msp.shipment_id = mfq.number2 and msp.plan_id = mfq.number1 AND mcs.ship_method_code(+) = msp.ship_method AND mcs.sr_instance_id(+) = msp.sr_instance_id AND mtp.partner_id(+) = mcs.carrier_id AND mtp.sr_instance_id(+) = mcs.sr_instance_id AND mtp.partner_type(+) = 4

View Text - HTML Formatted

SELECT MSP.ROWID
, MFQ.QUERY_ID
, MSP.SHIPMENT_ID
, MSP.PLAN_ID
, MSP.SHIP_DATE
, MSP.DOCK_DATE
, MSP.FROM_ORGANIZATION_ID
, MSP.SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(MSP.FROM_ORGANIZATION_ID
, MSP.SR_INSTANCE_ID)
, MSP.TO_ORGANIZATION_ID
, MSP.TO_SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(MSP.TO_ORGANIZATION_ID
, MSP.TO_SR_INSTANCE_ID)
, DECODE(MSP.TRIP_NAME
, NULL
, 2
, 1)
, MSP.TRIP_NAME
, NVL(MSP.WEIGHT
, 0)
, NVL(MSP.VOLUME
, 0)
, NVL(MFQ.NUMBER5
, 0)
, MSP.WEIGHT_UOM_CODE
, MSP.VOLUME_UOM_CODE
, MSP.WEIGHT_CAPACITY - MSP.WEIGHT
, MSP.VOLUME_CAPACITY - MSP.VOLUME
, DECODE(NVL(MSP.WEIGHT_CAPACITY
, 0)
, 0
, TO_NUMBER(NULL)
, ROUND(MSP.WEIGHT/MSP.WEIGHT_CAPACITY *100
, 2))
, DECODE(NVL(MSP.VOLUME_CAPACITY
, 0)
, 0
, TO_NUMBER(NULL)
, ROUND(MSP.VOLUME/MSP.VOLUME_CAPACITY *100
, 2))
, MSP.SHIP_METHOD
, MSC_GET_NAME.LOOKUP_MEANING1('WSH_MODE_OF_TRANSPORT'
, MCS.MODE_OF_TRANSPORT
, 665
, 0)
, MCS.SERVICE_LEVEL
, MCS.CARRIER_ID
, MTP.PARTNER_NAME
, DECODE(MSP.RELEASED_FLAG
, 'S'
, 1
, 2)
, DECODE(MSP.RELEASED_FLAG
, 'R'
, 1
, 2)
, DECODE(MSP.RELEASED_FLAG
, 'R'
, MSP.LAST_UPDATE_DATE
, TO_DATE(NULL))
, NULL
, NVL(MSP.FIRM_FLAG
, 2)
, MSP.LAST_UPDATE_DATE
, MSP.LAST_UPDATED_BY
, MSP.CREATION_DATE
, MSP.CREATED_BY
, MSP.LAST_UPDATE_LOGIN
, MSP.STATUS
, MSP.APPLIED
, MSP.LEAD_TIME
, MFQ.DATE1
, MFQ.DATE2
, MSC_DRP_UTIL.COST_UNDER_UTIL(MSP.PLAN_ID
, MSP.WEIGHT_CAPACITY
, MSP.VOLUME_CAPACITY
, MSP.WEIGHT
, MSP.VOLUME
, MSP.FROM_ORGANIZATION_ID
, MSP.SR_INSTANCE_ID
, MSP.TO_ORGANIZATION_ID
, MSP.TO_SR_INSTANCE_ID
, MSP.SHIP_METHOD)
, DECODE(NVL(MSP.WEIGHT_CAPACITY
, 0)
, 0
, TO_NUMBER(NULL)
, (MSP.WEIGHT_CAPACITY-MSP.WEIGHT)/MSP.WEIGHT_CAPACITY *100)
, DECODE(NVL(MSP.VOLUME_CAPACITY
, 0)
, 0
, TO_NUMBER(NULL)
, (MSP.VOLUME_CAPACITY-MSP.VOLUME)/MSP.VOLUME_CAPACITY *100)
, MSP.WEIGHT_CAPACITY
, MSP.VOLUME_CAPACITY
, MCS.MODE_OF_TRANSPORT
FROM MSC_FORM_QUERY MFQ
, MSC_SHIPMENTS MSP
, MSC_CARRIER_SERVICES MCS
, MSC_TRADING_PARTNERS MTP
WHERE MSP.SHIPMENT_ID = MFQ.NUMBER2
AND MSP.PLAN_ID = MFQ.NUMBER1
AND MCS.SHIP_METHOD_CODE(+) = MSP.SHIP_METHOD
AND MCS.SR_INSTANCE_ID(+) = MSP.SR_INSTANCE_ID
AND MTP.PARTNER_ID(+) = MCS.CARRIER_ID
AND MTP.SR_INSTANCE_ID(+) = MCS.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE(+) = 4