eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.FTE_TRIP_STOP_ACTIVITIES_V

Source

View Text - Preformatted


SELECT DECODE(wts.physical_stop_id, NULL,wts.stop_sequence_number,(wts.stop_sequence_number+1)) as "SEQUENCE_NUMBER", wl.ui_location_code as "LOCATION", MAX(DECODE(wlo.owner_type,1,(SELECT organization_name FROM org_organization_definitions WHERE organization_id = wlo.owner_party_id), 2,(SELECT party_name FROM hz_parties WHERE party_id = wlo.owner_party_id and party_type <> 'PARTY_RELATIONSHIP') )) as "LOCATION_OWNER_NAME", MAX(DECODE(wlo.owner_type,1,'SHIP_FROM',2,'SHIP_TO')) as "LOCATION_TYPE", MAX(wl.ADDRESS1) as "ADDRESS", MAX(wl.city) AS "CITY", MAX(wl.STATE) AS "STATE", MAX(wl.COUNTRY) AS "COUNTRY", MAX(wl.POSTAL_CODE) AS "POSTAL_CODE", MAX( DECODE(wts.PHYSICAL_STOP_ID, NULL, NVL(wts.actual_arrival_date,wts.planned_arrival_date), (SELECT NVL(PHY.actual_arrival_date,PHY.planned_arrival_date) FROM WSH_TRIP_STOPS PHY WHERE PHY.STOP_ID=WTS.PHYSICAL_STOP_ID )) ) as "ARRIVAL_DATE" , MAX( DECODE(wts.PHYSICAL_STOP_ID, NULL, NVL(wts.actual_DEPARTURE_date,wts.planned_DEPARTURE_date), (SELECT NVL(PHY.actual_DEPARTURE_date,PHY.planned_DEPARTURE_date) FROM WSH_TRIP_STOPS PHY WHERE PHY.STOP_ID=WTS.PHYSICAL_STOP_ID )) ) as "DEPARTURE_DATE", NVL(activity,'NOACTIVITY') as "ACTIVITY", FVL.Meaning as "ACTIVITY_MEANING", SUM(pieces) as "PIECES", MAX(DECODE(wts.physical_location_id, NULL, wts.stop_location_id, wts.physical_location_id)) as "STOP_LOCATION_ID", DECODE(wts.physical_stop_id, NULL, wts.stop_id, wts.physical_stop_id ) as "STOP_ID", MAX(DECODE(activity,'LOAD',WTS.PICK_UP_WEIGHT,'UNLOAD',WTS.DROP_OFF_WEIGHT,NULL)) as "WEIGHT", MAX(WTS.WEIGHT_UOM_CODE) AS "WEIGHT_UOM", MAX(DECODE(activity,'LOAD',WTS.PICK_UP_VOLUME,'UNLOAD',WTS.DROP_OFF_VOLUME,NULL)) AS "VOLUME", MAX(WTS.VOLUME_UOM_CODE) AS "VOLUME_UOM", wts.trip_id AS "TRIP_ID" FROM (SELECT DISTINCT iwts.stop_sequence_number AS stop_sequence_number, iwts.stop_id AS stop_id, iwts.trip_id AS trip_id, DECODE(iwts.stop_id,wdl.pick_up_stop_id,'LOAD',wdl.drop_off_stop_id,'UNLOAD') AS activity, (SELECT SUM(DECODE(container_flag,'Y',1,NVL(NVL(wdd.received_quantity, NVL(wdd.shipped_quantity, NVL(wdd.picked_quantity, wdd.requested_quantity))),0))) FROM wsh_delivery_details wdd,wsh_delivery_assignments wda,wsh_delivery_legs wdl WHERE wda.delivery_detail_id = wdd.delivery_detail_id AND wda.parent_delivery_detail_id IS NULL AND wda.delivery_id = wdl.delivery_id AND (wdl.drop_off_stop_id = iwts.stop_id OR wdl.pick_up_stop_id = iwts.stop_id) ) AS pieces FROM wsh_trip_stops iwts,wsh_delivery_legs wdl WHERE iwts.stop_id = wdl.pick_up_stop_id OR iwts.stop_id = wdl.drop_off_stop_id ) activity_table, wsh_trip_stops wts, wsh_locations wl, wsh_location_owners wlo, fnd_lookup_values_vl fvl WHERE wts.stop_id = activity_table.stop_id(+) AND wts.trip_id = activity_table.trip_id(+) AND DECODE(wts.physical_location_id, NULL, wts.stop_location_id, wts.physical_location_id) = wl.wsh_location_id AND wl.wsh_location_id = wlo.wsh_location_id(+) AND NVL(activity_table.activity,'NOACTIVITY') = fvl.lookup_code AND fvl.lookup_type = 'FTE_STOP_ACTIVITY_TYPES' GROUP BY wts.trip_id, DECODE(wts.physical_stop_id, NULL,wts.stop_sequence_number,(wts.stop_sequence_number+1)), DECODE(wts.physical_stop_id, NULL, wts.stop_id, wts.physical_stop_id), wl.ui_location_code, activity, fvl.meaning ORDER BY 1

