[Home] [Help]
PACKAGE BODY: APPS.PA_WORKFLOW_UTILS
Source
1 PACKAGE BODY pa_workflow_utils AS
2 /* $Header: PAWFUTLB.pls 120.3.12010000.4 2009/09/18 06:50:17 jravisha ship $ */
3
4 -- -------------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 -- -------------------------------------------------------------------------------------
7
8 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.user_id;
9 G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.login_id;
10 G_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
11
12 g_module_name VARCHAR2(100) := 'pa_workflow_utils';
13
14
15
16 -- -------------------------------------------------------------------------------------
17 -- PROCEDURES
18 -- -------------------------------------------------------------------------------------
19
20 --Name: Insert_WF_Processes
21 --Type: Procedure
22 --Description: This procedure inserts rows into the pa_wf_processes
23 -- table for the start_approval procedures.
24 --
25 --
26 --Called subprograms: none.
27 --
28 --
29 --
30 --History:
31 -- 14-JUL-97 jwhite Updated to lastest specs
32 -- 12-AUG-97 jwhite Added new IN-parameters, p_wf_type_code and
33 -- p_description, to Insert_WF_Processes.
34 --
35 -- IN Parameters
36 -- p_wf_type_code - Entity invoking workflow, i.e., 'BUDGET', 'PROJECT'.
37 -- p_item_type - Workflow Name, i.e., 'PABUDWF'
38 -- p_item_key - Workflow process indentifer
39 -- p_entity_key1 - Primary key of calling entity, i.e., project_id,
40 -- budget_version_id, etc.
41 -- p_entity_key2 - Supplemental primary key for calling entity. Typically,
42 -- used to store baselined budget_version_id.
43 --
44 -- OUT Parameters
45 -- p_err_code - Standard error code: 0, Success; x < 0, Unexpected Error;
46 -- x > 0, Business Rule Violated.
47 -- p_err_stage - Standard error message
48 -- p_err_stack - Not used
49
50 PROCEDURE Insert_WF_Processes
51 (p_wf_type_code IN VARCHAR2
52 , p_item_type IN VARCHAR2
53 , p_item_key IN VARCHAR2
54 , p_entity_key1 IN VARCHAR2
55 , p_entity_key2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
56 , p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
57 , p_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
58 , p_err_stage IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
59 , p_err_stack IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
60 )
61
62 IS
63 --
64
65 l_entity_key2 pa_wf_processes.entity_key2%TYPE;
66 l_description pa_wf_processes.description%TYPE;
67 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
68
69 BEGIN
70
71 IF P_PA_DEBUG_MODE = 'Y' Then
72 PA_DEBUG.g_err_stage := 'Procedure Insert_WF_Processes - Begin';
73 PA_DEBUG.write
74 (x_Module => g_module_name
75 ,x_Msg => pa_debug.g_err_stage
76 ,x_Log_Level => 3);
77 END IF;
78
79 IF P_PA_DEBUG_MODE = 'Y' Then
80 PA_DEBUG.g_err_stage := 'Input Parameters : ' || ' p_wf_type_code ' || p_wf_type_code;
81 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_item_type ' || p_item_type;
82 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_entity_key1 ' || p_entity_key1;
83 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_entity_key2 ' || p_entity_key2;
84 PA_DEBUG.write
85 (x_Module => g_module_name
86 ,x_Msg => pa_debug.g_err_stage
87 ,x_Log_Level => 3);
88 END IF;
89
90 -- Standard Begin of API Savepoint
91
92 SAVEPOINT Insert_WF_Processes_pvt;
93
94 -- Set API Return Status to Success
95
96 p_err_code := 0;
97
98 -- Value-Id Layer --------------------------------------------------------------
99
100 IF (p_entity_key2 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
101 THEN
102 l_entity_key2 := '0'; -- Bug 7170228
103
104 /* Bug fix:5246812: When p_entity_key2 is NULL, throws
105 :ORA-01400: cannot insert NULL into (PA."PA_WF_PROCESSES.ENTITY_KEY2)
106 */
107 -- Bug#7517187
108 --ELSIF (p_entity_key2 is NULL OR p_entity_key2 = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) Then
109 ELSIF (p_entity_key2 is NULL) Then
110 l_entity_key2 := '-99'; -- Bug 7170228
111 /* end of bug fix:5246812 */
112
113 ELSE
114 l_entity_key2 := p_entity_key2;
115 END IF;
116
117 IF P_PA_DEBUG_MODE = 'Y' Then
118 PA_DEBUG.g_err_stage := ' l_entity_key2 ' || l_entity_key2;
119 PA_DEBUG.write
120 (x_Module => g_module_name
121 ,x_Msg => pa_debug.g_err_stage
122 ,x_Log_Level => 3);
123 END IF;
124
125 IF (p_description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
126 THEN
127 l_description := NULL;
128 ELSE
129 l_description := p_description;
130 END IF;
131
132 -- ----------------------------------------------------------------------------------
133 IF P_PA_DEBUG_MODE = 'Y' Then
134 pa_debug.g_err_stage := 'LOG:'||'Inserting into pa_wf_processes: wf_type_code['||p_wf_type_code||']';
135 pa_debug.g_err_stage := pa_debug.g_err_stage||'ItemType['||p_item_type||']ItemKey['||p_item_key||']';
136 pa_debug.g_err_stage := pa_debug.g_err_stage||'Key1['||p_entity_key1||']Key2['||l_entity_key2||']';
137 PA_DEBUG.write
138 (x_Module => 'pa_workflow_utils.Insert_WF_Processes'
139 ,x_Msg => pa_debug.g_err_stage
140 ,x_Log_Level => 3);
141 END IF;
142
143 INSERT INTO pa_wf_processes
144 (wf_type_code
145 , item_type
146 , item_key
147 , entity_key1
148 , entity_key2
149 , description
150 , last_update_date
151 , last_updated_by
152 , creation_date
153 , created_by
154 , last_update_login
155 )
156 VALUES
157 (p_wf_type_code
158 , p_item_type
159 , p_item_key
160 , p_entity_key1
161 , l_entity_key2
162 , l_description
163 , sysdate
164 , fnd_global.user_id
165 , sysdate
166 , fnd_global.user_id
167 , fnd_global.login_id
168 );
169
170 IF P_PA_DEBUG_MODE = 'Y' Then
171 PA_DEBUG.g_err_stage := 'Procedure Insert_WF_Processes - End';
172 PA_DEBUG.write
173 (x_Module => g_module_name
174 ,x_Msg => pa_debug.g_err_stage
175 ,x_Log_Level => 3);
176 END IF;
177
178 EXCEPTION
179
180 WHEN OTHERS
181 THEN
182 p_err_code := SQLCODE;
183 ROLLBACK TO Insert_WF_Processes_pvt;
184 WF_CORE.CONTEXT('PA_WORKFLOW_UTILS','INSERT_WF_PROCESSES', p_item_type, p_item_key );
185 RAISE;
186
187 END Insert_WF_Processes;
188
189 -- ==================================================
190
191 PROCEDURE Set_Global_Attr (p_item_type IN VARCHAR2,
192 p_item_key IN VARCHAR2,
193 p_err_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
194
195 l_resp_id NUMBER := 0;
196 l_workflow_started_by_id NUMBER := 0;
197 l_msg_count NUMBER := 0;
198 l_msg_data VARCHAR2(500) := 0;
199 l_data VARCHAR2(500) := 0;
200 l_return_status VARCHAR2(1) ;
201 l_msg_index_out NUMBER;
202 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
203
204 BEGIN
205 IF P_PA_DEBUG_MODE = 'Y' Then
206 PA_DEBUG.g_err_stage := 'Procedure Set_Global_Attr - Begin';
207 PA_DEBUG.write
208 (x_Module => g_module_name
209 ,x_Msg => pa_debug.g_err_stage
210 ,x_Log_Level => 3);
211 END IF;
212
213 IF P_PA_DEBUG_MODE = 'Y' Then
214 PA_DEBUG.g_err_stage := 'Input Parameters : ' || ' p_item_type ' || p_item_type;
215 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_item_key ' || p_item_key;
216 PA_DEBUG.write
217 (x_Module => g_module_name
218 ,x_Msg => pa_debug.g_err_stage
219 ,x_Log_Level => 3);
220 END IF;
221
222 p_err_code := 0;
223 l_resp_id := wf_engine.GetItemAttrNumber
224 (itemtype => p_item_type,
225 itemkey => p_item_key,
226 aname => 'RESPONSIBILITY_ID' );
227
228 IF P_PA_DEBUG_MODE = 'Y' Then
229 PA_DEBUG.g_err_stage := ' l_resp_id ' || l_resp_id;
230 PA_DEBUG.write
231 (x_Module => g_module_name
232 ,x_Msg => pa_debug.g_err_stage
233 ,x_Log_Level => 3);
234 END IF;
235
236 l_workflow_started_by_id := wf_engine.GetItemAttrNumber
237 (itemtype => p_item_type,
238 itemkey => p_item_key,
239 aname => 'WORKFLOW_STARTED_BY_ID' );
240
241 IF P_PA_DEBUG_MODE = 'Y' Then
242 PA_DEBUG.g_err_stage := ' l_workflow_started_by_id ' || l_workflow_started_by_id;
243 PA_DEBUG.write
244 (x_Module => g_module_name
245 ,x_Msg => pa_debug.g_err_stage
246 ,x_Log_Level => 3);
247 END IF;
248
249 -- Based on the Responsibility, Intialize the Application
250 FND_GLOBAL.Apps_Initialize
251 (user_id => l_workflow_started_by_id
252 , resp_id => l_resp_id
253 , resp_appl_id => pa_workflow_utils.get_application_id(l_resp_id)
254 );
255
256 IF P_PA_DEBUG_MODE = 'Y' Then
257 PA_DEBUG.g_err_stage := 'Procedure Set_Global_Attr - End';
258 PA_DEBUG.write
259 (x_Module => g_module_name
260 ,x_Msg => pa_debug.g_err_stage
261 ,x_Log_Level => 3);
262 END IF;
263
264
265 EXCEPTION
266
267 WHEN OTHERS
268 THEN
269 p_err_code := SQLCODE;
270 WF_CORE.CONTEXT('PA_WORKFLOW_UTILS','SET_GLOBAL_ATTR', p_item_type, p_item_key );
271 RAISE;
272
273 END Set_Global_Attr;
274
275 -- ==================================================
276
277 --Name: Set_Notification_Messages
278 --Type: Procedure
279 --Description: This procedure populates ten error message
280 -- attributes in the calling WF.
281 --
282 --
283 --Called subprograms: none.
284 --
285 --
286 --
287 --History:
288 -- XX-AUT-97 rkrishna - Created
289 -- 24-OCT-97 jwhite - Added intialization code
290 -- for error message attributes.
291 --
292 -- IN Parameters
293 -- p_item_type - WF item type
294 -- p_item_key - WF item key.
295 --
296 --
297
298 PROCEDURE Set_Notification_Messages
299 (p_item_type IN VARCHAR2
300 , p_item_key IN VARCHAR2
301 )
302 --
303 IS
304 --
305 l_attr_name VARCHAR2(30);
306 l_msg_count NUMBER := 0;
307 l_msg_text VARCHAR2(2000) := NULL;
308 l_encoded_mesg VARCHAR2(2000);
309
310 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
311 BEGIN
312 IF P_PA_DEBUG_MODE = 'Y' Then
313 PA_DEBUG.g_err_stage := 'Procedure Set_Notification_Messages - Begin';
314 PA_DEBUG.write
315 (x_Module => g_module_name
316 ,x_Msg => pa_debug.g_err_stage
317 ,x_Log_Level => 3);
318 END IF;
319
320 IF P_PA_DEBUG_MODE = 'Y' Then
321 PA_DEBUG.g_err_stage := 'Input Parameters : ' || ' p_item_type ' || p_item_type;
322 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_item_key ' || p_item_key;
323 PA_DEBUG.write
324 (x_Module => g_module_name
325 ,x_Msg => pa_debug.g_err_stage
326 ,x_Log_Level => 3);
327 END IF;
328
329 -- Get l_msg_count for Subsequent Processing
330 l_msg_count := FND_MSG_PUB.COUNT_MSG;
331
332 -- Intialize First Ten WF Error Message Attributes
333
334 IF P_PA_DEBUG_MODE = 'Y' Then
335 PA_DEBUG.g_err_stage := ' Calling wf_engine.SetItemAttrText in loop - Start ';
336 PA_DEBUG.write
337 (x_Module => g_module_name
338 ,x_Msg => pa_debug.g_err_stage
339 ,x_Log_Level => 3);
340 END IF;
341 FOR i IN 1..10 LOOP
342 l_attr_name := 'RULE_NOTE_'||i;
343 wf_engine.SetItemAttrText
344 (itemtype => p_item_type
345 , itemkey => p_item_key
346 , aname => l_attr_name
347 , avalue => l_msg_text
348 );
349 END LOOP;
350 IF P_PA_DEBUG_MODE = 'Y' Then
351 PA_DEBUG.g_err_stage := ' Calling wf_engine.SetItemAttrText in loop - END ';
352 PA_DEBUG.write
353 (x_Module => g_module_name
354 ,x_Msg => pa_debug.g_err_stage
355 ,x_Log_Level => 3);
356 END IF;
357
358 -- Populate WF Error Message Attributes with Messages, if any.
359
360 IF P_PA_DEBUG_MODE = 'Y' Then
361 PA_DEBUG.g_err_stage := ' Populate WF Error Message Attributes with Messages in loop - Start ';
362 PA_DEBUG.write
363 (x_Module => g_module_name
364 ,x_Msg => pa_debug.g_err_stage
365 ,x_Log_Level => 3);
366 END IF;
367 IF l_msg_count > 0 THEN
368 FOR i IN 1..l_msg_count LOOP
369 IF i > 10 THEN
370 EXIT;
371 END IF;
372 l_encoded_mesg := fnd_msg_pub.get
373 (p_msg_index => i,
374 p_encoded => FND_API.G_TRUE);
375 fnd_message.set_encoded (encoded_message => l_encoded_mesg);
376 l_msg_text := Fnd_Message.Get;
377 l_attr_name := 'RULE_NOTE_'||i;
378 wf_engine.SetItemAttrText (itemtype => p_item_type,
379 itemkey => p_item_key,
380 aname => l_attr_name,
381 avalue => l_msg_text );
382 END LOOP;
383 END IF;
384 IF P_PA_DEBUG_MODE = 'Y' Then
385 PA_DEBUG.g_err_stage := ' Populate WF Error Message Attributes with Messages in loop - End ';
386 PA_DEBUG.write
387 (x_Module => g_module_name
388 ,x_Msg => pa_debug.g_err_stage
389 ,x_Log_Level => 3);
390 END IF;
391
392 IF P_PA_DEBUG_MODE = 'Y' Then
393 PA_DEBUG.g_err_stage := 'Procedure Set_Notification_Messages - End';
394 PA_DEBUG.write
395 (x_Module => g_module_name
396 ,x_Msg => pa_debug.g_err_stage
397 ,x_Log_Level => 3);
398 END IF;
399
400 EXCEPTION
401 WHEN OTHERS
402 THEN
403 WF_CORE.CONTEXT('PA_WORKFLOW_UTILS','SET_NOTIFICATION_MESSAGES', p_item_type, p_item_key );
404 RAISE;
405
406 END Set_Notification_Messages;
407 -- ==================================================
408
409 --
410 -- FUNCTION
411 -- get_application_id
412 -- PURPOSE
413 -- This function retrieves the application id of a responsibility.
414 -- If no application id is found, null is returned.
415 -- If Oracle error occurs, Oracle error number is returned.
416 -- HISTORY
417 -- 02-SEP-99 sbalasub Created
418 --
419 function get_application_id (x_responsibility_id IN number) return number
420 is
421 cursor c1 is
422 select application_id
423 from fnd_responsibility
424 where responsibility_id = x_responsibility_id;
425
426 c1_rec c1%rowtype;
427
428 begin
429 open c1;
430 fetch c1 into c1_rec;
431 if c1%notfound then
432 close c1;
433 return( null);
434 else
435 close c1;
436 return( c1_rec.application_id);
437 end if;
438
439
440 exception
441 when others then
442 return(SQLCODE);
443
444 end get_application_id;
445
446 PROCEDURE get_workflow_info (
447 p_project_status_code IN VARCHAR2
448 ,p_project_status_type IN VARCHAR2
449 ,x_enable_wf_flag out NOCOPY varchar2 --File.Sql.39 bug 4440895
450 ,x_workflow_item_type out NOCOPY varchar2 --File.Sql.39 bug 4440895
451 ,x_workflow_process OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
452 ,x_wf_success_status_code OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
453 ,x_wf_failure_status_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
454 , x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
455 , x_msg_data out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
456 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
457 )
458 IS
459
460
461 CURSOR get_info IS
462 SELECT
463 enable_wf_flag,
464 workflow_item_type,
465 workflow_process,
466 wf_success_status_code,
467 wf_failure_status_code
468 FROM pa_project_statuses
469 WHERE
470 status_type = p_project_status_type
471 AND
472 project_status_code = p_project_status_code;
473 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
474 BEGIN
475 IF P_PA_DEBUG_MODE = 'Y' Then
476 PA_DEBUG.g_err_stage := 'Procedure get_workflow_info - Begin';
477 PA_DEBUG.write
478 (x_Module => g_module_name
479 ,x_Msg => pa_debug.g_err_stage
480 ,x_Log_Level => 3);
481 END IF;
482
483 IF P_PA_DEBUG_MODE = 'Y' Then
484 PA_DEBUG.g_err_stage := 'Input Parameters : ' || ' p_project_status_code ' || p_project_status_code;
485 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_project_status_type ' || p_project_status_type;
486 PA_DEBUG.write
487 (x_Module => g_module_name
488 ,x_Msg => pa_debug.g_err_stage
489 ,x_Log_Level => 3);
490 END IF;
491
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493
494 OPEN get_info;
495 FETCH get_info INTO
496 x_enable_wf_flag,
497 x_workflow_item_type
498 ,x_workflow_process
499 ,x_wf_success_status_code
500 ,x_wf_failure_status_code ;
501 CLOSE get_info;
502
503 IF P_PA_DEBUG_MODE = 'Y' Then
504 PA_DEBUG.g_err_stage := 'Procedure get_workflow_info - End';
505 PA_DEBUG.write
506 (x_Module => g_module_name
507 ,x_Msg => pa_debug.g_err_stage
508 ,x_Log_Level => 3);
509 END IF;
510
511 EXCEPTION
512 WHEN OTHERS
513 THEN
514 x_msg_count := 1;
515 x_msg_data := substr(SQLERRM,1,2000);
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517
518
519 end;
520
521
522 Procedure Cancel_Workflow
523 ( p_Item_type IN VARCHAR2
524 , p_Item_key IN VARCHAR2
525 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
526 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
527 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
528 )
529
530 IS
531
532 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
533
534 BEGIN
535
536 IF P_PA_DEBUG_MODE = 'Y' Then
537 PA_DEBUG.g_err_stage := 'Procedure Cancel_Workflow - Begin';
538 PA_DEBUG.write
539 (x_Module => g_module_name
540 ,x_Msg => pa_debug.g_err_stage
541 ,x_Log_Level => 3);
542 END IF;
543
544 IF P_PA_DEBUG_MODE = 'Y' Then
545 PA_DEBUG.g_err_stage := 'Input Parameters : ' || ' p_Item_type ' || p_Item_type;
546 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_Item_key ' || p_Item_key;
547 PA_DEBUG.write
548 (x_Module => g_module_name
549 ,x_Msg => pa_debug.g_err_stage
550 ,x_Log_Level => 3);
551 END IF;
552
553
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555
556 --debug_msg ( 'after client cancel_workflow call' );
557
558 IF (x_return_status = FND_API.g_ret_sts_success) THEN
559 WF_ENGINE.AbortProcess( p_Item_Type
560 , p_Item_Key
561 );
562
563 --debug_msg ( 'after WF_ENGINE abortProcess' );
564
565 --debug_msg ('before get task_id');
566
567 END IF;
568
569 IF P_PA_DEBUG_MODE = 'Y' Then
570 PA_DEBUG.g_err_stage := 'Procedure Cancel_Workflow - End';
571 PA_DEBUG.write
572 (x_Module => g_module_name
573 ,x_Msg => pa_debug.g_err_stage
574 ,x_Log_Level => 3);
575 END IF;
576
577
578 EXCEPTION
579
580 WHEN OTHERS THEN
581 --debug_msg ( 'Exception in Cancel_Wf ' || substr(SQLERRM,1,2000) );
582
583 x_msg_count := 1;
584 x_msg_data := substr(SQLERRM,1,2000);
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586
587 END Cancel_workflow;
588
589
590 Procedure create_workflow_process (
591 p_item_type IN VARCHAR2
592 , p_process_name IN VARCHAR2
593 , x_item_key out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
594 , x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
595 , x_msg_data out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
596 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
597 )
598 IS
599
600 l_item_key NUMBER;
601 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
602
603 BEGIN
604 IF P_PA_DEBUG_MODE = 'Y' Then
605 PA_DEBUG.g_err_stage := 'Procedure create_workflow_process - Begin';
606 PA_DEBUG.write
607 (x_Module => g_module_name
608 ,x_Msg => pa_debug.g_err_stage
609 ,x_Log_Level => 3);
610 END IF;
611
612 IF P_PA_DEBUG_MODE = 'Y' Then
613 PA_DEBUG.g_err_stage := 'Input Parameters : ' || ' p_Item_type ' || p_Item_type;
614 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_process_name ' || p_process_name;
615 PA_DEBUG.write
616 (x_Module => g_module_name
617 ,x_Msg => pa_debug.g_err_stage
618 ,x_Log_Level => 3);
619 END IF;
620 SELECT pa_workflow_itemkey_s.nextval
621 INTO l_item_key
622 from dual;
623
624 x_item_key := To_char(l_item_key);
625
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627
628 -- create the workflow process
629 IF P_PA_DEBUG_MODE = 'Y' Then
630 PA_DEBUG.g_err_stage := 'Calling WF_ENGINE.CreateProcess - Start';
631 PA_DEBUG.write
632 (x_Module => g_module_name
633 ,x_Msg => pa_debug.g_err_stage
634 ,x_Log_Level => 3);
635 END IF;
636 WF_ENGINE.CreateProcess( p_item_type
637 , x_item_key
638 , p_Process_Name);
639 IF P_PA_DEBUG_MODE = 'Y' Then
640 PA_DEBUG.g_err_stage := 'Calling WF_ENGINE.CreateProcess - End';
641 PA_DEBUG.write
642 (x_Module => g_module_name
643 ,x_Msg => pa_debug.g_err_stage
644 ,x_Log_Level => 3);
645 END IF;
646
647 IF P_PA_DEBUG_MODE = 'Y' Then
648 PA_DEBUG.g_err_stage := 'Procedure create_workflow_process - End';
649 PA_DEBUG.write
650 (x_Module => g_module_name
651 ,x_Msg => pa_debug.g_err_stage
652 ,x_Log_Level => 3);
653 END IF;
654 --debug_msg ( 'after WF_ENGINE createProcess: key = ' || x_item_key)
655
656 EXCEPTION
657
658 WHEN OTHERS THEN
659 --debug_msg ( 'Exception ' || substr(SQLERRM,1,2000) );
660
661
662 x_msg_count := 1;
663 x_msg_data := substr(SQLERRM,1,2000);
664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
665
666
667
668
669 END ;
670
671 procedure start_workflow_process (
672 p_item_type IN VARCHAR2
673 , p_process_name IN VARCHAR2
674 , p_item_key IN number
675 , p_wf_type_code IN VARCHAR2
676 , p_entity_key1 IN VARCHAR2
677 , p_entity_key2 IN VARCHAR2
678 , p_description IN VARCHAR2
679 , x_msg_count out NOCOPY NUMBER --File.Sql.39 bug 4440895
680 , x_msg_data out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
681 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
682 )
683 IS
684 l_err_code NUMBER;
685 l_err_stage VARCHAR2(30);
686 l_err_stack VARCHAR2(240);
687 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
688
689 BEGIN
690 IF P_PA_DEBUG_MODE = 'Y' Then
691 PA_DEBUG.g_err_stage := 'Procedure start_workflow_process - Begin';
692 PA_DEBUG.write
693 (x_Module => g_module_name
694 ,x_Msg => pa_debug.g_err_stage
695 ,x_Log_Level => 3);
696 END IF;
697
698 IF P_PA_DEBUG_MODE = 'Y' Then
699 PA_DEBUG.g_err_stage := 'Input Parameters : ' || ' p_Item_type ' || p_Item_type;
700 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_process_name ' || p_process_name;
701 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_Item_key ' || p_Item_key;
702 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_wf_type_code ' || p_wf_type_code;
703 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_entity_key1 ' || p_entity_key1 ;
704 PA_DEBUG.g_err_stage := PA_DEBUG.g_err_stage || ' p_entity_key2 ' || p_entity_key2 ;
705 PA_DEBUG.write
706 (x_Module => g_module_name
707 ,x_Msg => pa_debug.g_err_stage
708 ,x_Log_Level => 3);
709 END IF;
710
711 IF P_PA_DEBUG_MODE = 'Y' Then
712 PA_DEBUG.g_err_stage := 'WF_ENGINE.StartProcess - Begin';
713 PA_DEBUG.write
714 (x_Module => g_module_name
715 ,x_Msg => pa_debug.g_err_stage
716 ,x_Log_Level => 3);
717 END IF;
718 WF_ENGINE.StartProcess(
719 p_Item_Type
720 , p_Item_Key
721 );
722 IF P_PA_DEBUG_MODE = 'Y' Then
723 PA_DEBUG.g_err_stage := 'WF_ENGINE.StartProcess - End';
724 PA_DEBUG.write
725 (x_Module => g_module_name
726 ,x_Msg => pa_debug.g_err_stage
727 ,x_Log_Level => 3);
728 END IF;
729
730 IF P_PA_DEBUG_MODE = 'Y' Then
731 PA_DEBUG.g_err_stage := 'PA_WORKFLOW_UTILS.Insert_WF_Processes - Begin';
732 PA_DEBUG.write
733 (x_Module => g_module_name
734 ,x_Msg => pa_debug.g_err_stage
735 ,x_Log_Level => 3);
736 END IF;
737
738 PA_WORKFLOW_UTILS.Insert_WF_Processes
739 (p_wf_type_code => p_wf_type_code
740 ,p_item_type => p_item_type
741 ,p_item_key => p_item_key
742 ,p_entity_key1 => p_entity_key1
743 ,p_entity_key2 => p_entity_key2
744 ,p_description => p_description
745 ,p_err_code => l_err_code
746 ,p_err_stage => l_err_stage
747 ,p_err_stack => l_err_stack
748 );
749 IF P_PA_DEBUG_MODE = 'Y' Then
750 PA_DEBUG.g_err_stage := 'PA_WORKFLOW_UTILS.Insert_WF_Processes - End';
751 PA_DEBUG.write
752 (x_Module => g_module_name
753 ,x_Msg => pa_debug.g_err_stage
754 ,x_Log_Level => 3);
755 END IF;
756
757 IF l_err_code <> 0 THEN
758
759 PA_UTILS.Add_Message( p_app_short_name => 'PA'
760 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
761 x_return_status := FND_API.G_RET_STS_ERROR;
762
763
764 IF P_PA_DEBUG_MODE = 'Y' Then
765 PA_DEBUG.g_err_stage := ' WF_ENGINE.AbortProcess - Begin';
766 PA_DEBUG.write
767 (x_Module => g_module_name
768 ,x_Msg => pa_debug.g_err_stage
769 ,x_Log_Level => 3);
770 END IF;
771 -- abort the workflow process just launched, there is a problem
772 WF_ENGINE.AbortProcess( p_Item_Type
773 , p_Item_Key
774 );
775
776 IF P_PA_DEBUG_MODE = 'Y' Then
777 PA_DEBUG.g_err_stage := ' WF_ENGINE.AbortProcess - End';
778 PA_DEBUG.write
779 (x_Module => g_module_name
780 ,x_Msg => pa_debug.g_err_stage
781 ,x_Log_Level => 3);
782 END IF;
783
784 END IF;
785
786 IF P_PA_DEBUG_MODE = 'Y' Then
787 PA_DEBUG.g_err_stage := 'Procedure start_workflow_process - End';
788 PA_DEBUG.write
789 (x_Module => g_module_name
790 ,x_Msg => pa_debug.g_err_stage
791 ,x_Log_Level => 3);
792 END IF;
793
794 EXCEPTION
795
796 WHEN OTHERS THEN
797 --debug_msg ( 'Exception ' || substr(SQLERRM,1,2000) );
798
799
800 x_msg_count := 1;
801 x_msg_data := substr(SQLERRM,1,2000);
802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
803
804 end;
805
806
807 /* Bug 3787169. This API takes of removing class attributes from the html
808 before using the same in workflow. Further this api removes the
809 base and the style tags from html.
810 */
811 PROCEDURE modify_wf_clob_content
812 ( p_document IN OUT NOCOPY pa_page_contents.page_content%TYPE
813 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
814 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
815 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
816 AS
817
818 l_msg_count NUMBER := 0;
819 l_data VARCHAR2(2000);
820 l_msg_data VARCHAR2(2000);
821 l_error_msg_code VARCHAR2(30);
822 l_msg_index_out NUMBER;
823 l_return_status VARCHAR2(2000);
824 l_debug_mode VARCHAR2(30);
825 l_module_name VARCHAR2(100) := 'pa.plsql.PA_WORKFLOW_UTILS';
826
827 l_class_attr constant varchar2(7) := 'class="';
828 l_end_quote constant varchar2(1) := '"';
829 l_start_index number;
830 l_end_index number;
831 l_amount number;
832
833 BASE_TAG varchar2(5) :='<base';
834 END_TAG varchar2(1) := '>';
835
836 STYLE_TAG varchar2(22) := '<link rel="stylesheet"';
837
838 INPUT_TAG constant varchar2(6) := '<input';
839 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
840
841 BEGIN
842 IF P_PA_DEBUG_MODE = 'Y' Then
843 PA_DEBUG.g_err_stage := 'Procedure modify_wf_clob_content - Begin';
844 PA_DEBUG.write
845 (x_Module => g_module_name
846 ,x_Msg => pa_debug.g_err_stage
847 ,x_Log_Level => 3);
848 END IF;
849 /* IF P_PA_DEBUG_MODE = 'Y' Then
850 PA_DEBUG.g_err_stage := 'Input Parameters : ' || ' p_document ' || p_document;
851 PA_DEBUG.write
852 (x_Module => g_module_name
853 ,x_Msg => pa_debug.g_err_stage
854 ,x_Log_Level => 3);
855 END IF;*/ /*commented for bug 8915991 */
856 x_msg_count := 0;
857 x_return_status := FND_API.G_RET_STS_SUCCESS;
858 pa_debug.set_err_stack('PA_WORKFLOW_UTILS.modify_wf_clob_content');
859 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
860 l_debug_mode := NVL(l_debug_mode, 'Y');
861 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
862 l_start_index := dbms_lob.instr(p_document,l_class_attr,1,1);
863 while l_start_index <> 0 loop
864 l_end_index := dbms_lob.instr(p_document,l_end_quote,l_start_index+length(l_class_attr),1);
865 l_amount := l_end_index-l_start_index+1;
866 dbms_lob.erase(p_document,l_amount,l_start_index);
867 l_start_index := dbms_lob.instr(p_document,l_class_attr,l_end_index,1);
868 end loop;
869
870 --Identify the start and the end indices of the base tag and erase it from
871 --the clob contents.
872 l_start_index := dbms_lob.instr(p_document,BASE_TAG,1,1);
873
874 -- dbms_lob will throw error if l_start_index <> 0 is not present -- changes commented for bug 4350867
875 -- if(l_start_index <> 0) then -- Added If condition for 4289078
876 -- l_end_index := dbms_lob.instr(p_document,END_TAG,l_start_index,1);
877 -- l_amount := l_end_index-l_start_index+1;
878 -- dbms_lob.erase(p_document,l_amount,l_start_index);
879 -- end if;
880
881 --Identify the start and the end indices of the style sheet tag and erase it from
882 --the clob contents.
883 l_start_index := dbms_lob.instr(p_document,STYLE_TAG,1,1);
884 if(l_start_index <> 0) then -- Added If condition for 4289078
885 l_end_index := dbms_lob.instr(p_document,END_TAG,l_start_index,1);
886 l_amount := l_end_index-l_start_index+1;
887 dbms_lob.erase(p_document,l_amount,l_start_index);
888 end if;
889
890 --Identify the start and the end indices of the input tag and erase it from
891 --the clob contents.
892 l_start_index := dbms_lob.instr(p_document,INPUT_TAG,1,1);
893 while l_start_index <> 0 loop
894 l_end_index := dbms_lob.instr(p_document,END_TAG,l_start_index+length(INPUT_TAG),1);
895 l_amount := l_end_index-l_start_index+1;
896 dbms_lob.erase(p_document,l_amount,l_start_index);
897 l_start_index := dbms_lob.instr(p_document,INPUT_TAG,l_end_index,1);
898 end loop;
899 pa_debug.reset_err_stack;
900
901 IF P_PA_DEBUG_MODE = 'Y' Then
902 PA_DEBUG.g_err_stage := 'Procedure modify_wf_clob_content - End';
903 PA_DEBUG.write
904 (x_Module => g_module_name
905 ,x_Msg => pa_debug.g_err_stage
906 ,x_Log_Level => 3);
907 END IF;
908
909 EXCEPTION
910 WHEN others THEN
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 x_msg_count := 1;
913 x_msg_data := SQLERRM;
914 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_workflow_utils'
915 ,p_procedure_name => 'modify_wf_clob_content');
916
917
918 pa_debug.reset_err_stack;
919 RAISE;
920
921 END modify_wf_clob_content;
922
923 -- ==================================================
924
925 END pa_workflow_utils;