[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_select_all VARCHAR2(30);
SELECT QUERY_TYPE , PUBLIC_FLAG,
AND_OR_FLAG , APPLIES_TO,
EXECUTE_FLAG
FROM MST_PERSONAL_QUERIES
WHERE QUERY_ID = P_QUERY_ID;
SELECT msc.QUERY_ID , msc.FIELD_NAME ,
ms.FIELD_TYPE , msc.SEQUENCE ,
msc.FILTER_TYPE , msc.FIELD_VALUE_FROM,
msc.FIELD_VALUE_TO, msc.MULTI_SELECT ,
msc.ACTIVE_FLAG , msc.CREATED_BY
FROM mst_selection_criteria msc,
mst_selection ms
WHERE msc.query_id = p_query_id
AND ms.field_name = msc.field_name
AND msc.active_flag = 1
ORDER BY msc.SEQUENCE;
l_multi_select_temp NUMBER;
l_insert_str VARCHAR2(200) ; --:= 'INSERT INTO MST_PERSONAL_QUERY_RESULTS ';
l_insert_Col VARCHAR2(200) ;
l_select_str VARCHAR2(2000);
l_update_str VARCHAR2(500);
l_delete_str VARCHAR2(500);
l_insert_str := 'INSERT INTO MST_PERSONAL_QUERY_RESULTS ';
l_delete_str := 'DELETE MST_PERSONAL_QUERY_RESULTS WHERE QUERY_ID = :P_QUERY_ID AND PLAN_ID=:P_PLAN_ID';
EXECUTE IMMEDIATE(l_delete_str) USING p_query_id,p_plan_id;
l_update_str := 'UPDATE MST_PERSONAL_QUERIES SET EXECUTE_FLAG = 2 WHERE QUERY_ID = :P_QUERY_ID';
l_executed:= execute_dyn_sql(l_update_str, p_query_id);
l_insert_Col := '(QUERY_ID, PLAN_ID, TRIP_ID,CREATED_BY, CREATION_DATE) ';
l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,TRIP_ID,'|| l_user||', SYSDATE FROM MST_PQ_LOAD_DETAILS_V ';
l_insert_Col := '(QUERY_ID, PLAN_ID, CONTINUOUS_MOVE_ID,CREATED_BY, CREATION_DATE) ';
l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,CONTINUOUS_MOVE_ID,'|| l_user||', SYSDATE FROM MST_PQ_CM_DETAILS_V ';
l_insert_Col := '(QUERY_ID, PLAN_ID, SOURCE_CODE,SOURCE_HEADER_NUMBER,CREATED_BY, CREATION_DATE) ';
l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,SOURCE_CODE,SOURCE_HEADER_NUMBER,'|| l_user||', SYSDATE FROM MST_PQ_ORDER_DETAILS_V ';
l_insert_Col := '(QUERY_ID, PLAN_ID, EXCEPTION_ID,CREATED_BY, CREATION_DATE) ';
l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,EXCEPTION_ID,'|| l_user||', SYSDATE FROM MST_PQ_EXCEP_DETAILS_V ';
l_insert_Col := '(QUERY_ID, PLAN_ID, DELIVERY_ID, CREATED_BY, CREATION_DATE) ';
l_select_str := 'SELECT DISTINCT '||p_query_id||',PLAN_ID,DELIVERY_ID,'|| l_user||', SYSDATE FROM MST_PQ_UA_DEL_DETAILS_V ';
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
l_where_str3 := '('''||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,''''||l_delim||'''')||''')';
ELSIF l_rec_definition.MULTI_SELECT = 2
AND l_rec_definition.filter_type = 1 THEN -- NO
l_where_str3 := ''''||l_rec_definition.FIELD_VALUE_FROM||'''';
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT = 2
AND l_rec_definition.filter_type = 1 THEN -- NO
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
IF l_multi_select_temp = 1 THEN
l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''TRUCK'')'||
' OR MODE_OF_TRANSPORT IN '||l_mode_of_transport_temp||')';
ELSIF l_multi_select_temp = 2 THEN
l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''TRUCK'')'||
' OR MODE_OF_TRANSPORT = '||l_mode_of_transport_temp||')';
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT = 2 THEN
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT = 2 THEN
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT = 2 THEN
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT = 2
AND l_rec_definition.filter_type = 1 THEN -- NO
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
IF l_multi_select_temp = 1 THEN
l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''LTL'')'||
' OR MODE_OF_TRANSPORT IN '||l_mode_of_transport_temp||')';
ELSIF l_multi_select_temp = 2 THEN
l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''LTL'')'||
' OR MODE_OF_TRANSPORT = '||l_mode_of_transport_temp||')';
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT = 2 THEN
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT = 2
AND l_rec_definition.filter_type = 1 THEN -- NO
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
IF l_multi_select_temp = 1 THEN
l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''PARCEL'')'||
' OR MODE_OF_TRANSPORT IN '||l_mode_of_transport_temp||')';
ELSIF l_multi_select_temp = 2 THEN
l_where_str2 := '((' ||l_where_str2 || ' AND MODE_OF_TRANSPORT = ''PARCEL'')'||
' OR MODE_OF_TRANSPORT = '||l_mode_of_transport_temp||')';
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
IF l_rec_definition.FIELD_TYPE = 1 OR l_field_type_temp = 1 THEN -- NUMBER
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
l_multi_select_temp := 1;
ELSIF l_rec_definition.MULTI_SELECT =2 THEN -- no
IF l_rec_definition.FIELD_TYPE = 1 OR l_field_type_temp = 1 THEN -- NUMBER
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
l_multi_select_temp := 2;
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT =2 THEN -- no
IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
IF l_rec_definition.MULTI_SELECT =1 THEN -- YES
IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
l_where_str3 := '('||REPLACE(rtrim(l_rec_definition.FIELD_VALUE_FROM,l_separator),l_separator,l_delim)||')';
ELSIF l_rec_definition.MULTI_SELECT =2 THEN -- no
IF l_rec_definition.FIELD_TYPE = 1 THEN -- NUMBER
l_where_str3 := l_rec_definition.FIELD_VALUE_FROM;
l_executed:= execute_dyn_sql(l_insert_str||l_insert_Col||l_select_str||l_where_str1,NULL);
DELETE mst_personal_query_results
WHERE query_id = p_query_id;
DELETE mst_selection_criteria
WHERE query_id = p_query_id;
DELETE mst_personal_queries
WHERE query_id = p_query_id;
DELETE MST_LOAD_SELECTION_CRITERIA
WHERE query_id = p_query_id;
DELETE MST_CM_SELECTION_CRITERIA
WHERE query_id = p_query_id;
DELETE MST_ORDER_SELECTION_CRITERIA
WHERE query_id = p_query_id;
DELETE MST_EXCEP_SELECTION_CRITERIA
WHERE query_id = p_query_id;
DELETE mst_personal_query_results
WHERE query_id = p_query_id;
DELETE mst_selection_criteria
WHERE query_id = p_query_id;
UPDATE mst_personal_queries
SET query_name = p_query_name,
description = p_description,
public_flag = p_public_flag
WHERE query_id = p_query_id;
PROCEDURE insert_load_selection(p_query_id IN NUMBER) IS
cursor cur_loads is
select ALL_TL, ALL_LTL, ALL_PARCEL,
TLS_IN_CM,
TL_NUMBERS,LTL_NUMBERS,PARCEL_NUMBERS,
RANGE_TYPE, USER_EDITS_ONLY,
ORIGIN_FACILITIES, DESTINATION_FACILITIES,
ORIGIN_FACILITY_IDS, DESTINATION_FACILITY_IDS,
ORIGIN_ZIP , DESTINATION_ZIP,
ORIGIN_CITY , DESTINATION_CITY,
ORIGIN_STATE , DESTINATION_STATE,
ORIGIN_COUNTRY , DESTINATION_COUNTRY,
CARRIERS , CUSTOMERS , SUPPLIERS ,
CARRIER_IDS, CUSTOMER_IDS, SUPPLIER_IDS,
COST_TYPE , COST_FROM , COST_TO ,
WEIGHT_TYPE, WEIGHT_FROM , WEIGHT_TO ,
CUBE_TYPE , CUBE_FROM , CUBE_TO ,
UTILIZATION_TYPE , UTILIZATION_FROM, UTILIZATION_TO,
DEPARTURE_TIME_TYPE, DEPARTURE_TIME_FROM,
DEPARTURE_TIME_TO , DEPARTURE_TIME_UNIT
from mst_load_selection_criteria
where query_id = p_query_id;
l_insert_begin VARCHAR2(500);
l_insert_what VARCHAR2(3000);
l_insert_who VARCHAR2(500);
l_delete_str VARCHAR2(500);
l_multi_select NUMBER ; --:= 2; -- 1 true, 2 false
l_multi_select := 2; -- 1 true, 2 false
l_delete_str := 'DELETE MST_SELECTION_CRITERIA WHERE QUERY_ID = :P_QUERYID';
l_executed := execute_dyn_sql(l_delete_str,p_query_id);
IF g_select_all IS NULL THEN
FND_MESSAGE.set_name('MST','MST_PQ_ALL');
g_select_all:= FND_MESSAGE.GET;
l_insert_begin := 'INSERT INTO MST_SELECTION_CRITERIA '||
'(QUERY_ID , FIELD_NAME , SEQUENCE ,'||
'FILTER_TYPE , FIELD_VALUE_FROM, DISPLAY_VALUE,'||
'FIELD_VALUE_TO, MULTI_SELECT , ACTIVE_FLAG ,'||
'CREATED_BY , CREATION_DATE ) VALUES ';
l_multi_select := 2;
l_insert_what := '(';
l_insert_what := l_insert_what ||p_query_id||l_delim||'''CONTINUOUS_MOVE_ID'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cm||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''MODE_OF_TRANSPORT'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_modes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''TL_TRIP_NUMBER'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_trip_numbers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''LTL_TRIP_NUMBER'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_trip_numbers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''PARCEL_TRIP_NUMBER'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_trip_numbers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''TRIP_NUMBER'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_trip_numbers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_user_edits||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_FACILITIES <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_ZIP <> g_select_all THEN
DECLARE
l_postalcodes VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_ZIP <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_CITY <> g_select_all THEN
DECLARE
l_cities VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_CITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_STATE <> g_select_all THEN
DECLARE
l_states VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_STATE <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_COUNTRY <> g_select_all THEN
DECLARE
l_countries VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_COUNTRY <> g_select_all
IF l_rec_loads.ORIGIN_FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_FACILITIES <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_ZIP <> g_select_all THEN
DECLARE
l_postalcodes VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_ZIP <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_CITY <> g_select_all THEN
DECLARE
l_cities VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_CITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_STATE <> g_select_all THEN
DECLARE
l_states VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_STATE <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_COUNTRY <> g_select_all THEN
DECLARE
l_countries VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_COUNTRY <> g_select_all
IF l_rec_loads.ORIGIN_FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
IF l_rec_loads.DESTINATION_FACILITIES = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.DESTINATION_FACILITIES = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_FACILITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.DESTINATION_FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
IF l_rec_loads.ORIGIN_FACILITIES = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.ORIGIN_FACILITIES = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_FACILITIES <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_ZIP <> g_select_all THEN
DECLARE
l_postalcodes VARCHAR2(2000);
IF l_rec_loads.DESTINATION_ZIP = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.DESTINATION_ZIP = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_ZIP <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.DESTINATION_ZIP <> g_select_all THEN
DECLARE
l_postalcodes VARCHAR2(2000);
IF l_rec_loads.ORIGIN_ZIP = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.ORIGIN_ZIP = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_ZIP <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_CITY <> g_select_all THEN
DECLARE
l_cities VARCHAR2(2000);
IF l_rec_loads.DESTINATION_CITY = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.DESTINATION_CITY = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_CITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.DESTINATION_CITY <> g_select_all THEN
DECLARE
l_cities VARCHAR2(2000);
IF l_rec_loads.ORIGIN_CITY = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.ORIGIN_CITY = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_CITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_STATE <> g_select_all THEN
DECLARE
l_states VARCHAR2(2000);
IF l_rec_loads.DESTINATION_STATE = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.DESTINATION_STATE = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_STATE <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.DESTINATION_STATE <> g_select_all THEN
DECLARE
l_states VARCHAR2(2000);
IF l_rec_loads.ORIGIN_STATE = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.ORIGIN_STATE = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_STATE <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.ORIGIN_COUNTRY <> g_select_all THEN
DECLARE
l_countries VARCHAR2(2000);
IF l_rec_loads.DESTINATION_COUNTRY = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.DESTINATION_COUNTRY = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_COUNTRY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.DESTINATION_COUNTRY <> g_select_all THEN
DECLARE
l_countries VARCHAR2(2000);
IF l_rec_loads.ORIGIN_COUNTRY = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_loads.ORIGIN_COUNTRY = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_COUNTRY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.Carriers <> g_select_all THEN
DECLARE
l_Carriers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Carriers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Carriers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.Customers <> g_select_all THEN
DECLARE
l_Customers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Customers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Customers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.Suppliers <> g_select_all THEN
DECLARE
l_Suppliers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Suppliers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Suppliers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cost_from||''''||l_delim||
l_null_str||l_delim||''''||l_cost_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_WEIGHT_from||''''||l_delim||
l_null_str||l_delim||''''||l_WEIGHT_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_CUBE_from||''''||l_delim||
l_null_str||l_delim||''''||l_CUBE_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_UTILIZATION_from||''''||l_delim||
l_null_str||l_delim||''''||l_UTILIZATION_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_DEPARTURE_TIME_from||''''||l_delim||
l_null_str||l_delim||''''||l_DEPARTURE_TIME_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END insert_load_selection;
PROCEDURE insert_cm_selection(p_query_id IN NUMBER) IS
cursor cur_cms is
select ALL_CM, CM_TRIP_NUMBERS , USER_EDITS_ONLY ,
CARRIER_IDS ,
CARRIERS , COST_TYPE ,
COST_FROM , COST_TO ,
DEPARTURE_TIME_TYPE, DEPARTURE_TIME_FROM,
DEPARTURE_TIME_TO , DEPARTURE_TIME_UNIT
from mst_cm_selection_criteria
where query_id = p_query_id;
l_insert_begin VARCHAR2(500);
l_insert_what VARCHAR2(3000);
l_insert_who VARCHAR2(500);
l_delete_str VARCHAR2(500);
l_multi_select NUMBER ; --:= 2; -- 1 true, 2 false
l_multi_select := 2; -- 1 true, 2 false
l_delete_str := 'DELETE MST_SELECTION_CRITERIA WHERE QUERY_ID = :P_QUERYID';
l_executed := execute_dyn_sql(l_delete_str,p_query_id);
IF g_select_all IS NULL THEN
FND_MESSAGE.set_name('MST','MST_PQ_ALL');
g_select_all:= FND_MESSAGE.GET;
l_insert_begin := 'INSERT INTO MST_SELECTION_CRITERIA '||
'(QUERY_ID , FIELD_NAME , SEQUENCE ,'||
'FILTER_TYPE , FIELD_VALUE_FROM, DISPLAY_VALUE,'||
'FIELD_VALUE_TO, MULTI_SELECT , ACTIVE_FLAG ,'||
'CREATED_BY , CREATION_DATE ) VALUES ';
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''CM_TRIP_NUMBER'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cm_trip_numbers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''CM_TRIP_NUMBER'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cm_trip_numbers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_user_edits||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_loads.Carriers <> g_select_all THEN
DECLARE
l_Carriers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Carriers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Carriers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cost_from||''''||l_delim||
l_null_str||l_delim||''''||l_cost_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_DEPARTURE_TIME_from||''''||l_delim||
l_null_str||l_delim||''''||l_DEPARTURE_TIME_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END insert_cm_selection;
PROCEDURE insert_order_selection(p_query_id IN NUMBER) IS
CURSOR cur_orders IS
SELECT ALL_SALES_ORDERS , SALES_ORDER_NUMBERS,
ALL_PURCHASE_ORDERS, PURCHASE_ORDER_NUMBERS,
ALL_OTHER_ORDERS , OTHER_ORDER_NUMBERS,
RANGE_TYPE ,
ORIGIN_FACILITIES , DESTINATION_FACILITIES,
ORIGIN_FACILITY_IDS, DESTINATION_FACILITY_IDS,
ORIGIN_ZIP , DESTINATION_ZIP ,
ORIGIN_CITY , DESTINATION_CITY ,
ORIGIN_STATE , DESTINATION_STATE ,
ORIGIN_COUNTRY , DESTINATION_COUNTRY ,
ITEMS , INVENTORY_ITEM_IDS ,
CUSTOMERS , CUSTOMER_IDS ,
SUPPLIERS , SUPPLIER_IDS ,
WEIGHT_TYPE , WEIGHT_FROM , WEIGHT_TO ,
CUBE_TYPE , CUBE_FROM , CUBE_TO
FROM mst_order_selection_criteria
WHERE query_id = p_query_id;
l_insert_begin VARCHAR2(500);
l_insert_what VARCHAR2(3000);
l_insert_who VARCHAR2(500);
l_delete_str VARCHAR2(500);
l_multi_select NUMBER ; --:= 2; -- 1 true, 2 false
l_multi_select := 2; -- 1 true, 2 false
l_delete_str := 'DELETE MST_SELECTION_CRITERIA WHERE QUERY_ID = :P_QUERYID';
l_executed := execute_dyn_sql(l_delete_str,p_query_id);
IF g_select_all IS NULL THEN
FND_MESSAGE.set_name('MST','MST_PQ_ALL');
g_select_all:= FND_MESSAGE.GET;
l_insert_begin := 'INSERT INTO MST_SELECTION_CRITERIA '||
'(QUERY_ID , FIELD_NAME , SEQUENCE ,'||
'FILTER_TYPE , FIELD_VALUE_FROM, DISPLAY_VALUE,'||
'FIELD_VALUE_TO, MULTI_SELECT , ACTIVE_FLAG ,'||
'CREATED_BY , CREATION_DATE ) VALUES ';
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''SOURCE_CODE'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_orders||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''ORDER_NUMBER'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_order_numbers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_FACILITIES <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_ZIP <> g_select_all THEN
DECLARE
l_postalcodes VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_ZIP <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_CITY <> g_select_all THEN
DECLARE
l_cities VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_CITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_STATE <> g_select_all THEN
DECLARE
l_states VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_STATE <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_COUNTRY <> g_select_all THEN
DECLARE
l_countries VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_COUNTRY <> g_select_all
IF l_rec_orders.ORIGIN_FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_FACILITIES <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_ZIP <> g_select_all THEN
DECLARE
l_postalcodes VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_ZIP <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_CITY <> g_select_all THEN
DECLARE
l_cities VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_CITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_STATE <> g_select_all THEN
DECLARE
l_states VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_STATE <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_COUNTRY <> g_select_all THEN
DECLARE
l_countries VARCHAR2(2000);
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_COUNTRY <> g_select_all
IF l_rec_orders.ORIGIN_FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
IF l_rec_orders.DESTINATION_FACILITIES = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.DESTINATION_FACILITIES = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_FACILITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.DESTINATION_FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
IF l_rec_orders.ORIGIN_FACILITIES = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.ORIGIN_FACILITIES = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_FACILITIES <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_ZIP <> g_select_all THEN
DECLARE
l_postalcodes VARCHAR2(2000);
IF l_rec_orders.DESTINATION_ZIP = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.DESTINATION_ZIP = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_ZIP <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.DESTINATION_ZIP <> g_select_all THEN
DECLARE
l_postalcodes VARCHAR2(2000);
IF l_rec_orders.ORIGIN_ZIP = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.ORIGIN_ZIP = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_postalcodes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_ZIP <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_CITY <> g_select_all THEN
DECLARE
l_cities VARCHAR2(2000);
IF l_rec_orders.DESTINATION_CITY = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.DESTINATION_CITY = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_CITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.DESTINATION_CITY <> g_select_all THEN
DECLARE
l_cities VARCHAR2(2000);
IF l_rec_orders.ORIGIN_CITY = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.ORIGIN_CITY = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_CITY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_STATE <> g_select_all THEN
DECLARE
l_states VARCHAR2(2000);
IF l_rec_orders.DESTINATION_STATE = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.DESTINATION_STATE = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_STATE <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.DESTINATION_STATE <> g_select_all THEN
DECLARE
l_states VARCHAR2(2000);
IF l_rec_orders.ORIGIN_STATE = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.ORIGIN_STATE = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_states||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_STATE <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.ORIGIN_COUNTRY <> g_select_all THEN
DECLARE
l_countries VARCHAR2(2000);
IF l_rec_orders.DESTINATION_COUNTRY = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.DESTINATION_COUNTRY = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end ORIGIN_COUNTRY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.DESTINATION_COUNTRY <> g_select_all THEN
DECLARE
l_countries VARCHAR2(2000);
IF l_rec_orders.ORIGIN_COUNTRY = g_select_all THEN
l_filter_type := 3;
l_multi_select:= 1;
IF l_rec_orders.ORIGIN_COUNTRY = g_select_all THEN
l_filter_type := 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_countries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end DESTINATION_COUNTRY <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.Items <> g_select_all THEN
DECLARE
l_items VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_items||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Items <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.Customers <> g_select_all THEN
DECLARE
l_Customers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Customers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Customers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_orders.Suppliers <> g_select_all THEN
DECLARE
l_Suppliers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Suppliers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Suppliers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_WEIGHT_from||''''||l_delim||
l_null_str||l_delim||''''||l_WEIGHT_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_CUBE_from||''''||l_delim||
l_null_str||l_delim||''''||l_CUBE_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END insert_order_selection;
PROCEDURE insert_excep_selection(p_query_id IN NUMBER) IS
cursor cur_excep is
select ALL_EXCEPTION_TYPES ,EXCEPTION_TYPES ,
EXCEPTION_TYPE_IDS ,TRUCKLOADS ,
PARCELS ,LTLS ,
CONTINUOUS_MOVES ,DELIVERIES ,
FACILITIES ,FACILITY_IDS ,
CARRIERS ,CARRIER_IDS ,
CUSTOMERS ,CUSTOMER_IDS ,
SUPPLIERS ,SUPPLIER_IDS ,
EXCEPTION_COUNT_TYPE ,EXCEPTION_COUNT_FROM ,
EXCEPTION_COUNT_TO ,EXCEPTION_STATUS ,
EXCEPTION_STATUS_IDS
from mst_excep_selection_criteria
where query_id = p_query_id;
l_insert_begin VARCHAR2(500);
l_insert_what VARCHAR2(3000);
l_insert_who VARCHAR2(500);
l_delete_str VARCHAR2(500);
l_multi_select NUMBER ;--:= 2; -- 1 true, 2 false
l_multi_select := 2; -- 1 true, 2 false
l_delete_str := 'DELETE MST_SELECTION_CRITERIA WHERE QUERY_ID = :P_QUERYID';
l_executed := execute_dyn_sql(l_delete_str,p_query_id);
IF g_select_all IS NULL THEN
FND_MESSAGE.set_name('MST','MST_PQ_ALL');
g_select_all:= FND_MESSAGE.GET;
l_insert_begin := 'INSERT INTO MST_SELECTION_CRITERIA '||
'(QUERY_ID , FIELD_NAME , SEQUENCE ,'||
'FILTER_TYPE , FIELD_VALUE_FROM, DISPLAY_VALUE,'||
'FIELD_VALUE_TO, MULTI_SELECT , ACTIVE_FLAG ,'||
'CREATED_BY , CREATION_DATE ) VALUES ';
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''EXCEPTION_TYPE'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_excep_type_ids||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''MODE_OF_TRANSPORT'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_modes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''MODE_OF_TRANSPORT'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_modes||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''CONTINUOUS_MOVE_ID'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_cm||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||'''DELIVERY_ID'''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_deliveries||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_excep.FACILITIES <> g_select_all THEN
DECLARE
l_facilities VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_facilities||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end FACILITIES <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_excep.Carriers <> g_select_all THEN
DECLARE
l_Carriers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Carriers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Carriers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_excep.Customers <> g_select_all THEN
DECLARE
l_Customers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Customers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Customers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
IF l_rec_excep.Suppliers <> g_select_all THEN
DECLARE
l_Suppliers VARCHAR2(2000);
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_Suppliers||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END IF; -- end Suppliers <> g_select_all
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 2;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_excep_Count_from ||''''||l_delim||
l_null_str||l_delim||''''||l_excep_Count_to||''''||l_delim||
l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
l_multi_select := 2;
l_insert_what := '(';
l_multi_select:= 1;
l_INSERT_WHAT := l_insert_what ||p_query_id||l_delim||''''||l_field_name||''''||l_delim||
l_sequence ||l_delim||l_filter_type||l_delim||
''''||l_exceptions||''''||l_delim||
l_null_str||l_delim||l_null_str||l_delim||l_multi_select||l_delim||
l_active_flag||l_delim;
l_insert_who := l_userid||l_delim||'SYSDATE'||')';
l_executed := execute_dyn_sql(l_insert_begin||l_insert_what||l_insert_who,NULL);
END insert_excep_selection;
SELECT 'x' FROM mst_personal_queries
WHERE query_id = p_query_id
FOR UPDATE OF execute_flag NOWAIT;
UPDATE mst_personal_queries
SET execute_flag = 1
WHERE CURRENT OF query_def;
SELECT query_id, query_type
FROM mst_personal_queries
WHERE query_id = p_query_id; -- Temp. Query