VIEW: APPS.FTE_PRC_VIEW_PRICE_UI_V

Source

View Text - Preformatted

SELECT wfc.delivery_detail_id as entity_id, nvl(wda.parent_delivery_detail_id,wfc.delivery_leg_id) as parent_entity_id, wfc.delivery_leg_id as delivery_leg_id, to_char(wfc.delivery_detail_id) as entity_name, msik.concatenated_segments as item_name, 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, wfc.total_amount||' '||wfc.currency_code as price, wfc.creation_date as creation_date, wfc.created_by as created_by, wfc.last_update_date as last_update_date, wfc.last_updated_by as last_updated_by, wfc.last_update_login as last_update_login, wfc.program_application_id as program_application_id, wfc.program_id as program_id, wfc.program_update_date as program_update_date, wfc.request_id as request_id from wsh_freight_costs wfc, wsh_delivery_details wdd, mtl_system_items_kfv msik, wsh_delivery_assignments wda WHERE wfc.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 wfc.line_type_code IN ('PRICE','SUMMARY') and wfc.charge_source_code = 'PRICING_ENGINE' union select wfc.delivery_leg_id as entity_id, -1 as parent_entity_id, wfc.delivery_leg_id as delivery_leg_id, fte_freight_pricing_util.get_lookup_meaning('FTE_PRC_UI_COST_TYPE','DELIVERY_LEG_TOTAL') as entity_name, null as item_name, null as container_type, null as weight, null as volume, wfc.total_amount||' '||wfc.currency_code as price, wfc.creation_date as creation_date, wfc.created_by as created_by, wfc.last_update_date as last_update_date, wfc.last_updated_by as last_updated_by, wfc.last_update_login as last_update_login, wfc.program_application_id as program_application_id, wfc.program_id as program_id, wfc.program_update_date as program_update_date, wfc.request_id as request_id from wsh_freight_costs wfc, wsh_new_deliveries wnd, wsh_delivery_legs wdl where wfc.delivery_leg_id = wdl.delivery_leg_id and wdl.delivery_id = wnd.delivery_id and wfc.delivery_detail_id is NULL and wfc.line_type_code = 'SUMMARY' and wfc.charge_source_code = 'PRICING_ENGINE' union select wfc.freight_cost_id as entity_id, wfc.delivery_detail_id as parent_entity_id, wfc.delivery_leg_id as delivery_leg_id, decode(wfct.name,'PRICE', fte_freight_pricing_util.get_lookup_meaning('FTE_PRC_UI_COST_TYPE','PRICE') ,initcap(wfct.name)) as entity_name, null as item_name, null as container_type, null as weight, null as volume, decode(line_type_code, 'PRICE',round(wfc.charge_unit_value*wfc.quantity,2), wfc.total_amount)||' '||wfc.currency_code as price, wfc.creation_date as creation_date, wfc.created_by as created_by, wfc.last_update_date as last_update_date, wfc.last_updated_by as last_updated_by, wfc.last_update_login as last_update_login, wfc.program_application_id as program_application_id, wfc.program_id as program_id, wfc.program_update_date as program_update_date, wfc.request_id as request_id from wsh_freight_costs wfc, wsh_freight_cost_types wfct where wfc.line_type_code IN ('CHARGE','PRICE','DISCOUNT') and wfc.charge_source_code = 'PRICING_ENGINE' and wfct.freight_cost_type_id = wfc.freight_cost_type_id

View Text - HTML Formatted