View Text - HTML Formatted

SELECT DECODE(WTS.PHYSICAL_STOP_ID
, NULL
, WTS.STOP_SEQUENCE_NUMBER
, (WTS.STOP_SEQUENCE_NUMBER+1)) AS "SEQUENCE_NUMBER"
, WL.UI_LOCATION_CODE AS "LOCATION"
, MAX(DECODE(WLO.OWNER_TYPE
, 1
, (SELECT ORGANIZATION_NAME
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = WLO.OWNER_PARTY_ID)
, 2
, (SELECT PARTY_NAME
FROM HZ_PARTIES
WHERE PARTY_ID = WLO.OWNER_PARTY_ID
AND PARTY_TYPE <> 'PARTY_RELATIONSHIP') )) AS "LOCATION_OWNER_NAME"
, MAX(DECODE(WLO.OWNER_TYPE
, 1
, 'SHIP_FROM'
, 2
, 'SHIP_TO')) AS "LOCATION_TYPE"
, MAX(WL.ADDRESS1) AS "ADDRESS"
, MAX(WL.CITY) AS "CITY"
, MAX(WL.STATE) AS "STATE"
, MAX(WL.COUNTRY) AS "COUNTRY"
, MAX(WL.POSTAL_CODE) AS "POSTAL_CODE"
, MAX( DECODE(WTS.PHYSICAL_STOP_ID
, NULL
, NVL(WTS.ACTUAL_ARRIVAL_DATE
, WTS.PLANNED_ARRIVAL_DATE)
, (SELECT NVL(PHY.ACTUAL_ARRIVAL_DATE
, PHY.PLANNED_ARRIVAL_DATE)
FROM WSH_TRIP_STOPS PHY
WHERE PHY.STOP_ID=WTS.PHYSICAL_STOP_ID )) ) AS "ARRIVAL_DATE"
, MAX( DECODE(WTS.PHYSICAL_STOP_ID
, NULL
, NVL(WTS.ACTUAL_DEPARTURE_DATE
, WTS.PLANNED_DEPARTURE_DATE)
, (SELECT NVL(PHY.ACTUAL_DEPARTURE_DATE
, PHY.PLANNED_DEPARTURE_DATE)
FROM WSH_TRIP_STOPS PHY
WHERE PHY.STOP_ID=WTS.PHYSICAL_STOP_ID )) ) AS "DEPARTURE_DATE"
, NVL(ACTIVITY
, 'NOACTIVITY') AS "ACTIVITY"
, FVL.MEANING AS "ACTIVITY_MEANING"
, SUM(PIECES) AS "PIECES"
, MAX(DECODE(WTS.PHYSICAL_LOCATION_ID
, NULL
, WTS.STOP_LOCATION_ID
, WTS.PHYSICAL_LOCATION_ID)) AS "STOP_LOCATION_ID"
, DECODE(WTS.PHYSICAL_STOP_ID
, NULL
, WTS.STOP_ID
, WTS.PHYSICAL_STOP_ID ) AS "STOP_ID"
, MAX(DECODE(ACTIVITY
, 'LOAD'
, WTS.PICK_UP_WEIGHT
, 'UNLOAD'
, WTS.DROP_OFF_WEIGHT
, NULL)) AS "WEIGHT"
, MAX(WTS.WEIGHT_UOM_CODE) AS "WEIGHT_UOM"
, MAX(DECODE(ACTIVITY
, 'LOAD'
, WTS.PICK_UP_VOLUME
, 'UNLOAD'
, WTS.DROP_OFF_VOLUME
, NULL)) AS "VOLUME"
, MAX(WTS.VOLUME_UOM_CODE) AS "VOLUME_UOM"
, WTS.TRIP_ID AS "TRIP_ID"
FROM (SELECT DISTINCT IWTS.STOP_SEQUENCE_NUMBER AS STOP_SEQUENCE_NUMBER
, IWTS.STOP_ID AS STOP_ID
, IWTS.TRIP_ID AS TRIP_ID
, DECODE(IWTS.STOP_ID
, WDL.PICK_UP_STOP_ID
, 'LOAD'
, WDL.DROP_OFF_STOP_ID
, 'UNLOAD') AS ACTIVITY
, (SELECT SUM(DECODE(CONTAINER_FLAG
, 'Y'
, 1
, NVL(NVL(WDD.RECEIVED_QUANTITY
, NVL(WDD.SHIPPED_QUANTITY
, NVL(WDD.PICKED_QUANTITY
, WDD.REQUESTED_QUANTITY)))
, 0)))
FROM WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_LEGS WDL
WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL
AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
AND (WDL.DROP_OFF_STOP_ID = IWTS.STOP_ID OR WDL.PICK_UP_STOP_ID = IWTS.STOP_ID) ) AS PIECES
FROM WSH_TRIP_STOPS IWTS
, WSH_DELIVERY_LEGS WDL
WHERE IWTS.STOP_ID = WDL.PICK_UP_STOP_ID OR IWTS.STOP_ID = WDL.DROP_OFF_STOP_ID ) ACTIVITY_TABLE
, WSH_TRIP_STOPS WTS
, WSH_LOCATIONS WL
, WSH_LOCATION_OWNERS WLO
, FND_LOOKUP_VALUES_VL FVL
WHERE WTS.STOP_ID = ACTIVITY_TABLE.STOP_ID(+)
AND WTS.TRIP_ID = ACTIVITY_TABLE.TRIP_ID(+)
AND DECODE(WTS.PHYSICAL_LOCATION_ID
, NULL
, WTS.STOP_LOCATION_ID
, WTS.PHYSICAL_LOCATION_ID) = WL.WSH_LOCATION_ID
AND WL.WSH_LOCATION_ID = WLO.WSH_LOCATION_ID(+)
AND NVL(ACTIVITY_TABLE.ACTIVITY
, 'NOACTIVITY') = FVL.LOOKUP_CODE
AND FVL.LOOKUP_TYPE = 'FTE_STOP_ACTIVITY_TYPES' GROUP BY WTS.TRIP_ID
, DECODE(WTS.PHYSICAL_STOP_ID
, NULL
, WTS.STOP_SEQUENCE_NUMBER
, (WTS.STOP_SEQUENCE_NUMBER+1))
, DECODE(WTS.PHYSICAL_STOP_ID
, NULL
, WTS.STOP_ID
, WTS.PHYSICAL_STOP_ID)
, WL.UI_LOCATION_CODE
, ACTIVITY
, FVL.MEANING ORDER BY 1