1 PACKAGE BODY EGO_PARTY_PUB AS
2 /*$Header: EGOPRTYB.pls 120.12 2010/06/18 20:49:18 atjen ship $ */
3
4 -- ------------------------------------------------------------
5 -- -------------- Global variables and constants --------------
6 -- ------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_PARTY_PUB';
8
9 -- refer to bug 2465636
10 -- G_OWNER_GROUP_REL_TYPE CONSTANT VARCHAR2(30) := 'EGO_GROUP_OWNERSHIP';
11 -- G_OWNER_GROUP_REL_CODE CONSTANT VARCHAR2(30) := 'OWNER_OF';
12
13 G_MEMBER_GROUP_REL_TYPE CONSTANT VARCHAR2(30) := 'MEMBERSHIP';
14 G_MEMBER_GROUP_REL_CODE CONSTANT VARCHAR2(30) := 'MEMBER_OF';
15
16 G_DEBUG_LEVEL_UNEXPECTED NUMBER;
17 G_DEBUG_LEVEL_ERROR NUMBER;
18 G_DEBUG_LEVEL_EXCEPTION NUMBER;
19 G_DEBUG_LEVEL_EVENT NUMBER;
20 G_DEBUG_LEVEL_PROCEDURE NUMBER;
21 G_DEBUG_LEVEL_STATEMENT NUMBER;
22 G_CURR_LOG_LEVEL NUMBER;
23 G_DEBUG_LOG_HEAD VARCHAR2(30);
24 -- ---------------------------------------------------------------------
25
26 --
27 -- write to debug into concurrent log
28 --
29 PROCEDURE code_debug (p_log_level IN NUMBER
30 ,p_module IN VARCHAR2
31 ,p_message IN VARCHAR2
32 ) IS
33 BEGIN
34 IF (p_log_level >= G_CURR_LOG_LEVEL) THEN
35 fnd_log.string(log_level => p_log_level
36 ,module => G_DEBUG_LOG_HEAD||p_module
37 ,message => p_message
38 );
39 END IF;
40 -- sri_debug(G_DEBUG_LOG_HEAD||p_module||' - '||p_message);
41 EXCEPTION
42 WHEN OTHERS THEN
43 RAISE;
44 END code_debug;
45
46 -- For debugging purposes.
47 PROCEDURE mdebug (msg IN varchar2) IS
48 BEGIN
49 -- dbms_output.put_line(msg);
50 null;
51 END mdebug;
52 -- ---------------------------------------------------------------------
53
54 ----------------------------------------------------------------------------
55 -- A. Create_Relationship
56 ----------------------------------------------------------------------------
57
58 procedure Create_Relationship (
59 p_api_version IN NUMBER,
60 p_init_msg_list IN VARCHAR2,
61 p_commit IN VARCHAR2,
62 p_subject_id IN NUMBER,
63 p_subject_type IN VARCHAR2,
64 p_subject_table_name IN VARCHAR2,
65 p_object_id IN NUMBER,
66 p_object_type IN VARCHAR2,
67 p_object_table_name IN VARCHAR2,
68 p_relationship_code IN VARCHAR2,
69 p_relationship_type IN VARCHAR2,
70 p_program_name IN VARCHAR2,
71 p_start_date IN DATE,
72 x_return_status OUT NOCOPY VARCHAR2,
73 x_msg_count OUT NOCOPY NUMBER,
74 x_msg_data OUT NOCOPY VARCHAR2,
75 x_relationship_id OUT NOCOPY NUMBER
76 ) IS
77 ------------------------------------------------------------------------
78 -- Start of comments
79 -- API name : Create_Relationship
80 -- TYPE : Private
81 -- Pre-reqs : None
82 -- FUNCTION : Create a Relationship between 2 Party Ids.
83 -- This will be used for Relationship creation in IPD
84 --
85 -- Parameters:
86 -- IN : p_api_version IN NUMBER (required)
87 -- API Version of this procedure
88 -- p_init_msg_level IN VARCHAR2 (optional)
89 -- DEFAULT = FND_API.G_FALSE
90 -- Indicates whether the message stack needs to be cleared
91 -- p_commit IN VARCHAR2 (optional)
92 -- DEFAULT = FND_API.G_FALSE
93 -- Indicates whether the data should be committed
94 -- p_subject_id IN NUMBER (required)
95 -- Subject on which the relationship needs to be created
96 -- Eg., A person
97 -- p_subject_type IN VARCHAR2 (required)
98 -- Type of the subject
99 -- Eg., PERSON
100 -- p_subject_table_name IN VARCHAR2 (required)
101 -- Table in which the subject is available
102 -- Eg., HZ_PARTIES
103 -- p_object_id IN NUMBER (required)
104 -- Object on which the relationship needs to be created
105 -- Eg., A group
106 -- p_object_type IN VARCHAR2 (required)
107 -- Type of the object
108 -- Eg., GROUP
109 -- p_object_table_name IN VARCHAR2 (required)
110 -- Table in which the object is available
111 -- Eg., HZ_PARTIES
112 -- p_relationship_code IN VARCHAR2 (required)
113 -- Current values are MEMBER_OF wrt subject
114 -- p_relationship_type IN VARCHAR2 := fnd_api.g_MISS_CHAR
115 -- Forward OR Backward. Default is Bi-directional
116 -- p_program_name IN VARCHAR2 (required)
117 -- Program name to identify the creator of the record
118 -- p_start_date IN DATE (required)
119 -- Record is valid from..
120 --
121 -- OUT : x_return_status OUT NUMBER
122 -- Result of all the operations
123 -- FND_API.G_RET_STS_SUCCESS if success
124 -- FND_API.G_RET_STS_ERROR if error
125 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
126 -- x_msg_count OUT NUMBER
127 -- number of messages in the message list
128 -- x_msg_data OUT VARCHAR2
129 -- if number of messages is 1, then this parameter
130 -- contains the message itself
131 -- x_relationship_id OUT NUMBER
132 -- Relationship_Id created between Group AND member
133 -- These valuee is stored at
134 -- hz_relationships.PARTY_RELATIONSHIP_ID
135 --
136 -- Called From:
137 -- ego_party_pub.create_group
138 -- ego_party_pub.add_group_member
139 --
140 -- Version: Current Version 1.0
141 -- Previous Version : None
142 -- Notes :
143 --
144 -- END OF comments
145 ------------------------------------------------------------------------
146
147 l_Sysdate DATE := Sysdate;
148
149 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RELATIONSHIP';
150 -- On addition of any Required parameters the major version needs
151 -- to change i.e. for eg. 1.X to 2.X.
152 -- On addition of any Optional parameters the minor version needs
153 -- to change i.e. for eg. X.6 to X.7.
154 l_api_version CONSTANT NUMBER := 1.0;
155
156 -- General variables
157 l_revision_id NUMBER;
158 l_success BOOLEAN; --boolean for descr. flex valiation
159 l_row_id VARCHAR2(50);
160
161 l_relationship_id NUMBER;
162 l_member_already_exists BOOLEAN := FALSE;
163
164 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
165
166 l_party_id NUMBER;
167 l_party_number VARCHAR2(500); --my wild assumed length
168
169 CURSOR member_already_exists (cp_subject_id IN NUMBER
170 ,cp_subject_table_name IN VARCHAR2
171 ,cp_object_id IN NUMBER
172 ,cp_object_table_name IN VARCHAR2
173 ,cp_relationship_code IN VARCHAR2) IS
174 SELECT relationship_id
175 FROM hz_relationships
176 WHERE subject_id = cp_subject_id
177 AND subject_type = cp_subject_table_name
178 AND object_id = cp_object_id
179 AND object_type = cp_object_table_name
180 AND relationship_code = cp_relationship_code
181 AND status = 'A'
182 AND SYSDATE BETWEEN start_date AND NVL(end_date, SYSDATE);
183
184 BEGIN
185 --
186 -- Check if the relation already exists
187 --
188 OPEN member_already_exists (cp_subject_id => p_subject_id
189 ,cp_subject_table_name => p_subject_table_name
190 ,cp_object_id => p_object_id
191 ,cp_object_table_name => p_object_table_name
192 ,cp_relationship_code => p_relationship_code
193 );
194 FETCH member_already_exists INTO l_relationship_id;
195 IF member_already_exists%FOUND THEN
196 l_member_already_exists := TRUE;
197 END IF;
198 CLOSE member_already_exists;
199
200 --
201 IF l_member_already_exists THEN
202 x_relationship_id := l_relationship_id;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 x_msg_count := 1;
205 fnd_message.set_name('EGO','EGO_RELATION_EXISTS');
206 fnd_msg_pub.add;
207 ELSE
208 -- Standard Start of API savepoint
209 IF FND_API.TO_BOOLEAN(p_commit) THEN
210 SAVEPOINT EGO_CREATE_RELATIONSHIP;
211 END IF;
212 mdebug('. CREATE_RELATIONSHIP: Creating Relationship .....1...... ');
213 IF NOT FND_API.Compatible_API_Call (l_api_version,
214 p_api_version,
215 l_api_name,
216 G_PKG_NAME)
217 THEN
218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219 END IF;
220 -- Initialize API message list if necessary.
221 -- Initialize message list if p_init_msg_list is set to TRUE.
222 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
223 FND_MSG_PUB.initialize;
224 END IF;
225
226 l_party_rel_rec.subject_id := p_subject_id;
227 l_party_rel_rec.subject_type := p_subject_type;
228 l_party_rel_rec.subject_table_name := p_subject_table_name;
229 l_party_rel_rec.object_id := p_object_id;
230 l_party_rel_rec.object_type := p_object_type;
231 l_party_rel_rec.object_table_name := p_object_table_name;
232 l_party_rel_rec.relationship_code := p_relationship_code;
233 l_party_rel_rec.relationship_type := nvl(p_relationship_type,chr(0));
234 l_party_rel_rec.created_by_module := p_program_name;
235 l_party_rel_rec.start_date := NVL(p_start_date, SYSDATE);
236
237 -- mdebug('. CREATE_RELATIONSHIP: Before calling HZ_RELATIONSHIP_V2PUB.create_relationship');
238 -- mdebug('. CREATE_RELATIONSHIP: params p_subject_id ' || to_char(p_subject_id) );
239 -- mdebug('. CREATE_RELATIONSHIP: p_subject_type ' || p_subject_type );
240 -- mdebug('. CREATE_RELATIONSHIP: p_subject_table_name ' || p_subject_table_name );
241 -- mdebug('. CREATE_RELATIONSHIP: p_object_id ' || to_char(p_object_id) );
242 -- mdebug('. CREATE_RELATIONSHIP: p_object_type ' || p_object_type );
243 -- mdebug('. CREATE_RELATIONSHIP: p_object_table_name ' || p_object_table_name );
244 -- mdebug('. CREATE_RELATIONSHIP: p_relationship_code ' || p_relationship_code );
245 -- mdebug('. CREATE_RELATIONSHIP: p_relationship_type ' || p_relationship_type );
246
247 HZ_RELATIONSHIP_V2PUB.create_relationship(
248 p_init_msg_list => NVL(p_init_msg_list, 'F'),
249 p_relationship_rec => l_party_rel_rec,
250 x_relationship_id => x_relationship_id,
251 x_party_id => l_party_id,
252 x_party_number => l_party_number,
253 x_return_status => x_return_status,
254 x_msg_count => x_msg_count,
255 x_msg_data => x_msg_data
256 );
257
258 -- mdebug('. CREATE_RELATIONSHIP: Exited from HZ_RELATIONSHIP_V2PUB.create_relationship');
259 -- mdebug('. CREATE_RELATIONSHIP: party_rel_id '|| to_char(x_relationship_id));
260 -- mdebug('. CREATE_RELATIONSHIP: party_id '|| to_char(l_party_id));
261 -- mdebug('. CREATE_RELATIONSHIP: x_party_number '|| l_party_number);
262 -- mdebug('. CREATE_RELATIONSHIP: return_status '|| x_return_status);
263 -- mdebug('. CREATE_RELATIONSHIP: x_msg_data ' || x_msg_data);
264 -- mdebug('. CREATE_RELATIONSHIP: x_msg_count ' || x_msg_count);
265
266 END IF; -- member already exists
267 -- Standard check of p_commit.
268 IF FND_API.To_Boolean( NVL(p_commit, 'F') ) THEN
269 COMMIT WORK;
270 END IF;
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272 -- Standard call to get message count and if count is 1,
273 -- get message info.
274 -- The client will directly display the x_msg_data (which is already
275 -- translated) if the x_msg_count = 1;
276 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
277 -- Server-side procedure to access the messages, and consolidate them
278 -- and display (or) to display one message after another.
279 mdebug('. CREATE_RELATIONSHIP: Tracing....');
280 FND_MSG_PUB.Count_And_Get
281 ( p_count => x_msg_count,
282 p_data => x_msg_data
283 );
284 EXCEPTION
285 WHEN FND_API.G_EXC_ERROR THEN
286 IF FND_API.TO_BOOLEAN(p_commit) THEN
287 ROLLBACK TO EGO_CREATE_RELATIONSHIP;
288 END IF;
289 mdebug('. CREATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_ERROR''');
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 FND_MSG_PUB.Count_And_Get
292 (p_count => x_msg_count,
293 p_data => x_msg_data
294 );
295 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
296 IF FND_API.TO_BOOLEAN(p_commit) THEN
297 ROLLBACK TO EGO_CREATE_RELATIONSHIP;
298 END IF;
299 mdebug('. CREATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR''');
300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
301 FND_MSG_PUB.Count_And_Get
302 (p_count => x_msg_count,
303 p_data => x_msg_data
304 );
305 WHEN OTHERS THEN
306 IF FND_API.TO_BOOLEAN(p_commit) THEN
307 ROLLBACK TO EGO_CREATE_RELATIONSHIP;
308 END IF;
309 mdebug('. CREATE_RELATIONSHIP: Ending : Returning UNEXPECTED ERROR');
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
311 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
312 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
313 END IF;
314 FND_MSG_PUB.Count_And_Get
315 (p_count => x_msg_count,
316 p_data => x_msg_data
317 );
318 END Create_Relationship;
319
320 ----------------------------------------------------------------------------
321 -- B. Update_Relationship
322 ----------------------------------------------------------------------------
323 procedure Update_Relationship (
324 p_api_version IN NUMBER,
325 p_init_msg_list IN VARCHAR2,
326 p_commit IN VARCHAR2,
327 p_party_rel_rec IN HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
328 p_object_version_no_rel IN OUT NOCOPY NUMBER,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2
332 ) IS
333 ------------------------------------------------------------------------
334 -- Start of comments
335 -- API name : Update_Relationship
336 -- TYPE : Private
337 -- Pre-reqs : An existing Relationship
338 -- FUNCTION : Update a Relationship between 2 Party Ids.
339 --
340 -- Parameters:
341 -- IN : p_api_version IN NUMBER (required)
342 -- API Version of this procedure
343 -- p_init_msg_level IN VARCHAR2 (optional)
344 -- DEFAULT = FND_API.G_FALSE
345 -- Indicates whether the message stack needs to be cleared
346 -- p_commit IN VARCHAR2 (optional)
347 -- DEFAULT = FND_API.G_FALSE
348 -- Indicates whether the data should be committed
349 -- p_party_rel_rec IN NUMBER (required)
350 -- The party relation record that needs to be updated
351 -- Record type -> HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE
352 --
353 -- IN/OUT : p_object_version_no_rel IN OUT NUMBER (required)
354 -- Takes in the version of the record to be updated
355 -- Returns the version of the record after updation
356 --
357 -- OUT : x_return_status OUT NUMBER
358 -- Result of all the operations
359 -- FND_API.G_RET_STS_SUCCESS if success
360 -- FND_API.G_RET_STS_ERROR if error
361 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
362 -- x_msg_count OUT NUMBER
363 -- number of messages in the message list
364 -- x_msg_data OUT VARCHAR2
365 -- if number of messages is 1, then this parameter
366 -- contains the message itself
367 --
368 -- Called From:
369 -- ego_party_pub.update_group
370 -- ego_party_pub.remove_group_member
371 --
372 -- Version: Current Version 1.0
373 -- Previous Version : None
374 -- Notes :
375 --
376 -- END OF comments
377 ------------------------------------------------------------------------
378
379 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RELATIONSHIP';
380 -- On addition of any Required parameters the major version needs
381 -- to change i.e. for eg. 1.X to 2.X.
382 -- On addition of any Optional parameters the minor version needs
383 -- to change i.e. for eg. X.6 to X.7.
384 l_api_version CONSTANT NUMBER := 1.0;
385
386 l_number NUMBER ; -- Fix For Bug 2835026
387
388 BEGIN
389 -- Standard Start of API savepoint
390 IF FND_API.TO_BOOLEAN(p_commit) THEN
391 SAVEPOINT EGO_UPDATE_RELATIONSHIP;
392 END IF;
393 l_number := FND_API.G_MISS_NUM ; -- Fix For Bug 2835026
394
395 IF NOT FND_API.Compatible_API_Call (l_api_version,
396 p_api_version,
397 l_api_name,
398 G_PKG_NAME)
399 THEN
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402
403 -- Initialize API message list if necessary.
404 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
405 FND_MSG_PUB.initialize;
406 END IF;
407
408 HZ_RELATIONSHIP_V2PUB.update_relationship
409 (p_init_msg_list => NVL(p_init_msg_list, 'F')
410 ,p_relationship_rec => p_party_rel_rec
411 ,p_object_version_number => p_object_version_no_rel
412 ,p_party_object_version_number => l_number
413 ,x_return_status => x_return_status
414 ,x_msg_count => x_msg_count
415 ,x_msg_data => x_msg_data
416 );
417
418 mdebug('. UPDATE_RELATIONSHIP: Succesfully updated the relationship ');
419 mdebug('. UPDATE_RELATIONSHIP: return_status '|| x_return_status);
420 mdebug('. UPDATE_RELATIONSHIP: x_msg_data ' || x_msg_data);
421
422 -- Standard check of p_commit.
423 IF FND_API.To_Boolean( NVL(p_commit, 'F') ) THEN
424 COMMIT WORK;
425 END IF;
426
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428 -- Standard call to get message count and if count is 1,
429 -- get message info.
430 -- The client will directly display the x_msg_data (which is already
431 -- translated) if the x_msg_count = 1;
432 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
433 -- Server-side procedure to access the messages, and consolidate them
434 -- and display (or) to display one message after another.
435 mdebug('. UPDATE_RELATIONSHIP: Tracing....');
436
437 FND_MSG_PUB.Count_And_Get
438 ( p_count => x_msg_count,
439 p_data => x_msg_data
440 );
441
442 EXCEPTION
443 WHEN FND_API.G_EXC_ERROR THEN
444 IF FND_API.TO_BOOLEAN(p_commit) THEN
445 ROLLBACK TO EGO_UPDATE_RELATIONSHIP;
446 END IF;
447 mdebug('. UPDATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_ERROR''');
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 FND_MSG_PUB.Count_And_Get
450 (p_count => x_msg_count,
451 p_data => x_msg_data
452 );
453 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
454 IF FND_API.TO_BOOLEAN(p_commit) THEN
455 ROLLBACK TO EGO_UPDATE_RELATIONSHIP;
456 END IF;
457 mdebug('. UPDATE_RELATIONSHIP: Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR''');
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
459 FND_MSG_PUB.Count_And_Get
460 (p_count => x_msg_count,
461 p_data => x_msg_data
462 );
463 WHEN OTHERS THEN
464 IF FND_API.TO_BOOLEAN(p_commit) THEN
465 ROLLBACK TO EGO_UPDATE_RELATIONSHIP;
466 END IF;
467 mdebug('. UPDATE_RELATIONSHIP: Ending : Returning UNEXPECTED ERROR');
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
469 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
470 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
471 END IF;
472 FND_MSG_PUB.Count_And_Get
473 (p_count => x_msg_count,
474 p_data => x_msg_data
475 );
476 END Update_Relationship;
477
478
479 PROCEDURE SetGlobals IS
480 BEGIN
481 --
482 -- debug parameter constants
483 --
484 G_DEBUG_LEVEL_UNEXPECTED := FND_LOG.LEVEL_UNEXPECTED;
485 G_DEBUG_LEVEL_ERROR := FND_LOG.LEVEL_ERROR;
486 G_DEBUG_LEVEL_EXCEPTION := FND_LOG.LEVEL_EXCEPTION;
487 G_DEBUG_LEVEL_EVENT := FND_LOG.LEVEL_EVENT;
488 G_DEBUG_LEVEL_PROCEDURE := FND_LOG.LEVEL_PROCEDURE;
489 G_DEBUG_LEVEL_STATEMENT := FND_LOG.LEVEL_STATEMENT;
490 G_DEBUG_LOG_HEAD := 'fnd.plsql.ego.'||G_PKG_NAME||'.';
491 G_CURR_LOG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 code_debug (p_log_level => G_DEBUG_LEVEL_UNEXPECTED
496 ,p_module => 'SetGlobals'
497 ,p_message => 'Unable to intialize Globals'
498 );
499 END SetGlobals;
500
501 --
502
503
504 ----------------------------------------------------------------------------
505 -- 0. Get_Application_id
506 ----------------------------------------------------------------------------
507 FUNCTION get_application_id RETURN NUMBER IS
508 ------------------------------------------------------------------------
509 -- Start of comments
510 -- API name : Create_Group
511 -- TYPE : Public
512 -- Pre-reqs : None
513 -- FUNCTION : Gets the application id of Engineering Groups
514 -- Appliation short name = 'EGO'
515 --
516 -- Parameters:
517 -- IN : NONE
518 --
519 -- OUT : NONE
520 --
521 -- Version: Current Version 1.0
522 -- Previous Version : None
523 -- Notes :
524 --
525 -- END OF comments
526 ------------------------------------------------------------------------
527
528 l_application_id fnd_application.application_id%TYPE;
529
530 CURSOR get_ego_application_id IS
531 SELECT application_id
532 FROM fnd_application
533 WHERE application_short_name = 'EGO';
534
535 BEGIN
536
537 OPEN get_ego_application_id;
538 FETCH get_ego_application_id INTO l_application_id;
539 IF get_ego_application_id%NOTFOUND THEN
540 l_application_id := -1;
541 END IF;
542 CLOSE get_ego_application_id;
543 RETURN l_application_id;
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 IF get_ego_application_id%ISOPEN THEN
548 CLOSE get_ego_application_id;
549 END IF;
550
551 END get_application_id;
552
553
554 ----------------------------------------------------------------------------
555 -- 1. Create_Group
556 ----------------------------------------------------------------------------
557 procedure Create_Group (
558 p_api_version IN NUMBER,
559 p_init_msg_list IN VARCHAR2,
560 p_commit IN VARCHAR2,
561 p_group_name IN VARCHAR2,
562 p_group_type IN VARCHAR2,
563 p_description IN VARCHAR2,
564 p_email_address IN VARCHAR2,
565 p_creator_person_id IN NUMBER,
566 x_return_status OUT NOCOPY VARCHAR2,
567 x_msg_count OUT NOCOPY NUMBER,
568 x_msg_data OUT NOCOPY VARCHAR2,
569 x_group_id OUT NOCOPY NUMBER
570 ) IS
571 ------------------------------------------------------------------------
572 -- Start of comments
573 -- API name : CREATE_GROUP
574 -- TYPE : Public
575 -- Pre-reqs : None
576 -- FUNCTION : Create a Group.
577 -- Creates a record into HZ_PARTIES with party_type = 'GROUP'
578 -- Creates the requestor as a member of the GROUP
579 -- (two way relationship -- MEMBER_OF and CONTAINS_MEMBER)
580 --
581 -- x_group_id OUT NUMBER
582 -- new Group_Id that has been created.
583 --
584 -- Version: Current Version 1.0
585 -- Previous Version : None
586 -- Notes :
587 --
588 -- END OF comments
589 ------------------------------------------------------------------------
590
591 --local variables
592 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_GROUP';
593 -- On addition of any Required parameters the major version needs
594 -- to change i.e. for eg. 1.X to 2.X.
595 -- On addition of any Optional parameters the minor version needs
596 -- to change i.e. for eg. X.6 to X.7.
597 l_api_version CONSTANT NUMBER := 1.0;
598
599 l_group_id NUMBER;
600 l_group_number VARCHAR2(500); --my wild assumed length
601
602 l_group_rec HZ_PARTY_V2PUB.GROUP_REC_TYPE;
603 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
604
605 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
606 l_edi_rec HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
607 l_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
608 l_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
609 l_telex_rec HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
610 l_web_rec HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
611 l_contact_point_id NUMBER;
612
613 l_group_owner_rel_id NUMBER;
614 l_group_member_rel_id NUMBER;
615
616 BEGIN
617 -- check if all required parameters are passed to the procedure
618 IF (p_api_version IS NULL
619 OR p_group_name IS NULL
620 OR p_group_name IS NULL
621 OR p_group_type IS NULL
622 OR p_creator_person_id IS NULL
623 ) THEN
624 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
625 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
626 fnd_msg_pub.add;
627 x_return_status := FND_API.G_RET_STS_ERROR;
628 x_msg_count := 1;
629 fnd_msg_pub.Count_And_Get
630 (p_count => x_msg_count
631 ,p_data => x_msg_data
632 );
633 RETURN;
634 END IF;
635 -- Standard Start of API savepoint
636 IF FND_API.TO_BOOLEAN(p_commit) THEN
637 SAVEPOINT EGO_CREATE_GROUP;
638 END IF;
639
640 mdebug('CREATE_GROUP: ....1....');
641 --
642 -- checking if the caller is calling with correct name and version
643 --
644 IF NOT FND_API.Compatible_API_Call (l_api_version,
645 p_api_version,
646 l_api_name,
647 G_PKG_NAME)
648 THEN
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END IF;
651
652 -- Initialize API message list if necessary.
653 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
654 FND_MSG_PUB.initialize;
655 END IF;
656
657 l_group_rec.group_name := p_group_name;
658
659 IF ( p_group_type IS NULL OR p_group_type = fnd_api.g_MISS_CHAR ) THEN
660 l_group_rec.group_type := 'GROUP';
661 ELSE
662 l_group_rec.group_type := p_group_type;
663 END IF;
664 ---------------------------------------------------------------------
665 -- INFORMATION REGARDING USING FND_API.G_MISS_CHAR
666 -- while inserting data the following code is used by API
667 -- DECODE( X_LOCATION, FND_API.G_MISS_CHAR, NULL, X_LOCATION)
668 ---------------------------------------------------------------------
669 --
670 -- getting the application id
671 --
672 l_group_rec.application_id := EGO_PARTY_PUB.get_application_id;
673 l_group_rec.created_by_module := CREATED_BY_MODULE;
674
675 l_group_rec.party_rec := l_party_rec;
676 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER','Y');
677 mdebug('CREATE_GROUP Before calling HZ_PARTY_V2PUB.create_group');
678 HZ_PARTY_V2PUB.create_group
679 (p_init_msg_list => NVL(p_init_msg_list, 'F')
680 ,p_group_rec => l_group_rec
681 ,x_party_id => l_group_id
682 ,x_party_number => l_group_number
683 ,x_return_status => x_return_status
684 ,x_msg_count => x_msg_count
685 ,x_msg_data => x_msg_data
686 );
687 mdebug('CREATE_GROUP: HZ_PARTY_V2PUB.create_group call complete : groupId => '||l_group_id);
688 mdebug('CREATE_GROUP: return_status '|| x_return_status);
689
690 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
691 IF FND_API.TO_BOOLEAN(p_commit) THEN
692 ROLLBACK TO EGO_CREATE_GROUP;
693 END IF;
694 RETURN;
695 ELSE
696 --
697 -- l_group_rec doesnt have mission_statement as its attribute.
698 -- Refer to BUG 2467872
699 --
700 UPDATE hz_parties
701 SET mission_statement = p_description
702 WHERE party_id = l_group_id;
703 END IF;
704 --
705 -- inserting the email address for the group
706 --
707 IF p_email_address IS NOT NULL THEN
708 l_contact_point_rec.contact_point_type := 'EMAIL';
709 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
710 l_contact_point_rec.owner_table_id := l_group_id;
711 l_contact_point_rec.created_by_module := CREATED_BY_MODULE;
712 l_contact_point_rec.application_id := EGO_PARTY_PUB.get_application_id;
713 l_email_rec.email_address := p_email_address;
714
715 mdebug(' Before calling HZ_CONTACT_POINT_V2PUB.create_contact_point');
716 HZ_CONTACT_POINT_V2PUB.create_contact_point
717 (p_init_msg_list => NVL(p_init_msg_list, 'F')
718 ,p_contact_point_rec => l_contact_point_rec
719 ,p_edi_rec => l_edi_rec
720 ,p_email_rec => l_email_rec
721 ,p_phone_rec => l_phone_rec
722 ,p_telex_rec => l_telex_rec
723 ,p_web_rec => l_web_rec
724 ,x_contact_point_id => l_contact_point_id
725 ,x_return_status => x_return_status
726 ,x_msg_count => x_msg_count
727 ,x_msg_data => x_msg_data
728 );
729 mdebug('CREATE_GROUP: Returning after call to create_contact_point => '|| to_char(l_contact_point_id));
730 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
731 IF FND_API.TO_BOOLEAN(p_commit) THEN
732 ROLLBACK TO EGO_CREATE_GROUP;
733 END IF;
734 RETURN;
735 END IF;
736 ELSE
737 mdebug('CREATE_GROUP: No need to call HZ_CONTACT_POINT_V2PUB.create_contact_point');
738 END IF;
739 -- The concept of creating an owner no more exists
740 -- we are having the concept of Administrator
741 -- which are done using specific grants
742 -- --
743 -- -- A group has an Owner.
744 -- -- This relation should be created in hz_party_relationships
745 -- --
746 -- mdebug('CREATE_GROUP: Before calling create_relationship for Owner ');
747 -- l_group_owner_rel_id := NULL;
748 -- create_relationship(
749 -- p_api_version => 1.0,
750 -- p_init_msg_list => NVL(p_init_msg_list, 'F'),
751 -- p_commit => NVL(p_commit, 'F'),
752 -- p_subject_id => p_owner_person_id,
753 -- p_subject_type => 'PERSON',
754 -- p_subject_table_name => 'HZ_PARTIES',
755 -- p_object_id => l_group_id,
756 -- p_object_type => 'GROUP',
757 -- p_object_table_name => 'HZ_PARTIES',
758 -- p_relationship_code => G_OWNER_GROUP_REL_CODE,
759 -- p_relationship_type => G_OWNER_GROUP_REL_TYPE,
760 -- p_program_name => G_PKG_NAME,
761 -- p_start_date => SYSDATE,
762 -- x_return_status => x_return_status,
763 -- x_msg_count => x_msg_count,
764 -- x_msg_data => x_msg_data,
765 -- x_relationship_id => l_group_owner_rel_id
766 -- );
767 --
768 -- -- Output commands to test if Group successfully created.
769 -- mdebug('CREATE_GROUP: created owner for the group');
770 -- mdebug('CREATE_GROUP: group_owner_rel_id '|| to_char(l_group_owner_rel_id));
771 mdebug('CREATE_GROUP: return_status '|| x_return_status);
772 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
773 IF FND_API.TO_BOOLEAN(p_commit) THEN
774 ROLLBACK TO EGO_CREATE_GROUP;
775 END IF;
776 RETURN;
777 END IF;
778 --
779 -- Owner is a default member of the group he created, hence create
780 -- a MEMBER_OF relationship between the owner and group.
781 --
782 -- All the members for Group are created with start_date as Sysdate
783 -- and end_date as NULL (i.e. do not expire membership)
784 --
785 mdebug('CREATE_GROUP: Before calling Add_Group_Member');
786
787 Add_Group_Member(
788 p_api_version => 1.0,
789 p_init_msg_list => NVL(p_init_msg_list, 'F'),
790 p_commit => NVL(p_commit, 'F'),
791 p_member_id => p_creator_person_id,
792 p_group_id => l_group_id,
793 p_start_date => SYSDATE,
794 p_end_date => NULL,
795 x_return_status => x_return_status,
796 x_msg_count => x_msg_count,
797 x_msg_data => x_msg_data,
798 x_relationship_id => l_group_member_rel_id
799 );
800
801 mdebug('CREATE_GROUP: Successfully exited from Add_Group_Member');
802 mdebug('CREATE_GROUP: group_member_rel_id '|| to_char(l_group_member_rel_id));
803 mdebug('CREATE_GROUP: return status '|| x_return_status );
804 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
805 IF FND_API.TO_BOOLEAN(p_commit) THEN
806 ROLLBACK TO EGO_CREATE_GROUP;
807 END IF;
808 RETURN;
809 END IF;
810 -- before returning to the caller, set appropriate OUT values
811 x_group_id := l_group_id;
812 x_return_status := FND_API.G_RET_STS_SUCCESS;
813 -- Standard call to get message count and if count is 1,
814 -- get message info.
815 -- The client will directly display the x_msg_data (which is already
816 -- translated) if the x_msg_count = 1;
817 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
818 -- Server-side procedure to access the messages, and consolidate them
819 -- and display (or) to display one message after another.
820 mdebug('CREATE_GROUP: Tracing....');
821
822 FND_MSG_PUB.Count_And_Get
823 ( p_count => x_msg_count,
824 p_data => x_msg_data
825 );
826
827 EXCEPTION
828 WHEN FND_API.G_EXC_ERROR THEN
829 IF FND_API.TO_BOOLEAN(p_commit) THEN
830 ROLLBACK TO EGO_CREATE_GROUP;
831 END IF;
832 mdebug('CREATE_GROUP: Ending - Returning ''FND_API.G_EXC_ERROR''');
833 x_return_status := FND_API.G_RET_STS_ERROR;
834 FND_MSG_PUB.Count_And_Get
835 (p_count => x_msg_count,
836 p_data => x_msg_data
837 );
838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
839 IF FND_API.TO_BOOLEAN(p_commit) THEN
840 ROLLBACK TO EGO_CREATE_GROUP;
841 END IF;
842 mdebug('CREATE_GROUP: Ending - Returning ''FND_API.G_EXC_UNEXPECTED_ERROR''');
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
844 FND_MSG_PUB.Count_And_Get
845 ( p_count => x_msg_count,
846 p_data => x_msg_data
847 );
848 WHEN OTHERS THEN
849 IF FND_API.TO_BOOLEAN(p_commit) THEN
850 ROLLBACK TO EGO_CREATE_GROUP;
851 END IF;
852 mdebug('CREATE_GROUP: Ending - Returning UNEXPECTED ERROR');
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
854 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
855 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
856 END IF;
857 FND_MSG_PUB.Count_And_Get
858 (p_count => x_msg_count,
859 p_data => x_msg_data
860 );
861
862 END Create_Group;
863
864 ----------------------------------------------------------------------------
865 -- 2. Update_Group
866 ----------------------------------------------------------------------------
867 procedure Update_Group (
868 p_api_version IN NUMBER,
869 p_init_msg_list IN VARCHAR2,
870 p_commit IN VARCHAR2,
871 p_group_id IN NUMBER,
872 p_group_name IN VARCHAR2,
873 p_description IN VARCHAR2,
874 p_email_address IN VARCHAR2,
875 -- p_owner_person_id IN NUMBER,
876 p_object_version_no_group IN OUT NOCOPY NUMBER,
877 --p_object_version_no_owner_rel IN OUT NOCOPY NUMBER,
878 x_return_status OUT NOCOPY VARCHAR2,
879 x_msg_count OUT NOCOPY NUMBER,
880 x_msg_data OUT NOCOPY VARCHAR2
881 ) IS
882 ------------------------------------------------------------------------
883 -- Start of comments
884 -- API name : Update_Group
885 -- TYPE : Public
886 -- Pre-reqs : None
887 -- FUNCTION : Update a Group.
888 -- p_object_version_number is a mandatory field used to check
889 -- whether the record is updated after query
890 -- Looks for the following relationships
891 -- If the Group Owner has changed
892 -- update the owner relationship record
893 -- If the new Group Owner is not a member
894 -- create a new member record
895 -- If this operation fails then the category is not
896 -- created and error code is returned.
897 --
898 -- Version: Current Version 1.0
899 -- Previous Version : None
900 -- Notes :
901 --
902 -- END OF comments
903 ------------------------------------------------------------------------
904
905 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUP';
906 -- On addition of any Required parameters the major version needs
907 -- to change i.e. for eg. 1.X to 2.X.
908 -- On addition of any Optional parameters the minor version needs
909 -- to change i.e. for eg. X.6 to X.7.
910 l_api_version CONSTANT NUMBER := 1.0;
911
912 -- General variables
913 l_group_rec HZ_PARTY_V2PUB.GROUP_REC_TYPE;
914 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
915 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
916
917 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
918 l_edi_rec HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
919 l_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
920 l_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
921 l_telex_rec HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
922 l_web_rec HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
923
924 l_curr_owner_id NUMBER;
925 l_curr_member_id NUMBER;
926 l_update_owner BOOLEAN;
927 l_create_member BOOLEAN;
928 l_group_member_rel_id NUMBER;
929 l_group_owner_rel_id NUMBER;
930 l_object_version_no_owner NUMBER;
931 l_contact_point_id NUMBER;
932 l_email_address VARCHAR2(2000);
933 l_object_version_no_contact NUMBER;
934
935 l_status hz_contact_points.status%TYPE;
936 l_email_format hz_contact_points.email_format%TYPE;
937
938 -- To store last Modified Date
939 l_Sysdate DATE := Sysdate;
940 l_last_update_date DATE;
941 l_return_status VARCHAR2(1);
942 l_msg_count NUMBER;
943 l_msg_data VARCHAR2(2000);
944 l_relationship_id NUMBER;
945 l_member_found BOOLEAN := FALSE;
946 l_grp_member_id NUMBER;
947
948 -- CURSOR c_get_owner_details (cp_group_id IN NUMBER) IS
949 -- SELECT subject_id, relationship_id, object_version_number
950 -- FROM hz_relationships
951 -- WHERE object_id = cp_group_id
952 -- AND object_type = 'GROUP'
953 -- AND subject_type = 'PERSON'
954 -- AND relationship_type = G_OWNER_GROUP_REL_TYPE
955 -- AND status = 'A'
956 -- AND SYSDATE between start_date and NVL(end_date,SYSDATE);
957
958 CURSOR c_is_group_member(cp_group_id IN NUMBER
959 ,cp_member_id IN NUMBER) IS
960 SELECT subject_id
961 FROM hz_relationships
962 WHERE object_id = cp_group_id
963 AND object_type = 'GROUP'
964 AND subject_id = cp_member_id
965 AND subject_type = 'PERSON'
966 AND relationship_type = G_MEMBER_GROUP_REL_TYPE
967 AND status = 'A'
968 AND SYSDATE between start_date and NVL(end_date,SYSDATE);
969
970 CURSOR c_get_contact_details (cp_group_id IN NUMBER) IS
971 SELECT contact_point_id, object_version_number, email_address, status, email_format
972 FROM hz_contact_points
973 WHERE owner_table_id = cp_group_id
974 AND owner_table_name = 'HZ_PARTIES'
975 AND status = 'A';
976
977 BEGIN
978 -- check if all required parameters are passed to the procedure
979 mdebug('UPDATE_GROUP: ....1....... ');
980 IF (p_api_version IS NULL
981 OR p_group_id IS NULL
982 -- OR p_owner_person_id IS NULL
983 OR p_object_version_no_group IS NULL
984 -- OR p_object_version_no_owner_rel IS NULL
985 ) THEN
986 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
987 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
988 fnd_msg_pub.add;
989 x_return_status := FND_API.G_RET_STS_ERROR;
990 x_msg_count := 1;
991 fnd_msg_pub.Count_And_Get
992 (p_count => x_msg_count
993 ,p_data => x_msg_data
994 );
995 RETURN;
996 END IF;
997 mdebug('UPDATE_GROUP: All required params are passed ');
998 -- Standard Start of API savepoint
999 IF FND_API.TO_BOOLEAN(p_commit) THEN
1000 SAVEPOINT EGO_UPDATE_GROUP;
1001 END IF;
1002
1003 IF NOT FND_API.Compatible_API_Call (l_api_version,
1004 p_api_version,
1005 l_api_name,
1006 G_PKG_NAME)
1007 THEN
1008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1009 END IF;
1010
1011 -- Initialize API message list if necessary.
1012 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
1013 FND_MSG_PUB.initialize;
1014 END IF;
1015
1016 l_group_rec.party_rec.party_id := p_group_id;
1017 l_group_rec.group_name := nvl(p_group_name,chr(0));
1018
1019 mdebug('UPDATE_GROUP: Before calling HZ_PARTY_V2PUB.update_group');
1020 -- update the basic information in the group
1021 HZ_PARTY_V2PUB.update_group
1022 (p_init_msg_list => NVL(p_init_msg_list, 'F')
1023 ,p_group_rec => l_group_rec
1024 ,p_party_object_version_number => p_object_version_no_group
1025 ,x_return_status => l_return_status
1026 ,x_msg_count => l_msg_count
1027 ,x_msg_data => l_msg_data
1028 );
1029 mdebug('UPDATE_GROUP: Existed out of HZ_PARTY_V2PUB.update_group with status '''|| l_return_status||'''');
1030 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1031 IF FND_API.TO_BOOLEAN(p_commit) THEN
1032 ROLLBACK TO EGO_UPDATE_GROUP;
1033 END IF;
1034 RETURN;
1035 ELSE
1036 -- in TCA the update description not done,
1037 -- Refer to BUG 2467872
1038 UPDATE hz_parties
1039 SET mission_statement = p_description
1040 WHERE party_id = p_group_id;
1041 END IF;
1042
1043 OPEN c_get_contact_details (cp_group_id => p_group_id);
1044 FETCH c_get_contact_details
1045 INTO l_contact_point_id, l_object_version_no_contact, l_email_address,
1046 l_status, l_email_format;
1047 IF c_get_contact_details%NOTFOUND THEN
1048 l_email_address := NULL;
1049 END IF;
1050 CLOSE c_get_contact_details;
1051
1052 IF l_email_address IS NULL THEN
1053 -- no record created earlier
1054 IF p_email_address IS NULL THEN
1055 -- do not create any records into HZ_CONTACT_POINTS
1056 mdebug('UPDATE_GROUP: No need to create Contact Point during update of Group');
1057 ELSE
1058 -- contact point required. Need to create one.
1059 l_contact_point_rec.contact_point_type := 'EMAIL';
1060 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
1061 l_contact_point_rec.owner_table_id := p_group_id;
1062 l_contact_point_rec.created_by_module := CREATED_BY_MODULE;
1063 l_contact_point_rec.application_id := EGO_PARTY_PUB.get_application_id;
1064 l_email_rec.email_address := p_email_address;
1065 mdebug(' UPDATE_GROUP: Creating Contact point now !!! ');
1066 HZ_CONTACT_POINT_V2PUB.create_contact_point
1067 (p_init_msg_list => NVL(p_init_msg_list, 'F')
1068 ,p_contact_point_rec => l_contact_point_rec
1069 ,p_edi_rec => l_edi_rec
1070 ,p_email_rec => l_email_rec
1071 ,p_phone_rec => l_phone_rec
1072 ,p_telex_rec => l_telex_rec
1073 ,p_web_rec => l_web_rec
1074 ,x_contact_point_id => l_contact_point_id
1075 ,x_return_status => x_return_status
1076 ,x_msg_count => x_msg_count
1077 ,x_msg_data => x_msg_data
1078 );
1079 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1080 IF FND_API.TO_BOOLEAN(p_commit) THEN
1081 ROLLBACK TO EGO_UPDATE_GROUP;
1082 END IF;
1083 RETURN;
1084 END IF;
1085 END IF;
1086 ELSE
1087 IF p_email_address IS NULL THEN
1088 -- the existing contact point needs to be removed.
1089 mdebug('UPDATE_GROUP: Deleted the existing contact point');
1090 l_contact_point_rec.primary_flag := 'N';
1091 l_email_rec.email_address := l_email_address;
1092 l_contact_point_rec.status := 'I';
1093 ELSE
1094 -- update email address in contact_points
1095 mdebug('UPDATE_GROUP: before calling HZ_CONTACT_POINT_V2PUB.update_contact_point');
1096 l_email_rec.email_address := p_email_address;
1097 l_contact_point_rec.status := l_status;
1098 END IF;
1099 l_email_rec.email_format := l_email_format;
1100 l_contact_point_rec.contact_point_id := l_contact_point_id;
1101 l_contact_point_rec.contact_point_type := 'EMAIL';
1102 HZ_CONTACT_POINT_V2PUB.update_contact_point
1103 (p_init_msg_list => NVL(p_init_msg_list, 'F')
1104 ,p_contact_point_rec => l_contact_point_rec
1105 ,p_edi_rec => l_edi_rec
1106 ,p_email_rec => l_email_rec
1107 ,p_phone_rec => l_phone_rec
1108 ,p_telex_rec => l_telex_rec
1109 ,p_web_rec => l_web_rec
1110 ,p_object_version_number => l_object_version_no_contact
1111 ,x_return_status => l_return_status
1112 ,x_msg_count => l_msg_count
1113 ,x_msg_data => l_msg_data
1114 );
1115 mdebug('UPDATE_GROUP: Exited from HZ_CONTACT_POINT_V2PUB.update_contact_point with status '''||l_return_status||'''');
1116 IF l_return_status <> 'S' THEN
1117 IF FND_API.TO_BOOLEAN(p_commit) THEN
1118 ROLLBACK TO EGO_UPDATE_GROUP;
1119 END IF;
1120 RETURN;
1121 END IF;
1122 END IF;
1123 -- Commented out for 11.5.9 enh
1124 -- collect all relavent information regarding owner change
1125 -- OPEN c_get_owner_details (cp_group_id => p_group_id);
1126 --FETCH c_get_owner_details
1127 -- INTO l_curr_owner_id, l_group_owner_rel_id, l_object_version_no_owner;
1128 -- IF c_get_owner_details%NOTFOUND THEN
1129 -- l_curr_owner_id := NULL;
1130 --END IF;
1131 --CLOSE c_get_owner_details;
1132
1133 --IF l_curr_owner_id IS NOT NULL THEN
1134 -- IF l_curr_owner_id <> p_owner_person_id THEN
1135 -- the owner has changed
1136 -- l_update_owner := TRUE;
1137 -- check if the new person is already a member in the group
1138 -- OPEN c_is_group_member (cp_group_id => p_group_id
1139 -- ,cp_member_id => p_owner_person_id);
1140 -- FETCH c_is_group_member INTO l_curr_member_id;
1141 -- IF c_is_group_member%FOUND THEN
1142 -- l_create_member := FALSE;
1143 -- ELSE
1144 -- l_create_member := TRUE;
1145 -- END IF;
1146 -- CLOSE c_is_group_member;
1147 -- ELSE
1148 -- l_update_owner := FALSE;
1149 -- l_create_member := FALSE;
1150 -- END IF;
1151 -- ELSE
1152 -- should never occur if Create Group is Successful
1153 -- mdebug('UPDATE_GROUP: NO Owner for the Group !! ');
1154 -- l_update_owner := FALSE;
1155 -- l_create_member := FALSE;
1156 -- END IF;
1157
1158 --IF l_update_owner THEN
1159 --
1160 -- changing the owner of the group is done in two steps
1161 --
1162 -- Step - 1: make the current owner inactive
1163 --
1164 -- l_party_rel_rec.status := 'I';
1165 -- l_party_rel_rec.end_date := SYSDATE;
1166 -- l_party_rel_rec.relationship_id := l_group_owner_rel_id;
1167
1168 -- mdebug('UPDATE_GROUP: before deactivating the current owner');
1169 -- update_relationship
1170 -- (p_api_version => 1.0
1171 -- ,p_init_msg_list => NVL(p_init_msg_list, 'F')
1172 -- ,p_commit => NVL(p_commit, 'F')
1173 -- ,p_party_rel_rec => l_party_rel_rec
1174 -- ,p_object_version_no_rel => p_object_version_no_owner_rel
1175 -- ,x_return_status => x_return_status
1176 -- ,x_msg_count => x_msg_count
1177 -- ,x_msg_data => x_msg_data
1178 -- );
1179 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1180 -- ROLLBACK TO EGO_UPDATE_GROUP;
1181 -- RETURN;
1182 -- END IF;
1183 -- mdebug('UPDATE_GROUP: Deactivated the current relationship for owner with status '''||x_return_status||'''');
1184 --
1185 -- Step - 2: create the new owner
1186 --
1187 -- mdebug('UPDATE_GROUP: before creating the new owner');
1188 -- create_relationship(
1189 -- p_api_version => 1.0,
1190 -- p_init_msg_list => NVL(p_init_msg_list, 'F'),
1191 -- p_commit => NVL(p_commit, 'F'),
1192 -- p_subject_id => p_owner_person_id,
1193 -- p_subject_type => 'PERSON',
1194 -- p_subject_table_name => 'HZ_PARTIES',
1195 -- p_object_id => p_group_id,
1196 -- p_object_type => 'GROUP',
1197 -- p_object_table_name => 'HZ_PARTIES',
1198 -- p_relationship_code => G_OWNER_GROUP_REL_CODE,
1199 -- p_relationship_type => G_OWNER_GROUP_REL_TYPE,
1200 -- p_program_name => G_PKG_NAME,
1201 -- p_start_date => SYSDATE,
1202 -- x_return_status => x_return_status,
1203 -- x_msg_count => x_msg_count,
1204 -- x_msg_data => x_msg_data,
1205 -- x_relationship_id => l_group_owner_rel_id
1206 -- );
1207 -- mdebug('UPDATE_GROUP: New owner relationship created with status '''||x_return_status||'''');
1208 -- mdebug('UPDATE_GROUP: New owner relationship id '||to_char(l_group_owner_rel_id));
1209 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1210 -- ROLLBACK TO EGO_UPDATE_GROUP;
1211 -- RETURN;
1212 -- END IF;
1213 -- IF l_create_member THEN
1214 -- owner not a member so, create the new member
1215 -- mdebug('UPDATE_GROUP: before adding the new owner as member to the group ');
1216 -- mdebug('UPDATE_GROUP: group_id ' || to_char(p_group_id) || ' member_id ' || to_char(p_owner_person_id));
1217 -- Add_Group_Member(
1218 -- p_api_version => 1.0,
1219 -- p_init_msg_list => NVL(p_init_msg_list, 'F'),
1220 -- p_commit => NVL(p_commit, 'F'),
1221 -- p_member_id => p_owner_person_id,
1222 -- p_group_id => p_group_id,
1223 -- p_start_date => SYSDATE,
1224 -- p_end_date => NULL,
1225 -- x_return_status => x_return_status,
1226 -- x_msg_count => x_msg_count,
1227 -- x_msg_data => x_msg_data,
1228 -- x_relationship_id => l_group_member_rel_id
1229 -- );
1230 -- mdebug('UPDATE_GROUP: new owner added as member to the group with status ' ||x_return_status);
1231 -- mdebug('UPDATE_GROUP: new owner''s membership id ' ||to_char(l_group_member_rel_id));
1232 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1233 -- ROLLBACK TO EGO_UPDATE_GROUP;
1234 -- RETURN;
1235 -- END IF;
1236 -- END IF;
1237 -- END IF;
1238 -- End Of Commented Code
1239 -- Output commands to test if Group successfully created.
1240 mdebug('UPDATE_GROUP updated group '|| to_char(p_group_id));
1241
1242 -- Standard check of p_commit.
1243 IF FND_API.To_Boolean( NVL(p_commit, 'F') ) THEN
1244 COMMIT WORK;
1245 END IF;
1246
1247 x_return_status := FND_API.G_RET_STS_SUCCESS;
1248 -- Standard call to get message count and if count is 1,
1249 -- get message info.
1250 -- The client will directly display the x_msg_data (which is already
1251 -- translated) if the x_msg_count = 1;
1252 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1253 -- Server-side procedure to access the messages, and consolidate them
1254 -- and display (or) to display one message after another.
1255 mdebug('UPDATE_GROUP Tracing....');
1256
1257 FND_MSG_PUB.Count_And_Get
1258 ( p_count => x_msg_count,
1259 p_data => x_msg_data
1260 );
1261
1262 EXCEPTION
1263 WHEN FND_API.G_EXC_ERROR THEN
1264 IF FND_API.TO_BOOLEAN(p_commit) THEN
1265 ROLLBACK TO EGO_UPDATE_GROUP;
1266 END IF;
1267 mdebug('UPDATE_GROUP Ending : Returning ''FND_API.G_EXC_ERROR'' ERROR');
1268 x_return_status := FND_API.G_RET_STS_ERROR;
1269 FND_MSG_PUB.Count_And_Get
1270 (p_count => x_msg_count,
1271 p_data => x_msg_data
1272 );
1273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1274 IF FND_API.TO_BOOLEAN(p_commit) THEN
1275 ROLLBACK TO EGO_UPDATE_GROUP;
1276 END IF;
1277 mdebug('UPDATE_GROUP Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR'' ERROR');
1278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1279 FND_MSG_PUB.Count_And_Get
1280 (p_count => x_msg_count,
1281 p_data => x_msg_data
1282 );
1283 WHEN OTHERS THEN
1284 IF FND_API.TO_BOOLEAN(p_commit) THEN
1285 ROLLBACK TO EGO_UPDATE_GROUP;
1286 END IF;
1287 -- IF c_get_owner_details%ISOPEN THEN
1288 -- CLOSE c_get_owner_details;
1289 -- END IF;
1290 IF c_is_group_member%ISOPEN THEN
1291 CLOSE c_is_group_member;
1292 END IF;
1293 IF c_get_contact_details%ISOPEN THEN
1294 CLOSE c_get_contact_details;
1295 END IF;
1296 mdebug('UPDATE_GROUP Ending : Returning UNEXPECTED ERROR');
1297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1298 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1299 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1300 END IF;
1301 FND_MSG_PUB.Count_And_Get
1302 (p_count => x_msg_count,
1303 p_data => x_msg_data
1304 );
1305 END update_group;
1306
1307
1308 ----------------------------------------------------------------------------
1309 -- 3. Delete_Group
1310 ----------------------------------------------------------------------------
1311 procedure Delete_Group (
1312 p_api_version IN NUMBER,
1313 p_init_msg_list IN VARCHAR2,
1314 p_commit IN VARCHAR2,
1315 p_group_id IN NUMBER,
1316 p_object_version_no_group IN OUT NOCOPY NUMBER,
1317 x_return_status OUT NOCOPY VARCHAR2,
1318 x_msg_count OUT NOCOPY NUMBER,
1319 x_msg_data OUT NOCOPY VARCHAR2
1320 ) IS
1321 ------------------------------------------------------------------------
1322 -- Start of comments
1323 -- API name : Delete_Group
1324 -- TYPE : Public
1325 -- Pre-reqs : None
1326 -- FUNCTION : Delete a Group.
1327 -- p_object_version_no_group is a mandatory field used to check
1328 -- whether the record is updated after query
1329 -- Delete the Group, owner and all members of the Group
1330 --
1331 -- Version: Current Version 1.0
1332 -- Previous Version : None
1333 -- Notes :
1334 --
1335 -- END OF comments
1336 ------------------------------------------------------------------------
1337
1338 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP';
1339 -- On addition of any Required parameters the major version needs
1340 -- to change i.e. for eg. 1.X to 2.X.
1341 -- On addition of any Optional parameters the minor version needs
1342 -- to change i.e. for eg. X.6 to X.7.
1343 l_api_version CONSTANT NUMBER := 1.0;
1344
1345 -- General variables
1346 l_group_rec HZ_PARTY_V2PUB.GROUP_REC_TYPE;
1347 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
1348 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
1349
1350 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
1351 l_edi_rec HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
1352 l_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
1353 l_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
1354 l_telex_rec HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
1355 l_web_rec HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
1356
1357 l_curr_owner_id NUMBER;
1358 l_curr_member_id NUMBER;
1359 l_group_member_rel_id NUMBER;
1360 l_group_owner_rel_id NUMBER;
1361 l_contact_point_id NUMBER;
1362 l_object_version_number NUMBER;
1363
1364
1365 CURSOR c_get_group_members(cp_group_id IN NUMBER) IS
1366 SELECT relationship_id, object_version_number
1367 FROM hz_relationships
1368 WHERE object_id = cp_group_id
1369 AND relationship_type = G_MEMBER_GROUP_REL_TYPE
1370 AND status = 'A'
1371 AND SYSDATE between start_date and NVL(end_date,SYSDATE);
1372
1373 -- CURSOR c_get_group_owner (cp_group_id IN NUMBER) IS
1374 -- SELECT relationship_id, object_version_number
1375 -- FROM hz_relationships
1376 -- WHERE object_id = cp_group_id
1377 -- AND relationship_type = G_OWNER_GROUP_REL_TYPE
1378 -- AND status = 'A'
1379 -- AND SYSDATE between start_date and NVL(end_date,SYSDATE);
1380
1381 CURSOR c_get_contact_point (cp_group_id IN NUMBER) IS
1382 SELECT contact_point_id, object_version_number
1383 FROM hz_contact_points
1384 WHERE owner_table_id = cp_group_id
1385 AND owner_table_name = 'HZ_PARTIES'
1386 AND status = 'A';
1387
1388 BEGIN
1389 -- check if all required parameters are passed to the procedure
1390 IF (p_api_version IS NULL
1391 OR p_group_id IS NULL
1392 OR p_object_version_no_group IS NULL
1393 ) THEN
1394 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
1395 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
1396 fnd_msg_pub.add;
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 x_msg_count := 1;
1399 fnd_msg_pub.Count_And_Get
1400 (p_count => x_msg_count
1401 ,p_data => x_msg_data
1402 );
1403 RETURN;
1404 END IF;
1405 -- Standard Start of API savepoint
1406 IF FND_API.TO_BOOLEAN(p_commit) THEN
1407 SAVEPOINT EGO_DELETE_GROUP;
1408 END IF;
1409
1410 IF NOT FND_API.Compatible_API_Call (l_api_version,
1411 p_api_version,
1412 l_api_name,
1413 G_PKG_NAME)
1414 THEN
1415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1416 END IF;
1417 -- Initialize API message list if necessary.
1418 -- Initialize message list if p_init_msg_list is set to TRUE.
1419 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
1420 FND_MSG_PUB.initialize;
1421 END IF;
1422 --
1423 -- delete all the members of the Group
1424 --
1425 OPEN c_get_group_members (cp_group_id => p_group_id);
1426 LOOP
1427 FETCH c_get_group_members
1428 INTO l_group_member_rel_id, l_object_version_number;
1429 EXIT WHEN c_get_group_members%NOTFOUND;
1430 l_party_rel_rec.status := 'I';
1431 l_party_rel_rec.end_date := SYSDATE;
1432 l_party_rel_rec.relationship_id := l_group_member_rel_id;
1433 update_relationship
1434 (p_api_version => 1.0
1435 ,p_init_msg_list => NVL(p_init_msg_list, 'F')
1436 ,p_commit => NVL(p_commit, 'F')
1437 ,p_party_rel_rec => l_party_rel_rec
1438 ,p_object_version_no_rel => l_object_version_number
1439 ,x_return_status => x_return_status
1440 ,x_msg_count => x_msg_count
1441 ,x_msg_data => x_msg_data
1442 );
1443 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1444 IF FND_API.TO_BOOLEAN(p_commit) THEN
1445 ROLLBACK TO EGO_DELETE_GROUP;
1446 END IF;
1447 EXIT;
1448 END IF;
1449 END LOOP;
1450 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1451 RETURN;
1452 END IF;
1453 --
1454 -- The Owner relatioships does not exist any more
1455 --
1456 -- --
1457 -- -- delete the owner(s) of the Group
1458 -- --
1459 -- OPEN c_get_group_owner (cp_group_id => p_group_id);
1460 -- LOOP
1461 -- FETCH c_get_group_owner
1462 -- INTO l_group_owner_rel_id, l_object_version_number;
1463 -- EXIT WHEN c_get_group_owner%NOTFOUND;
1464 -- l_party_rel_rec.status := 'I';
1465 -- l_party_rel_rec.end_date := SYSDATE;
1466 -- l_party_rel_rec.relationship_id := l_group_owner_rel_id;
1467 -- update_relationship
1468 -- (p_api_version => 1.0
1469 -- ,p_init_msg_list => NVL(p_init_msg_list, 'F')
1470 -- ,p_commit => NVL(p_commit, 'F')
1471 -- ,p_party_rel_rec => l_party_rel_rec
1472 -- ,p_object_version_no_rel => l_object_version_number
1473 -- ,x_return_status => x_return_status
1474 -- ,x_msg_count => x_msg_count
1475 -- ,x_msg_data => x_msg_data
1476 -- );
1477 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1478 -- ROLLBACK TO EGO_DELETE_GROUP;
1479 -- EXIT;
1480 -- END IF;
1481 -- END LOOP;
1482 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1483 -- RETURN;
1484 -- END IF;
1485 --
1486 -- remove the contact point(s)
1487 --
1488 OPEN c_get_contact_point (cp_group_id => p_group_id);
1489 LOOP
1490 FETCH c_get_contact_point
1491 INTO l_contact_point_id, l_object_version_number;
1492 EXIT WHEN c_get_contact_point%NOTFOUND;
1493 l_contact_point_rec.status := 'I';
1494 l_contact_point_rec.contact_point_id := l_contact_point_id;
1495 HZ_CONTACT_POINT_V2PUB.update_contact_point
1496 (p_init_msg_list => NVL(p_init_msg_list, 'F')
1497 ,p_contact_point_rec => l_contact_point_rec
1498 ,p_edi_rec => l_edi_rec
1499 ,p_email_rec => l_email_rec
1500 ,p_phone_rec => l_phone_rec
1501 ,p_telex_rec => l_telex_rec
1502 ,p_web_rec => l_web_rec
1503 ,p_object_version_number => l_object_version_number
1504 ,x_return_status => x_return_status
1505 ,x_msg_count => x_msg_count
1506 ,x_msg_data => x_msg_data
1507 );
1508 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1509 IF FND_API.TO_BOOLEAN(p_commit) THEN
1510 ROLLBACK TO EGO_DELETE_GROUP;
1511 END IF;
1512 EXIT;
1513 END IF;
1514 END LOOP;
1515 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1516 RETURN;
1517 END IF;
1518 --
1519 -- delete the Group
1520 -- setting Party status to 'I'
1521 --
1522 l_group_rec.party_rec.party_id := p_group_id;
1523 l_group_rec.party_rec.status := 'I';
1524 HZ_PARTY_V2PUB.update_group
1525 (p_init_msg_list => NVL(p_init_msg_list, 'F')
1526 ,p_group_rec => l_group_rec
1527 ,p_party_object_version_number => p_object_version_no_group
1528 ,x_return_status => x_return_status
1529 ,x_msg_count => x_msg_count
1530 ,x_msg_data => x_msg_data
1531 );
1532 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1533 IF FND_API.TO_BOOLEAN(p_commit) THEN
1534 ROLLBACK TO EGO_DELETE_GROUP;
1535 END IF;
1536 RETURN;
1537 END IF;
1538
1539
1540 -- Standard check of p_commit.
1541 IF FND_API.To_Boolean( NVL(p_commit, 'F') ) THEN
1542 COMMIT WORK;
1543 END IF;
1544
1545 x_return_status := FND_API.G_RET_STS_SUCCESS;
1546 -- Standard call to get message count and if count is 1,
1547 -- get message info.
1548 -- The client will directly display the x_msg_data (which is already
1549 -- translated) if the x_msg_count = 1;
1550 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1551 -- Server-side procedure to access the messages, and consolidate them
1552 -- and display (or) to display one message after another.
1553 mdebug('Tracing....');
1554
1555 FND_MSG_PUB.Count_And_Get
1556 ( p_count => x_msg_count,
1557 p_data => x_msg_data
1558 );
1559
1560
1561 EXCEPTION
1562 WHEN FND_API.G_EXC_ERROR THEN
1563 IF FND_API.TO_BOOLEAN(p_commit) THEN
1564 ROLLBACK TO EGO_DELETE_GROUP;
1565 END IF;
1566 mdebug('DELETE_GROUP Ending : Returning ''FND_API.G_EXC_ERROR'' ERROR');
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 FND_MSG_PUB.Count_And_Get
1569 (p_count => x_msg_count,
1570 p_data => x_msg_data
1571 );
1572 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1573 IF FND_API.TO_BOOLEAN(p_commit) THEN
1574 ROLLBACK TO EGO_DELETE_GROUP;
1575 END IF;
1576 mdebug('DELETE_GROUP Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR'' ERROR');
1577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1578 FND_MSG_PUB.Count_And_Get
1579 (p_count => x_msg_count,
1580 p_data => x_msg_data
1581 );
1582 WHEN OTHERS THEN
1583 IF FND_API.TO_BOOLEAN(p_commit) THEN
1584 ROLLBACK TO EGO_DELETE_GROUP;
1585 END IF;
1586 mdebug('DELETE_GROPU Ending : Returning UNEXPECTED ERROR');
1587 IF c_get_group_members%ISOPEN THEN
1588 CLOSE c_get_group_members;
1589 END IF;
1590 -- IF c_get_group_owner%ISOPEN THEN
1591 -- CLOSE c_get_group_owner;
1592 -- END IF;
1593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1594 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1595 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1596 END IF;
1597 FND_MSG_PUB.Count_And_Get
1598 (p_count => x_msg_count,
1599 p_data => x_msg_data
1600 );
1601
1602 END delete_group;
1603
1604
1605 ----------------------------------------------------------------------------
1606 -- 4. Add_Group_Member
1607 ----------------------------------------------------------------------------
1608 procedure Add_Group_Member (
1609 p_api_version IN NUMBER,
1610 p_init_msg_list IN VARCHAR2,
1611 p_commit IN VARCHAR2,
1612 p_member_id IN NUMBER,
1613 p_group_id IN NUMBER,
1614 p_start_date IN DATE,
1615 p_end_date IN DATE,
1616 x_return_status OUT NOCOPY VARCHAR2,
1617 x_msg_count OUT NOCOPY NUMBER,
1618 x_msg_data OUT NOCOPY VARCHAR2,
1619 x_relationship_id OUT NOCOPY NUMBER
1620 ) IS
1621 ------------------------------------------------------------------------
1622 -- Start of comments
1623 -- API name : Add_Group_Member
1624 -- TYPE : Public
1625 -- Pre-reqs : None
1626 -- FUNCTION : Add a member to a Group.
1627 -- Creates two way relationship between Member and Group
1628 -- Forward relation person -> MEMBER_OF -> group
1629 -- Reverse relation group <- CONTAINS_MEMBER <- person
1630 --
1631 -- If this operation fails then the category is not
1632 -- created and error code is returned.
1633 --
1634 -- x_relationship_id OUT NUMBER
1635 -- Relationship_Id that has been created between Group_id.
1636 -- and the Member_Id, which is finally stored in
1637 -- hz_relationships.PARTY_RELATIONSHIP_ID
1638 --
1639 --
1640 -- Version: Current Version 1.0
1641 -- Previous Version : None
1642 -- Notes :
1643 --
1644 -- END OF comments
1645 ------------------------------------------------------------------------
1646
1647 l_Sysdate DATE := Sysdate;
1648 l_api_name CONSTANT VARCHAR2(30) := 'ADD_GROUP_MEMBER';
1649 -- On addition of any Required parameters the major version needs
1650 -- to change i.e. for eg. 1.X to 2.X.
1651 -- On addition of any Optional parameters the minor version needs
1652 -- to change i.e. for eg. X.6 to X.7.
1653 l_api_version CONSTANT NUMBER := 1.0;
1654
1655 -- General variables
1656 l_return_status VARCHAR2(50);
1657 l_error_code NUMBER;
1658
1659 BEGIN
1660 -- check if all required parameters are passed to the procedure
1661 IF (p_api_version IS NULL
1662 OR p_group_id IS NULL
1663 OR p_member_id IS NULL) THEN
1664 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
1665 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
1666 fnd_msg_pub.add;
1667 x_return_status := FND_API.G_RET_STS_ERROR;
1668 x_msg_count := 1;
1669 fnd_msg_pub.Count_And_Get
1670 (p_count => x_msg_count
1671 ,p_data => x_msg_data
1672 );
1673 RETURN;
1674 END IF;
1675 -- Standard Start of API savepoint
1676 IF FND_API.TO_BOOLEAN(p_commit) THEN
1677 SAVEPOINT EGO_ADD_GROUP_MEMBER;
1678 END IF;
1679
1680 mdebug('ADD_GROUP_MEMBER: ........1........ ');
1681 IF NOT FND_API.Compatible_API_Call (l_api_version,
1682 p_api_version,
1683 l_api_name,
1684 G_PKG_NAME)
1685 THEN
1686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1687 END IF;
1688 -- Initialize API message list if necessary.
1689 -- Initialize message list if p_init_msg_list is set to TRUE.
1690 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
1691 FND_MSG_PUB.initialize;
1692 END IF;
1693
1694 mdebug('ADD_GROUP_MEMBER: Setting local values ');
1695
1696 create_relationship(
1697 p_api_version => 1.0,
1698 p_init_msg_list => NVL(p_init_msg_list, 'F'),
1699 p_commit => NVL(p_commit, 'F'),
1700 p_subject_id => p_member_id,
1701 p_subject_type => 'PERSON',
1702 p_subject_table_name => 'HZ_PARTIES',
1703 p_object_id => p_group_id,
1704 p_object_type => 'GROUP',
1705 p_object_table_name => 'HZ_PARTIES',
1706 p_relationship_code => G_MEMBER_GROUP_REL_CODE,
1707 p_relationship_type => G_MEMBER_GROUP_REL_TYPE,
1708 p_program_name => CREATED_BY_MODULE,
1709 p_start_date => NVL(p_start_date, SYSDATE),
1710 x_return_status => x_return_status,
1711 x_msg_count => x_msg_count,
1712 x_msg_data => x_msg_data,
1713 x_relationship_id => x_relationship_id
1714 );
1715
1716 -- mdebug('ADD_GROUP_MEMBER: created party_relationship');
1717 -- mdebug('ADD_GROUP_MEMBER: party_rel_id '|| to_char(x_relationship_id)||' return_status '|| x_return_status);
1718 -- mdebug('ADD_GROUP_MEMBER: party_id '|| to_char(p_member_id)||' group_id '|| to_char(p_group_id));
1719 -- mdebug('ADD_GROUP_MEMBER: x_msg_data ' || x_msg_data);
1720 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1721 IF FND_API.TO_BOOLEAN(p_commit) THEN
1722 ROLLBACK TO EGO_ADD_GROUP_MEMBER;
1723 END IF;
1724 RETURN;
1725 END IF;
1726
1727 -- confirmed from Deb that this will never return error
1728 EGO_DOM_WS_INTERFACE_PUB.Add_OFO_Group_Member
1729 (p_api_version => 1.0
1730 ,p_init_msg_list => NVL(p_init_msg_list, 'F')
1731 ,p_commit => NVL(p_commit, 'F')
1732 ,p_group_id => p_group_id
1733 ,p_member_id => p_member_id
1734 ,x_return_status => x_return_status
1735 ,x_msg_count => x_msg_count
1736 ,x_msg_data => x_msg_data
1737 );
1738
1739 -- commented out for not giving a grant to the member
1740 -- mdebug('ADD_GROUP_MEMBER: Before calling EGO_SECURITY_PUB.GRANT_ROLE ');
1741 -- mdebug('ADD_GROUP_MEMBER: p_api_version => 1.0, p_role_name = ''EGO_VIEW_GROUP_MEMBERS'', ');
1742 -- mdebug('ADD_GROUP_MEMBER: p_object_name => ''EGO_GROUP'', p_instance_type => ''INSTANCE'', ');
1743 -- mdebug('ADD_GROUP_MEMBER: p_object_key => ' ||to_char(p_group_id) ||', p_party_id => ' || to_char(p_member_id)||', ');
1744 -- mdebug('ADD_GROUP_MEMBER: p_start_date => ' ||TO_CHAR(NVL(p_start_date, SYSDATE),'DD-MON-YYYY')||', p_end_date => NULL');
1745
1746 -- EGO_SECURITY_PUB.grant_role
1747 -- (p_api_version => 1.0
1748 -- ,p_role_name => 'EGO_VIEW_GROUP_MEMBERS'
1749 -- ,p_object_name => 'EGO_GROUP'
1750 -- ,p_instance_type => 'INSTANCE'
1751 -- ,p_object_key => p_group_id
1752 -- ,p_party_id => p_member_id
1753 -- ,p_start_date => NVL(p_start_date,SYSDATE)
1754 -- ,p_end_date => NULL
1755 -- ,x_return_status => l_return_status
1756 -- ,x_errorcode => l_error_code
1757 -- );
1758 -- mdebug('ADD_GROUP_MEMBER: Successfully exited from EGO_SECURITY_PUB.grant_role ');
1759 -- mdebug('ADD_GROUP_MEMBER: return status '|| l_return_status );
1760 -- mdebug('ADD_GROUP_MEMBER: error_code '|| l_error_code );
1761 -- --
1762 -- -- EGO Security pub returns T if the action is success
1763 -- -- and F on failure
1764 -- --
1765 -- IF l_return_status <> 'T' THEN
1766 -- x_return_status := FND_API.G_RET_STS_ERROR;
1767 -- ROLLBACK TO EGO_ADD_GROUP_MEMBER;
1768 -- RETURN;
1769 -- ELSE
1770 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
1771 -- END IF;
1772 -- -- Standard check of p_commit.
1773 -- Commenting by Sridhar ends here (conf call with Wasi on 12-feb-2003)
1774
1775 IF FND_API.To_Boolean( NVL(p_commit, 'F') ) THEN
1776 COMMIT WORK;
1777 END IF;
1778
1779 x_return_status := FND_API.G_RET_STS_SUCCESS;
1780 -- Standard call to get message count and if count is 1,
1781 -- get message info.
1782 -- The client will directly display the x_msg_data (which is already
1783 -- translated) if the x_msg_count = 1;
1784 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1785 -- Server-side procedure to access the messages, and consolidate them
1786 -- and display (or) to display one message after another.
1787 mdebug('ADD_GROUP_MEMBER Tracing....');
1788
1789 FND_MSG_PUB.Count_And_Get
1790 ( p_count => x_msg_count,
1791 p_data => x_msg_data
1792 );
1793
1794
1795 EXCEPTION
1796 WHEN FND_API.G_EXC_ERROR THEN
1797 IF FND_API.TO_BOOLEAN(p_commit) THEN
1798 ROLLBACK TO EGO_ADD_GROUP_MEMBER;
1799 END IF;
1800 mdebug('ADD_GROUP_MEMBER Ending : Returning ''FND_API.G_EXC_ERROR''');
1801 x_return_status := FND_API.G_RET_STS_ERROR;
1802 FND_MSG_PUB.Count_And_Get
1803 (p_count => x_msg_count,
1804 p_data => x_msg_data
1805 );
1806 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1807 IF FND_API.TO_BOOLEAN(p_commit) THEN
1808 ROLLBACK TO EGO_ADD_GROUP_MEMBER;
1809 END IF;
1810 mdebug('ADD_GROUP_MEMBER Ending : Returning ''FND_API.G_EXC_UNEXPECTED_ERROR''');
1811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1812 FND_MSG_PUB.Count_And_Get
1813 (p_count => x_msg_count,
1814 p_data => x_msg_data
1815 );
1816 WHEN OTHERS THEN
1817 IF FND_API.TO_BOOLEAN(p_commit) THEN
1818 ROLLBACK TO EGO_ADD_GROUP_MEMBER;
1819 END IF;
1820 mdebug('ADD_GROUP_MEMBER Ending : Returning UNEXPECTED ERROR');
1821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1822 IF FND_MSG_PUB.Check_Msg_Level
1823 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1824 THEN
1825 FND_MSG_PUB.Add_Exc_Msg
1826 ( G_PKG_NAME,
1827 l_api_name
1828 );
1829 END IF;
1830 FND_MSG_PUB.Count_And_Get
1831 ( p_count => x_msg_count,
1832 p_data => x_msg_data
1833 );
1834 END Add_Group_Member;
1835
1836
1837 ----------------------------------------------------------------------------
1838 -- 5. Remove_Group_Member
1839 ----------------------------------------------------------------------------
1840 procedure Remove_Group_Member (
1841 p_api_version IN NUMBER,
1842 p_init_msg_list IN VARCHAR2,
1843 p_commit IN VARCHAR2,
1844 p_relationship_id IN NUMBER,
1845 p_object_version_no_rel IN OUT NOCOPY NUMBER,
1846 x_return_status OUT NOCOPY VARCHAR2,
1847 x_msg_count OUT NOCOPY NUMBER,
1848 x_msg_data OUT NOCOPY VARCHAR2
1849 ) IS
1850 ------------------------------------------------------------------------
1851 -- Start of comments
1852 -- API name : Remove_Group_Member
1853 -- TYPE : Public
1854 -- Pre-reqs : None
1855 -- FUNCTION : Remove a Member from Group.
1856 --
1857 --
1858 -- Version: Current Version 1.0
1859 -- Previous Version : None
1860 -- Notes :
1861 --
1862 -- END OF comments
1863 ------------------------------------------------------------------------
1864 l_api_name CONSTANT VARCHAR2(30) := 'REMOVE_GROUP_MEMBER';
1865 -- On addition of any Required parameters the major version needs
1866 -- to change i.e. for eg. 1.X to 2.X.
1867 -- On addition of any Optional parameters the minor version needs
1868 -- to change i.e. for eg. X.6 to X.7.
1869
1870 l_api_version CONSTANT NUMBER := 1.0;
1871 l_return_status VARCHAR2(10);
1872
1873 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
1874
1875 l_member_id NUMBER;
1876 L_GRANT_GUID VARCHAR2(100);
1877 x_ret_status VARCHAR2(1);
1878 l_group_id HZ_PARTIES.PARTY_ID%TYPE;
1879 x_errorcode NUMBER;
1880
1881 CURSOR get_grant_guid_cur (cp_party_id NUMBER,
1882 cp_instance_id NUMBER)
1883 IS
1884 SELECT grants.grant_guid
1885 FROM fnd_grants grants,
1886 fnd_menus menus,
1887 fnd_objects obj
1888 WHERE menus.menu_name='EGO_VIEW_GROUP_MEMBERS'
1889 AND menus.menu_id=grants.menu_id
1890 AND obj.object_id=grants.object_id
1891 AND obj.obj_name='EGO_GROUP'
1892 AND grants.instance_pk1_value=cp_instance_id
1893 AND grantee_key='HZ_PARTY:'||cp_party_id;
1894
1895
1896 BEGIN
1897 -- check if all required parameters are passed to the procedure
1898 IF (p_api_version IS NULL
1899 OR p_relationship_id IS NULL
1900 OR p_object_version_no_rel IS NULL
1901 ) THEN
1902 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
1903 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
1904 fnd_msg_pub.add;
1905 x_return_status := FND_API.G_RET_STS_ERROR;
1906 x_msg_count := 1;
1907 fnd_msg_pub.Count_And_Get
1908 (p_count => x_msg_count
1909 ,p_data => x_msg_data
1910 );
1911 RETURN;
1912 END IF;
1913 -- Standard Start of API savepoint
1914 IF FND_API.TO_BOOLEAN(p_commit) THEN
1915 SAVEPOINT EGO_REMOVE_GROUP_MEMBER;
1916 END IF;
1917
1918 IF NOT FND_API.Compatible_API_Call (l_api_version,
1919 p_api_version,
1920 l_api_name,
1921 G_PKG_NAME)
1922 THEN
1923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1924 END IF;
1925 -- Initialize API message list if necessary.
1926 -- Initialize message list if p_init_msg_list is set to TRUE.
1927 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
1928 FND_MSG_PUB.initialize;
1929 END IF;
1930 IF (p_relationship_id IS NOT NULL) THEN
1931 -- added Directional flag in where clause since two records are getting returned for each relationship one forward and one backward
1932 SELECT subject_id, object_id
1933 INTO l_member_id, l_group_id
1934 FROM hz_relationships
1935 WHERE RELATIONSHIP_ID = p_relationship_id
1936 AND directional_flag = 'F';
1937
1938 l_party_rel_rec.status := 'I';
1939 l_party_rel_rec.end_date := SYSDATE;
1940 l_party_rel_rec.relationship_id := p_relationship_id;
1941
1942 update_relationship
1943 (p_api_version => 1.0
1944 ,p_init_msg_list => NVL(p_init_msg_list, 'F')
1945 ,p_commit => NVL(p_commit, 'F')
1946 ,p_party_rel_rec => l_party_rel_rec
1947 ,p_object_version_no_rel => p_object_version_no_rel
1948 ,x_return_status => x_return_status
1949 ,x_msg_count => x_msg_count
1950 ,x_msg_data => x_msg_data
1951 );
1952 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1953 IF FND_API.TO_BOOLEAN(p_commit) THEN
1954 ROLLBACK TO EGO_REMOVE_GROUP_MEMBER;
1955 END IF;
1956 RETURN;
1957 END IF;
1958 ELSE
1959 mdebug('No member id provided!');
1960 FND_MESSAGE.Set_Name('EGO', 'EGO_GRP_MEMB_CANNOT_DELETE');
1961 FND_MSG_PUB.Add;
1962 RAISE fnd_api.g_EXC_ERROR;
1963 END IF;
1964 OPEN get_grant_guid_cur (cp_party_id => l_member_id,
1965 cp_instance_id => l_group_id);
1966 FETCH get_grant_guid_cur INTO l_grant_guid;
1967 IF(get_grant_guid_cur%FOUND) THEN
1968 CLOSE get_grant_guid_cur;
1969 EGO_SECURITY_PUB.revoke_grant
1970 (
1971 p_api_version =>p_api_version
1972 ,p_grant_guid =>l_grant_guid
1973 ,x_return_status =>x_ret_status
1974 ,x_errorcode =>x_errorcode
1975 );
1976 ELSE
1977 CLOSE get_grant_guid_cur;
1978 END IF;
1979
1980 -- Standard check of p_commit.
1981 IF FND_API.To_Boolean( NVL(p_commit, 'F') ) THEN
1982 COMMIT WORK;
1983 END IF;
1984
1985 x_return_status := FND_API.G_RET_STS_SUCCESS;
1986 -- Standard call to get message count and if count is 1,
1987 -- get message info.
1988 -- The client will directly display the x_msg_data (which is already
1989 -- translated) if the x_msg_count = 1;
1990 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1991 -- Server-side procedure to access the messages, and consolidate them
1992 -- and display (or) to display one message after another.
1993 mdebug('Tracing....');
1994
1995 FND_MSG_PUB.Count_And_Get
1996 ( p_count => x_msg_count,
1997 p_data => x_msg_data
1998 );
1999
2000
2001 EXCEPTION
2002 WHEN FND_API.G_EXC_ERROR THEN
2003 IF FND_API.TO_BOOLEAN(p_commit) THEN
2004 ROLLBACK TO EGO_REMOVE_GROUP_MEMBER;
2005 END IF;
2006 mdebug('Ending : Returning ERROR');
2007 x_return_status := FND_API.G_RET_STS_ERROR;
2008 FND_MSG_PUB.Count_And_Get
2009 (p_count => x_msg_count,
2010 p_data => x_msg_data
2011 );
2012 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2013 IF FND_API.TO_BOOLEAN(p_commit) THEN
2014 ROLLBACK TO EGO_REMOVE_GROUP_MEMBER;
2015 END IF;
2016 mdebug('Ending : Returning UNEXPECTED ERROR');
2017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2018 FND_MSG_PUB.Count_And_Get
2019 (p_count => x_msg_count,
2020 p_data => x_msg_data
2021 );
2022 WHEN OTHERS THEN
2023 IF FND_API.TO_BOOLEAN(p_commit) THEN
2024 ROLLBACK TO EGO_REMOVE_GROUP_MEMBER;
2025 END IF;
2026 mdebug('Ending : Returning UNEXPECTED ERROR');
2027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2028 IF FND_MSG_PUB.Check_Msg_Level
2029 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2030 THEN
2031 FND_MSG_PUB.Add_Exc_Msg
2032 ( G_PKG_NAME,
2033 l_api_name
2034 );
2035 END IF;
2036 FND_MSG_PUB.Count_And_Get
2037 (p_count => x_msg_count,
2038 p_data => x_msg_data
2039 );
2040 END Remove_Group_Member;
2041
2042 ----------------------------------------------------------------------------
2043 -- 6. Get_Email_Address (party_id can be person / group Id)
2044 ----------------------------------------------------------------------------
2045 procedure Get_Email_Address (
2046 p_api_version IN NUMBER,
2047 p_init_msg_list IN VARCHAR2,
2048 p_commit IN VARCHAR2,
2049 p_party_id IN NUMBER,
2050 x_return_status OUT NOCOPY VARCHAR2,
2051 x_msg_count OUT NOCOPY NUMBER,
2052 x_msg_data OUT NOCOPY VARCHAR2,
2053 x_email_address OUT NOCOPY VARCHAR2
2054 ) IS
2055 ------------------------------------------------------------------------
2056 -- Start of comments
2057 -- API name : Get_Email_Address
2058 -- TYPE : Public
2059 -- Pre-reqs : None
2060 -- FUNCTION : Get Email Address.
2061 -- Then intention is to Get all e-mail addresses of the
2062 -- persons in the collapsed list of members for the Group
2063 --
2064 --
2065 -- Version: Current Version 1.0
2066 -- Previous Version : None
2067 -- Notes :
2068 --
2069 -- END OF comments
2070 ------------------------------------------------------------------------
2071
2072 l_api_name CONSTANT VARCHAR2(30) := 'GET_EMAIL_ADDRESS';
2073 -- On addition of any Required parameters the major version needs
2074 -- to change i.e. for eg. 1.X to 2.X.
2075 -- On addition of any Optional parameters the minor version needs
2076 -- to change i.e. for eg. X.6 to X.7.
2077
2078 l_api_version CONSTANT NUMBER := 1.0;
2079
2080 -- General variables
2081 l_party_type VARCHAR2(20); -- PERSON / GROUP
2082 l_email_address VARCHAR2(500);
2083 l_concat_email_addresses VARCHAR2(32767);
2084
2085
2086
2087 l_revision_id NUMBER;
2088 l_success BOOLEAN; --boolean for descr. flex valiation
2089 l_row_id VARCHAR2(20);
2090
2091 -- 4574359 this record type is not required
2092 -- l_person_rec HZ_PARTY_PUB.PERSON_REC_TYPE;
2093
2094 l_last_update_date DATE;
2095 l_party_rel_id NUMBER;
2096 l_party_id NUMBER;
2097 l_group_id NUMBER;
2098 l_party_number VARCHAR2(500); --my wild assumed length
2099 l_relationship_id NUMBER;
2100
2101 --output variables for the HZ_PARTY_PUB.Create_Person call, which
2102 --need not be passed back to the Calling procedure.
2103 x_main_id NUMBER;
2104 x_profile_id NUMBER;
2105 x_party_number HZ_PARTIES.party_number%TYPE;
2106
2107 CURSOR c_grp_member_emailaddr (cp_group_id IN NUMBER) IS
2108 SELECT member.email_address
2109 FROM hz_relationships grp_rel,
2110 EGO_PEOPLE_V member
2111 WHERE grp_rel.object_id = cp_group_id
2112 AND grp_rel.object_type = 'GROUP'
2113 AND grp_rel.relationship_type = 'MEMBERSHIP'
2114 AND grp_rel.status = 'A'
2115 AND grp_rel.start_date <= SYSDATE
2116 AND NVL(grp_rel.end_date, SYSDATE) >= SYSDATE
2117 AND grp_rel.subject_type = 'PERSON'
2118 AND grp_rel.subject_id = member.person_id;
2119
2120 BEGIN
2121 -- check if all required parameters are passed to the procedure
2122 IF (p_api_version IS NULL
2123 OR p_party_id IS NULL
2124 ) THEN
2125 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
2126 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
2127 fnd_msg_pub.add;
2128 x_return_status := FND_API.G_RET_STS_ERROR;
2129 x_msg_count := 1;
2130 fnd_msg_pub.Count_And_Get
2131 (p_count => x_msg_count
2132 ,p_data => x_msg_data
2133 );
2134 RETURN;
2135 END IF;
2136 -- Standard Start of API savepoint
2137 IF FND_API.TO_BOOLEAN(p_commit) THEN
2138 SAVEPOINT EGO_GET_EMAIL_ADDRESS;
2139 END IF;
2140
2141 mdebug('GET_EMAIL_ADDRESSES: ....1......');
2142
2143 IF NOT FND_API.Compatible_API_Call (l_api_version,
2144 p_api_version,
2145 l_api_name,
2146 G_PKG_NAME)
2147 THEN
2148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2149 END IF;
2150 -- Initialize API message list if necessary.
2151 -- Initialize message list if p_init_msg_list is set to TRUE.
2152 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
2153 FND_MSG_PUB.initialize;
2154 END IF;
2155
2156 mdebug('GET_EMAIL_ADDRESSES: selecting party type ');
2157 SELECT party_type
2158 INTO l_party_type
2159 FROM hz_parties
2160 WHERE party_id = p_party_id;
2161
2162 mdebug('GET_EMAIL_ADDRESSES: party type selected as ' || l_party_type );
2163 IF (l_party_type = 'PERSON') THEN
2164
2165 SELECT email_address
2166 INTO l_concat_email_addresses
2167 FROM ego_people_v
2168 WHERE person_id = p_party_id;
2169 mdebug('GET_EMAIL_ADDRESSES: person email address is ' || l_concat_email_addresses);
2170
2171 ELSIF (l_party_type = 'GROUP') THEN
2172
2173 --Gathering the Groupmember Persons email addresses.
2174 OPEN c_grp_member_emailaddr ( cp_group_id => p_party_id );
2175 LOOP FETCH c_grp_member_emailaddr INTO l_email_address;
2176 EXIT WHEN c_grp_member_emailaddr%NOTFOUND;
2177 l_concat_email_addresses := l_concat_email_addresses || l_email_address||', ';
2178 mdebug('GET_EMAIL_ADDRESSES: inside loop -- email address is ' || l_email_address);
2179 END LOOP;
2180 CLOSE c_grp_member_emailaddr;
2181
2182 -- Removing the final ','
2183 l_concat_email_addresses := Substr(l_concat_email_addresses,
2184 1,
2185 Length(l_concat_email_addresses)-2
2186 );
2187 ELSE -- neither PERSON nor GROUP
2188 FND_MESSAGE.Set_Name('EGO', 'EGO_INVALID_PARTY_TYPE');
2189 FND_MSG_PUB.Add;
2190 RAISE fnd_api.g_EXC_ERROR;
2191 END IF;
2192
2193 --finally assign prepared e-mail list to the OUT parameter.
2194 x_email_address := l_concat_email_addresses;
2195 mdebug('GET_EMAIL_ADDRESSES: ' || l_party_type||'''s Email address : '|| l_concat_email_addresses);
2196 mdebug('GET_EMAIL_ADDRESSES: x_return_status '|| x_return_status);
2197 mdebug('GET_EMAIL_ADDRESSES: x_msg_data ' || x_msg_data);
2198
2199 -- Standard check of p_commit.
2200 IF FND_API.To_Boolean( NVL(p_commit, 'F') ) THEN
2201 COMMIT WORK;
2202 END IF;
2203
2204 x_return_status := FND_API.G_RET_STS_SUCCESS;
2205
2206 -- Standard call to get message count and if count is 1,
2207 -- get message info.
2208 -- The client will directly display the x_msg_data (which is already
2209 -- translated) if the x_msg_count = 1;
2210 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2211 -- Server-side procedure to access the messages, and consolidate them
2212 -- and display (or) to display one message after another.
2213 FND_MSG_PUB.Count_And_Get
2214 ( p_count => x_msg_count,
2215 p_data => x_msg_data
2216 );
2217
2218 EXCEPTION
2219 WHEN FND_API.G_EXC_ERROR THEN
2220 IF FND_API.TO_BOOLEAN(p_commit) THEN
2221 ROLLBACK TO EGO_GET_EMAIL_ADDRESS;
2222 END IF;
2223 mdebug('GET_EMAIL_ADDRESSES: Ending : Returning FND_API.G_EXC_ERROR ');
2224 x_return_status := FND_API.G_RET_STS_ERROR;
2225 FND_MSG_PUB.Count_And_Get
2226 (p_count => x_msg_count,
2227 p_data => x_msg_data
2228 );
2229 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2230 IF FND_API.TO_BOOLEAN(p_commit) THEN
2231 ROLLBACK TO EGO_GET_EMAIL_ADDRESS;
2232 END IF;
2233 mdebug('GET_EMAIL_ADDRESSES: Ending : FND_API.G_EXC_UNEXPECTED_ERROR ');
2234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2235 FND_MSG_PUB.Count_And_Get
2236 ( p_count => x_msg_count,
2237 p_data => x_msg_data
2238 );
2239 WHEN OTHERS THEN
2240 IF FND_API.TO_BOOLEAN(p_commit) THEN
2241 ROLLBACK TO EGO_GET_EMAIL_ADDRESS;
2242 END IF;
2243 mdebug('GET_EMAIL_ADDRESSES: Ending : Returning UNEXPECTED ERROR');
2244 IF c_grp_member_emailaddr%ISOPEN THEN
2245 CLOSE c_grp_member_emailaddr;
2246 END IF;
2247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2248 IF FND_MSG_PUB.Check_Msg_Level
2249 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2250 THEN
2251 FND_MSG_PUB.Add_Exc_Msg
2252 ( G_PKG_NAME,
2253 l_api_name
2254 );
2255 END IF;
2256 FND_MSG_PUB.Count_And_Get
2257 ( p_count => x_msg_count,
2258 p_data => x_msg_data
2259 );
2260 END Get_Email_Address;
2261
2262
2263 PROCEDURE create_code_assignment (
2264 p_api_version IN NUMBER,
2265 p_init_msg_list IN VARCHAR2,
2266 p_commit IN VARCHAR2,
2267 p_party_id IN NUMBER,
2268 p_category IN VARCHAR2,
2269 p_code IN VARCHAR2,
2270 x_msg_count OUT NOCOPY NUMBER,
2271 x_return_status OUT NOCOPY VARCHAR2,
2272 x_msg_data OUT NOCOPY VARCHAR2,
2273 x_assignment_id OUT NOCOPY NUMBER
2274 ) IS
2275 ------------------------------------------------------------------------
2276 -- Start of comments
2277 -- API name : create_code_assignment
2278 -- TYPE : Public
2279 -- Pre-reqs : None
2280 -- FUNCTION : Get Email Address.
2281 -- Then intention is to Get all e-mail addresses of the
2282 -- persons in the collapsed list of members for the Group
2283 --
2284 --
2285 -- Version: Current Version 1.0
2286 -- Previous Version : None
2287 -- Notes :
2288 --
2289 -- END OF comments
2290 ------------------------------------------------------------------------
2291
2292 l_class_count NUMBER;
2293 l_code_assignment_rec HZ_CLASSIFICATION_V2PUB.CODE_ASSIGNMENT_REC_TYPE;
2294 -- On addition of any Required parameters the major version needs
2295 -- to change i.e. for eg. 1.X to 2.X.
2296 -- On addition of any Optional parameters the minor version needs
2297 -- to change i.e. for eg. X.6 to X.7.
2298
2299 l_api_version CONSTANT NUMBER := 1.0;
2300 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CODE_ASSIGNMENT';
2301 BEGIN
2302
2303 -- check if all required parameters are passed to the procedure
2304 IF (p_api_version IS NULL
2305 OR p_party_id IS NULL
2306 ) THEN
2307 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
2308 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
2309 fnd_msg_pub.add;
2310 x_return_status := FND_API.G_RET_STS_ERROR;
2311 x_msg_count := 1;
2312 fnd_msg_pub.Count_And_Get
2313 (p_count => x_msg_count
2314 ,p_data => x_msg_data
2315 );
2316 RETURN;
2317 END IF;
2318 -- Standard Start of API savepoint
2319 IF FND_API.TO_BOOLEAN(p_commit) THEN
2320 SAVEPOINT EGO_CREATE_CODE_ASSIGNMENT;
2321 END IF;
2322
2323 mdebug('CREATE_CODE_ASSIGNMENT: ....1......');
2324
2325 IF NOT FND_API.Compatible_API_Call (l_api_version,
2326 p_api_version,
2327 l_api_name,
2328 G_PKG_NAME)
2329 THEN
2330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2331 END IF;
2332 -- Initialize API message list if necessary.
2333 -- Initialize message list if p_init_msg_list is set to TRUE.
2334 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
2335 FND_MSG_PUB.initialize;
2336 END IF;
2337
2338
2339 SELECT COUNT(*)
2340 INTO l_class_count
2341 FROM hz_code_assignments
2342 WHERE owner_table_name = 'HZ_PARTIES'
2343 AND owner_table_id = p_party_id
2344 AND class_category = p_category;
2345
2346 IF ( l_class_count > 0 ) THEN
2347 x_return_status := 'S';
2348 RETURN;
2349 END IF;
2350
2351 l_code_assignment_rec.owner_table_name := OWNER_TABLE_NAME;
2352 l_code_assignment_rec.owner_table_id := p_party_id;
2353 l_code_assignment_rec.class_category := p_category;
2354 l_code_assignment_rec.class_code := p_code;
2355 l_code_assignment_rec.primary_flag := PRIMARY_FLAG;
2356 l_code_assignment_rec.content_source_type := CONTENT_SOURCE_TYPE;
2357 l_code_assignment_rec.start_date_active := SYSDATE;
2358 l_code_assignment_rec.status := ACTIVE_STATUS;
2359 l_code_assignment_rec.created_by_module := CREATED_BY_MODULE;
2360 l_code_assignment_rec.application_id := APPLICATION_ID;
2361
2362 HZ_CLASSIFICATION_V2PUB.create_code_assignment
2363 (
2364 FND_API.G_FALSE,
2365 l_code_assignment_rec,
2366 x_return_status,
2367 x_msg_count,
2368 x_msg_data,
2369 x_assignment_id
2370 );
2371
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374 IF FND_API.TO_BOOLEAN(p_commit) THEN
2375 ROLLBACK TO EGO_CREATE_CODE_ASSIGNMENT;
2376 END IF;
2377 x_return_status := 'F';
2378 END create_code_assignment;
2379
2380
2381 PROCEDURE update_code_assignment (
2382 p_api_version IN NUMBER,
2383 p_init_msg_list IN VARCHAR2,
2384 p_commit IN VARCHAR2,
2385 p_party_id IN NUMBER,
2386 p_category IN VARCHAR2,
2387 p_code IN VARCHAR2,
2388 x_return_status OUT NOCOPY VARCHAR2,
2389 x_msg_count OUT NOCOPY NUMBER,
2390 x_msg_data OUT NOCOPY VARCHAR2
2391 ) IS
2392 ------------------------------------------------------------------------
2393 -- Start of comments
2394 -- API name : create_code_assignment
2395 -- TYPE : Public
2396 -- Pre-reqs : None
2397 -- FUNCTION : Get Email Address.
2398 -- Then intention is to Get all e-mail addresses of the
2399 -- persons in the collapsed list of members for the Group
2400 --
2401 --
2402 -- Version: Current Version 1.0
2403 -- Previous Version : None
2404 -- Notes :
2405 --
2406 -- END OF comments
2407 ------------------------------------------------------------------------
2408 l_class_count NUMBER;
2409 l_assignment_id NUMBER;
2410 l_version_number NUMBER;
2411 l_code_assignment_rec HZ_CLASSIFICATION_V2PUB.CODE_ASSIGNMENT_REC_TYPE;
2412
2413 -- On addition of any Required parameters the major version needs
2414 -- to change i.e. for eg. 1.X to 2.X.
2415 -- On addition of any Optional parameters the minor version needs
2416 -- to change i.e. for eg. X.6 to X.7.
2417
2418 l_api_version CONSTANT NUMBER := 1.0;
2419 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CODE_ASSIGNMENT';
2420
2421 BEGIN
2422
2423 -- check if all required parameters are passed to the procedure
2424 IF (p_api_version IS NULL
2425 OR p_party_id IS NULL
2426 ) THEN
2427 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
2428 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
2429 fnd_msg_pub.add;
2430 x_return_status := FND_API.G_RET_STS_ERROR;
2431 x_msg_count := 1;
2432 fnd_msg_pub.Count_And_Get
2433 (p_count => x_msg_count
2434 ,p_data => x_msg_data
2435 );
2436 RETURN;
2437 END IF;
2438 -- Standard Start of API savepoint
2439 IF FND_API.TO_BOOLEAN(p_commit) THEN
2440 ROLLBACK TO EGO_UPDATE_CODE_ASSIGNMENT;
2441 END IF;
2442
2443 mdebug('UPDATE_CODE_ASSIGNMENT: ....1......');
2444
2445 IF NOT FND_API.Compatible_API_Call (l_api_version,
2446 p_api_version,
2447 l_api_name,
2448 G_PKG_NAME)
2449 THEN
2450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2451 END IF;
2452 -- Initialize API message list if necessary.
2453 -- Initialize message list if p_init_msg_list is set to TRUE.
2454 IF FND_API.to_Boolean( NVL(p_init_msg_list, 'F') ) THEN
2455 FND_MSG_PUB.initialize;
2456 END IF;
2457
2458
2459 SELECT code_assignment_id, object_version_number
2460 INTO l_assignment_id, l_version_number
2461 FROM hz_code_assignments
2462 WHERE owner_table_name = 'HZ_PARTIES'
2463 AND owner_table_id = p_party_id
2464 AND class_category = p_category;
2465
2466 l_code_assignment_rec.owner_table_name := OWNER_TABLE_NAME;
2467 l_code_assignment_rec.owner_table_id := p_party_id;
2468 l_code_assignment_rec.class_category := p_category;
2469 l_code_assignment_rec.class_code := p_code;
2470 l_code_assignment_rec.code_assignment_id := l_assignment_id;
2471 l_code_assignment_rec.primary_flag := PRIMARY_FLAG;
2472 l_code_assignment_rec.content_source_type := CONTENT_SOURCE_TYPE;
2473 l_code_assignment_rec.start_date_active := SYSDATE;
2474 l_code_assignment_rec.status := ACTIVE_STATUS;
2475 l_code_assignment_rec.created_by_module := CREATED_BY_MODULE;
2476 l_code_assignment_rec.application_id := APPLICATION_ID;
2477
2478 HZ_CLASSIFICATION_V2PUB.update_code_assignment
2479 (
2480 FND_API.G_FALSE,
2481 l_code_assignment_rec,
2482 l_version_number,
2483 x_return_status,
2484 x_msg_count,
2485 x_msg_data
2486 );
2487
2488 EXCEPTION
2489 WHEN OTHERS THEN
2490 x_return_status := 'F';
2491 END update_code_assignment
2492 ;
2493
2494 PROCEDURE setup_enterprise_user(p_company_id IN NUMBER
2495 ,x_return_status OUT NOCOPY VARCHAR2
2496 ,x_msg_count OUT NOCOPY NUMBER
2497 ,x_msg_data OUT NOCOPY VARCHAR2
2498 ) IS
2499 ------------------------------------------------------------------------
2500 -- Start of comments
2501 -- API name : setup_enterprise_user
2502 -- TYPE : Public
2503 -- Previous Version : None
2504 -- END OF comments
2505 ------------------------------------------------------------------------
2506 l_party_id NUMBER;
2507 l_request_id NUMBER;
2508 l_api_name VARCHAR2(30) := 'SETUP_ENTERPRISE_USER';
2509 l_run_cp BOOLEAN := FALSE;
2510 BEGIN
2511 setGlobals();
2512 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2513 ,p_module => l_api_name
2514 ,p_message => 'Started with 4 params: company_id: '||p_company_id
2515 );
2516 x_return_status := FND_API.G_RET_STS_SUCCESS;
2517 x_msg_count := 0;
2518 x_msg_data := NULL;
2519 SELECT hca.owner_table_id
2520 INTO l_party_id
2521 FROM hz_code_assignments hca
2522 WHERE hca.owner_table_id = p_company_id
2523 AND hca.owner_table_name = 'HZ_PARTIES'
2524 AND hca.class_category = 'POS_PARTICIPANT_TYPE'
2525 AND hca.class_code = 'ENTERPRISE';
2526
2527 BEGIN
2528
2529 -- Bug 9652538 - modified query to also pick up contingent workers
2530 -- based on the profile option 'HR_TREAT_CWK_AS_EMP'
2531 SELECT hr_employee.party_id person_id
2532 INTO l_party_id
2533 FROM fnd_user fnd_user, per_all_people_f hr_employee
2534 WHERE fnd_user.EMPLOYEE_ID = hr_employee.PERSON_ID
2535 AND fnd_user.person_party_id = hr_employee.party_id
2536 AND fnd_user.start_date <= SYSDATE
2537 AND NVL(fnd_user.end_date, SYSDATE) >= SYSDATE
2538 AND (hr_employee.CURRENT_EMPLOYEE_FLAG = 'Y'
2539 OR
2540 (fnd_profile.value('HR_TREAT_CWK_AS_EMP') = 'Y' AND
2541 hr_employee.current_npw_flag = 'Y')
2542 )
2543 AND hr_employee.EFFECTIVE_START_DATE <= SYSDATE
2544 AND NVL(hr_employee.EFFECTIVE_END_DATE,SYSDATE) >= SYSDATE
2545 AND NOT EXISTS
2546 (SELECT null
2547 FROM hz_relationships emp_cmpy
2548 WHERE emp_cmpy.relationship_code = 'EMPLOYEE_OF'
2549 AND emp_cmpy.subject_type = 'PERSON'
2550 AND emp_cmpy.subject_id = hr_employee.PARTY_ID
2551 AND emp_cmpy.object_type = 'ORGANIZATION'
2552 AND NVL(emp_cmpy.start_date,SYSDATE) <= SYSDATE
2553 AND NVL(emp_cmpy.end_date,SYSDATE) >= SYSDATE
2554 );
2555 l_run_cp := TRUE;
2556 EXCEPTION
2557 WHEN NO_DATA_FOUND THEN
2558 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2559 ,p_module => l_api_name
2560 ,p_message => 'No Users to process '
2561 );
2562 RETURN;
2563 WHEN OTHERS THEN
2564 -- users exist
2565 l_run_cp := TRUE;
2566 END;
2567 IF l_run_cp THEN
2568 l_request_id := FND_REQUEST.Submit_Request
2569 (application => 'EGO'
2570 ,program => 'EGOPRTYSTUP'
2571 );
2572 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2573 ,p_module => l_api_name
2574 ,p_message => 'Submitted concurrent request: '||l_request_id
2575 );
2576 END IF;
2577 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2578 ,p_module => l_api_name
2579 ,p_message => 'Completed'
2580 );
2581 EXCEPTION
2582 WHEN NO_DATA_FOUND THEN
2583 code_debug (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2584 ,p_module => l_api_name
2585 ,p_message => 'The organization is not Enterprise organization '
2586 );
2587 RETURN;
2588 WHEN OTHERS THEN
2589 code_debug (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2590 ,p_module => l_api_name
2591 ,p_message => 'EXCEPTION '||SQLERRM
2592 );
2593 x_return_status := FND_API.G_RET_STS_ERROR;
2594 x_msg_count := 1;
2595 x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
2596 END setup_enterprise_user;
2597
2598
2599 PROCEDURE setup_enterprise_user_cp
2600 (x_errbuff OUT NOCOPY VARCHAR2
2601 ,x_retcode OUT NOCOPY VARCHAR2
2602 ) IS
2603 ------------------------------------------------------------------------
2604 -- Start of comments
2605 -- API name : setup_enterprise_user_cp
2606 -- TYPE : Public
2607 -- Previous Version : None
2608 -- END OF comments
2609 ------------------------------------------------------------------------
2610 l_api_name VARCHAR2(30) := 'SETUP_ENTERPRISE_USER_CP';
2611 l_api_version NUMBER := 1.0;
2612 l_return_status VARCHAR2(1);
2613 l_msg_data VARCHAR2(1000);
2614 l_party_id NUMBER;
2615 l_relationship_id NUMBER;
2616 l_org_id NUMBER;
2617 l_msg_count NUMBER;
2618
2619 --changed query to remove full table scan. Bug#4429524
2620 --modified the query to reduce the cost bug 4895705
2621 -- Bug 9652538 - modified query to also pick up contingent workers
2622 -- based on the profile option 'HR_TREAT_CWK_AS_EMP'
2623 CURSOR internal_users_wc IS
2624 SELECT hr_employee.party_id person_id
2625 FROM fnd_user fnd_user, per_all_people_f hr_employee
2626 WHERE fnd_user.employee_id = hr_employee.person_id
2627 AND fnd_user.person_party_id = hr_employee.party_id
2628 AND fnd_user.start_date <= SYSDATE
2629 AND NVL(fnd_user.end_date, SYSDATE) >= SYSDATE
2630 AND (hr_employee.current_employee_flag = 'Y'
2631 OR
2632 (fnd_profile.value('HR_TREAT_CWK_AS_EMP') = 'Y' AND
2633 hr_employee.current_npw_flag = 'Y')
2634 )
2635 AND hr_employee.effective_start_date <= SYSDATE
2636 AND NVL(hr_employee.effective_end_date,SYSDATE) >= SYSDATE
2637 AND NOT EXISTS
2638 (SELECT NULL
2639 FROM hz_relationships emp_cmpy
2640 WHERE emp_cmpy.relationship_code = 'EMPLOYEE_OF'
2641 AND emp_cmpy.subject_type = 'PERSON'
2642 AND emp_cmpy.subject_id = hr_employee.party_id
2643 AND emp_cmpy.object_type = 'ORGANIZATION'
2644 AND NVL(emp_cmpy.start_date,SYSDATE) <= SYSDATE
2645 AND NVL(emp_cmpy.end_date,SYSDATE) >= SYSDATE
2646 );
2647
2648 BEGIN
2649
2650 SELECT hp.party_id
2651 INTO l_org_id
2652 FROM hz_parties hp, hz_code_assignments hca
2653 WHERE hca.owner_table_id = hp.party_id
2654 AND hca.owner_table_name = 'HZ_PARTIES'
2655 AND hca.class_category = 'POS_PARTICIPANT_TYPE'
2656 AND hca.class_code = 'ENTERPRISE'
2657 AND hp.status = 'A';
2658
2659 FND_FILE.put_line(which => fnd_file.log
2660 ,buff => '['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')
2661 ||'] Default Enterprise id '||l_org_id);
2662
2663 FOR user_rec IN INTERNAL_USERS_WC LOOP
2664 BEGIN
2665 Create_Relationship (
2666 p_api_version => l_api_version,
2667 p_init_msg_list => FND_API.G_FALSE,
2668 p_commit => FND_API.G_FALSE,
2669 p_subject_id => user_rec.PERSON_ID,
2670 p_subject_type => 'PERSON',
2671 p_subject_table_name => 'HZ_PARTIES',
2672 p_object_id => l_org_id,
2673 p_object_type => 'ORGANIZATION',
2674 p_object_table_name => 'HZ_PARTIES',
2675 p_relationship_code => 'EMPLOYEE_OF',
2676 p_relationship_type => 'POS_EMPLOYMENT',
2677 p_program_name => CREATED_BY_MODULE,
2678 p_start_date => SYSDATE,
2679 x_return_status => l_return_status,
2680 x_msg_count => l_msg_count,
2681 x_msg_data => l_msg_data,
2682 x_relationship_id => l_relationship_id);
2683 FND_FILE.put_line(which => fnd_file.log
2684 ,buff => '['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')
2685 ||'] Relationship created for '||user_rec.PERSON_ID
2686 ||' with status '||l_return_status
2687 ||' message '||l_msg_data
2688 );
2689 EXCEPTION
2690 WHEN OTHERS THEN
2691 FND_FILE.put_line(which => fnd_file.log
2692 ,buff => '['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')
2693 ||'] EXCEPTION in creating Relationship for '||user_rec.PERSON_ID
2694 ||' with error '||SQLERRM
2695 );
2696 NULL;
2697 END;
2698 END LOOP;
2699 COMMIT;
2700 EXCEPTION
2701 WHEN OTHERS THEN
2702 -- this will come only if there is not default enterprise org setup
2703 FND_FILE.put_line(which => fnd_file.log
2704 ,buff => '['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')
2705 ||'] EXCEPTION in getting default enterprise '
2706 ||' with error '||SQLERRM
2707 );
2708 NULL;
2709 END setup_enterprise_user_cp;
2710
2711
2712 END EGO_PARTY_PUB;
[Home] [Help]