[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_TMPL_APPROVAL_PVT
Source
1 PACKAGE BODY OKC_TERMS_TMPL_APPROVAL_PVT as
2 /* $Header: OKCVTMPLAPPB.pls 120.7.12020000.4 2013/02/06 08:13:29 skavutha ship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
8 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
9 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
10 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
11 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
12 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
13 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
14 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
15 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
16 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
17
18 ---------------------------------------------------------------------------
19 -- GLOBAL VARIABLES
20 ---------------------------------------------------------------------------
21 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_TMPL_APPROVAL_PVT';
22 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
23
24 ------------------------------------------------------------------------------
25 -- GLOBAL CONSTANTS
26 ------------------------------------------------------------------------------
27 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
28 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
29
30 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
31 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
32 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
33
34 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
35 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
36 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
37 G_TEMPLATE_MISS_REC OKC_TERMS_TEMPLATES_PVT.template_rec_type;
38
39 G_OKC_MSG_INVALID_ARGUMENT CONSTANT VARCHAR2(200) := 'OKC_INVALID_ARGUMENT';
40 -- ARG_NAME ARG_VALUE is invalid.
41
42 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
43 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
44 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
45
46
47 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
48
49 CURSOR tmpl_csr(cp_template_id IN NUMBER, cp_object_version_number IN NUMBER) IS
50 SELECT okc_template_wf_keys_s1.NEXTVAL WF_SEQUENCE,
51 tmpl.template_id,
52 tmpl.template_name,
53 tmpl.description,
54 tmpl.status_code,
55 tmpl.object_version_number,
56 tmpl.org_id,
57 tmpl.intent,
58 tmpl.working_copy_flag,
59 tmpl.print_template_id,
60 tmpl.contract_expert_enabled,
61 fnd_lang.description language_desc
62 FROM okc_terms_templates_all tmpl,
63 fnd_languages_vl fnd_lang
64 WHERE tmpl.template_id = cp_template_id
65 AND ( tmpl.object_version_number = cp_object_version_number
66 OR cp_object_version_number IS NULL)
67 AND tmpl.language = fnd_lang.language_code(+)
68 FOR UPDATE OF
69 tmpl.status_code,
70 tmpl.object_version_number,
71 tmpl.last_update_date,
72 tmpl.last_updated_by
73 NOWAIT;
74
75 l_tmpl_rec tmpl_csr%ROWTYPE;
76
77 ---------------------------------------------------------------------------
78 -- Procedure set_wf_error_context
79 ---------------------------------------------------------------------------
80 /* Proceedure loops through the FND_MSG_PUB stack and sets the
81 workflow error context to give as much information as possible to wf engine
82 in case an api fails
83 */
84 PROCEDURE set_wf_error_context(
85 p_pkg_name IN VARCHAR2,
86 p_api_name IN VARCHAR2,
87 p_itemtype IN VARCHAR2 DEFAULT NULL,
88 p_itemkey IN VARCHAR2 DEFAULT NULL,
89 p_actid IN NUMBER DEFAULT NULL,
90 p_funcmode IN VARCHAR2 DEFAULT NULL,
91 p_msg_count IN NUMBER)
92 IS
93
94 TYPE l_arg_tbl_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
95 l_arg_tbl l_arg_tbl_type;
96
97 l_err_msg VARCHAR2(2000);
98
99 BEGIN
100
101 l_arg_tbl(0) := null;
102 l_arg_tbl(1) := null;
103 l_arg_tbl(2) := null;
104 l_arg_tbl(3) := null;
105 l_arg_tbl(4) := null;
106
107 l_arg_tbl(0) := substrb('ItemType='||p_itemtype||' ItemKey='||p_itemkey||' actid='||p_actid||
108 ' funcmode='||p_funcmode,1,2000);
109
110 FOR k in 1..p_msg_count LOOP
111 EXIT WHEN k >= 4;
112 l_arg_tbl(k) := substrb(FND_MSG_PUB.get(p_msg_index => k,p_encoded => 'F'), 1, 2000);
113 END LOOP;
114
115 WF_CORE.CONTEXT(p_pkg_name, p_api_name,
116 l_arg_tbl(0), l_arg_tbl(1), l_arg_tbl(2),l_arg_tbl(3),l_arg_tbl(4));
117
118 EXCEPTION
119
120 WHEN OTHERS THEN
121 l_err_msg := substrb('SQLCODE='||SQLCODE||' SQLERRM='||SQLERRM,1,2000);
122 WF_CORE.CONTEXT(G_PKG_NAME, 'set_wf_error_context',
123 'p_pkg_name='||p_pkg_name||' p_api_name='||p_api_name,
124 'p_itemtype='||p_itemtype||' p_itemkey='||p_itemkey,
125 'p_actid='||p_actid||' p_funcmode='||p_funcmode,
126 'p_msg_count='||p_msg_count,
127 l_err_msg);
128 END;
129
130
131 ---------------------------------------------------------------------------
132 -- Procedure selector
133 ---------------------------------------------------------------------------
134 --
135 -- Procedure
136 -- selector
137 --
138 -- Description
139 -- Determine which process to run
140 -- IN
141 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
142 -- itemkey - A string generated from the application object's primary key.
143 -- actid - The function activity(instance id).
144 -- funcmode - Run/Cancel/Timeout
145 -- OUT
146 -- resultout - Name of workflow process to run
147 --
148 PROCEDURE selector (
149 itemtype in varchar2,
150 itemkey in varchar2,
151 actid in number,
152 funcmode in varchar2,
153 resultout out nocopy varchar2 )
154 IS
155 l_user_id NUMBER;
156 l_resp_id NUMBER;
157 l_resp_appl_id NUMBER;
158
159 CURSOR get_resp_id_csr IS
160 SELECT responsibility_id
161 FROM fnd_responsibility
162 WHERE responsibility_key = 'OKC_TERMS_LIBRARY_ADMIN'
163 AND application_id = 510;
164
165 BEGIN
166
167 /*6329229, 7605085*/
168 l_user_id := FND_GLOBAL.user_id;
169 --l_resp_id := 24286;
170
171 OPEN get_resp_id_csr;
172 FETCH get_resp_id_csr INTO l_resp_id;
173 CLOSE get_resp_id_csr;
174
175 l_resp_appl_id := 510;
176
177
178 -- RUN mode - normal process execution
179 IF (funcmode = 'RUN') THEN
180 -- Return process to run
181 resultout := 'TMPL_APPROVAL';
182 RETURN;
183 /*Bug 6329229*/
184 ELSIF (funcmode = 'SET_CTX') THEN
185
186 -- wf_seq_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'TMPL_WF_SEQ_ID', false);
187
188
189 -- Set the database session context
190 fnd_global.apps_initialize(l_user_id,l_resp_id, l_resp_appl_id);
191
192 RETURN;
193
194 END IF;
195
196 -- CANCEL mode - activity 'compensation'
197 IF (funcmode = 'CANCEL') THEN
198 -- Return process to run
199 resultout := 'TMPL_APPROVAL';
200 RETURN;
201 END IF;
202
203 -- TIMEOUT mode
204 IF (funcmode = 'TIMEOUT') THEN
205 resultout := 'TMPL_APPROVAL';
206 RETURN;
207 END IF;
208
209 EXCEPTION
210 WHEN OTHERS THEN
211 IF (get_resp_id_csr%ISOPEN) THEN
212 CLOSE get_resp_id_csr;
213 END IF;
214 WF_CORE.context('OKC_TERMS_TMPL_APPROVAL_PVT','Selector',itemtype,itemkey,actid,funcmode);
215 RAISE;
216 END selector;
217
218 ---------------------------------------------------------------------------
219 -- Procedure lock_row
220 ---------------------------------------------------------------------------
221 FUNCTION lock_row(
222 p_template_id IN NUMBER,
223 p_object_version_number IN NUMBER
224 ) RETURN VARCHAR2
225 IS
226
227 E_Resource_Busy EXCEPTION;
228 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
229
230 CURSOR lchk_csr (cp_template_id NUMBER) IS
231 SELECT object_version_number
232 FROM OKC_TERMS_TEMPLATES_ALL
233 WHERE TEMPLATE_ID = cp_template_id;
234
235 l_return_status VARCHAR2(1);
236 l_object_version_number OKC_TERMS_TEMPLATES_ALL.OBJECT_VERSION_NUMBER%TYPE;
237 l_row_notfound BOOLEAN := FALSE;
238
239 BEGIN
240
241 /*IF (l_debug = 'Y') THEN
242 Okc_Debug.Log('4900: Entered Lock_Row', 2);
243 END IF;*/
244
245 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
246 FND_LOG.STRING(G_PROC_LEVEL,
247 G_PKG_NAME, '4900: Entered Lock_Row' );
248 END IF;
249
250
251 BEGIN -- begin inner block
252
253 OPEN tmpl_csr( p_template_id, p_object_version_number );
254 FETCH tmpl_csr INTO l_tmpl_rec;
255 l_row_notfound := tmpl_csr%NOTFOUND;
256 CLOSE tmpl_csr;
257
258 EXCEPTION
259 WHEN E_Resource_Busy THEN
260
261 /*IF (l_debug = 'Y') THEN
262 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
263 END IF;*/
264
265 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
266 FND_LOG.STRING(G_EXCP_LEVEL,
267 G_PKG_NAME, '5000: Leaving Lock_Row:E_Resource_Busy Exception' );
268 END IF;
269
270 IF (tmpl_csr%ISOPEN) THEN
271 CLOSE tmpl_csr;
272 END IF;
273
274 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
275 RETURN( G_RET_STS_ERROR );
276 END; -- end inner block
277
278 IF ( l_row_notfound ) THEN
279
280 l_return_status := G_RET_STS_ERROR;
281
282 OPEN lchk_csr(p_template_id);
283 FETCH lchk_csr INTO l_object_version_number;
284 l_row_notfound := lchk_csr%NOTFOUND;
285 CLOSE lchk_csr;
286
287 IF (l_row_notfound) THEN
288 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
289 ELSIF l_object_version_number > p_object_version_number THEN
290 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
291 ELSIF l_object_version_number = -1 THEN
292 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
293 ELSE -- it can be the only above condition. It can happen after restore version
294 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
295 END IF;
296 ELSE
297 l_return_status := G_RET_STS_SUCCESS;
298 END IF;
299
300 /*IF (l_debug = 'Y') THEN
301 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
302 END IF;*/
303
304 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
305 FND_LOG.STRING(G_PROC_LEVEL,
306 G_PKG_NAME, '5100: Leaving Lock_Row' );
307 END IF;
308
309 RETURN( l_return_status );
310
311 EXCEPTION
312
313 WHEN OTHERS THEN
314 IF (tmpl_csr%ISOPEN) THEN
315 CLOSE tmpl_csr;
316 END IF;
317 IF (lchk_csr%ISOPEN) THEN
318 CLOSE lchk_csr;
319 END IF;
320
321 /*IF (l_debug = 'Y') THEN
322 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
323 END IF;*/
324
325 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
326 FND_LOG.STRING(G_EXCP_LEVEL,
327 G_PKG_NAME, '5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm );
328 END IF;
329
330 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
331 p_msg_name => G_UNEXPECTED_ERROR,
332 p_token1 => G_SQLCODE_TOKEN,
333 p_token1_value => sqlcode,
334 p_token2 => G_SQLERRM_TOKEN,
335 p_token2_value => sqlerrm);
336 RETURN( G_RET_STS_UNEXP_ERROR );
337 END lock_row;
338
339
340 ---------------------------------------------------------------------------
341 -- Procedure start_approval
342 ---------------------------------------------------------------------------
343 /* added 2 new IN params and 1 out param
344 p_validation_level : 'A' or 'E' do all checks or checks with severity = E
345 p_check_for_drafts : 'Y' or 'N' if Y checks for drafts and inserts them
346 in the OKC_TMPL_DRAFT_CLAUSES table
347 x_sequence_id : contains the sequence id for table OKC_QA_ERRORS_T
348 that contains the validation results, is null if
349 no qa errors or warnings are found.
350
351 Existing out param x_qa_return_status will change to
352 have the following statues
353 x_qa_return_status : S if the template was succesfully submitted
354 W if qa check resulted in warnings. Use x_sequence_id
355 to display the qa results.
356 E if qa check resulted in errors. Use x_sequence_id
357 to display the qa results
358 D if there are draft articles and the user should be
359 redirected to the new submit page. Use x_sequence_id
360 if not null, to display a warnings link on the
361 new submit page.
362
363 p_validation_level p_check_for_drafts
364 Search/View/Update : A Y
365 New Submit Page : A N
366 Validation Page : E N
367
368 */
369
370 PROCEDURE start_approval (
371 p_api_version IN Number,
372 p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
373 p_commit IN Varchar2 default FND_API.G_FALSE,
374 p_template_id IN Number,
375 p_object_version_number IN Number default NULL,
376 x_return_status OUT NOCOPY Varchar2,
377 x_msg_data OUT NOCOPY Varchar2,
378 x_msg_count OUT NOCOPY Number,
379 x_qa_return_status OUT NOCOPY Varchar2,
380 p_validation_level IN VARCHAR2 DEFAULT 'A',
381 p_check_for_drafts IN VARCHAR2 DEFAULT 'N',
382 x_sequence_id OUT NOCOPY NUMBER
383 )
384 IS
385
386 l_api_version CONSTANT NUMBER := 2;
387 --l_api_version CONSTANT NUMBER := 1;
388 l_api_name CONSTANT VARCHAR2(30) := 'start_approval';
389 l_ItemType varchar2(30) := 'OKCTPAPP';
390 l_ItemKey varchar2(240);
391 l_Attach_key varchar2(250);
392 --Bug 3374952 l_ItemUserKey varchar2(80);
393 l_ItemUserKey varchar2(300);
394 l_WorkFlowProcess varchar2(80);
395 l_org_name varchar2(2000);
396 l_processowner varchar2(80);
397 l_dummy_var varchar2(1);
398 l_seq_id NUMBER;
399 --l_qa_return_status varchar2(250);
400 l_qa_result_tbl OKC_TERMS_QA_GRP.QA_RESULT_TBL_TYPE;
401 l_deliverables_exist VARCHAR2(100);
402
403 l_tmpl_org_id NUMBER;
404 l_drafts_present VARCHAR2(1) := 'N';
405 l_art_validation_results OKC_ART_BLK_PVT.validation_tbl_type;
406 l_expert_enabled VARCHAR2(1) := 'N';
407
408 --modify cursor to fetch org_id also
409 CURSOR tmpl_exists_csr IS
410 SELECT org_id
411 -- SELECT '!'
412 FROM okc_terms_templates_all
413 WHERE template_id = p_template_id;
414
415 CURSOR article_exists_cur IS
416 SELECT 1
417 FROM OKC_K_ARTICLES_B
418 WHERE document_id = p_template_id
419 AND document_type = 'TEMPLATE';
420
421 CURSOR org_name_csr(pc_org_id NUMBER) IS
422 SELECT name
423 FROM hr_operating_units
424 WHERE organization_id = pc_org_id;
425
426 BEGIN
427
428 /*IF (l_debug = 'Y') THEN
429 okc_debug.log('100: Entered OKC_TERMS_TMPL_APPROVAL_PVT.start_approval', 2);
430 END IF;*/
431
432 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
433 FND_LOG.STRING(G_PROC_LEVEL,
434 G_PKG_NAME, '100: Entered OKC_TERMS_TMPL_APPROVAL_PVT.start_approval' );
435 END IF;
436
437 -- Standard Start of API savepoint
438 SAVEPOINT g_start_approval_PVT;
439
440 -- Standard call to check for call compatibility.
441 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444
445 -- Initialize message list if p_init_msg_list is set to TRUE.
446 IF FND_API.to_Boolean( p_init_msg_list ) THEN
447 FND_MSG_PUB.initialize;
448 END IF;
449
450 -- Initialize API return status to success
451 x_return_status := FND_API.G_RET_STS_SUCCESS;
452
453
454 /*IF (l_debug = 'Y') THEN
455 okc_debug.log('600: opening tmpl_exits_csr', 2);
456 END IF;*/
457
458 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
459 FND_LOG.STRING(G_PROC_LEVEL,
460 G_PKG_NAME, '600: opening tmpl_exits_csr' );
461 END IF;
462
463 OPEN tmpl_exists_csr;
464 FETCH tmpl_exists_csr INTO l_tmpl_org_id;
465 -- FETCH tmpl_exists_csr INTO l_dummy_var;
466 IF tmpl_exists_csr%NOTFOUND THEN
467 /*IF (l_debug = 'Y') THEN
468 Okc_Debug.Log('2300: - attribute TEMPLATE_ID is invalid', 2);
469 END IF;*/
470
471 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
472 FND_LOG.STRING(G_PROC_LEVEL,
473 G_PKG_NAME, '2300: - attribute TEMPLATE_ID is invalid' );
474 END IF;
475 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TEMPLATE_ID');
476 x_return_status := G_RET_STS_ERROR;
477 END IF;
478 CLOSE tmpl_exists_csr;
479 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
481 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
482 RAISE FND_API.G_EXC_ERROR;
483 END IF;
484
485 x_return_status := Lock_Row(
486 p_template_id => p_template_id,
487 p_object_version_number => p_object_version_number );
488
489 IF l_tmpl_rec.status_code NOT IN ('DRAFT','REJECTED','REVISION') THEN
490 /*IF (l_debug = 'Y') THEN
491 Okc_Debug.Log('2310: - Status not in DRAFT/REJECTED', 2);
492 END IF;*/
493
494 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
495 FND_LOG.STRING(G_PROC_LEVEL,
496 G_PKG_NAME, '2310: - Status not in DRAFT/REJECTED' );
497 END IF;
498
499 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_APP_INVALID_STATUS','STATUS',okc_util.decode_lookup('OKC_TERMS_TMPL_STATUS',l_tmpl_rec.status_code));
500 x_return_status := G_RET_STS_ERROR;
501 END IF;
502 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
505 RAISE FND_API.G_EXC_ERROR;
506 END IF;
507
508 -- 11.5.10+ changes
509 -- Insert records in OKC_TMPL_DRAFT_CLAUSES if p_check_for_drafts = Y
510 IF (p_check_for_drafts = 'Y') THEN
511
512 OKC_TERMS_UTIL_PVT.create_tmpl_clauses_to_submit (
513 p_api_version => 1,
514 p_init_msg_list => FND_API.G_FALSE,
515 p_template_id => p_template_id,
516 p_org_id => l_tmpl_org_id,
517 x_drafts_present => l_drafts_present,
518 x_return_status => x_return_status,
519 x_msg_count => x_msg_count,
520 x_msg_data => x_msg_data);
521 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
523 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
524 RAISE FND_API.G_EXC_ERROR;
525 END IF;
526
527 END IF;
528
529
530 l_deliverables_exist := okc_terms_util_grp.Is_Deliverable_Exist(
531 p_api_version => 1,
532 p_init_msg_list => FND_API.G_FALSE,
533 x_return_status => x_return_status,
534 x_msg_data => x_msg_data,
535 x_msg_count => x_msg_count,
536 p_doc_type => 'TEMPLATE',
537 p_doc_id => p_template_id
538 );
539 l_deliverables_exist := UPPER(nvl(l_deliverables_exist,'NONE'));
540
541 --IF (nvl(l_tmpl_rec.contract_expert_enabled,'N') = 'N' AND
542 -- l_deliverables_exist = 'NONE') THEN
543
544 IF (l_deliverables_exist = 'NONE'
545 AND NVL(l_tmpl_rec.contract_expert_enabled,'N') = 'N') THEN
546
547 OPEN article_exists_cur;
548 FETCH article_exists_cur INTO l_dummy_var;
549 IF article_exists_cur%NOTFOUND THEN
550 IF (l_tmpl_rec.intent = 'S') THEN
551 -- Added new message for sell side template for bug 3699018
552 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_APP_NO_ARTICLE_SELL');
553 ELSE
554 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_APP_NO_ARTICLE');
555 END IF;
556 x_return_status := G_RET_STS_ERROR;
557 END IF;
558 CLOSE article_exists_cur;
559
560 END IF;
561
562 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
564 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
565 RAISE FND_API.G_EXC_ERROR;
566 END IF;
567
568 x_qa_return_status := 'S';
569 OKC_TERMS_QA_GRP.QA_Doc (
570 p_api_version => 1,
571 p_init_msg_list => FND_API.G_FALSE,
572 p_commit => FND_API.G_FALSE,
573 x_return_status => x_return_status,
574 x_msg_count => x_msg_count,
575 x_msg_data => x_msg_data,
576
577 p_qa_mode => 'NORMAL',
578 p_doc_type => 'TEMPLATE',
579 p_doc_id => p_template_id,
580
581 x_sequence_id => x_sequence_id,
582 x_qa_return_status => x_qa_return_status,
583 p_validation_level => p_validation_level);
584
585 /* obsolete for 11.5.10+
586 IF x_qa_return_status = 'E' THEN
587 --IF (l_debug = 'Y') THEN
588 -- Okc_Debug.Log('2320: - Errors found in Template QA', 2);
589 --END IF;
590
591 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
592 FND_LOG.STRING(G_PROC_LEVEL,
593 G_PKG_NAME, '2320: - Errors found in Template QA' );
594 END IF;
595 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_APP_QA_ERROR');
596 x_return_status := G_RET_STS_ERROR;
597 END IF;
598 */
599 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
600 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
601 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
602 RAISE FND_API.G_EXC_ERROR;
603 END IF;
604
605 -- 11.5.10+ changes
606 -- check for various conditions to see if we need proceed further
607
608 -- set x_sequence_id to null if there are no errors or warnings
609 IF (x_qa_return_status = 'S') THEN
610 x_sequence_id := NULL;
611 END IF;
612
613 IF (x_qa_return_status = 'E') THEN
614 -- exit immediately, no further processing
615 RETURN;
616 ELSE
617 IF (l_drafts_present = 'Y') THEN
618 x_qa_return_status := 'D';
619 -- exit immediately as the user has to first
620 -- select/unselect draft articles
621 RETURN;
622 ELSE
623 -- no errors and no drafts, x_qa_return_status = S or W
624 IF (x_qa_return_status = 'W') THEN
625
626 -- validation level = A, and we get warnings we stop
627 IF (p_validation_level = 'A') THEN
628 -- exit immediately as the user has to be shown the warnings
629 RETURN;
630 -- validation level = E, so with warnings we are ok
631 ELSE
632 x_qa_return_status := 'S';
633 END IF;
634 ELSE
635 -- no warnings or error, so continue
636 NULL;
637 END IF; -- of IF/ELSE (x_qa_return_status = 'W')
638
639 END IF; -- of IF/ELSE (l_drafts_present = 'Y')
640 END IF; -- of IF/ELSE (x_qa_return_status = 'E')
641
642 -- call change_clause_status to update all draft articles to pending approval
643 -- if no draft articles are there, this will do nothing
644
645
646 -- check wether the template is expert enabled or not
647 l_expert_enabled := OKC_XPRT_UTIL_PVT. xprt_enabled_template(p_template_id);
648
649
650 change_clause_status (
651 p_api_version => 1,
652 p_init_msg_list => FND_API.G_FALSE,
653 p_commit => FND_API.G_FALSE,
654
655 x_return_status => x_return_status,
656 x_msg_data => x_msg_data,
657 x_msg_count => x_msg_count,
658
659 p_template_id => p_template_id,
660 p_wf_seq_id => NULL, -- we do not have this here
661 p_status => 'PENDING_APPROVAL',
662
663 -- the call to OKC_TERMS_QA_GRP.QA_Doc will ensure that
664 -- all article validation has been done
665 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
666 x_validation_results => l_art_validation_results);
667
668 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
670 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
671 -- get the validation results as a simple string
672 -- and add to fnd message stack
673 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, 'change_clause_status', get_error_string(l_art_validation_results));
674 RAISE FND_API.G_EXC_ERROR;
675 END IF;
676
677 UPDATE okc_terms_templates_all
678 SET status_code = 'PENDING_APPROVAL',
679 object_version_number = object_version_number + 1,
680 last_update_date = sysdate,
681 last_updated_by = fnd_global.user_id
682 WHERE template_id = p_template_id;
683
684 l_processOwner := fnd_global.user_name;
685
686 --Bug 3570042
687 -- l_itemkey := substr(l_tmpl_rec.template_name,1,150)||':'||l_tmpl_rec.org_id||':'||l_tmpl_rec.wf_sequence;
688 -- l_ItemUserKey := l_ItemKey;
689
690 l_itemkey := l_tmpl_rec.template_id||l_tmpl_rec.wf_sequence;
691 l_ItemUserKey :=
692 substrb(rpad(substr(l_tmpl_rec.template_name,1,130)||':'||l_tmpl_rec.org_id||':'
693 ||l_tmpl_rec.wf_sequence,(round(length(substr(l_tmpl_rec.template_name,1,160)||':'
694 ||l_tmpl_rec.org_id||':'||l_tmpl_rec.wf_sequence)/8)+1)*8,'0'),1,160);
695
696 l_ItemType := 'OKCTPAPP';
697 l_WorkflowProcess := 'TMPL_APPROVAL';
698
699 --
700 -- Start Process :
701 -- If workflowprocess is passed, it will be run.
702 -- If workflowprocess is NOT passed, the selector function
703 -- defined in the item type will determine which process to run.
704 --
705 wf_engine.CreateProcess( ItemType => l_ItemType,
706 ItemKey => l_ItemKey,
707 process => l_WorkflowProcess );
708
709 wf_engine.SetItemUserKey ( ItemType => l_ItemType,
710 ItemKey => l_ItemKey,
711 UserKey => l_ItemUserKey);
712 --
713 --
714 -- Initialize workflow item attributes
715 --
716 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
717 itemkey => l_itemkey,
718 aname => 'TEMPLATE_NAME',
719 avalue => l_tmpl_rec.template_name);
720 --
721 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
722 itemkey => l_itemkey,
723 aname => 'TEMPLATE_DESCRIPTION',
724 avalue => l_tmpl_rec.description);
725
726 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
727 itemkey => l_itemkey,
728 aname => 'SUBMITTED_BY_USER',
729 avalue => l_ProcessOwner);
730
731 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
732 itemkey => l_itemkey,
733 aname => 'TEMPLATE_ID',
734 avalue => l_tmpl_rec.template_id);
735
736
737 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
738 itemkey => l_itemkey,
739 aname => 'PRINT_TEMPLATE_ID',
740 avalue => l_tmpl_rec.print_template_id);
741
742 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
743 itemkey => l_itemkey,
744 aname => 'ORG_ID',
745 avalue => l_tmpl_rec.org_id);
746
747 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
748 itemkey => l_itemkey,
749 aname => 'STATUS_CODE',
750 avalue => l_tmpl_rec.status_code);
751
752 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
753 itemkey => l_itemkey,
754 aname => 'TMPL_INTENT',
755 avalue => okc_util.decode_lookup('OKC_TERMS_INTENT',l_tmpl_rec.intent) );
756
757 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
758 itemkey => l_itemkey,
759 aname => 'TMPL_WORKINGCOPY',
760 avalue => NVL(l_tmpl_rec.working_copy_flag,'N'));
761
762 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
763 itemkey => l_itemkey,
764 aname => 'TMPL_WF_SEQ_ID',
765 avalue => l_tmpl_rec.wf_sequence);
766
767 OPEN org_name_csr(l_tmpl_rec.org_id);
768 FETCH org_name_csr INTO l_org_name;
769 CLOSE org_name_csr;
770
771 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
772 itemkey => l_itemkey,
773 aname => 'ORG_NAME',
774 avalue => l_org_name);
775
776 --l_attach_key := 'FND:entity=OKC_TERMS_TEMPLATES'||fnd_global.local_chr(38)||'pk1name=TEMPLATE_ID'
777 -- ||fnd_global.local_chr(38)||'pk1value='||l_tmpl_rec.template_id||fnd_global.local_chr(38)
778 -- ||'pk2name=WF_SEQUENCE_ID'||fnd_global.local_chr(38)||'pk2value='||l_tmpl_rec.wf_sequence ;
779
780 l_attach_key := 'FND:entity=OKC_TERMS_TEMPLATES'||'&pk1name=TEMPLATE_ID&pk1value='||
781 l_tmpl_rec.template_id||'&pk2name=WF_SEQUENCE_ID&pk2value='||l_tmpl_rec.wf_sequence ;
782
783
784 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
785 itemkey => l_itemkey,
786 aname => 'TMPL_ATTACHMENT',
787 avalue => l_attach_key);
788
789 --wf_directory.GetRoleDisplayName(RequestorUsername) );
790
791 wf_engine.SetItemAttrText( itemtype => l_itemtype,
792 itemkey => l_itemkey,
793 aname => 'MONITOR_URL',
794 avalue =>
795 wf_monitor.GetUrl(wf_core.translate('WF_WEB_AGENT')
796 ,l_itemtype,l_itemkey,'NO'));
797
798 -- set new attribute expert enabled
799 -- TODO call expert function to set l_expert_enabled
800 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
801 itemkey => l_itemkey,
802 aname => 'TMPL_EXPERT_ENABLED',
803 avalue => l_expert_enabled);
804
805 wf_engine.SetItemOwner ( itemtype => l_itemtype,
806 itemkey => l_itemkey,
807 owner => l_ProcessOwner );
808
809 -- 11.5.10+ changes
810 -- update okc_tmpl_draft_clauses with the wf_seq_id
811 UPDATE OKC_TMPL_DRAFT_CLAUSES
812 SET WF_SEQ_ID = l_tmpl_rec.wf_sequence
813 WHERE template_id = p_template_id
814 AND nvl(selected_yn, 'N') = 'Y';
815
816 -- delete all clauses with selectedYn= N, they are not required any more.
817 DELETE OKC_TMPL_DRAFT_CLAUSES
818 WHERE TEMPLATE_ID = p_template_id
819 AND nvl(selected_yn, 'N') = 'N';
820
821 -- end of 11.5.10+ changes
822 --MLS for templates
823 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
824 itemkey => l_itemkey,
825 aname => 'TEMPLATE_LANGUAGE',
826 avalue => l_tmpl_rec.language_desc);
827
828
829 -- need to ensure that call to wf start_process is the last to be called
830 -- becuase once invoked, it stops only if a blocking activity is reached.
831 wf_engine.StartProcess( itemtype => l_itemtype,
832 itemkey => l_itemkey );
833
834 -- Standard call to get message count and if count is 1, get message info.
835 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
836
837 /*IF (l_debug = 'Y') THEN
838 okc_debug.log('1000: Leaving start_approval', 2);
839 END IF;*/
840
841 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
842 FND_LOG.STRING(G_PROC_LEVEL,
843 G_PKG_NAME, '1000: Leaving start_approval' );
844 END IF;
845
846 EXCEPTION
847
848 WHEN FND_API.G_EXC_ERROR THEN
849
850 /*IF (l_debug = 'Y') THEN
851 okc_debug.log('800: Leaving start_approval: OKC_API.G_EXCEPTION_ERROR Exception', 2);
852 END IF;*/
853
854 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
855 FND_LOG.STRING(G_EXCP_LEVEL,
856 G_PKG_NAME, '800: Leaving start_approval: OKC_API.G_EXCEPTION_ERROR Exception' );
857 END IF;
858
859 ROLLBACK TO g_start_approval_pvt;
860 x_return_status := G_RET_STS_ERROR ;
861 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
862
863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
864 /*IF (l_debug = 'Y') THEN
865 okc_debug.log('900: Leaving start_approval: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
866 END IF;*/
867
868 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
869 FND_LOG.STRING(G_EXCP_LEVEL,
870 G_PKG_NAME, '900: Leaving start_approval: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
871 END IF;
872
873 ROLLBACK TO g_start_approval_pvt;
874 x_return_status := G_RET_STS_UNEXP_ERROR ;
875 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
876
877 WHEN OTHERS THEN
878 /*IF (l_debug = 'Y') THEN
879 okc_debug.log('1000: Leaving start_approval because of EXCEPTION: '||sqlerrm, 2);
880 END IF;*/
881
882 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
883 FND_LOG.STRING(G_EXCP_LEVEL,
884 G_PKG_NAME, '1000: Leaving start_approval because of EXCEPTION: '||sqlerrm );
885 END IF;
886
887 ROLLBACK TO g_start_approval_pvt;
888 x_return_status := G_RET_STS_UNEXP_ERROR ;
889 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
890 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
891 END IF;
892 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
893 END start_approval;
894
895 ---------------------------------------------------------------------------
896 -- Procedure approve_template
897 ---------------------------------------------------------------------------
898 --
899 -- Procedure
900 -- approve_template
901 --
902 -- Description
903 --
904 -- IN
905 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
906 -- itemkey - A string generated from the application object's primary key.
907 -- itemuserkey - A string generated from the application object user-friendly
908 -- primary key.
909 -- actid - The function activity(instance id).
910 -- processowner - The username owner for this item instance.
911 -- funcmode - Run/Cancel
912 -- OUT
913 -- resultout - Name of workflow process to run
914 --
915 PROCEDURE approve_template (
916 itemtype in varchar2,
917 itemkey in varchar2,
918 actid in number,
919 funcmode in varchar2,
920 resultout out nocopy varchar2 )
921 IS
922
923 pragma autonomous_transaction;
924
925 l_tmpl_id okc_terms_templates_all.template_id%TYPE;
926 l_tmpl_status okc_terms_templates_all.status_code%TYPE;
927 l_tmpl_wc okc_terms_templates_all.working_copy_flag%TYPE;
928 l_tmpl_wf_seq_id NUMBER;
929 x_return_status VARCHAR2(1);
930 x_msg_count NUMBER;
931 x_msg_data VARCHAR2(2000);
932 l_validation_results OKC_ART_BLK_PVT.validation_tbl_type;
933 l_parent_template_id NUMBER;
934 l_expert_enabled VARCHAR2(1) := 'N';
935 approval_exception EXCEPTION;
936
937 clause_sts_exception_1 EXCEPTION;
938 clause_sts_exception_2 EXCEPTION;
939 templ_publish_exception EXCEPTION;
940
941 l_okc_rules_engine VARCHAR2(1);
942
943
944 BEGIN
945 IF ( funcmode = 'RUN' ) THEN
946 l_tmpl_id :=
947 wf_engine.getItemAttrNumber(itemtype, itemkey, 'TEMPLATE_ID', false);
948 l_tmpl_status :=
949 wf_engine.getItemAttrText(itemtype, itemkey, 'STATUS_CODE', false);
950 l_tmpl_wc :=
951 wf_engine.getItemAttrText(itemtype, itemkey, 'TMPL_WORKINGCOPY', false);
952 l_tmpl_wf_seq_id :=
953 wf_engine.getItemAttrText(itemtype, itemkey, 'TMPL_WF_SEQ_ID', false);
954 l_expert_enabled :=
955 wf_engine.getItemAttrText(itemtype, itemkey, 'TMPL_EXPERT_ENABLED', false);
956 x_return_status := 'S';
957
958
959 IF (l_expert_enabled = 'Y') THEN
960
961 SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
962
963 fnd_file.put_line(FND_FILE.LOG,'Using OKC Rules Engine'||l_okc_rules_engine);
964
965 IF Nvl(l_okc_rules_engine,'N') = 'N' THEN
966
967 OKC_XPRT_UTIL_PVT.create_production_publication (
968 p_calling_mode => 'TEMPLATE_APPROVAL',
969 p_template_id => l_tmpl_id,
970 x_return_status => x_return_status,
971 x_msg_data => x_msg_data,
972 x_msg_count => x_msg_count);
973
974 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
975 -- some clause validation failed
976 RAISE templ_publish_exception;
977 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
978 -- unexpected error
979 RAISE templ_publish_exception;
980 END IF;
981
982 ELSE
983
984 UPDATE Okc_Xprt_Question_Orders
985 SET runtime_available_flag = 'Y',
986 last_updated_by = FND_GLOBAL.USER_ID,
987 last_update_date = SYSDATE,
988 last_update_login = FND_GLOBAL.LOGIN_ID
989 WHERE template_id= l_tmpl_id
990 AND question_rule_status = 'ACTIVE';
991
992 -- Delete from okc_xprt_template_rules
993 DELETE FROM okc_xprt_template_rules
994 WHERE NVL(deleted_flag,'N') = 'Y'
995 AND template_id = l_tmpl_id;
996
997 -- Update published_flag in okc_xprt_template_rules
998 UPDATE okc_xprt_template_rules
999 SET published_flag = 'Y'
1000 WHERE template_id= l_tmpl_id ;
1001
1002
1003 END IF;
1004
1005 END IF;
1006
1007 CHANGE_CLAUSE_STATUS (
1008 p_api_version => 1,
1009 p_init_msg_list => FND_API.G_FALSE,
1010 p_commit => FND_API.G_FALSE,
1011 p_template_id => l_tmpl_id,
1012 p_wf_seq_id => l_tmpl_wf_seq_id,
1013 p_status => 'APPROVED',
1014 x_validation_results => l_validation_results,
1015 x_return_status => x_return_status,
1016 x_msg_data => x_msg_data,
1017 x_msg_count => x_msg_count);
1018
1019
1020 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1021 -- some clause validation failed
1022 RAISE clause_sts_exception_1;
1023 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1024 -- unexpected error
1025 RAISE clause_sts_exception_2;
1026 END IF;
1027
1028 IF ((l_tmpl_status = 'REVISION') OR
1029 (l_tmpl_status = 'REJECTED' AND l_tmpl_wc = 'Y')) THEN
1030
1031 OKC_TERMS_UTIL_PVT.merge_template_working_copy(
1032 p_api_version => 1,
1033 p_init_msg_list => FND_API.G_FALSE,
1034 p_commit => FND_API.G_FALSE,
1035 p_template_id => l_tmpl_id,
1036 x_return_status => x_return_status,
1037 x_msg_data => x_msg_data,
1038 x_msg_count => x_msg_count,
1039 x_parent_template_id => l_parent_template_id);
1040
1041 Wf_engine.setItemAttrNumber(
1042 itemtype => itemtype,
1043 itemkey => itemkey,
1044 aname => 'TEMPLATE_ID',
1045 avalue => l_parent_template_id);
1046
1047 ELSE
1048
1049 UPDATE okc_terms_templates_all
1050 SET status_code = 'APPROVED'
1051 WHERE template_id = l_tmpl_id;
1052
1053 END IF;
1054
1055 resultout := 'COMPLETE';
1056
1057 IF x_return_status = 'S' THEN
1058 COMMIT;
1059 ELSE
1060 /*
1061 ROLLBACK;
1062 UPDATE okc_terms_templates_all
1063 SET status_code = l_tmpl_status
1064 WHERE template_id = l_tmpl_id;
1065 COMMIT;
1066 */
1067 RAISE approval_exception;
1068 END IF;
1069
1070 RETURN;
1071
1072 END IF; -- of IF ( funcmode = 'RUN' ) THEN
1073
1074 EXCEPTION
1075 WHEN clause_sts_exception_1 THEN
1076 -- try to send as much info as possible to the workflow error stack from validation results
1077 WF_CORE.CONTEXT(G_PKG_NAME, 'Change_clause_status', itemtype,
1078 itemkey, to_char(actid)||funcmode,
1079 get_error_string(l_validation_results));
1080 RAISE;
1081
1082 WHEN clause_sts_exception_2 THEN
1083 set_wf_error_context(
1084 p_pkg_name => G_PKG_NAME,
1085 p_api_name => 'change_clause_status',
1086 p_itemtype => itemtype,
1087 p_itemkey => itemkey,
1088 p_actid => actid,
1089 p_funcmode => funcmode,
1090 p_msg_count => x_msg_count);
1091 RAISE;
1092
1093 WHEN approval_exception THEN
1094 set_wf_error_context(
1095 p_pkg_name => G_PKG_NAME,
1096 p_api_name => 'merge_template_working_copy',
1097 p_itemtype => itemtype,
1098 p_itemkey => itemkey,
1099 p_actid => actid,
1100 p_funcmode => funcmode,
1101 p_msg_count => x_msg_count);
1102 RAISE;
1103
1104 WHEN templ_publish_exception THEN
1105 set_wf_error_context(
1106 p_pkg_name => G_PKG_NAME,
1107 p_api_name => 'create_production_publication',
1108 p_itemtype => itemtype,
1109 p_itemkey => itemkey,
1110 p_actid => actid,
1111 p_funcmode => funcmode,
1112 p_msg_count => x_msg_count);
1113 RAISE;
1114 WHEN OTHERS THEN
1115 WF_CORE.CONTEXT ( G_PKG_NAME, 'approve_tempalte', itemtype,
1116 itemkey, to_char(actid), funcmode);
1117 RAISE;
1118 END approve_template;
1119
1120
1121
1122 ---------------------------------------------------------------------------
1123 -- Procedure reject_template
1124 ---------------------------------------------------------------------------
1125 --
1126 -- Procedure
1127 -- reject_template
1128 --
1129 -- Description
1130 --
1131 -- IN
1132 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1133 -- itemkey - A string generated from the application object's primary key.
1134 -- itemuserkey - A string generated from the application object user-friendly
1135 -- primary key.
1136 -- actid - The function activity(instance id).
1137 -- processowner - The username owner for this item instance.
1138 -- funcmode - Run/Cancel
1139 -- OUT
1140 -- resultout - Name of workflow process to run
1141 --
1142 PROCEDURE reject_template (
1143 itemtype in varchar2,
1144 itemkey in varchar2,
1145 actid in number,
1146 funcmode in varchar2,
1147 resultout out nocopy varchar2 )
1148 IS
1149
1150 l_tmpl_id okc_terms_templates_all.template_id%TYPE;
1151 l_tmpl_status okc_terms_templates_all.status_code%TYPE;
1152 l_tmpl_wf_seq_id NUMBER;
1153 x_return_status VARCHAR2(1);
1154 x_msg_count NUMBER;
1155 x_msg_data VARCHAR2(2000);
1156 reject_exception EXCEPTION;
1157 clause_sts_exception_1 EXCEPTION;
1158 clause_sts_exception_2 EXCEPTION;
1159 l_validation_results OKC_ART_BLK_PVT.validation_tbl_type;
1160
1161 BEGIN
1162
1163 IF ( funcmode = 'RUN' ) THEN
1164
1165 l_tmpl_id :=
1166 wf_engine.getItemAttrNumber(itemtype, itemkey, 'TEMPLATE_ID', false);
1167 l_tmpl_status :=
1168 wf_engine.getItemAttrText(itemtype, itemkey, 'STATUS_CODE', false);
1169 l_tmpl_wf_seq_id :=
1170 wf_engine.getItemAttrText(itemtype, itemkey, 'TMPL_WF_SEQ_ID', false);
1171 x_return_status := 'S';
1172
1173 CHANGE_CLAUSE_STATUS (
1174 p_api_version => 1,
1175 p_init_msg_list => FND_API.G_FALSE,
1176 p_commit => FND_API.G_FALSE,
1177 p_template_id => l_tmpl_id,
1178 p_wf_seq_id => l_tmpl_wf_seq_id,
1179 p_status => 'REJECTED',
1180 x_validation_results => l_validation_results,
1181 x_return_status => x_return_status,
1182 x_msg_data => x_msg_data,
1183 x_msg_count => x_msg_count);
1184
1185 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1186 -- some clause validation failed
1187 RAISE clause_sts_exception_1;
1188 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1189 -- unexpected error
1190 RAISE clause_sts_exception_2;
1191 END IF;
1192
1193 UPDATE okc_terms_templates_all
1194 --SET status_code = nvl(l_tmpl_status,decode(working_copy_flag,null,'DRAFT','REVISION'))
1195 --SET status_code = decode(working_copy_flag,null,'DRAFT','REVISION')
1196 SET status_code = 'REJECTED'
1197 WHERE template_id = l_tmpl_id;
1198
1199 resultout := 'COMPLETE';
1200
1201 IF x_return_status = 'S' THEN
1202 RETURN;
1203 ELSE
1204 RAISE reject_exception;
1205 END IF;
1206
1207 RETURN;
1208
1209 END IF;
1210
1211 EXCEPTION
1212 WHEN clause_sts_exception_1 THEN
1213 -- try to send as much info as possible to the workflow error stack from validation results
1214 WF_CORE.CONTEXT('OKC_TERMS_TMPL_APPROVAL_PVT', 'Change_clause_status', itemtype,
1215 itemkey, to_char(actid)||funcmode,
1216 get_error_string(l_validation_results));
1217 RAISE;
1218
1219 WHEN clause_sts_exception_2 THEN
1220 set_wf_error_context(
1221 p_pkg_name => G_PKG_NAME,
1222 p_api_name => 'change_clause_status',
1223 p_itemtype => itemtype,
1224 p_itemkey => itemkey,
1225 p_actid => actid,
1226 p_funcmode => funcmode,
1227 p_msg_count => x_msg_count);
1228 RAISE;
1229
1230 WHEN reject_exception THEN
1231 WF_CORE.CONTEXT('OKC_TERMS_UTIL_PVT', 'Reject_Template', itemtype,
1232 itemkey, to_char(actid), funcmode);
1233 RAISE;
1234
1235 WHEN OTHERS THEN
1236 WF_CORE.CONTEXT ( 'OKC_TERMS_TMPL_APPROVAL_PVT', 'Reject_Template', itemtype,
1237 itemkey, to_char(actid), funcmode);
1238 RAISE;
1239 END reject_template;
1240
1241
1242
1243
1244 PROCEDURE select_approver (
1245 itemtype in varchar2,
1246 itemkey in varchar2,
1247 actid in number,
1248 funcmode in varchar2,
1249 resultout out nocopy varchar2 )
1250 IS
1251
1252 l_template_id NUMBER;
1253 l_tmpl_intent VARCHAR2(1);
1254 l_approver fnd_user.user_name%TYPE NULL;
1255 l_org_id NUMBER := NULL;
1256
1257 CURSOR tmpl_approver_csr(cp_template_id IN NUMBER) IS
1258 SELECT decode(tmpl.intent,'S',org.org_information2,org.org_information6) org_information
1259 FROM hr_organization_information org,
1260 okc_terms_templates_all tmpl
1261 WHERE org.organization_id = tmpl.org_id
1262 AND org.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
1263 AND tmpl.template_id = cp_template_id;
1264
1265 BEGIN
1266 --
1267 -- RUN mode - normal process execution
1268 --
1269 IF (funcmode = 'RUN') then
1270
1271 l_template_id := wf_engine.GetItemAttrNumber(
1272 itemtype => itemtype,
1273 itemkey => itemkey,
1274 aname => 'TEMPLATE_ID' );
1275
1276 OPEN tmpl_approver_csr(l_template_id);
1277 FETCH tmpl_approver_csr INTO l_approver;
1278 CLOSE tmpl_approver_csr;
1279
1280 --l_approver := 'CONMGR';
1281
1282 IF l_approver IS NOT NULL THEN
1283 wf_engine.SetItemAttrText (itemtype => itemtype,
1284 itemkey => itemkey,
1285 aname => 'APPROVER_USERNAME',
1286 avalue => l_approver);
1287
1288 resultout := 'COMPLETE:T';
1289
1290 ELSE
1291 resultout := 'COMPLETE:F';
1292 END IF;
1293
1294 RETURN;
1295
1296 END IF;
1297
1298 -- CANCEL mode - activity 'compensation'
1299 IF (funcmode = 'CANCEL') then
1300 resultout := 'COMPLETE:';
1301 RETURN;
1302 END IF;
1303
1304 -- TIMEOUT mode
1305 IF (funcmode = 'TIMEOUT') then
1306 resultout := 'COMPLETE:';
1307 RETURN;
1308 END IF;
1309
1310 END select_approver;
1311
1312 ---------------------------------------------------------------------------
1313 -- Procedure attachment_exists
1314 ---------------------------------------------------------------------------
1315 PROCEDURE attachment_exists (
1316 itemtype in varchar2,
1317 itemkey in varchar2,
1318 actid in number,
1319 funcmode in varchar2,
1320 resultout out nocopy varchar2 )
1321 IS
1322 l_template_id NUMBER;
1323 l_wf_seq_id NUMBER;
1324 l_dummy_var VARCHAR2(1);
1325 l_tmpl_attach_exists VARCHAR2(1);
1326
1327 CURSOR attachment_exists_csr(cp_val1 IN VARCHAR2,cp_val2 IN VARCHAR2) IS
1328 SELECT 1
1329 FROM fnd_attached_documents
1330 WHERE entity_name = 'OKC_TERMS_TEMPLATES'
1331 AND pk1_value = cp_val1
1332 AND pk2_value = cp_val2;
1333
1334 BEGIN
1335
1336 -- RUN mode - normal process execution
1337 IF (funcmode = 'RUN') then
1338
1339
1340 -- checking wether the item attribute was already set.
1341 l_tmpl_attach_exists := wf_engine.GetItemAttrNumber(
1342 itemtype => itemtype,
1343 itemkey => itemkey,
1344 aname => 'TMPL_ATTACH_EXISTS' );
1345
1346 if l_tmpl_attach_exists is not null then
1347
1348 IF l_tmpl_attach_exists = 'Y' then
1349 resultout := 'COMPLETE:T';
1350 ELSE
1351 resultout := 'COMPLETE:F';
1352 END IF;
1353
1354 -- first time ,if item attribute was not set, execute the query
1355 else
1356
1357 l_template_id := wf_engine.GetItemAttrNumber(
1358 itemtype => itemtype,
1359 itemkey => itemkey,
1360 aname => 'TEMPLATE_ID' );
1361
1362 l_wf_seq_id := wf_engine.GetItemAttrNumber(
1363 itemtype => itemtype,
1364 itemkey => itemkey,
1365 aname => 'TMPL_WF_SEQ_ID' );
1366
1367 OPEN attachment_exists_csr(l_template_id,l_wf_seq_id);
1368 FETCH attachment_exists_csr INTO l_dummy_var;
1369
1370
1371 IF attachment_exists_csr%FOUND THEN
1372 resultout := 'COMPLETE:T';
1373 wf_engine.setItemAttrText(itemtype, itemkey, 'TMPL_ATTACH_EXISTS', 'Y');
1374 ELSE
1375 resultout := 'COMPLETE:F';
1376 wf_engine.setItemAttrText(itemtype, itemkey, 'TMPL_ATTACH_EXISTS', 'N');
1377 END IF;
1378 CLOSE attachment_exists_csr;
1379
1380 end if;
1381
1382 RETURN;
1383
1384 END IF;
1385 /* bug 5631705 commented out CANCEL and TIMEOUT modes
1386 -- CANCEL mode - activity 'compensation'
1387 IF (funcmode = 'CANCEL') then
1388 resultout := 'COMPLETE:';
1389 RETURN;
1390 END IF;
1391
1392 -- TIMEOUT mode
1393 IF (funcmode = 'TIMEOUT') then
1394 resultout := 'COMPLETE:';
1395 RETURN;
1396 END IF;
1397 */
1398 END attachment_exists;
1399
1400 ---------------------------------------------------------------------------
1401 -- Procedure layout_template_exists
1402 ---------------------------------------------------------------------------
1403 PROCEDURE layout_template_exists ( itemtype in varchar2,
1404 itemkey in varchar2,
1405 actid in number,
1406 funcmode in varchar2,
1407 resultout out nocopy varchar2 )
1408 IS
1409 l_template_id NUMBER;
1410 l_wf_seq_id NUMBER;
1411 l_dummy_var VARCHAR2(1);
1412
1413 CURSOR print_template_exits_csr(cp_tmpl_id IN NUMBER) IS
1414 SELECT 1
1415 FROM OKC_TERMS_TEMPLATES_ALL
1416 WHERE template_id = cp_tmpl_id
1417 AND print_template_id IS NOT NULL;
1418
1419 BEGIN
1420
1421 -- RUN mode - normal process execution
1422 IF (funcmode = 'RUN') then
1423
1424 l_template_id := wf_engine.GetItemAttrNumber(
1425 itemtype => itemtype,
1426 itemkey => itemkey,
1427 aname => 'TEMPLATE_ID' );
1428
1429 OPEN print_template_exits_csr(l_template_id);
1430 FETCH print_template_exits_csr INTO l_dummy_var;
1431 IF print_template_exits_csr%FOUND THEN
1432 resultout := 'COMPLETE:T';
1433 ELSE
1434 resultout := 'COMPLETE:F';
1435 END IF;
1436 CLOSE print_template_exits_csr;
1437
1438 RETURN;
1439 END IF;
1440
1441
1442 -- CANCEL mode -
1443 IF (funcmode = 'CANCEL') then
1444 resultout := 'COMPLETE:';
1445 RETURN;
1446 END IF;
1447
1448 -- TIMEOUT mode
1449 IF (funcmode = 'TIMEOUT') then
1450 resultout := 'COMPLETE:';
1451 RETURN;
1452 END IF;
1453
1454 END layout_template_exists;
1455
1456 ---------------------------------------------------------------------------
1457 -- Procedure get_template_url
1458 ---------------------------------------------------------------------------
1459 PROCEDURE get_template_url (
1460 itemtype in varchar2,
1461 itemkey in varchar2,
1462 actid in number,
1463 funcmode in varchar2,
1464 resultout out nocopy varchar2 )
1465 IS
1466 BEGIN
1467
1468 -- RUN mode - normal process execution
1469 IF (funcmode = 'RUN') then
1470 resultout := 'COMPLETE:';
1471 RETURN;
1472 END IF;
1473
1474 -- CANCEL mode - activity 'compensation'
1475 IF (funcmode = 'CANCEL') then
1476 resultout := 'COMPLETE:';
1477 RETURN;
1478 END IF;
1479
1480 -- TIMEOUT mode
1481 IF (funcmode = 'TIMEOUT') then
1482 resultout := 'COMPLETE:';
1483 RETURN;
1484 END IF;
1485 END get_template_url;
1486
1487 ---------------------------------------------------------------------------
1488 -- Procedure change_clause_status
1489 ---------------------------------------------------------------------------
1490 /* 11.5.10+
1491 new procedure to change the status of articles submitted with a template
1492 Fecthes the article versions from table OKC_TMPL_DRAFT_CLAUSES and then
1493 calls article bulk api's to do the actual status changes.
1494
1495 The following status changes are allowed
1496 DRAFT -> PENDING_APPROVAL
1497 PENDING_APPROVAL -> APPROVED/REJECTED
1498
1499 p_template_id Maps to document_id column in table OKC_TMPL_DRAFT_CLAUSES.
1500 p_wf_seq_id Maps to WF_SEQ_ID column in table OKC_TMPL_DRAFT_CLAUSES.
1501 p_status The status that the articles should be updated to,
1502 can be one of 3 values - 'PENDING_APPROVAL', 'APPROVED', 'REJECTED'.
1503 Error is thrown if the status is something else.
1504
1505 p_validation_level meaningful only for p_status = PENDING_APPROVAL.
1506 The pending approval blk api accepts a validation level parameter
1507 to either do complete or no validation. Passed as it is to the
1508 pending approval blk api.
1509
1510 x_validation_results If for any clauses fail the validation check the results
1511 are returned in this table
1512 */
1513 PROCEDURE change_clause_status (
1514 p_api_version IN NUMBER,
1515 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1516 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1517
1518 x_return_status OUT NOCOPY VARCHAR2,
1519 x_msg_data OUT NOCOPY VARCHAR2,
1520 x_msg_count OUT NOCOPY NUMBER,
1521
1522 p_template_id IN NUMBER,
1523 p_wf_seq_id IN NUMBER DEFAULT NULL,
1524 p_status IN VARCHAR2,
1525 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1526 x_validation_results OUT NOCOPY OKC_ART_BLK_PVT.validation_tbl_type)
1527
1528 IS
1529
1530 l_api_version CONSTANT NUMBER := 1;
1531 l_api_name CONSTANT VARCHAR2(30) := 'change_clause_status';
1532 l_current_org_id NUMBER;
1533 l_article_version_id_tbl OKC_ART_BLK_PVT.NUM_TBL_TYPE;
1534
1535 CURSOR l_tmpl_csr (cp_template_id IN NUMBER) IS
1536 SELECT org_id from OKC_TERMS_TEMPLATES_ALL
1537 WHERE template_id = cp_template_id;
1538
1539 CURSOR l_tmpl_clauses_csr (cp_template_id IN NUMBER, cp_wf_seq_id IN NUMBER) IS
1540 SELECT article_version_id from OKC_TMPL_DRAFT_CLAUSES
1541 WHERE template_id = cp_template_id
1542 AND nvl(wf_seq_id, -99) = nvl(cp_wf_seq_id, -99) --[p_wf_seq_id can be null]
1543 AND selected_yn = 'Y';
1544
1545 BEGIN
1546
1547 /*IF (l_debug = 'Y') THEN
1548 okc_debug.log('100: Entered OKC_TERMS_TMPL_APPROVAL_PVT.change_clause_status', 2);
1549 END IF;*/
1550
1551 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1552 FND_LOG.STRING(G_PROC_LEVEL,
1553 G_PKG_NAME, '100: Entered OKC_TERMS_TMPL_APPROVAL_PVT.change_clause_status' );
1554 END IF;
1555
1556 -- Standard Start of API savepoint
1557 SAVEPOINT change_clause_status_pvt;
1558
1559 -- Standard call to check for call compatibility.
1560 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1562 END IF;
1563
1564 -- Initialize message list if p_init_msg_list is set to TRUE.
1565 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1566 FND_MSG_PUB.initialize;
1567 END IF;
1568
1569 -- Initialize API return status to success
1570 x_return_status := FND_API.G_RET_STS_SUCCESS;
1571 l_article_version_id_tbl := OKC_ART_BLK_PVT.NUM_TBL_TYPE();
1572
1573 IF (p_status NOT IN ('PENDING_APPROVAL', 'APPROVED', 'REJECTED')) THEN
1574 x_return_status := FND_API.G_RET_STS_ERROR;
1575 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
1576 FND_MESSAGE.set_token('ARG_NAME', 'p_status');
1577 FND_MESSAGE.set_token('ARG_VALUE', p_status);
1578 FND_MSG_PUB.add;
1579 RAISE FND_API.G_EXC_ERROR;
1580 END IF;
1581
1582 OPEN l_tmpl_csr(p_template_id);
1583 FETCH l_tmpl_csr INTO l_current_org_id;
1584 IF l_tmpl_csr%NOTFOUND THEN
1585 x_return_status := FND_API.G_RET_STS_ERROR;
1586 CLOSE l_tmpl_csr;
1587 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
1588 FND_MESSAGE.set_token('ARG_NAME', 'p_template_id');
1589 FND_MESSAGE.set_token('ARG_VALUE', p_template_id);
1590 FND_MSG_PUB.add;
1591 RAISE FND_API.G_EXC_ERROR;
1592 END IF;
1593
1594 OPEN l_tmpl_clauses_csr(p_template_id, p_wf_seq_id);
1595 LOOP
1596 l_article_version_id_tbl.DELETE;
1597
1598 FETCH l_tmpl_clauses_csr BULK COLLECT INTO l_article_version_id_tbl LIMIT 100;
1599
1600 IF l_article_version_id_tbl.COUNT = 0 THEN
1601 EXIT;
1602 END IF;
1603
1604 IF (p_status = 'PENDING_APPROVAL') THEN
1605 OKC_ART_BLK_PVT.PENDING_APPROVAL_BLK(
1606 p_api_version => 1,
1607 p_init_msg_list => FND_API.G_FALSE,
1608 p_commit => FND_API.G_FALSE,
1609 p_validation_level => p_validation_level,
1610
1611 x_return_status => x_return_status,
1612 x_msg_count => x_msg_count,
1613 x_msg_data => x_msg_data,
1614
1615 p_org_id => l_current_org_id,
1616 p_art_ver_tbl => l_article_version_id_tbl,
1617 x_validation_results => x_validation_results);
1618 ELSIF(p_status = 'APPROVED') THEN
1619 OKC_ART_BLK_PVT.APPROVE_BLK(
1620 p_api_version => 1,
1621 p_init_msg_list => FND_API.G_FALSE,
1622 p_commit => FND_API.G_FALSE,
1623
1624 x_return_status => x_return_status,
1625 x_msg_count => x_msg_count,
1626 x_msg_data => x_msg_data,
1627
1628 p_org_id => l_current_org_id,
1629 p_art_ver_tbl => l_article_version_id_tbl,
1630 x_validation_results => x_validation_results);
1631 ELSIF(p_status = 'REJECTED') THEN
1632 OKC_ART_BLK_PVT.REJECT_BLK(
1633 p_api_version => 1,
1634 p_init_msg_list => FND_API.G_FALSE,
1635 p_commit => FND_API.G_FALSE,
1636
1637 x_return_status => x_return_status,
1638 x_msg_count => x_msg_count,
1639 x_msg_data => x_msg_data,
1640
1641 p_org_id => l_current_org_id,
1642 p_art_ver_tbl => l_article_version_id_tbl,
1643 x_validation_results => x_validation_results);
1644 END IF;
1645
1646 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1647 RAISE FND_API.G_EXC_ERROR ;
1648 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1650 END IF;
1651
1652 END LOOP;
1653
1654 IF l_tmpl_clauses_csr%ISOPEN THEN
1655 CLOSE l_tmpl_clauses_csr;
1656 END IF;
1657
1658
1659 -- Standard call to get message count and if count is 1, get message info.
1660 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1661
1662 IF(FND_API.to_boolean(p_commit)) THEN
1663 COMMIT;
1664 END IF;
1665
1666 /*IF (l_debug = 'Y') THEN
1667 okc_debug.log('1000: Leaving change_clause_status', 2);
1668 END IF;*/
1669
1670 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1671 FND_LOG.STRING(G_PROC_LEVEL,
1672 G_PKG_NAME, '1000: Leaving change_clause_status' );
1673 END IF;
1674
1675 EXCEPTION
1676
1677 WHEN FND_API.G_EXC_ERROR THEN
1678
1679 /*IF (l_debug = 'Y') THEN
1680 okc_debug.log('800: Leaving change_clause_status: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1681 END IF;*/
1682
1683 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1684 FND_LOG.STRING(G_EXCP_LEVEL,
1685 G_PKG_NAME, '800: Leaving change_clause_status: OKC_API.G_EXCEPTION_ERROR Exception' );
1686 END IF;
1687
1688 IF (l_tmpl_csr%ISOPEN) THEN
1689 CLOSE l_tmpl_csr;
1690 END IF;
1691 IF (l_tmpl_clauses_csr%ISOPEN) THEN
1692 CLOSE l_tmpl_clauses_csr;
1693 END IF;
1694
1695 ROLLBACK TO change_clause_status_pvt;
1696 x_return_status := G_RET_STS_ERROR ;
1697 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1698
1699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1700 /*IF (l_debug = 'Y') THEN
1701 okc_debug.log('900: Leaving change_clause_status: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1702 END IF;*/
1703
1704 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1705 FND_LOG.STRING(G_EXCP_LEVEL,
1706 G_PKG_NAME, '900: Leaving change_clause_status: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
1707 END IF;
1708
1709 IF (l_tmpl_csr%ISOPEN) THEN
1710 CLOSE l_tmpl_csr;
1711 END IF;
1712 IF (l_tmpl_clauses_csr%ISOPEN) THEN
1713 CLOSE l_tmpl_clauses_csr;
1714 END IF;
1715
1716 ROLLBACK TO change_clause_status_pvt;
1717 x_return_status := G_RET_STS_UNEXP_ERROR ;
1718 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1719
1720 WHEN OTHERS THEN
1721 /*IF (l_debug = 'Y') THEN
1722 okc_debug.log('1000: Leaving change_clause_status because of EXCEPTION: '||sqlerrm, 2);
1723 END IF;*/
1724
1725 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1726 FND_LOG.STRING(G_EXCP_LEVEL,
1727 G_PKG_NAME, '1000: Leaving change_clause_status because of EXCEPTION: '||sqlerrm );
1728 END IF;
1729
1730 IF (l_tmpl_csr%ISOPEN) THEN
1731 CLOSE l_tmpl_csr;
1732 END IF;
1733 IF (l_tmpl_clauses_csr%ISOPEN) THEN
1734 CLOSE l_tmpl_clauses_csr;
1735 END IF;
1736
1737 ROLLBACK TO change_clause_status_pvt;
1738 x_return_status := G_RET_STS_UNEXP_ERROR ;
1739 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1740 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1741 END IF;
1742 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1743 END change_clause_status;
1744
1745 ---------------------------------------------------------------------------
1746 -- Function get_error_string
1747 ---------------------------------------------------------------------------
1748 FUNCTION get_error_string(
1749 l_validation_results IN OKC_ART_BLK_PVT.validation_tbl_type) RETURN VARCHAR2
1750 IS
1751 l_error_msg VARCHAR2(4000);
1752
1753 BEGIN
1754
1755 IF l_validation_results.COUNT > 0 THEN
1756 FOR i IN l_validation_results.FIRST..l_validation_results.LAST LOOP
1757 l_error_msg := substrb( l_error_msg ||
1758 l_validation_results(i).article_title || ' ' ||
1759 l_validation_results(i).article_version_id || ' ' ||
1760 l_validation_results(i).error_message, 0,1333 );
1761 END LOOP;
1762 END if;
1763 RETURN l_error_msg;
1764 END;
1765
1766 PROCEDURE set_notified_list(
1767 itemtype in varchar2,
1768 itemkey in varchar2,
1769 actid in number,
1770 funcmode in varchar2,
1771 resultout out nocopy varchar2)
1772 IS
1773
1774 template_id number;
1775 l_tmpl_intent VARCHAR2(1);
1776
1777 CURSOR notified_csr(cp_tmpl_intent IN VARCHAR2, cp_global_org_id IN NUMBER) is
1778 SELECT decode(org_information1, 'Y', 'ADOPTED','AVAILABLE'),
1779 hr.organization_id,
1780 decode(cp_tmpl_intent,'S',org_information3,org_information7) org_information
1781 FROM hr_organization_units hr,
1782 hr_organization_information hri
1783 WHERE hri.organization_id = hr.organization_id
1784 and org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
1785 and hr.organization_id <> cp_global_org_id;
1786
1787
1788
1789 TYPE adoption_type_tbl IS TABLE OF VARCHAR(30) INDEX BY BINARY_INTEGER;
1790 TYPE organization_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1791 TYPE org_information3_tbl IS TABLE OF VARCHAR(150) INDEX BY BINARY_INTEGER;
1792
1793 adoption_type_lst adoption_type_tbl;
1794 organization_id_lst organization_id_tbl;
1795 org_information3_lst org_information3_tbl;
1796
1797 operation Wf_Engine.NameTabTyp;
1798 operation_list Wf_Engine.TextTabTyp;
1799 organization Wf_Engine.NameTabTyp;
1800 organization_list Wf_Engine.NumTabTyp;
1801 notified Wf_Engine.NameTabTyp;
1802 notified_list Wf_Engine.TextTabTyp;
1803 global_org_id NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1804 counter NUMBER;
1805
1806 BEGIN
1807 counter := 0;
1808 template_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'TEMPLATE_ID', false);
1809 l_tmpl_intent := wf_engine.getItemAttrNumber(itemtype, itemkey, 'TMPL_INTENT', false);
1810
1811 IF ( funcmode = 'RUN' ) THEN
1812 OPEN notified_csr(l_tmpl_intent , global_org_id);
1813 FETCH notified_csr BULK COLLECT INTO adoption_type_lst,organization_id_lst,org_information3_lst;
1814 CLOSE notified_csr;
1815
1816 IF adoption_type_lst.COUNT > 0 THEN
1817 FOR i IN adoption_type_lst.FIRST..adoption_type_lst.LAST LOOP
1818 counter := counter+1;
1819 operation(counter):= 'OPERATION_LIST$'||counter;
1820 operation_list(counter):= adoption_type_lst(i);
1821 organization(counter):= 'ORGANIZATION_LIST$'||counter;
1822 organization_list(counter):= organization_id_lst(i);
1823 notified(counter):= 'NOTIFIED_LIST$'||counter;
1824 notified_list(counter):= org_information3_lst(i);
1825 END LOOP;
1826
1827 wf_engine.AddItemAttrTextArray( itemtype, itemkey, operation, operation_list);
1828 wf_engine.AddItemAttrNumberArray( itemtype, itemkey, organization, organization_list);
1829 wf_engine.AddItemAttrTextArray( itemtype, itemkey, notified, notified_list);
1830 wf_engine.AddItemAttr(itemtype, itemkey, 'COUNTER$', null, counter, null);
1831 resultout := 'COMPLETE';
1832
1833 RETURN;
1834 END IF;
1835 END IF;
1836 EXCEPTION
1837
1838 WHEN OTHERS THEN
1839 WF_CORE.CONTEXT ( 'OKC_TERMS_TMPL_APPROVAL_PVT', 'set_notified_list', itemtype,
1840 itemkey, to_char(actid), funcmode);
1841 RAISE;
1842 END set_notified_list;
1843
1844
1845
1846 ---------------------------------------------------------------------------
1847 -- PROCEDURE set_notified
1848 ---------------------------------------------------------------------------
1849 --
1850 -- set_notified
1851 -- IN
1852 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1853 -- itemkey - A string generated from the application object's primary key.
1854 -- actid - The function activity(instance id).
1855 -- funcmode - Run/Cancel
1856 -- OUT
1857 -- Resultout - 'COMPLETE:'||operation' where operations is ADOPTED or AVAILABLE
1858 -- -'COMPLETE:UNDEFINED' if any error
1859 --
1860
1861 --
1862 PROCEDURE set_notified(
1863 itemtype in varchar2,
1864 itemkey in varchar2,
1865 actid in number,
1866 funcmode in varchar2,
1867 resultout out nocopy varchar2)
1868 IS
1869
1870 operation okc_article_adoptions.adoption_type%type;
1871 organization hr_organization_units_v.organization_id%type;
1872 notified hr_organization_information.org_information3%type;
1873
1874 org_name VARCHAR(240);
1875 wf_admin_user VARCHAR(320);
1876 counter NUMBER;
1877 adoption_type_desc fnd_lookup_values.meaning%type;
1878
1879 CURSOR org_name_csr(cp_org_id in number) IS
1880 SELECT name
1881 FROM hr_all_organization_units
1882 WHERE organization_id = cp_org_id;
1883
1884 CURSOR adoption_type_meaning_csr(cp_adoption_type in VARCHAR2) IS
1885 SELECT meaning
1886 FROM fnd_lookups
1887 WHERE lookup_type = 'OKC_ARTICLE_ADOPTION_TYPE'
1888 AND lookup_code = cp_adoption_type;
1889
1890 CURSOR wf_admin_csr IS
1891 SELECT user_name
1892 FROM wf_user_roles
1893 WHERE role_name ='SYSADMIN';
1894
1895 BEGIN
1896 IF (funcmode = 'RUN') THEN
1897 OPEN wf_admin_csr;
1898 FETCH wf_admin_csr INTO wf_admin_user;
1899 CLOSE wf_admin_csr;
1900
1901 counter := wf_engine.getItemAttrNumber(itemtype, itemkey, 'COUNTER$', false);
1902 IF counter > 0 THEN
1903 operation := wf_engine.getItemAttrText(itemtype, itemkey, 'OPERATION_LIST$'||counter, false);
1904 organization := wf_engine.getItemAttrNumber(itemtype, itemkey, 'ORGANIZATION_LIST$'||counter, false);
1905 notified := wf_engine.getItemAttrText(itemtype, itemkey, 'NOTIFIED_LIST$'||counter, false);
1906
1907 OPEN org_name_csr(organization);
1908 FETCH org_name_csr INTO org_name;
1909 CLOSE org_name_csr;
1910
1911 OPEN adoption_type_meaning_csr(operation);
1912 FETCH adoption_type_meaning_csr INTO adoption_type_desc;
1913 CLOSE adoption_type_meaning_csr;
1914
1915 if (org_name is null ) then
1916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1917 end if ;
1918
1919 if (adoption_type_desc is null ) then
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 end if ;
1922
1923 wf_engine.setItemAttrText(itemtype, itemkey, 'ADOPTION_DESC', adoption_type_desc);
1924 wf_engine.setItemAttrText(itemtype, itemkey, 'ADOPTION_TYPE', operation);
1925 wf_engine.setItemAttrText(itemtype, itemkey, 'LOCAL_ORG_NAME', org_name);
1926 wf_engine.setItemAttrNumber(itemtype, itemkey, 'LOCAL_ORG_ID', organization);
1927
1928 IF notified IS NOT NULL THEN
1929 wf_engine.setItemAttrText(itemtype, itemkey, 'LOCAL_ORG_APPROVER', notified);
1930 ELSE
1931 wf_engine.setItemAttrText(itemtype, itemkey, 'LOCAL_ORG_APPROVER', wf_admin_user);
1932 END IF;
1933
1934 resultout := 'COMPLETE:'||operation;
1935 ELSE
1936 resultout := 'COMPLETE:UNDEFINED';
1937 END IF;
1938
1939 RETURN;
1940 END IF; --if func mode is 'RUN'
1941 EXCEPTION
1942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1943 WF_CORE.CONTEXT ( 'OKC_TERMS_TMPL_APPROVAL_PVT', 'set_notified ', itemtype,
1944 itemkey, to_char(actid), funcmode);
1945 RAISE;
1946 WHEN OTHERS THEN
1947 WF_CORE.CONTEXT ( 'OKC_TERMS_TMPL_APPROVAL_PVT', 'set_notified', itemtype,
1948 itemkey, to_char(actid), funcmode);
1949 RAISE;
1950 END set_notified;
1951
1952 ---------------------------------------------------------------------------
1953 -- PROCEDURE decrement_counter
1954 ---------------------------------------------------------------------------
1955 --
1956 -- decrement_counter
1957 -- IN
1958 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1959 -- itemkey - A string generated from the application object's primary key.
1960 -- actid - The function activity(instance id).
1961 -- funcmode - Run/Cancel
1962 -- OUT
1963 -- Resultout - 'COMPLETE:T' if the counter is greater than 0, more notifications to be sent
1964 -- - 'COMPLETE:F' if the counter is 0 , no more notifications to be sent
1965 --
1966
1967 --
1968 PROCEDURE decrement_counter(
1969 itemtype in varchar2,
1970 itemkey in varchar2,
1971 actid in number,
1972 funcmode in varchar2,
1973 resultout out nocopy varchar2)
1974 IS
1975
1976 counter number;
1977
1978 BEGIN
1979 IF (funcmode = 'RUN') THEN
1980 counter := wf_engine.getItemAttrNumber(itemtype, itemkey, 'COUNTER$', false) - 1;
1981 IF counter > 0 THEN
1982 wf_engine.setItemAttrNumber(itemtype, itemkey, 'COUNTER$', counter);
1983 resultout := 'COMPLETE:T';
1984 ELSE
1985 resultout := 'COMPLETE:F';
1986 END IF;
1987
1988 RETURN;
1989 END IF; -- if funcmode = 'RUN'
1990 EXCEPTION
1991 WHEN OTHERS THEN
1992 WF_CORE.CONTEXT ( 'OKC_TERMS_TMPL_APPROVAL_PVT', 'decrement_counter', itemtype,
1993 itemkey, to_char(actid), funcmode);
1994 RAISE;
1995 END decrement_counter;
1996
1997 /*Bug 6329229*/
1998 ---------------------------------------------------------------------------
1999 -- PROCEDURE set_context_info
2000 ---------------------------------------------------------------------------
2001
2002 PROCEDURE set_context_info(
2003 itemtype in varchar2,
2004 itemkey in varchar2,
2005 actid in number,
2006 funcmode in varchar2,
2007 resultout out nocopy varchar2)
2008 IS
2009
2010 begin
2011 resultout := NULL;
2012
2013 selector(itemtype => itemtype,
2014 itemkey => itemkey,
2015 actid => actid,
2016 funcmode => 'SET_CTX',
2017 resultout => resultout);
2018
2019 return;
2020
2021 end set_context_info;
2022
2023 ---------------------------------------------------------------------------
2024 -- PROCEDURE global_articles_exist
2025 ---------------------------------------------------------------------------
2026 --
2027 -- global_articles_exist
2028 -- IN
2029 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2030 -- itemkey - A string generated from the application object's primary key.
2031 -- actid - The function activity(instance id).
2032 -- funcmode - Run/Cancel
2033 -- OUT
2034 -- Resultout - 'COMPLETE:T' if global articles exist in the template
2035 -- - 'COMPLETE:F' if current org is not a global org or no global articles exist
2036 --
2037 PROCEDURE global_articles_exist(
2038 itemtype in varchar2,
2039 itemkey in varchar2,
2040 actid in number,
2041 funcmode in varchar2,
2042 resultout out nocopy varchar2)
2043 IS
2044 template_id NUMBER;
2045 wf_seq_id NUMBER;
2046 current_org_id NUMBER;
2047 l_rowfound BOOLEAN := FALSE;
2048 GLOBAL_ORG_ID NUMBER := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
2049 l_dummy NUMBER;
2050 l_org_count NUMBER;
2051
2052 CURSOR global_articles_csr(cp_template_id in number,cp_wf_seq_id IN NUMBER) IS
2053 SELECT 1
2054 FROM okc_article_versions oav,
2055 okc_tmpl_draft_clauses otdc
2056 WHERE otdc.template_id = cp_template_id
2057 AND otdc.wf_seq_id = cp_wf_seq_id
2058 AND otdc.article_version_id = oav.article_version_id
2059 AND oav.global_yn = 'Y'
2060 AND ROWNUM < 2;
2061
2062 BEGIN
2063 IF (funcmode = 'RUN') THEN
2064 resultout := 'COMPLETE:F';
2065 current_org_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'ORG_ID', false);
2066 template_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'TEMPLATE_ID', false);
2067 wf_seq_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'TMPL_WF_SEQ_ID', false);
2068
2069 IF (current_org_id = GLOBAL_ORG_ID ) THEN
2070
2071 OPEN global_articles_csr(template_id,wf_seq_id);
2072 FETCH global_articles_csr INTO l_dummy;
2073 l_rowfound := global_articles_csr%FOUND;
2074 CLOSE global_articles_csr;
2075
2076 IF (l_rowfound) THEN
2077 --resultout := 'COMPLETE:T';
2078 -- bug fix 14822902 start - SKAVUTHA
2079 -- if only one org exists in the setup, then return COMPLETE:F
2080 SELECT Count(1)
2081 INTO l_org_count
2082 FROM hr_organization_units hr,
2083 hr_organization_information hri
2084 WHERE hri.organization_id = hr.organization_id
2085 and org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
2086 and hr.organization_id <> global_org_id;
2087
2088 IF l_org_count = 0 THEN
2089 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2090 FND_LOG.STRING(G_PROC_LEVEL,
2091 G_PKG_NAME, 'NO active local orgs exist.' );
2092 END IF;
2093
2094 resultout := 'COMPLETE:F';
2095 ELSE
2096 resultout := 'COMPLETE:T';
2097 END IF;
2098 ELSE
2099 resultout := 'COMPLETE:F';
2100 END IF;
2101
2102 ELSE
2103 resultout := 'COMPLETE:F';
2104 END IF;
2105
2106 RETURN;
2107 END IF; -- if funcmode = 'RUN'
2108 EXCEPTION
2109 WHEN OTHERS THEN
2110 WF_CORE.CONTEXT ( 'OKC_TERMS_TMPL_APPROVAL_PVT', 'global_articles_exist', itemtype,
2111 itemkey, to_char(actid), funcmode);
2112 RAISE;
2113 END global_articles_exist;
2114
2115 ---------------------------------------------------------------------------
2116 -- PROCEDURE select_draft_clauses
2117 ---------------------------------------------------------------------------
2118 --
2119 -- global_articles_exist
2120 -- IN
2121 -- p_template_id - A valid template id for which all draft clauses
2122 -- in table OKC_TMPL_DRAFT_CLAUSES will have the selected_yn flag set to Y
2123 --
2124 PROCEDURE select_draft_clauses(
2125 p_api_version IN NUMBER,
2126 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2127 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2128
2129 x_return_status OUT NOCOPY VARCHAR2,
2130 x_msg_data OUT NOCOPY VARCHAR2,
2131 x_msg_count OUT NOCOPY NUMBER,
2132
2133 p_template_id IN NUMBER)
2134 IS
2135 l_api_version CONSTANT NUMBER := 1;
2136 l_api_name CONSTANT VARCHAR2(30) := 'select_draft_clauses';
2137
2138 BEGIN
2139
2140 /*IF (l_debug = 'Y') THEN
2141 okc_debug.log('100: Entered OKC_TERMS_TMPL_APPROVAL_PVT.select_draft_clauses, p_template_id' || p_template_id, 2);
2142 END IF;*/
2143
2144 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2145 FND_LOG.STRING(G_PROC_LEVEL,
2146 G_PKG_NAME, '100: Entered OKC_TERMS_TMPL_APPROVAL_PVT.select_draft_clauses, p_template_id' || p_template_id );
2147 END IF;
2148
2149 -- Standard Start of API savepoint
2150 SAVEPOINT select_draft_clauses_pvt;
2151
2152 -- Standard call to check for call compatibility.
2153 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2155 END IF;
2156
2157 -- Initialize message list if p_init_msg_list is set to TRUE.
2158 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2159 FND_MSG_PUB.initialize;
2160 END IF;
2161
2162 -- Initialize API return status to success
2163 x_return_status := FND_API.G_RET_STS_SUCCESS;
2164
2165 UPDATE OKC_TMPL_DRAFT_CLAUSES
2166 SET selected_yn = 'Y'
2167 WHERE template_id = (
2168 select template_id from okc_terms_templates_all
2169 where template_id = p_template_id and status_code in ('DRAFT', 'REJECTED', 'REVISION'));
2170
2171 -- Standard call to get message count and if count is 1, get message info.
2172 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2173
2174 IF(FND_API.to_boolean(p_commit)) THEN
2175 COMMIT;
2176 END IF;
2177
2178 /*IF (l_debug = 'Y') THEN
2179 okc_debug.log('1000: Leaving select_draft_clauses', 2);
2180 END IF;*/
2181
2182 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2183 FND_LOG.STRING(G_PROC_LEVEL,
2184 G_PKG_NAME, '1000: Leaving select_draft_clauses' );
2185 END IF;
2186
2187 EXCEPTION
2188
2189 WHEN FND_API.G_EXC_ERROR THEN
2190
2191 /*IF (l_debug = 'Y') THEN
2192 okc_debug.log('800: Leaving select_draft_clauses: OKC_API.G_EXCEPTION_ERROR Exception', 2);
2193 END IF;*/
2194
2195 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2196 FND_LOG.STRING(G_EXCP_LEVEL,
2197 G_PKG_NAME, '800: Leaving select_draft_clauses: OKC_API.G_EXCEPTION_ERROR Exception' );
2198 END IF;
2199
2200 ROLLBACK TO change_clause_status_pvt;
2201 x_return_status := G_RET_STS_ERROR ;
2202 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2203
2204 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2205 /*IF (l_debug = 'Y') THEN
2206 okc_debug.log('900: Leaving select_draft_clauses: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2207 END IF;*/
2208
2209 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2210 FND_LOG.STRING(G_EXCP_LEVEL,
2211 G_PKG_NAME, '900: Leaving select_draft_clauses: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
2212 END IF;
2213
2214 ROLLBACK TO change_clause_status_pvt;
2215 x_return_status := G_RET_STS_UNEXP_ERROR ;
2216 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2217
2218 WHEN OTHERS THEN
2219 /*IF (l_debug = 'Y') THEN
2220 okc_debug.log('1000: Leaving select_draft_clauses because of EXCEPTION: '||sqlerrm, 2);
2221 END IF;*/
2222
2223 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2224 FND_LOG.STRING(G_EXCP_LEVEL,
2225 G_PKG_NAME, '1000: Leaving select_draft_clauses because of EXCEPTION: '||sqlerrm );
2226 END IF;
2227
2228 ROLLBACK TO change_clause_status_pvt;
2229 x_return_status := G_RET_STS_UNEXP_ERROR ;
2230 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2231 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2232 END IF;
2233 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2234 END select_draft_clauses;
2235
2236 end OKC_TERMS_TMPL_APPROVAL_PVT;