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