[Home] [Help]
PACKAGE BODY: APPS.PA_DISTRIBUTION_LISTS_PVT
Source
1 PACKAGE BODY PA_DISTRIBUTION_LISTS_PVT AS
2 /* $Header: PATDSLVB.pls 120.1 2005/08/19 17:03:55 mwasowic noship $ */
3 procedure CREATE_DIST_LIST (
4 p_api_version IN NUMBER := 1.0,
5 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
6 p_commit IN VARCHAR2 := FND_API.g_false,
7 p_validate_only IN VARCHAR2 := FND_API.g_true,
8 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
9 P_LIST_ID in OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
10 P_NAME in VARCHAR2,
11 P_DESCRIPTION in VARCHAR2,
12 P_RECORD_VERSION_NUMBER in NUMBER := 1,
13 P_CREATED_BY in NUMBER default fnd_global.user_id,
14 P_CREATION_DATE in DATE default sysdate,
15 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
16 P_LAST_UPDATE_DATE in DATE default sysdate,
17 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
18 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
19 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
20 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21 )
22 IS
23 l_error_msg_code varchar2(30);
24 BEGIN
25 IF p_commit = FND_API.G_TRUE
26 THEN
27 SAVEPOINT CREATE_DIST_LIST;
28 END IF;
29
30 x_return_status := 'S';
31
32 -- Validate the Input Values
33 If (PA_DISTRIBUTION_LIST_UTILS.Check_dist_list_name_exists(
34 p_list_name =>p_name) )
35 then
36 l_error_msg_code := 'PA_DL_NAME_NOT_UNIQUE';
37 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
38 p_msg_name => l_error_msg_code);
39
40 x_msg_data := l_error_msg_code;
41 x_msg_count := x_msg_count +1;
42 x_return_status := 'E';
43 RAISE FND_API.G_EXC_ERROR;
44 END IF;
45
46 -- Insert a row if no validation failure
47 If (x_return_status = fnd_api.g_ret_sts_success
48 AND p_validate_only <> fnd_api.g_true) then
49 PA_DISTRIBUTION_LISTS_PKG.INSERT_ROW (
50 P_LIST_ID => P_LIST_ID,
51 P_NAME => P_NAME,
52 P_DESCRIPTION => P_DESCRIPTION,
53 P_RECORD_VERSION_NUMBER => 1,
54 P_CREATED_BY => P_CREATED_BY,
55 P_CREATION_DATE => P_CREATION_DATE,
56 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
57 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
58 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ) ;
59 End if;
60 -- Commit the changes if requested
61 if (p_commit = FND_API.G_TRUE
62 AND x_return_status = fnd_api.g_ret_sts_success) then
63 commit;
64 end if;
65
66 EXCEPTION
67 WHEN FND_API.G_EXC_ERROR THEN
68 IF p_commit = FND_API.G_TRUE
69 THEN
70 ROLLBACK TO CREATE_DIST_LIST;
71 END IF;
72 x_return_status := 'E';
73
74 WHEN OTHERS THEN
75 IF p_commit = FND_API.G_TRUE
76 THEN
77 ROLLBACK TO CREATE_DIST_LIST;
78 END IF;
79 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
80 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_DISTRIBUTION_LISTS_PVT',
81 p_procedure_name => 'CREATE_DIST_LIST',
82 p_error_text => SUBSTRB(SQLERRM,1,240));
83 RAISE;
84
85 END CREATE_DIST_LIST;
86
87 procedure UPDATE_DIST_LIST (
88 p_api_version IN NUMBER := 1.0,
89 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
90 p_commit IN VARCHAR2 := FND_API.g_false,
91 p_validate_only IN VARCHAR2 := FND_API.g_true,
92 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
93 P_LIST_ID in NUMBER,
94 P_NAME in VARCHAR2,
95 P_DESCRIPTION in VARCHAR2,
96 P_RECORD_VERSION_NUMBER in NUMBER,
97 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
98 P_LAST_UPDATE_DATE in DATE default sysdate,
99 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
100 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
101 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
102 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
103 )
104 IS
105 Cursor check_record_changed IS
106 select rowid
107 from pa_distribution_lists
108 where list_id = p_list_id
109 and record_version_number = p_record_version_number
110 for update of list_id;
111
112 l_error_msg_code varchar2(30);
113 l_rowid rowid;
114
115 Begin
116 IF p_commit = FND_API.G_TRUE
117 THEN
118 SAVEPOINT UPDATE_DIST_LIST;
119 END IF;
120
121 x_return_status := fnd_api.g_ret_sts_success;
122
123 -- Validate the Input Values
124 If (PA_DISTRIBUTION_LIST_UTILS.Check_dist_list_name_exists(
125 p_list_id =>p_list_id
126 ,p_list_name =>p_name) )
127 then
128 l_error_msg_code := 'PA_DL_NAME_NOT_UNIQUE';
129 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
130 p_msg_name => l_error_msg_code);
131
132 x_msg_data := l_error_msg_code;
133 x_msg_count := x_msg_count +1;
134 x_return_status := 'E';
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137
138 -- Lock the Row
139 OPEN check_record_changed;
140 FETCH check_record_changed INTO l_rowid;
141 IF check_record_changed%NOTFOUND THEN
142 PA_UTILS.Add_Message( p_app_short_name => 'PA'
143 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 CLOSE check_record_changed;
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148 if (check_record_changed%ISOPEN) then
149 CLOSE check_record_changed;
150 end if;
151 -- Update row
152 If (x_return_status = FND_API.G_RET_STS_SUCCESS
153 AND p_validate_only <> fnd_api.g_true) then
154 PA_DISTRIBUTION_LISTS_PKG.UPDATE_ROW (
155 P_LIST_ID => P_LIST_ID,
156 P_NAME => P_NAME,
157 P_DESCRIPTION => P_DESCRIPTION,
158 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
159 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
160 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
161 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ) ;
162 End if;
163 -- Commit the changes if requested
164 if (p_commit = FND_API.G_TRUE
165 AND x_return_status = fnd_api.g_ret_sts_success) then
166 commit;
167 end if;
168
169 EXCEPTION
170 WHEN FND_API.G_EXC_ERROR THEN
171 IF p_commit = FND_API.G_TRUE
172 THEN
173 ROLLBACK TO UPDATE_DIST_LIST;
174 END IF;
175 x_return_status := 'E';
176
177 WHEN OTHERS THEN
178 IF p_commit = FND_API.G_TRUE
179 THEN
180 ROLLBACK TO UPDATE_DIST_LIST;
181 END IF;
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_DISTRIBUTION_LISTS_PVT',
184 p_procedure_name => 'UPDATE_DIST_LIST',
185 p_error_text => SUBSTRB(SQLERRM,1,240));
186 RAISE;
187
188 END UPDATE_DIST_LIST;
189
190
191 procedure DELETE_DIST_LIST (
192 p_api_version IN NUMBER := 1.0,
193 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
194 p_commit IN VARCHAR2 := FND_API.g_false,
195 p_validate_only IN VARCHAR2 := FND_API.g_true,
196 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
197 P_LIST_ID in NUMBER,
198 P_DELETE_LIST_ITEM_FLAG in VARCHAR2 default 'N',
199 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
200 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
201 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
202 )
203 IS
204 Begin
205 IF p_commit = FND_API.G_TRUE
206 THEN
207 SAVEPOINT DELETE_DIST_LIST;
208 END IF;
209
210 x_return_status := fnd_api.g_ret_sts_success;
211 -- Update row
212 If (x_return_status = FND_API.G_RET_STS_SUCCESS
213 AND p_validate_only <> fnd_api.g_true) then
214 PA_DISTRIBUTION_LISTS_PKG.DELETE_ROW (
215 P_LIST_ID => P_LIST_ID) ;
216 IF (P_DELETE_LIST_ITEM_FLAG = 'Y') then
217 Delete from PA_DIST_LIST_ITEMS
218 where list_id = p_list_id;
219 End if;
220 End if;
221 -- Commit the changes if requested
222 if (p_commit = FND_API.G_TRUE
223 AND x_return_status = fnd_api.g_ret_sts_success) then
224 commit;
225 end if;
226 EXCEPTION
227 WHEN FND_API.G_EXC_ERROR THEN
228 IF p_commit = FND_API.G_TRUE
229 THEN
230 ROLLBACK TO DELETE_DIST_LIST;
231 END IF;
232 x_return_status := 'E';
233
234 WHEN OTHERS THEN
235 IF p_commit = FND_API.G_TRUE
236 THEN
237 ROLLBACK TO DELETE_DIST_LIST;
238 END IF;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_DISTRIBUTION_LISTS_PVT',
241 p_procedure_name => 'DELETE_DIST_LIST',
242 p_error_text => SUBSTRB(SQLERRM,1,240));
243 RAISE;
244
245 End DELETE_DIST_LIST;
246
247 procedure CREATE_DIST_LIST_ITEM (
248 p_api_version IN NUMBER := 1.0,
249 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
250 p_commit IN VARCHAR2 := FND_API.g_false,
251 p_validate_only IN VARCHAR2 := FND_API.g_true,
252 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
253 P_LIST_ITEM_ID in OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
254 P_LIST_ID in NUMBER := null,
255 P_RECIPIENT_TYPE in VARCHAR2:= null,
256 P_RECIPIENT_ID in VARCHAR2:= null,
257 P_ACCESS_LEVEL in NUMBER:= null,
258 P_MENU_ID in NUMBER:= null,
259 P_EMAIL in VARCHAR2:= null,
260 P_RECORD_VERSION_NUMBER in NUMBER := 1,
261 P_CREATED_BY in NUMBER default fnd_global.user_id,
262 P_CREATION_DATE in DATE default sysdate,
263 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
264 P_LAST_UPDATE_DATE in DATE default sysdate,
265 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
266 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
267 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
268 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
269 )
270 IS
271 l_error_msg_code varchar2(30);
272 BEGIN
273 IF p_commit = FND_API.G_TRUE
274 THEN
275 SAVEPOINT CREATE_DIST_LIST_ITEM;
276 END IF;
277
278 x_return_status := fnd_api.g_ret_sts_success;
279 -- Validate the Input Values
280 -- Validate list_id
281 IF (p_list_id IS NULL OR
282 NOT pa_distribution_list_utils.Check_valid_dist_list_id(p_list_id)
283 ) then
284 PA_UTILS.Add_Message( p_app_short_name => 'PA'
285 ,p_msg_name => 'PA_DL_LIST_ID_INV');
286 x_return_status := FND_API.G_RET_STS_ERROR;
287 END IF;
288 -- Validate recipient type
289 IF (p_recipient_type IS NULL OR
290 NOT pa_distribution_list_utils.check_valid_recipient_type( p_recipient_type )
291 ) then
292 PA_UTILS.Add_Message( p_app_short_name => 'PA'
293 ,p_msg_name => 'PA_DL_RECIPIENT_TYPE_INV');
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 END IF;
296
297 -- Validate Recipient Id
298 IF (p_recipient_id IS NULL OR
299 NOT pa_distribution_list_utils.check_valid_recipient_id
300 ( p_recipient_type,
301 p_recipient_id )
302 ) then
303 PA_UTILS.Add_Message( p_app_short_name => 'PA'
304 ,p_msg_name => 'PA_DL_RECIPIENT_ID_INV');
305 x_return_status := FND_API.G_RET_STS_ERROR;
306 END IF;
307
308 -- Validate Access Level
309
310 -- Validate Menu Id
311 IF (p_menu_id IS NOT NULL AND
312 NOT pa_distribution_list_utils.Check_valid_menu_id(p_menu_id)
313 ) then
314 PA_UTILS.Add_Message( p_app_short_name => 'PA'
315 ,p_msg_name => 'PA_DL_MENU_ID_INV');
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 END IF;
318
319 -- Validate Email
320 IF (p_email IS NOT NULL AND (p_email <> 'Y' AND p_email <> 'N')) then
321 PA_UTILS.Add_Message( p_app_short_name => 'PA'
322 ,p_msg_name => 'PA_EMAIL_INV');
323 x_return_status := FND_API.G_RET_STS_ERROR;
324 END IF;
325
326 If (x_return_status = fnd_api.g_ret_sts_success
327 AND p_validate_only <> fnd_api.g_true) then
328 PA_DIST_LIST_ITEMS_PKG.INSERT_ROW (
329 P_LIST_ITEM_ID => P_LIST_ITEM_ID,
330 P_LIST_ID => P_LIST_ID ,
331 P_RECIPIENT_TYPE => P_RECIPIENT_TYPE ,
332 P_RECIPIENT_ID => P_RECIPIENT_ID,
333 P_ACCESS_LEVEL => P_ACCESS_LEVEL,
334 P_MENU_ID => P_MENU_ID ,
335 P_EMAIL => P_EMAIL ,
336 P_RECORD_VERSION_NUMBER => 1,
337 P_CREATED_BY => P_CREATED_BY,
338 P_CREATION_DATE => P_CREATION_DATE,
339 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
340 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
341 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ) ;
342 End If;
343 -- Commit the changes if requested
344 if (p_commit = FND_API.G_TRUE
345 AND x_return_status = fnd_api.g_ret_sts_success) then
346 commit;
347 end if;
348 EXCEPTION
349 WHEN FND_API.G_EXC_ERROR THEN
350 IF p_commit = FND_API.G_TRUE
351 THEN
352 ROLLBACK TO CREATE_DIST_LIST_ITEM;
353 END IF;
354 x_return_status := 'E';
355
356 WHEN OTHERS THEN
357 IF p_commit = FND_API.G_TRUE
358 THEN
359 ROLLBACK TO CREATE_DIST_LIST_ITEM;
360 END IF;
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_DISTRIBUTION_LISTS_PVT',
363 p_procedure_name => 'CREATE_DIST_LIST_ITEM',
364 p_error_text => SUBSTRB(SQLERRM,1,240));
365 RAISE;
366
367 End CREATE_DIST_LIST_ITEM;
368
369 procedure UPDATE_DIST_LIST_ITEM (
370 p_api_version IN NUMBER := 1.0,
371 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
372 p_commit IN VARCHAR2 := FND_API.g_false,
373 p_validate_only IN VARCHAR2 := FND_API.g_true,
374 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
375 P_LIST_ITEM_ID in NUMBER := null,
376 P_LIST_ID in NUMBER := null,
377 P_RECIPIENT_TYPE in VARCHAR2 := null,
378 P_RECIPIENT_ID in VARCHAR2 := null,
379 P_ACCESS_LEVEL in NUMBER := null,
380 P_MENU_ID in NUMBER := null,
381 P_EMAIL in VARCHAR2 := null,
382 P_RECORD_VERSION_NUMBER in NUMBER := 1,
383 P_LAST_UPDATED_BY in NUMBER := fnd_global.user_id,
384 P_LAST_UPDATE_DATE in DATE := sysdate,
385 P_LAST_UPDATE_LOGIN in NUMBER := fnd_global.user_id,
386 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
387 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
388 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
389 )
390 IS
391 l_error_msg_code varchar2(30);
392 Cursor check_record_changed IS
393 select rowid
394 from pa_dist_list_items
395 where list_item_id = p_list_item_id
396 and record_version_number = p_record_version_number
397 for update ;
398
399 l_rowid rowid;
400 BEGIN
401 IF p_commit = FND_API.G_TRUE
402 THEN
403 SAVEPOINT UPDATE_DIST_LIST_ITEM;
404 END IF;
405 x_return_status := fnd_api.g_ret_sts_success;
406
407 -- Validate the Input Values
408 -- Validate list_id
409 IF (p_list_id IS NULL OR
410 NOT pa_distribution_list_utils.Check_valid_dist_list_id(p_list_id)
411 ) then
412 PA_UTILS.Add_Message( p_app_short_name => 'PA'
413 ,p_msg_name => 'PA_DL_LIST_ID_INV');
414 x_return_status := FND_API.G_RET_STS_ERROR;
415 END IF;
416 -- Validate recipient type
417 IF (p_recipient_type IS NULL OR
418 NOT pa_distribution_list_utils.check_valid_recipient_type( p_recipient_type )
419 ) then
420 PA_UTILS.Add_Message( p_app_short_name => 'PA'
421 ,p_msg_name => 'PA_DL_RECIPIENT_TYPE_INV');
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 END IF;
424 -- Validate Email
425 IF (p_email IS NOT NULL AND (p_email <> 'Y' AND p_email <> 'N')) then
426 PA_UTILS.Add_Message( p_app_short_name => 'PA'
427 ,p_msg_name => 'PA_EMAIL_INV');
428 x_return_status := FND_API.G_RET_STS_ERROR;
429 END IF;
430 -- Validate Recipient Id
431 IF (p_recipient_id IS NULL OR
432 NOT pa_distribution_list_utils.check_valid_recipient_id
433 ( p_recipient_type,
434 p_recipient_id )
435 ) then
436 PA_UTILS.Add_Message( p_app_short_name => 'PA'
437 ,p_msg_name => 'PA_DL_RECIPIENT_ID_INV');
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 END IF;
440 -- Validate Access Level
441
442 -- Validate Menu Id
443 IF (p_menu_id IS NOT NULL AND
444 NOT pa_distribution_list_utils.Check_valid_menu_id(p_menu_id)
445 ) then
446 PA_UTILS.Add_Message( p_app_short_name => 'PA'
447 ,p_msg_name => 'PA_DL_MENU_ID_INV');
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 END IF;
450 -- Lock the Row
451 OPEN check_record_changed;
452 FETCH check_record_changed INTO l_rowid;
453 IF check_record_changed%NOTFOUND THEN
454 PA_UTILS.Add_Message( p_app_short_name => 'PA'
455 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 CLOSE check_record_changed;
458 RAISE FND_API.G_EXC_ERROR;
459 END IF;
460 if (check_record_changed%ISOPEN) then
461 CLOSE check_record_changed;
462 end if;
463 If (x_return_status = fnd_api.g_ret_sts_success
464 AND p_validate_only <> fnd_api.g_true) then
465 PA_DIST_LIST_ITEMS_PKG.UPDATE_ROW (
466 P_LIST_ITEM_ID => P_LIST_ITEM_ID,
467 P_LIST_ID => P_LIST_ID ,
468 P_RECIPIENT_TYPE => P_RECIPIENT_TYPE ,
469 P_RECIPIENT_ID => P_RECIPIENT_ID,
470 P_ACCESS_LEVEL => P_ACCESS_LEVEL,
471 P_EMAIL => P_EMAIL,
472 P_MENU_ID => P_MENU_ID ,
473 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER + 1,
474 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
475 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
476 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN ) ;
477 End if;
478 -- Commit the changes if requested
479 if (p_commit = FND_API.G_TRUE
480 AND x_return_status = fnd_api.g_ret_sts_success) then
481 commit;
482 end if;
483 EXCEPTION
484 WHEN FND_API.G_EXC_ERROR THEN
485 IF p_commit = FND_API.G_TRUE
486 THEN
487 ROLLBACK TO UPDATE_DIST_LIST_ITEM;
488 END IF;
489 x_return_status := 'E';
490
491 WHEN OTHERS THEN
492 IF p_commit = FND_API.G_TRUE
493 THEN
494 ROLLBACK TO UPDATE_DIST_LIST_ITEM;
495 END IF;
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_DISTRIBUTION_LISTS_PVT',
498 p_procedure_name => 'UPDATE_DIST_LIST_ITEM',
499 p_error_text => SUBSTRB(SQLERRM,1,240));
500 RAISE;
501
502 End UPDATE_DIST_LIST_ITEM;
503
504 procedure DELETE_DIST_LIST_ITEM (
505 p_api_version IN NUMBER := 1.0,
506 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
507 p_commit IN VARCHAR2 := FND_API.g_false,
508 p_validate_only IN VARCHAR2 := FND_API.g_true,
509 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
510 P_LIST_ITEM_ID in NUMBER,
511 P_RECORD_VERSION_NUMBER in NUMBER := 1,
512 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
513 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
514 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
515 )
516 IS
517 Cursor check_record_changed IS
518 select rowid
519 from pa_dist_list_items
520 where list_item_id = p_list_item_id
521 and record_version_number = p_record_version_number
522 for update ;
523
524 l_error_msg_code varchar2(30);
525 l_rowid rowid;
526
527 Begin
528 IF p_commit = FND_API.G_TRUE
529 THEN
530 SAVEPOINT DELETE_DIST_LIST_ITEM;
531 END IF;
532
533 x_return_status := fnd_api.g_ret_sts_success;
534
535 -- Lock the Row
536 OPEN check_record_changed;
537 FETCH check_record_changed INTO l_rowid;
538 IF check_record_changed%NOTFOUND THEN
539 PA_UTILS.Add_Message( p_app_short_name => 'PA'
540 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
541 x_return_status := FND_API.G_RET_STS_ERROR;
542 CLOSE check_record_changed;
543 RAISE FND_API.G_EXC_ERROR;
544 END IF;
545 if (check_record_changed%ISOPEN) then
546 CLOSE check_record_changed;
547 end if;
548
549 -- Delete row
550 If (x_return_status = FND_API.G_RET_STS_SUCCESS
551 AND p_validate_only <> fnd_api.g_true
552 AND pa_distribution_list_utils.Check_valid_dist_list_item_id(p_list_item_id) = 'T') then
553 PA_DIST_LIST_ITEMS_PKG.DELETE_ROW (
554 P_LIST_ITEM_ID => P_LIST_ITEM_ID) ;
555 End if;
556 -- Commit the changes if requested
557 if (p_commit = FND_API.G_TRUE
558 AND x_return_status = fnd_api.g_ret_sts_success) then
559 commit;
560 end if;
561 EXCEPTION
562 WHEN FND_API.G_EXC_ERROR THEN
563 IF p_commit = FND_API.G_TRUE
564 THEN
565 ROLLBACK TO DELETE_DIST_LIST_ITEM;
566 END IF;
567 x_return_status := 'E';
568
569 WHEN OTHERS THEN
570 IF p_commit = FND_API.G_TRUE
571 THEN
572 ROLLBACK TO DELETE_DIST_LIST_ITEM;
573 END IF;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_DISTRIBUTION_LISTS_PVT',
576 p_procedure_name => 'DELETE_DIST_LIST_ITEM',
577 p_error_text => SUBSTRB(SQLERRM,1,240));
578 RAISE;
579 End DELETE_DIST_LIST_ITEM;
580
581 END PA_DISTRIBUTION_LISTS_PVT;