[Home] [Help]
PACKAGE BODY: APPS.AST_SEARCHURL_PVT
Source
1 PACKAGE BODY ast_SEARCHURL_PVT AS
2 /* $Header: astvschb.pls 115.12 2002/02/06 11:44:32 pkm ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) :='AST_SEARCHURL_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) :='astvsrch.pls';
6 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
7 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
8 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
9 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
10 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
11
12 PROCEDURE Query_SearchURL (p_api_version IN NUMBER := 1.0,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
16 x_return_status OUT VARCHAR2,
17 x_msg_count OUT NUMBER,
18 x_msg_data OUT VARCHAR2,
19 p_search_id IN NUMBER, -- add by jypark 12/27/2000 for new requirement
20 p_fname IN VARCHAR2,
21 p_lname IN VARCHAR2,
22 p_address IN VARCHAR2,
23 p_city IN VARCHAR2,
24 p_state IN VARCHAR2,
25 p_zip IN VARCHAR2,
26 p_country IN VARCHAR2,
27 x_search_url OUT VARCHAR2,
28 x_max_nbr_pages OUT VARCHAR2,
29 x_next_page_ident OUT VARCHAR2)
30 AS
31 l_api_version CONSTANT NUMBER := 1.0;
32 l_api_name CONSTANT VARCHAR2(30) := 'Query_SearchURL';
33 l_search_url VARCHAR2(1000);
34 i NUMBER;
35 j NUMBER;
36 k NUMBER;
37 l_return_status VARCHAR2(1);
38 l_msg_count NUMBER;
39 l_msg_data VARCHAR2(32767);
40
41 -- added these for message hooks
42 l_fname VARCHAR2(50);
43 l_lname VARCHAR2(50);
44 l_address VARCHAR2(100);
45 l_city VARCHAR2(50);
46 l_state VARCHAR2(50);
47 l_zip VARCHAR2(50);
48 l_country VARCHAR2(50);
49
50 CURSOR c_wsearch(x_search_id NUMBER) IS
51 SELECT search_id, search_url, next_page_ident, max_nbr_pages
52 FROM ast_web_searches
53 WHERE search_id = x_search_id
54 AND UPPER(enabled_flag) = 'Y';
55
56 /* begin: commented by scherkas on 01/12/2001 to support maps
57 AND UPPER(directory_assist_flag) = 'Y';
58 end: commented by scherkas on 01/12/2001 to support maps */
59
60 CURSOR c_qstring(x_search_id NUMBER) IS
61 SELECT query_string_id, switch_separator, url_separator, header_const, trailer_const
62 FROM ast_query_strings
63 WHERE search_id = x_search_id
64 AND UPPER(enabled_flag) = 'Y';
65
66 CURSOR c_cswitch(x_query_string_id NUMBER) IS
67 SELECT cgi_switch_id, switch_code, switch_type
68 FROM ast_cgi_switches
69 WHERE query_string_id = x_query_string_id
70 AND UPPER(enabled_flag) = 'Y'
71 ORDER BY sort_order;
72
73 CURSOR c_sdata(x_cgi_switch_id NUMBER) IS
74 SELECT first_name_yn, last_name_yn, address_yn, city_yn, state_yn, country_yn, zip_yn
75 FROM ast_switch_data
76 WHERE cgi_switch_id = x_cgi_switch_id;
77
78 BEGIN
79 --dbms_output.put_line('In Query_SearchURL ....');
80 -- Standard begin of API savepoint
81 SAVEPOINT Query_SearchURL_PVT;
82
83 -- Standard call to check for call compatibility.
84 IF NOT FND_API.Compatible_API_Call (l_api_version,
85 p_api_version,
86 l_api_name,
87 G_PKG_NAME) THEN
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90
91 -- Check p_init_msg_list
92 IF FND_API.to_Boolean( p_init_msg_list ) THEN
93 FND_MSG_PUB.initialize;
94 END IF;
95
96 -- Implementation of User Hooks
97 /* Copy all parameters to local variables to be passed to Pre, Post and Business APIs */
98 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
99 /* l_return_status - will be a out variable to get return code from called APIs */
100
101 l_fname := p_fname;
102 l_lname := p_lname;
103 l_address := p_address;
104 l_city := p_city;
105 l_state := p_state;
106 l_zip := p_zip;
107 l_country := p_country;
108
109 /* Customer pre -processing section - Mandatory */
110 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
111 ast_SEARCHURL_CUHK.Query_SearchURL_PRE(p_api_version => l_api_version,
112 x_return_status => l_return_status,
113 x_msg_count => l_msg_count,
114 x_msg_data => l_msg_data,
115 p_fname => l_fname,
116 p_lname => l_lname,
117 p_address => l_address,
118 p_city => l_city,
119 p_state => l_state,
120 p_zip => l_zip,
121 p_country => l_country);
122 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
123 RAISE FND_API.G_EXC_ERROR;
124 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF;
127 END IF;
128
129
130 /* Verticle industry pre- processing section - mandatory */
131 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
132 ast_SEARCHURL_VUHK.Query_SearchURL_PRE(p_api_version => l_api_version,
133 x_return_status => l_return_status,
134 x_msg_count => l_msg_count,
135 x_msg_data => l_msg_data,
136 p_fname => l_fname,
137 p_lname => l_lname,
138 p_address => l_address,
139 p_city => l_city,
140 p_state => l_state,
141 p_zip => l_zip,
142 p_country => l_country);
143 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
144 RAISE FND_API.G_EXC_ERROR;
145 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END IF;
148 END IF;
149
150
151 -- Initialize API return status to success
152 x_return_status := FND_API.G_RET_STS_SUCCESS;
153
154 -- beginning of API body
155
156 --dbms_output.put_line('before first cursor');
157
158 FOR rec_wsearch IN c_wsearch(p_search_id) LOOP
159 l_search_url := rec_wsearch.search_url;
160 x_max_nbr_pages := rec_wsearch.max_nbr_pages;
161 x_next_page_ident := rec_wsearch.next_page_ident;
162 --dbms_output.put_line('l_search_url=' || l_search_url);
163 FOR rec_qstring IN c_qstring(rec_wsearch.search_id) LOOP
164 l_search_url := l_search_url || rec_qstring.url_separator || rec_qstring.header_const;
165 --dbms_output.put_line('l_search_url=' || l_search_url);
166 i := 0;
167 FOR rec_cswitch IN c_cswitch(rec_qstring.query_string_id) LOOP
168 IF i = 0 THEN
169 l_search_url := l_search_url || rec_cswitch.switch_code;
170 ELSE
171 l_search_url := l_search_url || rec_qstring.switch_separator || rec_cswitch.switch_code;
172 END IF;
173 i := i + 1;
174
175 --dbms_output.put_line('l_search_url=' || l_search_url);
176 FOR rec_sdata IN c_sdata(rec_cswitch.cgi_switch_id) LOOP
177 IF UPPER(rec_sdata.first_name_yn) = 'Y' THEN
178 l_search_url := l_search_url || '=' || l_fname;
179 ELSIF UPPER(rec_sdata.last_name_yn) = 'Y' THEN
180 l_search_url := l_search_url || '=' || l_lname;
181 ELSIF UPPER(rec_sdata.address_yn) = 'Y' THEN
182 l_search_url := l_search_url || '=' || l_address;
183 ELSIF UPPER(rec_sdata.city_yn) = 'Y' THEN
184 l_search_url := l_search_url || '=' || l_city;
185 ELSIF UPPER(rec_sdata.state_yn) = 'Y' THEN
186 l_search_url := l_search_url || '=' || l_state;
187 ELSIF UPPER(rec_sdata.country_yn) = 'Y' THEN
188 l_search_url := l_search_url || '=' || l_country;
189 ELSIF UPPER(rec_sdata.zip_yn) = 'Y' THEN
190 l_search_url := l_search_url || '=' || l_zip;
191 END IF;
192 --dbms_output.put_line('l_search_url=' || l_search_url);
193 END LOOP;
194 END LOOP;
195 --dbms_output.put_line('l_search_url=' || l_search_url);
196 if rec_qstring.trailer_const is not null then
197 l_search_url := l_search_url || rec_qstring.switch_separator || rec_qstring.trailer_const;
198 end if;
199 --dbms_output.put_line('l_search_url=' || l_search_url);
200 END LOOP;
201 exit;
202 END LOOP;
203 --dbms_output.PUT_LINE('Query String > ' || l_search_url);
204
205 l_search_url := REPLACE(l_search_url, ' ', '+');
206 x_search_url := l_search_url;
207
208 --dbms_output.PUT_LINE('x_searchurl >>>>>>>>>>>>> ' || x_search_url);
209 --dbms_output.PUT_LINE('x_max_nbr_pages >>>>>>>>> ' || x_max_nbr_pages);
210 --dbms_output.PUT_LINE('x_next_page_ident >>>>>>> ' || x_next_page_ident);
211
212 -- end of API body
213
214
215 /* Vertical Post Processing section - mandatory */
216 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
217 ast_SEARCHURL_VUHK.Query_SearchURL_POST(p_api_version => l_api_version,
218 x_return_status => l_return_status,
219 x_msg_count => l_msg_count,
220 x_msg_data => l_msg_data,
221 p_fname => l_fname,
222 p_lname => l_lname,
223 p_address => l_address,
224 p_city => l_city,
225 p_state => l_state,
226 p_zip => l_zip,
227 p_country => l_country);
228 if (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
229 RAISE FND_API.G_EXC_ERROR;
230 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 END IF;
233
234 END IF;
235
236 /* Customer Post Processing section - mandatory */
237 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
238 ast_SEARCHURL_CUHK.Query_SearchURL_POST(p_api_version => l_api_version,
239 x_return_status => l_return_status,
240 x_msg_count => l_msg_count,
241 x_msg_data => l_msg_data,
242 p_fname => l_fname,
243 p_lname => l_lname,
244 p_address => l_address,
245 p_city => l_city,
246 p_state => l_state,
247 p_zip => l_zip,
248 p_country => l_country);
249 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
250 RAISE FND_API.G_EXC_ERROR;
251 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254 END IF;
255
256 -- Standard check of p_commit
257 IF FND_API.To_Boolean(p_commit) THEN
258 COMMIT WORK;
259 END IF;
260
261 -- Standard call to get message count and if count is 1, get message info
262 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
263
264
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267 ROLLBACK TO Query_SearchURL_PVT;
268 x_return_status := FND_API.G_RET_STS_ERROR;
269 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
270
271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
272 ROLLBACK TO Query_SearchURL_PVT;
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
274 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
275
276 WHEN OTHERS THEN
277 ROLLBACK TO Query_SearchURL_PVT;
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
280 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
281 END IF;
282 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
283
284 END;
285 END;