1 PACKAGE FTE_LANE_SEARCH AS
2 /* $Header: FTELNSES.pls 120.1 2005/06/27 23:07:30 appldev ship $ */
3
4 -- ----------------------------------------------------------------
5 -- Name: Search_Lanes
6 -- Type: Procedure
7 --
8 -- Description: This procedure calls FTE_LANE_SEARCH_QUERY_GEN
9 -- procedures to create the dynamic SQL.
10 -- Binds the variables, executes the query, and
11 -- prepares the results in SQL records and SQL
12 -- types for returning to the calling procedure.
13 -- Constraints checking is done for lanes if
14 -- a delivery_id or delivery_leg_id is present.
15 --
16 -- Input: p_search_type 'L' = lanes; 'S' = schedules
17 -- p_source_type 'L' = lanes; 'R' = rating
18 -- <currently not used>
19 -- p_num_results maximum number of results
20 -- desired (OA should be 200)
21 -- -----------------------------------------------------------------
22 PROCEDURE Search_Lanes(p_search_criteria IN fte_search_criteria_rec,
23 p_search_type IN VARCHAR2, -- 'L' for lanes, 'S' for schedules
24 p_source_type IN VARCHAR2, -- 'L' for lane search, 'R' for rating
25 p_num_results IN NUMBER,
26 x_lane_results OUT NOCOPY fte_lane_tab,
27 x_schedule_results OUT NOCOPY fte_schedule_tab,
28 x_return_message OUT NOCOPY VARCHAR2,
29 x_return_status OUT NOCOPY VARCHAR2);
30
31 -- ----------------------------------------------------------------
32 -- Name: Search_Lanes
33 -- Type: Procedure
34 --
35 -- Description: OverLoaded method for the above search_lanes. This
36 -- will be called if we have more than one set of search criteria
37 -- Duplicate fetches in the search would be removed
38 -- p_search_type 'L' - Lanes
39 -- 'S' - Scheduldes
40 -- Limitations:
41 -- and p_source_type = 'R'
42 -- -----------------------------------------------------------------
43
44 PROCEDURE Search_Lanes( p_search_criteria IN fte_search_criteria_tab,
45 p_num_results IN NUMBER,
46 p_search_type IN VARCHAR2,
47 x_lane_results OUT NOCOPY fte_lane_tab,
48 x_schedule_results OUT NOCOPY fte_schedule_tab,
49 x_return_message OUT NOCOPY VARCHAR2,
50 x_return_status OUT NOCOPY VARCHAR2);
51
52 PROCEDURE Get_Rate_Chart_Ids(p_search_criteria IN fte_search_criteria_rec,
53 p_num_results IN NUMBER,
54 x_rate_chart_ids OUT NOCOPY STRINGARRAY,
55 x_return_status OUT NOCOPY VARCHAR2);
56
57 PROCEDURE Get_Transit_Time(p_ship_from_loc_id IN NUMBER,
58 p_ship_to_site_id IN NUMBER,
59 p_carrier_id IN NUMBER,
60 p_service_code IN VARCHAR2,
61 p_mode_code IN VARCHAR2,
62 p_from IN VARCHAR2,
63 x_transit_time OUT NOCOPY NUMBER,
64 x_return_status OUT NOCOPY VARCHAR2);
65
66 -- will cache transit time given a ship method by maintaining ship method
67 -- and transit time with same index for corresponding ship method-transit time
68 -- combination
69 TYPE t_ship_method_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
70 TYPE t_transit_time_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
71
72 g_ship_method_tab t_ship_method_tab;
73 g_transit_time_tab t_transit_time_tab;
74
75 END FTE_LANE_SEARCH;