VIEW: APPS.FTE_PRC_LANE_COMP_UI_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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