VIEW: APPS.MSC_SHIPMENT_DETAILS_V

Source

View Text - Preformatted

SELECT msp.rowid, msp.SHIPMENT_ID, ms.transaction_id, msp.plan_id , ms.ORGANIZATION_ID, ms.sr_instance_id , ms.inventory_item_id, mic.category_set_id , mic.sr_category_id , mic.category_name , msc_get_name.org_code(ms.organization_id, ms.sr_instance_id), msi.item_name, msi.buyer_name , msi.planner_code , 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), ms.new_order_quantity, 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), msp.weight/msp.WEIGHT_CAPACITY*100), decode(nvl(msp.volume_capacity, 0), 0, to_number(null), msp.volume/msp.VOLUME_CAPACITY*100), 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', ms.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, ms.new_order_placement_date, msi.product_family_id, nvl(ms.source_supplier_id, ms.supplier_id), trunc(msp.ship_date) - trunc(sysdate), msc_drp_util.material_avail_date(ms.plan_id, ms.transaction_id), ms.new_dock_date, 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 FROM msc_shipments msp, msc_system_items msi, msc_item_categories mic, msc_carrier_services mcs, msc_trading_partners mtp, msc_supplies ms WHERE MSI.inventory_item_id = ms.INVENTORY_ITEM_ID AND MSI.organization_id = ms.organization_id AND MSI.sr_instance_id = ms.sr_instance_id AND MSI.plan_id = ms.plan_id AND MS.sr_instance_id = msp.sr_instance_id AND MS.shipment_id = msp.shipment_id AND MS.plan_id = msp.plan_id AND mic.organization_id = msi.organization_id AND mic.sr_instance_id = msi.sr_instance_id AND mic.inventory_item_id = msi.inventory_item_id 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 union all SELECT msp.rowid, msp.SHIPMENT_ID, to_number(null), msp.plan_id , msp.to_ORGANIZATION_ID, msp.to_sr_instance_id , to_number(null), to_number(null), to_number(null), null, msc_get_name.org_code(msp.to_organization_id, msp.to_sr_instance_id), null, null, null, 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), to_number(null), 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), msp.weight/msp.WEIGHT_CAPACITY*100), decode(nvl(msp.volume_capacity, 0), 0, to_number(null), msp.volume/msp.VOLUME_CAPACITY*100), 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, to_date(null), to_number(null), to_number(null), to_number(null), to_date(null), to_date(null), 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 FROM msc_shipments msp, msc_carrier_services mcs, msc_trading_partners mtp WHERE 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
, MSP.SHIPMENT_ID
, MS.TRANSACTION_ID
, MSP.PLAN_ID
, MS.ORGANIZATION_ID
, MS.SR_INSTANCE_ID
, MS.INVENTORY_ITEM_ID
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, MSC_GET_NAME.ORG_CODE(MS.ORGANIZATION_ID
, MS.SR_INSTANCE_ID)
, MSI.ITEM_NAME
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, 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)
, MS.NEW_ORDER_QUANTITY
, 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)
, MSP.WEIGHT/MSP.WEIGHT_CAPACITY*100)
, DECODE(NVL(MSP.VOLUME_CAPACITY
, 0)
, 0
, TO_NUMBER(NULL)
, MSP.VOLUME/MSP.VOLUME_CAPACITY*100)
, 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'
, MS.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
, MS.NEW_ORDER_PLACEMENT_DATE
, MSI.PRODUCT_FAMILY_ID
, NVL(MS.SOURCE_SUPPLIER_ID
, MS.SUPPLIER_ID)
, TRUNC(MSP.SHIP_DATE) - TRUNC(SYSDATE)
, MSC_DRP_UTIL.MATERIAL_AVAIL_DATE(MS.PLAN_ID
, MS.TRANSACTION_ID)
, MS.NEW_DOCK_DATE
, 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
FROM MSC_SHIPMENTS MSP
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
, MSC_CARRIER_SERVICES MCS
, MSC_TRADING_PARTNERS MTP
, MSC_SUPPLIES MS
WHERE MSI.INVENTORY_ITEM_ID = MS.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MS.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID = MS.SR_INSTANCE_ID
AND MSI.PLAN_ID = MS.PLAN_ID
AND MS.SR_INSTANCE_ID = MSP.SR_INSTANCE_ID
AND MS.SHIPMENT_ID = MSP.SHIPMENT_ID
AND MS.PLAN_ID = MSP.PLAN_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
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 UNION ALL SELECT MSP.ROWID
, MSP.SHIPMENT_ID
, TO_NUMBER(NULL)
, MSP.PLAN_ID
, MSP.TO_ORGANIZATION_ID
, MSP.TO_SR_INSTANCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, MSC_GET_NAME.ORG_CODE(MSP.TO_ORGANIZATION_ID
, MSP.TO_SR_INSTANCE_ID)
, NULL
, NULL
, NULL
, 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)
, TO_NUMBER(NULL)
, 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)
, MSP.WEIGHT/MSP.WEIGHT_CAPACITY*100)
, DECODE(NVL(MSP.VOLUME_CAPACITY
, 0)
, 0
, TO_NUMBER(NULL)
, MSP.VOLUME/MSP.VOLUME_CAPACITY*100)
, 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
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, 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
FROM MSC_SHIPMENTS MSP
, MSC_CARRIER_SERVICES MCS
, MSC_TRADING_PARTNERS MTP
WHERE 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