[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_BULK_INSERT_LIMIT NUMBER := 250;
SELECT
SUBSTR(lane_type,INSTR(lane_type,'_', -1)+1)
FROM
fte_lanes
WHERE
tariff_name = p_tariff_name
ORDER BY creation_date DESC;
SELECT
carrier_id,
TO_CHAR(new_effective_date, G_DATE_FORMAT),
TO_CHAR(new_expiry_date,G_DATE_FORMAT)
FROM
fte_tariff_carriers
WHERE
tariff_name = p_tariff_name AND
action_code = p_action_code;
SELECT
SUBSTR(l.lane_type,INSTR(lane_type,'_', -1) + 1),
l.service_type_code,
owr.country_code,
dwr.country_code,
l.carrier_id
FROM
fte_lanes l,
fte_tariff_carriers tc,
wsh_zone_regions ozr,
wsh_zone_regions dzr,
wsh_regions owr,
wsh_regions dwr
WHERE
l.tariff_name = p_tariff_name AND
l.tariff_name = tc.tariff_name AND
tc.action_code IN ('M', 'D') AND
l.carrier_id = tc.carrier_id AND
ozr.parent_region_id = l.origin_id AND
dzr.parent_region_id = l.destination_id AND
ozr.region_id = owr.region_id AND
dzr.region_id = dwr.region_id
ORDER BY l.creation_date DESC;
SELECT
MAX(row_number) INTO l_max
FROM
fte_interface_zones
WHERE
zone_name = l_zone_name;
SELECT distinct
lrc.list_header_id
FROM
fte_lanes l,
fte_lane_rate_charts lrc
WHERE
l.lane_id = lrc.lane_id AND
l.tariff_name = p_tariff_name AND
l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
l.carrier_id = p_carrier_id;
PROCEDURE BULK_INSERT_LANES IS
l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BULK_INSERT_LANES';
INSERT INTO fte_lanes( lane_id,
lane_number,
owner_id,
carrier_id,
origin_id,
destination_id,
mode_of_transportation_code,
commodity_detail_flag,
service_detail_flag,
equipment_detail_flag,
commodity_catg_id,
service_type_code,
basis,
pricelist_view_flag,
effective_date,
expiry_date,
comm_fc_class_code,
schedules_flag,
editable_flag,
lane_type,
tariff_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES ( LN_LANE_ID(i),
LN_LANE_ID(i),
-1,
LN_CARRIER_ID(i),
LN_ORIGIN_ID(i),
LN_DEST_ID(i),
'LTL',
'Y',
'Y',
'N',
LN_COMMODITY_CATG_ID(i),
G_SERVICE_CODE,
'WEIGHT',
'Y',
TO_DATE(LN_START_DATE(i), G_DATE_FORMAT),
TO_DATE(LN_END_DATE(i), G_DATE_FORMAT),
LN_COMM_FC_CLASS_CODE(i),
'N',
'N',
LN_LANE_TYPE(i),
LN_TARIFF_NAME(i),
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID);
INSERT INTO fte_lane_services(lane_service_id,
lane_id,
service_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES(fte_lane_services_s.nextval,
LN_LANE_ID(i),
G_SERVICE_CODE,
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID);
LN_LANE_ID.DELETE;
LN_CARRIER_ID.DELETE;
LN_ORIGIN_ID.DELETE;
LN_DEST_ID.DELETE;
LN_COMMODITY_CATg_ID.DELETE;
LN_COMM_FC_CLASS_CODE.DELETE;
LN_LANE_TYPE.DELETE;
LN_TARIFF_NAME.DELETE;
LN_START_DATE.DELETE;
LN_END_DATE.DELETE;
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER.BULK_INSERT_LANES', sqlerrm);
END BULK_INSERT_LANES;
PROCEDURE BULK_INSERT_LANE_RATE_CHARTS IS
l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BULK_INSERT_LANE_RATE_CHARTS';
INSERT INTO fte_lane_rate_charts(lane_id,
list_header_id,
start_date_active,
end_date_active,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login )
VALUES(LRC_LANE_ID(i),
LRC_LIST_HEADER_ID(i),
LRC_START_DATE(i),
LRC_END_DATE(i),
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID);
LRC_LANE_ID.DELETE;
LRC_LIST_HEADER_ID.DELETE;
LRC_START_DATE.DELETE;
LRC_END_DATE.DELETE;
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER.BULK_INSERT_LANE_RATE_CHARTS', sqlerrm);
END BULK_INSERT_LANE_RATE_CHARTS;
PROCEDURE BULK_INSERT_LANE_PARAMETERS IS
l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BULK_INSERT_LANE_PARAMETERS';
INSERT INTO fte_prc_parameters(value_from,
uom_code,
currency_code,
parameter_instance_id,
lane_id,
parameter_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(PRC_VALUE_FROM(i),
G_LTL_UOM,
G_LTL_CURRENCY,
FTE_PRC_PARAMETERS_S.NEXTVAL,
PRC_LANE_ID(i),
PRC_PARAMETER_ID(i),
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID);
PRC_LANE_ID.DELETE;
PRC_VALUE_FROM.DELETE;
PRC_PARAMETER_ID.DELETE;
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER_PKG.BULK_INSERT_LANE_PARAMETERS', sqlerrm);
END BULK_INSERT_LANE_PARAMETERS;
PROCEDURE BULK_INSERT_LANE_COMMODITIES IS
l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.BULK_INSERT_LANE_COMMODITIES';
INSERT INTO FTE_LANE_COMMODITIES(lane_commodity_id,
lane_id,
commodity_catg_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(FTE_BULKLOAD_DATA_S.NEXTVAL,
CM_LANE_ID(i),
CM_CATg_ID(i),
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID);
CM_LANE_ID.DELETE;
CM_CATG_ID.DELETE;
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR IN FTE_LTL_LOADER.BULK_INSERT_LANE_PARAMETERS', sqlerrm);
END BULK_INSERT_LANE_COMMODITIES;
SELECT
zone_name
FROM
fte_interface_zones
WHERE
load_id = p_load_id AND
hash_value <> 0;
SELECT
l.lane_number,
oz.zone Origin,
dz.zone Destination,
hzp.party_name carrier_name,
qlht.name rate_chart_name,
prc.value_from minimum_charge
FROM
fte_lanes l,
hz_parties hzp,
qp_list_headers_tl qlht,
wsh_regions_tl oz,
wsh_regions_tl dz,
fte_lane_rate_charts flrc,
fte_prc_parameters prc
WHERE
l.tariff_name = p_tariff_name AND
l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
l.lane_id = prc.lane_id AND
prc.parameter_id = g_min_charge_id AND
oz.language = dz.language AND
oz.language = userenv('LANG') AND
l.origin_id = oz.region_id AND
l.destination_id = dz.region_id AND
l.lane_id = flrc.lane_id AND
flrc.list_header_id = qlht.list_header_id AND
qlht.language = userenv('LANG') AND
hzp.party_id = l.carrier_id;
SELECT DISTINCT
qlht.name rate_chart_name
FROM
fte_lanes l,
fte_lane_rate_charts flrc,
qp_list_headers_tl qlht
WHERE
l.tariff_name = p_tariff_name AND
l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
l.lane_id = flrc.lane_id AND
flrc.list_header_id = qlht.list_header_id AND
qlht.language = userenv('LANG');
p_delete_lanes IN BOOLEAN,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_lane_ids NUMBER_TAB;
IF (p_delete_lanes) THEN
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_delete_lanes', 'true');
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'p_delete_lanes', 'false');
IF p_delete_lanes THEN
UPDATE
FTE_LANES
SET
expiry_date = sysdate-1,
effective_date = sysdate,
editable_flag = 'D',
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
lane_type LIKE p_lane_type
RETURNING
lane_id, effective_date, expiry_date
BULK COLLECT INTO
l_lane_ids, l_effective_dates, l_expiry_dates;
UPDATE
FTE_LANES
SET
expiry_date = (G_VALID_DATE-Fnd_Number.Canonical_To_Number('0.0001')),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
lane_type LIKE p_lane_type AND
nvl(expiry_date, G_VALID_DATE) >= G_VALID_DATE
RETURNING
lane_id, effective_date, expiry_date
BULK COLLECT INTO
l_lane_ids, l_effective_dates, l_expiry_dates;
UPDATE
FTE_LANE_RATE_CHARTS
SET
START_DATE_ACTIVE = l_effective_dates(i),
END_DATE_ACTIVE = l_expiry_dates(i),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
list_header_id = (select list_header_id
from fte_lane_rate_charts
where lane_id = l_lane_ids(i));
UPDATE
QP_LIST_HEADERS_B
SET
START_DATE_ACTIVE = l_effective_dates(i),
END_DATE_ACTIVE = l_expiry_dates(i),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
LIST_HEADER_ID = (SELECT list_header_id
FROM fte_lane_rate_charts
WHERE lane_id = l_lane_ids(i));
SELECT
MAX(lh.list_header_id),
MAX(lh.name),
MAX(lh.description),
MAX(b.currency_code),
MAX(ql.qualifier_attr_value),
MAX(ql.qualifier_grouping_no),
MAX(b.start_date_active)
FROM
qp_list_headers_tl lh,
qp_list_headers_b b,
qp_qualifiers ql,
fte_lane_rate_charts lrc,
fte_lanes l
WHERE
l.tariff_name = p_tariff_name AND
l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
l.lane_id = lrc.lane_id AND
lh.list_header_id = lrc.list_header_id AND
lh.list_header_id = ql.list_header_id AND
lh.list_header_id = b.list_header_id AND
ql.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' AND
ql.qualifier_context = 'PARTY' AND
lh.language = USERENV('LANG')
GROUP BY
lh.list_header_id;
SELECT qp_process_id_s.NEXTVAL
INTO l_process_id
FROM DUAL;
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Calling QP for update ...');
SELECT
parameter_id
INTO
G_LANE_FUNCTION_ID
FROM
FTE_PRC_PARAMETER_DEFAULTS
WHERE
parameter_type = 'PARAMETER' AND
parameter_sub_type = 'LANE' AND
parameter_name = 'LANE_FUNCTION' AND
lane_function = 'NONE';
SELECT
parameter_id
INTO
G_MIN_CHARGE_ID
FROM
FTE_PRC_PARAMETER_DEFAULTS
WHERE
parameter_type = 'PARAMETER' AND
parameter_sub_type = 'MIN_CHARGE' AND
parameter_name = 'MIN_CHARGE_AMT' AND
lane_function = 'NONE';
SELECT
parameter_id
INTO
G_DEF_WT_ENABLED_ID
FROM
FTE_PRC_PARAMETER_DEFAULTS
WHERE
parameter_type = 'PARAMETER' AND
parameter_sub_type = 'DEFICIT_WT' AND
parameter_name = 'ENABLED' AND
lane_function = 'LTL';
SELECT
parameter_id
INTO
G_DEF_WT_BREAK_ID
FROM
FTE_PRC_PARAMETER_DEFAULTS
WHERE
parameter_type = 'PARAMETER' AND
parameter_sub_type = 'DEFICIT_WT' AND
parameter_name = 'WT_BREAK_POINT' AND
lane_function = 'LTL';
SELECT
process_id,
rate_chart_name
FROM
fte_interface_lanes
WHERE
load_id = p_load_id;
DELETE FROM
QP_LIST_HEADERS_B
WHERE
list_header_id IN (SELECT list_header_id
FROM qp_list_headers_tl
WHERE name = l_chart_name);
DELETE FROM
QP_QUALIFIERS
WHERE
list_header_id IN (SELECT list_header_id
FROM qp_list_headers_tl
WHERE name = l_chart_name);
DELETE FROM
QP_LIST_LINES
WHERE
list_header_id IN (SELECT list_header_id
FROM qp_list_headers_tl
WHERE name = l_chart_name);
DELETE FROM
QP_PRICING_ATTRIBUTES
WHERE list_header_id IN (SELECT list_header_id
FROM qp_list_headers_tl
WHERE name = l_chart_name);
DELETE FROM
FTE_LANE_RATE_CHARTS
WHERE
list_header_id IN (SELECT list_header_id
FROM qp_list_headers_tl
WHERE name = l_chart_name);
DELETE FROM
QP_LIST_HEADERS_TL
WHERE name = l_chart_name;
DELETE FROM
FTE_INTERFACE_ZONES
WHERE load_id = p_load_id;
DELETE FROM QP_INTERFACE_LIST_LINES WHERE process_id = l_process_id;
DELETE FROM QP_INTERFACE_QUALIFIERS WHERE process_id = l_process_id;
DELETE FROM QP_INTERFACE_PRICINg_ATTRIBS WHERE process_id = l_process_id;
DELETE FROM QP_INTERFACE_LIST_HEADERS WHERE process_id = l_process_id;
DELETE FROM fte_interface_lanes WHERE load_id = p_load_id;
DELETE FROM fte_interface_zones WHERE zone_id IS NULL;
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_lh || ' from QP_LIST_HEADERS');
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ll || ' from QP_LIST_LINES');
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_at || ' from QP_PRICINg_ATTRIBUTES');
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ql || ' from QP_QUALIFIERS');
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_ln || ' from FTE_INTERFACE_LANES');
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_zn || ' from FTE_INTERFACE_ZONES');
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || l_num_lane || ' from FTE_LANES');
DELETE FROM
fte_tariff_carriers
WHERE
tariff_name = p_tariff_name AND
action_code = 'N';
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleted ' || SQL%ROWCOUNT || ' rows from fte_tariff_carriers for tariff ' || p_tariff_name);
UPDATE
fte_tariff_carriers
SET
action_code = 'D',
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
tariff_name = p_tariff_name AND
action_code = 'M';
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Updated ' || sql%rowcount || ' rows in fte_tariff_carriers.');
SELECT QP_PROCESS_ID_S.NEXTVAL
INTO x_process_ID
FROM DUAL;
x_error_msg := 'Unexpected error while performing select qp_process_id_s.nextval ' || sqlerrm;
l_rate_hdr_data('ACTION') := 'DELETE';
IF (G_CHART_COUNT_TEMP = G_BULK_INSERT_LIMIT) THEN
FTE_RATE_CHART_LOADER.SUBMIT_QP_PROCESS(x_status => x_status,
x_error_msg => x_error_msg);
x_error_msg := 'ERROR INSERTING DATA into QP_INTERFACE TABLES: ' || x_error_msg;
SELECT fte_lanes_s.NEXTVAL INTO l_lane_id FROM DUAL;
IF (LN_LANE_ID.COUNT >= G_BULK_INSERT_LIMIT) THEN
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk inserting Lanes');
Bulk_Insert_Lanes;
Bulk_Insert_Lane_Rate_Charts;
IF (PRC_LANE_ID.COUNT >= G_BULK_INSERT_LIMIT) THEN
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk inserting Lane Parameters');
Bulk_Insert_Lane_Parameters;
IF (CM_LANE_ID.COUNT >= G_BULK_INSERT_LIMIT) THEN
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk inserting Lane Commodities');
Bulk_Insert_Lane_Commodities;
SELECT
origin_id,
dest_id,
rate_chart_name,
class_string,
min_charge1
FROM
fte_interface_lanes
WHERE
load_id = p_load_id;
SELECT
l.list_header_id
INTO
l_list_header_id
FROM
qp_list_headers_tl l,
qp_list_headers_b b
WHERE
l.list_header_id = b.list_header_id AND
l.name = l_lanes_temp.rate_chart_name(i) AND
l.language = userenv('LANG');
l_category_ids.DELETE;
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk Inserting Last Set of Data');
Bulk_Insert_Lanes;
Bulk_Insert_Lane_Rate_Charts;
Bulk_Insert_Lane_Parameters;
Bulk_Insert_Lane_Commodities;
SELECT
zone_name,
postal_code_string,
row_number,
zone_id
FROM
fte_interface_zones
WHERE
zone_name IN (p_zone1, p_zone2) AND
hash_value <> 0
ORDER BY
row_number;
SELECT
w2.country_code
FROM
wsh_zone_regions w,
fte_interface_zones f,
wsh_regions w2
WHERE
w.parent_region_id = f.zone_id AND
f.zone_name = p_zone_name AND
w.region_id = w2.region_id;
SELECT
substr(lane_type,instr(lane_type, '_', -1 ) + 1)
FROM
fte_lanes
WHERE
tariff_name = p_tariff_name
ORDER BY creation_date desc;
SELECT DISTINCT
origin_low,
origin_high
FROM
fte_bulkload_file
WHERE
load_id = p_load_id;
SELECT
lpad(nvl(l5c,0),6,'0') ||lpad(nvl(m5c,0),6,'0') ||
lpad(nvl(m1m,0),6,'0') ||lpad(nvl(m2m,0),6,'0') ||
lpad(nvl(m5m,0),6,'0') ||lpad(nvl(m10m,0),6,'0')||
lpad(nvl(m20m,0),6,'0') ||lpad(nvl(m30m,0),6,'0')||
lpad(nvl(m40m,0),6,'0'),
dest_low,
dest_high,
class,
lpad(min_charge1,6,'0')
FROM
fte_bulkload_file
WHERE
load_id = p_load_id AND
origin_low = p_origin_low
ORDER BY dest_low, dest_high, TO_NUMBER(class) desc;
INSERT INTO fte_interface_zones(ZONE_NAME,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
POSTAL_CODE_STRING,
LOAD_ID,
HASH_VALUE,
ZONE_ID,
ROW_NUMBER)
VALUES (l_origin_name,
l_origin,
l_origin_high,
'',
p_load_id,
0,
null,
1);
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXCEPTED ERROR while inserting into FTE_INTERFACE_ZONES',sqlerrm);
SELECT
dest_name,
min_charge1,
rate_chart_name
BULK COLLECT INTO
l_dest_names,
l_min_charges,
l_rate_names
FROM
fte_interface_lanes
WHERE
hash_value = l_hash_value AND
rate_chart_string = l_total_string AND
class_string = l_class_string AND
origin_name = l_origin_name AND
load_id = p_load_id;
INSERT INTO fte_interface_zones(ZONE_NAME,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
POSTAL_CODE_STRING,
LOAD_ID,
HASH_VALUE,
ZONE_ID,
ROW_NUMBER )
VALUES(l_dest_names(i),
l_dest_low,
l_dest_high,
'',
p_load_id,
0,
null,
l_row_number);
INSERT INTO fte_interface_lanes(ORIGIN_NAME,
DEST_NAME,
RATE_CHART_STRING,
RATE_CHART_NAME,
HASH_VALUE,
CLASS_STRING,
MIN_CHARGE1,
LOAD_ID,
ORIGIN_ID,
DEST_ID,
PROCESS_ID,
GROUP_PROCESS_ID)
VALUES(l_origin_name,
l_zone_name,
l_total_string,
l_rate_name,
l_hash_value,
l_class_string,
l_min_charge1,
p_load_id,
null,
null,
l_process_id,
decode(l_rate_chart_match, 'N', l_group_id, 'Y', NULL));
INSERT INTO fte_interface_zones(ZONE_NAME,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
POSTAL_CODE_STRING,
LOAD_ID,
HASH_VALUE,
ZONE_ID,
ROW_NUMBER )
VALUES( l_zone_name,
l_dest_low,
l_dest_high,
'',
p_load_id,
0,
null,
1);
SELECT
zone_name,
postal_code_from,
postal_code_to
FROM
fte_interface_zones
WHERE
load_id = p_load_id
ORDER BY
zone_name ASC, row_number ASC ;
SELECT
zone_name
FROM
fte_interface_zones
WHERE
load_id <> p_load_id
HAVING
SUM(hash_value) = p_sum_hash AND
SUM(row_number) = p_sum_rownum AND
MAX(row_number) = p_max_rownum
GROUP BY zone_name;
SELECT
zone_name,
SUM(row_number),
MAX(row_number),
SUM(hash_value)
FROM
fte_interface_zones
WHERE
load_id = p_load_id AND
hash_value <> 0
GROUP BY
zone_name;
UPDATE
FTE_INTERFACE_ZONES
SET
POSTAL_CODE_STRING = l_dest_string,
HASH_VALUE = l_hash_value
WHERE
ZONE_NAME = l_zone_name AND
LOAD_ID = p_load_id AND
ROW_NUMBER = l_row_number;
UPDATE
fte_interface_lanes
SET
origin_name = l_identical_zone,
origin_id = l_zone_id
WHERE
load_id = p_load_id AND
origin_name = l_zone_name;
UPDATE
fte_interface_lanes
SET
dest_name = l_identical_zone,
dest_id = l_zone_id
WHERE
load_id = p_load_id AND
dest_name = l_zone_name;
DELETE FROM
fte_interface_zones
WHERE
zone_name = l_zone_name AND
load_id = p_load_id;
SELECT
zone_name,
postal_code_from,
postal_code_to
FROM
fte_interface_zones
WHERE
load_id = p_load_id AND
zone_id is null
ORDER BY
zone_name,
row_number asc;
WSH_REGIONS_PKG.UPDATE_ZONE(p_insert_type => 'INSERT',
p_zone_id => '',
p_zone_name => l_zone_name,
p_zone_level => 11,
p_zone_type => 11,
p_lang_code => userenv('LANG'),
p_user_id => G_USER_ID,
x_zone_id => l_zone_id,
x_status => l_status,
x_error_msg => x_error_msg);
SELECT
region_id
INTO
l_zone_id
FROM
wsh_regions_tl
WHERE
zone = l_zone_name;
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Zone ID is NULL after WSH_REGIONS_PKG.Update_Zone ');
UPDATE
FTE_INTERFACE_ZONES
SET
ZONE_ID = l_zone_id
WHERE
zone_name = l_zone_name AND
HASH_VALUE <> 0 AND
LOAD_ID = p_load_id;
UPDATE
FTE_INTERFACE_LANES
SET
ORIGIN_ID = l_zone_id
WHERE
load_id = p_load_id AND
origin_name = l_zone_name;
UPDATE
FTE_INTERFACE_LANES
SET
DEST_ID = l_zone_id
WHERE
load_id = p_load_id AND
dest_name = l_zone_name;
WSH_REGIONS_PKG.UPDATE_ZONE_REGION(p_insert_type => 'INSERT',
p_zone_region_id => null,
p_zone_id => l_zone_id,
p_region_id => null,
p_country => '',
p_state => '',
p_city => '',
p_postal_code_from => l_zones_temp.dest_low(l_counter),
p_postal_code_to => l_zones_temp.dest_high(l_counter),
p_lang_code => userenv('LANG'),
p_country_code => l_country_code,
p_state_code => '',
p_city_code => '',
p_user_id => G_USER_ID,
p_zone_type => '11',
x_zone_region_id => l_region_id,
x_region_id => l_parent_region_id,
x_status => l_status,
x_error_msg => x_error_msg);
DELETE FROM FTE_INTERFACE_ZONES WHERE hash_value = 0;
SELECT
scac_code
INTO
l_scac
FROM
wsh_carriers
WHERE
carrier_id = p_carrier_ids(q);
p_delete_lanes => FALSE,
x_status => x_status,
x_error_msg => x_error_msg);
p_delete_lanes => TRUE,
x_status => x_status,
x_error_msg => x_error_msg);
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Update FTE_TARIFF_CARRIERS to complete process');
UPDATE
fte_tariff_carriers
SET
effective_date = to_date(p_effective_dates(n), G_DATE_FORMAT),
expiry_date = to_date(p_expiry_dates(n), G_DATE_FORMAT),
action_code = 'D',
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
tariff_name = p_tariff_name AND
carrier_id = p_carrier_ids(n);
PROCEDURE INSERT_LTL_DATA (p_load_id IN NUMBER,
x_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.INSERT_LTL_DATA';
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Inserting ' || FL_ORIGIN_LOW.COUNT || ' rows into fte_bulkload_file.');
INSERT INTO FTE_BULKLOAD_FILE ( LOAD_ID,
ORIGIN_LOW,
ORIGIN_HIGH,
DEST_LOW,
DEST_HIGH,
CLASS,
MIN_CHARGE1,
L5C,
M5C,
M1M,
M2M,
M5M,
M10M,
M20M,
M30M,
M40M,
EFFECTIVE_DATE,
OUTBOUND_FLAG,
MILEAGE)
VALUES (P_LOAD_ID,
FL_ORIGIN_LOW(i),
FL_ORIGIN_HIGH(i),
FL_DEST_LOW(i),
FL_DEST_HIGH(i),
FL_CLASS(i),
FL_MIN_CHARGE1(i),
FL_L5C(i),
FL_M5C(i),
FL_M1M(i),
FL_M2M(i),
FL_M5M(i),
FL_M10M(i),
FL_M20M(i),
FL_M30M(i),
FL_M40M(i),
G_VALID_DATE,
FL_OUTBOUND_FLAG(i),
FL_MILEAGE(i));
FL_ORIGIN_LOW.DELETE;
FL_ORIGIN_HIGH.DELETE;
FL_DEST_LOW.DELETE;
FL_DEST_HIGH.DELETE;
FL_CLASS.DELETE;
FL_MIN_CHARGE1.DELETE;
FL_L5C.DELETE;
FL_M5C.DELETE;
FL_M1M.DELETE;
FL_M2M.DELETE;
FL_M5M.DELETE;
FL_M10M.DELETE;
FL_M20M.DELETE;
FL_M30M.DELETE;
FL_M40M.DELETE;
FL_OUTBOUND_FLAG.DELETE;
FL_MILEAGE.DELETE;
END INSERT_LTL_DATA;
IF (FL_ORIGIN_LOW.COUNT = G_BULK_INSERT_LIMIT) THEN
INSERT_LTL_DATA(p_load_id => p_load_id,
x_status => x_status,
x_error_msg => x_error_msg);
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'INSERT_LTL_DATA returned with ERROR');
SELECT
content
INTO
l_content
FROM
fte_bulkload_data
WHERE
file_name = p_file_name and
load_id = p_load_id;
INSERT_LTL_DATA(p_load_id => p_load_id,
x_status => x_status,
x_error_msg => x_error_msg);
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'G_BULK_INSERT_LIMIT', G_BULK_INSERT_LIMIT);
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Inserting last set of lines :-)');
INSERT_LTL_DATA(p_load_id => p_load_id,
x_status => x_status,
x_error_msg => x_error_msg);
PROCEDURE UPDATE_TARIFF_LANES(p_tariff_name IN VARCHAR2,
p_load_id IN NUMBER,
x_abort OUT NOCOPY BOOLEAN,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR GET_TARIFF_LANES(p_load_number IN NUMBER, p_carrier_id IN NUMBER) IS
SELECT
l.lane_id,
l.origin_id,
l.destination_id,
lrc.list_header_id,
lrc.start_date_active,
prc.value_from
FROM
fte_lanes l,
fte_lane_rate_charts lrc,
fte_prc_parameters prc
WHERE
l.tariff_name = p_tariff_name AND
l.LANE_ID = lrc.LANE_ID AND
prc.LANE_ID = l.LANE_ID AND
prc.LANE_ID = lrc.LANE_ID AND
prc.PARAMETER_ID = g_min_charge_id AND
mode_of_transportation_code = 'LTL' AND
l.lane_type = 'LTL_' || p_tariff_name || '_' || p_load_number AND
l.carrier_id = p_carrier_id;
SELECT
commodity_catg_id
FROM
fte_lane_commodities
WHERE
lane_id = p_lane_id;
SELECT
DECODE (COUNT(DISTINCT origin_id), 1, 'O', 'I') direction,
MAX(ozr.postal_code_from) origin_low,
MAX(dzr.postal_code_from) destination_low
FROM
fte_lanes l,
wsh_zone_regions ozr,
wsh_zone_regions dzr
WHERE
l.lane_type = p_lane_type AND
l.tariff_name = p_tariff_name AND
ozr.parent_region_id = l.origin_id AND
dzr.parent_region_id = l.destination_id;
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_TARIFF_LANES';
UPDATE
fte_tariff_carriers
SET
effective_date = l_add_start_dates(p),
expiry_date = l_add_end_dates(p),
action_code = 'D',
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
tariff_name = p_tariff_name AND
action_code = 'N' AND
carrier_id = l_add_carriers(p);
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Bulk Insert Lane Data');
Bulk_Insert_Lanes;
Bulk_Insert_Lane_Rate_Charts;
Bulk_Insert_Lane_Parameters;
Bulk_Insert_Lane_Commodities;
SELECT scac_code INTO l_scac
FROM wsh_carriers
WHERE carrier_id = l_add_carriers(q);
p_delete_lanes => FALSE,
x_status => x_status,
x_error_msg => x_error_msg);
UPDATE
fte_lanes
SET
effective_date = l_mod_start_dates(n),
expiry_date = l_mod_end_dates(n),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
tariff_name = p_tariff_name AND
lane_type = 'LTL_' || p_tariff_name || '_' || l_load_number AND
carrier_id = l_modified_carriers(n);
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Update Tariff Carriers Table');
UPDATE
fte_tariff_carriers
SET
effective_date = l_mod_start_dates(n),
expiry_date = l_mod_end_dates(n),
action_code = 'D',
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
tariff_name = p_tariff_name AND
action_code = 'M' AND
carrier_id = l_modified_carriers(n);
END UPDATE_TARIFF_LANES;
ELSIF (p_action_code = 'UPDATE') THEN
OPEN GET_TARIFF_CARRIERS(p_tariff_name => p_tariff_name,
p_action_code => 'M');
p_action_code IN ('ADD', 'UPDATE')) THEN
x_status := 2;
IF (p_action_code = 'UPDATE') THEN
OPEN GET_PREVIOUS_LOAD_INFO (p_tariff_name => p_tariff_name);
UPDATE
fte_tariff_carriers
SET
new_expiry_date = to_date(to_char(to_date(to_char(new_expiry_date, G_DATE), G_DATE_FORMAT)+1-1/24/60/60, G_DATE_FORMAT), G_DATE_FORMAT),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_USER_ID
WHERE
tariff_name = p_tariff_name;
IF (p_action_code IN ('ADD', 'UPDATE')) THEN
UPLOAD_LTL_RATES(p_file_name => p_src_filename,
p_load_id => p_load_id,
p_tariff_name => p_tariff_name,
p_action_code => p_action_code,
x_phase => l_phase,
x_abort => l_abort,
x_status => x_status,
x_error_msg => errbuf);
ELSIF (p_action_code = 'UPDATE_ASSOC') THEN
UPDATE_TARIFF_LANES(p_tariff_name => p_tariff_name,
p_load_id => p_load_id,
x_abort => l_abort,
x_status => x_status,
x_error_msg => errbuf);
IF (p_action_code = 'UPDATE') THEN
l_load_number := l_load_number + 1;
SELECT
l.process_id, qh.name
FROM
fte_interface_lanes l, qp_interface_list_headers qh
WHERE
l.load_id = p_load_id AND
l.group_process_id = p_group_process_id AND
l.process_id = qh.process_id;