[Home] [Help]
PACKAGE: APPS.FTE_LANE_SEARCH_QUERY_GEN
Source
1 PACKAGE FTE_LANE_SEARCH_QUERY_GEN AUTHID CURRENT_USER AS
2 /* $Header: FTELNQYS.pls 120.0 2005/05/26 17:27:27 appldev noship $ */
3
4 -- ----------------------------------------------------------------------------------------
5 --
6 -- Tables and records for input
7 --
8
9 g_varchar2 CONSTANT VARCHAR2(10) := 'VARCHAR2';
10 g_number CONSTANT VARCHAR2(10) := 'NUMBER';
11 g_date CONSTANT VARCHAR2(10) := 'DATE';
12
13 TYPE fte_regions_types IS RECORD
14 (region_id NUMBER,
15 region_type NUMBER,
16 country VARCHAR2(100),
17 country_code VARCHAR2(10),
18 state VARCHAR2(100),
19 state_code VARCHAR2(10),
20 city VARCHAR2(100),
21 city_code VARCHAR2(10),
22 postal_code_from VARCHAR2(30),
23 postal_code_to VARCHAR2(30));
24
25 TYPE fte_lane_search_regions_tab IS TABLE OF fte_regions_types INDEX BY BINARY_INTEGER;
26
27 TYPE fte_lane_search_criteria_rec IS RECORD
28 (relax_flag VARCHAR2(1), -- will dictate if relaxation occurs
29 origin_zip_request VARCHAR2(30), -- postal code entered
30 dest_zip_request VARCHAR2(30), -- postal code entered
31 mode_of_transport VARCHAR2(30),
32 lane_number VARCHAR2(30),
33 carrier_id NUMBER,
34 carrier_name VARCHAR2(360),
35 commodity_catg_id NUMBER,
36 commodity VARCHAR2(240),
37 service_code VARCHAR2(30),
38 service VARCHAR2(80),
39 -- equipment_code VARCHAR2(30),
40 -- equipment VARCHAR2(80),
41 schedule_only_flag VARCHAR2(1),
42 dep_date_from DATE,
43 dep_date_to DATE,
44 arr_date_from DATE,
45 arr_date_to DATE,
46 lane_ids_list VARCHAR2(2000),
47 vehicle_id NUMBER,
48 effective_date DATE,
49 effective_date_type VARCHAR2(10),
50 tariff_name VARCHAR2(80)
51 );
52
53 -- [08/30]Add check for Vehicle_id
54
55 TYPE bindvar_type IS RECORD
56 (bindvar VARCHAR2(30),
57 bindtype VARCHAR2(10),
58 bindvarindex NUMBER);
59
60 TYPE bindvars IS TABLE OF bindvar_type INDEX BY BINARY_INTEGER;
61
62
63 --
64 -- Tables and records for output
65 --
66
67 PROCEDURE Create_Lane_Query(p_search_criteria IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_criteria_rec,
68 p_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
69 p_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
70 p_parent_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab, -- includes origins
71 p_parent_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab, -- includes dests
72 p_source_type IN VARCHAR2,
73 x_query1 OUT NOCOPY VARCHAR2,
74 x_query2 OUT NOCOPY VARCHAR2,
75 x_bindvars1 OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
76 x_bindvars2 OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
77 x_bindvars_common OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
78 x_bindvars_orderby OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
79 x_return_message OUT NOCOPY VARCHAR2,
80 x_return_status OUT NOCOPY VARCHAR2);
81
82 PROCEDURE Create_Schedule_Query(p_search_criteria IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_criteria_rec,
83 p_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
84 p_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
85 p_parent_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab, -- includes origins
86 p_parent_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab, -- includes dests
87 x_query OUT NOCOPY VARCHAR2,
88 x_bindvars OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
89 x_return_message OUT NOCOPY VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2);
91
92 PROCEDURE Create_Schedule_Clause(p_dep_date_from IN DATE,
93 p_dep_date_to IN DATE,
94 p_arr_date_from IN DATE,
95 p_arr_date_to IN DATE,
96 x_query OUT NOCOPY VARCHAR2,
97 x_bindvars IN OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars,
98 x_return_message OUT NOCOPY VARCHAR2,
99 x_return_status OUT NOCOPY VARCHAR2);
100
101
102 PROCEDURE Create_Rate_Chart_Query(p_parent_origins IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
103 p_parent_destinations IN FTE_LANE_SEARCH_QUERY_GEN.fte_lane_search_regions_tab,
104 p_origin_zip_request IN VARCHAR2,
105 p_dest_zip_request IN VARCHAR2,
106 p_carrier_name IN VARCHAR2,
107 p_tariff_name IN VARCHAR2,
108 x_query OUT NOCOPY VARCHAR2,
109 x_bindvars OUT NOCOPY FTE_LANE_SEARCH_QUERY_GEN.bindvars);
110
111 END FTE_LANE_SEARCH_QUERY_GEN;