[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ca.carrier_id,
ca.currency_code,
nvl(cs.unit_rate_basis, ca.unit_rate_basis) unit_rate_basis,
ca.time_uom,
ca.distance_uom,
ca.weight_uom,
ca.volume_uom
INTO
g_carrier_id,
g_carrier_currency,
g_carrier_unit_basis,
g_carrier_time_uom,
g_carrier_distance_uom,
l_weight_uom,
l_volume_uom
FROM
WSH_CARRIER_SERVICES cs,
WSH_CARRIERS ca,
HZ_PARTIES hz
WHERE
cs.carrier_id(+) = ca.carrier_id AND
cs.service_level(+) = p_service_level AND
cs.mode_of_transport = l_mode_of_trans AND
hz.party_name = p_carrier_name AND
hz.party_id = ca.carrier_id;
SELECT
lh.name, b.attribute1
FROM
qp_list_headers_tl lh,
qp_list_headers_b b,
qp_qualifiers qc,
qp_qualifiers qs,
qp_qualifiers qm
WHERE
lh.list_header_id = b.list_header_id AND
qc.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' AND
qc.qualifier_context = 'PARTY' AND
qc.qualifier_attr_value = Fnd_Number.Number_To_Canonical(g_carrier_id) AND
qc.list_header_id = lh.list_header_id AND
qs.qualifier_attribute = 'QUALIFIER_ATTRIBUTE10' AND
qs.qualifier_context = 'LOGISTICS' AND
qs.qualifier_attr_value = p_service_level AND
qs.list_header_id = qc.list_header_id AND
qm.qualifier_attribute = 'QUALIFIER_ATTRIBUTE7' AND
qm.qualifier_context = 'LOGISTICS' AND
qm.qualifier_attr_value = 'TRUCK' AND
qm.list_header_id = qc.list_header_id AND
lh.language = userenv('LANG');
SELECT
lane_id,
carrier_id,
lane_number,
service_type_code
FROM
fte_lanes
WHERE
UPPER(lane_type) = UPPER('HOLD_'|| p_chart_name) AND
editable_flag <> 'D';
SELECT
l.list_header_id
INTO
l_rc_id
FROM
qp_list_headers_tl l,
qp_list_headers_b b
WHERE
l.list_header_id = b.list_header_id AND
l.name = p_chart_name AND
l.language = userenv('LANG');
l_qualifier_data.DELETE;
l_qualifier_data_tbl.DELETE;
l_attribute_data.DELETE;
l_attribute_block_tbl.DELETE;
Chart_Names.DELETE;
Chart_Carriers.DELETE;
Chart_Service_Levels.DELETE;
Chart_LineNums.DELETE;
Chart_Currencies.DELETE;
Chart_Process_Ids.DELETE;
Chart_Types.DELETE;
Chart_Min_Charges.DELETE;
Chart_Ids.DELETE;
Chart_Start_Dates.DELETE;
Chart_End_Dates.DELETE;
g_layovr_charges.DELETE;
g_layovr_breaks.DELETE;
Link_ChartNames.DELETE;
Link_Modifiernames.DELETE;
l_rate_break_data.DELETE;
l_rate_break_block_tbl.DELETE;
l_rate_hdr_data.DELETE;
l_rate_hdr_block_tbl.DELETE;
SELECT price_formula_id
INTO l_formula_id
FROM qp_price_formulas_b
WHERE price_formula_no = 'QP_MIN_CHARGE';
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
g_layovr_breaks.DELETE;
g_layovr_charges.DELETE;
IF (instr(g_chart_name, 'MOD_') > 0 AND g_action <> 'DELETE') THEN
Get_Chart_Data(p_chart_name => g_chart_name,
p_currency => l_currency,
p_chart_type => l_chart_type,
x_carrier_name => l_carrier_name_temp,
x_service_level => l_service_level_temp,
x_cur_line => l_linenum_temp,
x_job_id => l_process_id_temp,
p_line_number => null,
x_error_msg => x_error_msg,
x_status => x_status);
SELECT
lh.list_header_id,
b.start_date_active,
b.end_date_active
INTO
l_list_header_id,
l_start_date,
l_end_date
FROM
qp_list_headers_tl lh,
qp_list_headers_b b,
qp_qualifiers q
WHERE
lh.list_header_id = b.list_header_id AND
lh.list_header_id = q.list_header_id AND
q.qualifier_context = 'PARTY' AND
q.qualifier_attr_value = Fnd_Number.Number_To_Canonical(g_carrier_id) AND
lh.name = l_chart_name AND
lh.language = userenv('LANG');
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 (l_lane_ids(j),
l_list_header_id,
l_start_date,
l_end_date,
FND_GLOBAL.USER_ID,
G_CURDATE,
FND_GLOBAL.USER_ID,
G_CURDATE,
FND_GLOBAL.USER_ID);
UPDATE fte_lanes
SET service_detail_flag ='Y',
service_type_code = l_service_level,
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_lane_ids(j);
SELECT
modc.list_header_id,
Fnd_Number.Canonical_To_Number(qual.qualifier_attr_value)
INTO
l_modifier_id,
l_list_header_id
FROM
qp_list_headers_tl modc,
qp_list_headers_b b,
qp_qualifiers qual
WHERE
modc.list_header_id = b.list_header_id AND
qual.list_header_id = modc.list_header_id AND
qual.qualifier_context = 'MODLIST' AND
qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4' AND
modc.name = l_chart_name AND
modc.language = userenv('LANG');
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Inserting into FTE_PRC_PARAMETERS');
INSERT INTO FTE_PRC_PARAMETERS( PARAMETER_INSTANCE_ID,
PARAMETER_ID,
VALUE_FROM,
UOM_CODE,
CURRENCY_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LIST_HEADER_ID)
VALUES( fte_prc_parameters_s.NEXTVAL,
l_prc_param_ids(m),
l_prc_param_values(m),
l_rate_basis_uom,
l_currency,
sysdate,
FND_GLOBAL.User_Id,
sysdate,
FND_GLOBAL.User_Id,
l_modifier_id);
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXP. ERROR Inserting Prc_Parameters', sqlerrm);
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
l_break_charges.DELETE(i, l_break_charges.COUNT);
l_break_charges.DELETE(i+1, l_break_charges.COUNT);
l_break_charges.DELETE(1);
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
l_rate_break_data.DELETE;
l_rate_break_block_tbl.DELETE;
l_rate_break_data.DELETE;
l_rate_break_block_tbl.DELETE;
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
l_rate_break_data.DELETE;
l_rate_break_block_tbl.DELETE;
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
IF (G_ACTION IN('DELETE')) THEN
--+
-- For DELETE, we delete the dummy rate chart associated with the modifier.
--+
l_assoc_rc_ids := FTE_RATE_CHART_PKG.GET_ASSOC_PRICELISTS(NULL, l_name);
FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => l_assoc_rc_ids(i),
p_name => NULL,
p_action => 'DELETE',
p_line_number => p_line_number + i + 1,
x_status => x_status,
x_error_msg => x_error_msg);
FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => NULL,
p_name => l_name,
p_action => G_ACTION,
p_line_number => p_line_number + i + 1,
x_status => x_status,
x_error_msg => x_error_msg);
l_rate_hdr_data.DELETE;
l_rate_hdr_block_tbl.DELETE;
l_rate_hdr_data.DELETE;
l_rate_hdr_block_tbl.DELETE;
IF (NOT(g_action = 'DELETE' AND Upper(l_type) <> 'C')) THEN
IF (l_type = 'C') THEN
RESET_CHART_INFO;
IF (l_currency IS NULL OR LENGTH(l_currency) = 0 AND G_ACTION IN ('ADD', 'UPDATE')) THEN
x_status := 2;
SELECT
'MOD_' || hz.party_id || '_' || l_service_level INTO l_name
FROM
HZ_PARTIES hz,
WSH_CARRIERS ca
WHERE
hz.party_name = l_carrier_name AND
hz.party_id = ca.carrier_id;
IF (G_ACTION = 'DELETE') THEN
IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Deleting TL_MODIFIER ' || l_name);
FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => NULL,
p_name => l_name,
p_action => G_ACTION,
p_line_number => p_line_number + i + 1,
x_status => x_status,
x_error_msg => x_error_msg);
l_rate_hdr_data.DELETE;
l_rate_hdr_block_tbl.DELETE;
l_deleted_rate_charts STRINGARRAY;
IF (G_ACTION IN('UPDATE', 'DELETE')) THEN
-- For both UPDATE and DELETE, delete the associated Mincharge Modifier of the rate chart
l_assoc_modifier_ids := FTE_RATE_CHART_PKG.GET_ASSOC_MODIFIERS(NULL, l_rate_chart_name);
FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => l_assoc_modifier_ids(i),
p_name => NULL,
p_action => 'DELETE',
p_line_number => p_line_number + i + 1,
x_status => x_status,
x_error_msg => x_error_msg);
FTE_UTIL_PKG.Write_LogFile(l_module_name,'FTE_RATE_CHART_PKG.DELETE_FROM_QP returned with error' || x_error_msg);
IF (G_ACTION = 'DELETE') THEN
--+
-- For Delete, we delete and exit
--+
FOR i IN 1..l_deleted_rate_charts.COUNT
LOOP
IF (l_rate_chart_name = l_deleted_rate_charts(i)) THEN
GOTO next_record;
FTE_RATE_CHART_PKG.DELETE_FROM_QP(p_list_header_id => NULL,
p_name => l_rate_chart_name,
p_action => G_ACTION,
p_line_number => p_line_number + i + 1,
x_status => x_status,
x_error_msg => x_error_msg);
FTE_UTIL_PKG.Write_LogFile(l_module_name,'FTE_RATE_CHART_PKG.DELETE_FROM_QP returned with error' || x_error_msg);
l_deleted_rate_charts(l_del_count) := l_rate_chart_name;
l_rate_hdr_data.DELETE;
l_rate_hdr_block_tbl.DELETE;
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
l_rate_line_data.DELETE;
l_rate_line_block_tbl.DELETE;
FTE_LANE_PKG.INSERT_LANE_TABLES(p_lane_tbl => g_lane_tbl,
p_lane_rate_chart_tbl => g_lane_rate_chart_tbl,
p_lane_commodity_tbl => g_lane_commodity_tbl,
p_lane_service_tbl => g_lane_service_tbl,
x_status => x_status,
x_error_msg => x_error_msg);