[Home] [Help]
182:
183: -- cursor definitions
184: CURSOR c_get_carrier_id(p_carrier_name VARCHAR2) IS
185: select party_id
186: from hz_parties, wsh_carriers
187: where party_name = p_carrier_name and party_id = carrier_id;
188:
189: CURSOR c_get_lookup_code(p_lookup_type VARCHAR2, p_meaning VARCHAR2) IS
190: select lookup_code from fnd_lookup_values_vl
931: 'S.FREQUENCY_TYPE AS FREQUENCY_TYPE, ' ||
932: --'S.FREQUENCY AS ORIG_FREQUENCY, ' ||
933: '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 ' ||
934: 'FROM FTE_SCHEDULES S, FTE_LANES L, FND_LOOKUP_VALUES_VL LV1, ' ||
935: 'FND_LOOKUP_VALUES_VL LV2, HZ_PARTIES H, FND_LOOKUP_VALUES_VL LV3 ' ||
936: 'WHERE S.LANE_ID = L.LANE_ID ' ||
937: 'AND NVL(S.EDITABLE_FLAG, ''Y'') = ''Y'' ' ||
938: 'AND NVL(S.FREQUENCY_TYPE, ''NULL'') = LV1.LOOKUP_CODE ' ||
939: 'AND LV1.LOOKUP_TYPE = ''FTE_FREQUENCY_TYPE'' ' ||
1802: AND (p_parent_origins.COUNT > 0 OR p_parent_destinations.COUNT > 0) THEN
1803:
1804: -- Changes for J:Multiple Rate Charts
1805: /*
1806: l_query := l_query || 'from fte_lanes l, hz_parties hz, wsh_zone_regions zr, ' ||
1807: 'wsh_zone_regions pd where l.pricelist_id is not null ' ||
1808: 'AND l.origin_id = zr.parent_region_id ' ||
1809: 'AND l.destination_id = pd.parent_region_id ' ||
1810: 'AND l.carrier_id = hz.party_id ' ||
1809: 'AND l.destination_id = pd.parent_region_id ' ||
1810: 'AND l.carrier_id = hz.party_id ' ||
1811: 'and upper(hz.party_name) like upper(:' || g_bind_counter_global || ')';
1812: */
1813: l_query := l_query || 'from fte_lanes l, hz_parties hz, wsh_zone_regions zr, ' ||
1814: 'wsh_zone_regions pd, fte_lane_rate_charts lrc where l.lane_id = lrc.lane_id ' ||
1815: 'AND l.origin_id = zr.parent_region_id ' ||
1816: 'AND l.destination_id = pd.parent_region_id ' ||
1817: 'AND l.carrier_id = hz.party_id ' ||
1833:
1834: ELSE -- only have carrier
1835: -- Changes for J:Multiple Rate Charts
1836: /*
1837: l_query := l_query || 'from fte_lanes l, hz_parties hz ' ||
1838: 'where pricelist_id is not null and upper(party_name) ' ||
1839: 'like upper(:' || g_bind_counter_global || ')';
1840: */
1841: l_query := l_query || 'from fte_lanes l, hz_parties hz, fte_lane_rate_charts lrc ' ||
1837: l_query := l_query || 'from fte_lanes l, hz_parties hz ' ||
1838: 'where pricelist_id is not null and upper(party_name) ' ||
1839: 'like upper(:' || g_bind_counter_global || ')';
1840: */
1841: l_query := l_query || 'from fte_lanes l, hz_parties hz, fte_lane_rate_charts lrc ' ||
1842: 'where l.lane_id = lrc.lane_id and upper(hz.party_name) ' ||
1843: 'like upper(:' || g_bind_counter_global || ')';
1844:
1845: Process_Bind_Var(l_bindvars, p_carrier_name || '%', g_varchar2, g_type1);