[Home] [Help]
PACKAGE BODY: APPS.EDR_EVENT_RELATIONSHIP_PUB
Source
1 package body EDR_EVENT_RELATIONSHIP_PUB as
2 /* $Header: EDRPRELB.pls 120.0.12000000.1 2007/01/18 05:54:39 appldev ship $ */
3
4 -- Private Utility Functions --
5
6 -- Start of comments
7 -- API name : getWhoColumns
8 -- Type : Private Utility.
9 -- Function : Gets the WHO columns for inserting a row
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- OUT : creation_date out date
13 -- created_by out number
14 -- last_update_date out date
15 -- last_updated_by out number
16 -- last_update_login out number
17 --
18 -- End of comments
19
20 PROCEDURE getWhoColumns(creation_date out nocopy date,
21 created_by out nocopy number,
22 last_update_date out nocopy date,
23 last_updated_by out nocopy number,
24 last_update_login out nocopy number)
25 is
26 begin
27 creation_date := sysdate;
28 created_by := fnd_global.user_id();
29 last_update_date := sysdate;
30 last_updated_by := fnd_global.user_id();
31 last_update_login := fnd_global.login_id();
32
33 end getWhoColumns;
34
35 procedure CREATE_RELATIONSHIP
36 ( p_api_version IN NUMBER ,
37 p_init_msg_list IN VARCHAR2 ,
38 p_commit IN VARCHAR2 ,
39 p_validation_level IN NUMBER ,
40 x_return_status OUT NOCOPY VARCHAR2 ,
41 x_msg_count OUT NOCOPY NUMBER ,
42 x_msg_data OUT NOCOPY VARCHAR2 ,
43 P_PARENT_ERECORD_ID IN NUMBER ,
44 P_PARENT_EVENT_NAME IN VARCHAR2 ,
45 P_PARENT_EVENT_KEY IN VARCHAR2 ,
46 P_CHILD_ERECORD_ID IN NUMBER ,
47 P_CHILD_EVENT_NAME IN VARCHAR2 ,
48 P_CHILD_EVENT_KEY IN VARCHAR2 ,
49 X_RELATIONSHIP_ID OUT NOCOPY NUMBER
50 )
51 AS
52 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RELATIONSHIP';
53 l_api_version CONSTANT NUMBER := 1.0;
54
55 l_return_status VARCHAR2(1);
56 l_msg_count NUMBER;
57 l_msg_data VARCHAR2(2000);
58
59 L_CREATION_DATE DATE;
60 L_CREATED_BY NUMBER;
61 L_LAST_UPDATE_DATE DATE;
62 L_LAST_UPDATED_BY NUMBER;
63 L_LAST_UPDATE_LOGIN NUMBER;
64
65
66 BEGIN
67 -- Standard Start of API savepoint
68 SAVEPOINT CREATE_RELATIONSHIP_PUB;
69
70 -- Standard call to check for call compatibility.
71 IF NOT FND_API.Compatible_API_Call (l_api_version ,
72 p_api_version ,
73 l_api_name ,
74 G_PKG_NAME )
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78
79 -- Initialize message list if p_init_msg_list is set to TRUE.
80 IF FND_API.to_Boolean( p_init_msg_list ) THEN
81 FND_MSG_PUB.initialize;
82 END IF;
83
84 -- Initialize API return status to success
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 -- API Body
88
89 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
90 VALIDATE_RELATIONSHIP
91 ( p_api_version => 1.0,
92 x_return_status => l_return_status,
93 x_msg_count => l_msg_count,
94 x_msg_data => l_msg_data,
95 P_PARENT_ERECORD_ID => p_parent_erecord_id,
96 P_PARENT_EVENT_NAME => p_parent_event_name,
97 P_PARENT_EVENT_KEY => p_parent_event_key,
98 P_CHILD_ERECORD_ID => p_child_erecord_id,
99 P_CHILD_EVENT_NAME => p_child_event_name,
100 P_CHILD_EVENT_KEY => p_child_event_key
101 );
102
103 -- If any errors happen abort API.
104 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
106 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
107 RAISE FND_API.G_EXC_ERROR;
108 END IF;
109
110 END IF; --end of validation
111
112 -- after all the validations are done, insert the row in the
113 -- database
114
115 select edr_event_relationship_s.nextval into x_relationship_id
116 from dual;
117
118 getWhoColumns( l_creation_date,
119 l_created_by,
120 l_last_update_date,
121 l_last_updated_by,
122 l_last_update_login);
123
124 insert into EDR_EVENT_RELATIONSHIP(
125 RELATIONSHIP_ID
126 ,PARENT_EVENT_NAME
127 ,PARENT_EVENT_KEY
128 ,PARENT_ERECORD_ID
129 ,CHILD_EVENT_NAME
130 ,CHILD_EVENT_KEY
131 ,CHILD_ERECORD_ID
132 ,CREATION_DATE
133 ,CREATED_BY
134 ,LAST_UPDATE_DATE
135 ,LAST_UPDATED_BY
136 ,LAST_UPDATE_LOGIN
137 ) values (
138 X_RELATIONSHIP_ID
139 ,P_PARENT_EVENT_NAME
140 ,P_PARENT_EVENT_KEY
141 ,P_PARENT_ERECORD_ID
142 ,P_CHILD_EVENT_NAME
143 ,P_CHILD_EVENT_KEY
144 ,P_CHILD_ERECORD_ID
145 ,L_CREATION_DATE
146 ,L_CREATED_BY
147 ,L_LAST_UPDATE_DATE
148 ,L_LAST_UPDATED_BY
149 ,L_LAST_UPDATE_LOGIN
150 );
151
152 -- End of API Body
153
154 -- Standard check of p_commit.
155 IF FND_API.To_Boolean( p_commit ) THEN
156 COMMIT;
157 END IF;
158
159 -- Standard call to get message count and if count is 1,
160 --get message info.
161 FND_MSG_PUB.Count_And_Get
162 ( p_count => x_msg_count ,
163 p_data => x_msg_data
164 );
165
166 EXCEPTION
167 WHEN FND_API.G_EXC_ERROR THEN
168 x_return_status := FND_API.G_RET_STS_ERROR ;
169 FND_MSG_PUB.Count_And_Get
170 ( p_count => x_msg_count ,
171 p_data => x_msg_data
172 );
173
174 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
175 ROLLBACK TO CREATE_RELATIONSHIP_PUB;
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
177 FND_MSG_PUB.Count_And_Get
178 ( p_count => x_msg_count ,
179 p_data => x_msg_data
180 );
181
182 WHEN OTHERS THEN
183 ROLLBACK TO CREATE_RELATIONSHIP_PUB;
184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
185 x_relationship_id := null;
186
187 IF FND_MSG_PUB.Check_Msg_Level
188 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
189 THEN
190 FND_MSG_PUB.Add_Exc_Msg
191 ( G_PKG_NAME ,
192 l_api_name
193 );
194 END IF;
195
196 FND_MSG_PUB.Count_And_Get
197 (p_count => x_msg_count ,
198 p_data => x_msg_data
199 );
200
201
202 END CREATE_RELATIONSHIP;
203
204 PROCEDURE VALIDATE_RELATIONSHIP
205 ( p_api_version IN NUMBER ,
206 p_init_msg_list IN VARCHAR2 ,
207 x_return_status OUT NOCOPY VARCHAR2 ,
208 x_msg_count OUT NOCOPY NUMBER ,
209 x_msg_data OUT NOCOPY VARCHAR2 ,
210 P_PARENT_ERECORD_ID IN NUMBER ,
211 P_PARENT_EVENT_NAME IN VARCHAR2 ,
212 P_PARENT_EVENT_KEY IN VARCHAR2 ,
213 P_CHILD_ERECORD_ID IN NUMBER ,
214 P_CHILD_EVENT_NAME IN VARCHAR2 ,
215 P_CHILD_EVENT_KEY IN VARCHAR2
216 )
217 AS
218 l_mesg_text VARCHAR2(2000);
219
220 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RELATIONSHIP';
221 l_api_version CONSTANT NUMBER := 1.0;
222
223 l_return_status VARCHAR2(1);
224 l_msg_count NUMBER;
225 l_msg_data VARCHAR2(2000);
226
227 l_event_name VARCHAR2(80);
228 l_event_key VARCHAR2(240);
229
230 PARENT_ERECORD_ID_ERROR EXCEPTION;
231 PARENT_EVENT_ERROR EXCEPTION;
232 INVALID_EVENT_ERROR EXCEPTION;
233 CHILD_ERECORD_ID_ERROR EXCEPTION;
234 CHILD_EVENT_ERROR EXCEPTION;
235 BEGIN
236 -- Standard call to check for call compatibility.
237 IF NOT FND_API.Compatible_API_Call (l_api_version ,
238 p_api_version ,
239 l_api_name ,
240 G_PKG_NAME )
241 THEN
242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
243 END IF;
244
245 -- Initialize message list if p_init_msg_list is set to TRUE.
246 IF FND_API.to_Boolean( p_init_msg_list ) THEN
247 FND_MSG_PUB.initialize;
248 END IF;
249
250 -- Initialize API return status to success
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252
253 -- API Body
254
255 /* SKARIMIS Bug fix 3134883 */
256 if ( p_child_event_name = p_parent_event_name
257 and p_Child_event_key = p_parent_event_key) then
258 RAISE INVALID_EVENT_ERROR;
259 end if;
260 /* ENd of Bug Fix */
261
262 -- validate the parent erecord id
263 EDR_ERES_EVENT_PUB.VALIDATE_ERECORD
264 ( p_api_version => 1.0 ,
265 x_return_status => l_return_status ,
266 x_msg_count => l_msg_count ,
267 x_msg_data => l_msg_data ,
268 p_erecord_id => p_parent_erecord_id
269 );
270
271 -- If any errors happen abort API.
272 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
275 RAISE PARENT_ERECORD_ID_ERROR;
276 END IF;
277
278 -- Now validate that the parent event name and event key are
279 -- valid for the parent erecord id
280 EDR_ERES_EVENT_PUB.GET_EVENT_DETAILS
281 ( p_api_version => 1.0 ,
282 x_return_status => l_return_status ,
283 x_msg_count => l_msg_count ,
284 x_msg_data => l_msg_data ,
285 p_erecord_id => p_parent_erecord_id ,
286 x_event_name => l_event_name ,
287 x_event_key => l_event_key
288 );
289
290 if ( l_event_name <> p_parent_event_name
291 OR l_event_key <> p_parent_event_key) then
292 RAISE PARENT_EVENT_ERROR;
293 end if;
294
295 -- validate the child erecord id
296 EDR_ERES_EVENT_PUB.VALIDATE_ERECORD
297 ( p_api_version => 1.0 ,
298 x_return_status => l_return_status ,
299 x_msg_count => l_msg_count ,
300 x_msg_data => l_msg_data ,
301 p_erecord_id => p_child_erecord_id
302 );
303
304 -- If any errors happen abort API.
305 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
307 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
308 RAISE CHILD_ERECORD_ID_ERROR;
309 END IF;
310
311 -- Now validate that the child event name and event key are
312 -- valid for the child erecord id
313 EDR_ERES_EVENT_PUB.GET_EVENT_DETAILS
314 ( p_api_version => 1.0 ,
315 x_return_status => l_return_status ,
316 x_msg_count => l_msg_count ,
317 x_msg_data => l_msg_data ,
318 p_erecord_id => p_child_erecord_id ,
319 x_event_name => l_event_name ,
320 x_event_key => l_event_key
321 );
322
323 if ( l_event_name <> p_child_event_name
324 OR l_event_key <> p_child_event_key) then
325 RAISE CHILD_EVENT_ERROR;
326 end if;
327
328 -- End of API Body
329
330 -- Standard call to get message count and if count is 1,
331 --get message info.
332 FND_MSG_PUB.Count_And_Get
333 ( p_count => x_msg_count ,
334 p_data => x_msg_data
335 );
336
337 EXCEPTION
338 WHEN PARENT_ERECORD_ID_ERROR THEN
339 x_return_status := FND_API.G_RET_STS_ERROR ;
340
341 fnd_message.set_name('EDR','EDR_VAL_INVALID_PARENT_ID');
342 fnd_message.set_token('ERECORD_ID', p_parent_erecord_id);
343 fnd_message.set_token('EVENT_NAME', p_parent_event_name);
344 fnd_message.set_token('EVENT_KEY', p_parent_event_key);
345
346 l_mesg_text := fnd_message.get();
347
348 FND_MSG_PUB.Add_Exc_Msg
349 ( G_PKG_NAME ,
350 l_api_name ,
351 l_mesg_text
352 );
353
354 FND_MSG_PUB.Count_And_Get
355 ( p_count => x_msg_count ,
356 p_data => x_msg_data
357 );
358
359
360 WHEN INVALID_EVENT_ERROR THEN
361 x_return_status := FND_API.G_RET_STS_ERROR ;
362 fnd_message.set_name('EDR','EDR_VAL_INVALID_EVENT');
363 l_mesg_text := fnd_message.get();
364
365 FND_MSG_PUB.Add_Exc_Msg
366 ( G_PKG_NAME ,
367 l_api_name ,
368 l_mesg_text
369 );
370
371 FND_MSG_PUB.Count_And_Get
372 ( p_count => x_msg_count ,
373 p_data => x_msg_data
374 );
375 WHEN PARENT_EVENT_ERROR THEN
376 x_return_status := FND_API.G_RET_STS_ERROR ;
377
378 fnd_message.set_name('EDR','EDR_VAL_INVALID_PARENT_EVENT');
379 fnd_message.set_token('EVENT_NAME', l_event_name);
380 fnd_message.set_token('EVENT_KEY', l_event_key);
381 l_mesg_text := fnd_message.get();
382
383 FND_MSG_PUB.Add_Exc_Msg
384 ( G_PKG_NAME ,
385 l_api_name ,
386 l_mesg_text
387 );
388
389 FND_MSG_PUB.Count_And_Get
390 ( p_count => x_msg_count ,
391 p_data => x_msg_data
392 );
393
394 WHEN CHILD_ERECORD_ID_ERROR THEN
395 x_return_status := FND_API.G_RET_STS_ERROR ;
396
397 fnd_message.set_name('EDR','EDR_VAL_INVALID_CHILD_ID');
398 fnd_message.set_token('ERECORD_ID', p_child_erecord_id);
399 l_mesg_text := fnd_message.get();
400
401 FND_MSG_PUB.Add_Exc_Msg
402 ( G_PKG_NAME ,
403 l_api_name ,
404 l_mesg_text
405 );
406
407 FND_MSG_PUB.Count_And_Get
408 ( p_count => x_msg_count ,
409 p_data => x_msg_data
410 );
411
412
413 WHEN CHILD_EVENT_ERROR THEN
414 x_return_status := FND_API.G_RET_STS_ERROR ;
415
416 fnd_message.set_name('EDR','EDR_VAL_INVALID_CHILD_EVENT');
417 fnd_message.set_token('EVENT_NAME', l_event_name);
418 fnd_message.set_token('EVENT_KEY', l_event_key);
419 l_mesg_text := fnd_message.get();
420
421 FND_MSG_PUB.Add_Exc_Msg
422 ( G_PKG_NAME ,
423 l_api_name ,
424 l_mesg_text
425 );
426
427 FND_MSG_PUB.Count_And_Get
428 ( p_count => x_msg_count ,
429 p_data => x_msg_data
430 );
431
432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
434
435 FND_MSG_PUB.Count_And_Get
436 ( p_count => x_msg_count ,
437 p_data => x_msg_data
438 );
439
440 WHEN OTHERS THEN
441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
442
443 IF FND_MSG_PUB.Check_Msg_Level
444 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
445 THEN
446 FND_MSG_PUB.Add_Exc_Msg
447 ( G_PKG_NAME ,
448 l_api_name
449 );
450 END IF;
451
452 FND_MSG_PUB.Count_And_Get
453 (p_count => x_msg_count ,
454 p_data => x_msg_data
455 );
456
457 END VALIDATE_RELATIONSHIP;
458
459 end EDR_EVENT_RELATIONSHIP_PUB;