VIEW: APPS.FTE_STOP_FREIGHT_CLASS_V

Source

View Text - Preformatted

SELECT wts.trip_id as "TRIP_ID", c.concatenated_segments as "FREIGHT_CLASS", SUM(WSH_WV_UTILS.convert_uom( NVL(wdd.weight_uom_code,msi.weight_uom_code), nvl(wts.weight_uom_code,(select GU_WEIGHT_UOM from wsh_global_parameters)), WSH_WV_UTILS.convert_uom( msi.weight_uom_code, NVL(wdd.weight_uom_code,msi.weight_uom_code), (NVL(wdd.unit_weight,msi.unit_weight) * WSH_WV_UTILS.convert_uom( wdd.requested_quantity_uom, msi.primary_uom_code, nvl(wdd.received_quantity, nvl(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity))), wdd.inventory_item_id) ), wdd.inventory_item_id ), wdd.inventory_item_id)) as "WEIGHT", max(nvl(wts.weight_uom_code,(select GU_WEIGHT_UOM from wsh_global_parameters))) as "WEIGHT_UOM_CODE", decode(wts.stop_id,wdl.pick_up_stop_id,'LOAD',wdl.drop_off_stop_id,'UNLOAD') as "ACTIVITY", DECODE(wts.physical_stop_id, NULL, wts.stop_id, wts.physical_stop_id ) as "STOP_ID" FROM mtl_categories_kfv c, mtl_category_sets s , mtl_item_categories mic, wsh_delivery_details wdd , wsh_delivery_assignments wda, wsh_delivery_legs wdl, wsh_trip_stops wts, mtl_units_of_measure weig, mtl_units_of_measure vol, mtl_system_items msi WHERE s.structure_id = c.structure_id AND s.category_set_name ='WSH_COMMODITY_CODE' AND c.enabled_flag ='Y' AND c.category_id = mic.category_id AND msi.inventory_item_id = wdd.inventory_item_id AND msi.organization_id = wdd.organization_id AND mic.inventory_item_id = wdd.inventory_item_id AND mic.category_set_id = s.category_set_id AND mic.organization_id = wdd.org_id AND wda.DELIVERY_DETAIL_ID = wdd.DELIVERY_DETAIL_Id AND wda.delivery_id = wdl.delivery_id AND ( WDL.PICK_UP_STOP_ID = WTS.STOP_ID OR WDL.DROP_OFF_STOP_ID = WTS.STOP_ID) AND weig.uom_code (+)= wdd.weight_uom_code AND vol.uom_code (+)= wdd.volume_uom_code GROUP BY wts.trip_id, DECODE(wts.physical_stop_id, NULL, wts.stop_id, wts.physical_stop_id ), decode(wts.stop_id,wdl.pick_up_stop_id,'LOAD',wdl.drop_off_stop_id,'UNLOAD'), c.concatenated_segments

View Text - HTML Formatted

SELECT WTS.TRIP_ID AS "TRIP_ID"
, C.CONCATENATED_SEGMENTS AS "FREIGHT_CLASS"
, SUM(WSH_WV_UTILS.CONVERT_UOM( NVL(WDD.WEIGHT_UOM_CODE
, MSI.WEIGHT_UOM_CODE)
, NVL(WTS.WEIGHT_UOM_CODE
, (SELECT GU_WEIGHT_UOM
FROM WSH_GLOBAL_PARAMETERS))
, WSH_WV_UTILS.CONVERT_UOM( MSI.WEIGHT_UOM_CODE
, NVL(WDD.WEIGHT_UOM_CODE
, MSI.WEIGHT_UOM_CODE)
, (NVL(WDD.UNIT_WEIGHT
, MSI.UNIT_WEIGHT) * WSH_WV_UTILS.CONVERT_UOM( WDD.REQUESTED_QUANTITY_UOM
, MSI.PRIMARY_UOM_CODE
, NVL(WDD.RECEIVED_QUANTITY
, NVL(WDD.SHIPPED_QUANTITY
, NVL(WDD.PICKED_QUANTITY
, WDD.REQUESTED_QUANTITY)))
, WDD.INVENTORY_ITEM_ID) )
, WDD.INVENTORY_ITEM_ID )
, WDD.INVENTORY_ITEM_ID)) AS "WEIGHT"
, MAX(NVL(WTS.WEIGHT_UOM_CODE
, (SELECT GU_WEIGHT_UOM
FROM WSH_GLOBAL_PARAMETERS))) AS "WEIGHT_UOM_CODE"
, DECODE(WTS.STOP_ID
, WDL.PICK_UP_STOP_ID
, 'LOAD'
, WDL.DROP_OFF_STOP_ID
, 'UNLOAD') AS "ACTIVITY"
, DECODE(WTS.PHYSICAL_STOP_ID
, NULL
, WTS.STOP_ID
, WTS.PHYSICAL_STOP_ID ) AS "STOP_ID"
FROM MTL_CATEGORIES_KFV C
, MTL_CATEGORY_SETS S
, MTL_ITEM_CATEGORIES MIC
, WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_LEGS WDL
, WSH_TRIP_STOPS WTS
, MTL_UNITS_OF_MEASURE WEIG
, MTL_UNITS_OF_MEASURE VOL
, MTL_SYSTEM_ITEMS MSI
WHERE S.STRUCTURE_ID = C.STRUCTURE_ID
AND S.CATEGORY_SET_NAME ='WSH_COMMODITY_CODE'
AND C.ENABLED_FLAG ='Y'
AND C.CATEGORY_ID = MIC.CATEGORY_ID
AND MSI.INVENTORY_ITEM_ID = WDD.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = WDD.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = WDD.INVENTORY_ITEM_ID
AND MIC.CATEGORY_SET_ID = S.CATEGORY_SET_ID
AND MIC.ORGANIZATION_ID = WDD.ORG_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
AND ( WDL.PICK_UP_STOP_ID = WTS.STOP_ID OR WDL.DROP_OFF_STOP_ID = WTS.STOP_ID)
AND WEIG.UOM_CODE (+)= WDD.WEIGHT_UOM_CODE
AND VOL.UOM_CODE (+)= WDD.VOLUME_UOM_CODE GROUP BY WTS.TRIP_ID
, DECODE(WTS.PHYSICAL_STOP_ID
, NULL
, WTS.STOP_ID
, WTS.PHYSICAL_STOP_ID )
, DECODE(WTS.STOP_ID
, WDL.PICK_UP_STOP_ID
, 'LOAD'
, WDL.DROP_OFF_STOP_ID
, 'UNLOAD')
, C.CONCATENATED_SEGMENTS