[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_query_select VARCHAR2(2000);
select party_id
from hz_parties, wsh_carriers
where party_name = p_carrier_name and party_id = carrier_id;
select lookup_code from fnd_lookup_values_vl
where lookup_type = p_lookup_type
and upper(meaning) like upper(p_meaning)||'%';
l_query_select := 'select wc.carrier_name, l.carrier_id, l.origin_id, l.destination_id, lv2.meaning, l.mode_of_transportation_code, r.region_type, min(l.lane_id) lane_id ';
l_query_select := 'select l.lane_id, l.lane_number, wc.carrier_name, ' ||
'l.origin_id, l.destination_id, lv2.meaning, ' ||
'lv3.meaning ,' ||
'decode(l.commodity_detail_flag, ''Y'', ' ||
'decode(l.commodity_catg_id, null, ''multiple'', m.description), null), ' ||
'lv1.meaning, l.distance, l.port_of_loading, ' ||
'l.port_of_discharge, l.transit_time, lh.name, ' ||
'l.basis, l.owner_id, l.carrier_id, l.mode_of_transportation_code, ' ||
'l.transit_time_uom, l.special_handling, l.additional_instructions, ' ||
'l.commodity_detail_flag,' ||
'l.service_detail_flag, ' ||
'l.commodity_catg_id,'||
' ls.service_code, ' ||
'l.distance_uom, l.pricelist_id, l.pricelist_view_flag, l.effective_date, ' ||
'l.expiry_date, ro.region_type, rd.region_type, l.comm_fc_class_code, ' ||
'l.schedules_flag, ls.lane_service_id, l.tariff_name, l.lane_type';
l_query_relax_down := ' AND zr.region_id in (select f1.region_id from wsh_regions f1 where f1.parent_region_id = :' || g_bind_counter_global;
l_query_relax_down := l_query_relax_down || ' UNION select :' || g_bind_counter_global ||
' from dual) ';
l_query_relax_down := l_query_relax_down || ' AND pd.region_id in (select f1.region_id from wsh_regions f1 where f1.parent_region_id = :' || g_bind_counter_global;
l_query_relax_down := l_query_relax_down || ' UNION select :' || g_bind_counter_global ||
' from dual) ';
l_query1 := l_query_select || l_query_from ||
l_query_common_join ||
l_query_common_criteria;
l_query1 := l_query_select || l_query_from ||
l_query_common_join || l_query_relax_up ||
l_query_common_criteria;
l_query1 := l_query_select || l_query_from ||
l_query_common_join || l_query_relax_up ||
l_query_common_criteria || l_query_group_by;
l_query1 := l_query_select || l_query_from ||
l_query_common_join || l_query_relax_up ||
l_query_common_criteria ||l_query_order_by;
l_query2 := l_query_select || l_query_from ||
l_query_common_join || l_query_relax_down ||
l_query_common_criteria||l_query_group_by;
l_query := 'SELECT S.SCHEDULES_ID, ' ||
'S.LANE_ID, ' ||
'L.LANE_NUMBER, ' ||
'S.DEPARTURE_DATE, ' ||
'S.ARRIVAL_DATE, ' ||
'S.TRANSIT_TIME, ' ||
'S.FREQUENCY, ' ||
'S.EFFECTIVE_DATE, ' ||
'S.PORT_OF_LOADING, ' ||
'S.PORT_OF_DISCHARGE, ' ||
'S.EXPIRY_DATE, ' ||
'S.ARRIVAL_DATE_INDICATOR, ' ||
'S.FREQUENCY_ARRIVAL, ' ||
'L.ORIGIN_ID, ' ||
'L.DESTINATION_ID, ' ||
'LV2.MEANING AS MODE_OF_TRANSPORTATION, ' ||
'L.CARRIER_ID, ' ||
'H.PARTY_NAME AS CARRIER_NAME, ' ||
'S.DEPARTURE_TIME, ' ||
'S.ARRIVAL_TIME, ' ||
'LV1.MEANING AS FREQUENCY_TYPE_MEANING, ' ||
'S.TRANSIT_TIME_UOM, ' ||
'S.VESSEL_TYPE, ' ||
'S.VESSEL_NAME, ' ||
'S.VOYAGE_NUMBER, ' ||
'S.ARRIVAL_TIME || decode(S.ARRIVAL_DATE_INDICATOR, null, '''', '' ('' || decode(substr(S.ARRIVAL_DATE_INDICATOR, 1, 1), ''-'',' ||
' TO_CHAR(S.ARRIVAL_DATE_INDICATOR), ''+'' || S.ARRIVAL_DATE_INDICATOR) || '')'') AS ARRIVAL_TIME_WITH_INDICATOR, ' ||
'L.MODE_OF_TRANSPORTATION_CODE, ' ||
'L.SERVICE_TYPE_CODE, ' ||
'DECODE(L.SERVICE_DETAIL_FLAG, ''Y'', DECODE(L.SERVICE_TYPE_CODE, null, ''multiple'', LV3.MEANING), null) AS SERVICE_TYPE_MEANING, ' ||
'S.FREQUENCY_TYPE AS FREQUENCY_TYPE, ' ||
--'S.FREQUENCY AS ORIG_FREQUENCY, ' ||
'NVL((SELECT ''Y'' FROM FTE_SCHEDULES S1 WHERE S1.SCHEDULES_ID = S.SCHEDULES_ID AND SYSDATE BETWEEN NVL(S.EFFECTIVE_DATE, SYSDATE) AND NVL(S.EXPIRY_DATE, SYSDATE)), ''N'') AS ACTIVE_FLAG ' ||
'FROM FTE_SCHEDULES S, FTE_LANES L, FND_LOOKUP_VALUES_VL LV1, ' ||
'FND_LOOKUP_VALUES_VL LV2, HZ_PARTIES H, FND_LOOKUP_VALUES_VL LV3 ' ||
'WHERE S.LANE_ID = L.LANE_ID ' ||
'AND NVL(S.EDITABLE_FLAG, ''Y'') = ''Y'' ' ||
'AND NVL(S.FREQUENCY_TYPE, ''NULL'') = LV1.LOOKUP_CODE ' ||
'AND LV1.LOOKUP_TYPE = ''FTE_FREQUENCY_TYPE'' ' ||
'AND LV2.LOOKUP_TYPE = ''WSH_MODE_OF_TRANSPORT'' ' ||
'AND LV2.LOOKUP_CODE = L.MODE_OF_TRANSPORTATION_CODE ' ||
'AND L.CARRIER_ID = H.PARTY_ID ' ||
'AND LV3.LOOKUP_TYPE(+) = ''WSH_SERVICE_LEVELS'' ' ||
'AND LV3.LOOKUP_CODE(+) = L.SERVICE_TYPE_CODE ' ||
'AND L.LANE_ID IN (' || p_search_criteria.lane_ids_list || ')';
l_query := 'select distinct lrc.list_header_id ';