[Home] [Help]
PACKAGE BODY: APPS.ENG_DOCUMENT_UTIL
Source
1 PACKAGE BODY ENG_DOCUMENT_UTIL AS
2 /* $Header: ENGUDOCB.pls 120.16 2011/03/30 04:51:25 pnagasur ship $ */
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 --Commented out method as part of 12.2 DOM deprecation
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 NULL;
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 --
361 ,p_output_dir IN VARCHAR2 := NULL
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 --
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 --Commented this method as part of 12.2 DOM deprecation
380 x_return_status := FND_API.G_RET_STS_SUCCESS ;
381 x_msg_count := 0;
382 x_msg_data := NULL;
383 -- For Test/Debug
384 /* Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
385
386 IF g_debug_flag THEN
387 Write_Debug('ENG_DOCUMENT_UTIL.Update_Approval_Status Log');
388 Write_Debug('-----------------------------------------------------');
389 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
390 Write_Debug('Approval Status : ' || TO_CHAR(p_approval_status) );
391 Write_Debug('Workflow Status : ' || p_wf_route_status);
392 Write_Debug('API Caller : ' || p_api_caller);
393 Write_Debug('-----------------------------------------------------');
394 Write_Debug('Calling DOM_LIFECYCLE_UTIL.UPDATE_APPROVAL_STATUS');
395 END IF ;
396
397
398 l_return_status := FND_API.G_RET_STS_SUCCESS ;
399
400 -- We always pass p_commit FND_API.G_FALSE
401 -- DOM API should NOT commit or rollback
402 l_pls_block := ' BEGIN '
403 || ' DOM_DOCUMENT_UTIL.UPDATE_APPROVAL_STATUS'
404 || ' ( p_api_version => :1 '
405 || ' ,p_init_msg_list => :2 '
406 || ' ,p_commit => :3 '
407 || ' ,p_validation_level => :4 '
408 || ' ,x_return_status => :5 '
409 || ' ,x_msg_count => :6 '
410 || ' ,x_msg_data => :7 '
411 || ' ,p_change_id => :8 '
412 || ' ,p_approval_status => :9 '
413 || ' ,p_wf_route_status => :10 '
414 || ' ,p_api_caller => :11 '
415 || ' ); '
416 || ' END; ';
417
418 EXECUTE IMMEDIATE l_pls_block USING
419 p_api_version
420 ,p_init_msg_list
421 ,p_commit
422 ,p_validation_level
423 ,OUT l_return_status
424 ,OUT l_msg_count
425 ,OUT l_msg_data
426 ,p_change_id
427 ,p_approval_status
428 ,p_wf_route_status
429 ,p_api_caller ;
430
431 -- Set Return vars
432 x_return_status := l_return_status;
433 x_msg_count := l_msg_count;
434 x_msg_data := l_msg_data;
435
436 -- Until DOM team implemented the logic indeed
437 IF x_return_status IS NULL
438 THEN
439 x_return_status := FND_API.G_RET_STS_SUCCESS ;
440 END IF ;
441
442 IF g_debug_flag THEN
443 Write_Debug('After Calling DOM_DOCUMENT_UTIL.UPDATE_APPROVAL_STATUS: ' || l_return_status);
444 END IF ;
445
446
447 EXCEPTION
448 WHEN PLSQL_COMPILE_ERROR THEN
449 -- Assuming DOM is not installed
450 x_return_status := FND_API.G_RET_STS_SUCCESS ;
451 x_msg_count := 0;
452 x_msg_data := NULL;
453
454
455 IF g_debug_flag THEN
456 Write_Debug('Exception Update_Approval_Status: PLSQL_COMPILE_ERROR ');
457 END IF ;
458
459
460 WHEN OTHERS THEN
461
462 IF g_debug_flag THEN
463 Write_Debug('Unexpected Exception Update_Approval_Status: ' || SQLERRM);
464 END IF ;
465
466
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
468
469 FND_MSG_PUB.Add_Exc_Msg
470 ( p_pkg_name => 'DOM_DOCUMENT_UTIL' ,
471 p_procedure_name => 'UPDATE_APPROVAL_STATUS',
472 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
473 );
474
475 FND_MSG_PUB.Count_And_Get
476 ( p_count => x_msg_count ,
477 p_data => x_msg_data
478 );
479
480 */
481 END Update_Approval_Status ;
482
483
484 --
485 -- Wrapper API to integrate DOM Document API when Promoting/Demoting
486 -- Document LC Phase
487 --
488 PROCEDURE Change_Doc_LC_Phase
489 (
490 p_api_version IN NUMBER --
491 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
492 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
493 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
494 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
498 ,x_msg_count OUT NOCOPY NUMBER --
495 ,p_output_dir IN VARCHAR2 := NULL
496 ,p_debug_filename IN VARCHAR2 := NULL
497 ,x_return_status OUT NOCOPY VARCHAR2 --
499 ,x_msg_data OUT NOCOPY VARCHAR2 --
500 ,p_change_id IN NUMBER -- header's change_id
501 ,p_lc_phase_code IN NUMBER -- new phase
502 ,p_action_type IN VARCHAR2 := NULL -- promote/demote action type 'PROMOTE' or 'DEMOTE'
503 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
504 )
505 IS
506
507 /*
508 l_return_status VARCHAR2(1);
509 l_msg_count NUMBER;
510 l_msg_data VARCHAR2(2000);
511 l_pls_block VARCHAR2(4000);*/
512
513 BEGIN
514 --COmmented method as part of 12.2 DOM deprecation
515 x_return_status := FND_API.G_RET_STS_SUCCESS ;
516 x_msg_count := 0;
517 x_msg_data := NULL;
518
519 -- For Test/Debug
520 /* Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
521
522 IF g_debug_flag THEN
523 Write_Debug('ENG_DOCUMENT_UTIL.Change_Doc_LC_Phase Log');
524 Write_Debug('-----------------------------------------------------');
525 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
526 Write_Debug('LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
527 Write_Debug('Action Type : ' || p_action_type);
528 Write_Debug('API Caller : ' || p_api_caller);
529 Write_Debug('-----------------------------------------------------');
530 Write_Debug('Calling DOM_DOCUMENT_UTIL.CHANGE_DOC_LC_PHASE');
531 END IF ;
532
533 l_return_status := FND_API.G_RET_STS_SUCCESS ;
534
535
536 -- We always pass p_commit FND_API.G_FALSE
537 -- DOM API should NOT commit or rollback
538 l_pls_block := ' BEGIN '
539 || ' DOM_DOCUMENT_UTIL.CHANGE_DOC_LC_PHASE'
540 || ' ( p_api_version => :1 '
541 || ' ,p_init_msg_list => :2 '
542 || ' ,p_commit => :3 '
543 || ' ,p_validation_level => :4 '
544 || ' ,x_return_status => :5 '
545 || ' ,x_msg_count => :6 '
546 || ' ,x_msg_data => :7 '
547 || ' ,p_change_id => :8 '
548 || ' ,p_lc_phase_code => :9 '
549 || ' ,p_action_type => :10 ' -- 'PROMOTE' or 'DEMOTE'
550 || ' ,p_api_caller => :11 '
551 || ' ); '
552 || ' END; ';
553
554 EXECUTE IMMEDIATE l_pls_block USING
555 p_api_version
556 ,p_init_msg_list
557 ,p_commit
558 ,p_validation_level
559 ,OUT l_return_status
560 ,OUT l_msg_count
561 ,OUT l_msg_data
562 ,p_change_id
563 ,p_lc_phase_code
564 ,p_action_type
565 ,p_api_caller ;
566
567
568 -- set Return vars
569 x_return_status := l_return_status;
570 x_msg_count := l_msg_count;
571 x_msg_data := l_msg_data;
572
573 -- Until DOM team implemented the logic indeed
574 IF x_return_status IS NULL
575 THEN
576 x_return_status := FND_API.G_RET_STS_SUCCESS ;
577 END IF ;
578
579
580 IF g_debug_flag THEN
581 Write_Debug('After Calling DOM_DOCUMENT_UTIL.CHANGE_DOC_LC_PHASE: ' || l_return_status);
582 END IF ;
583
584
585 EXCEPTION
586 WHEN PLSQL_COMPILE_ERROR THEN
587 -- Assuming DOM is not installed
588 x_return_status := FND_API.G_RET_STS_SUCCESS ;
589 x_msg_count := 0;
590 x_msg_data := NULL;
591
592 IF g_debug_flag THEN
593 Write_Debug('Exception Change_Doc_LC_Phase: PLSQL_COMPILE_ERROR ');
594 END IF ;
595
596 WHEN OTHERS THEN
597
598
599
600 IF g_debug_flag THEN
601 Write_Debug('Unexpected Exception Change_Doc_LC_Phase: ' || SQLERRM);
602 END IF ;
603
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
605
606 FND_MSG_PUB.Add_Exc_Msg
607 ( p_pkg_name => 'DOM_DOCUMENT_UTIL' ,
608 p_procedure_name => 'CHANGE_DOC_LC_PHASE',
609 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
610 );
611
612 FND_MSG_PUB.Count_And_Get
613 ( p_count => x_msg_count ,
614 p_data => x_msg_data
615 );
616
617 */
618 END Change_Doc_LC_Phase ;
619
620
621
622 --
623 -- Wrapper API to integrate DOM Document API when starting
624 -- Document LC Phase Workflow
625 --
626 PROCEDURE Start_Doc_LC_Phase_WF
627 (
628 p_api_version IN NUMBER --
629 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
630 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
631 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
632 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
633 ,p_output_dir IN VARCHAR2 := NULL
634 ,p_debug_filename IN VARCHAR2 := NULL
635 ,x_return_status OUT NOCOPY VARCHAR2 --
639 ,p_route_id IN NUMBER -- DOC LC Phase WF Route ID
636 ,x_msg_count OUT NOCOPY NUMBER --
637 ,x_msg_data OUT NOCOPY VARCHAR2 --
638 ,p_change_id IN NUMBER -- DOC LC Object Change Id
640 ,p_lc_phase_code IN NUMBER := NULL -- Doc LC Phase
641 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
642 )
643 IS
644
645 /*
646 l_return_status VARCHAR2(1);
647 l_msg_count NUMBER;
648 l_msg_data VARCHAR2(2000);
649 l_pls_block VARCHAR2(4000);*/
650
651 BEGIN
652 --Commented this method as part of 12.2 DOM deprecation
653 x_return_status := FND_API.G_RET_STS_SUCCESS ;
654 x_msg_count := 0;
655 x_msg_data := NULL;
656 /*
657 -- For Test/Debug
658 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
659
660 IF g_debug_flag THEN
661 Write_Debug('ENG_DOCUMENT_UTIL.Start_Doc_LC_Phase_WF Log');
662 Write_Debug('-----------------------------------------------------');
663 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
664 Write_Debug('Route Id : ' || TO_CHAR(p_route_id) );
665 Write_Debug('LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
666 Write_Debug('API Caller : ' || p_api_caller);
667 Write_Debug('-----------------------------------------------------');
668 Write_Debug('Calling DOM_DOCUMENT_UTIL.Start_Doc_LC_Phase_WF');
669 END IF ;
670
671 l_return_status := FND_API.G_RET_STS_SUCCESS ;
672
673
674 -- We always pass p_commit FND_API.G_FALSE
675 -- DOM API should NOT commit or rollback
676 l_pls_block := ' BEGIN '
677 || ' DOM_DOCUMENT_UTIL.Start_Doc_LC_Phase_WF'
678 || ' ( p_api_version => :1 '
679 || ' ,p_init_msg_list => :2 '
680 || ' ,p_commit => :3 '
681 || ' ,p_validation_level => :4 '
682 || ' ,x_return_status => :5 '
683 || ' ,x_msg_count => :6 '
684 || ' ,x_msg_data => :7 '
685 || ' ,p_change_id => :8 '
686 || ' ,p_route_id => :9 '
687 || ' ,p_lc_phase_code => :10 '
688 || ' ,p_api_caller => :11 '
689 || ' ); '
690 || ' END; ';
691
692 EXECUTE IMMEDIATE l_pls_block USING
693 p_api_version
694 ,p_init_msg_list
695 ,p_commit
696 ,p_validation_level
697 ,OUT l_return_status
698 ,OUT l_msg_count
699 ,OUT l_msg_data
700 ,p_change_id
701 ,p_route_id
702 ,p_lc_phase_code
703 ,p_api_caller ;
704
705
706 -- set Return vars
707 x_return_status := l_return_status;
708 x_msg_count := l_msg_count;
709 x_msg_data := l_msg_data;
710
711 -- Until DOM team implemented the logic indeed
712 IF x_return_status IS NULL
713 THEN
714 x_return_status := FND_API.G_RET_STS_SUCCESS ;
715 END IF ;
716
717
718 IF g_debug_flag THEN
719 Write_Debug('After Calling DOM_DOCUMENT_UTIL.Start_Doc_LC_Phase_WF: ' || l_return_status);
720 END IF ;
721
722
723 EXCEPTION
724 WHEN PLSQL_COMPILE_ERROR THEN
725 -- Assuming DOM is not installed
726 x_return_status := FND_API.G_RET_STS_SUCCESS ;
727 x_msg_count := 0;
728 x_msg_data := NULL;
729
730 IF g_debug_flag THEN
731 Write_Debug('Exception Start_Doc_LC_Phase_WF: PLSQL_COMPILE_ERROR ');
732 END IF ;
733
734 WHEN OTHERS THEN
735
736
737
738 IF g_debug_flag THEN
739 Write_Debug('Unexpected Exception Start_Doc_LC_Phase_WF: ' || SQLERRM);
740 END IF ;
741
742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
743
744 FND_MSG_PUB.Add_Exc_Msg
745 ( p_pkg_name => 'DOM_DOCUMENT_UTIL' ,
746 p_procedure_name => 'Start_Doc_LC_Phase_WF',
747 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
748 );
749
750 FND_MSG_PUB.Count_And_Get
751 ( p_count => x_msg_count ,
752 p_data => x_msg_data
753 );
754
755 */
756 END Start_Doc_LC_Phase_WF ;
757
758
759 --
760 -- Wrapper API to integrate DOM Document API when aborting
761 -- Document LC Phase Workflow
762 --
763 PROCEDURE Abort_Doc_LC_Phase_WF
764 (
765 p_api_version IN NUMBER --
766 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
767 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
768 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
769 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
770 ,p_output_dir IN VARCHAR2 := NULL
771 ,p_debug_filename IN VARCHAR2 := NULL
772 ,x_return_status OUT NOCOPY VARCHAR2 --
773 ,x_msg_count OUT NOCOPY NUMBER --
774 ,x_msg_data OUT NOCOPY VARCHAR2 --
778 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
775 ,p_change_id IN NUMBER -- DOC LC Object Change Id
776 ,p_route_id IN NUMBER -- DOC LC Phase WF Route ID
777 ,p_lc_phase_code IN NUMBER := NULL -- Doc LC Phase
779 )
780 IS
781
782 /*
783 l_return_status VARCHAR2(1);
784 l_msg_count NUMBER;
785 l_msg_data VARCHAR2(2000);
786 l_pls_block VARCHAR2(4000);*/
787
788 BEGIN
789 --Commented this method as part of 12.2 DOM deprecation
790 x_return_status := FND_API.G_RET_STS_SUCCESS ;
791 x_msg_count := 0;
792 x_msg_data := NULL;
793
794 -- For Test/Debug
795 /* Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
796
797 IF g_debug_flag THEN
798 Write_Debug('ENG_DOCUMENT_UTIL.Abort_Doc_LC_Phase_WF Log');
799 Write_Debug('-----------------------------------------------------');
800 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
801 Write_Debug('Route Id : ' || TO_CHAR(p_route_id) );
802 Write_Debug('LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
803 Write_Debug('API Caller : ' || p_api_caller);
804 Write_Debug('-----------------------------------------------------');
805 Write_Debug('Calling DOM_DOCUMENT_UTIL.Abort_Doc_LC_Phase_WF');
806 END IF ;
807
808 l_return_status := FND_API.G_RET_STS_SUCCESS ;
809
810
811 -- We always pass p_commit FND_API.G_FALSE
812 -- DOM API should NOT commit or rollback
813 l_pls_block := ' BEGIN '
814 || ' DOM_DOCUMENT_UTIL.Abort_Doc_LC_Phase_WF'
815 || ' ( p_api_version => :1 '
816 || ' ,p_init_msg_list => :2 '
817 || ' ,p_commit => :3 '
818 || ' ,p_validation_level => :4 '
819 || ' ,x_return_status => :5 '
820 || ' ,x_msg_count => :6 '
821 || ' ,x_msg_data => :7 '
822 || ' ,p_change_id => :8 '
823 || ' ,p_route_id => :9 '
824 || ' ,p_lc_phase_code => :10 '
825 || ' ,p_api_caller => :11 '
826 || ' ); '
827 || ' END; ';
828
829 EXECUTE IMMEDIATE l_pls_block USING
830 p_api_version
831 ,p_init_msg_list
832 ,p_commit
833 ,p_validation_level
834 ,OUT l_return_status
835 ,OUT l_msg_count
836 ,OUT l_msg_data
837 ,p_change_id
838 ,p_route_id
839 ,p_lc_phase_code
840 ,p_api_caller ;
841
842
843 -- set Return vars
844 x_return_status := l_return_status;
845 x_msg_count := l_msg_count;
846 x_msg_data := l_msg_data;
847
848 -- Until DOM team implemented the logic indeed
849 IF x_return_status IS NULL
850 THEN
851 x_return_status := FND_API.G_RET_STS_SUCCESS ;
852 END IF ;
853
854 IF g_debug_flag THEN
855 Write_Debug('After Calling DOM_DOCUMENT_UTIL.Abort_Doc_LC_Phase_WF: ' || l_return_status);
856 END IF ;
857
858
859 EXCEPTION
860 WHEN PLSQL_COMPILE_ERROR THEN
861 -- Assuming DOM is not installed
862 x_return_status := FND_API.G_RET_STS_SUCCESS ;
863 x_msg_count := 0;
864 x_msg_data := NULL;
865
866 IF g_debug_flag THEN
867 Write_Debug('Exception Abort_Doc_LC_Phase_WF: PLSQL_COMPILE_ERROR ');
868 END IF ;
869
870 WHEN OTHERS THEN
871
872
873
874 IF g_debug_flag THEN
875 Write_Debug('Unexpected Exception Abort_Doc_LC_Phase_WF: ' || SQLERRM);
876 END IF ;
877
878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
879
880 FND_MSG_PUB.Add_Exc_Msg
881 ( p_pkg_name => 'DOM_DOCUMENT_UTIL' ,
882 p_procedure_name => 'CHANGE_DOC_LC_PHASE',
883 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
884 );
885
886 FND_MSG_PUB.Count_And_Get
887 ( p_count => x_msg_count ,
888 p_data => x_msg_data
889 );
890
891 */
892 END Abort_Doc_LC_Phase_WF ;
893
894
895 --
896 -- Wrapper API to grant Document Role to Document Revision
897 --
898 PROCEDURE Grant_Document_Role
899 (
900 p_api_version IN NUMBER --
901 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
902 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
903 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
904 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
905 ,p_output_dir IN VARCHAR2 := NULL
906 ,p_debug_filename IN VARCHAR2 := NULL
907 ,x_return_status OUT NOCOPY VARCHAR2 --
908 ,x_msg_count OUT NOCOPY NUMBER --
909 ,x_msg_data OUT NOCOPY VARCHAR2 --
910 ,p_document_id IN NUMBER -- Dom Document Id
911 ,p_document_revision_id IN NUMBER -- Dom Document Revision Id
912 ,p_change_id IN NUMBER -- Change Id
916 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
913 ,p_change_line_id IN NUMBER -- Change Line Id
914 ,p_party_ids IN FND_TABLE_OF_NUMBER -- Person's HZ_PARTIES.PARTY_ID Array
915 ,p_role_id IN NUMBER -- Role Id to be granted
917 )
918 IS
919 /*
920 l_change_line_id NUMBER ;
921
922 l_return_status VARCHAR2(1);
923 l_msg_count NUMBER;
924 l_msg_data VARCHAR2(2000);
925 l_pls_block VARCHAR2(4000);*/
926
927 BEGIN
928 --Commented this method as part of 12.2 DOM deprecation
929 x_return_status := FND_API.G_RET_STS_SUCCESS ;
930 x_msg_count := 0;
931 x_msg_data := NULL;
932
933 -- For Test/Debug
934 /* Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
935
936 IF g_debug_flag THEN
937 Write_Debug('ENG_DOCUMENT_UTIL.Grant_Document_Role Log');
938 Write_Debug('-----------------------------------------------------');
939 Write_Debug('Document Id : ' || TO_CHAR(p_document_id) );
940 Write_Debug('Document Revision Id : ' || TO_CHAR(p_document_revision_id) );
941 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
942 Write_Debug('Change Line Id : ' || TO_CHAR(p_change_line_id) );
943 Write_Debug('Role Id : ' || TO_CHAR(p_role_id) );
944 Write_Debug('API Caller : ' || p_api_caller);
945 Write_Debug('-----------------------------------------------------');
946 Write_Debug('Calling DOM_SECURITY_PUB.Grant_Document_Role');
947 END IF ;
948
949
950 l_return_status := FND_API.G_RET_STS_SUCCESS ;
951
952 l_change_line_id := p_change_line_id ;
953
954 IF l_change_line_id <= 0 THEN
955
956 l_change_line_id := NULL ;
957
958 END IF ;
959
960 IF g_debug_flag THEN
961 Write_Debug('Param: Change Line Id : ' || TO_CHAR(l_change_line_id) );
962 END IF ;
963
964
965 -- We always pass p_commit FND_API.G_FALSE
966 -- DOM API should NOT commit or rollback
967
968 /*
969 --
970 -- Comment out: somehow dynamic call does not work
971 -- I guess we should use DBMS_SQL to achieve this
972 --
973 -- l_pls_block := ' BEGIN '
974 -- || ' DOM_SECURITY_PUB.Grant_Document_Role'
975 -- || ' ( p_api_version => :1 '
976 -- || ' ,p_init_msg_list => :2 '
977 -- || ' ,p_commit => :3 '
978 -- || ' ,p_validation_level => :4 '
979 -- || ' ,x_return_status => :5 '
980 -- || ' ,x_msg_count => :6 '
981 -- || ' ,x_msg_data => :7 '
982 -- || ' ,p_object_name => ''DOM_DOCUMENT_REVISION'' '
983 -- || ' ,p_pk1_value => :8 ' -- Document_Id
984 -- || ' ,p_pk2_value => :9 ' -- Revision_Id
985 -- || ' ,p_pk3_value => :10 ' -- Change_Id
986 -- || ' ,p_pk4_value => :11 ' -- Change_Line_Id
987 -- || ' ,p_pk5_value => NULL '
988 -- || ' ,p_party_ids => :12 '
989 -- || ' ,p_role_id => :13 '
990 -- || ' ,p_api_caller => :14 '
991 -- || ' ); '
992 -- || ' END; ';
993 --
994 -- EXECUTE IMMEDIATE l_pls_block USING
995 -- p_api_version
996 -- ,p_init_msg_list
997 -- ,p_commit
998 -- ,p_validation_level
999 -- ,OUT l_return_status
1000 -- ,OUT l_msg_count
1001 -- ,OUT l_msg_data
1002 -- ,TO_CHAR(p_document_id)
1003 -- ,TO_CHAR(p_document_revision_id)
1004 -- ,TO_CHAR(p_change_id)
1005 -- ,TO_CHAR(l_change_line_id)
1006 -- ,p_party_ids
1007 -- ,p_role_id
1008 -- ,p_api_caller ;
1009 --
1010 */
1011
1012
1013 /* BEGIN
1014
1015 DOM_SECURITY_PUB.Grant_Document_Role
1016 ( p_api_version => p_api_version
1017 ,p_init_msg_list => p_init_msg_list
1018 ,p_commit => p_commit
1019 ,p_validation_level => p_validation_level
1020 ,x_return_status => l_return_status
1021 ,x_msg_count => l_msg_count
1022 ,x_msg_data => l_msg_data
1023 ,p_object_name => 'DOM_DOCUMENT_REVISION'
1024 ,p_pk1_value => TO_CHAR(p_document_id) -- Document_Id
1025 ,p_pk2_value => TO_CHAR(p_document_revision_id) -- Revision_Id
1026 ,p_pk3_value => TO_CHAR(p_change_id) -- Change_Id
1027 ,p_pk4_value => TO_CHAR(l_change_line_id) -- Change_Line_Id
1028 ,p_pk5_value => NULL
1029 ,p_party_ids => p_party_ids
1030 ,p_role_id => p_role_id
1031 ,p_api_caller => p_api_caller
1032 );
1033
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 IF g_debug_flag THEN
1037 Write_Debug('Unexpected Exception DOM_SECURITY_PUB.Grant_Document_Role: ' || SQLERRM);
1038 END IF ;
1039
1040 END ;
1041
1042 -- set Return vars
1046
1043 x_return_status := l_return_status;
1044 x_msg_count := l_msg_count;
1045 x_msg_data := l_msg_data;
1047
1048 -- Until DOM team implemented the logic indeed
1049 IF x_return_status IS NULL
1050 THEN
1051 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1052 END IF ;
1053
1054
1055 IF g_debug_flag THEN
1056 Write_Debug('After Calling DOM_SECURITY_PUB.Grant_Document_Role: ' || l_return_status);
1057 END IF ;
1058
1059 EXCEPTION
1060 WHEN PLSQL_COMPILE_ERROR THEN
1061 -- Assuming DOM is not installed
1062 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1063 x_msg_count := 0;
1064 x_msg_data := NULL;
1065
1066 IF g_debug_flag THEN
1067 Write_Debug('Exception Grant_Document_Role: PLSQL_COMPILE_ERROR ');
1068 END IF ;
1069
1070 WHEN OTHERS THEN
1071
1072 IF g_debug_flag THEN
1073 Write_Debug('Unexpected Exception Grant_Document_Role: ' || SQLERRM);
1074 END IF ;
1075
1076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1077
1078 FND_MSG_PUB.Add_Exc_Msg
1079 ( p_pkg_name => 'DOM_SECURITY_PUB' ,
1080 p_procedure_name => 'Grant_Document_Role',
1081 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
1082 );
1083
1084 FND_MSG_PUB.Count_And_Get
1085 ( p_count => x_msg_count ,
1086 p_data => x_msg_data
1087 );
1088
1089
1090 */
1091 END Grant_Document_Role ;
1092
1093
1094
1095 --
1096 -- Wrapper API to revoke Document Role to Document Revision
1097 --
1098 PROCEDURE Revoke_Document_Role
1099 (
1100 p_api_version IN NUMBER --
1101 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1102 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1103 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1104 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1105 ,p_output_dir IN VARCHAR2 := NULL
1106 ,p_debug_filename IN VARCHAR2 := NULL
1107 ,x_return_status OUT NOCOPY VARCHAR2 --
1108 ,x_msg_count OUT NOCOPY NUMBER --
1109 ,x_msg_data OUT NOCOPY VARCHAR2 --
1110 ,p_document_id IN NUMBER -- Dom Document Id
1111 ,p_document_revision_id IN NUMBER -- Dom Document Revision Id
1112 ,p_change_id IN NUMBER -- Change Id
1113 ,p_change_line_id IN NUMBER -- Change Line Id
1114 ,p_party_ids IN FND_TABLE_OF_NUMBER -- Person's HZ_PARTIES.PARTY_ID Array
1115 ,p_role_id IN NUMBER -- Role Id to be revoked. If NULL, Revoke all grants per given object info
1116 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
1117 )
1118 IS
1119 /* l_change_line_id NUMBER ;
1120
1121 l_return_status VARCHAR2(1);
1122 l_msg_count NUMBER;
1123 l_msg_data VARCHAR2(2000);
1124 l_pls_block VARCHAR2(4000);*/
1125
1126 BEGIN
1127 --Commented this method as part of 12.2 DOM deprecation
1128 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1129 x_msg_count := 0;
1130 x_msg_data := NULL;
1131 -- For Test/Debug
1132 /* Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1133
1134 IF g_debug_flag THEN
1135 Write_Debug('ENG_DOCUMENT_UTIL.Revoke_Document_Role Log');
1136 Write_Debug('-----------------------------------------------------');
1137 Write_Debug('Document Id : ' || TO_CHAR(p_document_id) );
1138 Write_Debug('Document Revision Id : ' || TO_CHAR(p_document_revision_id) );
1139 Write_Debug('Change Id : ' || TO_CHAR(p_change_id) );
1140 Write_Debug('Change Line Id : ' || TO_CHAR(p_change_line_id) );
1141 Write_Debug('Role Id : ' || TO_CHAR(p_role_id) );
1142 Write_Debug('API Caller : ' || p_api_caller);
1143 Write_Debug('-----------------------------------------------------');
1144 Write_Debug('Calling DOM_SECURITY_PUB.Revoke_Document_Role');
1145 END IF ;
1146
1147
1148 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1149
1150 l_change_line_id := p_change_line_id ;
1151
1152 IF l_change_line_id <= 0 THEN
1153
1154 l_change_line_id := NULL ;
1155
1156 END IF ;
1157
1158
1159 IF g_debug_flag THEN
1160 Write_Debug('Param: Change Line Id : ' || TO_CHAR(l_change_line_id) );
1161 END IF ;
1162
1163
1164
1165 -- We always pass p_commit FND_API.G_FALSE
1166 -- DOM API should NOT commit or rollback
1167 -- We always pass p_commit FND_API.G_FALSE
1168 -- DOM API should NOT commit or rollback
1169 -- No need to pass p_role_id
1170
1171 /*
1172 --
1173 -- Comment out: somehow dynamic call does not work
1174 -- I guess we should use DBMS_SQL to achieve this
1175 --
1176 -- l_pls_block := ' BEGIN '
1177 -- || ' DOM_SECURITY_PUB.Revoke_Document_Role'
1178 -- || ' ( p_api_version => :1 '
1179 -- || ' ,p_init_msg_list => :2 '
1180 -- || ' ,p_commit => :3 '
1181 -- || ' ,p_validation_level => :4 '
1182 -- || ' ,x_return_status => :5 '
1186 -- || ' ,p_pk1_value => :8 ' -- Document_Id
1183 -- || ' ,x_msg_count => :6 '
1184 -- || ' ,x_msg_data => :7 '
1185 -- || ' ,p_object_name => ''DOM_DOCUMENT_REVISION'' '
1187 -- || ' ,p_pk2_value => :9 ' -- Revision_Id
1188 -- || ' ,p_pk3_value => :10 ' -- Change_Id
1189 -- || ' ,p_pk4_value => :11 ' -- Change_Line_Id
1190 -- || ' ,p_pk5_value => NULL '
1191 -- || ' ,p_party_ids => :12 '
1192 -- || ' ,p_role_id => NULL '
1193 -- || ' ,p_api_caller => :13 '
1194 -- || ' ); '
1195 -- || ' END; ';
1196 --
1197 -- EXECUTE IMMEDIATE l_pls_block USING
1198 -- p_api_version
1199 -- ,p_init_msg_list
1200 -- ,p_commit
1201 -- ,p_validation_level
1202 -- ,OUT l_return_status
1203 -- ,OUT l_msg_count
1204 -- ,OUT l_msg_data
1205 -- ,TO_CHAR(p_document_id)
1206 -- ,TO_CHAR(p_document_revision_id)
1207 -- ,TO_CHAR(p_change_id)
1208 -- ,TO_CHAR(l_change_line_id)
1209 -- ,p_party_ids
1210 -- ,p_api_caller ;
1211 --
1212 */
1213
1214 /* BEGIN
1215
1216 DOM_SECURITY_PUB.Revoke_Document_Role
1217 ( p_api_version => p_api_version
1218 ,p_init_msg_list => p_init_msg_list
1219 ,p_commit => p_commit
1220 ,p_validation_level => p_validation_level
1221 ,x_return_status => l_return_status
1222 ,x_msg_count => l_msg_count
1223 ,x_msg_data => l_msg_data
1224 ,p_object_name => 'DOM_DOCUMENT_REVISION'
1225 ,p_pk1_value => TO_CHAR(p_document_id) -- Document_Id
1226 ,p_pk2_value => TO_CHAR(p_document_revision_id) -- Revision_Id
1227 ,p_pk3_value => TO_CHAR(p_change_id) -- Change_Id
1228 ,p_pk4_value => TO_CHAR(l_change_line_id) -- Change_Line_Id
1229 ,p_pk5_value => NULL
1230 ,p_party_ids => p_party_ids
1231 ,p_role_id => NULL
1232 ,p_api_caller => p_api_caller
1233 );
1234
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 IF g_debug_flag THEN
1238 Write_Debug('Unexpected Exception DOM_SECURITY_PUB.Revoke_Document_Role: ' || SQLERRM);
1239 END IF ;
1240
1241 END ;
1242
1243
1244 -- set Return vars
1245 x_return_status := l_return_status;
1246 x_msg_count := l_msg_count;
1247 x_msg_data := l_msg_data;
1248
1249
1250 -- Until DOM team implemented the logic indeed
1251 IF x_return_status IS NULL
1252 THEN
1253 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1254 END IF ;
1255
1256
1257 IF g_debug_flag THEN
1258 Write_Debug('After Calling DOM_SECURITY_PUB.Revoke_Document_Role: ' || l_return_status);
1259 END IF ;
1260
1261
1262
1263 EXCEPTION
1264 WHEN PLSQL_COMPILE_ERROR THEN
1265 -- Assuming DOM is not installed
1266 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1267 x_msg_count := 0;
1268 x_msg_data := NULL;
1269
1270 IF g_debug_flag THEN
1271 Write_Debug('Exception Revoke_Document_Role: PLSQL_COMPILE_ERROR ');
1272 END IF ;
1273
1274 WHEN OTHERS THEN
1275
1276 IF g_debug_flag THEN
1277 Write_Debug('Unexpected Exception Revoke_Document_Role: ' || SQLERRM);
1278 END IF ;
1279
1280
1281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1282
1283 FND_MSG_PUB.Add_Exc_Msg
1284 ( p_pkg_name => 'DOM_SECURITY_PUB' ,
1285 p_procedure_name => 'Revoke_Document_Role',
1286 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
1287 );
1288
1289 FND_MSG_PUB.Count_And_Get
1290 ( p_count => x_msg_count ,
1291 p_data => x_msg_data
1292 );
1293 */
1294 END Revoke_Document_Role ;
1295
1296
1297 --
1298 -- Wrapper API to grant Document Role to Document Revision
1299 --
1300 PROCEDURE Grant_Attachments_OCSRole
1301 (
1302 p_api_version IN NUMBER --
1303 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1304 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1305 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1306 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1307 ,p_output_dir IN VARCHAR2 := NULL
1308 ,p_debug_filename IN VARCHAR2 := NULL
1309 ,x_return_status OUT NOCOPY VARCHAR2 --
1310 ,x_msg_count OUT NOCOPY NUMBER --
1311 ,x_msg_data OUT NOCOPY VARCHAR2 --
1312 ,p_entity_name IN VARCHAR2
1313 ,p_pk1value IN VARCHAR2
1314 ,p_pk2value IN VARCHAR2
1315 ,p_pk3value IN VARCHAR2
1316 ,p_pk4value IN VARCHAR2
1317 ,p_pk5value IN VARCHAR2
1318 ,p_party_ids IN FND_TABLE_OF_NUMBER -- Person's HZ_PARTIES.PARTY_ID Array
1322 ,p_repository_id_tbl IN FND_TABLE_OF_NUMBER := null
1319 ,p_ocs_role IN VARCHAR2 -- OCS File Role to be granted
1320 ,p_source_media_id_tbl IN FND_TABLE_OF_NUMBER := null
1321 ,p_attachment_id_tbl IN FND_TABLE_OF_NUMBER := null
1323 ,p_submitted_by IN NUMBER := null
1324 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
1325 )
1326 IS
1327
1328 l_return_status VARCHAR2(1);
1329 l_msg_count NUMBER;
1330 l_msg_data VARCHAR2(2000);
1331 l_pls_block VARCHAR2(4000);
1332
1333 BEGIN
1334
1335 -- For Test/Debug
1336 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1337
1338 IF g_debug_flag THEN
1339 Write_Debug('ENG_DOCUMENT_UTIL.Grant_Attachments_OCSRole Log');
1340 Write_Debug('-----------------------------------------------------');
1341 Write_Debug('Entity Name : ' || p_entity_name);
1342 Write_Debug('PK1 Value : ' || p_pk1value);
1343 Write_Debug('PK2 Value : ' || p_pk2value);
1344 Write_Debug('PK3 Value : ' || p_pk3value);
1345 Write_Debug('PK4 Value : ' || p_pk4value);
1346 Write_Debug('PK5 Value : ' || p_pk5value);
1347 Write_Debug('OCS ROle : ' || p_ocs_role);
1348 Write_Debug('API Caller : ' || p_api_caller);
1349 Write_Debug('-----------------------------------------------------');
1350 Write_Debug('Calling DOM_SECURITY_PUB.Grant_Attachments_OCSRole');
1351 END IF ;
1352
1353
1354 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1355
1356 -- We always pass p_commit FND_API.G_FALSE
1357 -- DOM API should NOT commit or rollback
1358
1359 /*
1360 --
1361 -- Comment out: somehow dynamic call does not work
1362 -- I guess we should use DBMS_SQL to achieve this
1363 --
1364 -- l_pls_block := ' BEGIN '
1365 -- || ' DOM_SECURITY_PUB.Grant_Attachments_OCSRole'
1366 -- || ' ( p_api_version => :1 '
1367 -- || ' ,p_init_msg_list => :2 '
1368 -- || ' ,p_commit => :3 '
1369 -- || ' ,p_validation_level => :4 '
1370 -- || ' ,x_return_status => :5 '
1371 -- || ' ,x_msg_count => :6 '
1372 -- || ' ,x_msg_data => :7 '
1373 -- || ' ,p_entity_name => :8 '
1374 -- || ' ,p_pk1_value => :9 ' -- Document_Id
1375 -- || ' ,p_pk2_value => :10 ' -- Revision_Id
1376 -- || ' ,p_pk3_value => :11 ' -- Change_Id
1377 -- || ' ,p_pk4_value => :12 ' -- Change_Line_Id
1378 -- || ' ,p_pk5_value => :13 '
1379 -- || ' ,p_party_ids => :14 '
1380 -- || ' ,p_ocs_role => :15 '
1381 -- || ' ,p_api_caller => :16 '
1382 -- || ' ); '
1383 -- || ' END; ';
1384 --
1385 -- EXECUTE IMMEDIATE l_pls_block USING
1386 -- p_api_version
1387 -- ,p_init_msg_list
1388 -- ,p_commit
1389 -- ,p_validation_level
1390 -- ,OUT l_return_status
1391 -- ,OUT l_msg_count
1392 -- ,OUT l_msg_data
1393 -- ,p_entity_name
1394 -- ,p_pk1value
1395 -- ,p_pk2value
1396 -- ,p_pk3value
1397 -- ,p_pk4value
1398 -- ,p_pk5value
1399 -- ,p_party_ids
1400 -- ,p_ocs_role
1401 -- ,p_api_caller ;
1402 --
1403 */
1404
1405 BEGIN
1406
1407
1408 DOM_SECURITY_PUB.Grant_Attachments_OCSRole
1409 ( p_api_version => p_api_version
1410 ,p_init_msg_list => p_init_msg_list
1411 ,p_commit => p_commit
1412 ,p_validation_level => p_validation_level
1413 ,x_return_status => l_return_status
1414 ,x_msg_count => l_msg_count
1415 ,x_msg_data => l_msg_data
1416 ,p_entity_name => p_entity_name
1417 ,p_pk1_value => p_pk1value
1418 ,p_pk2_value => p_pk2value
1419 ,p_pk3_value => p_pk3value
1420 ,p_pk4_value => p_pk4value
1421 ,p_pk5_value => p_pk5value
1422 ,p_party_ids => p_party_ids
1423 ,p_ocs_role => p_ocs_role
1424 ,p_api_caller => p_api_caller
1425 );
1426
1427 IF x_return_status IS NULL
1428 THEN
1429 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1430 END IF ;
1431
1432 if x_return_status = FND_API.G_RET_STS_SUCCESS
1433 AND p_source_media_id_tbl is not null and p_source_media_id_tbl.count > 0
1434 AND p_repository_id_tbl is not null and p_repository_id_tbl.count>0
1435 then
1436 for ind in p_source_media_id_tbl.first .. p_source_media_id_tbl.last
1437 loop
1438 DOM_SECURITY_PUB.Grant_Attachment_Access
1439 (
1440 p_api_version => p_api_version,
1441 p_attached_document_id => p_attachment_id_tbl(ind),
1442 p_source_media_id => p_source_media_id_tbl(ind),
1443 p_repository_id => p_repository_id_tbl(ind),
1444 p_ocs_role => p_ocs_role,
1448 x_msg_data => l_msg_data ,
1445 p_party_ids => p_party_ids,
1446 p_submitted_by => p_submitted_by,
1447 x_msg_count => l_msg_count,
1449 x_return_status => l_return_status
1450 );
1451 END LOOP;
1452
1453 END IF;
1454
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 IF g_debug_flag THEN
1458 Write_Debug('Unexpected Exception Grant_Attachments_OCSRole: ' || SQLERRM);
1459 END IF ;
1460
1461 END ;
1462
1463
1464 -- set Return vars
1465 x_return_status := l_return_status;
1466 x_msg_count := l_msg_count;
1467 x_msg_data := l_msg_data;
1468
1469
1470 -- Until DOM team implemented the logic indeed
1471 IF x_return_status IS NULL
1472 THEN
1473 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1474 END IF ;
1475
1476
1477 IF g_debug_flag THEN
1478 Write_Debug('After Calling DOM_SECURITY_PUB.Grant_Attachments_OCSRole: ' || l_return_status);
1479 END IF ;
1480
1481 EXCEPTION
1482 WHEN PLSQL_COMPILE_ERROR THEN
1483 -- Assuming DOM is not installed
1484 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1485 x_msg_count := 0;
1486 x_msg_data := NULL;
1487
1488
1489 IF g_debug_flag THEN
1490 Write_Debug('Exception Grant_Attachments_OCSRole: PLSQL_COMPILE_ERROR ');
1491 END IF ;
1492
1493 WHEN OTHERS THEN
1494 IF g_debug_flag THEN
1495 Write_Debug('Unexpected Exception Grant_Attachments_OCSRole: ' || SQLERRM);
1496 END IF ;
1497
1498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1499
1500 FND_MSG_PUB.Add_Exc_Msg
1501 ( p_pkg_name => 'DOM_SECURITY_PUB' ,
1502 p_procedure_name => 'Grant_Attachments_OCSRole',
1503 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
1504 );
1505
1506 FND_MSG_PUB.Count_And_Get
1507 ( p_count => x_msg_count ,
1508 p_data => x_msg_data
1509 );
1510
1511
1512 END Grant_Attachments_OCSRole ;
1513
1514
1515
1516 --
1517 -- Wrapper API to revoke Document Role to Document Revision
1518 --
1519 PROCEDURE Revoke_Attachments_OCSRole
1520 (
1521 p_api_version IN NUMBER --
1522 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1523 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1524 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1525 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1526 ,p_output_dir IN VARCHAR2 := NULL
1527 ,p_debug_filename IN VARCHAR2 := NULL
1528 ,x_return_status OUT NOCOPY VARCHAR2 --
1529 ,x_msg_count OUT NOCOPY NUMBER --
1530 ,x_msg_data OUT NOCOPY VARCHAR2 --
1531 ,p_entity_name IN VARCHAR2
1532 ,p_pk1value IN VARCHAR2
1533 ,p_pk2value IN VARCHAR2
1534 ,p_pk3value IN VARCHAR2
1535 ,p_pk4value IN VARCHAR2
1536 ,p_pk5value IN VARCHAR2
1537 ,p_party_ids IN FND_TABLE_OF_NUMBER -- Person's HZ_PARTIES.PARTY_ID Array
1538 ,p_ocs_role IN VARCHAR2 -- OCS File Role to be revoked. If NULL, Revoke all grants per given entity info
1539 ,p_api_caller IN VARCHAR2 := NULL -- Optionnal for future use
1540 )
1541 IS
1542
1543 l_return_status VARCHAR2(1);
1544 l_msg_count NUMBER;
1545 l_msg_data VARCHAR2(2000);
1546 l_pls_block VARCHAR2(4000);
1547
1548 BEGIN
1549
1550 -- For Test/Debug
1551 Check_And_Open_Debug_Session(p_debug, p_output_dir, p_debug_filename) ;
1552
1553 IF g_debug_flag THEN
1554 Write_Debug('ENG_DOCUMENT_UTIL.Revoke_Attachments_OCSRole Log');
1555 Write_Debug('-----------------------------------------------------');
1556 Write_Debug('Entity Name : ' || p_entity_name);
1557 Write_Debug('PK1 Value : ' || p_pk1value);
1558 Write_Debug('PK2 Value : ' || p_pk2value);
1559 Write_Debug('PK3 Value : ' || p_pk3value);
1560 Write_Debug('PK4 Value : ' || p_pk4value);
1561 Write_Debug('PK5 Value : ' || p_pk5value);
1562 Write_Debug('API Caller : ' || p_api_caller);
1563 Write_Debug('OCS ROle : ' || p_ocs_role);
1564 Write_Debug('-----------------------------------------------------');
1565 Write_Debug('Calling DOM_SECURITY_PUB.Revoke_Attachments_OCSRole');
1566 END IF ;
1567
1568 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1569
1570
1571
1572 -- We always pass p_commit FND_API.G_FALSE
1573 -- DOM API should NOT commit or rollback
1574 -- No need to pass p_ocs_role
1575
1576 /*
1577 --
1578 -- Comment out: somehow dynamic call does not work
1579 -- I guess we should use DBMS_SQL to achieve this
1580 --
1581 --
1582 -- l_pls_block := ' BEGIN '
1583 -- || ' DOM_SECURITY_PUB.Revoke_Attachments_OCSRole'
1584 -- || ' ( p_api_version => :1 '
1585 -- || ' ,p_init_msg_list => :2 '
1586 -- || ' ,p_commit => :3 '
1590 -- || ' ,x_msg_data => :7 '
1587 -- || ' ,p_validation_level => :4 '
1588 -- || ' ,x_return_status => :5 '
1589 -- || ' ,x_msg_count => :6 '
1591 -- || ' ,p_entity_name => :8 '
1592 -- || ' ,p_pk1_value => :9 '
1593 -- || ' ,p_pk2_value => :10 '
1594 -- || ' ,p_pk3_value => :11 '
1595 -- || ' ,p_pk4_value => :12 '
1596 -- || ' ,p_pk5_value => :13 '
1597 -- || ' ,p_party_ids => :14 '
1598 -- || ' ,p_ocs_role => NULL '
1599 -- || ' ,p_api_caller => :15 '
1600 -- || ' ); '
1601 -- || ' END; ';
1602 --
1603 -- EXECUTE IMMEDIATE l_pls_block USING
1604 -- p_api_version
1605 -- ,p_init_msg_list
1606 -- ,p_commit
1607 -- ,p_validation_level
1608 -- ,OUT l_return_status
1609 -- ,OUT l_msg_count
1610 -- ,OUT l_msg_data
1611 -- ,p_entity_name
1612 -- ,p_pk1value
1613 -- ,p_pk2value
1614 -- ,p_pk3value
1615 -- ,p_pk4value
1616 -- ,p_pk5value
1617 -- ,p_party_ids
1618 -- ,p_api_caller ;
1619 --
1620 */
1621
1622 BEGIN
1623
1624
1625 DOM_SECURITY_PUB.Revoke_Attachments_OCSRole
1626 ( p_api_version => p_api_version
1627 ,p_init_msg_list => p_init_msg_list
1628 ,p_commit => p_commit
1629 ,p_validation_level => p_validation_level
1630 ,x_return_status => l_return_status
1631 ,x_msg_count => l_msg_count
1632 ,x_msg_data => l_msg_data
1633 ,p_entity_name => p_entity_name
1634 ,p_pk1_value => p_pk1value
1635 ,p_pk2_value => p_pk2value
1636 ,p_pk3_value => p_pk3value
1637 ,p_pk4_value => p_pk4value
1638 ,p_pk5_value => p_pk5value
1639 ,p_party_ids => p_party_ids
1640 ,p_ocs_role => NULL
1641 ,p_api_caller => p_api_caller
1642 );
1643
1644 EXCEPTION
1645 WHEN OTHERS THEN
1646 IF g_debug_flag THEN
1647 Write_Debug('Unexpected Exception Revoke_Attachments_OCSRole: ' || SQLERRM);
1648 END IF ;
1649
1650 END ;
1651
1652
1653 -- set Return vars
1654 x_return_status := l_return_status;
1655 x_msg_count := l_msg_count;
1656 x_msg_data := l_msg_data;
1657
1658
1659 -- Until DOM team implemented the logic indeed
1660 IF x_return_status IS NULL
1661 THEN
1662 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1663 END IF ;
1664
1665
1666
1667 IF g_debug_flag THEN
1668 Write_Debug('After Calling DOM_SECURITY_PUB.Revoke_Attachments_OCSRole: ' || l_return_status);
1669 END IF ;
1670
1671
1672 EXCEPTION
1673 WHEN PLSQL_COMPILE_ERROR THEN
1674 -- Assuming DOM is not installed
1675 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1676 x_msg_count := 0;
1677 x_msg_data := NULL;
1678
1679
1680 IF g_debug_flag THEN
1681 Write_Debug('Exception Grant_Attachments_OCSRole: PLSQL_COMPILE_ERROR ');
1682 END IF ;
1683
1684 WHEN OTHERS THEN
1685
1686 IF g_debug_flag THEN
1687 Write_Debug('Unexpected Exception Grant_Attachments_OCSRole: ' || SQLERRM);
1688 END IF ;
1689
1690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1691
1692 FND_MSG_PUB.Add_Exc_Msg
1693 ( p_pkg_name => 'DOM_SECURITY_PUB' ,
1694 p_procedure_name => 'Revoke_Attachments_OCSRole',
1695 p_error_text => Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240)
1696 );
1697
1698 FND_MSG_PUB.Count_And_Get
1699 ( p_count => x_msg_count ,
1700 p_data => x_msg_data
1701 );
1702
1703
1704 END Revoke_Attachments_OCSRole ;
1705
1706
1707
1708
1709 END ENG_DOCUMENT_UTIL;