[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT currency_code INTO l_carrier_currency
FROM wsh_carriers
WHERE carrier_id = p_carrier_id;
SELECT name INTO l_subtype
FROM wsh_freight_cost_types
WHERE freight_cost_type_code = 'FTECHARGE'
AND name = upper(p_subtype);
SELECT service_level INTO l_temp_char
FROM WSH_CARRIER_SERVICES
WHERE carrier_id = l_carrier_id
AND service_level = l_service_code
AND enabled_flag = 'Y'
AND rownum = 1;
SELECT PARAMETER_ID
FROM FTE_PRC_PARAMETER_DEFAULTS
WHERE parameter_type = p_type
AND parameter_sub_type = p_subtype
AND parameter_name = p_name
AND lane_function in ('NONE', p_lane_function);
SELECT PARAMETER_ID
FROM FTE_PRC_PARAMETER_DEFAULTS
WHERE parameter_type = p_type
AND parameter_sub_type = p_subtype
AND parameter_name = p_name
AND lane_function = p_lane_function;
SELECT basis
INTO l_basis
FROM fte_lanes
WHERE lane_id = p_lane_id;
SELECT 'true'
FROM wsh_carrier_services s
WHERE s.carrier_id = p_carrier_id
AND s.mode_of_transport = p_mode
AND s.service_level = p_service_code
AND nvl(s.enabled_flag,'N') = 'Y';
SELECT 'true'
FROM wsh_carrier_services s, fte_lanes l
WHERE s.carrier_id = l.carrier_id
AND s.mode_of_transport=l.mode_of_transportation_code
AND l.lane_id = p_lane_id
AND s.service_level = p_service_code
AND nvl(s.enabled_flag,'N') = 'Y';
AND p_action NOT IN ('SYNC', 'DELETE', 'ADD', 'UPDATE')) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_ACTION_INVALID');
IF (p_type = 'SCHEDULE' AND p_action NOT IN ('SYNC', 'DELETE', 'ADD')) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_ACTION_INVALID');
IF (p_type = 'LANE_COMMODITY_TYPE' AND p_action NOT IN ('ADD', 'DELETE')) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_COMM_TYPES_INV_ACT');
IF (p_type = 'LANE_SERVICE_LEVEL' AND p_action NOT IN ('ADD', 'DELETE')) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_SERV_TYPES_INV_ACT');
IF (p_type = 'LANE_RATE_CHART' AND p_action NOT IN ('ADD', 'DELETE')) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_FIELD_RATE_CHART_INV_ACT');
IF (p_type = 'RATE_CHART' AND p_action NOT IN ('ADD', 'UPDATE', 'DELETE', 'APPEND')) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_ACTION_INVALID');
IF (p_type IN ('RATE_LINE', 'TL_SURCHARGES') AND p_action NOT IN ('ADD', 'UPDATE', 'DELETE')) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_ACTION_INVALID');
IF (p_type IN ('RATING_ATTRIBUTE', 'RATE_BREAK') AND p_action NOT IN ('ADD', 'UPDATE')) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_ACTION_INVALID');
SELECT mode_of_transport
FROM wsh_carrier_services
WHERE carrier_id = p_carrier_id
AND mode_of_transport = p_mode
AND nvl(enabled_flag,'N') = 'Y';
IF (p_action <> 'DELETE') THEN
IF (FTE_LANE_PKG.CHECK_EXISTING_LOAD(p_id => p_lane_tbl(p_lane_tbl.COUNT).lane_id,
p_table => 'FTE_LANE_RATE_CHARTS',
p_code => TO_NUMBER(l_rate_chart_info(1)),
p_line_number => p_line_number,
x_status => x_status,
x_error_msg => x_error_msg)) THEN
IF (x_status <> -1) THEN
FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
IF (p_action = 'UPDATE') THEN
IF (l_lane_number IS NULL) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(P_Name => 'FTE_CAT_LANE_NUMBER_MISSING');
p_lane_tbl(l_count).action := 'UPDATE';
ELSIF (p_action = 'DELETE') THEN
l_old_lane_id := FTE_LANE_PKG.GET_LANE_ID(l_lane_number, l_carrier_id);
-- Inserting the region in wsh_zone_regions
--+
IF (FTE_REGION_ZONE_LOADER.INSERT_PARTY_REGION(p_region_id => l_region_id,
p_parent_region_id => l_region_id,
p_supplier_id => -1,
p_validate_flag => TRUE,
p_postal_code_from => l_region_info.postal_code_from,
p_postal_code_to => l_region_info.postal_code_to) = -1) THEN
FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
--inserting the region in wsh_zone_regions
IF (FTE_REGION_ZONE_LOADER.INSERT_PARTY_REGION(p_region_id => l_region_id,
p_parent_region_id => l_region_id,
p_supplier_id => -1,
p_validate_flag => TRUE,
p_postal_code_from => l_region_info.postal_code_from,
p_postal_code_to => l_region_info.postal_code_to) = -1) THEN
FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
ELSIF (l_old_lane_id <> -1) THEN --if old lane does have basis and this is a update of it, then error
x_error_msg := FTE_UTIL_PKG.GET_MSG(P_Name => 'FTE_BASIS_MISSING');
SELECT PARAMETER_INSTANCE_ID
FROM FTE_PRC_PARAMETERS
WHERE lane_id = p_lane_id
AND parameter_id = p_parameter_id;
SELECT parameter_datatype
FROM FTE_PRC_PARAMETER_DEFAULTS
WHERE parameter_id = p_parameter_id;
SELECT value_from
FROM FTE_PRC_PARAMETERS
WHERE lane_id = p_lane_id
AND parameter_id = p_parameter_id;
IF (p_action IN ('SYNC', 'ADD', 'UPDATE')) THEN
-- Added
IF (l_parameter_data_type IS NOT NULL AND l_parameter_data_type = 'NUMBER') THEN
p_prc_parameter_tbl(l_count).value_from := fnd_number.canonical_to_number(l_value_from);
p_action := 'UPDATE';
IF (p_action = 'UPDATE') THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_UPDATE_ERROR',
p_tokens => STRINGARRAY(''),
p_values => STRINGARRAY(p_lane_function||':'||l_type||','||l_subtype||','||l_name));
ELSIF (p_action = 'DELETE') THEN
IF (p_deficit_wt OR l_prc_parameter_id <> -1) THEN
p_prc_parameter_tbl(l_count).parameter_instance_id := l_prc_parameter_id;
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_UPDATE_ERROR',
p_tokens => STRINGARRAY(''),
p_values => STRINGARRAY(p_lane_function||':'||l_type||','||l_subtype||','||l_name));
ELSIF (p_action = 'UPDATE') THEN
p_prc_parameter_tbl(l_count).parameter_instance_id := l_prc_parameter_id;
ELSIF (l_old_schedule_id = -1 AND p_action = 'DELETE') THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_NO_SCHEDULE');
ELSIF (l_old_schedule_id <> -1 AND p_action = 'DELETE') THEN
p_schedule_tbl(l_count).schedules_id := l_old_schedule_id;
ELSIF (p_action IN ('DELETE', 'UPDATE') AND (l_temp <> l_chart_type OR l_temp IS NULL) AND l_list_header_id <> -1) THEN
-- need messages for these names
l_tokens.EXTEND;
x_error_msg := Fte_Util_PKG.Get_Msg(p_name => 'FTE_CAT_DELETE_TYPE_WRONG',
p_tokens => StringArray('NAME', 'TYPE', 'ACTUAL'),
p_values => l_tokens);
ELSIF (p_action IN ('DELETE', 'UPDATE', 'APPEND') AND l_list_header_id = -1) THEN
x_error_msg := Fte_Util_PKG.Get_Msg(p_name => 'FTE_PRICELIST_INVALID',
p_tokens => StringArray('NAME'),
p_values => StringArray(l_name));
IF (l_chart_type <> 'FAC_MODIFIER' AND p_action <> 'DELETE') THEN
-- not checking carrier currency anymore
VALIDATE_CURRENCY(p_currency => l_currency,
p_carrier_id => p_carrier_id,
p_line_number => p_line_number,
x_status => x_status,
x_error_msg => x_error_msg);
SELECT list_header_id
INTO l_old_rc_id
FROM qp_list_headers_tl
WHERE name = l_replaced_rc
AND language = userenv('LANG');
IF (p_action = 'DELETE') THEN
l_count := p_qp_list_header_tbl.COUNT+1;
ELSIF (p_action IN ('ADD', 'APPEND','UPDATE')) THEN
IF (p_action = 'ADD') THEN
l_intAction := 'C';
ELSE --'UPDATE'
l_intAction := 'U';
IF (l_action IN ('ADD', 'UPDATE')) THEN
-- Insert into QP_INTERFACE_LIST_LINES
l_count := p_qp_list_line_tbl.COUNT+1;
l_brk_hdr_updated BOOLEAN;
IF (l_action IN ('ADD', 'UPDATE')) THEN
--Update this break's header line with the code 'PBH'
l_count := p_qp_list_line_tbl.COUNT;
IF (p_action = 'UPDATE') THEN
IF (l_old_lane_id = -1) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_LANE_NUMBER_INVALID');
ELSIF (p_action = 'DELETE') THEN
IF (l_old_lane_id = -1) THEN
x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_CAT_LANE_NUMBER_INVALID');
--inserting the region in wsh_zone_regions
IF (FTE_REGION_ZONE_LOADER.INSERT_PARTY_REGION(p_region_id => l_region_id,
p_parent_region_id => l_region_id,
p_supplier_id => -1,
p_validate_flag => TRUE,
p_postal_code_from => l_region_info.postal_code_from,
p_postal_code_to => l_region_info.postal_code_to) = -1) THEN
FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
--inserting the region in wsh_zone_regions
IF (FTE_REGION_ZONE_LOADER.INSERT_PARTY_REGION(p_region_id => l_region_id,
p_parent_region_id => l_region_id,
p_supplier_id => -1,
p_validate_flag => TRUE,
p_postal_code_from => l_region_info.postal_code_from,
p_postal_code_to => l_region_info.postal_code_to) = -1) THEN
FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);