[Home] [Help]
PACKAGE BODY: APPS.ENG_DOCUMENT_UTIL
Source
1 PACKAGE BODY ENG_DOCUMENT_UTIL AS
2 /* $Header: ENGUDOCB.pls 120.14 2006/11/14 08:47:20 asjohal noship $ */
3
4
5 PLSQL_COMPILE_ERROR EXCEPTION;
6 PRAGMA EXCEPTION_INIT(PLSQL_COMPILE_ERROR, -6550);
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ENG_DOCUMENT_UTIL' ;
9
10 -- For Debug
11 g_debug_flag BOOLEAN := FALSE ;
12 g_output_dir VARCHAR2(240) := NULL ;
13 g_debug_filename VARCHAR2(200) := NULL ;
14 g_debug_errmesg VARCHAR2(240);
15 G_BO_IDENTIFIER VARCHAR2(30) := 'ENG_DOCUMENT_UTIL';
16 G_ERRFILE_PATH_AND_NAME VARCHAR2(10000);
17 g_profile_debug_option VARCHAR2(10) ;
18 g_profile_debug_level VARCHAR2(10) ;
19
20
21 /********************************************************************
22 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
23 * Write_Debug
24 * Parameters IN :
25 * Parameters OUT:
26 * Purpose : These procedures are for test and debug
27 *********************************************************************/
28 ----------------------------------------------------------
29 -- Internal procedure to open Debug Session. --
30 ----------------------------------------------------------
31 -- Open_Debug_Session
32 PROCEDURE Open_Debug_Session
33 ( p_output_dir IN VARCHAR2 := NULL
34 , p_file_name IN VARCHAR2 := NULL
35 )
36 IS
37
38 CURSOR c_get_utl_file_dir IS
39 SELECT VALUE
40 FROM V$PARAMETER
41 WHERE NAME = 'utl_file_dir';
42
43 --local variables
44 l_found NUMBER;
45
46 l_log_output_dir VARCHAR2(512);
47 l_log_return_status VARCHAR2(99);
48 l_errbuff VARCHAR2(2000);
49
50 BEGIN
51
52
53 -- Ignore open_debug_session call if package debugging mode is already ON
54 IF ( g_debug_flag AND Error_Handler.Get_Debug = 'Y' ) THEN
55 RETURN ;
56 END IF ;
57
58 l_found := 0 ;
59 Error_Handler.initialize();
60 Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
61
62 ---------------------------------------------------------------------------------
63 -- Open_Debug_Session should set the value
64 -- appropriately, so that when the Debug Session is successfully opened :
65 -- will return Error_Handler.Get_Debug = 'Y', else Error_Handler.Get_Debug = 'N'
66 ---------------------------------------------------------------------------------
67
68 IF p_output_dir IS NOT NULL THEN
69 g_output_dir := p_output_dir ;
70 END IF;
71
72 IF p_file_name IS NOT NULL THEN
73 g_debug_filename := p_file_name ;
74 END IF;
75
76 OPEN c_get_utl_file_dir;
77 FETCH c_get_utl_file_dir INTO l_log_output_dir;
78
79 IF c_get_utl_file_dir%FOUND THEN
80
81 IF g_output_dir IS NOT NULL
82 THEN
83 l_found := INSTR(l_log_output_dir, g_output_dir);
84 IF l_found = 0
85 THEN
86 g_output_dir := NULL ;
87 END IF;
88 END IF;
89
90 ------------------------------------------------------
91 -- Trim to get only the first directory in the list --
92 ------------------------------------------------------
93 IF INSTR(l_log_output_dir,',') <> 0 THEN
94 l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
95 END IF;
96
97
98 IF g_output_dir IS NULL
99 THEN
100 g_output_dir := l_log_output_dir ;
101 END IF ;
102
103
104 IF g_debug_filename IS NULL
105 THEN
106 g_debug_filename := G_BO_IDENTIFIER ||'_' || to_char(sysdate, 'DDMONYYYY_HH24MISS')||'.log';
107 END IF ;
108
109 -----------------------------------------------------------------------
110 -- To open the Debug Session to write the Debug Log. --
111 -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --
112 -----------------------------------------------------------------------
113 Error_Handler.Open_Debug_Session(
114 p_debug_filename => g_debug_filename
115 ,p_output_dir => g_output_dir
116 ,x_return_status => l_log_return_status
117 ,x_error_mesg => l_errbuff
118 );
119
120 IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS)
121 THEN
122 FND_FILE.put_line(FND_FILE.LOG, 'Unable to open error log file. Error => '||l_errbuff) ;
123 END IF;
124
125 END IF; --IF c_get_utl_file_dir%FOUND THEN
126 -- Bug : 4099546
127 CLOSE c_get_utl_file_dir;
128
129 -- Set Global Debug Flag
130
131 g_debug_flag := TRUE ;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135
136 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
137
138 FND_FILE.put_line(FND_FILE.LOG, 'Log file location --> '||l_log_output_dir||'/'||g_debug_filename ||' created with status '|| l_log_return_status);
139 FND_FILE.put_line(FND_FILE.LOG, G_PKG_NAME || ' Open_Debug_Session LOGGING SQL ERROR => '||g_debug_errmesg);
140 g_debug_flag := FALSE;
141 END Open_Debug_Session ;
142
143
144 -- Close Debug_Session
145 PROCEDURE Close_Debug_Session
146 IS
147 BEGIN
148
149 -----------------------------------------------------------------------------
150 -- Close Error_Handler debug session, only if Debug session is already open.
151 -----------------------------------------------------------------------------
152 IF (Error_Handler.Get_Debug = 'Y') THEN
153 Error_Handler.Close_Debug_Session;
154 END IF;
155
156 g_debug_flag := FALSE;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
161 FND_FILE.put_line(FND_FILE.LOG, G_PKG_NAME || ' Close_Debug_Session LOGGING SQL ERROR => '||g_debug_errmesg);
162 g_debug_flag := FALSE;
163
164 END Close_Debug_Session;
165
166 -- Test Debug
167 PROCEDURE Write_Debug
168 ( p_debug_message IN VARCHAR2 )
169 IS
170 BEGIN
171 -- Sometimes Error_Handler.Write_Debug would not write
172 -- the debug message properly
173 -- So as workaround, I added special developer debug mode here
174 -- to write debug message forcedly
175 IF (TO_NUMBER(g_profile_debug_level) = 999)
176 THEN
177 FND_FILE.put_line(FND_FILE.LOG
178 , G_PKG_NAME
179 || '['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '
180 || p_debug_message
181 );
182
183 END IF ;
184
185 Error_Handler.Write_Debug('['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '|| p_debug_message);
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
190 FND_FILE.put_line(FND_FILE.LOG, G_PKG_NAME || ' Write_Debug LOGGING SQL ERROR => '||g_debug_errmesg);
191 g_debug_flag := FALSE;
192 END Write_Debug;
193
194
195
196 -----------------------------------------------------------
197 -- Open the Debug Session, conditionally if the profile: --
198 -- INV Debug Trace is set to TRUE --
199 -----------------------------------------------------------
200 PROCEDURE Check_And_Open_Debug_Session
201 ( p_debug_flag IN VARCHAR2
202 , p_output_dir IN VARCHAR2 := NULL
203 , p_file_name IN VARCHAR2 := NULL
204 )
205 IS
206
207
208 BEGIN
209 ----------------------------------------------------------------
210 -- Open the Debug Log Session, p_debug_flag is TRUE or
211 -- if Profile is set to TRUE: INV_DEBUG_TRACE Yes, INV_DEBUG_LEVEL 20
212 ----------------------------------------------------------------
213 g_profile_debug_option := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), TO_CHAR(0));
214 g_profile_debug_level := NVL(FND_PROFILE.VALUE('INV_DEBUG_LEVEL'), TO_CHAR(0));
215
216 IF (g_profile_debug_option = '1' AND TO_NUMBER(g_profile_debug_level) >= 20)
217 OR FND_API.to_Boolean(p_debug_flag)
218 THEN
219
220 ----------------------------------------------------------------------------------
221 -- Opens Error_Handler debug session, only if Debug session is not already open.
222 -- Suggested by RFAROOK, so that multiple debug sessions are not open PER
223 -- Concurrent Request.
224 ----------------------------------------------------------------------------------
225 IF (Error_Handler.Get_Debug <> 'Y') THEN
226 Open_Debug_Session(p_output_dir => p_output_dir, p_file_name => p_file_name) ;
227 END IF;
228
229 END IF;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
234 FND_FILE.put_line(FND_FILE.LOG, G_PKG_NAME || ' Check_And_Open_Debug_Session LOGGING SQL ERROR => '||g_debug_errmesg);
235 g_debug_flag := FALSE;
236 END Check_And_Open_Debug_Session;
237
238
239
240 /********************************************************************
241 * API Type : Private APIs
242 * Purpose : Those APIs are private
243 *********************************************************************/
244 FUNCTION Is_Dom_Document_Lifecycle( p_change_id IN NUMBER
245 , p_base_change_mgmt_type_code IN VARCHAR2 := NULL
246 )
247 RETURN BOOLEAN
248 IS
249 l_base_change_mgmt_type_code VARCHAR2(30) ;
250
251 CURSOR c_change_mgmt_cur (c_change_id NUMBER)
252 IS
253 SELECT eec.change_mgmt_type_code
254 , ecot.base_change_mgmt_type_code
255 FROM eng_engineering_changes eec,
256 eng_change_order_types ecot
257 WHERE ecot.change_order_type_id = eec.change_order_type_id
258 AND eec.change_id = c_change_id ;
259
260
261 BEGIN
262
263
264 l_base_change_mgmt_type_code := p_base_change_mgmt_type_code ;
265
266 IF l_base_change_mgmt_type_code IS NULL
267 THEN
268
269 FOR l_rec IN c_change_mgmt_cur (c_change_id => p_change_id)
270 LOOP
271 l_base_change_mgmt_type_code := l_rec.base_change_mgmt_type_code ;
272 END LOOP ;
273
274 END IF ;
275
276
277 IF l_base_change_mgmt_type_code = G_DOM_DOCUMENT_LIFECYCLE
278 THEN
279
280 RETURN TRUE ;
281
282 ELSE
283
284 RETURN FALSE ;
285
286 END IF ;
287
288
289 END Is_Dom_Document_Lifecycle ;
290
291
292 PROCEDURE Get_Document_Revision_Id( p_change_id IN NUMBER
293 , x_document_id OUT NOCOPY NUMBER
294 , x_document_revision_id OUT NOCOPY NUMBER
295 )
296 IS
297
298 BEGIN
299
300
301 SELECT TO_NUMBER(pk2_value) document_revision_id
302 , TO_NUMBER(pk1_value) document_id
303 INTO x_document_revision_id
304 , x_document_id
305 FROM ENG_CHANGE_SUBJECTS subj
306 WHERE subj.entity_name = 'DOM_DOCUMENT_REVISION'
307 AND subj.change_id = p_change_id
308 AND ROWNUM = 1 ;
309
310 END Get_Document_Revision_Id ;
311
312
313 -- Get Document Revision Info
314 PROCEDURE Get_Document_Rev_Info
315 ( p_document_revision_id IN NUMBER
316 , x_document_id OUT NOCOPY NUMBER
317 , x_document_number OUT NOCOPY VARCHAR2
318 , x_document_revision OUT NOCOPY VARCHAR2
319 , x_documnet_name OUT NOCOPY VARCHAR2
320 , x_document_detail_page_url OUT NOCOPY VARCHAR2
321 )
322 IS
323
324
325 BEGIN
326
327 SELECT dom_doc.document_id
328 , dom_doc.doc_number
329 , dom_doc_rev.revision
330 , dom_doc.name
331 INTO x_document_id
332 , x_document_number
333 , x_document_revision
334 , x_documnet_name
335 FROM dom_documents_vl dom_doc
336 , dom_document_revisions dom_doc_rev
337 WHERE dom_doc.document_id = dom_doc_rev.document_id
338 AND dom_doc_rev.revision_id = p_document_revision_id ;
339
340 -- Get Document Revision Overview Page URL using RF.jsp version
341 -- e.g. OA.jsp?OAFunc=DOM_DOC_OVERVIEW&documentId=999&revisionId=7777
342 x_document_detail_page_url := Eng_Workflow_Ntf_Util.GetRunFuncURL
343 ( p_function_name => 'DOM_DOC_OVERVIEW'
344 , p_parameters => '&documentId=' || TO_CHAR(x_document_id) || '&revisionId=' || TO_CHAR(p_document_revision_id) ) ;
345
346 END Get_Document_Rev_Info ;
347
348
349
350 --
351 -- Wrapper API to integrate DOM Document API when Updating Approval Status
352 -- of Document LC Phase Change Object
353 --
354 PROCEDURE Update_Approval_Status
355 (
356 p_api_version IN NUMBER --
357 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
358 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
359 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
360 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
361 ,p_output_dir IN VARCHAR2 := NULL
362 ,p_debug_filename IN VARCHAR2 := NULL
363 ,x_return_status OUT NOCOPY VARCHAR2 --
364 ,x_msg_count OUT NOCOPY NUMBER --
365 ,x_msg_data OUT NOCOPY VARCHAR2 --
366 ,p_change_id IN NUMBER -- header's change_id
367 ,p_approval_status IN NUMBER -- header new approval status
368 ,p_wf_route_status IN VARCHAR2 -- workflow routing status (for document types)
369 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
370 )
371 IS
372
373 l_return_status VARCHAR2(1);
374 l_msg_count NUMBER;
375 l_msg_data VARCHAR2(2000);
376 l_pls_block VARCHAR2(4000);
377
378 BEGIN
379
380 -- For Test/Debug
381 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
382
383 IF g_debug_flag THEN
384 Write_Debug('ENG_DOCUMENT_UTIL.Update_Approval_Status Log');
385 Write_Debug('-----------------------------------------------------');
386 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
387 Write_Debug('Approval Status : ' || TO_CHAR(p_approval_status) );
388 Write_Debug('Workflow Status : ' || p_wf_route_status);
389 Write_Debug('API Caller : ' || p_api_caller);
390 Write_Debug('-----------------------------------------------------');
391 Write_Debug('Calling DOM_LIFECYCLE_UTIL.UPDATE_APPROVAL_STATUS');
392 END IF ;
393
394
395 l_return_status := FND_API.G_RET_STS_SUCCESS ;
396
397 -- We always pass p_commit FND_API.G_FALSE
398 -- DOM API should NOT commit or rollback
399 l_pls_block := ' BEGIN '
400 || ' DOM_DOCUMENT_UTIL.UPDATE_APPROVAL_STATUS'
401 || ' ( p_api_version => :1 '
402 || ' ,p_init_msg_list => :2 '
403 || ' ,p_commit => :3 '
404 || ' ,p_validation_level => :4 '
405 || ' ,x_return_status => :5 '
406 || ' ,x_msg_count => :6 '
407 || ' ,x_msg_data => :7 '
408 || ' ,p_change_id => :8 '
409 || ' ,p_approval_status => :9 '
410 || ' ,p_wf_route_status => :10 '
411 || ' ,p_api_caller => :11 '
412 || ' ); '
413 || ' END; ';
414
415 EXECUTE IMMEDIATE l_pls_block USING
416 p_api_version
417 ,p_init_msg_list
418 ,p_commit
419 ,p_validation_level
420 ,OUT l_return_status
421 ,OUT l_msg_count
422 ,OUT l_msg_data
423 ,p_change_id
424 ,p_approval_status
425 ,p_wf_route_status
426 ,p_api_caller ;
427
428 -- Set Return vars
429 x_return_status := l_return_status;
430 x_msg_count := l_msg_count;
431 x_msg_data := l_msg_data;
432
433 -- Until DOM team implemented the logic indeed
434 IF x_return_status IS NULL
435 THEN
436 x_return_status := FND_API.G_RET_STS_SUCCESS ;
437 END IF ;
438
439 IF g_debug_flag THEN
440 Write_Debug('After Calling DOM_DOCUMENT_UTIL.UPDATE_APPROVAL_STATUS: ' || l_return_status);
441 END IF ;
442
443
444 EXCEPTION
445 WHEN PLSQL_COMPILE_ERROR THEN
446 -- Assuming DOM is not installed
447 x_return_status := FND_API.G_RET_STS_SUCCESS ;
448 x_msg_count := 0;
449 x_msg_data := NULL;
450
451
452 IF g_debug_flag THEN
453 Write_Debug('Exception Update_Approval_Status: PLSQL_COMPILE_ERROR ');
454 END IF ;
455
456
457 WHEN OTHERS THEN
458
459 IF g_debug_flag THEN
460 Write_Debug('Unexpected Exception Update_Approval_Status: ' || SQLERRM);
461 END IF ;
462
463
464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
465
466 FND_MSG_PUB.Add_Exc_Msg
467 ( p_pkg_name => 'DOM_DOCUMENT_UTIL' ,
468 p_procedure_name => 'UPDATE_APPROVAL_STATUS',
469 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
470 );
471
472 FND_MSG_PUB.Count_And_Get
473 ( p_count => x_msg_count ,
474 p_data => x_msg_data
475 );
476
477
478 END Update_Approval_Status ;
479
480
481 --
482 -- Wrapper API to integrate DOM Document API when Promoting/Demoting
483 -- Document LC Phase
484 --
485 PROCEDURE Change_Doc_LC_Phase
486 (
487 p_api_version IN NUMBER --
488 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
489 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
490 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
491 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
492 ,p_output_dir IN VARCHAR2 := NULL
493 ,p_debug_filename IN VARCHAR2 := NULL
494 ,x_return_status OUT NOCOPY VARCHAR2 --
495 ,x_msg_count OUT NOCOPY NUMBER --
496 ,x_msg_data OUT NOCOPY VARCHAR2 --
497 ,p_change_id IN NUMBER -- header's change_id
498 ,p_lc_phase_code IN NUMBER -- new phase
499 ,p_action_type IN VARCHAR2 := NULL -- promote/demote action type 'PROMOTE' or 'DEMOTE'
500 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
501 )
502 IS
503
504
505 l_return_status VARCHAR2(1);
506 l_msg_count NUMBER;
507 l_msg_data VARCHAR2(2000);
508 l_pls_block VARCHAR2(4000);
509
510 BEGIN
511
512
513 -- For Test/Debug
514 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
515
516 IF g_debug_flag THEN
517 Write_Debug('ENG_DOCUMENT_UTIL.Change_Doc_LC_Phase Log');
518 Write_Debug('-----------------------------------------------------');
519 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
520 Write_Debug('LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
521 Write_Debug('Action Type : ' || p_action_type);
522 Write_Debug('API Caller : ' || p_api_caller);
523 Write_Debug('-----------------------------------------------------');
524 Write_Debug('Calling DOM_DOCUMENT_UTIL.CHANGE_DOC_LC_PHASE');
525 END IF ;
526
527 l_return_status := FND_API.G_RET_STS_SUCCESS ;
528
529
530 -- We always pass p_commit FND_API.G_FALSE
531 -- DOM API should NOT commit or rollback
532 l_pls_block := ' BEGIN '
533 || ' DOM_DOCUMENT_UTIL.CHANGE_DOC_LC_PHASE'
534 || ' ( p_api_version => :1 '
535 || ' ,p_init_msg_list => :2 '
536 || ' ,p_commit => :3 '
537 || ' ,p_validation_level => :4 '
538 || ' ,x_return_status => :5 '
539 || ' ,x_msg_count => :6 '
540 || ' ,x_msg_data => :7 '
541 || ' ,p_change_id => :8 '
542 || ' ,p_lc_phase_code => :9 '
543 || ' ,p_action_type => :10 ' -- 'PROMOTE' or 'DEMOTE'
544 || ' ,p_api_caller => :11 '
545 || ' ); '
546 || ' END; ';
547
548 EXECUTE IMMEDIATE l_pls_block USING
549 p_api_version
550 ,p_init_msg_list
551 ,p_commit
552 ,p_validation_level
553 ,OUT l_return_status
554 ,OUT l_msg_count
555 ,OUT l_msg_data
556 ,p_change_id
557 ,p_lc_phase_code
558 ,p_action_type
559 ,p_api_caller ;
560
561
562 -- set Return vars
563 x_return_status := l_return_status;
564 x_msg_count := l_msg_count;
565 x_msg_data := l_msg_data;
566
567 -- Until DOM team implemented the logic indeed
568 IF x_return_status IS NULL
569 THEN
570 x_return_status := FND_API.G_RET_STS_SUCCESS ;
571 END IF ;
572
573
574 IF g_debug_flag THEN
575 Write_Debug('After Calling DOM_DOCUMENT_UTIL.CHANGE_DOC_LC_PHASE: ' || l_return_status);
576 END IF ;
577
578
579 EXCEPTION
580 WHEN PLSQL_COMPILE_ERROR THEN
581 -- Assuming DOM is not installed
582 x_return_status := FND_API.G_RET_STS_SUCCESS ;
583 x_msg_count := 0;
584 x_msg_data := NULL;
585
586 IF g_debug_flag THEN
587 Write_Debug('Exception Change_Doc_LC_Phase: PLSQL_COMPILE_ERROR ');
588 END IF ;
589
590 WHEN OTHERS THEN
591
592
593
594 IF g_debug_flag THEN
595 Write_Debug('Unexpected Exception Change_Doc_LC_Phase: ' || SQLERRM);
596 END IF ;
597
598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
599
600 FND_MSG_PUB.Add_Exc_Msg
601 ( p_pkg_name => 'DOM_DOCUMENT_UTIL' ,
602 p_procedure_name => 'CHANGE_DOC_LC_PHASE',
603 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
604 );
605
606 FND_MSG_PUB.Count_And_Get
607 ( p_count => x_msg_count ,
608 p_data => x_msg_data
609 );
610
611
612 END Change_Doc_LC_Phase ;
613
614
615
616 --
617 -- Wrapper API to integrate DOM Document API when starting
618 -- Document LC Phase Workflow
619 --
620 PROCEDURE Start_Doc_LC_Phase_WF
621 (
622 p_api_version IN NUMBER --
623 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
624 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
625 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
626 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
627 ,p_output_dir IN VARCHAR2 := NULL
628 ,p_debug_filename IN VARCHAR2 := NULL
629 ,x_return_status OUT NOCOPY VARCHAR2 --
630 ,x_msg_count OUT NOCOPY NUMBER --
631 ,x_msg_data OUT NOCOPY VARCHAR2 --
632 ,p_change_id IN NUMBER -- DOC LC Object Change Id
633 ,p_route_id IN NUMBER -- DOC LC Phase WF Route ID
634 ,p_lc_phase_code IN NUMBER := NULL -- Doc LC Phase
635 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
636 )
637 IS
638
639
640 l_return_status VARCHAR2(1);
641 l_msg_count NUMBER;
642 l_msg_data VARCHAR2(2000);
643 l_pls_block VARCHAR2(4000);
644
645 BEGIN
646
647
648 -- For Test/Debug
649 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
650
651 IF g_debug_flag THEN
652 Write_Debug('ENG_DOCUMENT_UTIL.Start_Doc_LC_Phase_WF Log');
653 Write_Debug('-----------------------------------------------------');
654 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
655 Write_Debug('Route Id : ' || TO_CHAR(p_route_id) );
656 Write_Debug('LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
657 Write_Debug('API Caller : ' || p_api_caller);
658 Write_Debug('-----------------------------------------------------');
659 Write_Debug('Calling DOM_DOCUMENT_UTIL.Start_Doc_LC_Phase_WF');
660 END IF ;
661
662 l_return_status := FND_API.G_RET_STS_SUCCESS ;
663
664
665 -- We always pass p_commit FND_API.G_FALSE
666 -- DOM API should NOT commit or rollback
667 l_pls_block := ' BEGIN '
668 || ' DOM_DOCUMENT_UTIL.Start_Doc_LC_Phase_WF'
669 || ' ( p_api_version => :1 '
670 || ' ,p_init_msg_list => :2 '
671 || ' ,p_commit => :3 '
672 || ' ,p_validation_level => :4 '
673 || ' ,x_return_status => :5 '
674 || ' ,x_msg_count => :6 '
675 || ' ,x_msg_data => :7 '
676 || ' ,p_change_id => :8 '
677 || ' ,p_route_id => :9 '
678 || ' ,p_lc_phase_code => :10 '
679 || ' ,p_api_caller => :11 '
680 || ' ); '
681 || ' END; ';
682
683 EXECUTE IMMEDIATE l_pls_block USING
684 p_api_version
685 ,p_init_msg_list
686 ,p_commit
687 ,p_validation_level
688 ,OUT l_return_status
689 ,OUT l_msg_count
690 ,OUT l_msg_data
691 ,p_change_id
692 ,p_route_id
693 ,p_lc_phase_code
694 ,p_api_caller ;
695
696
697 -- set Return vars
698 x_return_status := l_return_status;
699 x_msg_count := l_msg_count;
700 x_msg_data := l_msg_data;
701
702 -- Until DOM team implemented the logic indeed
703 IF x_return_status IS NULL
704 THEN
705 x_return_status := FND_API.G_RET_STS_SUCCESS ;
706 END IF ;
707
708
709 IF g_debug_flag THEN
710 Write_Debug('After Calling DOM_DOCUMENT_UTIL.Start_Doc_LC_Phase_WF: ' || l_return_status);
711 END IF ;
712
713
714 EXCEPTION
715 WHEN PLSQL_COMPILE_ERROR THEN
716 -- Assuming DOM is not installed
717 x_return_status := FND_API.G_RET_STS_SUCCESS ;
718 x_msg_count := 0;
719 x_msg_data := NULL;
720
721 IF g_debug_flag THEN
722 Write_Debug('Exception Start_Doc_LC_Phase_WF: PLSQL_COMPILE_ERROR ');
723 END IF ;
724
725 WHEN OTHERS THEN
726
727
728
729 IF g_debug_flag THEN
730 Write_Debug('Unexpected Exception Start_Doc_LC_Phase_WF: ' || SQLERRM);
731 END IF ;
732
733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
734
735 FND_MSG_PUB.Add_Exc_Msg
736 ( p_pkg_name => 'DOM_DOCUMENT_UTIL' ,
737 p_procedure_name => 'Start_Doc_LC_Phase_WF',
738 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
739 );
740
741 FND_MSG_PUB.Count_And_Get
742 ( p_count => x_msg_count ,
743 p_data => x_msg_data
744 );
745
746
747 END Start_Doc_LC_Phase_WF ;
748
749
750 --
751 -- Wrapper API to integrate DOM Document API when aborting
752 -- Document LC Phase Workflow
753 --
754 PROCEDURE Abort_Doc_LC_Phase_WF
755 (
756 p_api_version IN NUMBER --
757 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
758 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
759 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
760 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
761 ,p_output_dir IN VARCHAR2 := NULL
762 ,p_debug_filename IN VARCHAR2 := NULL
763 ,x_return_status OUT NOCOPY VARCHAR2 --
764 ,x_msg_count OUT NOCOPY NUMBER --
765 ,x_msg_data OUT NOCOPY VARCHAR2 --
766 ,p_change_id IN NUMBER -- DOC LC Object Change Id
767 ,p_route_id IN NUMBER -- DOC LC Phase WF Route ID
768 ,p_lc_phase_code IN NUMBER := NULL -- Doc LC Phase
769 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
770 )
771 IS
772
773
774 l_return_status VARCHAR2(1);
775 l_msg_count NUMBER;
776 l_msg_data VARCHAR2(2000);
777 l_pls_block VARCHAR2(4000);
778
779 BEGIN
780
781
782 -- For Test/Debug
783 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
784
785 IF g_debug_flag THEN
786 Write_Debug('ENG_DOCUMENT_UTIL.Abort_Doc_LC_Phase_WF Log');
787 Write_Debug('-----------------------------------------------------');
788 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
789 Write_Debug('Route Id : ' || TO_CHAR(p_route_id) );
790 Write_Debug('LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
791 Write_Debug('API Caller : ' || p_api_caller);
792 Write_Debug('-----------------------------------------------------');
793 Write_Debug('Calling DOM_DOCUMENT_UTIL.Abort_Doc_LC_Phase_WF');
794 END IF ;
795
796 l_return_status := FND_API.G_RET_STS_SUCCESS ;
797
798
799 -- We always pass p_commit FND_API.G_FALSE
800 -- DOM API should NOT commit or rollback
801 l_pls_block := ' BEGIN '
802 || ' DOM_DOCUMENT_UTIL.Abort_Doc_LC_Phase_WF'
803 || ' ( p_api_version => :1 '
804 || ' ,p_init_msg_list => :2 '
805 || ' ,p_commit => :3 '
806 || ' ,p_validation_level => :4 '
807 || ' ,x_return_status => :5 '
808 || ' ,x_msg_count => :6 '
809 || ' ,x_msg_data => :7 '
810 || ' ,p_change_id => :8 '
811 || ' ,p_route_id => :9 '
812 || ' ,p_lc_phase_code => :10 '
813 || ' ,p_api_caller => :11 '
814 || ' ); '
815 || ' END; ';
816
817 EXECUTE IMMEDIATE l_pls_block USING
818 p_api_version
819 ,p_init_msg_list
820 ,p_commit
821 ,p_validation_level
822 ,OUT l_return_status
823 ,OUT l_msg_count
824 ,OUT l_msg_data
825 ,p_change_id
826 ,p_route_id
827 ,p_lc_phase_code
828 ,p_api_caller ;
829
830
831 -- set Return vars
832 x_return_status := l_return_status;
833 x_msg_count := l_msg_count;
834 x_msg_data := l_msg_data;
835
836 -- Until DOM team implemented the logic indeed
837 IF x_return_status IS NULL
838 THEN
839 x_return_status := FND_API.G_RET_STS_SUCCESS ;
840 END IF ;
841
842 IF g_debug_flag THEN
843 Write_Debug('After Calling DOM_DOCUMENT_UTIL.Abort_Doc_LC_Phase_WF: ' || l_return_status);
844 END IF ;
845
846
847 EXCEPTION
848 WHEN PLSQL_COMPILE_ERROR THEN
849 -- Assuming DOM is not installed
850 x_return_status := FND_API.G_RET_STS_SUCCESS ;
851 x_msg_count := 0;
852 x_msg_data := NULL;
853
854 IF g_debug_flag THEN
855 Write_Debug('Exception Abort_Doc_LC_Phase_WF: PLSQL_COMPILE_ERROR ');
856 END IF ;
857
858 WHEN OTHERS THEN
859
860
861
862 IF g_debug_flag THEN
863 Write_Debug('Unexpected Exception Abort_Doc_LC_Phase_WF: ' || SQLERRM);
864 END IF ;
865
866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
867
868 FND_MSG_PUB.Add_Exc_Msg
869 ( p_pkg_name => 'DOM_DOCUMENT_UTIL' ,
870 p_procedure_name => 'CHANGE_DOC_LC_PHASE',
871 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
872 );
873
874 FND_MSG_PUB.Count_And_Get
875 ( p_count => x_msg_count ,
876 p_data => x_msg_data
877 );
878
879
880 END Abort_Doc_LC_Phase_WF ;
881
882
883 --
884 -- Wrapper API to grant Document Role to Document Revision
885 --
886 PROCEDURE Grant_Document_Role
887 (
888 p_api_version IN NUMBER --
889 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
890 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
891 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
892 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
893 ,p_output_dir IN VARCHAR2 := NULL
894 ,p_debug_filename IN VARCHAR2 := NULL
895 ,x_return_status OUT NOCOPY VARCHAR2 --
896 ,x_msg_count OUT NOCOPY NUMBER --
897 ,x_msg_data OUT NOCOPY VARCHAR2 --
898 ,p_document_id IN NUMBER -- Dom Document Id
899 ,p_document_revision_id IN NUMBER -- Dom Document Revision Id
900 ,p_change_id IN NUMBER -- Change Id
901 ,p_change_line_id IN NUMBER -- Change Line Id
902 ,p_party_ids IN FND_TABLE_OF_NUMBER -- Person's HZ_PARTIES.PARTY_ID Array
903 ,p_role_id IN NUMBER -- Role Id to be granted
904 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
905 )
906 IS
907
908 l_change_line_id NUMBER ;
909
910 l_return_status VARCHAR2(1);
911 l_msg_count NUMBER;
912 l_msg_data VARCHAR2(2000);
913 l_pls_block VARCHAR2(4000);
914
915 BEGIN
916
917
918 -- For Test/Debug
919 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
920
921 IF g_debug_flag THEN
922 Write_Debug('ENG_DOCUMENT_UTIL.Grant_Document_Role Log');
923 Write_Debug('-----------------------------------------------------');
924 Write_Debug('Document Id : ' || TO_CHAR(p_document_id) );
925 Write_Debug('Document Revision Id : ' || TO_CHAR(p_document_revision_id) );
926 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
927 Write_Debug('Change Line Id : ' || TO_CHAR(p_change_line_id) );
928 Write_Debug('Role Id : ' || TO_CHAR(p_role_id) );
929 Write_Debug('API Caller : ' || p_api_caller);
930 Write_Debug('-----------------------------------------------------');
931 Write_Debug('Calling DOM_SECURITY_PUB.Grant_Document_Role');
932 END IF ;
933
934
935 l_return_status := FND_API.G_RET_STS_SUCCESS ;
936
937 l_change_line_id := p_change_line_id ;
938
939 IF l_change_line_id <= 0 THEN
940
941 l_change_line_id := NULL ;
942
943 END IF ;
944
945 IF g_debug_flag THEN
946 Write_Debug('Param: Change Line Id : ' || TO_CHAR(l_change_line_id) );
947 END IF ;
948
949
950 -- We always pass p_commit FND_API.G_FALSE
951 -- DOM API should NOT commit or rollback
952
953 /*
954 --
955 -- Comment out: somehow dynamic call does not work
956 -- I guess we should use DBMS_SQL to achieve this
957 --
958 -- l_pls_block := ' BEGIN '
959 -- || ' DOM_SECURITY_PUB.Grant_Document_Role'
960 -- || ' ( p_api_version => :1 '
961 -- || ' ,p_init_msg_list => :2 '
962 -- || ' ,p_commit => :3 '
963 -- || ' ,p_validation_level => :4 '
964 -- || ' ,x_return_status => :5 '
965 -- || ' ,x_msg_count => :6 '
966 -- || ' ,x_msg_data => :7 '
967 -- || ' ,p_object_name => ''DOM_DOCUMENT_REVISION'' '
968 -- || ' ,p_pk1_value => :8 ' -- Document_Id
969 -- || ' ,p_pk2_value => :9 ' -- Revision_Id
970 -- || ' ,p_pk3_value => :10 ' -- Change_Id
971 -- || ' ,p_pk4_value => :11 ' -- Change_Line_Id
972 -- || ' ,p_pk5_value => NULL '
973 -- || ' ,p_party_ids => :12 '
974 -- || ' ,p_role_id => :13 '
975 -- || ' ,p_api_caller => :14 '
976 -- || ' ); '
977 -- || ' END; ';
978 --
979 -- EXECUTE IMMEDIATE l_pls_block USING
980 -- p_api_version
981 -- ,p_init_msg_list
982 -- ,p_commit
983 -- ,p_validation_level
984 -- ,OUT l_return_status
985 -- ,OUT l_msg_count
986 -- ,OUT l_msg_data
987 -- ,TO_CHAR(p_document_id)
988 -- ,TO_CHAR(p_document_revision_id)
989 -- ,TO_CHAR(p_change_id)
990 -- ,TO_CHAR(l_change_line_id)
991 -- ,p_party_ids
992 -- ,p_role_id
993 -- ,p_api_caller ;
994 --
995 */
996
997
998 BEGIN
999
1000 DOM_SECURITY_PUB.Grant_Document_Role
1001 ( p_api_version => p_api_version
1002 ,p_init_msg_list => p_init_msg_list
1003 ,p_commit => p_commit
1004 ,p_validation_level => p_validation_level
1005 ,x_return_status => l_return_status
1006 ,x_msg_count => l_msg_count
1007 ,x_msg_data => l_msg_data
1008 ,p_object_name => 'DOM_DOCUMENT_REVISION'
1009 ,p_pk1_value => TO_CHAR(p_document_id) -- Document_Id
1010 ,p_pk2_value => TO_CHAR(p_document_revision_id) -- Revision_Id
1011 ,p_pk3_value => TO_CHAR(p_change_id) -- Change_Id
1012 ,p_pk4_value => TO_CHAR(l_change_line_id) -- Change_Line_Id
1013 ,p_pk5_value => NULL
1014 ,p_party_ids => p_party_ids
1015 ,p_role_id => p_role_id
1016 ,p_api_caller => p_api_caller
1017 );
1018
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 IF g_debug_flag THEN
1022 Write_Debug('Unexpected Exception DOM_SECURITY_PUB.Grant_Document_Role: ' || SQLERRM);
1023 END IF ;
1024
1025 END ;
1026
1027 -- set Return vars
1028 x_return_status := l_return_status;
1029 x_msg_count := l_msg_count;
1030 x_msg_data := l_msg_data;
1031
1032
1033 -- Until DOM team implemented the logic indeed
1034 IF x_return_status IS NULL
1035 THEN
1036 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1037 END IF ;
1038
1039
1040 IF g_debug_flag THEN
1041 Write_Debug('After Calling DOM_SECURITY_PUB.Grant_Document_Role: ' || l_return_status);
1042 END IF ;
1043
1044 EXCEPTION
1045 WHEN PLSQL_COMPILE_ERROR THEN
1046 -- Assuming DOM is not installed
1047 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1048 x_msg_count := 0;
1049 x_msg_data := NULL;
1050
1051 IF g_debug_flag THEN
1052 Write_Debug('Exception Grant_Document_Role: PLSQL_COMPILE_ERROR ');
1053 END IF ;
1054
1055 WHEN OTHERS THEN
1056
1057 IF g_debug_flag THEN
1058 Write_Debug('Unexpected Exception Grant_Document_Role: ' || SQLERRM);
1059 END IF ;
1060
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1062
1063 FND_MSG_PUB.Add_Exc_Msg
1064 ( p_pkg_name => 'DOM_SECURITY_PUB' ,
1065 p_procedure_name => 'Grant_Document_Role',
1066 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
1067 );
1068
1069 FND_MSG_PUB.Count_And_Get
1070 ( p_count => x_msg_count ,
1071 p_data => x_msg_data
1072 );
1073
1074
1075 END Grant_Document_Role ;
1076
1077
1078
1079 --
1080 -- Wrapper API to revoke Document Role to Document Revision
1081 --
1082 PROCEDURE Revoke_Document_Role
1083 (
1084 p_api_version IN NUMBER --
1085 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1086 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1087 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1088 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1089 ,p_output_dir IN VARCHAR2 := NULL
1090 ,p_debug_filename IN VARCHAR2 := NULL
1091 ,x_return_status OUT NOCOPY VARCHAR2 --
1092 ,x_msg_count OUT NOCOPY NUMBER --
1093 ,x_msg_data OUT NOCOPY VARCHAR2 --
1094 ,p_document_id IN NUMBER -- Dom Document Id
1095 ,p_document_revision_id IN NUMBER -- Dom Document Revision Id
1096 ,p_change_id IN NUMBER -- Change Id
1097 ,p_change_line_id IN NUMBER -- Change Line Id
1098 ,p_party_ids IN FND_TABLE_OF_NUMBER -- Person's HZ_PARTIES.PARTY_ID Array
1099 ,p_role_id IN NUMBER -- Role Id to be revoked. If NULL, Revoke all grants per given object info
1100 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
1101 )
1102 IS
1103
1104 l_change_line_id NUMBER ;
1105
1106 l_return_status VARCHAR2(1);
1107 l_msg_count NUMBER;
1108 l_msg_data VARCHAR2(2000);
1109 l_pls_block VARCHAR2(4000);
1110
1111 BEGIN
1112
1113 -- For Test/Debug
1114 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1115
1116 IF g_debug_flag THEN
1117 Write_Debug('ENG_DOCUMENT_UTIL.Revoke_Document_Role Log');
1118 Write_Debug('-----------------------------------------------------');
1119 Write_Debug('Document Id : ' || TO_CHAR(p_document_id) );
1120 Write_Debug('Document Revision Id : ' || TO_CHAR(p_document_revision_id) );
1121 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
1122 Write_Debug('Change Line Id : ' || TO_CHAR(p_change_line_id) );
1123 Write_Debug('Role Id : ' || TO_CHAR(p_role_id) );
1124 Write_Debug('API Caller : ' || p_api_caller);
1125 Write_Debug('-----------------------------------------------------');
1126 Write_Debug('Calling DOM_SECURITY_PUB.Revoke_Document_Role');
1127 END IF ;
1128
1129
1130 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1131
1132 l_change_line_id := p_change_line_id ;
1133
1134 IF l_change_line_id <= 0 THEN
1135
1136 l_change_line_id := NULL ;
1137
1138 END IF ;
1139
1140
1141 IF g_debug_flag THEN
1142 Write_Debug('Param: Change Line Id : ' || TO_CHAR(l_change_line_id) );
1143 END IF ;
1144
1145
1146
1147 -- We always pass p_commit FND_API.G_FALSE
1148 -- DOM API should NOT commit or rollback
1149 -- We always pass p_commit FND_API.G_FALSE
1150 -- DOM API should NOT commit or rollback
1151 -- No need to pass p_role_id
1152
1153 /*
1154 --
1155 -- Comment out: somehow dynamic call does not work
1156 -- I guess we should use DBMS_SQL to achieve this
1157 --
1158 -- l_pls_block := ' BEGIN '
1159 -- || ' DOM_SECURITY_PUB.Revoke_Document_Role'
1160 -- || ' ( p_api_version => :1 '
1161 -- || ' ,p_init_msg_list => :2 '
1162 -- || ' ,p_commit => :3 '
1163 -- || ' ,p_validation_level => :4 '
1164 -- || ' ,x_return_status => :5 '
1165 -- || ' ,x_msg_count => :6 '
1166 -- || ' ,x_msg_data => :7 '
1167 -- || ' ,p_object_name => ''DOM_DOCUMENT_REVISION'' '
1168 -- || ' ,p_pk1_value => :8 ' -- Document_Id
1169 -- || ' ,p_pk2_value => :9 ' -- Revision_Id
1170 -- || ' ,p_pk3_value => :10 ' -- Change_Id
1171 -- || ' ,p_pk4_value => :11 ' -- Change_Line_Id
1172 -- || ' ,p_pk5_value => NULL '
1173 -- || ' ,p_party_ids => :12 '
1174 -- || ' ,p_role_id => NULL '
1175 -- || ' ,p_api_caller => :13 '
1176 -- || ' ); '
1177 -- || ' END; ';
1178 --
1179 -- EXECUTE IMMEDIATE l_pls_block USING
1180 -- p_api_version
1181 -- ,p_init_msg_list
1182 -- ,p_commit
1183 -- ,p_validation_level
1184 -- ,OUT l_return_status
1185 -- ,OUT l_msg_count
1186 -- ,OUT l_msg_data
1187 -- ,TO_CHAR(p_document_id)
1188 -- ,TO_CHAR(p_document_revision_id)
1189 -- ,TO_CHAR(p_change_id)
1190 -- ,TO_CHAR(l_change_line_id)
1191 -- ,p_party_ids
1192 -- ,p_api_caller ;
1193 --
1194 */
1195
1196 BEGIN
1197
1198 DOM_SECURITY_PUB.Revoke_Document_Role
1199 ( p_api_version => p_api_version
1200 ,p_init_msg_list => p_init_msg_list
1201 ,p_commit => p_commit
1202 ,p_validation_level => p_validation_level
1203 ,x_return_status => l_return_status
1204 ,x_msg_count => l_msg_count
1205 ,x_msg_data => l_msg_data
1206 ,p_object_name => 'DOM_DOCUMENT_REVISION'
1207 ,p_pk1_value => TO_CHAR(p_document_id) -- Document_Id
1208 ,p_pk2_value => TO_CHAR(p_document_revision_id) -- Revision_Id
1209 ,p_pk3_value => TO_CHAR(p_change_id) -- Change_Id
1210 ,p_pk4_value => TO_CHAR(l_change_line_id) -- Change_Line_Id
1211 ,p_pk5_value => NULL
1212 ,p_party_ids => p_party_ids
1213 ,p_role_id => NULL
1214 ,p_api_caller => p_api_caller
1215 );
1216
1217 EXCEPTION
1218 WHEN OTHERS THEN
1219 IF g_debug_flag THEN
1220 Write_Debug('Unexpected Exception DOM_SECURITY_PUB.Revoke_Document_Role: ' || SQLERRM);
1221 END IF ;
1222
1223 END ;
1224
1225
1226 -- set Return vars
1227 x_return_status := l_return_status;
1228 x_msg_count := l_msg_count;
1229 x_msg_data := l_msg_data;
1230
1231
1232 -- Until DOM team implemented the logic indeed
1233 IF x_return_status IS NULL
1234 THEN
1235 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1236 END IF ;
1237
1238
1239 IF g_debug_flag THEN
1240 Write_Debug('After Calling DOM_SECURITY_PUB.Revoke_Document_Role: ' || l_return_status);
1241 END IF ;
1242
1243
1244
1245 EXCEPTION
1246 WHEN PLSQL_COMPILE_ERROR THEN
1247 -- Assuming DOM is not installed
1248 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1249 x_msg_count := 0;
1250 x_msg_data := NULL;
1251
1252 IF g_debug_flag THEN
1253 Write_Debug('Exception Revoke_Document_Role: PLSQL_COMPILE_ERROR ');
1254 END IF ;
1255
1256 WHEN OTHERS THEN
1257
1258 IF g_debug_flag THEN
1259 Write_Debug('Unexpected Exception Revoke_Document_Role: ' || SQLERRM);
1260 END IF ;
1261
1262
1263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1264
1265 FND_MSG_PUB.Add_Exc_Msg
1266 ( p_pkg_name => 'DOM_SECURITY_PUB' ,
1267 p_procedure_name => 'Revoke_Document_Role',
1268 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
1269 );
1270
1271 FND_MSG_PUB.Count_And_Get
1272 ( p_count => x_msg_count ,
1273 p_data => x_msg_data
1274 );
1275
1276 END Revoke_Document_Role ;
1277
1278
1279 --
1280 -- Wrapper API to grant Document Role to Document Revision
1281 --
1282 PROCEDURE Grant_Attachments_OCSRole
1283 (
1284 p_api_version IN NUMBER --
1285 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1286 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1287 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1288 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1289 ,p_output_dir IN VARCHAR2 := NULL
1290 ,p_debug_filename IN VARCHAR2 := NULL
1291 ,x_return_status OUT NOCOPY VARCHAR2 --
1292 ,x_msg_count OUT NOCOPY NUMBER --
1293 ,x_msg_data OUT NOCOPY VARCHAR2 --
1294 ,p_entity_name IN VARCHAR2
1295 ,p_pk1value IN VARCHAR2
1296 ,p_pk2value IN VARCHAR2
1297 ,p_pk3value IN VARCHAR2
1298 ,p_pk4value IN VARCHAR2
1299 ,p_pk5value IN VARCHAR2
1300 ,p_party_ids IN FND_TABLE_OF_NUMBER -- Person's HZ_PARTIES.PARTY_ID Array
1301 ,p_ocs_role IN VARCHAR2 -- OCS File Role to be granted
1302 ,p_source_media_id_tbl IN FND_TABLE_OF_NUMBER := null
1303 ,p_attachment_id_tbl IN FND_TABLE_OF_NUMBER := null
1304 ,p_repository_id_tbl IN FND_TABLE_OF_NUMBER := null
1305 ,p_submitted_by IN NUMBER := null
1306 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
1307 )
1308 IS
1309
1310 l_return_status VARCHAR2(1);
1311 l_msg_count NUMBER;
1312 l_msg_data VARCHAR2(2000);
1313 l_pls_block VARCHAR2(4000);
1314
1315 BEGIN
1316
1317 -- For Test/Debug
1318 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1319
1320 IF g_debug_flag THEN
1321 Write_Debug('ENG_DOCUMENT_UTIL.Grant_Attachments_OCSRole Log');
1322 Write_Debug('-----------------------------------------------------');
1323 Write_Debug('Entity Name : ' || p_entity_name);
1324 Write_Debug('PK1 Value : ' || p_pk1value);
1325 Write_Debug('PK2 Value : ' || p_pk2value);
1326 Write_Debug('PK3 Value : ' || p_pk3value);
1327 Write_Debug('PK4 Value : ' || p_pk4value);
1328 Write_Debug('PK5 Value : ' || p_pk5value);
1329 Write_Debug('OCS ROle : ' || p_ocs_role);
1330 Write_Debug('API Caller : ' || p_api_caller);
1331 Write_Debug('-----------------------------------------------------');
1332 Write_Debug('Calling DOM_SECURITY_PUB.Grant_Attachments_OCSRole');
1333 END IF ;
1334
1335
1336 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1337
1338 -- We always pass p_commit FND_API.G_FALSE
1339 -- DOM API should NOT commit or rollback
1340
1341 /*
1342 --
1343 -- Comment out: somehow dynamic call does not work
1344 -- I guess we should use DBMS_SQL to achieve this
1345 --
1346 -- l_pls_block := ' BEGIN '
1347 -- || ' DOM_SECURITY_PUB.Grant_Attachments_OCSRole'
1348 -- || ' ( p_api_version => :1 '
1349 -- || ' ,p_init_msg_list => :2 '
1350 -- || ' ,p_commit => :3 '
1351 -- || ' ,p_validation_level => :4 '
1352 -- || ' ,x_return_status => :5 '
1353 -- || ' ,x_msg_count => :6 '
1354 -- || ' ,x_msg_data => :7 '
1355 -- || ' ,p_entity_name => :8 '
1356 -- || ' ,p_pk1_value => :9 ' -- Document_Id
1357 -- || ' ,p_pk2_value => :10 ' -- Revision_Id
1358 -- || ' ,p_pk3_value => :11 ' -- Change_Id
1359 -- || ' ,p_pk4_value => :12 ' -- Change_Line_Id
1360 -- || ' ,p_pk5_value => :13 '
1361 -- || ' ,p_party_ids => :14 '
1362 -- || ' ,p_ocs_role => :15 '
1363 -- || ' ,p_api_caller => :16 '
1364 -- || ' ); '
1365 -- || ' END; ';
1366 --
1367 -- EXECUTE IMMEDIATE l_pls_block USING
1368 -- p_api_version
1369 -- ,p_init_msg_list
1370 -- ,p_commit
1371 -- ,p_validation_level
1372 -- ,OUT l_return_status
1373 -- ,OUT l_msg_count
1374 -- ,OUT l_msg_data
1375 -- ,p_entity_name
1376 -- ,p_pk1value
1377 -- ,p_pk2value
1378 -- ,p_pk3value
1379 -- ,p_pk4value
1380 -- ,p_pk5value
1381 -- ,p_party_ids
1382 -- ,p_ocs_role
1383 -- ,p_api_caller ;
1384 --
1385 */
1386
1387 BEGIN
1388
1389
1390 DOM_SECURITY_PUB.Grant_Attachments_OCSRole
1391 ( p_api_version => p_api_version
1392 ,p_init_msg_list => p_init_msg_list
1393 ,p_commit => p_commit
1394 ,p_validation_level => p_validation_level
1395 ,x_return_status => l_return_status
1396 ,x_msg_count => l_msg_count
1397 ,x_msg_data => l_msg_data
1398 ,p_entity_name => p_entity_name
1399 ,p_pk1_value => p_pk1value
1400 ,p_pk2_value => p_pk2value
1401 ,p_pk3_value => p_pk3value
1402 ,p_pk4_value => p_pk4value
1403 ,p_pk5_value => p_pk5value
1404 ,p_party_ids => p_party_ids
1405 ,p_ocs_role => p_ocs_role
1406 ,p_api_caller => p_api_caller
1407 );
1408
1409 IF x_return_status IS NULL
1410 THEN
1411 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1412 END IF ;
1413
1414 if x_return_status = FND_API.G_RET_STS_SUCCESS
1415 AND p_source_media_id_tbl is not null and p_source_media_id_tbl.count > 0
1416 AND p_repository_id_tbl is not null and p_repository_id_tbl.count>0
1417 then
1418 for ind in p_source_media_id_tbl.first .. p_source_media_id_tbl.last
1419 loop
1420 DOM_SECURITY_PUB.Grant_Attachment_Access
1421 (
1422 p_api_version => p_api_version,
1423 p_attached_document_id => p_attachment_id_tbl(ind),
1424 p_source_media_id => p_source_media_id_tbl(ind),
1425 p_repository_id => p_repository_id_tbl(ind),
1426 p_ocs_role => p_ocs_role,
1427 p_party_ids => p_party_ids,
1428 p_submitted_by => p_submitted_by,
1429 x_msg_count => l_msg_count,
1430 x_msg_data => l_msg_data ,
1431 x_return_status => l_return_status
1432 );
1433 END LOOP;
1434
1435 END IF;
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 IF g_debug_flag THEN
1440 Write_Debug('Unexpected Exception Grant_Attachments_OCSRole: ' || SQLERRM);
1441 END IF ;
1442
1443 END ;
1444
1445
1446 -- set Return vars
1447 x_return_status := l_return_status;
1448 x_msg_count := l_msg_count;
1449 x_msg_data := l_msg_data;
1450
1451
1452 -- Until DOM team implemented the logic indeed
1453 IF x_return_status IS NULL
1454 THEN
1455 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1456 END IF ;
1457
1458
1459 IF g_debug_flag THEN
1460 Write_Debug('After Calling DOM_SECURITY_PUB.Grant_Attachments_OCSRole: ' || l_return_status);
1461 END IF ;
1462
1463 EXCEPTION
1464 WHEN PLSQL_COMPILE_ERROR THEN
1465 -- Assuming DOM is not installed
1466 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1467 x_msg_count := 0;
1468 x_msg_data := NULL;
1469
1470
1471 IF g_debug_flag THEN
1472 Write_Debug('Exception Grant_Attachments_OCSRole: PLSQL_COMPILE_ERROR ');
1473 END IF ;
1474
1475 WHEN OTHERS THEN
1476 IF g_debug_flag THEN
1477 Write_Debug('Unexpected Exception Grant_Attachments_OCSRole: ' || SQLERRM);
1478 END IF ;
1479
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1481
1482 FND_MSG_PUB.Add_Exc_Msg
1483 ( p_pkg_name => 'DOM_SECURITY_PUB' ,
1484 p_procedure_name => 'Grant_Attachments_OCSRole',
1485 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
1486 );
1487
1488 FND_MSG_PUB.Count_And_Get
1489 ( p_count => x_msg_count ,
1490 p_data => x_msg_data
1491 );
1492
1493
1494 END Grant_Attachments_OCSRole ;
1495
1496
1497
1498 --
1499 -- Wrapper API to revoke Document Role to Document Revision
1500 --
1501 PROCEDURE Revoke_Attachments_OCSRole
1502 (
1503 p_api_version IN NUMBER --
1504 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1505 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1506 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1507 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1508 ,p_output_dir IN VARCHAR2 := NULL
1509 ,p_debug_filename IN VARCHAR2 := NULL
1510 ,x_return_status OUT NOCOPY VARCHAR2 --
1511 ,x_msg_count OUT NOCOPY NUMBER --
1512 ,x_msg_data OUT NOCOPY VARCHAR2 --
1513 ,p_entity_name IN VARCHAR2
1514 ,p_pk1value IN VARCHAR2
1515 ,p_pk2value IN VARCHAR2
1516 ,p_pk3value IN VARCHAR2
1517 ,p_pk4value IN VARCHAR2
1518 ,p_pk5value IN VARCHAR2
1519 ,p_party_ids IN FND_TABLE_OF_NUMBER -- Person's HZ_PARTIES.PARTY_ID Array
1520 ,p_ocs_role IN VARCHAR2 -- OCS File Role to be revoked. If NULL, Revoke all grants per given entity info
1521 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
1522 )
1523 IS
1524
1525 l_return_status VARCHAR2(1);
1526 l_msg_count NUMBER;
1527 l_msg_data VARCHAR2(2000);
1528 l_pls_block VARCHAR2(4000);
1529
1530 BEGIN
1531
1532 -- For Test/Debug
1533 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1534
1535 IF g_debug_flag THEN
1536 Write_Debug('ENG_DOCUMENT_UTIL.Revoke_Attachments_OCSRole Log');
1537 Write_Debug('-----------------------------------------------------');
1538 Write_Debug('Entity Name : ' || p_entity_name);
1539 Write_Debug('PK1 Value : ' || p_pk1value);
1540 Write_Debug('PK2 Value : ' || p_pk2value);
1541 Write_Debug('PK3 Value : ' || p_pk3value);
1542 Write_Debug('PK4 Value : ' || p_pk4value);
1543 Write_Debug('PK5 Value : ' || p_pk5value);
1544 Write_Debug('API Caller : ' || p_api_caller);
1545 Write_Debug('OCS ROle : ' || p_ocs_role);
1546 Write_Debug('-----------------------------------------------------');
1547 Write_Debug('Calling DOM_SECURITY_PUB.Revoke_Attachments_OCSRole');
1548 END IF ;
1549
1550 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1551
1552
1553
1554 -- We always pass p_commit FND_API.G_FALSE
1555 -- DOM API should NOT commit or rollback
1556 -- No need to pass p_ocs_role
1557
1558 /*
1559 --
1560 -- Comment out: somehow dynamic call does not work
1561 -- I guess we should use DBMS_SQL to achieve this
1562 --
1563 --
1564 -- l_pls_block := ' BEGIN '
1565 -- || ' DOM_SECURITY_PUB.Revoke_Attachments_OCSRole'
1566 -- || ' ( p_api_version => :1 '
1567 -- || ' ,p_init_msg_list => :2 '
1568 -- || ' ,p_commit => :3 '
1569 -- || ' ,p_validation_level => :4 '
1570 -- || ' ,x_return_status => :5 '
1571 -- || ' ,x_msg_count => :6 '
1572 -- || ' ,x_msg_data => :7 '
1573 -- || ' ,p_entity_name => :8 '
1574 -- || ' ,p_pk1_value => :9 '
1575 -- || ' ,p_pk2_value => :10 '
1576 -- || ' ,p_pk3_value => :11 '
1577 -- || ' ,p_pk4_value => :12 '
1578 -- || ' ,p_pk5_value => :13 '
1579 -- || ' ,p_party_ids => :14 '
1580 -- || ' ,p_ocs_role => NULL '
1581 -- || ' ,p_api_caller => :15 '
1582 -- || ' ); '
1583 -- || ' END; ';
1584 --
1585 -- EXECUTE IMMEDIATE l_pls_block USING
1586 -- p_api_version
1587 -- ,p_init_msg_list
1588 -- ,p_commit
1589 -- ,p_validation_level
1590 -- ,OUT l_return_status
1591 -- ,OUT l_msg_count
1592 -- ,OUT l_msg_data
1593 -- ,p_entity_name
1594 -- ,p_pk1value
1595 -- ,p_pk2value
1596 -- ,p_pk3value
1597 -- ,p_pk4value
1598 -- ,p_pk5value
1599 -- ,p_party_ids
1600 -- ,p_api_caller ;
1601 --
1602 */
1603
1604 BEGIN
1605
1606
1607 DOM_SECURITY_PUB.Revoke_Attachments_OCSRole
1608 ( p_api_version => p_api_version
1609 ,p_init_msg_list => p_init_msg_list
1610 ,p_commit => p_commit
1611 ,p_validation_level => p_validation_level
1612 ,x_return_status => l_return_status
1613 ,x_msg_count => l_msg_count
1614 ,x_msg_data => l_msg_data
1615 ,p_entity_name => p_entity_name
1616 ,p_pk1_value => p_pk1value
1617 ,p_pk2_value => p_pk2value
1618 ,p_pk3_value => p_pk3value
1619 ,p_pk4_value => p_pk4value
1620 ,p_pk5_value => p_pk5value
1621 ,p_party_ids => p_party_ids
1622 ,p_ocs_role => NULL
1623 ,p_api_caller => p_api_caller
1624 );
1625
1626 EXCEPTION
1627 WHEN OTHERS THEN
1628 IF g_debug_flag THEN
1629 Write_Debug('Unexpected Exception Revoke_Attachments_OCSRole: ' || SQLERRM);
1630 END IF ;
1631
1632 END ;
1633
1634
1635 -- set Return vars
1636 x_return_status := l_return_status;
1637 x_msg_count := l_msg_count;
1638 x_msg_data := l_msg_data;
1639
1640
1641 -- Until DOM team implemented the logic indeed
1642 IF x_return_status IS NULL
1643 THEN
1644 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1645 END IF ;
1646
1647
1648
1649 IF g_debug_flag THEN
1650 Write_Debug('After Calling DOM_SECURITY_PUB.Revoke_Attachments_OCSRole: ' || l_return_status);
1651 END IF ;
1652
1653
1654 EXCEPTION
1655 WHEN PLSQL_COMPILE_ERROR THEN
1656 -- Assuming DOM is not installed
1657 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1658 x_msg_count := 0;
1659 x_msg_data := NULL;
1660
1661
1662 IF g_debug_flag THEN
1663 Write_Debug('Exception Grant_Attachments_OCSRole: PLSQL_COMPILE_ERROR ');
1664 END IF ;
1665
1666 WHEN OTHERS THEN
1667
1668 IF g_debug_flag THEN
1669 Write_Debug('Unexpected Exception Grant_Attachments_OCSRole: ' || SQLERRM);
1670 END IF ;
1671
1672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1673
1674 FND_MSG_PUB.Add_Exc_Msg
1675 ( p_pkg_name => 'DOM_SECURITY_PUB' ,
1676 p_procedure_name => 'Revoke_Attachments_OCSRole',
1677 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
1678 );
1679
1680 FND_MSG_PUB.Count_And_Get
1681 ( p_count => x_msg_count ,
1682 p_data => x_msg_data
1683 );
1684
1685
1686 END Revoke_Attachments_OCSRole ;
1687
1688
1689
1690
1691 END ENG_DOCUMENT_UTIL;