1 package body DOM_WS_INTERFACE_PUB AS
2 /*$Header: DOMPITFB.pls 120.8 2006/09/04 10:31:57 ysireesh noship $ */
3
4 -- ------------------------------------------------------------
5 -- -------------- Global variables and constants --------------
6 -- ------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DOM_WS_INTERFACE_PUB';
8 G_CURRENT_USER_ID NUMBER := FND_GLOBAL.USER_ID;
9 G_CURRENT_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
10 G_DOM_INTERFACE_JSP VARCHAR2(80) := '/OA_HTML/DOMInterface.jsp';
11
12 -- ---------------------------------------------------------------------
13 -- For debugging purposes.
14 PROCEDURE mdebug (msg IN varchar2) IS
15 BEGIN
16 --dd_debug('DOM WS INTERFACE ' || msg);
17 --dbms_output.put_line('DOM WS INTERFACE :' || msg);
18 null;
19 END mdebug;
20 -- ---------------------------------------------------------------------
21
22
23 procedure call_dom_interface_jsp
24 (
25 p_param_query_string IN VARCHAR2
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_count OUT NOCOPY NUMBER
28 ,x_msg_data OUT NOCOPY VARCHAR2
29 )
30 IS
31
32 l_proxy varchar2(80);
33 l_request UTL_HTTP.REQ;
34 l_response UTL_HTTP.RESP;
35 l_name VARCHAR2(255);
36 l_value VARCHAR2(1023);
37 v_msg VARCHAR2(80);
38 v_url VARCHAR2(32767) := '/';
39 l_api_name varchar2(80):='call_dom_interface_jsp';
40
41 cookies UTL_HTTP.COOKIE_TABLE;
42 my_session_id BINARY_INTEGER;
43 secure VARCHAR2(1);
44 proxy VARCHAR2(250);
45
46
47 BEGIN
48 SELECT profile_option_value INTO proxy
49 FROM fnd_profile_option_values vl , FND_PROFILE_OPTIONS pr
50 WHERE vl.profile_option_id = pr.profile_option_id
51 AND pr.PROFILE_OPTION_NAME = 'WEB_PROXY_HOST';
52
53 --proxy := 'http://www-proxy.us.oracle.com';
54
55
56 UTL_HTTP.Set_Response_Error_Check ( enable => true );
57
58 UTL_HTTP.Set_Detailed_Excp_Support ( enable => true );
59
60 /*
61 UTL_HTTP.Set_Proxy (
62 proxy => proxy,
63 no_proxy_domains => '');
64 */
65
66 v_url := fnd_profile.value('APPS_FRAMEWORK_AGENT');
67 -- v_url := 'http://qapache.us.oracle.com:6482';
68
69 v_url := v_url || G_DOM_INTERFACE_JSP||'?'||p_param_query_string;
70
71 l_request := Utl_Http.Begin_Request (
72 url => v_url,
73 method => 'POST',
74 http_version => 'HTTP/1.1'
75 );
76
77
78 l_response := UTL_HTTP.Get_Response ( r => l_request );
79 FOR i IN 1..UTL_HTTP.Get_Header_Count ( r => l_response )
80 LOOP
81 UTL_HTTP.Get_Header (
82 r => l_response,
83 n => i,
84 name => l_name,
85 value => l_value );
86 END LOOP;
87 BEGIN
88 LOOP
89 UTL_HTTP.Read_Text (
90 r => l_response,
91 data => v_msg );
92
93 END LOOP;
94 EXCEPTION WHEN UTL_HTTP.End_Of_Body then null;
95 END;
96
97 UTL_HTTP.End_Response ( r => l_response );
98
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101
102 FND_MSG_PUB.Count_And_Get
103 ( p_count => x_msg_count,
104 p_data => x_msg_data
105 );
106 EXCEPTION
107 /*
108 The exception handling illustrates the use of "pragma-ed" exceptions
109 like Utl_Http.Http_Client_Error. In a realistic example, the program
110 would use these when it coded explicit recovery actions.
111 Request_Failed is raised for all exceptions after calling
112 Utl_Http.Set_Detailed_Excp_Support ( enable=>false )
113
114 And it is NEVER raised after calling with enable=>true
115 */
116 WHEN UTL_HTTP.Request_Failed THEN
117 mdebug ( 'Request_Failed: ' || Utl_Http.Get_Detailed_Sqlerrm );
118 /* raised by URL http://xxx.oracle.com/ */
119 WHEN UTL_HTTP.Http_Server_Error THEN
120 mdebug ( 'Http_Server_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );
121 /* raised by URL /xxx */
122 when UTL_HTTP.Http_Client_Error THEN
123 mdebug ( 'Http_Client_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );
124 /* code for all the other defined exceptions you can recover from */
125
126 WHEN FND_API.G_EXC_ERROR THEN
127 ROLLBACK TO DOM_ADD_OFO_GROUP_MEMBER;
128 mdebug('. CREATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_ERROR''');
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 FND_MSG_PUB.Count_And_Get
131 ( p_count => x_msg_count,
132 p_data => x_msg_data
133 );
134 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
135 ROLLBACK TO EGO_CREATE_RELATIONSHIP;
136 mdebug('. CREATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR''');
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
138 FND_MSG_PUB.Count_And_Get
139 ( p_count => x_msg_count,
140 p_data => x_msg_data
141 );
142 WHEN OTHERS THEN
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
144 IF FND_MSG_PUB.Check_Msg_Level
145 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
146 THEN
147 FND_MSG_PUB.Add_Exc_Msg
148 ( G_PKG_NAME,
149 l_api_name
150 );
151 END IF;
152 FND_MSG_PUB.Count_And_Get
153 ( p_count => x_msg_count,
154 p_data => x_msg_data
155 );
156 mdebug (SQLERRM);
157
158
159 END;
160
161
162
163 ----------------------------------------------------------------------------
164 -- A. Add_OFO_Group_Member
165 ----------------------------------------------------------------------------
166
167 procedure Add_OFO_Group_Member (
168 p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2,
170 p_commit IN VARCHAR2,
171 p_group_id IN NUMBER,
172 p_member_id IN NUMBER,
173 x_return_status OUT NOCOPY VARCHAR2,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2
176 ) IS
177 ------------------------------------------------------------------------
178 -- Start OF comments
179 -- API name : Add_OFO_Group_Member
180 -- TYPE : Public
181 -- Pre-reqs : None
182 -- FUNCTION : Add a member to the corresponding OFO Group.
183 --
184 --
185 -- Parameters:
186 -- IN : p_api_version IN NUMBER (required)
187 -- API Version of this procedure
188 -- p_init_msg_level IN VARCHAR2 (optional)
189 -- DEFAULT = FND_API.G_FALSE
190 -- Indicates whether the message stack needs to be cleared
191 -- p_commit IN VARCHAR2 (optional)
192 -- DEFAULT = FND_API.G_FALSE
193 -- Indicates whether the data should be committed
194 -- p_group_id IN NUMBER (required)
195 -- Group to which the member is being added
196 -- Eg., A Group
197 -- p_member_id IN VARCHAR2 (required)
198 -- Member which is to be added
199 -- Eg., PERSON
200 --
201 -- OUT : x_return_status OUT NUMBER
202 -- Result of all the operations
203 -- FND_API.G_RET_STS_SUCCESS if success
204 -- FND_API.G_RET_STS_ERROR if error
205 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
206 -- x_msg_count OUT NUMBER
207 -- number of messages in the message list
208 -- x_msg_data OUT VARCHAR2
209 -- if number of messages is 1, then this parameter
210 -- contains the message itself
211 --
212 -- Called From:
213 -- ego_party_pub.add_group_member
214 --
215 -- Version: Current Version 1.0
216 -- Previous Version : None
217 -- Notes :
218 --
219 -- END OF comments
220 ------------------------------------------------------------------------
221
222 l_Sysdate DATE := Sysdate;
223
224 l_api_name CONSTANT VARCHAR2(30) := 'Add_OFO_Group_Member';
225 -- On addition of any Required parameters the major version needs
226 -- to change i.e. for eg. 1.X to 2.X.
227 -- On addition of any Optional parameters the minor version needs
228 -- to change i.e. for eg. X.6 to X.7.
229 l_api_version CONSTANT NUMBER := 1.0;
230
231 -- General variables
232
233 l_success BOOLEAN; --boolean for descr. flex valiation
234
235 l_group_name VARCHAR2(100);
236 l_member_name VARCHAR2(100); --my wild assumed length
237 l_logged_in VARCHAR2(100);
238 l_name VARCHAR2(255);
239 l_value VARCHAR2(1023);
240 v_msg VARCHAR2(80);
241
242
243 l_param_query_string VARCHAR2(2000);
244
245 CURSOR user_info is
246 SELECT USER_NAME FROM fnd_user WHERE person_party_id = p_member_id;
247
248 BEGIN
249 -- Standard Start of API savepoint
250 mdebug('. ADD_OFO_GROUP_MEMBER: ADD_OFO_GROUP_MEMBER .....1...... ');
251 IF NOT FND_API.Compatible_API_Call (l_api_version,
252 p_api_version,
253 l_api_name,
254 G_PKG_NAME)
255 THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END IF;
258 -- Initialize API message list if necessary.
259 -- Initialize message list if p_init_msg_list is set to TRUE.
260 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
261 FND_MSG_PUB.initialize;
262 END IF;
263
264 mdebug(' in DOM WS interface ' || p_group_id || ' ' || p_member_id);
265
266 SELECT GROUP_NAME INTO l_group_name FROM EGO_GROUPS_V WHERE GROUP_ID = p_group_id;
267
268 --SELECT MEMBER_USER_NAME INTO l_member_name FROM EGO_GROUP_MEMBERS_V WHERE GROUP_ID = p_group_id AND MEMBER_PERSON_ID = p_member_id;
269 --SELECT USER_NAME INTO l_member_name FROM fnd_user WHERE person_party_id = p_member_id;
270 --SELECT USER_NAME INTO l_logged_in FROM FND_USER WHERE USER_ID = G_CURRENT_USER_ID;
271
272 FOR info_rec IN user_info LOOP
273 l_member_name := info_rec.USER_NAME;
274 SELECT us.user_name INTO l_logged_in
275 FROM hz_parties hz, fnd_user us
276 WHERE hz.created_by = us.USER_ID
277 AND party_id = p_group_id;
278
279
280 mdebug ( 'l_group_name : ' || l_group_name );
281 mdebug ( 'l_member_name : ' || l_member_name );
282 mdebug ( 'l_logged_in : ' || l_logged_in );
283
284 l_param_query_string:='opName=addUserToGroup&groupName=' || l_group_name || '&memberName=' || l_member_name || '&loggedInUser=' || l_logged_in;
285 call_dom_interface_jsp
286 (
287 p_param_query_string =>l_param_query_string
288 ,x_return_status =>x_return_status
289 ,x_msg_count =>x_msg_count
290 ,x_msg_data =>x_msg_data
291 );
292
293 END LOOP;
294
295 END Add_OFO_Group_Member;
296
297 --------------------------------------------------------------
298 procedure Update_Files_Document_Status (
299 p_api_version IN NUMBER,
300 p_service_url IN VARCHAR2,
301 p_document_id IN NUMBER,
302 p_status IN VARCHAR2,
303 p_login_user_name IN VARCHAR2,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2
307 )
308 IS
309
310 l_api_name CONSTANT VARCHAR2(30) := 'Update_Files_Document_Status';
311 -- On addition of any Required parameters the major version needs
312 -- to change i.e. for eg. 1.X to 2.X.
313 -- On addition of any Optional parameters the minor version needs
314 -- to change i.e. for eg. X.6 to X.7.
315 l_api_version CONSTANT NUMBER := 1.0;
316
317 -- General variables
318
319 l_success BOOLEAN;
320 l_param_query_string VARCHAR2(2000);
321
322 BEGIN
323 -- Standard Start of API savepoint
324 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)
325 THEN
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327 END IF;
328
329
330 l_param_query_string:='opName=updateDocStatus&docId='||p_document_id||'&status='||p_status||'&serviceUrl='||p_service_Url||'&loggedInUser='||p_login_user_name;
331 call_dom_interface_jsp
332 (
333 p_param_query_string =>l_param_query_string
334 ,x_return_status =>x_return_status
335 ,x_msg_count =>x_msg_count
336 ,x_msg_data =>x_msg_data
337 );
338
339 END Update_Files_Document_Status;
340
341 --------------------------------------------------------------
342
343 procedure Grant_Attachments_OCSRole (
344 p_api_version IN NUMBER,
345 p_service_url IN VARCHAR2,
346 p_family_id IN NUMBER,
347 p_role IN VARCHAR2,
348 p_user_name IN VARCHAR2,
349 p_user_login IN VARCHAR2,
350 x_return_status OUT NOCOPY VARCHAR2,
351 x_msg_count OUT NOCOPY NUMBER,
352 x_msg_data OUT NOCOPY VARCHAR2
353 )
354 IS
355
356
357 l_api_name CONSTANT VARCHAR2(30) := 'Grant_Attachments_OCSRole';
358 -- On addition of any Required parameters the major version needs
359 -- to change i.e. for eg. 1.X to 2.X.
360 -- On addition of any Optional parameters the minor version needs
361 -- to change i.e. for eg. X.6 to X.7.
362 l_api_version CONSTANT NUMBER := 1.0;
363
364 -- General variables
365
366 l_success BOOLEAN;
367 l_param_query_string VARCHAR2(2000);
368
369 BEGIN
370
371 -- Standard Start of API savepoint
372 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)
373 THEN
374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
375 END IF;
376
377 -- code_debug('YSIREESH: User id: '+FND_PROFILE.Value('USER_ID'));
378 l_param_query_string:='opName=grantAttachmentsOCSRole&familyId='||p_family_id||'&role='||p_role||'&serviceUrl=' || p_service_url || '&userToRole='||p_user_name||'&loggedInUser='||p_user_login;
379 call_dom_interface_jsp
380 (
381 p_param_query_string =>l_param_query_string
382 ,x_return_status =>x_return_status
383 ,x_msg_count =>x_msg_count
384 ,x_msg_data =>x_msg_data
385 );
386
387 END Grant_Attachments_OCSRole;
388
389 procedure Remove_Attachments_OCSRole (
390 p_api_version IN NUMBER,
391 p_service_url IN VARCHAR2,
392 p_family_id IN NUMBER,
393 p_role IN VARCHAR2,
394 p_user_name IN VARCHAR2,
395 p_user_login IN VARCHAR2,
396 x_return_status OUT NOCOPY VARCHAR2,
397 x_msg_count OUT NOCOPY NUMBER,
398 x_msg_data OUT NOCOPY VARCHAR2
399 )
400 IS
401
402 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Attachments_OCSRole';
403 -- On addition of any Required parameters the major version needs
404 -- to change i.e. for eg. 1.X to 2.X.
405 -- On addition of any Optional parameters the minor version needs
406 -- to change i.e. for eg. X.6 to X.7.
407 l_api_version CONSTANT NUMBER := 1.0;
408
409 -- General variables
410
411 l_success BOOLEAN;
412 l_param_query_string VARCHAR2(2000);
413
414 BEGIN
415 -- Standard Start of API savepoint
416 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)
417 THEN
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 END IF;
420
421
422 l_param_query_string:='opName=grantAttachmentsOCSRole&familyId='||p_family_id||'&role='||p_role||'&serviceUrl=' || p_service_url || '&userToRole='||p_user_name||'&loggedInUser='||p_user_login||'&addOrRemove=REMOVE';
423 call_dom_interface_jsp
424 (
425 p_param_query_string =>l_param_query_string
426 ,x_return_status =>x_return_status
427 ,x_msg_count =>x_msg_count
428 ,x_msg_data =>x_msg_data
429 );
430
431 END Remove_Attachments_OCSRole;
432
433
434
435
436 END DOM_WS_INTERFACE_PUB;
[Home] [Help]