SELECT WFC.DELIVERY_DETAIL_ID AS ENTITY_ID
, NVL(WDA.PARENT_DELIVERY_DETAIL_ID
, WFC.DELIVERY_LEG_ID) AS PARENT_ENTITY_ID
, WFC.DELIVERY_LEG_ID AS DELIVERY_LEG_ID
, TO_CHAR(WFC.DELIVERY_DETAIL_ID) AS ENTITY_NAME
, MSIK.CONCATENATED_SEGMENTS AS ITEM_NAME
, 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
, WFC.TOTAL_AMOUNT||' '||WFC.CURRENCY_CODE AS PRICE
, WFC.CREATION_DATE AS CREATION_DATE
, WFC.CREATED_BY AS CREATED_BY
, WFC.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, WFC.LAST_UPDATED_BY AS LAST_UPDATED_BY
, WFC.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, WFC.PROGRAM_APPLICATION_ID AS PROGRAM_APPLICATION_ID
, WFC.PROGRAM_ID AS PROGRAM_ID
, WFC.PROGRAM_UPDATE_DATE AS PROGRAM_UPDATE_DATE
, WFC.REQUEST_ID AS REQUEST_ID
FROM WSH_FREIGHT_COSTS WFC
, WSH_DELIVERY_DETAILS WDD
, MTL_SYSTEM_ITEMS_KFV MSIK
, WSH_DELIVERY_ASSIGNMENTS WDA
WHERE WFC.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 WFC.LINE_TYPE_CODE IN ('PRICE'
, 'SUMMARY')
AND WFC.CHARGE_SOURCE_CODE = 'PRICING_ENGINE' UNION SELECT WFC.DELIVERY_LEG_ID AS ENTITY_ID
, -1 AS PARENT_ENTITY_ID
, WFC.DELIVERY_LEG_ID AS DELIVERY_LEG_ID
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('FTE_PRC_UI_COST_TYPE'
, 'DELIVERY_LEG_TOTAL') AS ENTITY_NAME
, NULL AS ITEM_NAME
, NULL AS CONTAINER_TYPE
, NULL AS WEIGHT
, NULL AS VOLUME
, WFC.TOTAL_AMOUNT||' '||WFC.CURRENCY_CODE AS PRICE
, WFC.CREATION_DATE AS CREATION_DATE
, WFC.CREATED_BY AS CREATED_BY
, WFC.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, WFC.LAST_UPDATED_BY AS LAST_UPDATED_BY
, WFC.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, WFC.PROGRAM_APPLICATION_ID AS PROGRAM_APPLICATION_ID
, WFC.PROGRAM_ID AS PROGRAM_ID
, WFC.PROGRAM_UPDATE_DATE AS PROGRAM_UPDATE_DATE
, WFC.REQUEST_ID AS REQUEST_ID
FROM WSH_FREIGHT_COSTS WFC
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_LEGS WDL
WHERE WFC.DELIVERY_LEG_ID = WDL.DELIVERY_LEG_ID
AND WDL.DELIVERY_ID = WND.DELIVERY_ID
AND WFC.DELIVERY_DETAIL_ID IS NULL
AND WFC.LINE_TYPE_CODE = 'SUMMARY'
AND WFC.CHARGE_SOURCE_CODE = 'PRICING_ENGINE' UNION SELECT WFC.FREIGHT_COST_ID AS ENTITY_ID
, WFC.DELIVERY_DETAIL_ID AS PARENT_ENTITY_ID
, WFC.DELIVERY_LEG_ID AS DELIVERY_LEG_ID
, DECODE(WFCT.NAME
, 'PRICE'
, FTE_FREIGHT_PRICING_UTIL.GET_LOOKUP_MEANING('FTE_PRC_UI_COST_TYPE'
, 'PRICE')
, INITCAP(WFCT.NAME)) AS ENTITY_NAME
, NULL AS ITEM_NAME
, NULL AS CONTAINER_TYPE
, NULL AS WEIGHT
, NULL AS VOLUME
, DECODE(LINE_TYPE_CODE
, 'PRICE'
, ROUND(WFC.CHARGE_UNIT_VALUE*WFC.QUANTITY
, 2)
, WFC.TOTAL_AMOUNT)||' '||WFC.CURRENCY_CODE AS PRICE
, WFC.CREATION_DATE AS CREATION_DATE
, WFC.CREATED_BY AS CREATED_BY
, WFC.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, WFC.LAST_UPDATED_BY AS LAST_UPDATED_BY
, WFC.LAST_UPDATE_LOGIN AS LAST_UPDATE_LOGIN
, WFC.PROGRAM_APPLICATION_ID AS PROGRAM_APPLICATION_ID
, WFC.PROGRAM_ID AS PROGRAM_ID
, WFC.PROGRAM_UPDATE_DATE AS PROGRAM_UPDATE_DATE
, WFC.REQUEST_ID AS REQUEST_ID
FROM WSH_FREIGHT_COSTS WFC
, WSH_FREIGHT_COST_TYPES WFCT
WHERE WFC.LINE_TYPE_CODE IN ('CHARGE'
, 'PRICE'
, 'DISCOUNT')
AND WFC.CHARGE_SOURCE_CODE = 'PRICING_ENGINE'
AND WFCT.FREIGHT_COST_TYPE_ID = WFC.FREIGHT_COST_TYPE_ID