[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ACTIONS_UTIL
Source
1 PACKAGE BODY ENG_CHANGE_ACTIONS_UTIL AS
2 /* $Header: ENGUCCMB.pls 120.3 2005/12/22 04:26:02 lkasturi noship $ */
3
4 -- Global variables and constants
5 -- ---------------------------------------------------------------------------
6 G_PKG_NAME VARCHAR2(30) := 'ENG_CHANGE_ACTIONS_UTIL';
7
8 -- Global cursors
9 -- ---------------------------------------------------------------------------
10
11 -- For Debug
12 g_debug_file UTL_FILE.FILE_TYPE ;
13 g_debug_flag BOOLEAN := FALSE ; -- For TEST : FALSE ;
14 g_output_dir VARCHAR2(80) := NULL ;
15 g_debug_filename VARCHAR2(30) := 'eng.chgmt.action.log' ;
16 g_debug_errmesg VARCHAR2(240);
17
18 /********************************************************************
19 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
20 * Write_Debug
21 * Parameters IN :
22 * Parameters OUT:
23 * Purpose : These procedures are for test and debug
24 *********************************************************************/
25 -- Open_Debug_Session
26 PROCEDURE Open_Debug_Session
27 ( p_output_dir IN VARCHAR2 := NULL
28 , p_file_name IN VARCHAR2 := NULL
29 )
30 IS
31 l_found NUMBER := 0;
32 l_utl_file_dir VARCHAR2(2000);
33
34 BEGIN
35
36 IF p_output_dir IS NOT NULL THEN
37 g_output_dir := p_output_dir ;
38
39 END IF ;
40
41 IF p_file_name IS NOT NULL THEN
42 g_debug_filename := p_file_name ;
43 END IF ;
44
45 IF g_output_dir IS NULL
46 THEN
47
48 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
49
50 END IF;
51
52 select value
53 INTO l_utl_file_dir
54 FROM v$parameter
55 WHERE name = 'utl_file_dir';
56
57 l_found := INSTR(l_utl_file_dir, g_output_dir);
58
59 IF l_found = 0
60 THEN
61 RETURN;
62 END IF;
63
64 g_debug_file := utl_file.fopen( g_output_dir
65 , g_debug_filename
66 , 'w');
67 g_debug_flag := TRUE ;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
72 g_debug_flag := FALSE;
73
74 END Open_Debug_Session ;
75
76 -- Close Debug_Session
77 PROCEDURE Close_Debug_Session
78 IS
79 BEGIN
80 IF utl_file.is_open(g_debug_file)
81 THEN
82 utl_file.fclose(g_debug_file);
83 END IF ;
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
88 g_debug_flag := FALSE;
89
90 END Close_Debug_Session ;
91
92 -- Test Debug
93 PROCEDURE Write_Debug
94 ( p_debug_message IN VARCHAR2 )
95 IS
96 BEGIN
97
98 IF utl_file.is_open(g_debug_file)
99 THEN
100 utl_file.put_line(g_debug_file, p_debug_message);
101 END IF ;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
106 g_debug_flag := FALSE;
107
108 END Write_Debug;
109
110 PROCEDURE Get_Debug_Mode
111 ( p_item_type IN VARCHAR2
112 , p_item_key IN VARCHAR2
113 , x_debug_flag OUT NOCOPY BOOLEAN
114 , x_output_dir OUT NOCOPY VARCHAR2
115 , x_debug_filename OUT NOCOPY VARCHAR2
116 )
117 IS
118
119 l_debug_flag VARCHAR2(1) ;
120
121 BEGIN
122
123 -- Get Debug Flag
124 l_debug_flag := WF_ENGINE.GetItemAttrText
125 ( p_item_type
126 , p_item_key
127 , '.DEBUG_FLAG'
128 );
129
130 IF FND_API.to_Boolean( l_debug_flag ) THEN
131 x_debug_flag := TRUE ;
132 END IF ;
133
134
135 -- Get Debug Output Directory
136 x_output_dir := WF_ENGINE.GetItemAttrText
137 ( p_item_type
138 , p_item_key
139 , '.DEBUG_OUTPUT_DIR'
140 );
141
142
143 -- Get Debug File Name
144 x_debug_filename := WF_ENGINE.GetItemAttrText
145 ( p_item_type
146 , p_item_key
147 , '.DEBUG_FILE_NAME'
148 );
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
153 g_debug_flag := FALSE;
154
155
156 END Get_Debug_Mode ;
157
158
159
160
161 /********************************************************************
162 * API Type : Local APIs
163 * Purpose : Those APIs are private
164 *********************************************************************/
165
166 /**
167 * Create new action row in the ENG_CHANGE_ACTIONS and its TL table
168 */
169 PROCEDURE Create_Change_Action
170 (
171 p_api_version IN NUMBER --
172 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
173 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
174 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
175 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
176 ,p_output_dir IN VARCHAR2 := NULL --
177 ,p_debug_filename IN VARCHAR2 := NULL --
178 ,x_return_status OUT NOCOPY VARCHAR2 --
179 ,x_msg_count OUT NOCOPY NUMBER --
180 ,x_msg_data OUT NOCOPY VARCHAR2 --
181 ,p_action_type IN VARCHAR2 --
182 ,p_object_name IN VARCHAR2 --
183 ,p_object_id1 IN NUMBER --
184 ,p_object_id2 IN NUMBER DEFAULT NULL --
185 ,p_object_id3 IN NUMBER DEFAULT NULL --
186 ,p_object_id4 IN NUMBER DEFAULT NULL --
187 ,p_object_id5 IN NUMBER DEFAULT NULL --
188 ,p_parent_action_id IN NUMBER DEFAULT -1 --
189 ,p_status_code IN NUMBER DEFAULT NULL --
190 ,p_priority_code IN VARCHAR2 DEFAULT NULL --
191 ,p_assignee_id IN NUMBER DEFAULT NULL --
192 ,p_response_by_date IN DATE DEFAULT NULL --
193 ,p_party_id_list IN VARCHAR2 DEFAULT NULL --
194 ,p_parent_status_code IN NUMBER DEFAULT NULL --
195 ,p_workflow_item_type IN VARCHAR2 DEFAULT NULL --
196 ,p_workflow_item_key IN VARCHAR2 DEFAULT NULL --
197 ,p_route_id IN NUMBER DEFAULT NULL --
198 ,p_action_date IN DATE DEFAULT SYSDATE --
199 ,p_change_description IN VARCHAR2 DEFAULT NULL --
200 ,p_user_id IN NUMBER DEFAULT NULL --
201 ,p_api_caller IN VARCHAR2 DEFAULT NULL --
202 ,p_raise_event_flag IN VARCHAR2 := FND_API.G_FALSE -- R12
203 ,p_local_organization_id IN NUMBER DEFAULT NULL --Bug 4704384
204 ,x_change_action_id OUT NOCOPY NUMBER --
205 )
206 IS
207 l_api_name CONSTANT VARCHAR2(30) := 'Create_Change_Action';
208 l_api_version CONSTANT NUMBER := 1.0;
209
210 -- General variables
211 l_new_action_id NUMBER;
212 l_parent_action_id NUMBER;
213 l_fnd_user_id NUMBER := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
214 l_fnd_login_id NUMBER := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
215 l_language VARCHAR2(4) := userenv('LANG');
216 l_rowid ROWID;
217
218 BEGIN
219
220 -- Standard Start of API savepoint
221 SAVEPOINT Create_Change_Action;
222
223 -- Standard call to check for call compatibility
224 IF NOT FND_API.Compatible_API_Call ( l_api_version
225 ,p_api_version
226 ,l_api_name
227 ,G_PKG_NAME )
228 THEN
229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230 END IF;
231
232 -- Initialize message list if p_init_msg_list is set to TRUE.
233 IF FND_API.to_Boolean( p_init_msg_list ) THEN
234 FND_MSG_PUB.initialize;
235 END IF ;
236
237 -- For Test/Debug
238 IF FND_API.to_Boolean( p_debug ) THEN
239 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
240 END IF ;
241
242
243
244 -- Write debug message if debug mode is on
245 IF g_debug_flag THEN
246 Write_Debug('Eng_Change_Action_Util.Create_Change_Action log');
247 Write_Debug('-----------------------------------------------------');
248 Write_Debug('p_action_type : ' || p_action_type );
249 Write_Debug('p_object_name : ' || p_object_name );
250 Write_Debug('p_object_id1 : ' || p_object_id1 );
251 Write_Debug('p_parent_action_id : ' || p_parent_action_id );
252 Write_Debug('p_status_code : ' || p_status_code );
253 Write_Debug('p_priority_code : ' || p_priority_code );
254 Write_Debug('p_assignee_id : ' || p_assignee_id );
255 Write_Debug('p_response_by_date : ' || p_response_by_date );
256 Write_Debug('p_party_id_list : ' || p_party_id_list );
257 Write_Debug('p_workflow_item_type: ' || p_workflow_item_type );
258 Write_Debug('p_workflow_item_key : ' || p_workflow_item_key );
259 Write_Debug('p_route_id : ' || p_workflow_item_key );
260 Write_Debug('p_action_date : ' || p_workflow_item_key );
261 Write_Debug('p_change_description: ' || p_workflow_item_key );
262 Write_Debug('p_user_id : ' || p_workflow_item_key );
263 Write_Debug('p_api_caller : ' || p_api_caller );
264 Write_Debug('p_local_organization: ' || p_local_organization_id );
265 Write_Debug('-----------------------------------------------------');
266 Write_Debug('Initializing return status... ' );
267 END IF ;
268
269 -- Initialize API return status to success
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272
273 -- Real code starts here
274 -- FND_PROFILE package is not available for workflow (WF),
275 -- therefore manually set WHO column values
276 IF ( p_api_caller = 'WF' OR p_api_caller = 'CP' )
277 THEN
278 l_fnd_user_id := p_user_id;
279 l_fnd_login_id := '';
280 END IF;
281
282 SELECT eng_change_actions_s.nextval into l_new_action_id
283 FROM DUAL;
284
285 -- make sure it is called
286 IF ( p_parent_action_id IS NULL)
287 THEN
288 l_parent_action_id := -1;
289 ELSE
290 l_parent_action_id := p_parent_action_id;
291 END IF;
292
293 ENG_CHANGE_ACTIONS_PKG.INSERT_ROW(
294 X_ROWID => l_rowid, -- rowid (in/out)
295 X_ACTION_ID => l_new_action_id, -- action_id
296 X_ORIGINAL_SYSTEM_REFERENCE => null, -- original_system_reference
297 X_WORKFLOW_ITEM_KEY => p_workflow_item_key, -- workflow_item_key
298 X_REQUEST_ID => null, -- request_id
299 X_STATUS_CODE => p_status_code, -- status_code
300 X_PRIORITY_CODE => p_priority_code, -- priority_code
301 X_ASSIGNEE_ID => p_assignee_id, -- assignee_id
302 X_RESPONSE_BY_DATE => p_response_by_date, -- response_by_date
303 X_PARTY_ID_LIST => p_party_id_list, -- party_id_list
304 X_PARENT_STATUS_CODE => p_parent_status_code, -- parent_status_code
305 X_WORKFLOW_ITEM_TYPE => p_workflow_item_type, -- workflow_item_type
306 X_ROUTE_ID => p_route_id, -- route_id for approval routing
307 X_PARENT_ACTION_ID => l_parent_action_id, -- parent_action_id
308 X_ACTION_TYPE => p_action_type, -- action_type
309 X_OBJECT_NAME => p_object_name, -- object_name
310 X_OBJECT_ID1 => p_object_id1,
311 X_OBJECT_ID2 => p_object_id2,
312 X_OBJECT_ID3 => p_object_id3,
313 X_OBJECT_ID4 => p_object_id4,
314 X_OBJECT_ID5 => p_object_id5,
315 X_DESCRIPTION => p_change_description, -- description
316 X_PROGRAM_ID => null, -- program_id
317 X_PROGRAM_APPLICATION_ID => null, -- program_application_id
318 X_PROGRAM_UPDATE_DATE => null, -- program_update_date
319 X_CREATION_DATE => p_action_date, -- creation_date
320 X_CREATED_BY => l_fnd_user_id, -- created_by
321 X_LAST_UPDATE_DATE => p_action_date, -- last_update_date
322 X_LAST_UPDATED_BY => l_fnd_user_id, -- last_updated_by
323 X_LAST_UPDATE_LOGIN => l_fnd_login_id -- last_update_login
324 -- X_IMPLEMENTATION_REQ_ID => NULL
325 ,X_LOCAL_ORGANIZATION_ID => p_local_organization_id --Bug 4704384
326 ) ;
327
328 x_change_action_id := l_new_action_id;
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330
331
332 IF g_debug_flag THEN
333 Write_Debug('Action row inserted successfully... ' );
334 Write_Debug(' x_change_action_id : ' || x_change_action_id );
335 END IF ;
336
337 --
338 -- R12 Added
339 -- Raise Business Event in ACTION_TYPE is "REPLIED"
340 -- in case that REPLIED action is recorded by Workflow
341 -- response.
342 -- In case of UI, it will be handled by ChangeActionAMImpl
343 --
344 --
345 IF FND_API.to_Boolean( p_raise_event_flag )
346 THEN
347
348 IF g_debug_flag THEN
349 Write_Debug('p_raise_event_flag is True... ' );
350 END IF ;
351
352 IF p_action_type = G_ACT_REPLIED
353 THEN
354
355 ENG_CHANGE_BES_UTIL.Raise_Post_Comment_Event
356 ( p_change_id => p_object_id1
357 ,p_action_type => p_action_type
358 ,p_action_id => l_new_action_id
359 );
360
361 IF g_debug_flag THEN
362 Write_Debug('After calling ENG_CHANGE_BES_UTIL.Raise_Post_Comment_Event. .. ' );
363 END IF ;
364
365
366 END IF ;
367
368 END IF ; -- p_raise_event_flag is true
369
370
371
372
373
374 FND_MSG_PUB.Count_And_Get
375 ( p_count => x_msg_count,
376 p_data => x_msg_data );
377
378
379 IF g_debug_flag THEN
380 Write_Debug('Finish. Eng Of Proc') ;
381 Close_Debug_Session ;
382 END IF ;
383
384
385 EXCEPTION
386 WHEN FND_API.G_EXC_ERROR THEN
387 ROLLBACK TO Create_Change_Action;
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 FND_MSG_PUB.Count_And_Get
390 ( p_count => x_msg_count
391 ,p_data => x_msg_data );
392 IF g_debug_flag THEN
393 Write_Debug('Rollback and Finish with unxepcted error.') ;
394 Close_Debug_Session ;
395 END IF ;
396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
397 ROLLBACK TO Create_Change_Action;
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 FND_MSG_PUB.Count_And_Get
400 ( p_count => x_msg_count
401 ,p_data => x_msg_data );
402 IF g_debug_flag THEN
403 Write_Debug('Rollback and Finish with unxepcted error.') ;
404 Close_Debug_Session ;
405 END IF ;
406 WHEN OTHERS THEN
407 ROLLBACK TO Create_Change_Action;
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
409 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
410 THEN
411 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
412 END IF;
413 FND_MSG_PUB.Count_And_Get
414 ( p_count => x_msg_count
415 ,p_data => x_msg_data );
416 IF g_debug_flag THEN
417 Write_Debug('Rollback and Finish with unxepcted error.') ;
418 Close_Debug_Session ;
419 END IF ;
420
421 END Create_Change_Action;
422
423
424
425 /**
426 * Called right after creating comment row and submitting workflow
427 * to update the workflow-related columns value given the action_id
428 */
429 PROCEDURE Update_Workflow_Info
430 (
431 p_api_version IN NUMBER --
432 ,p_init_msg_list IN VARCHAR2 --
433 ,p_commit IN VARCHAR2 --
434 ,p_validation_level IN NUMBER --
435 ,p_debug IN VARCHAR2 --
436 ,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log'
437 ,p_debug_filename IN VARCHAR2 --
438 ,x_return_status OUT NOCOPY VARCHAR2 --
439 ,x_msg_count OUT NOCOPY NUMBER --
440 ,x_msg_data OUT NOCOPY VARCHAR2 --
441 ,p_change_action_id IN NUMBER --
442 ,p_workflow_item_type IN VARCHAR2 --
443 ,p_workflow_item_key IN VARCHAR2 --
444 )
445 IS
446 l_api_name CONSTANT VARCHAR2(30) := 'Update_Workflow_Info';
447 l_api_version CONSTANT NUMBER := 1.0;
448 BEGIN
449 -- Standard Start of API savepoint
450 SAVEPOINT Update_Workflow_Info_PUB;
451
452 -- Standard call to check for call compatibility
453 IF NOT FND_API.Compatible_API_Call ( l_api_version
454 ,p_api_version
455 ,l_api_name
456 ,G_PKG_NAME )
457 THEN
458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
459 END IF;
460
461 -- Initialize message list if p_init_msg_list is set to TRUE.
462 IF FND_API.to_Boolean( p_init_msg_list ) THEN
463 FND_MSG_PUB.initialize;
464 END IF ;
465
466 -- For Test/Debug
467 IF FND_API.to_Boolean( p_debug ) THEN
468 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
469 END IF ;
470
471 -- Write debug message if debug mode is on
472 IF g_debug_flag THEN
473 Write_Debug('Eng_Change_Action_Util.Update_Workflow_Info log');
474 Write_Debug('-----------------------------------------------------');
475 Write_Debug('Action Id : ' || p_change_action_id );
476 Write_Debug('Workflow Item Type: ' || p_workflow_item_type );
477 Write_Debug('Workflow Item Key : ' || p_workflow_item_key );
478 Write_Debug('-----------------------------------------------------');
479 Write_Debug('Initializing return status... ' );
480 END IF ;
481
482 -- Initialize API return status to success
483 x_return_status := FND_API.G_RET_STS_SUCCESS;
484
485 -- Update the comment table
486 UPDATE Eng_Change_Actions
487 SET workflow_item_type = p_workflow_item_type
488 ,workflow_item_key = p_workflow_item_key
489 WHERE action_id = p_change_action_id;
490
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492
493 IF g_debug_flag THEN
494 Write_Debug('Action row updated successfully... ' );
495 END IF ;
496
497 FND_MSG_PUB.Count_And_Get
498 ( p_count => x_msg_count,
499 p_data => x_msg_data );
500
501 IF g_debug_flag THEN
502 Write_Debug('Finish. Eng Of Proc') ;
503 Close_Debug_Session ;
504 END IF ;
505
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR THEN
508 ROLLBACK TO Update_Workflow_Info_PUB;
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 FND_MSG_PUB.Count_And_Get
511 ( p_count => x_msg_count
512 ,p_data => x_msg_data );
513 IF g_debug_flag THEN
514 Write_Debug('Rollback and Finish with unxepcted error.') ;
515 Close_Debug_Session ;
516 END IF ;
517 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
518 ROLLBACK TO Update_Workflow_Info_PUB;
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520 FND_MSG_PUB.Count_And_Get
521 ( p_count => x_msg_count
522 ,p_data => x_msg_data );
523 IF g_debug_flag THEN
524 Write_Debug('Rollback and Finish with unxepcted error.') ;
525 Close_Debug_Session ;
526 END IF ;
527 WHEN OTHERS THEN
528 ROLLBACK TO Update_Workflow_Info_PUB;
529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
530 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
531 THEN
532 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
533 END IF;
534 FND_MSG_PUB.Count_And_Get
535 ( p_count => x_msg_count
536 ,p_data => x_msg_data );
537 IF g_debug_flag THEN
538 Write_Debug('Rollback and Finish with unxepcted error.') ;
539 Close_Debug_Session ;
540 END IF ;
541
542 END Update_Workflow_Info;
543
544
545 /**
546 * If the responder is on the comment request party list, this procedure
547 * updates the workflow notification with the response comment, and returns
548 * the corresponding notification id;
549 * otherwise no update is performed and notification_id is returned as null
550 */
551 PROCEDURE Respond_Notification
552 (
553 p_api_version IN NUMBER --
554 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
555 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
556 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
557 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
558 ,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log' --
559 ,p_debug_filename IN VARCHAR2 := 'eng.chgmt.action.respNotif.log'
560 ,x_return_status OUT NOCOPY VARCHAR2 --
561 ,x_msg_count OUT NOCOPY NUMBER --
562 ,x_msg_data OUT NOCOPY VARCHAR2 --
563 ,p_parent_action_id IN NUMBER --
564 ,p_comment IN VARCHAR2 DEFAULT NULL --
565 ,p_fnd_user_name IN VARCHAR2 --
566 ,x_processed_ntf_id OUT NOCOPY NUMBER --
567 )
568 IS
569 l_api_name CONSTANT VARCHAR2(30) := 'Respond_Notification';
570 l_api_version CONSTANT NUMBER := 1.0;
571
572 l_action_type VARCHAR2(30);
573 l_wf_item_type VARCHAR2(8);
574 l_wf_item_key VARCHAR2(240);
575
576 l_return_status VARCHAR2(1);
577 l_msg_count NUMBER;
578 l_msg_data VARCHAR2(2000);
579
580 BEGIN
581 -- Standard Start of API savepoint
582 SAVEPOINT Respond_Notification_PUB;
583
584 -- Standard call to check for call compatibility
585 IF NOT FND_API.Compatible_API_Call ( l_api_version
586 ,p_api_version
587 ,l_api_name
588 ,G_PKG_NAME )
589 THEN
590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
591 END IF;
592
593 -- Initialize message list if p_init_msg_list is set to TRUE.
594 IF FND_API.to_Boolean( p_init_msg_list ) THEN
595 FND_MSG_PUB.initialize;
596 END IF ;
597
598 -- For Test/Debug
599 IF FND_API.to_Boolean( p_debug ) THEN
600 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
601 END IF ;
602
603 -- Write debug message if debug mode is on
604 IF g_debug_flag THEN
605 Write_Debug('Eng_Change_Action_Util.Respond_Notification log');
606 Write_Debug('-----------------------------------------------------');
607 Write_Debug('p_parent_action_id : ' || p_parent_action_id );
608 Write_Debug('p_comment : ' || p_comment );
609 Write_Debug('p_fnd_user_name : ' || p_fnd_user_name );
610 Write_Debug('-----------------------------------------------------');
611 Write_Debug('Initializing return status... ' );
612 END IF ;
613
614 -- Initialize API return status to success
615 x_return_status := FND_API.G_RET_STS_SUCCESS;
616
617 -- look up parent action's action type, workflow item type and item key
618 SELECT action_type, workflow_item_type, workflow_item_key
619 INTO l_action_type, l_wf_item_type, l_wf_item_key
620 FROM eng_change_actions
621 where action_id = p_parent_action_id;
622
623 ENG_WORKFLOW_UTIL.RespondToActReqCommentFromUI
624 ( x_return_status => l_return_status
625 ,x_msg_count => l_msg_count
626 ,x_msg_data => l_msg_data
627 ,x_processed_ntf_id => x_processed_ntf_id
628 ,p_item_type => l_wf_item_type
629 ,p_item_key => l_wf_item_key
630 ,p_responder => p_fnd_user_name
631 ,p_response_comment => p_comment
632 ,p_action_source => NULL
633 );
634
635 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
636 THEN
637 x_return_status := l_return_status;
638 x_msg_count := l_msg_count;
639 x_msg_data := l_msg_data;
640 --#FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_WF_API');
641 --#FND_MSG_PUB.Add;
642 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
643 END IF;
644 IF g_debug_flag THEN
645 Write_Debug('Successful: Calling ENG_WORKFLOW_UTIL.RespondToActReqCommentFromUI');
646 END IF;
647
648
649 FND_MSG_PUB.Count_And_Get
650 ( p_count => x_msg_count,
651 p_data => x_msg_data );
652
653 IF g_debug_flag THEN
654 Write_Debug('Finish. Eng Of Proc') ;
655 Close_Debug_Session ;
656 END IF ;
657
658 EXCEPTION
659 WHEN FND_API.G_EXC_ERROR THEN
660 ROLLBACK TO Respond_Notification_PUB;
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 FND_MSG_PUB.Count_And_Get
663 ( p_count => x_msg_count
664 ,p_data => x_msg_data );
665 IF g_debug_flag THEN
666 Write_Debug('Rollback and Finish with unxepcted error.') ;
667 Close_Debug_Session ;
668 END IF ;
669 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
670 ROLLBACK TO Respond_Notification_PUB;
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 FND_MSG_PUB.Count_And_Get
673 ( p_count => x_msg_count
674 ,p_data => x_msg_data );
675 IF g_debug_flag THEN
676 Write_Debug('Rollback and Finish with unxepcted error.') ;
677 Close_Debug_Session ;
678 END IF ;
679 WHEN OTHERS THEN
680 ROLLBACK TO Respond_Notification_PUB;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
682 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
683 THEN
684 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
685 END IF;
686 FND_MSG_PUB.Count_And_Get
687 ( p_count => x_msg_count
688 ,p_data => x_msg_data );
689 IF g_debug_flag THEN
690 Write_Debug('Rollback and Finish with unxepcted error.') ;
691 Close_Debug_Session ;
692 END IF ;
693
694 END Respond_Notification;
695
696
697 /**
698 * -- Workflow Utility Function
699 * Get notification id given workflow item type, workflow item key,
700 * and current logon user name, returns null if id doesn't exist
701 */
702 /*
703 FUNCTION Get_Workflow_Notif_Id
704 (
705 p_workflow_item_type IN VARCHAR2 --
706 ,p_workflow_item_key IN VARCHAR2 --
707 ,p_username IN VARCHAR2 --
708 ) RETURN NUMBER
709 IS
710 l_ntf_id number ;
711 CURSOR c_waiting_ntf ( p_workflow_item_type varchar2,
712 p_workflow_item_key varchar2,
713 p_username varchar2 )
714 IS
715 SELECT ntf.notification_id
716 FROM wf_item_activity_statuses ias,
717 wf_notifications ntf
718 WHERE ias.item_type = p_workflow_item_type
719 AND ias.item_key = p_workflow_item_key
720 AND ias.notification_id IS NOT NULL
721 AND ias.notification_id = ntf.group_id
722 AND ntf.recipient_role = p_username
723 AND ntf.status = 'OPEN'
724 AND ( EXISTS
725 ( SELECT NULL
726 FROM WF_MESSAGE_ATTRIBUTES WMA
727 WHERE WMA.MESSAGE_NAME = ntf.MESSAGE_NAME
728 AND WMA.MESSAGE_TYPE = ntf.MESSAGE_TYPE
729 AND WMA.MESSAGE_NAME <> 'ENG_STATUS_REVIEW'
730 AND WMA.SUBTYPE = 'RESPOND' )
731 );
732 BEGIN
733 IF p_workflow_item_type IS NOT NULL AND
734 p_workflow_item_key IS NOT NULL AND
735 p_username IS NOT NULL
736 THEN
737 FOR l_rec IN c_waiting_ntf ( p_workflow_item_type,
738 p_workflow_item_key,
739 p_username )
740 LOOP
741 l_ntf_id := l_rec.notification_id ;
742 END LOOP ;
743 END IF ;
744
745 return l_ntf_id ;
746
747 END Get_Workflow_Notif_Id;
748 */
749
750
751 /**
752 * Called when a line is deleted
753 * to delete all the action log entries associated with that line
754 */
755 PROCEDURE Delete_Line_Actions
756 (
757 p_api_version IN NUMBER --
758 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
759 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
760 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
761 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
762 ,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log' --
763 ,p_debug_filename IN VARCHAR2 := 'eng.chgmt.action.line.log'
764 ,x_return_status OUT NOCOPY VARCHAR2 --
765 ,x_msg_count OUT NOCOPY NUMBER --
766 ,x_msg_data OUT NOCOPY VARCHAR2 --
767 ,p_change_id IN NUMBER
768 ,p_change_line_id IN NUMBER
769 )
770 IS
771 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Line_Actions';
772 l_api_version CONSTANT NUMBER := 1.0;
773
774
775 l_action_id ENG_CHANGE_ACTIONS.ACTION_ID%TYPE;
776 CURSOR ECACursor IS
777 SELECT ACTION_ID
778 FROM ENG_CHANGE_ACTIONS
779 WHERE OBJECT_ID1 = p_change_id
780 AND OBJECT_ID2 = p_change_line_id
781 FOR UPDATE;
782
783 BEGIN
784 -- Standard Start of API savepoint
785 SAVEPOINT Delete_Line_Actions_PUB;
786
787 -- Standard call to check for call compatibility
788 IF NOT FND_API.Compatible_API_Call ( l_api_version
789 ,p_api_version
790 ,l_api_name
791 ,G_PKG_NAME )
792 THEN
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 END IF;
795
796 -- Initialize message list if p_init_msg_list is set to TRUE.
797 IF FND_API.to_Boolean( p_init_msg_list ) THEN
798 FND_MSG_PUB.initialize;
799 END IF ;
800
801 -- For Test/Debug
802 IF FND_API.to_Boolean( p_debug ) THEN
803 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
804 END IF ;
805
806 -- Write debug message if debug mode is on
807 IF g_debug_flag THEN
808 Write_Debug('Eng_Change_Action_Util.Delete_Line_Actions log');
809 Write_Debug('-----------------------------------------------------');
810 Write_Debug('Change_Id : ' || p_change_id );
811 Write_Debug('Change_Line_Id : ' || p_change_line_id );
812 Write_Debug('-----------------------------------------------------');
813 Write_Debug('Initializing return status... ' );
814 END IF ;
815
816 -- Initialize API return status to success
817 x_return_status := FND_API.G_RET_STS_SUCCESS;
818
819 -- Delete all the action log records for the line
820 OPEN ECACursor;
821 LOOP
822 FETCH ECACursor INTO l_action_id;
823 EXIT WHEN ECACursor%NOTFOUND;
824 ENG_CHANGE_ACTIONS_PKG.DELETE_ROW( l_action_id );
825 END LOOP;
826 CLOSE ECACursor;
827 -- End of Delete
828
829 x_return_status := FND_API.G_RET_STS_SUCCESS;
830
831 IF g_debug_flag THEN
832 Write_Debug('Action rows deleted successfully... ' );
833 END IF ;
834
835 FND_MSG_PUB.Count_And_Get
836 ( p_count => x_msg_count,
837 p_data => x_msg_data );
838
839 IF g_debug_flag THEN
840 Write_Debug('Finish. Eng Of Proc') ;
841 Close_Debug_Session ;
842 END IF ;
843
844 EXCEPTION
845 WHEN FND_API.G_EXC_ERROR THEN
846 ROLLBACK TO Delete_Line_Actions_PUB;
847 x_return_status := FND_API.G_RET_STS_ERROR;
848 FND_MSG_PUB.Count_And_Get
849 ( p_count => x_msg_count
850 ,p_data => x_msg_data );
851 IF g_debug_flag THEN
852 Write_Debug('Rollback and Finish with unxepcted error.') ;
853 Close_Debug_Session ;
854 END IF ;
855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
856 ROLLBACK TO Delete_Line_Actions_PUB;
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858 FND_MSG_PUB.Count_And_Get
859 ( p_count => x_msg_count
860 ,p_data => x_msg_data );
861 IF g_debug_flag THEN
862 Write_Debug('Rollback and Finish with unxepcted error.') ;
863 Close_Debug_Session ;
864 END IF ;
865 WHEN OTHERS THEN
866 ROLLBACK TO Delete_Line_Actions_PUB;
867 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
868 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
869 THEN
870 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
871 END IF;
872 FND_MSG_PUB.Count_And_Get
873 ( p_count => x_msg_count
874 ,p_data => x_msg_data );
875 IF g_debug_flag THEN
876 Write_Debug('Rollback and Finish with unxepcted error.') ;
877 Close_Debug_Session ;
878 END IF ;
879
880 END Delete_Line_Actions;
881
882
883 END ENG_CHANGE_ACTIONS_UTIL;
884