[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fte_schedules_s.nextval
INTO l_id
FROM dual;
SELECT fte_prc_parameters_s.nextval
INTO l_id
FROM dual;
SELECT schedules_id
INTO l_id
FROM fte_schedules
WHERE lane_id = p_lane_id
AND voyage_number = p_voyage
AND nvl(editable_flag,'Y') = 'Y';
SELECT lane_id
INTO l_lane_id
FROM fte_lanes
WHERE lane_number = p_lane_number
AND carrier_id = p_carrier_id
AND editable_flag <> 'D';
SELECT l.lane_number
FROM fte_lanes l, fte_lane_rate_charts c
WHERE c.lane_id = l.lane_id
AND c.list_header_id = p_pricelist_id
AND l.editable_flag <> 'D';
SELECT fte_lane_commodities_s.nextval
INTO l_id
FROM dual;
SELECT fte_lane_services_s.nextval
INTO l_id
FROM dual;
SELECT fte_lanes_s.nextval
INTO l_id
FROM dual;
SELECT lb.start_date_active, lb.end_date_active
INTO l_start_date_active, l_end_date_active
FROM qp_list_headers_tl ll, qp_list_headers_b lb
WHERE ll.list_header_id = lb.list_header_id
AND ll.name = p_name
AND ll.language = userenv('LANG');
SELECT count(list_header_id)
INTO l_number_of_chart
FROM fte_lane_rate_charts
WHERE lane_id = p_lane_id;
SELECT count(list_header_id)
INTO l_number_of_chart
FROM fte_lane_rate_charts
WHERE lane_id = p_lane_id
AND ((nvl(start_date_active, l_end_date_active) <= l_end_date_active));
SELECT count(list_header_id)
INTO l_number_of_chart
FROM fte_lane_rate_charts
WHERE lane_id = p_lane_id
AND ((nvl(end_date_active, l_start_date_active) >= l_start_date_active));
SELECT count(list_header_id)
INTO l_number_of_chart
FROM fte_lane_rate_charts
WHERE lane_id = p_lane_id
AND ((nvl(start_date_active, l_start_date_active) <= l_start_date_active AND nvl(end_date_active, l_end_date_active) >= l_start_date_active)
OR (nvl(start_date_active, l_start_date_active) <= l_end_date_active AND nvl(end_date_active, l_end_date_active) >= l_end_date_active)
OR (nvl(end_date_active, l_start_date_active) >= l_start_date_active AND nvl(start_date_active, l_end_date_active) <= l_end_date_active));
SELECT 'true'
FROM fte_lane_services
WHERE service_code = p_value
AND lane_id = p_lane_id;
SELECT 'true'
FROM fte_lane_commodities
WHERE commodity_catg_id = TO_NUMBER(p_value)
AND lane_id = p_lane_id;
SELECT count(lane_id)
INTO l_result
FROM fte_lane_rate_charts
WHERE lane_id = p_id
AND list_header_id = TO_NUMBER(p_code);
SELECT count(lane_id)
INTO l_result
FROM fte_lane_commodities
WHERE lane_id = p_id
AND commodity_catg_id = TO_NUMBER(p_code);
SELECT count(lane_id)
INTO l_result
FROM fte_lane_services
WHERE lane_id = p_id
AND service_code = p_code;
PROCEDURE DELETE_ROW(p_id IN NUMBER,
p_table IN VARCHAR2,
p_code IN VARCHAR2,
p_line_number IN NUMBER,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.DELETE_ROW';
DELETE from fte_lane_rate_charts
WHERE lane_id = p_id
AND list_header_id = TO_NUMBER(p_code);
DELETE from fte_lane_commodities
WHERE lane_id = p_id
AND commodity_catg_id = TO_NUMBER(p_code);
DELETE from fte_lane_services
WHERE lane_id = p_id
AND service_code = p_code;
UPDATE fte_lanes
SET editable_flag = 'D',
lane_number = p_id || '-DELETED by USER', -- might just leave the lane number as it
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE lane_id = p_id;
DELETE from fte_prc_parameters
WHERE parameter_instance_id = p_id;
DELETE from fte_prc_parameters
WHERE lane_id = p_id
AND parameter_id = TO_NUMBER(p_code);
UPDATE FTE_SCHEDULES
SET EDITABLE_FLAG = 'D',
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE SCHEDULES_ID = p_id
AND NVL(EDITABLE_FLAG,'Y') = 'Y';
END DELETE_ROW;
PROCEDURE UPDATE_LANE_FLAGS(p_type IN VARCHAR2,
p_lane_id IN NUMBER,
p_value IN VARCHAR2 DEFAULT 'N',
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_return STRINGARRAY := STRINGARRAY();
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_LANE_FLAGS';
SELECT TO_CHAR(commodity_catg_id)
FROM fte_lane_commodities
WHERE lane_id = p_lane_id;
SELECT service_code
FROM fte_lane_services
WHERE lane_id = p_lane_id;
UPDATE fte_lanes
SET service_type_code = l_code,
service_detail_flag = l_flag,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE lane_id = p_lane_id;
UPDATE fte_lanes
SET commodity_catg_id = TO_NUMBER(l_code),
commodity_detail_flag = l_flag,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE lane_id = p_lane_id;
UPDATE fte_lanes
SET schedules_flag = p_value,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE lane_id = p_lane_id;
END UPDATE_LANE_FLAGS;
PROCEDURE INSERT_SCHEDULES(p_schedule_tbl IN OUT NOCOPY schedule_tbl,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_vessel_name VESSEL_NAME_TBL;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.INSERT_SCHEDULES';
INSERT INTO FTE_SCHEDULES(LANE_ID,
SCHEDULES_ID,
VESSEL_NAME,
VESSEL_TYPE,
VOYAGE_NUMBER,
ARRIVAL_DATE_INDICATOR,
TRANSIT_TIME,
PORT_OF_LOADING,
PORT_OF_DISCHARGE,
FREQUENCY_TYPE,
FREQUENCY,
FREQUENCY_ARRIVAL,
DEPARTURE_TIME,
ARRIVAL_TIME,
DEPARTURE_DATE,
ARRIVAL_DATE,
EFFECTIVE_DATE,
EXPIRY_DATE,
TRANSIT_TIME_UOM,
LANE_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (l_lane_id(i),
l_schedules_id(i),
l_vessel_name(i),
l_vessel_type(i),
l_voyage_number(i),
l_arrival_date_ind(i),
l_transit_time(i),
l_port_of_loading(i),
l_port_of_discharge(i),
l_frequency_type(i),
l_frequency(i),
l_frequency_arrival(i),
l_departure_time(i),
l_arrival_time(i),
l_departure_date(i),
l_arrival_date(i),
l_effective_date(i),
l_expiry_date(i),
l_transit_time_uom(i),
l_lane_number(i),
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
END INSERT_SCHEDULES;
PROCEDURE INSERT_LANE_TABLES(p_lane_tbl IN OUT NOCOPY lane_tbl,
p_lane_rate_chart_tbl IN OUT NOCOPY lane_rate_chart_tbl,
p_lane_commodity_tbl IN OUT NOCOPY lane_commodity_tbl,
p_lane_service_tbl IN OUT NOCOPY lane_service_tbl,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_lane_id LANE_ID_TBL;
l_update BOOLEAN := FALSE;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.INSERT_LANE_TABLES';
IF (p_lane_tbl(i).action = 'UPDATE') THEN
l_update := TRUE;
UPDATE FTE_LANES
SET COMM_FC_CLASS_CODE = p_lane_tbl(i).comm_fc_class_code,
ORIGIN_ID = nvl(p_lane_tbl(i).origin_id, ORIGIN_ID),
DESTINATION_ID = nvl(p_lane_tbl(i).destination_id, DESTINATION_ID),
LANE_TYPE = nvl(p_lane_tbl(i).lane_type, lane_type),
PRICELIST_VIEW_FLAG = p_lane_tbl(i).pricelist_view_flag,
BASIS = p_lane_tbl(i).basis,
EFFECTIVE_DATE = p_lane_tbl(i).effective_date,
EXPIRY_DATE = p_lane_tbl(i).expiry_date,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE lane_id = p_lane_tbl(i).lane_id;
p_lane_tbl.DELETE(i);
ELSIF (p_lane_tbl(i).action = 'DELETE') THEN
p_lane_tbl.DELETE(i);
IF (NOT l_update) THEN
BEGIN
FORALL i in l_lane_id.FIRST..l_lane_id.LAST
INSERT INTO FTE_LANES (LANE_ID,
LANE_NUMBER,
OWNER_ID,
CARRIER_ID,
ORIGIN_ID,
DESTINATION_ID,
MODE_OF_TRANSPORTATION_CODE,
TRANSIT_TIME,
TRANSIT_TIME_UOM,
SPECIAL_HANDLING,
ADDITIONAL_INSTRUCTIONS,
COMMODITY_DETAIL_FLAG,
EQUIPMENT_DETAIL_FLAG,
SERVICE_DETAIL_FLAG,
COMM_FC_CLASS_CODE,
COMMODITY_CATG_ID,
EQUIPMENT_TYPE_CODE,
SERVICE_TYPE_CODE,
DISTANCE,
DISTANCE_UOM,
SCHEDULES_FLAG,
PRICELIST_VIEW_FLAG,
BASIS,
EFFECTIVE_DATE,
EXPIRY_DATE,
EDITABLE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANE_TYPE,
TARIFF_NAME)
VALUES (l_lane_id(i),
l_lane_number(i),
-1,
l_carrier_id(i),
l_origin_id(i),
l_dest_id(i),
l_mode_of_trans(i),
l_transit_time(i),
l_transit_time_uom(i),
l_special_handling(i),
l_additional_instructions(i),
'N',
'N',
'N',
l_comm_fc_class_code(i),
l_comm_catg_id(i),
l_equip_type_code(i),
l_service_type_code(i),
l_distance(i),
l_distance_uom(i),
'N',
l_pricelist_view_flag(i),
l_basis(i),
l_effective_date(i),
l_expiry_date(i),
l_editable_flag(i),
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_lane_type(i),
l_tariff_name(i));
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting lanes]');
p_lane_tbl.DELETE;
p_lane_rate_chart_tbl.DELETE;
p_lane_commodity_tbl.DELETE;
p_lane_service_tbl.DELETE;
END; -- FINISH INSERTING lanes
INSERT INTO FTE_LANE_RATE_CHARTS (LANE_ID,
LIST_HEADER_ID,
END_DATE_ACTIVE,
START_DATE_ACTIVE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (l_lrc_lane_id(i),
l_list_header_id(i),
l_end_date_active(i),
l_start_date_active(i),
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
UPDATE fte_lanes
SET lane_type = NULL,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE lane_id = l_lrc_lane_id(i);
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting lane rate charts]');
p_lane_tbl.DELETE;
p_lane_rate_chart_tbl.DELETE;
p_lane_commodity_tbl.DELETE;
p_lane_service_tbl.DELETE;
END; --FINISH INSERTING lane rate charts
INSERT INTO FTE_LANE_COMMODITIES (LANE_ID,
LANE_COMMODITY_ID,
BASIS,
COMMODITY_CATG_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (l_lc_lane_id(i),
l_lane_commodity_id(i),
l_lc_basis(i),
l_lc_comm_catg_id(i),
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting lane commodities]');
p_lane_tbl.DELETE;
p_lane_rate_chart_tbl.DELETE;
p_lane_commodity_tbl.DELETE;
p_lane_service_tbl.DELETE;
END; --FINISH INSERTING lane commodities
INSERT INTO FTE_LANE_SERVICES (LANE_ID,
LANE_SERVICE_ID,
SERVICE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (l_ls_lane_id(i),
l_lane_service_id(i),
l_service_code(i),
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, '[Inserting lane services]');
p_lane_tbl.DELETE;
p_lane_rate_chart_tbl.DELETE;
p_lane_commodity_tbl.DELETE;
p_lane_service_tbl.DELETE;
END; --FINISH INSERTING lane services
UPDATE_LANE_FLAGS(p_type => 'SERVICE_LEVEL',
p_lane_id => p_lane_tbl(i).lane_id,
x_status => x_status,
x_error_msg => x_error_msg);
UPDATE_LANE_FLAGS(p_type => 'COMMODITY_TYPE',
p_lane_id => p_lane_tbl(i).lane_id,
x_status => x_status,
x_error_msg => x_error_msg);
p_lane_tbl.DELETE;
p_lane_rate_chart_tbl.DELETE;
p_lane_commodity_tbl.DELETE;
p_lane_service_tbl.DELETE;
p_lane_tbl.DELETE;
p_lane_rate_chart_tbl.DELETE;
p_lane_commodity_tbl.DELETE;
p_lane_service_tbl.DELETE;
END INSERT_LANE_TABLES;
PROCEDURE INSERT_PRC_PARAMETERS(p_prc_parameter_tbl IN OUT NOCOPY prc_parameter_tbl,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_value_from VALUE_FROM_TBL;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.INSERT_PRC_PARAMETERS';
INSERT INTO FTE_PRC_PARAMETERS (LANE_ID,
PARAMETER_ID,
PARAMETER_INSTANCE_ID,
VALUE_FROM,
VALUE_TO,
UOM_CODE,
CURRENCY_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (l_lane_id(i),
l_parameter_id(i),
l_parameter_instance_id(i),
l_value_from(i),
l_value_to(i),
l_uom_code(i),
l_currency_code(i),
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
END INSERT_PRC_PARAMETERS;
PROCEDURE UPDATE_LANE_RATE_CHART (p_list_header_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2)IS
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_LANE_RATE_CHART';
UPDATE fte_lane_rate_charts
SET START_DATE_ACTIVE = p_start_date,
END_DATE_ACTIVE = p_end_date,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE LIST_HEADER_ID = p_list_header_id;
END UPDATE_LANE_RATE_CHART;
PROCEDURE UPDATE_PRC_PARAMETER( p_prc_parameter_tbl IN prc_parameter_tbl,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2)IS
l_value_from fte_prc_parameters.value_from%TYPE;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_PRC_PARAMETERS';
UPDATE fte_prc_parameters
SET value_from = l_value_from,
value_to = l_value_to,
uom_code = l_uom_code,
currency_code = l_currency_code,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE parameter_instance_id = l_parameter_instance_id;
END UPDATE_PRC_PARAMETER;