1 package body DOM_WS_INTERFACE_PUB AS
2 /*$Header: DOMPITFB.pls 120.12 2011/06/02 07:50:18 evwang ship $ */
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 -- For bug 8401333
46 l_count NUMBER ;
47 l_profile_option_value VARCHAR2(240);
48 -- For bug 8401333
49
50
51 BEGIN
52 -- For bug 8401333
53 SELECT profile_option_value INTO l_profile_option_value FROM fnd_profile_option_values WHERE profile_option_id =
54 (select profile_option_id FROM fnd_profile_options WHERE profile_option_name = 'EGO_ENABLE_PLM')
55 AND LEVEL_ID =10001 AND LEVEL_VALUE=0;
56
57 IF (l_profile_option_value = '1') THEN
58 SELECT Count(*) INTO l_count FROM dom_repositories WHERE protocol = 'WEBSERVICES';
59
60 IF(l_count > 0) THEN
61 -- For bug 8401333
62
63 SELECT profile_option_value INTO proxy
64 FROM fnd_profile_option_values vl , FND_PROFILE_OPTIONS pr
65 WHERE vl.profile_option_id = pr.profile_option_id
66 AND pr.PROFILE_OPTION_NAME = 'WEB_PROXY_HOST';
67
68 --proxy := 'http://www-proxy.us.oracle.com';
69
70
71 UTL_HTTP.Set_Response_Error_Check ( enable => true );
72
73 UTL_HTTP.Set_Detailed_Excp_Support ( enable => true );
74
75 /*
76 UTL_HTTP.Set_Proxy (
77 proxy => proxy,
78 no_proxy_domains => '');
79 */
80
81 v_url := fnd_profile.value('APPS_FRAMEWORK_AGENT');
82 -- v_url := 'http://qapache.us.oracle.com:6482';
83
84 v_url := v_url || G_DOM_INTERFACE_JSP||'?'||p_param_query_string;
85
86 l_request := Utl_Http.Begin_Request (
87 url => v_url,
88 method => 'POST',
89 http_version => 'HTTP/1.1'
90 );
91
92
93 l_response := UTL_HTTP.Get_Response ( r => l_request );
94 FOR i IN 1..UTL_HTTP.Get_Header_Count ( r => l_response )
95 LOOP
96 UTL_HTTP.Get_Header (
97 r => l_response,
98 n => i,
99 name => l_name,
100 value => l_value );
101 END LOOP;
102 BEGIN
103 LOOP
104 UTL_HTTP.Read_Text (
105 r => l_response,
106 data => v_msg );
107
108 END LOOP;
109 EXCEPTION WHEN UTL_HTTP.End_Of_Body then null;
110 END;
111
112 UTL_HTTP.End_Response ( r => l_response );
113
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115
116
117 FND_MSG_PUB.Count_And_Get
118 ( p_count => x_msg_count,
119 p_data => x_msg_data
120 );
121
122 END IF;
123
124 END IF;
125 EXCEPTION
126 /*
127 The exception handling illustrates the use of "pragma-ed" exceptions
128 like Utl_Http.Http_Client_Error. In a realistic example, the program
129 would use these when it coded explicit recovery actions.
130 Request_Failed is raised for all exceptions after calling
131 Utl_Http.Set_Detailed_Excp_Support ( enable=>false )
132
133 And it is NEVER raised after calling with enable=>true
134 */
135 WHEN UTL_HTTP.Request_Failed THEN
136 mdebug ( 'Request_Failed: ' || Utl_Http.Get_Detailed_Sqlerrm );
137 /* raised by URL http://xxx.oracle.com/ */
138 WHEN UTL_HTTP.Http_Server_Error THEN
139 mdebug ( 'Http_Server_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );
140 /* raised by URL /xxx */
141 when UTL_HTTP.Http_Client_Error THEN
142 mdebug ( 'Http_Client_Error: ' || Utl_Http.Get_Detailed_Sqlerrm );
143 /* code for all the other defined exceptions you can recover from */
144
145 WHEN FND_API.G_EXC_ERROR THEN
146 ROLLBACK TO DOM_ADD_OFO_GROUP_MEMBER;
147 mdebug('. CREATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_ERROR''');
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 FND_MSG_PUB.Count_And_Get
150 ( p_count => x_msg_count,
151 p_data => x_msg_data
152 );
153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154 ROLLBACK TO EGO_CREATE_RELATIONSHIP;
155 mdebug('. CREATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR''');
156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
157 FND_MSG_PUB.Count_And_Get
158 ( p_count => x_msg_count,
159 p_data => x_msg_data
160 );
161 WHEN OTHERS THEN
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
163 IF FND_MSG_PUB.Check_Msg_Level
164 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
165 THEN
166 FND_MSG_PUB.Add_Exc_Msg
167 ( G_PKG_NAME,
168 l_api_name
169 );
170 END IF;
171 FND_MSG_PUB.Count_And_Get
172 ( p_count => x_msg_count,
173 p_data => x_msg_data
174 );
175 mdebug (SQLERRM);
176
177
178 END;
179
180
181
182
183
184 --Bug 9697013
185 FUNCTION Get_WebService_Repositories
186 RETURN BOOLEAN
187 IS
188
189
190 l_ws_exist BOOLEAN := FALSE;
191 l_protocol VARCHAR2(200) := NULL;
192
193 CURSOR Cur_WS_Rep
194 IS
195 SELECT DomRepositories.ID NODE_ID,
196 DomRepositories.NAME REPOS_NAME,
197 DomRepositories.DESCRIPTION NODE_DESCRIPTION,
198 DomRepositories.SHORT_NAME ,
199 DomRepositories.DAV_URL ,
200 DomRepositories.SERVICE_URL,
201 DomRepositories.PROTOCOL,
202 DomRepositories.SEQUENCE,
203 DomRepositories.CERTIFICATE_PATH
204 FROM DOM_REPOSITORIES_VL DomRepositories,
205 FND_LOOKUP_VALUES_VL lkp
206 WHERE lkp.lookup_type='DOM_REPOSITORY_PROTOCOLS'
207 AND lkp.lookup_code = DomRepositories.PROTOCOL
208 AND DomRepositories.ID <>0
209 ORDER BY id,DomRepositories.SEQUENCE;
210
211
212 BEGIN
213
214 FOR i IN Cur_WS_Rep
215 LOOP
216
217 l_protocol := i.PROTOCOL;
218
219 IF l_protocol = 'WEBSERVICES' THEN
220
221 l_ws_exist := TRUE;
222
223 END IF;
224
225 END LOOP;
226
227 RETURN l_ws_exist;
228
229 END Get_WebService_Repositories;
230
231 --Bug 9697013
232
233
234
235
236
237
238 ----------------------------------------------------------------------------
239 -- A. Add_OFO_Group_Member
240 ----------------------------------------------------------------------------
241
242 procedure Add_OFO_Group_Member (
243 p_api_version IN NUMBER,
244 p_init_msg_list IN VARCHAR2,
245 p_commit IN VARCHAR2,
246 p_group_id IN NUMBER,
247 p_member_id IN NUMBER,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2
251 ) IS
252 ------------------------------------------------------------------------
253 -- Start OF comments
254 -- API name : Add_OFO_Group_Member
255 -- TYPE : Public
256 -- Pre-reqs : None
257 -- FUNCTION : Add a member to the corresponding OFO Group.
258 --
259 --
260 -- Parameters:
261 -- IN : p_api_version IN NUMBER (required)
262 -- API Version of this procedure
263 -- p_init_msg_level IN VARCHAR2 (optional)
264 -- DEFAULT = FND_API.G_FALSE
265 -- Indicates whether the message stack needs to be cleared
266 -- p_commit IN VARCHAR2 (optional)
267 -- DEFAULT = FND_API.G_FALSE
268 -- Indicates whether the data should be committed
269 -- p_group_id IN NUMBER (required)
270 -- Group to which the member is being added
271 -- Eg., A Group
272 -- p_member_id IN VARCHAR2 (required)
273 -- Member which is to be added
274 -- Eg., PERSON
275 --
276 -- OUT : x_return_status OUT NUMBER
277 -- Result of all the operations
278 -- FND_API.G_RET_STS_SUCCESS if success
279 -- FND_API.G_RET_STS_ERROR if error
280 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
281 -- x_msg_count OUT NUMBER
282 -- number of messages in the message list
283 -- x_msg_data OUT VARCHAR2
284 -- if number of messages is 1, then this parameter
285 -- contains the message itself
286 --
287 -- Called From:
288 -- ego_party_pub.add_group_member
289 --
290 -- Version: Current Version 1.0
291 -- Previous Version : None
292 -- Notes :
293 --
294 -- END OF comments
295 ------------------------------------------------------------------------
296
297 l_Sysdate DATE := Sysdate;
298
299 l_api_name CONSTANT VARCHAR2(30) := 'Add_OFO_Group_Member';
300 -- On addition of any Required parameters the major version needs
301 -- to change i.e. for eg. 1.X to 2.X.
302 -- On addition of any Optional parameters the minor version needs
303 -- to change i.e. for eg. X.6 to X.7.
304 l_api_version CONSTANT NUMBER := 1.0;
305
306 -- General variables
307
308 l_success BOOLEAN; --boolean for descr. flex valiation
309
310 l_group_name VARCHAR2(100);
311 l_member_name VARCHAR2(100); --my wild assumed length
312 l_logged_in VARCHAR2(100);
313 l_name VARCHAR2(255);
314 l_value VARCHAR2(1023);
315 v_msg VARCHAR2(80);
316
317
318 l_param_query_string VARCHAR2(2000);
319
320 CURSOR user_info is
321 SELECT USER_NAME FROM fnd_user WHERE person_party_id = p_member_id;
322
323 BEGIN
324 -- Standard Start of API savepoint
325 mdebug('. ADD_OFO_GROUP_MEMBER: ADD_OFO_GROUP_MEMBER .....1...... ');
326 IF NOT FND_API.Compatible_API_Call (l_api_version,
327 p_api_version,
328 l_api_name,
329 G_PKG_NAME)
330 THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333 -- Initialize API message list if necessary.
334 -- Initialize message list if p_init_msg_list is set to TRUE.
335 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
336 FND_MSG_PUB.initialize;
337 END IF;
338
339 mdebug(' in DOM WS interface ' || p_group_id || ' ' || p_member_id);
340
341 SELECT GROUP_NAME INTO l_group_name FROM EGO_GROUPS_V WHERE GROUP_ID = p_group_id;
342
343 --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;
344 --SELECT USER_NAME INTO l_member_name FROM fnd_user WHERE person_party_id = p_member_id;
345 --SELECT USER_NAME INTO l_logged_in FROM FND_USER WHERE USER_ID = G_CURRENT_USER_ID;
346
347 FOR info_rec IN user_info LOOP
348 l_member_name := info_rec.USER_NAME;
349 SELECT us.user_name INTO l_logged_in
350 FROM hz_parties hz, fnd_user us
351 WHERE hz.created_by = us.USER_ID
352 AND party_id = p_group_id;
353
354
355 mdebug ( 'l_group_name : ' || l_group_name );
356 mdebug ( 'l_member_name : ' || l_member_name );
357 mdebug ( 'l_logged_in : ' || l_logged_in );
358
359 l_param_query_string:='opName=addUserToGroup&groupName=' || l_group_name || '&memberName=' || l_member_name || '&loggedInUser=' || l_logged_in;
360 call_dom_interface_jsp
361 (
362 p_param_query_string =>l_param_query_string
363 ,x_return_status =>x_return_status
364 ,x_msg_count =>x_msg_count
365 ,x_msg_data =>x_msg_data
366 );
367
368 END LOOP;
369
370 END Add_OFO_Group_Member;
371
372 --------------------------------------------------------------
373 procedure Update_Files_Document_Status (
374 p_api_version IN NUMBER,
375 p_service_url IN VARCHAR2,
376 p_document_id IN NUMBER,
377 p_status IN VARCHAR2,
378 p_login_user_name IN VARCHAR2,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2
382 )
383 IS
384
385 l_api_name CONSTANT VARCHAR2(30) := 'Update_Files_Document_Status';
386 -- On addition of any Required parameters the major version needs
387 -- to change i.e. for eg. 1.X to 2.X.
388 -- On addition of any Optional parameters the minor version needs
389 -- to change i.e. for eg. X.6 to X.7.
390 l_api_version CONSTANT NUMBER := 1.0;
391
392 -- General variables
393
394 l_success BOOLEAN;
395 l_param_query_string VARCHAR2(2000);
396
397 BEGIN
398 -- Standard Start of API savepoint
399 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)
400 THEN
401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402 END IF;
403
404
405 l_param_query_string:='opName=updateDocStatus&docId='||p_document_id||'&status='||p_status||'&serviceUrl='||p_service_Url||'&loggedInUser='||p_login_user_name;
406 call_dom_interface_jsp
407 (
408 p_param_query_string =>l_param_query_string
409 ,x_return_status =>x_return_status
410 ,x_msg_count =>x_msg_count
411 ,x_msg_data =>x_msg_data
412 );
413
414 END Update_Files_Document_Status;
415
416 --------------------------------------------------------------
417
418 procedure Grant_Attachments_OCSRole (
419 p_api_version IN NUMBER,
420 p_service_url IN VARCHAR2,
421 p_family_id IN NUMBER,
422 p_role IN VARCHAR2,
423 p_user_name IN VARCHAR2,
424 p_user_login IN VARCHAR2,
425 x_return_status OUT NOCOPY VARCHAR2,
426 x_msg_count OUT NOCOPY NUMBER,
427 x_msg_data OUT NOCOPY VARCHAR2
428 )
429 IS
430
431
432 l_api_name CONSTANT VARCHAR2(30) := 'Grant_Attachments_OCSRole';
433 -- On addition of any Required parameters the major version needs
434 -- to change i.e. for eg. 1.X to 2.X.
435 -- On addition of any Optional parameters the minor version needs
436 -- to change i.e. for eg. X.6 to X.7.
437 l_api_version CONSTANT NUMBER := 1.0;
438
439 -- General variables
440
441 l_success BOOLEAN;
442 l_param_query_string VARCHAR2(2000);
443
444 BEGIN
445
446 -- Standard Start of API savepoint
447 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)
448 THEN
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451
452 -- code_debug('YSIREESH: User id: '+FND_PROFILE.Value('USER_ID'));
453 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;
454 call_dom_interface_jsp
455 (
456 p_param_query_string =>l_param_query_string
457 ,x_return_status =>x_return_status
458 ,x_msg_count =>x_msg_count
459 ,x_msg_data =>x_msg_data
460 );
461
462 END Grant_Attachments_OCSRole;
463
464 procedure Remove_Attachments_OCSRole (
465 p_api_version IN NUMBER,
466 p_service_url IN VARCHAR2,
467 p_family_id IN NUMBER,
468 p_role IN VARCHAR2,
469 p_user_name IN VARCHAR2,
470 p_user_login IN VARCHAR2,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_msg_count OUT NOCOPY NUMBER,
473 x_msg_data OUT NOCOPY VARCHAR2
474 )
475 IS
476
477 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Attachments_OCSRole';
478 -- On addition of any Required parameters the major version needs
479 -- to change i.e. for eg. 1.X to 2.X.
480 -- On addition of any Optional parameters the minor version needs
481 -- to change i.e. for eg. X.6 to X.7.
482 l_api_version CONSTANT NUMBER := 1.0;
483
484 -- General variables
485
486 l_success BOOLEAN;
487 l_param_query_string VARCHAR2(2000);
488
489 BEGIN
490 -- Standard Start of API savepoint
491 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME)
492 THEN
493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
494 END IF;
495
496
497 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';
498 call_dom_interface_jsp
499 (
500 p_param_query_string =>l_param_query_string
501 ,x_return_status =>x_return_status
502 ,x_msg_count =>x_msg_count
503 ,x_msg_data =>x_msg_data
504 );
505
506 END Remove_Attachments_OCSRole;
507
508
509
510
511 END DOM_WS_INTERFACE_PUB;
[Home] [Help]