[Home] [Help]
PACKAGE BODY: APPS.DOM_DOCUMENT_UTIL
Source
1 package body DOM_DOCUMENT_UTIL as
2 /*$Header: DOMPDUTB.pls 120.12.12010000.2 2009/01/23 22:03:40 ksuleman ship $ */
3 -- Global constant holding the package name
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DOM_DOCUMENT_UTIL' ;
6
7
8 /********************************************************************
9 * API Type : Local APIs
10 * Purpose : Those APIs are Local
11 *********************************************************************/
12
13 PROCEDURE Get_Document_LC_Info( p_lc_tracking_id IN NUMBER
14 , p_route_id IN NUMBER
15 , x_document_id OUT NOCOPY NUMBER
16 , x_document_revision_id OUT NOCOPY NUMBER
17 , x_checkout_status OUT NOCOPY VARCHAR2
18 , x_lc_sequence_number OUT NOCOPY NUMBER
19 , x_lc_phase_code OUT NOCOPY NUMBER
20 , x_lc_phase_type OUT NOCOPY NUMBER
21 , x_lc_phase_display_name OUT NOCOPY VARCHAR2
22 )
23 IS
24
25 CURSOR c_doc_lc (c_lc_tracking_id NUMBER
26 ,c_route_id NUMBER)
27 IS
28 SELECT rev.document_id
29 , rev.revision_id
30 , rev.lifecycle_tracking_id
31 , rev.lifecycle_phase_id
32 , lifecycle.sequence_number
33 , rev.checkout_status
34 , lifecycle.status_code
35 , stat.status_type
36 , lifecycle.change_wf_route_id
37 , stat.status_name
38 FROM eng_change_statuses_vl stat
39 , eng_lifecycle_statuses lifecycle
40 , dom_document_revisions rev
41 WHERE lifecycle.status_code = stat.status_code
42 AND lifecycle.change_wf_route_id = c_route_id
43 AND lifecycle.entity_id1 = rev.lifecycle_tracking_id
44 AND lifecycle.entity_name = 'ENG_CHANGE'
45 AND lifecycle.active_flag = 'Y'
46 AND rev.lifecycle_tracking_id = c_lc_tracking_id ;
47
48
49 BEGIN
50
51 FOR l_rec IN c_doc_lc ( c_lc_tracking_id => p_lc_tracking_id
52 , c_route_id => p_route_id )
53 LOOP
54
55 x_document_id := l_rec.document_id ;
56 x_document_revision_id := l_rec.revision_id ;
57 x_checkout_status := l_rec.checkout_status ;
58 x_lc_sequence_number := l_rec.sequence_number ;
59 x_lc_phase_code := l_rec.status_code ;
60 x_lc_phase_type := l_rec.status_type ;
61 x_lc_phase_display_name := l_rec.status_name ;
62
63 END LOOP ;
64
65
66 END Get_Document_LC_Info ;
67
68
69
70
71 /********************************************************************
72 * API Type : Private APIs
73 * Purpose : Those APIs are private
74 *********************************************************************/
75
76
77 Procedure Change_Doc_LC_Phase
78 ( p_api_version IN NUMBER --
79 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
80 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
81 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
82 ,p_change_id IN NUMBER
83 ,p_lc_phase_code IN NUMBER
84 ,p_action_type IN VARCHAR2-- 'PROMOTE' or 'DEMOTE'
85 ,p_api_caller IN VARCHAR2
86 ,x_return_status OUT NOCOPY VARCHAR2 --
87 ,x_msg_count OUT NOCOPY NUMBER --
88 ,x_msg_data OUT NOCOPY VARCHAR2
89 )
90 IS
91
92 l_api_name CONSTANT VARCHAR2(50) := 'Change_Doc_LC_Phase';
93
94 l_return_status VARCHAR2(1);
95 l_msg_count NUMBER;
96 l_msg_data VARCHAR2(2000);
97
98 BEGIN
99
100
101
102
103 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
104 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Begin ' || l_api_name);
105 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
106 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Change Id : ' || TO_CHAR(p_change_id) );
107 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
108 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Action Type : ' || p_action_type);
109 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'API Caller : ' || p_api_caller);
110 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
111 END IF ;
112
113
114
115 UPDATE dom_document_revisions SET lifecycle_phase_id = p_lc_phase_code
116 WHERE lifecycle_tracking_id = p_change_id;
117
118 IF p_commit = FND_API.G_TRUE THEN
119 commit;
120 END IF;
121
122 -- Initialize API return status to success
123 x_return_status := FND_API.G_RET_STS_SUCCESS;
124 -- Standard ending code ------------------------------------------------
125
126 FND_MSG_PUB.Count_And_Get
127 ( p_count => x_msg_count,
128 p_data => x_msg_data );
129
130
131
132
133 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
134 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'End ' || l_api_name);
135 END IF ;
136
137
138
139 EXCEPTION
140
141 WHEN FND_API.G_EXC_ERROR THEN
142 x_return_status := FND_API.G_RET_STS_ERROR;
143 FND_MSG_PUB.Count_And_Get
144 ( p_count => x_msg_count
145 ,p_data => x_msg_data );
146
147 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149 FND_MSG_PUB.Count_And_Get
150 ( p_count => x_msg_count
151 ,p_data => x_msg_data );
152
153 WHEN OTHERS THEN
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
155 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
156 THEN
157 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
158 END IF;
159 FND_MSG_PUB.Count_And_Get
160 ( p_count => x_msg_count
161 ,p_data => x_msg_data );
162
163 END Change_Doc_LC_Phase;
164
165
166 Procedure Update_Approval_Status
167 ( p_api_version IN NUMBER --
168 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
169 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
170 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
171 ,p_change_id IN NUMBER
172 ,p_approval_status IN NUMBER
173 ,p_wf_route_status IN VARCHAR2
174 ,p_api_caller IN VARCHAR2
175 ,x_return_status OUT NOCOPY VARCHAR2 --
176 ,x_msg_count OUT NOCOPY NUMBER --
177 ,x_msg_data OUT NOCOPY VARCHAR2
178 )
179 IS
180
181 l_api_name CONSTANT VARCHAR2(50) := 'Update_Approval_Status';
182
183 l_return_status VARCHAR2(1);
184 l_msg_count NUMBER;
185 l_msg_data VARCHAR2(2000);
186 l_version_id NUMBER;
187 l_status_code NUMBER;
188 l_status_type NUMBER;
189 l_seq_num NUMBER;
190 l_approval_status VARCHAR2(10);
191 l_row_count NUMBER;
192
193
194 BEGIN
195
196 /*
197 LOOKUP_CODE MEANING DOM STATUS
198 --------------------------------------- ----------------------------------------
199 1 Not submitted for approval N_SFA
200 2 Ready to approve SFA
201 3 Approval requested SFA
202 4 Rejected RJD
203 5 Approved A
204 6 No approval needed A
205 7 Processing error SFA
206 8 Time out SFA
207 */
208
209
210
211
212 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
213 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Begin ' || l_api_name);
214 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
215 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Change Id : ' || TO_CHAR(p_change_id) );
216 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Approval Status : ' || TO_CHAR(p_approval_status) );
217 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'WF Route Status : ' || p_wf_route_status);
218 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'API Caller : ' || p_api_caller);
219 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
220 END IF ;
221
222
223 -- Initialize API return status to success
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226
227 SELECT rev.lifecycle_phase_id, status_type, life.sequence_number
228 INTO l_status_code, l_status_type, l_seq_num
229 FROM eng_change_statuses stat, dom_document_revisions rev, eng_lifecycle_statuses life
230 WHERE
231 life.status_code = stat.status_code AND
232 stat.status_code = rev.lifecycle_phase_id AND
233 life.entity_id1 = rev.lifecycle_tracking_id AND
234 life.active_flag = 'Y' AND
235 rev.lifecycle_tracking_id = p_change_id;
236
237
238 SELECT status_code INTO l_approval_status
239 FROM dom_doc_rev_versions
240 WHERE version_id = (
241 SELECT max(version_id)
242 FROM dom_doc_rev_versions
243 WHERE revision_id = (SELECT revision_id FROM dom_document_revisions WHERE lifecycle_tracking_id = p_change_id));
244
245
246
247 IF ( l_status_type = 1 ) THEN
248 l_approval_status := 'C';
249 ELSIF ( l_status_type = 12 ) THEN
250 BEGIN
251 IF ( p_wf_route_status = ENG_WORKFLOW_UTIL.G_RT_NOT_STARTED ) THEN
252 l_approval_status := 'SFR';
253 ELSIF ( p_wf_route_status = ENG_WORKFLOW_UTIL.G_RT_IN_PROGRESS ) THEN
254 l_approval_status := 'SFR';
255 ELSIF (p_wf_route_status = ENG_WORKFLOW_UTIL.G_RT_COMPLETED) THEN
256
257 SELECT Count(*)
258 INTO l_row_count
259 FROM eng_lifecycle_statuses life,eng_change_statuses_vl stat
260 WHERE
261 life.status_code = stat.status_code and
262 life.entity_id1 = p_change_id AND
263 stat.status_type = l_status_type AND
264 life.sequence_number > l_seq_num;
265
266 IF l_row_count > 0 THEN
267 l_approval_status := 'SFR';
268 ELSE
269 l_approval_status := 'RVD';
270 END IF;
271 END IF;
272 END;
273 ELSIF ( l_status_type = 8 ) THEN
274 BEGIN
275 IF (p_approval_status = 1) THEN
276 l_approval_status := 'N_SFA';
277 ELSIF (p_approval_status = 2) THEN
278 l_approval_status := 'SFA';
279 ELSIF (p_approval_status = 3) THEN
280 l_approval_status := 'SFA';
281 ELSIF (p_approval_status = 4) THEN
282 l_approval_status := 'RJD';
283 ELSIF (p_approval_status = 5) THEN
284
285 SELECT Count(*)
286 INTO l_row_count
287 FROM eng_lifecycle_statuses life,eng_change_statuses_vl stat
288 WHERE
289 life.status_code = stat.status_code and
290 life.entity_id1 = p_change_id AND
291 stat.status_type = l_status_type AND
292 life.sequence_number > l_seq_num;
293
294 IF (l_row_count > 0) THEN
295 l_approval_status := 'SFA';
296 ELSE
297 l_approval_status := 'A';
298 END IF;
299 END IF;
300 END;
301 END IF;
302
303 UPDATE dom_doc_rev_versions SET STATUS_CODE = l_approval_status
304 WHERE version_id =
305 (SELECT version_id FROM dom_doc_rev_versions
306 WHERE creation_date = (
307 SELECT Max(ver.creation_date) FROM dom_doc_rev_versions ver, dom_document_revisions rev
308 WHERE ver.revision_id = rev.revision_id
309 AND rev.revision_id = ver.revision_id
310 AND rev.lifecycle_tracking_id = p_change_id));
311
312 IF p_commit = FND_API.G_TRUE THEN
313 commit;
314 END IF;
315
316
317
318
319 -- Standard ending code ------------------------------------------------
320
321 FND_MSG_PUB.Count_And_Get
322 ( p_count => x_msg_count,
323 p_data => x_msg_data );
324
325
326 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
327 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'End ' || l_api_name);
328 END IF ;
329
330
331
332 EXCEPTION
333
334 WHEN FND_API.G_EXC_ERROR THEN
335 x_return_status := FND_API.G_RET_STS_ERROR;
336 FND_MSG_PUB.Count_And_Get
337 ( p_count => x_msg_count
338 ,p_data => x_msg_data );
339
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 FND_MSG_PUB.Count_And_Get
343 ( p_count => x_msg_count
344 ,p_data => x_msg_data );
345
346 WHEN OTHERS THEN
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
348 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
349 THEN
350 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
351 END IF;
352 FND_MSG_PUB.Count_And_Get
353 ( p_count => x_msg_count
354 ,p_data => x_msg_data );
355 END Update_Approval_Status;
356
357
358
359
360 --
361 -- Start DOC LC Phase Workflow to integrate CM Worklfow
362 -- This API is called when starting Doc LC Phase Workflow
363 -- We can put validation logic here
364 --
365 Procedure Start_Doc_LC_Phase_WF
366 ( p_api_version IN NUMBER --
367 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
368 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
369 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
370 ,x_return_status OUT NOCOPY VARCHAR2 --
371 ,x_msg_count OUT NOCOPY NUMBER --
372 ,x_msg_data OUT NOCOPY VARCHAR2
373 ,p_change_id IN NUMBER
374 ,p_route_id IN NUMBER
375 ,p_lc_phase_code IN NUMBER := NULL
376 ,p_api_caller IN VARCHAR2
377 )
378 IS
379
380 l_api_name CONSTANT VARCHAR2(30) := 'Start_Doc_LC_Phase_WF';
381 l_api_version CONSTANT NUMBER := 1.0;
382
383 l_document_id NUMBER ;
384 l_document_revision_id NUMBER ;
385 l_checkout_status DOM_DOCUMENT_REVISIONS.CHECKOUT_STATUS%TYPE ;
386 l_lc_sequence_number NUMBER ;
387 l_lc_phase_code NUMBER ;
388 l_lc_phase_type NUMBER ;
392 -- Standard Start of API savepoint
389 l_lc_phase_display_name VARCHAR2(80) ;
390
391 BEGIN
393 SAVEPOINT l_api_name;
394
395 -- Standard call to check for call compatibility.
396 IF NOT FND_API.Compatible_API_Call( l_api_version
397 , p_api_version
398 , l_api_name
399 , G_PKG_NAME )
400 THEN
401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402 END IF;
403
404 -- Initialize message list if p_init_msg_list is set to TRUE.
405 IF FND_API.to_Boolean( p_init_msg_list ) THEN
406 FND_MSG_PUB.initialize;
407 END IF ;
408
409 -- Initialize API return status to success
410 x_return_status := FND_API.G_RET_STS_SUCCESS;
411
412
413 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
414 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Begin ' || l_api_name);
415 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
416 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Change Id : ' || TO_CHAR(p_change_id) );
417 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Route Id : ' || TO_CHAR(p_route_id) );
418 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
419 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'API Caller : ' || p_api_caller);
420 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
421 END IF ;
422
423
424 -----------------------------------------------------------------
425 -- API body
426 -----------------------------------------------------------------
427 -- 1. Get Document Lifecycle Info
428 Get_Document_LC_Info( p_lc_tracking_id => p_change_id
429 , p_route_id => p_route_id
430 , x_document_id => l_document_id
431 , x_document_revision_id => l_document_revision_id
432 , x_checkout_status => l_checkout_status
433 , x_lc_sequence_number => l_lc_sequence_number
434 , x_lc_phase_code => l_lc_phase_code
435 , x_lc_phase_type => l_lc_phase_type
436 , x_lc_phase_display_name => l_lc_phase_display_name
437 ) ;
438
439
440 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
441 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Got Document LC Info' );
442 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
443 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Document Id : ' || TO_CHAR(l_document_id) );
444 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Document Rev Id : ' || TO_CHAR(l_document_revision_id) );
445 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Check Out Status : ' || l_checkout_status);
446 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'LC Phase Seq Num : ' || TO_CHAR(l_lc_sequence_number) );
447 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'LC Phase Code : ' || TO_CHAR(l_lc_phase_code) );
448 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'LC Phase Type : ' || TO_CHAR(l_lc_phase_type) );
449 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'LC Phase Display : ' || l_lc_phase_display_name );
450 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
451 END IF ;
452
453
454 IF l_checkout_status IS NOT NULL
455 THEN
456 -- Document LC Workflow cannot be started
457 -- if the Document is being checked out
458 IF l_lc_phase_type IN (G_PHASE_TYPE_REVIEW
459 , G_PHASE_TYPE_APPROVAL
460 , G_PHASE_TYPE_RELEASE
461 , G_PHASE_TYPE_ARCHIVE
462 )
463 THEN
464
465 IF DOM_LOG.CHECK_LOG_LEVEL THEN
466 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Document LC Workflow cannot be started in this status') ;
467 END IF ;
468
469 x_return_status := FND_API.G_RET_STS_ERROR ;
470 FND_MESSAGE.SET_NAME('DOM', 'DOM_LC_WF_CANNOT_START') ;
471 FND_MESSAGE.SET_TOKEN('LC_PHASE', l_lc_phase_display_name ) ;
472 FND_MSG_PUB.Add ;
473
474 END IF ;
475
476 END IF ;
477
478
479 -----------------------------------------------------------------
480 -- End of API body
481 -----------------------------------------------------------------
482
483
484 -- Standard check of p_commit.
485 IF FND_API.To_Boolean( p_commit ) THEN
486
487 IF DOM_LOG.CHECK_LOG_LEVEL THEN
488 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Do Commit.') ;
489 END IF ;
490
491 COMMIT WORK;
492 END IF;
493
494 -- Standard call to get message count and if count is 1, get message info.
495 FND_MSG_PUB.Count_And_Get
496 ( p_count => x_msg_count
497 , p_data => x_msg_data
498 );
499
500 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
501 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'end ' || l_api_name);
502 END IF ;
503
504
505 EXCEPTION
506 WHEN FND_API.G_EXC_ERROR THEN
507 -- Standard check of p_commit.
511 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback . . .') ;
508 IF FND_API.To_Boolean( p_commit ) THEN
509
510 IF DOM_LOG.CHECK_LOG_LEVEL THEN
512 END IF ;
513 ROLLBACK TO l_api_name ;
514 END IF;
515
516 x_return_status := FND_API.G_RET_STS_ERROR ;
517
518 FND_MSG_PUB.Count_And_Get
519 ( p_count => x_msg_count
520 , p_data => x_msg_data
521 );
522
523 IF DOM_LOG.CHECK_LOG_LEVEL THEN
524 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'RollBack and Finish with Error.') ;
525 END IF ;
526
527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
528 IF FND_API.To_Boolean( p_commit ) THEN
529
530 IF DOM_LOG.CHECK_LOG_LEVEL THEN
531 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback . . .') ;
532 END IF ;
533 ROLLBACK TO l_api_name ;
534 END IF;
535
536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
537
538 FND_MSG_PUB.Count_And_Get
539 ( p_count => x_msg_count
540 , p_data => x_msg_data
541 );
542
543 IF DOM_LOG.CHECK_LOG_LEVEL THEN
544 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback and Finish with unxepcted error.') ;
545 END IF ;
546
547 WHEN OTHERS THEN
548 IF FND_API.To_Boolean( p_commit ) THEN
549 IF DOM_LOG.CHECK_LOG_LEVEL THEN
550 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback . . .') ;
551 END IF ;
552 ROLLBACK TO l_api_name ;
553 END IF;
554
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
556
557 FND_MSG_PUB.Count_And_Get
558 ( p_count => x_msg_count
559 , p_data => x_msg_data
560 );
561
562 IF FND_MSG_PUB.Check_Msg_Level
563 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
564 THEN
565 FND_MSG_PUB.Add_Exc_Msg
566 ( G_PKG_NAME
567 , l_api_name
568 );
569 END IF;
570
571 FND_MSG_PUB.Count_And_Get
572 ( p_count => x_msg_count
573 , p_data => x_msg_data
574 );
575
576 IF DOM_LOG.CHECK_LOG_LEVEL THEN
577 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback and finish with system unxepcted error: '
578 || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
579 END IF ;
580
581
582 END Start_Doc_LC_Phase_WF ;
583
584
585 --
586 --
587 -- Abort DOC LC Phase Workflow to integrate CM Worklfow
588 -- This API is called when starting Doc LC Phase Workflow
589 -- We can put validation logic here
590 --
591 Procedure Abort_Doc_LC_Phase_WF
592 ( p_api_version IN NUMBER --
593 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
594 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
595 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
596 ,x_return_status OUT NOCOPY VARCHAR2 --
597 ,x_msg_count OUT NOCOPY NUMBER --
598 ,x_msg_data OUT NOCOPY VARCHAR2
599 ,p_change_id IN NUMBER
600 ,p_route_id IN NUMBER
601 ,p_lc_phase_code IN NUMBER := NULL
602 ,p_api_caller IN VARCHAR2
603 )
604 IS
605
606
607 l_api_name CONSTANT VARCHAR2(30) := 'Abort_Doc_LC_Phase_WF';
608 l_api_version CONSTANT NUMBER := 1.0;
609
610 BEGIN
611
612
613 -- Standard Start of API savepoint
614 SAVEPOINT l_api_name;
615
616 -- Standard call to check for call compatibility.
617 IF NOT FND_API.Compatible_API_Call( l_api_version
618 , p_api_version
619 , l_api_name
620 , G_PKG_NAME )
621 THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 -- Initialize message list if p_init_msg_list is set to TRUE.
626 IF FND_API.to_Boolean( p_init_msg_list ) THEN
627 FND_MSG_PUB.initialize;
628 END IF ;
629
630 -- Initialize API return status to success
631 x_return_status := FND_API.G_RET_STS_SUCCESS;
632
633
634 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
635 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'begin ' || l_api_name);
636 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
637 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Change Id : ' || TO_CHAR(p_change_id) );
638 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Route Id : ' || TO_CHAR(p_route_id) );
639 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'LC Phase Code : ' || TO_CHAR(p_lc_phase_code) );
640 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'API Caller : ' || p_api_caller);
641 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'-----------------------------------------------------');
642 END IF ;
643
644
645 -----------------------------------------------------------------
646 -- API body
647 -----------------------------------------------------------------
648
649 -- R12 No Business Logic
653 -----------------------------------------------------------------
650 -- At this time, this is place folder for future enh.
651
652
654 -- End of API body
655 -----------------------------------------------------------------
656
657 IF ( DOM_LOG.CHECK_LOG_LEVEL) THEN
658 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'end ' || l_api_name);
659 END IF ;
660
661
662 -- Standard check of p_commit.
663 IF FND_API.To_Boolean( p_commit ) THEN
664
665 IF DOM_LOG.CHECK_LOG_LEVEL THEN
666 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Do Commit.') ;
667 END IF ;
668
669 COMMIT WORK;
670 END IF;
671
672 -- Standard call to get message count and if count is 1, get message info.
673 FND_MSG_PUB.Count_And_Get
674 ( p_count => x_msg_count
675 , p_data => x_msg_data
676 );
677
678 IF DOM_LOG.CHECK_LOG_LEVEL THEN
679 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Finish. Eng Of Proc') ;
680 END IF ;
681
682
683 EXCEPTION
684 WHEN FND_API.G_EXC_ERROR THEN
685 -- Standard check of p_commit.
686 IF FND_API.To_Boolean( p_commit ) THEN
687 IF DOM_LOG.CHECK_LOG_LEVEL THEN
688 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback . . .') ;
689 END IF ;
690 ROLLBACK TO l_api_name ;
691 END IF;
692
693 x_return_status := FND_API.G_RET_STS_ERROR ;
694
695 FND_MSG_PUB.Count_And_Get
696 ( p_count => x_msg_count
697 , p_data => x_msg_data
698 );
699
700 IF DOM_LOG.CHECK_LOG_LEVEL THEN
701 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'RollBack and Finish with Error.') ;
702 END IF ;
703
704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
705 IF FND_API.To_Boolean( p_commit ) THEN
706 IF DOM_LOG.CHECK_LOG_LEVEL THEN
707 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback . . .') ;
708 END IF ;
709 ROLLBACK TO l_api_name ;
710 END IF;
711
712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
713
714 FND_MSG_PUB.Count_And_Get
715 ( p_count => x_msg_count
716 , p_data => x_msg_data
717 );
718
719 IF DOM_LOG.CHECK_LOG_LEVEL THEN
720 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback and Finish with unxepcted error.') ;
721 END IF ;
722
723 WHEN OTHERS THEN
724 IF FND_API.To_Boolean( p_commit ) THEN
725 IF DOM_LOG.CHECK_LOG_LEVEL THEN
726 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback . . .') ;
727 END IF ;
728 ROLLBACK TO l_api_name ;
729 END IF;
730
731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
732
733 FND_MSG_PUB.Count_And_Get
734 ( p_count => x_msg_count
735 , p_data => x_msg_data
736 );
737
738 IF FND_MSG_PUB.Check_Msg_Level
739 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
740 THEN
741 FND_MSG_PUB.Add_Exc_Msg
742 ( G_PKG_NAME
743 , l_api_name
744 );
745 END IF;
746
747 FND_MSG_PUB.Count_And_Get
748 ( p_count => x_msg_count
749 , p_data => x_msg_data
750 );
751
752 IF DOM_LOG.CHECK_LOG_LEVEL THEN
753 DOM_LOG.LOG_STR(G_PKG_NAME,l_api_name, null,'Rollback and finish with system unxepcted error: '
754 || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
755 END IF ;
756
757
758 END Abort_Doc_LC_Phase_WF ;
759
760
761
762
763 -- -----------------------------------------------------------------------------
764 -- API Name: Generate_Seq_For_Doc_Category
765 --
766 -- Description:
767 -- Create sequences for Document categories for Number and Revision generation
768 -- -----------------------------------------------------------------------------
769 PROCEDURE Generate_Seq_For_Doc_Category (
770 p_doc_category_id IN NUMBER
771 ,p_seq_start_num IN NUMBER
772 ,p_seq_increment_by IN NUMBER
773 ,x_return_status OUT NOCOPY VARCHAR2
774 ,x_errorcode OUT NOCOPY NUMBER
775 ,x_msg_count OUT NOCOPY NUMBER
776 ,x_msg_data OUT NOCOPY VARCHAR2
777 ,p_num_rev_type IN VARCHAR2
778 )IS
779 l_api_name CONSTANT VARCHAR2(50) := 'Generate_Sequence_For_Doc_category';
780 l_seq_name VARCHAR2(100);
781 l_syn_name VARCHAR2(100);
782 l_seq_name_prefix VARCHAR2(70) ;
783 l_seq_name_suffix CONSTANT VARCHAR2(10) := '_S' ;
784 l_dyn_sql VARCHAR2(100);
785 l_syn_name_prefix VARCHAR2(40);
786 l_status VARCHAR2(1);
787 l_industry VARCHAR2(1);
788 l_schema VARCHAR2(30);
789 l_apps_user CONSTANT VARCHAR2(10) := 'APPS';
790
791 BEGIN
792
793 IF FND_INSTALLATION.GET_APP_INFO('DOM', l_status, l_industry, l_schema) THEN
794 IF l_schema IS NULL THEN
795 Raise_Application_Error (-20001, 'DOM Schema could not be located.');
796 END IF;
797 ELSE
798 Raise_Application_Error (-20001, 'DOM Schema could not be located.');
799 END IF;
800
801 IF p_num_rev_type = 'NUM' THEN
802 l_seq_name_prefix := l_schema ||'.'||'DOC_NUM_SEQ_';
803 l_syn_name_prefix := 'DOC_NUM_SEQ_';
804 ELSE
805 l_seq_name_prefix := l_schema ||'.'||'DOC_REV_SEQ_';
806 l_syn_name_prefix := 'DOC_REV_SEQ_';
807 END IF;
808
809 l_seq_name := l_seq_name_prefix || p_doc_category_id || l_seq_name_suffix;
810 l_dyn_sql := 'CREATE SEQUENCE '||l_seq_name||' INCREMENT BY '||p_seq_increment_by||' START WITH '||p_seq_start_num || ' NOCACHE';
811 EXECUTE IMMEDIATE l_dyn_sql;
812 l_syn_name := l_syn_name_prefix || p_doc_category_id || l_seq_name_suffix;
813 l_dyn_sql := 'CREATE SYNONYM '||l_syn_name||' FOR '||l_seq_name;
814 EXECUTE IMMEDIATE l_dyn_sql;
815 --fix for bug 7695643, create grant to APPS with grant option
816 l_dyn_sql := 'GRANT ALL ON ' || l_seq_name ||' TO ' || l_apps_user || ' WITH GRANT OPTION';
817 ad_ddl.do_ddl('APPS', 'DOM', ad_ddl.create_grants, l_dyn_sql , l_seq_name);
818
819
820
821 EXCEPTION
822 WHEN others THEN
823 x_return_status := G_RET_STS_UNEXP_ERROR;
824 x_msg_data := G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
825 END Generate_Seq_For_Doc_Category;
826
827 ----------------------------------------------------------------------
828 -- -----------------------------------------------------------------------------
829 -- API Name: Drop_Sequence_For_Category
830 --
831 -- Description:
832 -- Drops the Sequence For Number Generation or Revision Generation
833 -- -----------------------------------------------------------------------------
834 PROCEDURE Drop_Sequence_For_Category (
835 p_doc_category_seq_name IN VARCHAR2
836 ,x_return_status OUT NOCOPY VARCHAR2
837 ,x_errorcode OUT NOCOPY NUMBER
838 ,x_msg_count OUT NOCOPY NUMBER
839 ,x_msg_data OUT NOCOPY VARCHAR2)
840 IS
841 l_api_name CONSTANT VARCHAR2(50) := 'Drop_Sequence_For_Category';
842 l_dyn_sql VARCHAR2(100);
843 l_status VARCHAR2(1);
844 l_industry VARCHAR2(1);
845 l_schema VARCHAR2(30);
846 BEGIN
847
848 IF FND_INSTALLATION.GET_APP_INFO('DOM', l_status, l_industry, l_schema) THEN
849 IF l_schema IS NULL THEN
850 Raise_Application_Error (-20001, 'DOM Schema could not be located.');
851 END IF;
852 ELSE
853 Raise_Application_Error (-20001, 'DOM Schema could not be located.');
854 END IF;
855
856 l_dyn_sql := 'DROP SYNONYM '||p_doc_category_seq_name;
857 EXECUTE IMMEDIATE l_dyn_sql;
858 l_dyn_sql := 'DROP SEQUENCE '||l_schema||'.'||p_doc_category_seq_name;
859 EXECUTE IMMEDIATE l_dyn_sql;
860 EXCEPTION
861 WHEN others THEN
862 x_return_status := G_RET_STS_UNEXP_ERROR;
863 x_msg_data := G_PKG_NAME||'.'||l_api_name||' '||SQLERRM;
864 END Drop_Sequence_For_Category;
865 --------------------------------------------------------------------------------------
866
867 -- API Name: GET_DOC_NUM_SCHEME
868 --
869 -- Description:
870 -- to get effective num generation scheme
871 -- -----------------------------------------------------------------------------
872 FUNCTION GET_DOC_NUM_SCHEME
873 ( P_CATEGORY_ID IN NUMBER
874 ) RETURN VARCHAR2
875 IS
876 l_doc_num_scheme VARCHAR2(30);
877 BEGIN
878 SELECT
879 DOC_NUM_SCHEME INTO l_doc_num_scheme
880 FROM(
881 SELECT CATEGORY_ID
885 WHERE DOC_NUM_SCHEME <> 'INHERITED'
882 , PARENT_CATEGORY_ID
883 , DOC_NUM_SCHEME
884 FROM dom_document_categories
886 CONNECT BY PRIOR parent_category_id = category_id
887 START WITH category_id = P_CATEGORY_ID ) doc_schemes
888 WHERE ROWNUM=1;
889
890 RETURN l_doc_num_scheme;
891
892 END GET_DOC_NUM_SCHEME;
893 --------------------------------------------------------------------------------------
894 -- -----------------------------------------------------------------------------
895 -- API Name: rowtocol
896 -- Srinivas Chintamani
897 -- Description:
898 -- Generic function to convert rows returned by arbitrary SQL into
899 -- a list using the passed in seperator character.
900 -- -----------------------------------------------------------------------------
901 FUNCTION rowtocol
902 ( p_slct IN VARCHAR2,
903 p_dlmtr IN VARCHAR2 DEFAULT ','
904 ) RETURN VARCHAR2 is
905
906 /*
907 1) Column should be character type.
908 2) If it is non-character type, column has to be converted into character type.
909 3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument.
910 4) If the SQL statement happened to return duplicate values, and if you don't want that to happen, put DISTINCT in the SELECT statement argument.
911 */
912
913 TYPE c_refcur IS REF CURSOR;
914 lc_str VARCHAR2(4000);
915 lc_colval VARCHAR2(4000);
916 c_dummy c_refcur;
917 l number;
918
919 BEGIN
920 OPEN c_dummy FOR p_slct;
921 LOOP
922 FETCH c_dummy INTO lc_colval;
923 EXIT WHEN c_dummy%NOTFOUND;
924 lc_str := lc_str || p_dlmtr || lc_colval;
925 END LOOP;
926
927 CLOSE c_dummy;
928 RETURN SUBSTR(lc_str,2);
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 lc_str := SQLERRM;
933 IF c_dummy%ISOPEN THEN
934 CLOSE c_dummy;
935 END IF;
936 RETURN lc_str;
937 END rowtocol;
938 --------------------------------------------------------------------------------------
939
940 END DOM_DOCUMENT_UTIL;