[Home] [Help]
PACKAGE BODY: APPS.PA_CI_ACTIONS_PVT
Source
1 PACKAGE BODY PA_CI_ACTIONS_PVT AS
2 /* $Header: PACIACVB.pls 120.0 2005/06/03 13:27:41 appldev noship $ */
3
4
5 PROCEDURE CREATE_CI_ACTION (
6 p_api_version IN NUMBER := 1.0,
7 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
8 p_commit IN VARCHAR2 := FND_API.g_false,
9 p_validate_only IN VARCHAR2 := FND_API.g_true,
10 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
11 P_CI_ACTION_ID out NOCOPY NUMBER,
12 P_CI_ID in NUMBER,
13 P_TYPE_CODE in VARCHAR2,
14 P_ASSIGNED_TO in NUMBER,
15 P_DATE_REQUIRED in DATE,
16 P_SIGN_OFF_REQUIRED_FLAG in VARCHAR2,
17 P_COMMENT_TEXT in VARCHAR2,
18 P_SOURCE_CI_ACTION_ID in NUMBER default NULL,
19 P_CREATED_BY in NUMBER default fnd_global.user_id,
20 P_CREATION_DATE in DATE default sysdate,
21 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
22 P_LAST_UPDATE_DATE in DATE default sysdate,
23 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2
27 )
28 IS
29 -- Enter the procedure variables here. As shown below
30 l_error_msg_code varchar2(30);
31 l_ci_comment_id number;
32 l_ci_action_id number;
33 l_party_id number;
34 l_action_number number;
35 l_system_number_id number;
36 l_ci_record_version_number number;
37 l_num_of_actions number;
38 l_comment_text varchar2(32767);
39 l_process_name varchar2(100);
40
41
42 --bug 3297238
43 l_item_key pa_wf_processes.item_key%TYPE;
44
45 Cursor getRecordVersionNumber IS
46 select record_version_number
47 from pa_control_items
48 where ci_id = p_ci_id;
49
50 BEGIN
51 -- Initialize the Error Stack
52 PA_DEBUG.init_err_stack('PA_CI_ACTIONS_PVT.CREATE_CI_ACTION');
53
54 -- Initialize the return status to success
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 IF p_commit = FND_API.G_TRUE
58 THEN
59 SAVEPOINT ADD_ACTION;
60 END IF;
61 x_msg_count := 0;
62
63 if (P_ASSIGNED_TO IS NULL) then
64 PA_UTILS.Add_Message( p_app_short_name => 'PA'
65 ,p_msg_name => 'PA_CI_ACTION_INVALID_ASSIGNEE');
66 x_return_status := FND_API.G_RET_STS_ERROR;
67 return;
68 end if;
69
70
71
72
73
74 if (p_ci_id IS NOT NULL) then
75 l_action_number := PA_CI_ACTIONS_UTIL.get_next_ci_action_number(p_ci_id);
76 end if;
77
78 -- Validate the Input Values
79 If (x_return_status = fnd_api.g_ret_sts_success
80 AND p_validate_only <> fnd_api.g_true) then
81
82 PA_CI_ACTIONS_PKG.INSERT_ROW(
83 P_CI_ACTION_ID => l_ci_action_id,
84 P_CI_ID => P_CI_ID,
85 P_CI_ACTION_NUMBER => l_action_number,
86 P_STATUS_CODE => 'CI_ACTION_OPEN',
87 P_TYPE_CODE => P_TYPE_CODE,
88 P_ASSIGNED_TO => P_ASSIGNED_TO,
89 P_DATE_REQUIRED => P_DATE_REQUIRED,
90 P_SIGN_OFF_REQUIRED_FLAG => P_SIGN_OFF_REQUIRED_FLAG,
91 P_DATE_CLOSED => NULL,
92 P_SIGN_OFF_FLAG => 'N',
93 P_SOURCE_CI_ACTION_ID => P_SOURCE_CI_ACTION_ID,
94 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
95 P_CREATED_BY => P_CREATED_BY,
96 P_CREATION_DATE => P_CREATION_DATE,
97 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
98 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
99 P_RECORD_VERSION_NUMBER => 1);
100 end if;
101
102 if (P_COMMENT_TEXT IS NULL) THEN
103 l_comment_text := ' ';
104 else
105 l_comment_text := p_comment_text;
106 end if;
107
108 If (x_return_status = fnd_api.g_ret_sts_success
109 AND p_validate_only <> fnd_api.g_true) then
110 PA_CI_ACTIONS_PVT.ADD_CI_COMMENT(
111 p_api_version => P_API_VERSION,
112 p_init_msg_list => P_INIT_MSG_LIST,
113 p_commit => P_COMMIT,
114 p_validate_only => P_VALIDATE_ONLY,
115 p_max_msg_count => P_MAX_MSG_COUNT,
116 p_ci_comment_id => l_ci_comment_id,
117 p_ci_id =>P_CI_ID,
118 p_type_code => 'REQUESTOR',
119 p_comment_text => l_comment_text,
120 p_ci_action_id => l_ci_action_id,
121 x_return_status => x_return_status,
122 x_msg_count => x_msg_count,
123 x_msg_data => x_msg_data
124 );
125 END IF;
126
127 OPEN getRecordVersionNumber;
128 FETCH getRecordVersionNumber into l_ci_record_version_number;
129 CLOSE getRecordVersionNumber;
130
131 If (x_return_status = fnd_api.g_ret_sts_success
132 AND p_validate_only <> fnd_api.g_true) then
133 PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
134 p_api_version => P_API_VERSION,
135 p_init_msg_list => P_INIT_MSG_LIST,
136 p_commit => P_COMMIT,
137 p_validate_only => P_VALIDATE_ONLY,
138 p_max_msg_count => P_MAX_MSG_COUNT,
139 p_ci_id =>P_CI_ID,
140 p_num_of_actions => 1,
141 p_record_version_number =>l_ci_record_version_number,
142 x_num_of_actions => l_num_of_actions,
143 x_return_status => x_return_status,
144 x_msg_count => x_msg_count,
145 x_msg_data => x_msg_data);
146 End if;
147
148 -- Changes for bug# 3691192 FP M Changes
149 -- Depending upon Sign-off required different processes have been created in the PA Issue and Change Action Workflow
150 if P_SIGN_OFF_REQUIRED_FLAG = 'Y' then
151 l_process_name := 'PA_CI_ACTION_ASMT_SIGN_OFF';
152 else
153 l_process_name := 'PA_CI_ACTION_ASMT_NO_SIGN_OFF';
154 end if;
155 -- Launch the workflow notification if it is not validate only mode and no errors occured till now.
156 -- Bug 3297238. FP M Changes.
157 IF ( p_validate_only = FND_API.G_FALSE AND x_return_status = FND_API.g_ret_sts_success )THEN
158 pa_control_items_workflow.START_NOTIFICATION_WF
159 ( p_item_type => 'PAWFCIAC'
160 ,p_process_name => l_process_name
161 ,p_ci_id => p_ci_id
162 ,p_action_id => l_ci_action_id
163 ,x_item_key => l_item_key
164 ,x_return_status => x_return_status
165 ,x_msg_count => x_msg_count
166 ,x_msg_data => x_msg_data );
167 if(x_return_status <> FND_API.g_ret_sts_success) then
168 raise FND_API.G_EXC_ERROR;
169 end if;
170 END IF;
171
172 -- Commit the changes if requested
173 if (p_commit = FND_API.G_TRUE
174 AND x_return_status = fnd_api.g_ret_sts_success) then
175 commit;
176 end if;
177
178 EXCEPTION
179 WHEN FND_API.G_EXC_ERROR THEN
180 IF p_commit = FND_API.G_TRUE
181 THEN
182 ROLLBACK TO ADD_ACTION;
183 END IF;
184 x_return_status := 'E';
185
186 WHEN OTHERS THEN
187 IF p_commit = FND_API.G_TRUE
188 THEN
189 ROLLBACK TO ADD_ACTION;
190 END IF;
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_PVT',
193 p_procedure_name => 'CREATE_CI_ACTIONS',
194 p_error_text => SUBSTRB(SQLERRM,1,240));
195 RAISE;
196 END CREATE_CI_ACTION;
197
198 PROCEDURE CLOSE_CI_ACTION (
199 p_api_version IN NUMBER := 1.0,
200 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
201 p_commit IN VARCHAR2 := FND_API.g_false,
202 p_validate_only IN VARCHAR2 := FND_API.g_true,
203 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
204 P_calling_context IN VARCHAR2,
205 P_CI_ACTION_ID in NUMBER,
206 P_SIGN_OFF_FLAG in VARCHAR2,
207 P_RECORD_VERSION_NUMBER in NUMBER,
208 P_COMMENT_TEXT in VARCHAR2,
209 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
210 P_LAST_UPDATE_DATE in DATE default sysdate,
211 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2
215 )
216 IS
217 Cursor check_record_changed IS
218 select rowid
219 from pa_ci_actions
220 where ci_action_id = p_ci_action_id
221 and record_version_number = p_record_version_number
222 for update;
223
224 Cursor ci_action IS
225 select ci_id, type_code, assigned_to, date_required,
226 sign_off_required_flag, source_ci_action_id, created_by, creation_date
227 from pa_ci_actions
228 where ci_action_id = p_ci_action_id;
229
230 l_party_id number;
231 l_created_by number;
232 l_creation_date date;
233 l_ci_id number;
234 l_type_code varchar2(30);
235 l_assigned_to number;
236 l_date_required date;
237 l_sign_off_required_flag varchar2(1);
238 l_source_ci_action_id number;
239 l_error_msg_code varchar2(30);
240 l_rowid rowid;
241 l_ci_comment_id number;
242 l_ci_record_version_number number;
243 l_num_of_actions number;
244 l_comment_text varchar2(32767);
245
246 --bug 3297238
247 l_item_key pa_wf_processes.item_key%TYPE;
248
249 Cursor getRecordVersionNumber IS
250 select record_version_number
251 from pa_control_items
252 where ci_id = l_ci_id;
253 BEGIN
254 -- Initialize the Error Stack
255 PA_DEBUG.init_err_stack('PA_CI_ACTIONS_PVT.CLOSE_CI_ACTION');
256
257 -- Initialize the return status to success
258 x_return_status := FND_API.G_RET_STS_SUCCESS;
259
260 x_msg_count :=0 ;
261 IF p_commit = FND_API.G_TRUE
262 THEN
263 SAVEPOINT CLOSE_CI_ACTION;
264 END IF;
265
266 -- Validate the Input Values
267 OPEN ci_action;
268 FETCH ci_action INTO l_ci_id, l_type_code, l_assigned_to,
269 l_date_required, l_sign_off_required_flag, l_source_ci_action_id,
270 l_created_by, l_creation_date;
271 IF ci_action%NOTFOUND THEN
272 PA_UTILS.Add_Message( p_app_short_name => 'PA'
273 ,p_msg_name => 'PA_NO_ACTION_FOUND');
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 CLOSE ci_action;
276 return;
277 END IF;
278
279 --LOCK the ROW
280
281 OPEN check_record_changed;
282 FETCH check_record_changed INTO l_rowid;
283 IF check_record_changed%NOTFOUND THEN
284 PA_UTILS.Add_Message( p_app_short_name => 'PA'
285 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
286 x_return_status := FND_API.G_RET_STS_ERROR;
287 CLOSE check_record_changed;
288 return;
289 END IF;
290 if (check_record_changed%ISOPEN) then
291 CLOSE check_record_changed;
292 end if;
293 If (x_return_status = fnd_api.g_ret_sts_success
294 AND p_validate_only <> fnd_api.g_true) then
295 PA_CI_ACTIONS_PKG.UPDATE_ROW(
296 P_CI_ACTION_ID => P_CI_ACTION_ID,
297 P_CI_ID => l_ci_id,
298 P_STATUS_CODE => 'CI_ACTION_CLOSED',
299 P_TYPE_CODE => l_type_code,
300 P_ASSIGNED_TO => l_assigned_to,
301 P_DATE_REQUIRED => l_date_required,
302 P_SIGN_OFF_REQUIRED_FLAG => l_sign_off_required_flag,
303 P_DATE_CLOSED => sysdate,
304 P_SIGN_OFF_FLAG => P_SIGN_OFF_FLAG,
305 P_SOURCE_CI_ACTION_ID => l_source_ci_action_id,
306 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
307 P_CREATED_BY => l_created_by,
308 P_CREATION_DATE => l_creation_date,
309 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
310 P_LAST_UPDATE_LOGIN => p_last_update_login,
311 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER);
312 End if;
313
314 if (P_COMMENT_TEXT IS NULL) THEN
315 l_comment_text := ' ';
316 else
317 l_comment_text := p_comment_text;
318 end if;
319
320 If (x_return_status = fnd_api.g_ret_sts_success
321 AND p_validate_only <> fnd_api.g_true) then
322 PA_CI_ACTIONS_PVT.ADD_CI_COMMENT(
323 p_api_version => P_API_VERSION,
324 p_init_msg_list => P_INIT_MSG_LIST,
325 p_commit => P_COMMIT,
326 p_validate_only => P_VALIDATE_ONLY,
327 p_max_msg_count => P_MAX_MSG_COUNT,
328 p_ci_comment_id => l_ci_comment_id,
329 p_ci_id => l_ci_id,
330 p_type_code => 'CLOSURE',
331 p_comment_text => l_comment_text,
332 p_ci_action_id => P_CI_ACTION_ID,
333 x_return_status => x_return_status,
334 x_msg_count => x_msg_count,
335 x_msg_data => x_msg_data
336 );
337 END IF;
338 OPEN getRecordVersionNumber;
339 FETCH getRecordVersionNumber into l_ci_record_version_number;
340 CLOSE getRecordVersionNumber;
341
342 If (x_return_status = fnd_api.g_ret_sts_success
343 AND p_validate_only <> fnd_api.g_true) then
344 PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
345 p_api_version => P_API_VERSION,
346 p_init_msg_list => P_INIT_MSG_LIST,
347 p_commit => P_COMMIT,
348 p_validate_only => P_VALIDATE_ONLY,
349 p_max_msg_count => P_MAX_MSG_COUNT,
350 p_ci_id =>l_CI_ID,
351 p_num_of_actions => -1,
352 p_record_version_number =>l_ci_record_version_number,
353 x_num_of_actions => l_num_of_actions,
354 x_return_status => x_return_status,
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data);
357 End if;
358
359
360 -- Launch the workflow notification if it is not validate only mode and no errors occured till now and calling context is CLOSE.
361 -- Bug 3297238. FP M Changes.
362 IF ( p_validate_only = FND_API.G_FALSE AND x_return_status = FND_API.g_ret_sts_success AND P_calling_context = 'CLOSE' )THEN
363 pa_control_items_workflow.START_NOTIFICATION_WF
364 ( p_item_type => 'PAWFCIAC'
365 ,p_process_name => 'PA_CI_ACTION_CLOSE_FYI'
366 ,p_ci_id => l_ci_id
367 ,p_action_id => p_ci_action_id
368 ,x_item_key => l_item_key
369 ,x_return_status => x_return_status
370 ,x_msg_count => x_msg_count
371 ,x_msg_data => x_msg_data );
372 if(x_return_status <> FND_API.g_ret_sts_success) then
373 raise FND_API.G_EXC_ERROR;
374 end if;
375 END IF;
376
377 -- Commit the changes if requested
378 if (p_commit = FND_API.G_TRUE
379 AND x_return_status = fnd_api.g_ret_sts_success) then
380 commit;
381 end if;
382
383
384 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
385 IF p_commit = FND_API.G_TRUE THEN
386 ROLLBACK TO CLOSE_CI_ACTION;
387 END IF;
388 x_return_status := 'E';
389 WHEN OTHERS THEN
390 IF p_commit = FND_API.G_TRUE THEN
391 ROLLBACK TO CLOSE_CI_ACTION;
392 END IF;
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_PVT',
395 p_procedure_name => 'CLOSE_CI_ACTIONS',
396 p_error_text => SUBSTRB(SQLERRM,1,240));
397 RAISE;
398 END CLOSE_CI_ACTION;
399
400
401 PROCEDURE REASSIGN_CI_ACTION (
402 p_api_version IN NUMBER := 1.0,
403 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
404 p_commit IN VARCHAR2 := FND_API.g_false,
405 p_validate_only IN VARCHAR2 := FND_API.g_true,
406 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
407 P_CI_ACTION_ID in NUMBER,
408 P_SIGN_OFF_FLAG in VARCHAR2 := 'N',
409 P_RECORD_VERSION_NUMBER in NUMBER,
410 P_ASSIGNED_TO in NUMBER,
411 P_DATE_REQUIRED in DATE,
412 P_COMMENT_TEXT in VARCHAR2,
413 P_CLOSURE_COMMENT in VARCHAR2,
414 P_CREATED_BY in NUMBER default fnd_global.user_id,
415 P_CREATION_DATE in DATE default sysdate,
416 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
417 P_LAST_UPDATE_DATE in DATE default sysdate,
418 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
419 x_return_status OUT NOCOPY VARCHAR2,
420 x_msg_count OUT NOCOPY NUMBER,
421 x_msg_data OUT NOCOPY VARCHAR2
422 )
423 IS
424 Cursor check_record_changed IS
425 select rowid
426 from pa_ci_actions
427 where ci_action_id = p_ci_action_id
428 and record_version_number = p_record_version_number
429 for update;
430
431 Cursor ci_action IS
432 select ci_id, type_code, assigned_to, date_required,
433 sign_off_required_flag, source_ci_action_id
434 from pa_ci_actions
435 where ci_action_id = p_ci_action_id;
436
437 l_new_ci_action_id number;
438 l_ci_id number;
439 l_type_code varchar2(30);
440 l_assigned_to number;
441 l_date_required date;
442 l_sign_off_required_flag varchar2(1);
443 l_source_ci_action_id number;
444 l_error_msg_code varchar2(30);
445 l_rowid rowid;
446 l_created_by number;
447 l_creation_date date;
448 l_assigned_to_party NUMBER;
449
450
451 BEGIN
452 -- Initialize the Error Stack
453 PA_DEBUG.init_err_stack('PA_CI_ACTIONS_PVT.REASSIGN_CI_ACTION');
454
455 -- Initialize the return status to success
456 x_return_status := FND_API.G_RET_STS_SUCCESS;
457 x_msg_data := 0;
458
459 IF p_commit = FND_API.G_TRUE
460 THEN
461 SAVEPOINT REASSIGN_CI_ACTION;
462 END IF;
463
464 -- Validate the Input Values
465 OPEN ci_action;
466 FETCH ci_action INTO l_ci_id, l_type_code, l_assigned_to,
467 l_date_required, l_sign_off_required_flag, l_source_ci_action_id;
468 IF ci_action%NOTFOUND THEN
469 PA_UTILS.Add_Message( p_app_short_name => 'PA'
470 ,p_msg_name => 'PA_NO_ACTION_FOUND');
471 x_return_status := FND_API.G_RET_STS_ERROR;
472 CLOSE ci_action;
473 return;
474 END IF;
475
476
477 --LOCK the ROW
478
479 OPEN check_record_changed;
480 FETCH check_record_changed INTO l_rowid;
481 IF check_record_changed%NOTFOUND THEN
482 PA_UTILS.Add_Message( p_app_short_name => 'PA'
483 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 CLOSE check_record_changed;
486 return;
487 END IF;
488 if (check_record_changed%ISOPEN) then
489 CLOSE check_record_changed;
490 end if;
491
492 -- Validate if the action is being reassigned to the same person and check if date is before system date.
493 select assigned_to
494 into l_assigned_to_party
495 from pa_ci_actions
496 where ci_action_id = p_ci_action_id;
497
498
499 if (p_assigned_to is not null and (l_assigned_to_party = p_assigned_to OR ((P_DATE_REQUIRED is not null) and (P_DATE_REQUIRED < sysdate))))then
500
501 if l_assigned_to_party = p_assigned_to THEN
502 PA_UTILS.Add_Message( p_app_short_name => 'PA'
503 ,p_msg_name => 'PA_CI_ACTION_REASSIGN_INV');
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 end if;
506
507 if (P_DATE_REQUIRED is not null) and (P_DATE_REQUIRED < sysdate) then
508 PA_UTILS.Add_Message( p_app_short_name => 'PA'
509 ,p_msg_name => 'PA_CI_ACTION_DATE_REQ_INV');
510 x_return_status := FND_API.G_RET_STS_ERROR;
511 end if;
512
513 return;
514 end if;
515 --validation for action assignee and date ends here.
516
517
518 If (x_return_status = fnd_api.g_ret_sts_success
519 AND p_validate_only <> fnd_api.g_true) then
520 PA_CI_ACTIONS_PVT.CLOSE_CI_ACTION
521 (
522 p_api_version => P_API_VERSION,
523 p_init_msg_list => P_INIT_MSG_LIST,
524 p_commit => P_COMMIT,
525 p_validate_only => P_VALIDATE_ONLY,
526 p_max_msg_count => P_MAX_MSG_COUNT,
527 P_calling_context => 'REASSIGN',
528 P_CI_ACTION_ID => P_CI_ACTION_ID,
529 P_SIGN_OFF_FLAG => P_SIGN_OFF_FLAG,
530 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
531 P_COMMENT_TEXT => P_CLOSURE_COMMENT,
532 x_return_status => x_return_status,
533 x_msg_count => x_msg_count,
534 x_msg_data => x_msg_data
535 );
536 END IF;
537
538 If (x_return_status = fnd_api.g_ret_sts_success
539 AND p_validate_only <> fnd_api.g_true) then
540
541 PA_CI_ACTIONS_PVT.CREATE_CI_ACTION
542 (
543 P_API_VERSION => P_API_VERSION,
544 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
545 P_COMMIT => P_COMMIT,
546 P_VALIDATE_ONLY => P_VALIDATE_ONLY,
547 P_MAX_MSG_COUNT => P_MAX_MSG_COUNT,
548 P_CI_ACTION_ID => l_new_ci_action_id,
549 P_CI_ID => l_ci_id,
550 P_TYPE_CODE => l_type_code,
551 P_ASSIGNED_TO => P_ASSIGNED_TO,
552 P_DATE_REQUIRED => P_DATE_REQUIRED,
553 P_SIGN_OFF_REQUIRED_FLAG => l_sign_off_required_flag,
554 P_COMMENT_TEXT => P_COMMENT_TEXT,
555 P_SOURCE_CI_ACTION_ID => P_CI_ACTION_ID,
556 x_return_status => x_return_status,
557 x_msg_count => x_msg_count,
558 x_msg_data => x_msg_data
559 );
560 end if;
561
562 -- Commit the changes if requested
563 if (p_commit = FND_API.G_TRUE
564 AND x_return_status = fnd_api.g_ret_sts_success) then
565 commit;
566 end if;
567
568 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
569 IF p_commit = FND_API.G_TRUE THEN
570 ROLLBACK TO REASSIGN_CI_ACTION;
571 END IF;
572 x_return_status := 'E';
573 WHEN OTHERS THEN
574 IF p_commit = FND_API.G_TRUE THEN
575 ROLLBACK TO REASSIGN_CI_ACTION;
576 END IF;
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_PVT',
579 p_procedure_name => 'REASSIGN_CI_ACTION',
580 p_error_text => SUBSTRB(SQLERRM,1,240));
581 RAISE;
582 END REASSIGN_CI_ACTION;
583
584 PROCEDURE CANCEL_CI_ACTION (
585 p_api_version IN NUMBER := 1.0,
586 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
587 p_commit IN VARCHAR2 := FND_API.g_false,
588 p_validate_only IN VARCHAR2 := FND_API.g_true,
589 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
590 P_CI_ACTION_ID in NUMBER,
591 P_RECORD_VERSION_NUMBER in NUMBER,
592 P_CANCEL_COMMENT in VARCHAR2,
593 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
594 P_LAST_UPDATE_DATE in DATE default sysdate,
595 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
596 x_return_status OUT NOCOPY VARCHAR2,
597 x_msg_count OUT NOCOPY NUMBER,
598 x_msg_data OUT NOCOPY VARCHAR2
599 )
600 IS
601 Cursor check_record_changed IS
602 select rowid
603 from pa_ci_actions
604 where ci_action_id = p_ci_action_id
605 and record_version_number = p_record_version_number
606 for update;
607
608 Cursor ci_action IS
609 select ci_id, type_code, assigned_to, date_required,
610 sign_off_required_flag, source_ci_action_id, created_by, creation_date, sign_off_flag
611 from pa_ci_actions
612 where ci_action_id = p_ci_action_id;
613
614 l_party_id number;
615 l_created_by number;
616 l_creation_date date;
617 l_ci_id number;
618 l_type_code varchar2(30);
619 l_assigned_to number;
620 l_date_required date;
621 l_sign_off_required_flag varchar2(1);
622 l_source_ci_action_id number;
623 l_error_msg_code varchar2(30);
624 l_rowid rowid;
625 l_ci_comment_id number;
626 l_sign_off_flag varchar2(1);
627 l_ci_record_version_number number;
628 l_num_of_actions number;
629 l_comment_text varchar2(32767);
630
631 Cursor getRecordVersionNumber IS
632 select record_version_number
633 from pa_control_items
634 where ci_id = l_ci_id;
635
636 BEGIN
637 x_return_status := fnd_api.g_ret_sts_success;
638 x_msg_count :=0 ;
639 IF p_commit = FND_API.G_TRUE
640 THEN
641 SAVEPOINT CANCEL_CI_ACTION;
642 END IF;
643
644 -- Validate the Input Values
645 OPEN ci_action;
646 FETCH ci_action INTO l_ci_id, l_type_code, l_assigned_to,
647 l_date_required, l_sign_off_required_flag, l_source_ci_action_id,
648 l_created_by, l_creation_date,l_sign_off_flag;
649 IF ci_action%NOTFOUND THEN
650 PA_UTILS.Add_Message( p_app_short_name => 'PA'
651 ,p_msg_name => 'PA_NO_ACTION_FOUND');
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 CLOSE ci_action;
654 return;
655 END IF;
656
657 l_party_id := PA_UTILS.get_party_id(P_LAST_UPDATED_BY);
658 if (l_party_id IS NULL) then
659 x_return_status := FND_API.G_RET_STS_ERROR;
660 PA_UTILS.Add_Message( p_app_short_name => 'PA'
661 ,p_msg_name => 'PA_RESOURCE_INVALID_PERSON');
662 return;
663 end if;
664
665 --LOCK the ROW
666
667 OPEN check_record_changed;
668 FETCH check_record_changed INTO l_rowid;
669 IF check_record_changed%NOTFOUND THEN
670 PA_UTILS.Add_Message( p_app_short_name => 'PA'
671 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
672 x_return_status := FND_API.G_RET_STS_ERROR;
673 CLOSE check_record_changed;
674 return;
675 END IF;
676 if (check_record_changed%ISOPEN) then
677 CLOSE check_record_changed;
678 end if;
679 If (x_return_status = fnd_api.g_ret_sts_success
680 AND p_validate_only <> fnd_api.g_true) then
681 PA_CI_ACTIONS_PKG.UPDATE_ROW(
682 P_CI_ACTION_ID => P_CI_ACTION_ID,
683 P_CI_ID => l_ci_id,
684 P_STATUS_CODE => 'CI_ACTION_CANCELED',
685 P_TYPE_CODE => l_type_code,
686 P_ASSIGNED_TO => l_assigned_to,
687 P_DATE_REQUIRED => l_date_required,
688 P_SIGN_OFF_REQUIRED_FLAG => l_sign_off_required_flag,
689 P_DATE_CLOSED => sysdate,
690 P_SIGN_OFF_FLAG => l_sign_off_flag,
691 P_SOURCE_CI_ACTION_ID => l_source_ci_action_id,
692 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
693 P_CREATED_BY => l_created_by,
694 P_CREATION_DATE => l_creation_date,
695 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
696 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
697 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER);
698
699 END IF;
700
701 if (P_CANCEL_COMMENT IS NULL) THEN
702 l_comment_text := ' ';
703 else
704 l_comment_text := P_CANCEL_COMMENT;
705 end if;
706
707 If (x_return_status = fnd_api.g_ret_sts_success
708 AND p_validate_only <> fnd_api.g_true) then
709 PA_CI_ACTIONS_PVT.ADD_CI_COMMENT(
710 p_api_version => P_API_VERSION,
711 p_init_msg_list => P_INIT_MSG_LIST,
712 p_commit => P_COMMIT,
713 p_validate_only => P_VALIDATE_ONLY,
714 p_max_msg_count => P_MAX_MSG_COUNT,
715 p_ci_comment_id => l_ci_comment_id,
716 p_ci_id => l_ci_id,
717 p_type_code => 'CLOSURE',
718 p_comment_text => l_comment_text,
719 p_ci_action_id => P_CI_ACTION_ID,
720 x_return_status => x_return_status,
721 x_msg_count => x_msg_count,
722 x_msg_data => x_msg_data
723 );
724 END IF;
725 OPEN getRecordVersionNumber;
726 FETCH getRecordVersionNumber into l_ci_record_version_number;
727 CLOSE getRecordVersionNumber;
728
729 If (x_return_status = fnd_api.g_ret_sts_success
730 AND p_validate_only <> fnd_api.g_true) then
731 PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS (
732 p_api_version => P_API_VERSION,
733 p_init_msg_list => P_INIT_MSG_LIST,
734 p_commit => P_COMMIT,
735 p_validate_only => P_VALIDATE_ONLY,
736 p_max_msg_count => P_MAX_MSG_COUNT,
737 p_ci_id =>l_ci_id,
738 p_num_of_actions => -1,
739 p_record_version_number =>l_ci_record_version_number,
740 x_num_of_actions => l_num_of_actions,
741 x_return_status => x_return_status,
742 x_msg_count => x_msg_count,
743 x_msg_data => x_msg_data);
744 End if;
745
746
747 -- Commit the changes if requested
748 if (p_commit = FND_API.G_TRUE
749 AND x_return_status = fnd_api.g_ret_sts_success) then
750 commit;
751 end if;
752
753 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
754 IF p_commit = FND_API.G_TRUE THEN
755 ROLLBACK TO CLOSE_CI_ACTION;
756 END IF;
757 x_return_status := 'E';
758 WHEN OTHERS THEN
759 IF p_commit = FND_API.G_TRUE THEN
760 ROLLBACK TO CLOSE_CI_ACTION;
761 END IF;
762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
763 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_PVT',
764 p_procedure_name => 'CLOSE_CI_ACTIONS',
765 p_error_text => SUBSTRB(SQLERRM,1,240));
766 RAISE;
767 END CANCEL_CI_ACTION;
768
769 PROCEDURE UPDATE_CI_COMMENT(
770 p_api_version IN NUMBER := 1.0,
771 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
772 p_commit IN VARCHAR2 := FND_API.g_false,
773 p_validate_only IN VARCHAR2 := FND_API.g_true,
774 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
775 p_ci_comment_id IN NUMBER,
776 p_comment_text IN VARCHAR2,
777 p_record_version_number IN NUMBER,
778 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
779 P_LAST_UPDATE_DATE in DATE default sysdate,
780 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
781 x_return_status OUT NOCOPY VARCHAR2,
782 x_msg_count OUT NOCOPY NUMBER,
783 x_msg_data OUT NOCOPY VARCHAR2)
784 IS
785 l_error_msg_code varchar2(30);
786 l_party_id number;
787 l_creation_date date;
788 l_created_by number;
789 l_type_code varchar2(30);
790 l_ci_id number;
791 l_ci_action_id number;
792 l_rowid rowid;
793
794 Cursor old_comment IS
795 select ci_id, type_code, created_by, creation_date, ci_action_id from pa_ci_comments
796 where ci_comment_id = p_ci_comment_id;
797
798 Cursor check_record_changed IS
799 select rowid
800 from pa_ci_comments
801 where ci_comment_id = p_ci_comment_id
802 and record_version_number = p_record_version_number
803 for update;
804 BEGIN
805
806 x_return_status := fnd_api.g_ret_sts_success;
807 x_msg_data := 0;
808
809
810 IF p_commit = FND_API.G_TRUE
811 THEN
812 SAVEPOINT UPDATE_CI_COMMENT;
813 END IF;
814
815 OPEN old_comment;
816 FETCH old_comment INTO l_ci_id, l_type_code,
817 l_created_by, l_creation_date, l_ci_action_id;
818 IF old_comment%NOTFOUND THEN
819 PA_UTILS.Add_Message (p_app_short_name => 'PA'
820 ,p_msg_name => 'PA_NO_COMMENT_FOUND');
821 CLOSE old_comment;
822 return;
823 END IF;
824 CLOSE old_comment;
825
826 OPEN check_record_changed;
827 FETCH check_record_changed INTO l_rowid;
828 IF check_record_changed%NOTFOUND THEN
829 PA_UTILS.Add_Message( p_app_short_name => 'PA'
830 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
831 x_return_status := FND_API.G_RET_STS_ERROR;
832 CLOSE check_record_changed;
833 return;
834 END IF;
835 if (check_record_changed%ISOPEN) then
836 CLOSE check_record_changed;
837 end if;
838
839 If (x_return_status = fnd_api.g_ret_sts_success
840 AND p_validate_only <> fnd_api.g_true) then
841
842 PA_CI_COMMENTS_PKG.UPDATE_ROW(
843 P_CI_COMMENT_ID => P_CI_COMMENT_ID,
844 P_CI_ID => l_ci_id,
845 P_TYPE_CODE => l_type_code,
846 P_COMMENT_TEXT => P_COMMENT_TEXT,
847 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
848 P_CREATED_BY => l_created_by,
849 P_CREATION_DATE => l_creation_date,
850 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
851 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
852 P_RECORD_VERSION_NUMBER => p_record_version_number,
853 P_CI_ACTION_ID => l_ci_action_id);
854 End If;
855 -- Commit the changes if requested
856 if (p_commit = FND_API.G_TRUE
857 AND x_return_status = fnd_api.g_ret_sts_success) then
858 commit;
859 end if;
860
861 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
862 IF p_commit = FND_API.G_TRUE THEN
863 ROLLBACK TO UPDATE_CI_COMMENT;
864 END IF;
865 x_return_status := 'E';
866 WHEN OTHERS THEN
867 IF p_commit = FND_API.G_TRUE THEN
868 ROLLBACK TO UPDATE_CI_COMMENT;
869 END IF;
870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
871 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_PVT',
872 p_procedure_name => 'UPDATE_CI_COMMENT',
873 p_error_text => SUBSTRB(SQLERRM,1,240));
874 RAISE;
875
876
877 END UPDATE_CI_COMMENT;
878
879
880
881 PROCEDURE ADD_CI_COMMENT(
882 p_api_version IN NUMBER := 1.0,
883 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
884 p_commit IN VARCHAR2 := FND_API.g_false,
885 p_validate_only IN VARCHAR2 := FND_API.g_true,
886 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
887 p_ci_comment_id out NOCOPY NUMBER,
888 p_ci_id IN NUMBER,
889 p_type_code IN VARCHAR2,
890 p_comment_text IN VARCHAR2,
891 p_ci_action_id IN NUMBER,
892 P_CREATED_BY in NUMBER default fnd_global.user_id,
893 P_CREATION_DATE in DATE default sysdate,
894 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
895 P_LAST_UPDATE_DATE in DATE default sysdate,
896 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
897 x_return_status OUT NOCOPY VARCHAR2,
898 x_msg_count OUT NOCOPY NUMBER,
899 x_msg_data OUT NOCOPY VARCHAR2)
900 IS
901 l_error_msg_code varchar2(30);
902 l_party_id number;
903 BEGIN
904 x_return_status := fnd_api.g_ret_sts_success;
905 x_msg_data := 0;
906
907 IF p_commit = FND_API.G_TRUE
908 THEN
909 SAVEPOINT ADD_CI_COMMENT;
910 END IF;
911
912 If (x_return_status = fnd_api.g_ret_sts_success
913 AND p_validate_only <> fnd_api.g_true
914 AND P_COMMENT_TEXT IS NOT NULL) then
915 PA_CI_COMMENTS_PKG.INSERT_ROW(
916 P_CI_COMMENT_ID => P_CI_COMMENT_ID,
917 P_CI_ID => P_CI_ID,
918 P_TYPE_CODE => P_TYPE_CODE,
919 P_COMMENT_TEXT => P_COMMENT_TEXT,
920 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
921 P_CREATED_BY => P_CREATED_BY,
922 P_CREATION_DATE => P_CREATION_DATE,
923 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
924 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
925 P_CI_ACTION_ID => P_CI_ACTION_ID);
926 End If;
927 -- Commit the changes if requested
928 if (p_commit = FND_API.G_TRUE
929 AND x_return_status = fnd_api.g_ret_sts_success) then
930 commit;
931 end if;
932
933 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
934 IF p_commit = FND_API.G_TRUE THEN
935 ROLLBACK TO ADD_CI_COMMENT;
936 END IF;
937 x_return_status := 'E';
938 WHEN OTHERS THEN
939 IF p_commit = FND_API.G_TRUE THEN
940 ROLLBACK TO ADD_CI_COMMENT;
941 END IF;
942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
943 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_PVT',
944 p_procedure_name => 'ADD_CI_COMMENT',
945 p_error_text => SUBSTRB(SQLERRM,1,240));
946 RAISE;
947 END ADD_CI_COMMENT;
948
949 PROCEDURE CANCEL_ALL_ACTIONS(
950 p_api_version IN NUMBER := 1.0,
951 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
952 p_commit IN VARCHAR2 := FND_API.g_false,
953 p_validate_only IN VARCHAR2 := FND_API.g_true,
954 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
955 p_ci_id IN NUMBER,
956 p_cancel_comment IN VARCHAR2 := NULL,
957 P_CREATED_BY in NUMBER default fnd_global.user_id,
958 P_CREATION_DATE in DATE default sysdate,
959 P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
960 P_LAST_UPDATE_DATE in DATE default sysdate,
961 P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
962 x_return_status OUT NOCOPY VARCHAR2,
963 x_msg_count OUT NOCOPY NUMBER,
964 x_msg_data OUT NOCOPY VARCHAR2)
965 IS
966 l_error_msg_code varchar2(30);
967 l_ci_action_id number;
968 l_record_version_number number(15);
969 l_ci_commment_id number(15);
970 l_cancel_comment varchar2(32767);
971
972 CURSOR cancel_action IS
973 SELECT ci_action_id, record_version_number
974 FROM PA_CI_ACTIONS pca
975 WHERE pca.ci_id = p_ci_id
976 AND status_code = 'CI_ACTION_OPEN';
977
978
979 BEGIN
980 x_return_status := fnd_api.g_ret_sts_success;
981 x_msg_data := 0;
982
983 IF p_commit = FND_API.G_TRUE
984 THEN
985 SAVEPOINT CLOSE_ALL_ACTIONS;
986 END IF;
987
988 if (p_cancel_comment IS NULL) then
989 FND_MESSAGE.SET_NAME('PA','PA_CI_CANCEL_ALL_ACTIONS');
990 l_cancel_comment := FND_MESSAGE.GET;
991 else
992 l_cancel_comment := p_cancel_comment;
993 end if;
994 If (x_return_status = fnd_api.g_ret_sts_success
995 AND p_validate_only <> fnd_api.g_true) then
996 OPEN cancel_action;
997 LOOP
998 FETCH cancel_action INTO l_ci_action_id,
999 l_record_version_number;
1000 EXIT WHEN cancel_action%NOTFOUND;
1001 PA_CI_ACTIONS_PVT.CANCEL_CI_ACTION
1002 (
1003 p_api_version => P_API_VERSION,
1004 p_init_msg_list => P_INIT_MSG_LIST,
1005 p_commit => P_COMMIT,
1006 p_validate_only => P_VALIDATE_ONLY,
1007 p_max_msg_count => P_MAX_MSG_COUNT,
1008 P_CI_ACTION_ID => l_ci_action_id,
1009 P_RECORD_VERSION_NUMBER => l_record_version_number,
1010 P_CANCEL_COMMENT => P_CANCEL_COMMENT,
1011 x_return_status => x_return_status,
1012 x_msg_count => x_msg_count,
1013 x_msg_data => x_msg_data);
1014 END LOOP;
1015 CLOSE cancel_action;
1016 End If;
1017 -- Commit the changes if requested
1018 if (p_commit = FND_API.G_TRUE
1019 AND x_return_status = fnd_api.g_ret_sts_success) then
1020 commit;
1021 end if;
1022
1023 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
1024 IF p_commit = FND_API.G_TRUE THEN
1025 ROLLBACK TO ADD_CI_COMMENT;
1026 END IF;
1027 x_return_status := 'E';
1028 WHEN OTHERS THEN
1029 IF p_commit = FND_API.G_TRUE THEN
1030 ROLLBACK TO ADD_CI_COMMENT;
1031 END IF;
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1033 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_PVT',
1034 p_procedure_name => 'DELETE_ALL_ACTIONS',
1035 p_error_text => SUBSTRB(SQLERRM,1,240));
1036 RAISE;
1037 END CANCEL_ALL_ACTIONS;
1038
1039
1040
1041 PROCEDURE DELETE_ALL_ACTIONS(
1042 p_api_version IN NUMBER := 1.0,
1043 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1044 p_commit IN VARCHAR2 := FND_API.g_false,
1045 p_validate_only IN VARCHAR2 := FND_API.g_true,
1046 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1047 p_ci_id IN NUMBER,
1048 x_return_status OUT NOCOPY VARCHAR2,
1049 x_msg_count OUT NOCOPY NUMBER,
1050 x_msg_data OUT NOCOPY VARCHAR2)
1051 IS
1052 l_error_msg_code varchar2(30);
1053 l_ci_action_id number;
1054 l_ci_comment_id number;
1055
1056 CURSOR delete_action IS
1057 SELECT ci_action_id
1058 FROM PA_CI_ACTIONS
1059 WHERE ci_id = p_ci_id;
1060
1061 CURSOR delete_comment IS
1062 SELECT ci_comment_id
1063 FROM PA_CI_COMMENTS
1064 WHERE ci_action_id = l_ci_action_id;
1065
1066 BEGIN
1067 x_return_status := fnd_api.g_ret_sts_success;
1068 x_msg_data := 0;
1069
1070 IF p_commit = FND_API.G_TRUE
1071 THEN
1072 SAVEPOINT DELETE_ALL_ACTIONS;
1073 END IF;
1074
1075 If (x_return_status = fnd_api.g_ret_sts_success
1076 AND p_validate_only <> fnd_api.g_true) then
1077
1078 OPEN delete_action;
1079 LOOP
1080 FETCH delete_action INTO l_ci_action_id;
1081 EXIT WHEN delete_action%NOTFOUND;
1082 PA_CI_ACTIONS_PKG.DELETE_ROW(
1083 P_CI_ACTION_ID => l_ci_action_id);
1084 OPEN delete_comment;
1085 LOOP
1086 FETCH delete_comment INTO l_ci_comment_id;
1087 EXIT WHEN delete_comment%NOTFOUND;
1088 PA_CI_COMMENTS_PKG.DELETE_ROW(
1089 P_CI_COMMENT_ID => l_ci_comment_id);
1090 END LOOP;
1091 CLOSE delete_comment;
1092 END LOOP;
1093 CLOSE delete_action;
1094
1095 End If;
1096 -- Commit the changes if requested
1097 if (p_commit = FND_API.G_TRUE
1098 AND x_return_status = fnd_api.g_ret_sts_success) then
1099 commit;
1100 end if;
1101
1102 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
1103 IF p_commit = FND_API.G_TRUE THEN
1104 ROLLBACK TO ADD_CI_COMMENT;
1105 END IF;
1106 x_return_status := 'E';
1107 WHEN OTHERS THEN
1108 IF p_commit = FND_API.G_TRUE THEN
1109 ROLLBACK TO ADD_CI_COMMENT;
1110 END IF;
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1112 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_PVT',
1113 p_procedure_name => 'DELETE_ALL_ACTIONS',
1114 p_error_text => SUBSTRB(SQLERRM,1,240));
1115 RAISE;
1116 END DELETE_ALL_ACTIONS;
1117
1118
1119 END PA_CI_ACTIONS_PVT; -- Package Body PA_CI_ACTIONS_PVT