VIEW: APPS.MST_PLAN_SUMMARY_V

Source

View Text - Preformatted

SELECT plan_id , 'Total' , MST_AGG_PKG.get_loads_count(plan_id) , total_tl_cost + total_ltl_cost + total_parcel_cost , total_weight , total_volume , total_pallets , total_pieces , MST_AGG_PKG.get_loads_orders(plan_id) , 1 , currency_uom , weight_uom , volume_uom from mst_plans union all select plan_id , 'TLs' , MST_AGG_PKG.get_trip_count(plan_id, 'TRUCK') , total_tl_cost , total_tl_weight , total_tl_volume , total_tl_pallets , total_tl_pieces , MST_AGG_PKG.get_trip_orders(plan_id, 'TRUCK') , 2 , currency_uom , weight_uom , volume_uom from mst_plans union all select plan_id , 'LTLs' , MST_AGG_PKG.get_trip_count(plan_id, 'LTL') , total_ltl_cost , total_ltl_weight , total_ltl_volume , total_ltl_pallets , total_ltl_pieces , MST_AGG_PKG.get_trip_orders(plan_id, 'LTL') , 3 , currency_uom , weight_uom , volume_uom from mst_plans union all select plan_id , 'Parcels' , MST_AGG_PKG.get_trip_count(plan_id, 'PARCEL') , total_parcel_cost , total_parcel_weight , total_parcel_volume , total_parcel_pallets , total_parcel_pieces , MST_AGG_PKG.get_trip_orders(plan_id, 'PARCEL') , 4 , currency_uom , weight_uom , volume_uom from mst_plans union all select plan_id , 'Continuous Moves' , MST_AGG_PKG.get_cm_count(plan_id) , total_cm_cost , total_cm_weight , total_cm_volume , total_cm_pallets , total_cm_pieces , MST_AGG_PKG.get_cm_orders(plan_id) , 5 , currency_uom , weight_uom , volume_uom from mst_plans union all select plan_id , 'Unassigned Orders' , MST_AGG_PKG.get_unassigned_order_count(plan_id) , 0 , MST_AGG_PKG.get_unassigned_order_weight(plan_id) , MST_AGG_PKG.get_unassigned_order_cube(plan_id) , MST_AGG_PKG.get_unassigned_order_pallets(plan_id) , MST_AGG_PKG.get_unassigned_order_pieces(plan_id) , MST_AGG_PKG.get_unassigned_order_count(plan_id) , 6 , currency_uom , weight_uom , volume_uom from mst_plans

View Text - HTML Formatted

SELECT PLAN_ID
, 'TOTAL'
, MST_AGG_PKG.GET_LOADS_COUNT(PLAN_ID)
, TOTAL_TL_COST + TOTAL_LTL_COST + TOTAL_PARCEL_COST
, TOTAL_WEIGHT
, TOTAL_VOLUME
, TOTAL_PALLETS
, TOTAL_PIECES
, MST_AGG_PKG.GET_LOADS_ORDERS(PLAN_ID)
, 1
, CURRENCY_UOM
, WEIGHT_UOM
, VOLUME_UOM
FROM MST_PLANS UNION ALL SELECT PLAN_ID
, 'TLS'
, MST_AGG_PKG.GET_TRIP_COUNT(PLAN_ID
, 'TRUCK')
, TOTAL_TL_COST
, TOTAL_TL_WEIGHT
, TOTAL_TL_VOLUME
, TOTAL_TL_PALLETS
, TOTAL_TL_PIECES
, MST_AGG_PKG.GET_TRIP_ORDERS(PLAN_ID
, 'TRUCK')
, 2
, CURRENCY_UOM
, WEIGHT_UOM
, VOLUME_UOM
FROM MST_PLANS UNION ALL SELECT PLAN_ID
, 'LTLS'
, MST_AGG_PKG.GET_TRIP_COUNT(PLAN_ID
, 'LTL')
, TOTAL_LTL_COST
, TOTAL_LTL_WEIGHT
, TOTAL_LTL_VOLUME
, TOTAL_LTL_PALLETS
, TOTAL_LTL_PIECES
, MST_AGG_PKG.GET_TRIP_ORDERS(PLAN_ID
, 'LTL')
, 3
, CURRENCY_UOM
, WEIGHT_UOM
, VOLUME_UOM
FROM MST_PLANS UNION ALL SELECT PLAN_ID
, 'PARCELS'
, MST_AGG_PKG.GET_TRIP_COUNT(PLAN_ID
, 'PARCEL')
, TOTAL_PARCEL_COST
, TOTAL_PARCEL_WEIGHT
, TOTAL_PARCEL_VOLUME
, TOTAL_PARCEL_PALLETS
, TOTAL_PARCEL_PIECES
, MST_AGG_PKG.GET_TRIP_ORDERS(PLAN_ID
, 'PARCEL')
, 4
, CURRENCY_UOM
, WEIGHT_UOM
, VOLUME_UOM
FROM MST_PLANS UNION ALL SELECT PLAN_ID
, 'CONTINUOUS MOVES'
, MST_AGG_PKG.GET_CM_COUNT(PLAN_ID)
, TOTAL_CM_COST
, TOTAL_CM_WEIGHT
, TOTAL_CM_VOLUME
, TOTAL_CM_PALLETS
, TOTAL_CM_PIECES
, MST_AGG_PKG.GET_CM_ORDERS(PLAN_ID)
, 5
, CURRENCY_UOM
, WEIGHT_UOM
, VOLUME_UOM
FROM MST_PLANS UNION ALL SELECT PLAN_ID
, 'UNASSIGNED ORDERS'
, MST_AGG_PKG.GET_UNASSIGNED_ORDER_COUNT(PLAN_ID)
, 0
, MST_AGG_PKG.GET_UNASSIGNED_ORDER_WEIGHT(PLAN_ID)
, MST_AGG_PKG.GET_UNASSIGNED_ORDER_CUBE(PLAN_ID)
, MST_AGG_PKG.GET_UNASSIGNED_ORDER_PALLETS(PLAN_ID)
, MST_AGG_PKG.GET_UNASSIGNED_ORDER_PIECES(PLAN_ID)
, MST_AGG_PKG.GET_UNASSIGNED_ORDER_COUNT(PLAN_ID)
, 6
, CURRENCY_UOM
, WEIGHT_UOM
, VOLUME_UOM
FROM MST_PLANS