[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_ACTIONS_UTIL
Source
1 PACKAGE BODY ENG_CHANGE_ACTIONS_UTIL AS
2 /* $Header: ENGUCCMB.pls 120.3.12010000.2 2009/11/04 07:33:07 maychen ship $ */
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 -- Bug 8921326, init message list if any msg in msg_list before ENG_CHANGE_ACTIONS_PKG.INSERT_ROW
234 IF FND_API.to_Boolean( p_init_msg_list ) OR (FND_MSG_PUB.Count_Msg >0) THEN
235 FND_MSG_PUB.initialize;
236 END IF ;
237
238 -- For Test/Debug
239 IF FND_API.to_Boolean( p_debug ) THEN
240 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
241 END IF ;
242
243
244
245 -- Write debug message if debug mode is on
246 IF g_debug_flag THEN
247 Write_Debug('Eng_Change_Action_Util.Create_Change_Action log');
248 Write_Debug('-----------------------------------------------------');
249 Write_Debug('p_action_type : ' || p_action_type );
250 Write_Debug('p_object_name : ' || p_object_name );
251 Write_Debug('p_object_id1 : ' || p_object_id1 );
252 Write_Debug('p_parent_action_id : ' || p_parent_action_id );
253 Write_Debug('p_status_code : ' || p_status_code );
254 Write_Debug('p_priority_code : ' || p_priority_code );
255 Write_Debug('p_assignee_id : ' || p_assignee_id );
256 Write_Debug('p_response_by_date : ' || p_response_by_date );
257 Write_Debug('p_party_id_list : ' || p_party_id_list );
258 Write_Debug('p_workflow_item_type: ' || p_workflow_item_type );
259 Write_Debug('p_workflow_item_key : ' || p_workflow_item_key );
260 Write_Debug('p_route_id : ' || p_workflow_item_key );
261 Write_Debug('p_action_date : ' || p_workflow_item_key );
262 Write_Debug('p_change_description: ' || p_workflow_item_key );
263 Write_Debug('p_user_id : ' || p_workflow_item_key );
264 Write_Debug('p_api_caller : ' || p_api_caller );
265 Write_Debug('p_local_organization: ' || p_local_organization_id );
266 Write_Debug('-----------------------------------------------------');
267 Write_Debug('Initializing return status... ' );
268 END IF ;
269
270 -- Initialize API return status to success
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272
273
274 -- Real code starts here
275 -- FND_PROFILE package is not available for workflow (WF),
276 -- therefore manually set WHO column values
277 IF ( p_api_caller = 'WF' OR p_api_caller = 'CP' )
278 THEN
279 l_fnd_user_id := p_user_id;
280 l_fnd_login_id := '';
281 END IF;
282
283 SELECT eng_change_actions_s.nextval into l_new_action_id
284 FROM DUAL;
285
286 -- make sure it is called
287 IF ( p_parent_action_id IS NULL)
288 THEN
289 l_parent_action_id := -1;
290 ELSE
291 l_parent_action_id := p_parent_action_id;
292 END IF;
293
294 ENG_CHANGE_ACTIONS_PKG.INSERT_ROW(
295 X_ROWID => l_rowid, -- rowid (in/out)
296 X_ACTION_ID => l_new_action_id, -- action_id
297 X_ORIGINAL_SYSTEM_REFERENCE => null, -- original_system_reference
298 X_WORKFLOW_ITEM_KEY => p_workflow_item_key, -- workflow_item_key
299 X_REQUEST_ID => null, -- request_id
300 X_STATUS_CODE => p_status_code, -- status_code
301 X_PRIORITY_CODE => p_priority_code, -- priority_code
302 X_ASSIGNEE_ID => p_assignee_id, -- assignee_id
303 X_RESPONSE_BY_DATE => p_response_by_date, -- response_by_date
304 X_PARTY_ID_LIST => p_party_id_list, -- party_id_list
305 X_PARENT_STATUS_CODE => p_parent_status_code, -- parent_status_code
306 X_WORKFLOW_ITEM_TYPE => p_workflow_item_type, -- workflow_item_type
307 X_ROUTE_ID => p_route_id, -- route_id for approval routing
308 X_PARENT_ACTION_ID => l_parent_action_id, -- parent_action_id
309 X_ACTION_TYPE => p_action_type, -- action_type
310 X_OBJECT_NAME => p_object_name, -- object_name
311 X_OBJECT_ID1 => p_object_id1,
312 X_OBJECT_ID2 => p_object_id2,
313 X_OBJECT_ID3 => p_object_id3,
314 X_OBJECT_ID4 => p_object_id4,
315 X_OBJECT_ID5 => p_object_id5,
316 X_DESCRIPTION => p_change_description, -- description
317 X_PROGRAM_ID => null, -- program_id
318 X_PROGRAM_APPLICATION_ID => null, -- program_application_id
319 X_PROGRAM_UPDATE_DATE => null, -- program_update_date
320 X_CREATION_DATE => p_action_date, -- creation_date
321 X_CREATED_BY => l_fnd_user_id, -- created_by
322 X_LAST_UPDATE_DATE => p_action_date, -- last_update_date
323 X_LAST_UPDATED_BY => l_fnd_user_id, -- last_updated_by
324 X_LAST_UPDATE_LOGIN => l_fnd_login_id -- last_update_login
325 -- X_IMPLEMENTATION_REQ_ID => NULL
326 ,X_LOCAL_ORGANIZATION_ID => p_local_organization_id --Bug 4704384
327 ) ;
328
329 x_change_action_id := l_new_action_id;
330 x_return_status := FND_API.G_RET_STS_SUCCESS;
331
332
333 IF g_debug_flag THEN
334 Write_Debug('Action row inserted successfully... ' );
335 Write_Debug(' x_change_action_id : ' || x_change_action_id );
336 END IF ;
337
338 --
339 -- R12 Added
340 -- Raise Business Event in ACTION_TYPE is "REPLIED"
341 -- in case that REPLIED action is recorded by Workflow
342 -- response.
343 -- In case of UI, it will be handled by ChangeActionAMImpl
344 --
345 --
346 IF FND_API.to_Boolean( p_raise_event_flag )
347 THEN
348
349 IF g_debug_flag THEN
350 Write_Debug('p_raise_event_flag is True... ' );
351 END IF ;
352
353 IF p_action_type = G_ACT_REPLIED
354 THEN
355
356 ENG_CHANGE_BES_UTIL.Raise_Post_Comment_Event
357 ( p_change_id => p_object_id1
358 ,p_action_type => p_action_type
359 ,p_action_id => l_new_action_id
360 );
361
362 IF g_debug_flag THEN
363 Write_Debug('After calling ENG_CHANGE_BES_UTIL.Raise_Post_Comment_Event. .. ' );
364 END IF ;
365
366
367 END IF ;
368
369 END IF ; -- p_raise_event_flag is true
370
371
372
373
374
375 FND_MSG_PUB.Count_And_Get
376 ( p_count => x_msg_count,
377 p_data => x_msg_data );
378
379
380 IF g_debug_flag THEN
381 Write_Debug('Finish. Eng Of Proc') ;
382 Close_Debug_Session ;
383 END IF ;
384
385
386 EXCEPTION
387 WHEN FND_API.G_EXC_ERROR THEN
388 ROLLBACK TO Create_Change_Action;
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 FND_MSG_PUB.Count_And_Get
391 ( p_count => x_msg_count
392 ,p_data => x_msg_data );
393 IF g_debug_flag THEN
394 Write_Debug('Rollback and Finish with unxepcted error.') ;
395 Close_Debug_Session ;
396 END IF ;
397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
398 ROLLBACK TO Create_Change_Action;
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400 FND_MSG_PUB.Count_And_Get
401 ( p_count => x_msg_count
402 ,p_data => x_msg_data );
403 IF g_debug_flag THEN
404 Write_Debug('Rollback and Finish with unxepcted error.') ;
405 Close_Debug_Session ;
406 END IF ;
407 WHEN OTHERS THEN
408 ROLLBACK TO Create_Change_Action;
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
413 END IF;
410 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
411 THEN
412 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
414 FND_MSG_PUB.Count_And_Get
415 ( p_count => x_msg_count
416 ,p_data => x_msg_data );
417 IF g_debug_flag THEN
418 Write_Debug('Rollback and Finish with unxepcted error.') ;
419 Close_Debug_Session ;
420 END IF ;
421
422 END Create_Change_Action;
423
424
425
426 /**
427 * Called right after creating comment row and submitting workflow
428 * to update the workflow-related columns value given the action_id
429 */
430 PROCEDURE Update_Workflow_Info
431 (
432 p_api_version IN NUMBER --
433 ,p_init_msg_list IN VARCHAR2 --
434 ,p_commit IN VARCHAR2 --
435 ,p_validation_level IN NUMBER --
436 ,p_debug IN VARCHAR2 --
437 ,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log'
438 ,p_debug_filename IN VARCHAR2 --
439 ,x_return_status OUT NOCOPY VARCHAR2 --
440 ,x_msg_count OUT NOCOPY NUMBER --
441 ,x_msg_data OUT NOCOPY VARCHAR2 --
442 ,p_change_action_id IN NUMBER --
443 ,p_workflow_item_type IN VARCHAR2 --
444 ,p_workflow_item_key IN VARCHAR2 --
445 )
446 IS
447 l_api_name CONSTANT VARCHAR2(30) := 'Update_Workflow_Info';
448 l_api_version CONSTANT NUMBER := 1.0;
449 BEGIN
450 -- Standard Start of API savepoint
451 SAVEPOINT Update_Workflow_Info_PUB;
452
453 -- Standard call to check for call compatibility
454 IF NOT FND_API.Compatible_API_Call ( l_api_version
455 ,p_api_version
456 ,l_api_name
457 ,G_PKG_NAME )
458 THEN
459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
460 END IF;
461
462 -- Initialize message list if p_init_msg_list is set to TRUE.
463 IF FND_API.to_Boolean( p_init_msg_list ) THEN
464 FND_MSG_PUB.initialize;
465 END IF ;
466
467 -- For Test/Debug
468 IF FND_API.to_Boolean( p_debug ) THEN
469 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
470 END IF ;
471
472 -- Write debug message if debug mode is on
473 IF g_debug_flag THEN
474 Write_Debug('Eng_Change_Action_Util.Update_Workflow_Info log');
475 Write_Debug('-----------------------------------------------------');
476 Write_Debug('Action Id : ' || p_change_action_id );
477 Write_Debug('Workflow Item Type: ' || p_workflow_item_type );
478 Write_Debug('Workflow Item Key : ' || p_workflow_item_key );
479 Write_Debug('-----------------------------------------------------');
480 Write_Debug('Initializing return status... ' );
481 END IF ;
482
483 -- Initialize API return status to success
484 x_return_status := FND_API.G_RET_STS_SUCCESS;
485
486 -- Update the comment table
487 UPDATE Eng_Change_Actions
488 SET workflow_item_type = p_workflow_item_type
489 ,workflow_item_key = p_workflow_item_key
490 WHERE action_id = p_change_action_id;
491
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493
494 IF g_debug_flag THEN
495 Write_Debug('Action row updated successfully... ' );
496 END IF ;
497
498 FND_MSG_PUB.Count_And_Get
499 ( p_count => x_msg_count,
500 p_data => x_msg_data );
501
502 IF g_debug_flag THEN
503 Write_Debug('Finish. Eng Of Proc') ;
504 Close_Debug_Session ;
505 END IF ;
506
507 EXCEPTION
508 WHEN FND_API.G_EXC_ERROR THEN
509 ROLLBACK TO Update_Workflow_Info_PUB;
510 x_return_status := FND_API.G_RET_STS_ERROR;
511 FND_MSG_PUB.Count_And_Get
512 ( p_count => x_msg_count
513 ,p_data => x_msg_data );
514 IF g_debug_flag THEN
515 Write_Debug('Rollback and Finish with unxepcted error.') ;
516 Close_Debug_Session ;
517 END IF ;
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519 ROLLBACK TO Update_Workflow_Info_PUB;
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521 FND_MSG_PUB.Count_And_Get
522 ( p_count => x_msg_count
523 ,p_data => x_msg_data );
524 IF g_debug_flag THEN
525 Write_Debug('Rollback and Finish with unxepcted error.') ;
526 Close_Debug_Session ;
527 END IF ;
528 WHEN OTHERS THEN
529 ROLLBACK TO Update_Workflow_Info_PUB;
530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
531 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
532 THEN
533 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
534 END IF;
535 FND_MSG_PUB.Count_And_Get
536 ( p_count => x_msg_count
537 ,p_data => x_msg_data );
538 IF g_debug_flag THEN
539 Write_Debug('Rollback and Finish with unxepcted error.') ;
540 Close_Debug_Session ;
541 END IF ;
542
543 END Update_Workflow_Info;
544
545
546 /**
547 * If the responder is on the comment request party list, this procedure
551 */
548 * updates the workflow notification with the response comment, and returns
549 * the corresponding notification id;
550 * otherwise no update is performed and notification_id is returned as null
552 PROCEDURE Respond_Notification
553 (
554 p_api_version IN NUMBER --
555 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
556 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
557 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
558 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
559 ,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log' --
560 ,p_debug_filename IN VARCHAR2 := 'eng.chgmt.action.respNotif.log'
561 ,x_return_status OUT NOCOPY VARCHAR2 --
562 ,x_msg_count OUT NOCOPY NUMBER --
563 ,x_msg_data OUT NOCOPY VARCHAR2 --
564 ,p_parent_action_id IN NUMBER --
565 ,p_comment IN VARCHAR2 DEFAULT NULL --
566 ,p_fnd_user_name IN VARCHAR2 --
567 ,x_processed_ntf_id OUT NOCOPY NUMBER --
568 )
569 IS
570 l_api_name CONSTANT VARCHAR2(30) := 'Respond_Notification';
571 l_api_version CONSTANT NUMBER := 1.0;
572
573 l_action_type VARCHAR2(30);
574 l_wf_item_type VARCHAR2(8);
575 l_wf_item_key VARCHAR2(240);
576
577 l_return_status VARCHAR2(1);
578 l_msg_count NUMBER;
579 l_msg_data VARCHAR2(2000);
580
581 BEGIN
582 -- Standard Start of API savepoint
583 SAVEPOINT Respond_Notification_PUB;
584
585 -- Standard call to check for call compatibility
586 IF NOT FND_API.Compatible_API_Call ( l_api_version
587 ,p_api_version
588 ,l_api_name
589 ,G_PKG_NAME )
590 THEN
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 END IF;
593
594 -- Initialize message list if p_init_msg_list is set to TRUE.
595 IF FND_API.to_Boolean( p_init_msg_list ) THEN
596 FND_MSG_PUB.initialize;
597 END IF ;
598
599 -- For Test/Debug
600 IF FND_API.to_Boolean( p_debug ) THEN
601 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
602 END IF ;
603
604 -- Write debug message if debug mode is on
605 IF g_debug_flag THEN
606 Write_Debug('Eng_Change_Action_Util.Respond_Notification log');
607 Write_Debug('-----------------------------------------------------');
608 Write_Debug('p_parent_action_id : ' || p_parent_action_id );
609 Write_Debug('p_comment : ' || p_comment );
610 Write_Debug('p_fnd_user_name : ' || p_fnd_user_name );
611 Write_Debug('-----------------------------------------------------');
612 Write_Debug('Initializing return status... ' );
613 END IF ;
614
615 -- Initialize API return status to success
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617
618 -- look up parent action's action type, workflow item type and item key
619 SELECT action_type, workflow_item_type, workflow_item_key
620 INTO l_action_type, l_wf_item_type, l_wf_item_key
621 FROM eng_change_actions
622 where action_id = p_parent_action_id;
623
624 ENG_WORKFLOW_UTIL.RespondToActReqCommentFromUI
625 ( x_return_status => l_return_status
626 ,x_msg_count => l_msg_count
627 ,x_msg_data => l_msg_data
628 ,x_processed_ntf_id => x_processed_ntf_id
629 ,p_item_type => l_wf_item_type
630 ,p_item_key => l_wf_item_key
631 ,p_responder => p_fnd_user_name
632 ,p_response_comment => p_comment
633 ,p_action_source => NULL
634 );
635
636 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
637 THEN
638 x_return_status := l_return_status;
639 x_msg_count := l_msg_count;
640 x_msg_data := l_msg_data;
641 --#FND_MESSAGE.Set_Name('ENG','ENG_ERROR_CALLING_WF_API');
642 --#FND_MSG_PUB.Add;
643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
644 END IF;
645 IF g_debug_flag THEN
646 Write_Debug('Successful: Calling ENG_WORKFLOW_UTIL.RespondToActReqCommentFromUI');
647 END IF;
648
649
650 FND_MSG_PUB.Count_And_Get
651 ( p_count => x_msg_count,
652 p_data => x_msg_data );
653
654 IF g_debug_flag THEN
655 Write_Debug('Finish. Eng Of Proc') ;
656 Close_Debug_Session ;
657 END IF ;
658
659 EXCEPTION
660 WHEN FND_API.G_EXC_ERROR THEN
661 ROLLBACK TO Respond_Notification_PUB;
662 x_return_status := FND_API.G_RET_STS_ERROR;
663 FND_MSG_PUB.Count_And_Get
664 ( p_count => x_msg_count
665 ,p_data => x_msg_data );
666 IF g_debug_flag THEN
667 Write_Debug('Rollback and Finish with unxepcted error.') ;
668 Close_Debug_Session ;
669 END IF ;
670 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
671 ROLLBACK TO Respond_Notification_PUB;
672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
673 FND_MSG_PUB.Count_And_Get
674 ( p_count => x_msg_count
675 ,p_data => x_msg_data );
676 IF g_debug_flag THEN
677 Write_Debug('Rollback and Finish with unxepcted error.') ;
678 Close_Debug_Session ;
679 END IF ;
680 WHEN OTHERS THEN
684 THEN
681 ROLLBACK TO Respond_Notification_PUB;
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
683 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
685 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
686 END IF;
687 FND_MSG_PUB.Count_And_Get
688 ( p_count => x_msg_count
689 ,p_data => x_msg_data );
690 IF g_debug_flag THEN
691 Write_Debug('Rollback and Finish with unxepcted error.') ;
692 Close_Debug_Session ;
693 END IF ;
694
695 END Respond_Notification;
696
697
698 /**
699 * -- Workflow Utility Function
700 * Get notification id given workflow item type, workflow item key,
701 * and current logon user name, returns null if id doesn't exist
702 */
703 /*
704 FUNCTION Get_Workflow_Notif_Id
705 (
706 p_workflow_item_type IN VARCHAR2 --
707 ,p_workflow_item_key IN VARCHAR2 --
708 ,p_username IN VARCHAR2 --
709 ) RETURN NUMBER
710 IS
711 l_ntf_id number ;
712 CURSOR c_waiting_ntf ( p_workflow_item_type varchar2,
713 p_workflow_item_key varchar2,
714 p_username varchar2 )
715 IS
716 SELECT ntf.notification_id
717 FROM wf_item_activity_statuses ias,
718 wf_notifications ntf
719 WHERE ias.item_type = p_workflow_item_type
720 AND ias.item_key = p_workflow_item_key
721 AND ias.notification_id IS NOT NULL
722 AND ias.notification_id = ntf.group_id
723 AND ntf.recipient_role = p_username
724 AND ntf.status = 'OPEN'
725 AND ( EXISTS
726 ( SELECT NULL
727 FROM WF_MESSAGE_ATTRIBUTES WMA
728 WHERE WMA.MESSAGE_NAME = ntf.MESSAGE_NAME
729 AND WMA.MESSAGE_TYPE = ntf.MESSAGE_TYPE
730 AND WMA.MESSAGE_NAME <> 'ENG_STATUS_REVIEW'
731 AND WMA.SUBTYPE = 'RESPOND' )
732 );
733 BEGIN
734 IF p_workflow_item_type IS NOT NULL AND
735 p_workflow_item_key IS NOT NULL AND
736 p_username IS NOT NULL
737 THEN
738 FOR l_rec IN c_waiting_ntf ( p_workflow_item_type,
739 p_workflow_item_key,
740 p_username )
741 LOOP
742 l_ntf_id := l_rec.notification_id ;
743 END LOOP ;
744 END IF ;
745
746 return l_ntf_id ;
747
748 END Get_Workflow_Notif_Id;
749 */
750
751
752 /**
753 * Called when a line is deleted
754 * to delete all the action log entries associated with that line
755 */
756 PROCEDURE Delete_Line_Actions
757 (
758 p_api_version IN NUMBER --
759 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
760 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
761 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
762 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
763 ,p_output_dir IN VARCHAR2 := '/nfs/log/bis_top/utl/plm115dv/log' --
764 ,p_debug_filename IN VARCHAR2 := 'eng.chgmt.action.line.log'
765 ,x_return_status OUT NOCOPY VARCHAR2 --
766 ,x_msg_count OUT NOCOPY NUMBER --
767 ,x_msg_data OUT NOCOPY VARCHAR2 --
768 ,p_change_id IN NUMBER
769 ,p_change_line_id IN NUMBER
770 )
771 IS
772 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Line_Actions';
773 l_api_version CONSTANT NUMBER := 1.0;
774
775
776 l_action_id ENG_CHANGE_ACTIONS.ACTION_ID%TYPE;
777 CURSOR ECACursor IS
778 SELECT ACTION_ID
779 FROM ENG_CHANGE_ACTIONS
780 WHERE OBJECT_ID1 = p_change_id
781 AND OBJECT_ID2 = p_change_line_id
782 FOR UPDATE;
783
784 BEGIN
785 -- Standard Start of API savepoint
786 SAVEPOINT Delete_Line_Actions_PUB;
787
788 -- Standard call to check for call compatibility
789 IF NOT FND_API.Compatible_API_Call ( l_api_version
790 ,p_api_version
791 ,l_api_name
792 ,G_PKG_NAME )
793 THEN
794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
795 END IF;
796
797 -- Initialize message list if p_init_msg_list is set to TRUE.
798 IF FND_API.to_Boolean( p_init_msg_list ) THEN
799 FND_MSG_PUB.initialize;
800 END IF ;
801
802 -- For Test/Debug
803 IF FND_API.to_Boolean( p_debug ) THEN
804 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
805 END IF ;
806
807 -- Write debug message if debug mode is on
808 IF g_debug_flag THEN
809 Write_Debug('Eng_Change_Action_Util.Delete_Line_Actions log');
810 Write_Debug('-----------------------------------------------------');
811 Write_Debug('Change_Id : ' || p_change_id );
812 Write_Debug('Change_Line_Id : ' || p_change_line_id );
813 Write_Debug('-----------------------------------------------------');
814 Write_Debug('Initializing return status... ' );
815 END IF ;
816
817 -- Initialize API return status to success
818 x_return_status := FND_API.G_RET_STS_SUCCESS;
819
820 -- Delete all the action log records for the line
821 OPEN ECACursor;
822 LOOP
826 END LOOP;
823 FETCH ECACursor INTO l_action_id;
824 EXIT WHEN ECACursor%NOTFOUND;
825 ENG_CHANGE_ACTIONS_PKG.DELETE_ROW( l_action_id );
827 CLOSE ECACursor;
828 -- End of Delete
829
830 x_return_status := FND_API.G_RET_STS_SUCCESS;
831
832 IF g_debug_flag THEN
833 Write_Debug('Action rows deleted successfully... ' );
834 END IF ;
835
836 FND_MSG_PUB.Count_And_Get
837 ( p_count => x_msg_count,
838 p_data => x_msg_data );
839
840 IF g_debug_flag THEN
841 Write_Debug('Finish. Eng Of Proc') ;
842 Close_Debug_Session ;
843 END IF ;
844
845 EXCEPTION
846 WHEN FND_API.G_EXC_ERROR THEN
847 ROLLBACK TO Delete_Line_Actions_PUB;
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 FND_MSG_PUB.Count_And_Get
850 ( p_count => x_msg_count
851 ,p_data => x_msg_data );
852 IF g_debug_flag THEN
853 Write_Debug('Rollback and Finish with unxepcted error.') ;
854 Close_Debug_Session ;
855 END IF ;
856 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
857 ROLLBACK TO Delete_Line_Actions_PUB;
858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
859 FND_MSG_PUB.Count_And_Get
860 ( p_count => x_msg_count
861 ,p_data => x_msg_data );
862 IF g_debug_flag THEN
863 Write_Debug('Rollback and Finish with unxepcted error.') ;
864 Close_Debug_Session ;
865 END IF ;
866 WHEN OTHERS THEN
867 ROLLBACK TO Delete_Line_Actions_PUB;
868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
869 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
870 THEN
871 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
872 END IF;
873 FND_MSG_PUB.Count_And_Get
874 ( p_count => x_msg_count
875 ,p_data => x_msg_data );
876 IF g_debug_flag THEN
877 Write_Debug('Rollback and Finish with unxepcted error.') ;
878 Close_Debug_Session ;
879 END IF ;
880
881 END Delete_Line_Actions;
882
883
884 END ENG_CHANGE_ACTIONS_UTIL;
885