View: FTE_PRC_LANE_COMP_UI_V

Product:	FTE - Transportation Execution
Description:	used by the price comparison user interface
Implementation/DBA Data:	APPS.FTE_PRC_LANE_COMP_UI_V

View Text

SELECT TO_CHAR(WFCTMP.DELIVERY_DETAIL_ID) AS ENTITY_ID
, NVL(TO_CHAR(WDA.PARENT_DELIVERY_DETAIL_ID)
, TO_CHAR(NVL(WFCTMP.LANE_ID
, -1*WFCTMP.SCHEDULE_ID))||' '||WFCTMP.SERVICE_TYPE_CODE) AS PARENT_ENTITY_ID
, TO_CHAR(NVL(WFCTMP.LANE_ID
, -1*WFCTMP.SCHEDULE_ID))||' '||WFCTMP.SERVICE_TYPE_CODE AS LANE_SCHED_ID
, WFCTMP.COMPARISON_REQUEST_ID AS COMPARISON_REQUEST_ID
, WFCTMP.DELIVERY_ID AS DELIVERY_ID
, WFCTMP.LANE_ID AS LANE_ID
, WFCTMP.SCHEDULE_ID AS SCHEDULE_ID
, TO_CHAR(WFCTMP.DELIVERY_DETAIL_ID) AS ENTITY_NAME
, MSIK.CONCATENATED_SEGMENTS AS ITEM_NAME
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('WSH_SERVICE_LEVELS'
, WFCTMP.SERVICE_TYPE_CODE) AS SERVICE_TYPE
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('CONTAINER_TYPE'
, MSIK.CONTAINER_TYPE_CODE) AS CONTAINER_TYPE
, DECODE(WDD.CONTAINER_FLAG
, 'Y'
, WDD.GROSS_WEIGHT
, NVL(WDD.NET_WEIGHT
, WDD.GROSS_WEIGHT)) ||' '||WDD.WEIGHT_UOM_CODE AS WEIGHT
, WDD.VOLUME||' '||WDD.VOLUME_UOM_CODE AS VOLUME
, WFCTMP.TOTAL_AMOUNT||' '||WFCTMP.CURRENCY_CODE AS PRICE
, WFCTMP.CREATION_DATE AS CREATION_DATE
, WFCTMP.CREATED_BY AS CREATED_BY
, WFCTMP.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, WFCTMP.LAST_UPDATED_BY AS LAST_UPDATED_BY
, WFCTMP.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, WFCTMP.PROGRAM_APPLICATION_ID AS PROGRAM_APPLICATION_ID
, WFCTMP.PROGRAM_ID AS PROGRAM_ID
, WFCTMP.PROGRAM_UPDATE_DATE AS PROGRAM_UPDATE_DATE
, WFCTMP.REQUEST_ID AS REQUEST_ID
FROM FTE_FREIGHT_COSTS_TEMP WFCTMP
, WSH_DELIVERY_DETAILS WDD
, MTL_SYSTEM_ITEMS_KFV MSIK
, WSH_DELIVERY_ASSIGNMENTS WDA
WHERE WFCTMP.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND WDD.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WFCTMP.LINE_TYPE_CODE IN ('PRICE'
, 'SUMMARY')
AND WFCTMP.CHARGE_SOURCE_CODE = 'PRICING_ENGINE' UNION SELECT TO_CHAR(-1*WFCTMP.SCHEDULE_ID)||' '||WFCTMP.SERVICE_TYPE_CODE AS ENTITY_ID
, TO_CHAR(WFCTMP.DELIVERY_ID) AS PARENT_ENTITY_ID
, TO_CHAR(NVL(WFCTMP.LANE_ID
, -1*WFCTMP.SCHEDULE_ID))||' '||WFCTMP.SERVICE_TYPE_CODE AS LANE_SCHED_ID
, WFCTMP.COMPARISON_REQUEST_ID AS COMPARISON_REQUEST_ID
, WFCTMP.DELIVERY_ID AS DELIVERY_ID
, WFCTMP.LANE_ID AS LANE_ID
, WFCTMP.SCHEDULE_ID AS SCHEDULE_ID
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('FTE_PRC_UI_COST_TYPE'
, 'SCHEDULE')||': '||TO_CHAR(WFCTMP.SCHEDULE_ID)||' '||FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('WSH_SERVICE_LEVELS'
, WFCTMP.SERVICE_TYPE_CODE) AS ENTITY_NAME
, NULL AS ITEM_NAME
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('WSH_SERVICE_LEVELS'
, WFCTMP.SERVICE_TYPE_CODE) AS SERVICE_TYPE
, NULL AS CONTAINER_TYPE
, NULL AS WEIGHT
, NULL AS VOLUME
, WFCTMP.TOTAL_AMOUNT||' '||WFCTMP.CURRENCY_CODE AS PRICE
, WFCTMP.CREATION_DATE AS CREATION_DATE
, WFCTMP.CREATED_BY AS CREATED_BY
, WFCTMP.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, WFCTMP.LAST_UPDATED_BY AS LAST_UPDATED_BY
, WFCTMP.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, WFCTMP.PROGRAM_APPLICATION_ID AS PROGRAM_APPLICATION_ID
, WFCTMP.PROGRAM_ID AS PROGRAM_ID
, WFCTMP.PROGRAM_UPDATE_DATE AS PROGRAM_UPDATE_DATE
, WFCTMP.REQUEST_ID AS REQUEST_ID
FROM FTE_FREIGHT_COSTS_TEMP WFCTMP
WHERE WFCTMP.LINE_TYPE_CODE IN ('PRICE'
, 'SUMMARY')
AND WFCTMP.CHARGE_SOURCE_CODE = 'PRICING_ENGINE'
AND WFCTMP.DELIVERY_DETAIL_ID IS NULL
AND WFCTMP.LANE_ID IS NULL
AND WFCTMP.SCHEDULE_ID IS NOT NULL UNION SELECT TO_CHAR(WFCTMP.LANE_ID)||' '||WFCTMP.SERVICE_TYPE_CODE AS ENTITY_ID
, TO_CHAR(WFCTMP.DELIVERY_ID) AS PARENT_ENTITY_ID
, TO_CHAR(NVL(WFCTMP.LANE_ID
, -1*WFCTMP.SCHEDULE_ID))||' '||WFCTMP.SERVICE_TYPE_CODE AS LANE_SCHED_ID
, WFCTMP.COMPARISON_REQUEST_ID AS COMPARISON_REQUEST_ID
, WFCTMP.DELIVERY_ID AS DELIVERY_ID
, WFCTMP.LANE_ID AS LANE_ID
, WFCTMP.SCHEDULE_ID AS SCHEDULE_ID
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('FTE_PRC_UI_COST_TYPE'
, 'LANE')||': '||FL.LANE_NUMBER||' '||FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('WSH_SERVICE_LEVELS'
, WFCTMP.SERVICE_TYPE_CODE) AS ENTITY_NAME
, NULL AS ITEM_NAME
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('WSH_SERVICE_LEVELS'
, WFCTMP.SERVICE_TYPE_CODE) AS SERVICE_TYPE
, NULL AS CONTAINER_TYPE
, NULL AS WEIGHT
, NULL AS VOLUME
, WFCTMP.TOTAL_AMOUNT||' '||WFCTMP.CURRENCY_CODE AS PRICE
, WFCTMP.CREATION_DATE AS CREATION_DATE
, WFCTMP.CREATED_BY AS CREATED_BY
, WFCTMP.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, WFCTMP.LAST_UPDATED_BY AS LAST_UPDATED_BY
, WFCTMP.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, WFCTMP.PROGRAM_APPLICATION_ID AS PROGRAM_APPLICATION_ID
, WFCTMP.PROGRAM_ID AS PROGRAM_ID
, WFCTMP.PROGRAM_UPDATE_DATE AS PROGRAM_UPDATE_DATE
, WFCTMP.REQUEST_ID AS REQUEST_ID
FROM FTE_FREIGHT_COSTS_TEMP WFCTMP
, FTE_LANES FL
WHERE WFCTMP.LANE_ID = FL.LANE_ID
AND WFCTMP.LINE_TYPE_CODE IN ('PRICE'
, 'SUMMARY')
AND WFCTMP.CHARGE_SOURCE_CODE = 'PRICING_ENGINE'
AND WFCTMP.DELIVERY_DETAIL_ID IS NULL
AND WFCTMP.LANE_ID IS NOT NULL
AND WFCTMP.SCHEDULE_ID IS NULL UNION SELECT TO_CHAR(WFCTMP.FREIGHT_COST_ID) AS ENTITY_ID
, TO_CHAR(WFCTMP.DELIVERY_DETAIL_ID) AS PARENT_ENTITY_ID
, TO_CHAR(NVL(WFCTMP.LANE_ID
, -1*WFCTMP.SCHEDULE_ID))||' '||WFCTMP.SERVICE_TYPE_CODE AS LANE_SCHED_ID
, WFCTMP.COMPARISON_REQUEST_ID AS COMPARISON_REQUEST_ID
, WFCTMP.DELIVERY_ID AS DELIVERY_ID
, WFCTMP.LANE_ID AS LANE_ID
, WFCTMP.SCHEDULE_ID AS SCHEDULE_ID
, INITCAP(DECODE(WFCT.NAME
, 'PRICE'
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('FTE_PRC_UI_COST_TYPE'
, 'PRICE')
, WFCT.NAME) ) AS ENTITY_NAME
, NULL AS ITEM_NAME
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('WSH_SERVICE_LEVELS'
, WFCTMP.SERVICE_TYPE_CODE) AS SERVICE_TYPE
, NULL AS CONTAINER_TYPE
, NULL AS WEIGHT
, NULL AS VOLUME
, DECODE(LINE_TYPE_CODE
, 'PRICE'
, ROUND(WFCTMP.CHARGE_UNIT_VALUE*WFCTMP.QUANTITY
, 2)
, WFCTMP.TOTAL_AMOUNT)||' '||WFCTMP.CURRENCY_CODE AS PRICE
, WFCTMP.CREATION_DATE AS CREATION_DATE
, WFCTMP.CREATED_BY AS CREATED_BY
, WFCTMP.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, WFCTMP.LAST_UPDATED_BY AS LAST_UPDATED_BY
, WFCTMP.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, WFCTMP.PROGRAM_APPLICATION_ID AS PROGRAM_APPLICATION_ID
, WFCTMP.PROGRAM_ID AS PROGRAM_ID
, WFCTMP.PROGRAM_UPDATE_DATE AS PROGRAM_UPDATE_DATE
, WFCTMP.REQUEST_ID AS REQUEST_ID
FROM FTE_FREIGHT_COSTS_TEMP WFCTMP
, WSH_FREIGHT_COST_TYPES WFCT
WHERE WFCTMP.LINE_TYPE_CODE IN ('CHARGE'
, 'PRICE'
, 'DISCOUNT')
AND WFCTMP.CHARGE_SOURCE_CODE = 'PRICING_ENGINE'
AND WFCT.FREIGHT_COST_TYPE_ID = WFCTMP.FREIGHT_COST_TYPE_ID

Columns