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