[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_TEMPLATES_PVT
Source
1 PACKAGE BODY OKC_TERMS_TEMPLATES_PVT AS
2 /* $Header: OKCVTERMTMPLB.pls 120.3.12020000.2 2013/01/31 08:09:23 skavutha ship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
10 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
11 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
12 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
13 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
14 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
15 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
16 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
17 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
18 ---------------------------------------------------------------------------
19 -- VALIDATION LEVELS
20 ---------------------------------------------------------------------------
21 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
22 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
23 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
24 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
25 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
26 ---------------------------------------------------------------------------
27 -- GLOBAL VARIABLES
28 ---------------------------------------------------------------------------
29 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMTMPL_PVT';
30 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
31 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
32
33 ------------------------------------------------------------------------------
34 -- GLOBAL CONSTANTS
35 ------------------------------------------------------------------------------
36 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
37 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
38 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
39 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
40 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
41
42 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
44 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
45
46 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
47 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
48 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
49
50 ---------------------------------------------------------------------------
51 -- FUNCTION get_seq_id
52 ---------------------------------------------------------------------------
53 FUNCTION Get_Seq_Id (
54 p_template_id IN NUMBER,
55 x_template_id OUT NOCOPY NUMBER
56 ) RETURN VARCHAR2 IS
57 l_api_name CONSTANT VARCHAR2(30) := 'Get_Seq_Id';
58 CURSOR l_seq_csr IS
59 SELECT OKC_TERMS_TEMPLATES_ALL_S.NEXTVAL FROM DUAL;
60 BEGIN
61 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
62 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered get_seq_id');
63 END IF;
64
65 IF( p_template_id IS NULL ) THEN
66 OPEN l_seq_csr;
67 FETCH l_seq_csr INTO x_template_id ;
68 IF l_seq_csr%NOTFOUND THEN
69 RAISE NO_DATA_FOUND;
70 END IF;
71 CLOSE l_seq_csr;
72 END IF;
73
74 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
75 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Leaving get_seq_id');
76 END IF;
77 RETURN G_RET_STS_SUCCESS;
78 EXCEPTION
79 WHEN OTHERS THEN
80
81 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
82 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm);
83 END IF;
84
85 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
86 p_msg_name => G_UNEXPECTED_ERROR,
87 p_token1 => G_SQLCODE_TOKEN,
88 p_token1_value => sqlcode,
89 p_token2 => G_SQLERRM_TOKEN,
90 p_token2_value => sqlerrm);
91
92 IF l_seq_csr%ISOPEN THEN
93 CLOSE l_seq_csr;
94 END IF;
95
96 RETURN G_RET_STS_UNEXP_ERROR ;
97
98 END Get_Seq_Id;
99
100 ---------------------------------------------------------------------------
101 -- FUNCTION get_rec for: OKC_TERMS_TEMPLATES_ALL
102 ---------------------------------------------------------------------------
103 FUNCTION Get_Rec (
104 p_template_id IN NUMBER,
105
106 x_template_name OUT NOCOPY VARCHAR2,
107 x_working_copy_flag OUT NOCOPY VARCHAR2,
108 x_intent OUT NOCOPY VARCHAR2,
109 x_status_code OUT NOCOPY VARCHAR2,
110 x_start_date OUT NOCOPY DATE,
111 x_end_date OUT NOCOPY DATE,
112 x_global_flag OUT NOCOPY VARCHAR2,
113 x_parent_template_id OUT NOCOPY NUMBER,
114 x_print_template_id OUT NOCOPY NUMBER,
115 x_contract_expert_enabled OUT NOCOPY VARCHAR2,
116 x_xprt_clause_mandatory_flag OUT NOCOPY VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
117 x_xprt_scn_code OUT NOCOPY VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
118 x_template_model_id OUT NOCOPY NUMBER,
119 x_instruction_text OUT NOCOPY VARCHAR2,
120 x_tmpl_numbering_scheme OUT NOCOPY NUMBER,
121 x_description OUT NOCOPY VARCHAR2,
122 x_approval_wf_key OUT NOCOPY VARCHAR2,
123 x_cz_export_wf_key OUT NOCOPY VARCHAR2,
124 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
125 x_orig_system_reference_id1 OUT NOCOPY NUMBER,
126 x_orig_system_reference_id2 OUT NOCOPY NUMBER,
127 x_org_id OUT NOCOPY NUMBER,
128 x_attribute_category OUT NOCOPY VARCHAR2,
129 x_attribute1 OUT NOCOPY VARCHAR2,
130 x_attribute2 OUT NOCOPY VARCHAR2,
131 x_attribute3 OUT NOCOPY VARCHAR2,
132 x_attribute4 OUT NOCOPY VARCHAR2,
133 x_attribute5 OUT NOCOPY VARCHAR2,
134 x_attribute6 OUT NOCOPY VARCHAR2,
135 x_attribute7 OUT NOCOPY VARCHAR2,
136 x_attribute8 OUT NOCOPY VARCHAR2,
137 x_attribute9 OUT NOCOPY VARCHAR2,
138 x_attribute10 OUT NOCOPY VARCHAR2,
139 x_attribute11 OUT NOCOPY VARCHAR2,
140 x_attribute12 OUT NOCOPY VARCHAR2,
141 x_attribute13 OUT NOCOPY VARCHAR2,
142 x_attribute14 OUT NOCOPY VARCHAR2,
143 x_attribute15 OUT NOCOPY VARCHAR2,
144 x_object_version_number OUT NOCOPY NUMBER,
145 x_created_by OUT NOCOPY NUMBER,
146 x_creation_date OUT NOCOPY DATE,
147 x_last_updated_by OUT NOCOPY NUMBER,
148 x_last_update_login OUT NOCOPY NUMBER,
149 x_last_update_date OUT NOCOPY DATE,
150 x_translated_from_tmpl_id OUT NOCOPY NUMBER,
151 x_language OUT NOCOPY VARCHAR2
152
153 ) RETURN VARCHAR2 IS
154 l_api_name CONSTANT VARCHAR2(30) := 'get_rec';
155 CURSOR OKC_TERMS_TEMPLATES_ALL_pk_csr (cp_template_id IN NUMBER) IS
156 SELECT
157 TEMPLATE_NAME,
158 WORKING_COPY_FLAG,
159 INTENT,
160 STATUS_CODE,
161 START_DATE,
162 END_DATE,
163 GLOBAL_FLAG,
164 PARENT_TEMPLATE_ID,
165 PRINT_TEMPLATE_ID,
166 CONTRACT_EXPERT_ENABLED,
167 XPRT_CLAUSE_MANDATORY_FLAG, -- Added for 11.5.10+ : Contract Expert Changes
168 XPRT_SCN_CODE, -- Added for 11..510+ : Contract Expert Changes
169 TEMPLATE_MODEL_ID,
170 INSTRUCTION_TEXT,
171 TMPL_NUMBERING_SCHEME,
172 DESCRIPTION,
173 APPROVAL_WF_KEY,
174 CZ_EXPORT_WF_KEY,
175 ORIG_SYSTEM_REFERENCE_CODE,
176 ORIG_SYSTEM_REFERENCE_ID1,
177 ORIG_SYSTEM_REFERENCE_ID2,
178 ORG_ID,
179 ATTRIBUTE_CATEGORY,
180 ATTRIBUTE1,
181 ATTRIBUTE2,
182 ATTRIBUTE3,
183 ATTRIBUTE4,
184 ATTRIBUTE5,
185 ATTRIBUTE6,
186 ATTRIBUTE7,
187 ATTRIBUTE8,
188 ATTRIBUTE9,
189 ATTRIBUTE10,
190 ATTRIBUTE11,
191 ATTRIBUTE12,
192 ATTRIBUTE13,
193 ATTRIBUTE14,
194 ATTRIBUTE15,
195 OBJECT_VERSION_NUMBER,
196 CREATED_BY,
197 CREATION_DATE,
198 LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN,
200 LAST_UPDATE_DATE,
201 --MLS for templates
202 TRANSLATED_FROM_TMPL_ID,
203 LANGUAGE
204 FROM OKC_TERMS_TEMPLATES_ALL t
205 WHERE t.TEMPLATE_ID = cp_template_id;
206 BEGIN
207
208 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
209 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Entered get_rec');
210 END IF;
211
212 -- Get current database values
213 OPEN OKC_TERMS_TEMPLATES_ALL_pk_csr (p_template_id);
214 FETCH OKC_TERMS_TEMPLATES_ALL_pk_csr INTO
215 x_template_name,
216 x_working_copy_flag,
217 x_intent,
218 x_status_code,
219 x_start_date,
220 x_end_date,
221 x_global_flag,
222 x_parent_template_id,
223 x_print_template_id,
224 x_contract_expert_enabled,
225 x_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
226 x_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
227 x_template_model_id,
228 x_instruction_text,
229 x_tmpl_numbering_scheme,
230 x_description,
231 x_approval_wf_key,
232 x_cz_export_wf_key,
233 x_orig_system_reference_code,
234 x_orig_system_reference_id1,
235 x_orig_system_reference_id2,
236 x_org_id,
237 x_attribute_category,
238 x_attribute1,
239 x_attribute2,
240 x_attribute3,
241 x_attribute4,
242 x_attribute5,
243 x_attribute6,
244 x_attribute7,
245 x_attribute8,
246 x_attribute9,
247 x_attribute10,
248 x_attribute11,
249 x_attribute12,
250 x_attribute13,
251 x_attribute14,
252 x_attribute15,
253 x_object_version_number,
254 x_created_by,
255 x_creation_date,
256 x_last_updated_by,
257 x_last_update_login,
258 x_last_update_date,
259 --MLS for templates
260 x_translated_from_tmpl_id,
261 x_language ;
262 IF OKC_TERMS_TEMPLATES_ALL_pk_csr%NOTFOUND THEN
263 RAISE NO_DATA_FOUND;
264 END IF;
265 CLOSE OKC_TERMS_TEMPLATES_ALL_pk_csr;
266
267 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
268 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'500: Leaving get_rec ');
269 END IF;
270
271 RETURN G_RET_STS_SUCCESS ;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275
276 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
277 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving get_rec because of EXCEPTION: '||sqlerrm);
278 END IF;
279
280 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
281 p_msg_name => G_UNEXPECTED_ERROR,
282 p_token1 => G_SQLCODE_TOKEN,
283 p_token1_value => sqlcode,
284 p_token2 => G_SQLERRM_TOKEN,
285 p_token2_value => sqlerrm);
286
287 IF OKC_TERMS_TEMPLATES_ALL_pk_csr%ISOPEN THEN
288 CLOSE OKC_TERMS_TEMPLATES_ALL_pk_csr;
289 END IF;
290
291 RETURN G_RET_STS_UNEXP_ERROR ;
292
293 END Get_Rec;
294
295 -----------------------------------------
296 -- Set_Attributes for:OKC_TERMS_TEMPLATES_ALL --
297 -----------------------------------------
298 FUNCTION Set_Attributes(
299 p_template_name IN VARCHAR2,
300 p_template_id IN NUMBER,
301 p_working_copy_flag IN VARCHAR2,
302 p_intent IN VARCHAR2,
303 p_status_code IN VARCHAR2,
304 p_start_date IN DATE,
305 p_end_date IN DATE,
306 p_global_flag IN VARCHAR2,
307 p_parent_template_id IN NUMBER,
308 p_print_template_id IN NUMBER,
309 p_contract_expert_enabled IN VARCHAR2,
310 p_cls_enabled IN VARCHAR2,
311 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+ : Contract Expert Changes
312 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+ : Contract Expert Changes
313 p_template_model_id IN NUMBER,
314 p_instruction_text IN VARCHAR2,
315 p_tmpl_numbering_scheme IN NUMBER,
316 p_description IN VARCHAR2,
317 p_approval_wf_key IN VARCHAR2,
318 p_cz_export_wf_key IN VARCHAR2,
319 p_orig_system_reference_code IN VARCHAR2,
320 p_orig_system_reference_id1 IN NUMBER,
321 p_orig_system_reference_id2 IN NUMBER,
322 p_org_id IN NUMBER,
323 p_attribute_category IN VARCHAR2,
324 p_attribute1 IN VARCHAR2,
325 p_attribute2 IN VARCHAR2,
326 p_attribute3 IN VARCHAR2,
327 p_attribute4 IN VARCHAR2,
328 p_attribute5 IN VARCHAR2,
329 p_attribute6 IN VARCHAR2,
330 p_attribute7 IN VARCHAR2,
331 p_attribute8 IN VARCHAR2,
332 p_attribute9 IN VARCHAR2,
333 p_attribute10 IN VARCHAR2,
334 p_attribute11 IN VARCHAR2,
335 p_attribute12 IN VARCHAR2,
336 p_attribute13 IN VARCHAR2,
337 p_attribute14 IN VARCHAR2,
338 p_attribute15 IN VARCHAR2,
339 p_translated_from_tmpl_id IN NUMBER,
340 --MLS for templates
341 p_language IN VARCHAR2,
342 p_object_version_number IN NUMBER,
343
344 x_template_name OUT NOCOPY VARCHAR2,
345 x_working_copy_flag OUT NOCOPY VARCHAR2,
346 x_intent OUT NOCOPY VARCHAR2,
347 x_status_code OUT NOCOPY VARCHAR2,
348 x_start_date OUT NOCOPY DATE,
349 x_end_date OUT NOCOPY DATE,
350 x_global_flag OUT NOCOPY VARCHAR2,
351 x_parent_template_id OUT NOCOPY NUMBER,
352 x_print_template_id OUT NOCOPY NUMBER,
353 x_contract_expert_enabled OUT NOCOPY VARCHAR2,
354 x_cls_enabled OUT NOCOPY VARCHAR2,
355 x_xprt_clause_mandatory_flag OUT NOCOPY VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
356 x_xprt_scn_code OUT NOCOPY VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
357 x_template_model_id OUT NOCOPY NUMBER,
358 x_instruction_text OUT NOCOPY VARCHAR2,
359 x_tmpl_numbering_scheme OUT NOCOPY NUMBER,
360 x_description OUT NOCOPY VARCHAR2,
361 x_approval_wf_key OUT NOCOPY VARCHAR2,
362 x_cz_export_wf_key OUT NOCOPY VARCHAR2,
363 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
364 x_orig_system_reference_id1 OUT NOCOPY NUMBER,
365 x_orig_system_reference_id2 OUT NOCOPY NUMBER,
366 x_org_id OUT NOCOPY NUMBER,
367 x_attribute_category OUT NOCOPY VARCHAR2,
368 x_attribute1 OUT NOCOPY VARCHAR2,
369 x_attribute2 OUT NOCOPY VARCHAR2,
370 x_attribute3 OUT NOCOPY VARCHAR2,
371 x_attribute4 OUT NOCOPY VARCHAR2,
372 x_attribute5 OUT NOCOPY VARCHAR2,
373 x_attribute6 OUT NOCOPY VARCHAR2,
374 x_attribute7 OUT NOCOPY VARCHAR2,
375 x_attribute8 OUT NOCOPY VARCHAR2,
376 x_attribute9 OUT NOCOPY VARCHAR2,
377 x_attribute10 OUT NOCOPY VARCHAR2,
378 x_attribute11 OUT NOCOPY VARCHAR2,
379 x_attribute12 OUT NOCOPY VARCHAR2,
380 x_attribute13 OUT NOCOPY VARCHAR2,
381 x_attribute14 OUT NOCOPY VARCHAR2,
382 x_attribute15 OUT NOCOPY VARCHAR2,
383 x_translated_from_tmpl_id OUT NOCOPY NUMBER,
384 x_language OUT NOCOPY VARCHAR2
385 ) RETURN VARCHAR2 IS
386 l_api_name CONSTANT VARCHAR2(30) := 'set_attributes';
387 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
388 l_object_version_number OKC_TERMS_TEMPLATES_ALL.OBJECT_VERSION_NUMBER%TYPE;
389 l_created_by OKC_TERMS_TEMPLATES_ALL.CREATED_BY%TYPE;
390 l_creation_date OKC_TERMS_TEMPLATES_ALL.CREATION_DATE%TYPE;
391 l_last_updated_by OKC_TERMS_TEMPLATES_ALL.LAST_UPDATED_BY%TYPE;
392 l_last_update_login OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_LOGIN%TYPE;
393 l_last_update_date OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_DATE%TYPE;
394 BEGIN
395 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
396 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Entered Set_Attributes ');
397 END IF;
398
399 IF( p_template_id IS NOT NULL ) THEN
400 -- Get current database values
401 l_return_status := Get_Rec(
402 p_template_id => p_template_id,
403 x_template_name => x_template_name,
404 x_working_copy_flag => x_working_copy_flag,
405 x_intent => x_intent,
406 x_status_code => x_status_code,
407 x_start_date => x_start_date,
408 x_end_date => x_end_date,
409 x_global_flag => x_global_flag,
410 x_parent_template_id => x_parent_template_id,
411 x_print_template_id => x_print_template_id,
412 x_contract_expert_enabled => x_contract_expert_enabled,
413 x_xprt_clause_mandatory_flag => x_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
414 x_xprt_scn_code => x_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
415 x_template_model_id => x_template_model_id,
416 x_instruction_text => x_instruction_text,
417 x_tmpl_numbering_scheme => x_tmpl_numbering_scheme,
418 x_description => x_description,
419 x_approval_wf_key => x_approval_wf_key,
420 x_cz_export_wf_key => x_cz_export_wf_key,
421 x_orig_system_reference_code => x_orig_system_reference_code,
422 x_orig_system_reference_id1 => x_orig_system_reference_id1,
423 x_orig_system_reference_id2 => x_orig_system_reference_id2,
424 x_org_id => x_org_id,
425 x_attribute_category => x_attribute_category,
426 x_attribute1 => x_attribute1,
427 x_attribute2 => x_attribute2,
428 x_attribute3 => x_attribute3,
429 x_attribute4 => x_attribute4,
430 x_attribute5 => x_attribute5,
431 x_attribute6 => x_attribute6,
432 x_attribute7 => x_attribute7,
433 x_attribute8 => x_attribute8,
434 x_attribute9 => x_attribute9,
435 x_attribute10 => x_attribute10,
436 x_attribute11 => x_attribute11,
437 x_attribute12 => x_attribute12,
438 x_attribute13 => x_attribute13,
439 x_attribute14 => x_attribute14,
440 x_attribute15 => x_attribute15,
441 x_object_version_number => l_object_version_number,
442 x_created_by => l_created_by,
443 x_creation_date => l_creation_date,
444 x_last_updated_by => l_last_updated_by,
445 x_last_update_login => l_last_update_login,
446 x_last_update_date => l_last_update_date,
447 x_translated_from_tmpl_id => x_translated_from_tmpl_id,
448 x_language => x_language );
449 --- If any errors happen abort API
450 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
452 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
453 RAISE FND_API.G_EXC_ERROR;
454 END IF;
455
456 --- Reversing G_MISS/NULL values logic
457
458 IF (p_template_name = G_MISS_CHAR) THEN
459 x_template_name := NULL;
460 ELSIF (p_TEMPLATE_NAME IS NOT NULL) THEN
461 x_template_name := p_template_name;
462 END IF;
463
464 IF (p_working_copy_flag = G_MISS_CHAR) THEN
465 x_working_copy_flag := NULL;
466 ELSIF (p_WORKING_COPY_FLAG IS NOT NULL) THEN
467 x_working_copy_flag := p_working_copy_flag;
468 END IF;
469
470 IF (p_intent = G_MISS_CHAR) THEN
471 x_intent := NULL;
472 ELSIF (p_INTENT IS NOT NULL) THEN
473 x_intent := p_intent;
474 END IF;
475
476 IF (p_status_code = G_MISS_CHAR) THEN
477 x_status_code := NULL;
478 ELSIF (p_STATUS_CODE IS NOT NULL) THEN
479 x_status_code := p_status_code;
480 END IF;
481
482 IF (p_start_date = G_MISS_DATE) THEN
483 x_start_date := NULL;
484 ELSIF (p_START_DATE IS NOT NULL) THEN
485 x_start_date := p_start_date;
486 END IF;
487
488 IF (p_end_date = G_MISS_DATE) THEN
489 x_end_date := NULL;
490 ELSIF (p_END_DATE IS NOT NULL) THEN
491 x_end_date := p_end_date;
492 END IF;
493
494 IF (p_global_flag = G_MISS_CHAR) THEN
495 x_global_flag := NULL;
496 ELSIF (p_GLOBAL_FLAG IS NOT NULL) THEN
497 x_global_flag := p_global_flag;
498 END IF;
499
500 IF (p_parent_template_id = G_MISS_NUM) THEN
501 x_parent_template_id := NULL;
502 ELSIF (p_PARENT_TEMPLATE_ID IS NOT NULL) THEN
503 x_parent_template_id := p_parent_template_id;
504 END IF;
505
506 IF (p_print_template_id = G_MISS_NUM) THEN
507 x_print_template_id := NULL;
508 ELSIF (p_PRINT_TEMPLATE_ID IS NOT NULL) THEN
509 x_print_template_id := p_print_template_id;
510 END IF;
511
512 IF (p_contract_expert_enabled = G_MISS_CHAR) THEN
513 x_contract_expert_enabled := NULL;
514 ELSIF (p_CONTRACT_EXPERT_ENABLED IS NOT NULL) THEN
515 x_contract_expert_enabled := p_contract_expert_enabled;
516 END IF;
517
518 IF (p_cls_enabled = G_MISS_CHAR) THEN
519 x_cls_enabled := NULL;
520 ELSIF (p_CONTRACT_EXPERT_ENABLED IS NOT NULL) THEN
521 x_cls_enabled := p_cls_enabled;
522 END IF;
523
524 IF (p_xprt_clause_mandatory_flag = G_MISS_CHAR) THEN
525 x_xprt_clause_mandatory_flag := NULL;
526 ELSIF (p_xprt_clause_mandatory_flag IS NOT NULL) THEN
527 x_xprt_clause_mandatory_flag := p_xprt_clause_mandatory_flag;
528 END IF;
529
530 IF (p_xprt_scn_code = G_MISS_CHAR) THEN
531 x_xprt_scn_code:= NULL;
532 ELSIF (p_xprt_scn_code IS NOT NULL) THEN
533 x_xprt_scn_code := p_xprt_scn_code;
534 END IF;
535 IF (p_template_model_id = G_MISS_NUM) THEN
536 x_template_model_id := NULL;
537 ELSIF (p_TEMPLATE_MODEL_ID IS NOT NULL) THEN
538 x_template_model_id := p_template_model_id;
539 END IF;
540
541 IF (p_instruction_text = G_MISS_CHAR) THEN
542 x_instruction_text := NULL;
543 ELSIF (p_INSTRUCTION_TEXT IS NOT NULL) THEN
544 x_instruction_text := p_instruction_text;
545 END IF;
546
547 IF (p_tmpl_numbering_scheme = G_MISS_NUM) THEN
548 x_tmpl_numbering_scheme := NULL;
549 ELSIF (p_TMPL_NUMBERING_SCHEME IS NOT NULL) THEN
550 x_tmpl_numbering_scheme := p_tmpl_numbering_scheme;
551 END IF;
552
553 IF (p_description = G_MISS_CHAR) THEN
554 x_description := NULL;
555 ELSIF (p_DESCRIPTION IS NOT NULL) THEN
556 x_description := p_description;
557 END IF;
558
559 IF (p_approval_wf_key = G_MISS_CHAR) THEN
560 x_approval_wf_key := NULL;
561 ELSIF (p_approval_wf_key IS NOT NULL) THEN
562 x_approval_wf_key := p_approval_wf_key;
563 END IF;
564
565 IF (p_cz_export_wf_key = G_MISS_CHAR) THEN
566 x_cz_export_wf_key := NULL;
567 ELSIF (p_cz_export_wf_key IS NOT NULL) THEN
568 x_cz_export_wf_key := p_cz_export_wf_key;
569 END IF;
570
571 IF (p_org_id = G_MISS_NUM) THEN
572 x_org_id := NULL;
573 ELSIF (p_ORG_ID IS NOT NULL) THEN
574 x_org_id := p_org_id;
575 END IF;
576
577 IF (p_orig_system_reference_code = G_MISS_CHAR) THEN
578 x_orig_system_reference_code := NULL;
579 ELSIF (p_orig_system_reference_code IS NOT NULL) THEN
580 x_orig_system_reference_code := p_orig_system_reference_code;
581 END IF;
582
583 IF (p_orig_system_reference_id1 = G_MISS_NUM) THEN
584 x_orig_system_reference_id1 := NULL;
585 ELSIF (p_orig_system_reference_id1 IS NOT NULL) THEN
586 x_orig_system_reference_id1 := p_orig_system_reference_id1;
587 END IF;
588
589 IF (p_orig_system_reference_id2 = G_MISS_NUM) THEN
590 x_orig_system_reference_id2 := NULL;
591 ELSIF (p_orig_system_reference_id2 IS NOT NULL) THEN
592 x_orig_system_reference_id2 := p_orig_system_reference_id2;
593 END IF;
594
595 IF (p_attribute_category = G_MISS_CHAR) THEN
596 x_attribute_category := NULL;
597 ELSIF (p_ATTRIBUTE_CATEGORY IS NOT NULL) THEN
598 x_attribute_category := p_attribute_category;
599 END IF;
600
601 IF (p_attribute1 = G_MISS_CHAR) THEN
602 x_attribute1 := NULL;
603 ELSIF (p_ATTRIBUTE1 IS NOT NULL) THEN
604 x_attribute1 := p_attribute1;
605 END IF;
606
607 IF (p_attribute2 = G_MISS_CHAR) THEN
608 x_attribute2 := NULL;
609 ELSIF (p_ATTRIBUTE2 IS NOT NULL) THEN
610 x_attribute2 := p_attribute2;
611 END IF;
612
613 IF (p_attribute3 = G_MISS_CHAR) THEN
614 x_attribute3 := NULL;
615 ELSIF (p_ATTRIBUTE3 IS NOT NULL) THEN
616 x_attribute3 := p_attribute3;
617 END IF;
618
619 IF (p_attribute4 = G_MISS_CHAR) THEN
620 x_attribute4 := NULL;
621 ELSIF (p_ATTRIBUTE4 IS NOT NULL) THEN
622 x_attribute4 := p_attribute4;
623 END IF;
624
625 IF (p_attribute5 = G_MISS_CHAR) THEN
626 x_attribute5 := NULL;
627 ELSIF (p_ATTRIBUTE5 IS NOT NULL) THEN
628 x_attribute5 := p_attribute5;
629 END IF;
630
631 IF (p_attribute6 = G_MISS_CHAR) THEN
632 x_attribute6 := NULL;
633 ELSIF (p_ATTRIBUTE6 IS NOT NULL) THEN
634 x_attribute6 := p_attribute6;
635 END IF;
636
637 IF (p_attribute7 = G_MISS_CHAR) THEN
638 x_attribute7 := NULL;
639 ELSIF (p_ATTRIBUTE7 IS NOT NULL) THEN
640 x_attribute7 := p_attribute7;
641 END IF;
642
643 IF (p_attribute8 = G_MISS_CHAR) THEN
644 x_attribute8 := NULL;
645 ELSIF (p_ATTRIBUTE8 IS NOT NULL) THEN
646 x_attribute8 := p_attribute8;
647 END IF;
648
649 IF (p_attribute9 = G_MISS_CHAR) THEN
650 x_attribute9 := NULL;
651 ELSIF (p_ATTRIBUTE9 IS NOT NULL) THEN
652 x_attribute9 := p_attribute9;
653 END IF;
654
655 IF (p_attribute10 = G_MISS_CHAR) THEN
656 x_attribute10 := NULL;
657 ELSIF (p_ATTRIBUTE10 IS NOT NULL) THEN
658 x_attribute10 := p_attribute10;
659 END IF;
660
661 IF (p_attribute11 = G_MISS_CHAR) THEN
662 x_attribute11 := NULL;
663 ELSIF (p_ATTRIBUTE11 IS NOT NULL) THEN
664 x_attribute11 := p_attribute11;
665 END IF;
666
667 IF (p_attribute12 = G_MISS_CHAR) THEN
668 x_attribute12 := NULL;
669 ELSIF (p_ATTRIBUTE12 IS NOT NULL) THEN
670 x_attribute12 := p_attribute12;
671 END IF;
672
673 IF (p_attribute13 = G_MISS_CHAR) THEN
674 x_attribute13 := NULL;
675 ELSIF (p_ATTRIBUTE13 IS NOT NULL) THEN
676 x_attribute13 := p_attribute13;
677 END IF;
678
679 IF (p_attribute14 = G_MISS_CHAR) THEN
680 x_attribute14 := NULL;
681 ELSIF (p_ATTRIBUTE14 IS NOT NULL) THEN
682 x_attribute14 := p_attribute14;
683 END IF;
684
685 IF (p_attribute15 = G_MISS_CHAR) THEN
686 x_attribute15 := NULL;
687 ELSIF (p_ATTRIBUTE15 IS NOT NULL) THEN
688 x_attribute15 := p_attribute15;
689 END IF;
690 --MLS for templates
691 IF (p_translated_from_tmpl_id = G_MISS_NUM) THEN
692 x_translated_from_tmpl_id := NULL;
693 ELSIF (p_translated_from_tmpl_id IS NOT NULL) THEN
694 x_translated_from_tmpl_id := p_translated_from_tmpl_id;
695 END IF;
696 IF (p_language = G_MISS_CHAR) THEN
697 x_language := NULL;
698 ELSIF (p_language IS NOT NULL) THEN
699 x_language := p_language;
700 END IF;
701
702 END IF;
703
704 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
705 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Leaving Set_Attributes ');
706 END IF;
707
708 RETURN G_RET_STS_SUCCESS ;
709 EXCEPTION
710 WHEN FND_API.G_EXC_ERROR THEN
711 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
712 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception');
713 END IF;
714 RETURN G_RET_STS_ERROR;
715
716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
717 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
718 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
719 END IF;
720 RETURN G_RET_STS_UNEXP_ERROR;
721
722 WHEN OTHERS THEN
723 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
724 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm);
725 END IF;
726 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
727 p_msg_name => G_UNEXPECTED_ERROR,
728 p_token1 => G_SQLCODE_TOKEN,
729 p_token1_value => sqlcode,
730 p_token2 => G_SQLERRM_TOKEN,
731 p_token2_value => sqlerrm);
732 RETURN G_RET_STS_UNEXP_ERROR;
733
734 END Set_Attributes ;
735
736 ----------------------------------------------
737 -- Validate_Attributes for: OKC_TERMS_TEMPLATES_ALL --
738 ----------------------------------------------
739 FUNCTION Validate_Attributes (
740 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
741
742 p_template_name IN VARCHAR2,
743 p_template_id IN NUMBER,
744 p_working_copy_flag IN VARCHAR2,
745 p_intent IN VARCHAR2,
746 p_status_code IN VARCHAR2,
747 p_start_date IN DATE,
748 p_end_date IN DATE,
749 p_global_flag IN VARCHAR2,
750 p_parent_template_id IN NUMBER,
751 p_print_template_id IN NUMBER,
752 p_contract_expert_enabled IN VARCHAR2,
753 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+ : Contract Expert Changes
754 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+ : Contract Expert Changes
755 p_template_model_id IN NUMBER,
756 p_instruction_text IN VARCHAR2,
757 p_tmpl_numbering_scheme IN NUMBER,
758 p_description IN VARCHAR2,
759 p_approval_wf_key IN VARCHAR2,
760 p_cz_export_wf_key IN VARCHAR2,
761 p_orig_system_reference_code IN VARCHAR2,
762 p_orig_system_reference_id1 IN NUMBER,
763 p_orig_system_reference_id2 IN NUMBER,
764 p_org_id IN NUMBER,
765 p_attribute_category IN VARCHAR2,
766 p_attribute1 IN VARCHAR2,
767 p_attribute2 IN VARCHAR2,
768 p_attribute3 IN VARCHAR2,
769 p_attribute4 IN VARCHAR2,
770 p_attribute5 IN VARCHAR2,
771 p_attribute6 IN VARCHAR2,
772 p_attribute7 IN VARCHAR2,
773 p_attribute8 IN VARCHAR2,
774 p_attribute9 IN VARCHAR2,
775 p_attribute10 IN VARCHAR2,
776 p_attribute11 IN VARCHAR2,
777 p_attribute12 IN VARCHAR2,
778 p_attribute13 IN VARCHAR2,
779 p_attribute14 IN VARCHAR2,
780 p_attribute15 IN VARCHAR2,
781 p_translated_from_tmpl_id IN NUMBER,
782 p_language IN VARCHAR2
783 ) RETURN VARCHAR2 IS
784 l_api_name CONSTANT VARCHAR2(30) := 'validate_attributes';
785 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
786 l_dummy_var VARCHAR2(1) := '?';
787 TYPE layout_tmpl_csr IS REF CURSOR;
788 l_layout_tmpl_csr LAYOUT_TMPL_CSR;
789 l_sql_stmt VARCHAR2(4000);
790 l_print_template_id NUMBER;
791 l_apps_user VARCHAR2(150);
792 l_layout_start_date DATE;
793 l_layout_end_date DATE;
794 l_eff_date DATE;
795 l_tmpl_status okc_terms_templates_all.status_code%TYPE := 'ZZZ';
796 l_validate_layout VARCHAR2(1) := 'Y';
797
798 CURSOR l_apps_user_csr IS
799 SELECT oracle_username
800 FROM fnd_oracle_userid
801 WHERE read_only_flag = 'U';
802
803 CURSOR l_xdo_view_csr(pc_user VARCHAR2) IS
804 SELECT 1
805 FROM all_views
806 WHERE view_name like 'XDO_TEMPLATES_VL'
807 AND owner = pc_user;
808
809 CURSOR l_parent_template_id_csr is
810 SELECT '!'
811 FROM okc_terms_templates_all
812 WHERE TEMPLATE_ID = p_parent_template_id;
813
814 /***************Commented out to remove XDO dependency
815 CURSOR l_print_template_id_csr is
816 SELECT '!'
817 FROM xdo_templates_b
818 WHERE TEMPLATE_ID = p_print_template_id;
819 ***************/
820
821 /************* Removed as Expert will validate and update templates
822 CURSOR l_template_model_id_csr is
823 SELECT '!'
824 FROM cz_ps_nodes
825 WHERE PS_NODE_ID = p_template_model_id;
826 *****************************/
827
828 CURSOR l_org_id_csr is
829 SELECT '!'
830 FROM hr_operating_units
831 WHERE ORGANIZATION_ID = p_org_id;
832
833 CURSOR l_tmpl_status_csr(pc_template_id NUMBER) IS
834 SELECT status_code
835 FROM OKC_TERMS_TEMPLATES_ALL
836 WHERE template_id = pc_template_id;
837
838
839 BEGIN
840
841 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
842 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Entered Validate_Attributes');
843 END IF;
844
845 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
846 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
847 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1300: required values validation');
848 END IF;
849
850 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
851 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: - attribute TEMPLATE_NAME ');
852 END IF;
853 IF ( p_template_name IS NULL) THEN
854 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
855 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute TEMPLATE_NAME is invalid');
856 END IF;
857 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'TEMPLATE_NAME');
858 l_return_status := G_RET_STS_ERROR;
859 END IF;
860
861
862 END IF;
863
864 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
865 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
866 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: static values and range validation');
867 END IF;
868
869 END IF;
870
871 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
872 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
873 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: lookup codes validation');
874 END IF;
875
876 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
877 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: - attribute STATUS_CODE ');
878 END IF;
879 IF p_status_code IS NOT NULL THEN
880 l_return_status := Okc_Util.Check_Lookup_Code('OKC_TERMS_TMPL_STATUS',p_status_code);
881 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
882 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'STATUS_CODE');
883 l_return_status := G_RET_STS_ERROR;
884 END IF;
885 END IF;
886
887 END IF;
888
889 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
890 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
891 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: foreigh keys validation ');
892 END IF;
893
894 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
895 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - attribute TEMPLATE_ID ');
896 END IF;
897 /*
898 IF p_template_id IS NOT NULL THEN
899 l_dummy_var := '?';
900 OPEN l_template_id_csr;
901 FETCH l_template_id_csr INTO l_dummy_var;
902 CLOSE l_template_id_csr;
903 IF (l_dummy_var = '?') THEN
904 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
905 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: - attribute TEMPLATE_ID is invalid');
906 END IF;
907 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TEMPLATE_ID');
908 l_return_status := G_RET_STS_ERROR;
909 END IF;
910 END IF;
911 */
912
913 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
914 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - attribute PARENT_TEMPLATE_ID ');
915 END IF;
916 IF p_parent_template_id IS NOT NULL THEN
917 l_dummy_var := '?';
918 OPEN l_parent_template_id_csr;
919 FETCH l_parent_template_id_csr INTO l_dummy_var;
920 CLOSE l_parent_template_id_csr;
921 IF (l_dummy_var = '?') THEN
922 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
923 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: - attribute PARENT_TEMPLATE_ID is invalid');
924 END IF;
925 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PARENT_TEMPLATE_ID');
926 l_return_status := G_RET_STS_ERROR;
927 END IF;
928 END IF;
929
930 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
931 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - attribute PRINT_TEMPLATE_ID ');
932 END IF;
933
934 IF p_print_template_id IS NOT NULL THEN
935 l_dummy_var := '?';
936 /***********
937 OPEN l_print_template_id_csr;
938 FETCH l_print_template_id_csr INTO l_dummy_var;
939 CLOSE l_print_template_id_csr;
940 **************/
941
942 OPEN l_apps_user_csr;
943 FETCH l_apps_user_csr INTO l_apps_user;
944 CLOSE l_apps_user_csr;
945
946 OPEN l_xdo_view_csr(l_apps_user);
947 FETCH l_xdo_view_csr INTO l_dummy_var;
948 IF l_xdo_view_csr%FOUND THEN
949 l_sql_stmt := 'SELECT template_id,start_date,end_date FROM xdo_templates_b WHERE template_id = :1';
950
951 OPEN l_layout_tmpl_csr FOR l_sql_stmt USING p_print_template_id;
952 FETCH l_layout_tmpl_csr INTO l_print_template_id,l_layout_start_date,l_layout_end_date;
953 IF l_layout_tmpl_csr%FOUND THEN
954 l_dummy_var := '!';
955 END IF;
956 CLOSE l_layout_tmpl_csr;
957 END IF;
958 CLOSE l_xdo_view_csr;
959
960 IF (l_dummy_var = '?') THEN
961 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
962 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: - attribute PRINT_TEMPLATE_ID is invalid');
963 END IF;
964 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PRINT_TEMPLATE_ID');
965 l_return_status := G_RET_STS_ERROR;
966 END IF;
967 END IF;
968
969 IF p_template_id IS NOT NULL THEN
970 OPEN l_tmpl_status_csr(p_template_id);
971 FETCH l_tmpl_status_csr INTO l_tmpl_status;
972 CLOSE l_tmpl_status_csr;
973 END IF;
974
975
976 --Bug 3674152 Not checking layout template validity
977 IF ((l_tmpl_status='APPROVED' AND p_status_code = 'ON_HOLD') OR
978 (l_tmpl_status='ON_HOLD' AND p_status_code = 'APPROVED') OR
979 (l_tmpl_status='PENDING_APPROVAL' AND p_status_code = 'APPROVED') OR
980 (l_tmpl_status='PENDING_APPROVAL' AND p_status_code = 'REJECTED') OR
981 (l_tmpl_status='ZZZ' AND p_status_code = 'REVISION') OR
982 (p_status_code = 'PENDING_APPROVAL') ) THEN
983 l_validate_layout := 'N';
984 END IF;
985
986
987 IF l_validate_layout <> 'N' THEN
988 --Bug 3674152 Validate start_date and end_date of layout template against template effective date
989 l_eff_date := sysdate;
990 IF nvl(p_end_date,sysdate) < sysdate THEN
991 l_eff_date := p_end_date;
992 ELSIF p_start_date > sysdate THEN
993 l_eff_date := p_start_date;
994 END IF;
995
996
997 IF l_layout_start_date > l_eff_date OR nvl(l_layout_end_date,l_eff_date) < l_eff_date THEN
998 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
999 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - LAYOUT_TEMPLATE is invalid or end_dated');
1000 END IF;
1001 Okc_Api.Set_Message(G_APP_NAME,'OKC_LAYOUT_TMPL_INVALID');
1002 l_return_status := G_RET_STS_ERROR;
1003 END IF;
1004 END IF;
1005
1006 /****************Removed as COntracts Expert will validate and update template
1007 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1008 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - attribute TEMPLATE_MODEL_ID ');
1009 END IF;
1010 IF p_template_model_id IS NOT NULL THEN
1011 l_dummy_var := '?';
1012 OPEN l_template_model_id_csr;
1013 FETCH l_template_model_id_csr INTO l_dummy_var;
1014 CLOSE l_template_model_id_csr;
1015 IF (l_dummy_var = '?') THEN
1016 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1017 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: - attribute TEMPLATE_MODEL_ID is invalid');
1018 END IF;
1019 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TEMPLATE_MODEL_ID');
1020 l_return_status := G_RET_STS_ERROR;
1021 END IF;
1022 END IF;
1023 *****************/
1024
1025
1026 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1027 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - attribute ORG_ID ');
1028 END IF;
1029 IF p_org_id IS NOT NULL THEN
1030 l_dummy_var := '?';
1031 OPEN l_org_id_csr;
1032 FETCH l_org_id_csr INTO l_dummy_var;
1033 CLOSE l_org_id_csr;
1034 IF (l_dummy_var = '?') THEN
1035 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1036 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: - attribute ORG_ID is invalid');
1037 END IF;
1038 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'ORG_ID');
1039 l_return_status := G_RET_STS_ERROR;
1040 END IF;
1041 END IF;
1042
1043
1044 END IF;
1045
1046
1047 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1048 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2400: Leaving Validate_Attributes ');
1049 END IF;
1050
1051 RETURN l_return_status;
1052
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1056 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm);
1057 END IF;
1058 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1059 p_msg_name => G_UNEXPECTED_ERROR,
1060 p_token1 => G_SQLCODE_TOKEN,
1061 p_token1_value => sqlcode,
1062 p_token2 => G_SQLERRM_TOKEN,
1063 p_token2_value => sqlerrm);
1064
1065 /* ?? uncomment next part after you check and change this foreign key validation
1066
1067 IF l_template_id_csr%ISOPEN THEN
1068 CLOSE l_template_id_csr;
1069 END IF; */
1070
1071 IF l_parent_template_id_csr%ISOPEN THEN
1072 CLOSE l_parent_template_id_csr;
1073 END IF;
1074
1075 /****************
1076 IF l_print_template_id_csr%ISOPEN THEN
1077 CLOSE l_print_template_id_csr;
1078 END IF;
1079 ********************/
1080
1081 /****************Removed as COntracts Expert will validate and update template
1082 IF l_template_model_id_csr%ISOPEN THEN
1083 CLOSE l_template_model_id_csr;
1084 END IF;
1085 ****************/
1086
1087 IF l_org_id_csr%ISOPEN THEN
1088 CLOSE l_org_id_csr;
1089 END IF;
1090
1091 RETURN G_RET_STS_UNEXP_ERROR;
1092
1093 END Validate_Attributes;
1094
1095
1096 ---------------------------------------------------------------------------
1097 -- PROCEDURE Validate_Record
1098 -- It calls Item Level Validations and then makes Record Level Validations
1099 ---------------------------------------------------------------------------
1100 ------------------------------------------
1101 -- Validate_Record for:OKC_TERMS_TEMPLATES_ALL --
1102 ------------------------------------------
1103 FUNCTION Validate_Record (
1104 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1105
1106 p_template_name IN VARCHAR2,
1107 p_template_id IN NUMBER,
1108 p_working_copy_flag IN VARCHAR2,
1109 p_intent IN VARCHAR2,
1110 p_status_code IN VARCHAR2,
1111 p_start_date IN DATE,
1112 p_end_date IN DATE,
1113 p_global_flag IN VARCHAR2,
1114 p_parent_template_id IN NUMBER,
1115 p_print_template_id IN NUMBER,
1116 p_contract_expert_enabled IN VARCHAR2,
1117 p_xprt_clause_mandatory_flag in VARCHAR2,
1118 p_xprt_scn_code in VARCHAR2,
1119 p_template_model_id IN NUMBER,
1120 p_instruction_text IN VARCHAR2,
1121 p_tmpl_numbering_scheme IN NUMBER,
1122 p_description IN VARCHAR2,
1123 p_approval_wf_key IN VARCHAR2,
1124 p_cz_export_wf_key IN VARCHAR2,
1125 p_orig_system_reference_code IN VARCHAR2,
1126 p_orig_system_reference_id1 IN NUMBER,
1127 p_orig_system_reference_id2 IN NUMBER,
1128 p_org_id IN NUMBER,
1129 p_attribute_category IN VARCHAR2,
1130 p_attribute1 IN VARCHAR2,
1131 p_attribute2 IN VARCHAR2,
1132 p_attribute3 IN VARCHAR2,
1133 p_attribute4 IN VARCHAR2,
1134 p_attribute5 IN VARCHAR2,
1135 p_attribute6 IN VARCHAR2,
1136 p_attribute7 IN VARCHAR2,
1137 p_attribute8 IN VARCHAR2,
1138 p_attribute9 IN VARCHAR2,
1139 p_attribute10 IN VARCHAR2,
1140 p_attribute11 IN VARCHAR2,
1141 p_attribute12 IN VARCHAR2,
1142 p_attribute13 IN VARCHAR2,
1143 p_attribute14 IN VARCHAR2,
1144 p_attribute15 IN VARCHAR2,
1145 p_translated_from_tmpl_id IN NUMBER,
1146 p_language IN VARCHAR2
1147 ) RETURN VARCHAR2 IS
1148 l_api_name CONSTANT VARCHAR2(30) := 'validate_record';
1149 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1150 l_dummy_var VARCHAR2(1) := '?';
1151 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
1152
1153 CURSOR l_tmpl_name_csr is
1154 SELECT '!'
1155 FROM okc_terms_templates_all
1156 WHERE template_name = p_template_name
1157 AND nvl(working_copy_flag,'N') = nvl(p_working_copy_flag,'N')
1158 AND ((p_template_id IS NULL) OR (p_template_id <> template_id))
1159 AND ORG_ID = mo_global.get_current_org_id();
1160 --MLS for templates
1161 CURSOR l_primary_tmpl_csr IS
1162 SELECT '!'
1163 FROM okc_terms_templates_all
1164 WHERE translated_from_tmpl_id = p_template_id
1165 UNION ALL
1166 SELECT '!'
1167 FROM okc_terms_templates_all
1168 WHERE translated_from_tmpl_id = p_parent_template_id ;
1169 --MLS for templates
1170 CURSOR l_tmpl_group1_csr IS
1171 SELECT '!', template_name
1172 FROM okc_terms_templates_all
1173 WHERE translated_from_tmpl_id= p_parent_template_id
1174 AND language = p_language;
1175
1176 --MLS for templates
1177 CURSOR l_tmpl_group2_csr IS
1178 SELECT '!', template_name
1179 FROM okc_terms_templates_all
1180 WHERE translated_from_tmpl_id= p_template_id
1181 AND language = p_language;
1182
1183 --MLS for templates
1184 CURSOR l_tmpl_group3_csr IS
1185 SELECT '!', tta.template_name
1186 FROM okc_terms_templates_all tta
1187 WHERE tta.template_id = p_translated_from_tmpl_id
1188 AND language = p_language
1189 UNION ALL
1190 SELECT '!', tta.template_name
1191 FROM okc_terms_templates_all tta
1192 WHERE tta.parent_template_id = p_translated_from_tmpl_id
1193 AND language = p_language
1194 UNION ALL
1195 SELECT '!', tta.template_name
1196 FROM okc_terms_templates_all tta
1197 WHERE tta.translated_from_tmpl_id = p_translated_from_tmpl_id
1198 AND tta.language = p_language
1199 AND tta.template_id <> p_template_id
1200 AND tta.template_id <> nvl(p_parent_template_id,0)
1201 AND not exists
1202 (SELECT 1
1203 FROM okc_terms_templates_all tta3
1204 WHERE tta3.template_id = tta.template_id
1205 AND tta3.parent_template_id = p_template_id) ;
1206
1207
1208 BEGIN
1209
1210 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1211 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2600: Entered Validate_Record');
1212 END IF;
1213
1214 --- Validate all non-missing attributes (Item Level Validation)
1215 l_return_status := Validate_Attributes(
1216 p_validation_level => p_validation_level,
1217
1218 p_template_name => p_template_name,
1219 p_template_id => p_template_id,
1220 p_working_copy_flag => p_working_copy_flag,
1221 p_intent => p_intent,
1222 p_status_code => p_status_code,
1223 p_start_date => p_start_date,
1224 p_end_date => p_end_date,
1225 p_global_flag => p_global_flag,
1226 p_parent_template_id => p_parent_template_id,
1227 p_print_template_id => p_print_template_id,
1228 p_contract_expert_enabled => p_contract_expert_enabled,
1229 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
1230 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
1231 p_template_model_id => p_template_model_id,
1232 p_instruction_text => p_instruction_text,
1233 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
1234 p_description => p_description,
1235 p_approval_wf_key => p_approval_wf_key,
1236 p_cz_export_wf_key => p_cz_export_wf_key,
1237 p_orig_system_reference_code => p_orig_system_reference_code,
1238 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1239 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1240 p_org_id => p_org_id,
1241 p_attribute_category => p_attribute_category,
1242 p_attribute1 => p_attribute1,
1243 p_attribute2 => p_attribute2,
1244 p_attribute3 => p_attribute3,
1245 p_attribute4 => p_attribute4,
1246 p_attribute5 => p_attribute5,
1247 p_attribute6 => p_attribute6,
1248 p_attribute7 => p_attribute7,
1249 p_attribute8 => p_attribute8,
1250 p_attribute9 => p_attribute9,
1251 p_attribute10 => p_attribute10,
1252 p_attribute11 => p_attribute11,
1253 p_attribute12 => p_attribute12,
1254 p_attribute13 => p_attribute13,
1255 p_attribute14 => p_attribute14,
1256 p_attribute15 => p_attribute15,
1257 p_translated_from_tmpl_id => p_translated_from_tmpl_id ,
1258 p_language => p_language
1259 );
1260 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1261 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1262 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm);
1263 END IF;
1264 RETURN G_RET_STS_UNEXP_ERROR;
1265 END IF;
1266
1267 --- Record Level Validation
1268 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
1269 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1270 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2800: Entered Record Level Validations');
1271 END IF;
1272
1273 IF nvl(TRUNC(p_end_date),TRUNC(p_start_date)) < TRUNC(p_start_date) THEN
1274 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1275 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2810: Start Date > End Date');
1276 END IF;
1277 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_SDATE_GREATER');
1278 l_return_status := G_RET_STS_ERROR;
1279 END IF;
1280
1281 IF (nvl(p_working_copy_flag,'N') = 'Y' AND p_parent_template_id IS NULL) THEN
1282 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1283 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2820: Working Copy Flag is Y and Parent_Template_Id is Null');
1284 END IF;
1285 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'PARENT_TEMPALTE_ID');
1286 l_return_status := G_RET_STS_ERROR;
1287 END IF;
1288
1289 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1290 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2830: - Validate Template Name Uniqueness ');
1291 END IF;
1292 l_dummy_var := '?';
1293 OPEN l_tmpl_name_csr;
1294 FETCH l_tmpl_name_csr INTO l_dummy_var;
1295 CLOSE l_tmpl_name_csr;
1296 IF (l_dummy_var = '!') THEN
1297 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1298 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2840: - Template Name Not Unique in the Org');
1299 END IF;
1300 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_DUP_NAME');
1301 l_return_status := G_RET_STS_ERROR;
1302 END IF;
1303 ----MLS for templates
1304
1305 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1306 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2850: - Validate LANGUAGE ');
1307 END IF;
1308 IF (p_translated_from_tmpl_id IS NOT NULL) AND (p_language IS NULL) THEN
1309 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1310 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2860: - Error:Template Language is null when tft is specified');
1311 END IF;
1312 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_LANG_REQUIRED');
1313 l_return_status := G_RET_STS_ERROR;
1314 END IF;
1315
1316
1317 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1318 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2865: - Validate primary template ');
1319 END IF;
1320 IF p_translated_from_tmpl_id IS NOT NULL AND p_language IS NOT NULL THEN
1321 l_dummy_var := '?';
1322 OPEN l_primary_tmpl_csr;
1323 FETCH l_primary_tmpl_csr INTO l_dummy_var;
1324 CLOSE l_primary_tmpl_csr;
1325 IF (l_dummy_var = '!') THEN
1326 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1327 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2868: - Error:User adding tft for a template which alreay has children pointing to it');
1328 END IF;
1329 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_ALREADY_TRANSLATED');
1330 l_return_status := G_RET_STS_ERROR;
1331 END IF;
1332 END IF;
1333
1334
1335 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1336 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2870: - Validate language on primary template ');
1337 END IF;
1338 IF p_language IS NULL AND p_translated_from_tmpl_id IS NULL THEN
1339 l_dummy_var := '?';
1340 OPEN l_primary_tmpl_csr;
1341 FETCH l_primary_tmpl_csr INTO l_dummy_var;
1342 CLOSE l_primary_tmpl_csr;
1343 IF (l_dummy_var = '!') THEN
1344 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1345 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2880: - Error:Template Language removed in a priamry template');
1346 END IF;
1347 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_ALREADY_REFERENCED', 'TMPL1', p_template_name);
1348 l_return_status := G_RET_STS_ERROR;
1349 END IF;
1350 END IF;
1351
1352
1353 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1354 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2890: - Validate language in Template group ');
1355 END IF;
1356 l_dummy_var := '?';
1357 l_template_name := null;
1358 IF p_language IS NOT NULL AND l_return_status <> G_RET_STS_ERROR THEN
1359 IF p_translated_from_tmpl_id IS NULL THEN -- primary template
1360 IF p_parent_template_id IS NOT NULL THEN -- template has undergone revision
1361 OPEN l_tmpl_group1_csr;
1362 FETCH l_tmpl_group1_csr INTO l_dummy_var, l_template_name;
1363 CLOSE l_tmpl_group1_csr;
1364 ELSE
1365 OPEN l_tmpl_group2_csr;
1366 FETCH l_tmpl_group2_csr INTO l_dummy_var, l_template_name;
1367 CLOSE l_tmpl_group2_csr;
1368 END IF;
1369 ELSE
1370 OPEN l_tmpl_group3_csr;
1371 FETCH l_tmpl_group3_csr INTO l_dummy_var, l_template_name;
1372 CLOSE l_tmpl_group3_csr;
1373 END IF;
1374
1375
1376 IF (l_dummy_var = '!') THEN
1377 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1378 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: - Error:Template with specified language already exists in the group');
1379 END IF;
1380
1381 /* Fix for te bug# 4646417. Removed TMPL2 parameter from the set_message call */
1382 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_LANG_EXISTS','TMPL1' , p_template_name);
1383 l_return_status := G_RET_STS_ERROR;
1384 END IF;
1385 END IF;
1386
1387
1388
1389
1390 ----MLS for templates
1391
1392 /*+++++++++++++start of hand code +++++++++++++++++++*/
1393 -- ?? manual coding for Record Level Validations if required ??
1394 /*+++++++++++++End of hand code +++++++++++++++++++*/
1395 END IF;
1396
1397 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1398 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2995: Leaving Validate_Record : '||sqlerrm);
1399 END IF;
1400 RETURN l_return_status ;
1401
1402 EXCEPTION
1403 WHEN OTHERS THEN
1404
1405 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1406 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm);
1407 END IF;
1408
1409 IF l_tmpl_name_csr%ISOPEN THEN
1410 CLOSE l_tmpl_name_csr;
1411 END IF;
1412
1413 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1414 p_msg_name => G_UNEXPECTED_ERROR,
1415 p_token1 => G_SQLCODE_TOKEN,
1416 p_token1_value => sqlcode,
1417 p_token2 => G_SQLERRM_TOKEN,
1418 p_token2_value => sqlerrm);
1419 RETURN G_RET_STS_UNEXP_ERROR ;
1420
1421 END Validate_Record;
1422
1423 ---------------------------------------------------------------------------
1424 -- PROCEDURE validate_row
1425 ---------------------------------------------------------------------------
1426 ---------------------------------------
1427 -- validate_row for:OKC_TERMS_TEMPLATES_ALL --
1428 ---------------------------------------
1429 PROCEDURE validate_row(
1430 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1431
1432 x_return_status OUT NOCOPY VARCHAR2,
1433
1434 p_template_name IN VARCHAR2,
1435 p_template_id IN NUMBER,
1436 p_working_copy_flag IN VARCHAR2,
1437 p_intent IN VARCHAR2,
1438 p_status_code IN VARCHAR2,
1439 p_start_date IN DATE,
1440 p_end_date IN DATE,
1441 p_global_flag IN VARCHAR2,
1442 p_parent_template_id IN NUMBER,
1443 p_print_template_id IN NUMBER,
1444 p_contract_expert_enabled IN VARCHAR2,
1445 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
1446 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
1447 p_template_model_id IN NUMBER,
1448 p_instruction_text IN VARCHAR2,
1449 p_tmpl_numbering_scheme IN NUMBER,
1450 p_description IN VARCHAR2,
1451 p_approval_wf_key IN VARCHAR2,
1452 p_cz_export_wf_key IN VARCHAR2,
1453 p_orig_system_reference_code IN VARCHAR2,
1454 p_orig_system_reference_id1 IN NUMBER,
1455 p_orig_system_reference_id2 IN NUMBER,
1456 p_org_id IN NUMBER,
1457 p_cls_enabled IN VARCHAR2,
1458
1459 p_attribute_category IN VARCHAR2 := NULL,
1460 p_attribute1 IN VARCHAR2 := NULL,
1461 p_attribute2 IN VARCHAR2 := NULL,
1462 p_attribute3 IN VARCHAR2 := NULL,
1463 p_attribute4 IN VARCHAR2 := NULL,
1464 p_attribute5 IN VARCHAR2 := NULL,
1465 p_attribute6 IN VARCHAR2 := NULL,
1466 p_attribute7 IN VARCHAR2 := NULL,
1467 p_attribute8 IN VARCHAR2 := NULL,
1468 p_attribute9 IN VARCHAR2 := NULL,
1469 p_attribute10 IN VARCHAR2 := NULL,
1470 p_attribute11 IN VARCHAR2 := NULL,
1471 p_attribute12 IN VARCHAR2 := NULL,
1472 p_attribute13 IN VARCHAR2 := NULL,
1473 p_attribute14 IN VARCHAR2 := NULL,
1474 p_attribute15 IN VARCHAR2 := NULL,
1475 p_translated_from_tmpl_id IN NUMBER,
1476 p_language IN VARCHAR2,
1477 p_object_version_number IN NUMBER
1478 ) IS
1479 l_api_name CONSTANT VARCHAR2(30) := 'validate_row';
1480 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
1481 l_working_copy_flag OKC_TERMS_TEMPLATES_ALL.WORKING_COPY_FLAG%TYPE;
1482 l_intent OKC_TERMS_TEMPLATES_ALL.INTENT%TYPE;
1483 l_status_code OKC_TERMS_TEMPLATES_ALL.STATUS_CODE%TYPE;
1484 l_start_date OKC_TERMS_TEMPLATES_ALL.START_DATE%TYPE;
1485 l_end_date OKC_TERMS_TEMPLATES_ALL.END_DATE%TYPE;
1486 l_global_flag OKC_TERMS_TEMPLATES_ALL.GLOBAL_FLAG%TYPE;
1487 l_parent_template_id OKC_TERMS_TEMPLATES_ALL.PARENT_TEMPLATE_ID%TYPE;
1488 l_print_template_id OKC_TERMS_TEMPLATES_ALL.PRINT_TEMPLATE_ID%TYPE;
1489 l_contract_expert_enabled OKC_TERMS_TEMPLATES_ALL.CONTRACT_EXPERT_ENABLED%TYPE;
1490 l_cls_enabled OKC_TERMS_TEMPLATES_ALL.CLS_ENABLED%TYPE;
1491 l_xprt_clause_mandatory_flag OKC_TERMS_TEMPLATES_ALL.XPRT_CLAUSE_MANDATORY_FLAG%TYPE;
1492 l_xprt_scn_code OKC_TERMS_TEMPLATES_ALL.XPRT_SCN_CODE%TYPE;
1493 l_template_model_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_MODEL_ID%TYPE;
1494 l_instruction_text OKC_TERMS_TEMPLATES_ALL.INSTRUCTION_TEXT%TYPE;
1495 l_tmpl_numbering_scheme OKC_TERMS_TEMPLATES_ALL.TMPL_NUMBERING_SCHEME%TYPE;
1496 l_description OKC_TERMS_TEMPLATES_ALL.DESCRIPTION%TYPE;
1497 l_approval_wf_key OKC_TERMS_TEMPLATES_ALL.APPROVAL_WF_KEY%TYPE;
1498 l_cz_export_wf_key OKC_TERMS_TEMPLATES_ALL.CZ_EXPORT_WF_KEY%TYPE;
1499 l_orig_system_reference_code OKC_TERMS_TEMPLATES_ALL.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
1500 l_orig_system_reference_id1 OKC_TERMS_TEMPLATES_ALL.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
1501 l_orig_system_reference_id2 OKC_TERMS_TEMPLATES_ALL.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
1502 l_org_id OKC_TERMS_TEMPLATES_ALL.ORG_ID%TYPE;
1503 l_attribute_category OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE_CATEGORY%TYPE;
1504 l_attribute1 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE1%TYPE;
1505 l_attribute2 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE2%TYPE;
1506 l_attribute3 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE3%TYPE;
1507 l_attribute4 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE4%TYPE;
1508 l_attribute5 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE5%TYPE;
1509 l_attribute6 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE6%TYPE;
1510 l_attribute7 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE7%TYPE;
1511 l_attribute8 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE8%TYPE;
1512 l_attribute9 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE9%TYPE;
1513 l_attribute10 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE10%TYPE;
1514 l_attribute11 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE11%TYPE;
1515 l_attribute12 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE12%TYPE;
1516 l_attribute13 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE13%TYPE;
1517 l_attribute14 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE14%TYPE;
1518 l_attribute15 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE15%TYPE;
1519 l_object_version_number OKC_TERMS_TEMPLATES_ALL.OBJECT_VERSION_NUMBER%TYPE;
1520 l_created_by OKC_TERMS_TEMPLATES_ALL.CREATED_BY%TYPE;
1521 l_creation_date OKC_TERMS_TEMPLATES_ALL.CREATION_DATE%TYPE;
1522 l_last_updated_by OKC_TERMS_TEMPLATES_ALL.LAST_UPDATED_BY%TYPE;
1523 l_last_update_login OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_LOGIN%TYPE;
1524 l_last_update_date OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_DATE%TYPE;
1525 l_translated_from_tmpl_id OKC_TERMS_TEMPLATES_ALL.TRANSLATED_FROM_TMPL_ID%TYPE;
1526 l_language OKC_TERMS_TEMPLATES_ALL.LANGUAGE%TYPE;
1527 BEGIN
1528
1529 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1530 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3100: Entered validate_row');
1531 END IF;
1532
1533 -- Setting attributes
1534 x_return_status := Set_Attributes(
1535 p_template_name => p_template_name,
1536 p_template_id => p_template_id,
1537 p_working_copy_flag => p_working_copy_flag,
1538 p_intent => p_intent,
1539 p_status_code => p_status_code,
1540 p_start_date => p_start_date,
1541 p_end_date => p_end_date,
1542 p_global_flag => p_global_flag,
1543 p_parent_template_id => p_parent_template_id,
1544 p_print_template_id => p_print_template_id,
1545 p_contract_expert_enabled => p_contract_expert_enabled,
1546 p_cls_enabled => p_cls_enabled,
1547 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
1548 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
1549 p_template_model_id => p_template_model_id,
1550 p_instruction_text => p_instruction_text,
1551 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
1552 p_description => p_description,
1553 p_approval_wf_key => p_approval_wf_key,
1554 p_cz_export_wf_key => p_cz_export_wf_key,
1555 p_orig_system_reference_code => p_orig_system_reference_code,
1556 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1557 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1558 p_org_id => p_org_id,
1559 p_attribute_category => p_attribute_category,
1560 p_attribute1 => p_attribute1,
1561 p_attribute2 => p_attribute2,
1562 p_attribute3 => p_attribute3,
1563 p_attribute4 => p_attribute4,
1564 p_attribute5 => p_attribute5,
1565 p_attribute6 => p_attribute6,
1566 p_attribute7 => p_attribute7,
1567 p_attribute8 => p_attribute8,
1568 p_attribute9 => p_attribute9,
1569 p_attribute10 => p_attribute10,
1570 p_attribute11 => p_attribute11,
1571 p_attribute12 => p_attribute12,
1572 p_attribute13 => p_attribute13,
1573 p_attribute14 => p_attribute14,
1574 p_attribute15 => p_attribute15,
1575 p_translated_from_tmpl_id => p_translated_from_tmpl_id ,
1576 p_language => p_language,
1577 p_object_version_number => p_object_version_number,
1578 x_template_name => l_template_name,
1579 x_working_copy_flag => l_working_copy_flag,
1580 x_intent => l_intent,
1581 x_status_code => l_status_code,
1582 x_start_date => l_start_date,
1583 x_end_date => l_end_date,
1584 x_global_flag => l_global_flag,
1585 x_parent_template_id => l_parent_template_id,
1586 x_print_template_id => l_print_template_id,
1587 x_contract_expert_enabled => l_contract_expert_enabled,
1588 x_cls_enabled => l_cls_enabled,
1589 x_xprt_clause_mandatory_flag => l_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
1590 x_xprt_scn_code => l_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
1591 x_template_model_id => l_template_model_id,
1592 x_instruction_text => l_instruction_text,
1593 x_tmpl_numbering_scheme => l_tmpl_numbering_scheme,
1594 x_description => l_description,
1595 x_approval_wf_key => l_approval_wf_key,
1596 x_cz_export_wf_key => l_cz_export_wf_key,
1597 x_orig_system_reference_code => l_orig_system_reference_code,
1598 x_orig_system_reference_id1 => l_orig_system_reference_id1,
1599 x_orig_system_reference_id2 => l_orig_system_reference_id2,
1600
1601 x_org_id => l_org_id,
1602 x_attribute_category => l_attribute_category,
1603 x_attribute1 => l_attribute1,
1604 x_attribute2 => l_attribute2,
1605 x_attribute3 => l_attribute3,
1606 x_attribute4 => l_attribute4,
1607 x_attribute5 => l_attribute5,
1608 x_attribute6 => l_attribute6,
1609 x_attribute7 => l_attribute7,
1610 x_attribute8 => l_attribute8,
1611 x_attribute9 => l_attribute9,
1612 x_attribute10 => l_attribute10,
1613 x_attribute11 => l_attribute11,
1614 x_attribute12 => l_attribute12,
1615 x_attribute13 => l_attribute13,
1616 x_attribute14 => l_attribute14,
1617 x_attribute15 => l_attribute15,
1618 x_translated_from_tmpl_id => l_translated_from_tmpl_id ,
1619 x_language => l_language
1620 );
1621 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1623 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1624 RAISE FND_API.G_EXC_ERROR;
1625 END IF;
1626
1627 -- Validate all non-missing attributes (Item Level Validation)
1628 x_return_status := Validate_Record(
1629 p_validation_level => p_validation_level,
1630 p_template_id => p_template_id,
1631 p_template_name => l_template_name,
1632 p_working_copy_flag => l_working_copy_flag,
1633 p_intent => l_intent,
1634 p_status_code => l_status_code,
1635 p_start_date => l_start_date,
1636 p_end_date => l_end_date,
1637 p_global_flag => l_global_flag,
1638 p_parent_template_id => l_parent_template_id,
1639 p_print_template_id => l_print_template_id,
1640 p_contract_expert_enabled => l_contract_expert_enabled,
1641 p_xprt_clause_mandatory_flag => l_xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
1642 p_xprt_scn_code => l_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
1643 p_template_model_id => l_template_model_id,
1644 p_instruction_text => l_instruction_text,
1645 p_tmpl_numbering_scheme => l_tmpl_numbering_scheme,
1646 p_description => l_description,
1647 p_approval_wf_key => l_approval_wf_key,
1648 p_cz_export_wf_key => l_cz_export_wf_key,
1649 p_orig_system_reference_code => l_orig_system_reference_code,
1650 p_orig_system_reference_id1 => l_orig_system_reference_id1,
1651 p_orig_system_reference_id2 => l_orig_system_reference_id2,
1652
1653 p_org_id => l_org_id,
1654 p_attribute_category => l_attribute_category,
1655 p_attribute1 => l_attribute1,
1656 p_attribute2 => l_attribute2,
1657 p_attribute3 => l_attribute3,
1658 p_attribute4 => l_attribute4,
1659 p_attribute5 => l_attribute5,
1660 p_attribute6 => l_attribute6,
1661 p_attribute7 => l_attribute7,
1662 p_attribute8 => l_attribute8,
1663 p_attribute9 => l_attribute9,
1664 p_attribute10 => l_attribute10,
1665 p_attribute11 => l_attribute11,
1666 p_attribute12 => l_attribute12,
1667 p_attribute13 => l_attribute13,
1668 p_attribute14 => l_attribute14,
1669 p_attribute15 => l_attribute15,
1670 p_translated_from_tmpl_id => l_translated_from_tmpl_id ,
1671 p_language => l_language
1672 );
1673
1674 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1675 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3200: Leaving validate_row');
1676 END IF;
1677
1678 EXCEPTION
1679 WHEN FND_API.G_EXC_ERROR THEN
1680 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1681 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception');
1682 END IF;
1683 x_return_status := G_RET_STS_ERROR;
1684
1685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1686 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1687 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1688 END IF;
1689 x_return_status := G_RET_STS_UNEXP_ERROR;
1690
1691 WHEN OTHERS THEN
1692 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1693 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm);
1694 END IF;
1695 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1696 p_msg_name => G_UNEXPECTED_ERROR,
1697 p_token1 => G_SQLCODE_TOKEN,
1698 p_token1_value => sqlcode,
1699 p_token2 => G_SQLERRM_TOKEN,
1700 p_token2_value => sqlerrm);
1701 x_return_status := G_RET_STS_UNEXP_ERROR;
1702
1703 END Validate_Row;
1704
1705 ---------------------------------------------------------------------------
1706 -- PROCEDURE Insert_Row
1707 ---------------------------------------------------------------------------
1708 -------------------------------------
1709 -- Insert_Row for:OKC_TERMS_TEMPLATES_ALL --
1710 -------------------------------------
1711 FUNCTION Insert_Row(
1712 p_template_name IN VARCHAR2,
1713 p_template_id IN NUMBER,
1714 p_working_copy_flag IN VARCHAR2,
1715 p_intent IN VARCHAR2,
1716 p_status_code IN VARCHAR2,
1717 p_start_date IN DATE,
1718 p_end_date IN DATE,
1719 p_global_flag IN VARCHAR2,
1720 p_parent_template_id IN NUMBER,
1721 p_print_template_id IN NUMBER,
1722 p_contract_expert_enabled IN VARCHAR2,
1723 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
1724 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
1725 p_template_model_id IN NUMBER,
1726 p_instruction_text IN VARCHAR2,
1727 p_tmpl_numbering_scheme IN NUMBER,
1728 p_description IN VARCHAR2,
1729 p_approval_wf_key IN VARCHAR2,
1730 p_cz_export_wf_key IN VARCHAR2,
1731 p_orig_system_reference_code IN VARCHAR2,
1732 p_orig_system_reference_id1 IN NUMBER,
1733 p_orig_system_reference_id2 IN NUMBER,
1734 p_org_id IN NUMBER,
1735 p_cls_enabled IN VARCHAR2,
1736
1737 p_attribute_category IN VARCHAR2,
1738 p_attribute1 IN VARCHAR2,
1739 p_attribute2 IN VARCHAR2,
1740 p_attribute3 IN VARCHAR2,
1741 p_attribute4 IN VARCHAR2,
1742 p_attribute5 IN VARCHAR2,
1743 p_attribute6 IN VARCHAR2,
1744 p_attribute7 IN VARCHAR2,
1745 p_attribute8 IN VARCHAR2,
1746 p_attribute9 IN VARCHAR2,
1747 p_attribute10 IN VARCHAR2,
1748 p_attribute11 IN VARCHAR2,
1749 p_attribute12 IN VARCHAR2,
1750 p_attribute13 IN VARCHAR2,
1751 p_attribute14 IN VARCHAR2,
1752 p_attribute15 IN VARCHAR2,
1753 p_object_version_number IN NUMBER,
1754 p_created_by IN NUMBER,
1755 p_creation_date IN DATE,
1756 p_last_updated_by IN NUMBER,
1757 p_last_update_login IN NUMBER,
1758 p_last_update_date IN DATE,
1759 p_translated_from_tmpl_id IN NUMBER,
1760 p_language IN VARCHAR2
1761
1762 ) RETURN VARCHAR2 IS
1763 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1764 BEGIN
1765
1766 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1767 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Entered Insert_Row function');
1768 END IF;
1769
1770 INSERT INTO OKC_TERMS_TEMPLATES_ALL(
1771 TEMPLATE_NAME,
1772 TEMPLATE_ID,
1773 WORKING_COPY_FLAG,
1774 INTENT,
1775 STATUS_CODE,
1776 START_DATE,
1777 END_DATE,
1778 GLOBAL_FLAG,
1779 PARENT_TEMPLATE_ID,
1780 PRINT_TEMPLATE_ID,
1781 CONTRACT_EXPERT_ENABLED,
1782 XPRT_CLAUSE_MANDATORY_FLAG, -- Added for 11.5.10+: Contract Expert Changes
1783 XPRT_SCN_CODE, -- Added for 11.5.10+: Contract Expert Changes
1784 TEMPLATE_MODEL_ID,
1785 INSTRUCTION_TEXT,
1786 TMPL_NUMBERING_SCHEME,
1787 DESCRIPTION,
1788 APPROVAL_WF_KEY,
1789 CZ_EXPORT_WF_KEY,
1790 ORIG_SYSTEM_REFERENCE_CODE,
1791 ORIG_SYSTEM_REFERENCE_ID1,
1792 ORIG_SYSTEM_REFERENCE_ID2,
1793 ORG_ID,
1794 CLS_ENABLED,
1795 ATTRIBUTE_CATEGORY,
1796 ATTRIBUTE1,
1797 ATTRIBUTE2,
1798 ATTRIBUTE3,
1799 ATTRIBUTE4,
1800 ATTRIBUTE5,
1801 ATTRIBUTE6,
1802 ATTRIBUTE7,
1803 ATTRIBUTE8,
1804 ATTRIBUTE9,
1805 ATTRIBUTE10,
1806 ATTRIBUTE11,
1807 ATTRIBUTE12,
1808 ATTRIBUTE13,
1809 ATTRIBUTE14,
1810 ATTRIBUTE15,
1811 OBJECT_VERSION_NUMBER,
1812 CREATED_BY,
1813 CREATION_DATE,
1814 LAST_UPDATED_BY,
1815 LAST_UPDATE_LOGIN,
1816 LAST_UPDATE_DATE,
1817 --MLS for templates
1818 TRANSLATED_FROM_TMPL_ID,
1819 LANGUAGE )
1820 VALUES (
1821 p_template_name,
1822 p_template_id,
1823 nvl(p_working_copy_flag,'N'),
1824 p_intent,
1825 p_status_code,
1826 TRUNC(p_start_date),
1827 TRUNC(p_end_date),
1828 nvl(p_global_flag,'N'),
1829 p_parent_template_id,
1830 p_print_template_id,
1831 nvl(p_contract_expert_enabled,'N'),
1832 nvl(p_xprt_clause_mandatory_flag,'N'), -- Added for 11.5.10+ : Contract Expert Changes
1833 p_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
1834 p_template_model_id,
1835 p_instruction_text,
1836 p_tmpl_numbering_scheme,
1837 p_description,
1838 p_approval_wf_key,
1839 p_cz_export_wf_key,
1840 p_orig_system_reference_code,
1841 p_orig_system_reference_id1,
1842 p_orig_system_reference_id2,
1843 p_org_id,
1844 p_cls_enabled,
1845 p_attribute_category,
1846 p_attribute1,
1847 p_attribute2,
1848 p_attribute3,
1849 p_attribute4,
1850 p_attribute5,
1851 p_attribute6,
1852 p_attribute7,
1853 p_attribute8,
1854 p_attribute9,
1855 p_attribute10,
1856 p_attribute11,
1857 p_attribute12,
1858 p_attribute13,
1859 p_attribute14,
1860 p_attribute15,
1861 p_object_version_number,
1862 p_created_by,
1863 p_creation_date,
1864 p_last_updated_by,
1865 p_last_update_login,
1866 p_last_update_date,
1867 --MLS for templates
1868 p_translated_from_tmpl_id,
1869 p_language );
1870
1871 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1872 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Leaving Insert_Row');
1873 END IF;
1874
1875 RETURN( G_RET_STS_SUCCESS );
1876
1877 EXCEPTION
1878 WHEN OTHERS THEN
1879
1880 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1881 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3800: Leaving Insert_Row:OTHERS Exception');
1882 END IF;
1883
1884 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1885 p_msg_name => G_UNEXPECTED_ERROR,
1886 p_token1 => G_SQLCODE_TOKEN,
1887 p_token1_value => sqlcode,
1888 p_token2 => G_SQLERRM_TOKEN,
1889 p_token2_value => sqlerrm);
1890
1891 RETURN( G_RET_STS_UNEXP_ERROR );
1892
1893 END Insert_Row;
1894
1895
1896 -------------------------------------
1897 -- Insert_Row for:OKC_TERMS_TEMPLATES_ALL --
1898 -------------------------------------
1899 PROCEDURE Insert_Row(
1900 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1901 x_return_status OUT NOCOPY VARCHAR2,
1902
1903 p_template_name IN VARCHAR2,
1904 p_template_id IN NUMBER,
1905 p_working_copy_flag IN VARCHAR2,
1906 p_intent IN VARCHAR2,
1907 p_status_code IN VARCHAR2,
1908 p_start_date IN DATE,
1909 p_end_date IN DATE,
1910 p_global_flag IN VARCHAR2,
1911 p_parent_template_id IN NUMBER,
1912 p_print_template_id IN NUMBER,
1913 p_contract_expert_enabled IN VARCHAR2,
1914 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+ : Contract Expert Changes
1915 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+ : Contract Expert Changes
1916 p_template_model_id IN NUMBER,
1917 p_instruction_text IN VARCHAR2,
1918 p_tmpl_numbering_scheme IN NUMBER,
1919 p_description IN VARCHAR2,
1920 p_approval_wf_key IN VARCHAR2 := NULL,
1921 p_cz_export_wf_key IN VARCHAR2 := NULL,
1922 p_orig_system_reference_code IN VARCHAR2,
1923 p_orig_system_reference_id1 IN NUMBER,
1924 p_orig_system_reference_id2 IN NUMBER,
1925 p_org_id IN NUMBER,
1926 p_cls_enabled IN VARCHAR2 := NULL,
1927 p_attribute_category IN VARCHAR2 := NULL,
1928 p_attribute1 IN VARCHAR2 := NULL,
1929 p_attribute2 IN VARCHAR2 := NULL,
1930 p_attribute3 IN VARCHAR2 := NULL,
1931 p_attribute4 IN VARCHAR2 := NULL,
1932 p_attribute5 IN VARCHAR2 := NULL,
1933 p_attribute6 IN VARCHAR2 := NULL,
1934 p_attribute7 IN VARCHAR2 := NULL,
1935 p_attribute8 IN VARCHAR2 := NULL,
1936 p_attribute9 IN VARCHAR2 := NULL,
1937 p_attribute10 IN VARCHAR2 := NULL,
1938 p_attribute11 IN VARCHAR2 := NULL,
1939 p_attribute12 IN VARCHAR2 := NULL,
1940 p_attribute13 IN VARCHAR2 := NULL,
1941 p_attribute14 IN VARCHAR2 := NULL,
1942 p_attribute15 IN VARCHAR2 := NULL,
1943 p_translated_from_tmpl_id IN NUMBER,
1944 p_language IN VARCHAR2,
1945 x_template_id OUT NOCOPY NUMBER
1946
1947 ) IS
1948 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1949 l_object_version_number OKC_TERMS_TEMPLATES_ALL.OBJECT_VERSION_NUMBER%TYPE;
1950 l_created_by OKC_TERMS_TEMPLATES_ALL.CREATED_BY%TYPE;
1951 l_creation_date OKC_TERMS_TEMPLATES_ALL.CREATION_DATE%TYPE;
1952 l_last_updated_by OKC_TERMS_TEMPLATES_ALL.LAST_UPDATED_BY%TYPE;
1953 l_last_update_login OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_LOGIN%TYPE;
1954 l_last_update_date OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_DATE%TYPE;
1955 BEGIN
1956
1957 x_return_status := G_RET_STS_SUCCESS;
1958
1959 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1960 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4200: Entered Insert_Row');
1961 END IF;
1962
1963 --- Setting item attributes
1964 -- Set primary key value
1965 IF( p_template_id IS NULL ) THEN
1966 x_return_status := Get_Seq_Id(
1967 p_template_id => p_template_id,
1968 x_template_id => x_template_id
1969 );
1970 --- If any errors happen abort API
1971 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1973 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1974 RAISE FND_API.G_EXC_ERROR;
1975 END IF;
1976 ELSE
1977 x_template_id := p_template_id;
1978 END IF;
1979
1980 -- Set Internal columns
1981 l_object_version_number := 1;
1982 l_creation_date := Sysdate;
1983 l_created_by := Fnd_Global.User_Id;
1984 l_last_update_date := l_creation_date;
1985 l_last_updated_by := l_created_by;
1986 l_last_update_login := Fnd_Global.Login_Id;
1987
1988
1989 --- Validate all non-missing attributes
1990 x_return_status := Validate_Record(
1991 p_validation_level => p_validation_level,
1992 p_template_id => x_template_id,
1993 p_template_name => p_template_name,
1994 p_working_copy_flag => p_working_copy_flag,
1995 p_intent => p_intent,
1996 p_status_code => p_status_code,
1997 p_start_date => p_start_date,
1998 p_end_date => p_end_date,
1999 p_global_flag => p_global_flag,
2000 p_parent_template_id => p_parent_template_id,
2001 p_print_template_id => p_print_template_id,
2002 p_contract_expert_enabled => p_contract_expert_enabled,
2003 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
2004 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
2005 p_template_model_id => p_template_model_id,
2006 p_instruction_text => p_instruction_text,
2007 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
2008 p_description => p_description,
2009 p_approval_wf_key => p_description,
2010 p_cz_export_wf_key => p_description,
2011 p_orig_system_reference_code => p_orig_system_reference_code,
2012 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2013 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2014 p_org_id => p_org_id,
2015 p_attribute_category => p_attribute_category,
2016 p_attribute1 => p_attribute1,
2017 p_attribute2 => p_attribute2,
2018 p_attribute3 => p_attribute3,
2019 p_attribute4 => p_attribute4,
2020 p_attribute5 => p_attribute5,
2021 p_attribute6 => p_attribute6,
2022 p_attribute7 => p_attribute7,
2023 p_attribute8 => p_attribute8,
2024 p_attribute9 => p_attribute9,
2025 p_attribute10 => p_attribute10,
2026 p_attribute11 => p_attribute11,
2027 p_attribute12 => p_attribute12,
2028 p_attribute13 => p_attribute13,
2029 p_attribute14 => p_attribute14,
2030 p_attribute15 => p_attribute15,
2031 p_translated_from_tmpl_id => p_translated_from_tmpl_id,
2032 p_language => p_language
2033 );
2034 --- If any errors happen abort API
2035 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2037 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2038 RAISE FND_API.G_EXC_ERROR;
2039 END IF;
2040
2041 --------------------------------------------
2042 -- Call the internal Insert_Row for each child record
2043 --------------------------------------------
2044 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2045 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4300: Call the internal Insert_Row for Base Table');
2046 END IF;
2047
2048 x_return_status := Insert_Row(
2049 p_template_id => x_template_id,
2050 p_template_name => p_template_name,
2051 p_working_copy_flag => p_working_copy_flag,
2052 p_intent => p_intent,
2053 p_status_code => p_status_code,
2054 p_start_date => p_start_date,
2055 p_end_date => p_end_date,
2056 p_global_flag => p_global_flag,
2057 p_parent_template_id => p_parent_template_id,
2058 p_print_template_id => p_print_template_id,
2059 p_contract_expert_enabled => p_contract_expert_enabled,
2060 p_cls_enabled => p_cls_enabled,
2061 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Changes
2062 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+ : Changes
2063 p_template_model_id => p_template_model_id,
2064 p_instruction_text => p_instruction_text,
2065 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
2066 p_description => p_description,
2067 p_approval_wf_key => p_approval_wf_key,
2068 p_cz_export_wf_key => p_cz_export_wf_key,
2069 p_orig_system_reference_code => p_orig_system_reference_code,
2070 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2071 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2072 p_org_id => p_org_id,
2073 p_attribute_category => p_attribute_category,
2074 p_attribute1 => p_attribute1,
2075 p_attribute2 => p_attribute2,
2076 p_attribute3 => p_attribute3,
2077 p_attribute4 => p_attribute4,
2078 p_attribute5 => p_attribute5,
2079 p_attribute6 => p_attribute6,
2080 p_attribute7 => p_attribute7,
2081 p_attribute8 => p_attribute8,
2082 p_attribute9 => p_attribute9,
2083 p_attribute10 => p_attribute10,
2084 p_attribute11 => p_attribute11,
2085 p_attribute12 => p_attribute12,
2086 p_attribute13 => p_attribute13,
2087 p_attribute14 => p_attribute14,
2088 p_attribute15 => p_attribute15,
2089 p_object_version_number => l_object_version_number,
2090 p_created_by => l_created_by,
2091 p_creation_date => l_creation_date,
2092 p_last_updated_by => l_last_updated_by,
2093 p_last_update_login => l_last_update_login,
2094 p_last_update_date => l_last_update_date,
2095 p_translated_from_tmpl_id => p_translated_from_tmpl_id,
2096 p_language => p_language
2097 );
2098 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2100 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2101 RAISE FND_API.G_EXC_ERROR;
2102 END IF;
2103
2104
2105
2106 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2107 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Leaving Insert_Row');
2108 END IF;
2109
2110 EXCEPTION
2111 WHEN FND_API.G_EXC_ERROR THEN
2112 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2113 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception');
2114 END IF;
2115 x_return_status := G_RET_STS_ERROR;
2116
2117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2118 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2119 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2120 END IF;
2121 x_return_status := G_RET_STS_UNEXP_ERROR;
2122
2123 WHEN OTHERS THEN
2124 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2125 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm);
2126 END IF;
2127 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2128 p_msg_name => G_UNEXPECTED_ERROR,
2129 p_token1 => G_SQLCODE_TOKEN,
2130 p_token1_value => sqlcode,
2131 p_token2 => G_SQLERRM_TOKEN,
2132 p_token2_value => sqlerrm);
2133 x_return_status := G_RET_STS_UNEXP_ERROR;
2134
2135 END Insert_Row;
2136 ---------------------------------------------------------------------------
2137 -- PROCEDURE Lock_Row
2138 ---------------------------------------------------------------------------
2139 -----------------------------------
2140 -- Lock_Row for:OKC_TERMS_TEMPLATES_ALL --
2141 -----------------------------------
2142 FUNCTION Lock_Row(
2143 p_template_id IN NUMBER,
2144 p_object_version_number IN NUMBER
2145 ) RETURN VARCHAR2 IS
2146 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
2147 E_Resource_Busy EXCEPTION;
2148 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2149
2150 CURSOR lock_csr (cp_template_id NUMBER, cp_object_version_number NUMBER) IS
2151 SELECT object_version_number
2152 FROM OKC_TERMS_TEMPLATES_ALL
2153 WHERE TEMPLATE_ID = cp_template_id
2154 AND ( object_version_number = cp_object_version_number
2155 OR cp_object_version_number IS NULL)
2156 FOR UPDATE OF object_version_number NOWAIT;
2157
2158 CURSOR lchk_csr (cp_template_id NUMBER) IS
2159 SELECT object_version_number
2160 FROM OKC_TERMS_TEMPLATES_ALL
2161 WHERE TEMPLATE_ID = cp_template_id;
2162
2163 l_return_status VARCHAR2(1);
2164
2165 l_object_version_number OKC_TERMS_TEMPLATES_ALL.OBJECT_VERSION_NUMBER%TYPE;
2166
2167 l_row_notfound BOOLEAN := FALSE;
2168 BEGIN
2169
2170 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2171 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4900: Entered Lock_Row');
2172 END IF;
2173
2174
2175 BEGIN
2176
2177 OPEN lock_csr( p_template_id, p_object_version_number );
2178 FETCH lock_csr INTO l_object_version_number;
2179 l_row_notfound := lock_csr%NOTFOUND;
2180 CLOSE lock_csr;
2181
2182 EXCEPTION
2183 WHEN E_Resource_Busy THEN
2184
2185 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2186 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Leaving Lock_Row:E_Resource_Busy Exception');
2187 END IF;
2188
2189 IF (lock_csr%ISOPEN) THEN
2190 CLOSE lock_csr;
2191 END IF;
2192 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
2193 RETURN( G_RET_STS_ERROR );
2194 END;
2195
2196 IF ( l_row_notfound ) THEN
2197 l_return_status := G_RET_STS_ERROR;
2198
2199 OPEN lchk_csr(p_template_id);
2200 FETCH lchk_csr INTO l_object_version_number;
2201 l_row_notfound := lchk_csr%NOTFOUND;
2202 CLOSE lchk_csr;
2203
2204 IF (l_row_notfound) THEN
2205 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
2206 ELSIF l_object_version_number > p_object_version_number THEN
2207 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
2208 ELSIF l_object_version_number = -1 THEN
2209 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2210 ELSE -- it can be the only above condition. It can happen after restore version
2211 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
2212 END IF;
2213 ELSE
2214 l_return_status := G_RET_STS_SUCCESS;
2215 END IF;
2216
2217 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2218 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5100: Leaving Lock_Row');
2219 END IF;
2220
2221 RETURN( l_return_status );
2222
2223 EXCEPTION
2224 WHEN OTHERS THEN
2225
2226 IF (lock_csr%ISOPEN) THEN
2227 CLOSE lock_csr;
2228 END IF;
2229 IF (lchk_csr%ISOPEN) THEN
2230 CLOSE lchk_csr;
2231 END IF;
2232
2233 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2234 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
2235 END IF;
2236
2237 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2238 p_msg_name => G_UNEXPECTED_ERROR,
2239 p_token1 => G_SQLCODE_TOKEN,
2240 p_token1_value => sqlcode,
2241 p_token2 => G_SQLERRM_TOKEN,
2242 p_token2_value => sqlerrm);
2243
2244 RETURN( G_RET_STS_UNEXP_ERROR );
2245 END Lock_Row;
2246
2247 -----------------------------------
2248 -- Lock_Row for:OKC_TERMS_TEMPLATES_ALL --
2249 -----------------------------------
2250 PROCEDURE Lock_Row(
2251 x_return_status OUT NOCOPY VARCHAR2,
2252
2253 p_template_id IN NUMBER,
2254 p_object_version_number IN NUMBER
2255 ) IS
2256 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
2257 BEGIN
2258
2259 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2260 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5700: Entered Lock_Row');
2261 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5800: Locking Row for Base Table');
2262 END IF;
2263
2264 --------------------------------------------
2265 -- Call the LOCK_ROW for each _B child record
2266 --------------------------------------------
2267 x_return_status := Lock_Row(
2268 p_template_id => p_template_id,
2269 p_object_version_number => p_object_version_number
2270 );
2271 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2273 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2274 RAISE FND_API.G_EXC_ERROR;
2275 END IF;
2276
2277
2278
2279 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2280 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: Leaving Lock_Row');
2281 END IF;
2282
2283 EXCEPTION
2284 WHEN FND_API.G_EXC_ERROR THEN
2285 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2286 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception');
2287 END IF;
2288 x_return_status := G_RET_STS_ERROR;
2289
2290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2291 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2292 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2293 END IF;
2294 x_return_status := G_RET_STS_UNEXP_ERROR;
2295
2296 WHEN OTHERS THEN
2297 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2298 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
2299 END IF;
2300 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2301 p_msg_name => G_UNEXPECTED_ERROR,
2302 p_token1 => G_SQLCODE_TOKEN,
2303 p_token1_value => sqlcode,
2304 p_token2 => G_SQLERRM_TOKEN,
2305 p_token2_value => sqlerrm);
2306 x_return_status := G_RET_STS_UNEXP_ERROR;
2307
2308 END Lock_Row;
2309 ---------------------------------------------------------------------------
2310 -- PROCEDURE Update_Row
2311 ---------------------------------------------------------------------------
2312 -------------------------------------
2313 -- Update_Row for:OKC_TERMS_TEMPLATES_ALL --
2314 -------------------------------------
2315 FUNCTION Update_Row(
2316 p_template_name IN VARCHAR2,
2317 p_template_id IN NUMBER,
2318 p_working_copy_flag IN VARCHAR2,
2319 p_intent IN VARCHAR2,
2320 p_status_code IN VARCHAR2,
2321 p_start_date IN DATE,
2322 p_end_date IN DATE,
2323 p_global_flag IN VARCHAR2,
2324 p_parent_template_id IN NUMBER,
2325 p_print_template_id IN NUMBER,
2326 p_contract_expert_enabled IN VARCHAR2,
2327 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
2328 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
2329 p_template_model_id IN NUMBER,
2330 p_instruction_text IN VARCHAR2,
2331 p_tmpl_numbering_scheme IN NUMBER,
2332 p_description IN VARCHAR2,
2333 p_approval_wf_key IN VARCHAR2,
2334 p_cz_export_wf_key IN VARCHAR2,
2335 p_orig_system_reference_code IN VARCHAR2,
2336 p_orig_system_reference_id1 IN NUMBER,
2337 p_orig_system_reference_id2 IN NUMBER,
2338 p_org_id IN NUMBER,
2339 p_cls_enabled IN VARCHAR2,
2340 p_attribute_category IN VARCHAR2,
2341 p_attribute1 IN VARCHAR2,
2342 p_attribute2 IN VARCHAR2,
2343 p_attribute3 IN VARCHAR2,
2344 p_attribute4 IN VARCHAR2,
2345 p_attribute5 IN VARCHAR2,
2346 p_attribute6 IN VARCHAR2,
2347 p_attribute7 IN VARCHAR2,
2348 p_attribute8 IN VARCHAR2,
2349 p_attribute9 IN VARCHAR2,
2350 p_attribute10 IN VARCHAR2,
2351 p_attribute11 IN VARCHAR2,
2352 p_attribute12 IN VARCHAR2,
2353 p_attribute13 IN VARCHAR2,
2354 p_attribute14 IN VARCHAR2,
2355 p_attribute15 IN VARCHAR2,
2356 p_object_version_number IN NUMBER,
2357 p_created_by IN NUMBER,
2358 p_creation_date IN DATE,
2359 p_last_updated_by IN NUMBER,
2360 p_last_update_login IN NUMBER,
2361 p_last_update_date IN DATE,
2362 p_translated_from_tmpl_id IN NUMBER,
2363 p_language IN VARCHAR2
2364
2365 ) RETURN VARCHAR2 IS
2366 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2367 BEGIN
2368
2369 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2370 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6400: Entered Update_Row');
2371 END IF;
2372
2373 UPDATE OKC_TERMS_TEMPLATES_ALL
2374 SET TEMPLATE_NAME = p_template_name,
2375 WORKING_COPY_FLAG = NVL(p_working_copy_flag,'N'),
2376 INTENT = p_intent,
2377 STATUS_CODE = p_status_code,
2378 START_DATE = TRUNC(p_start_date),
2379 END_DATE = TRUNC(p_end_date),
2380 GLOBAL_FLAG = NVL(p_global_flag,'N'),
2381 PARENT_TEMPLATE_ID = p_parent_template_id,
2382 PRINT_TEMPLATE_ID = p_print_template_id,
2383 CONTRACT_EXPERT_ENABLED = NVL(p_contract_expert_enabled,'N'),
2384 XPRT_CLAUSE_MANDATORY_FLAG = NVL(p_xprt_clause_mandatory_flag,'N'), -- Added for 11.5.10+: Contract Expert Changes
2385 XPRT_SCN_CODE = p_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
2386 TEMPLATE_MODEL_ID = p_template_model_id,
2387 INSTRUCTION_TEXT = p_instruction_text,
2388 TMPL_NUMBERING_SCHEME = p_tmpl_numbering_scheme,
2389 DESCRIPTION = p_description,
2390 APPROVAL_WF_KEY = p_approval_wf_key,
2391 CZ_EXPORT_WF_KEY = p_cz_export_wf_key,
2392 ORIG_SYSTEM_REFERENCE_CODE = p_orig_system_reference_code,
2393 ORIG_SYSTEM_REFERENCE_ID1 = p_orig_system_reference_id1,
2394 ORIG_SYSTEM_REFERENCE_ID2 = p_orig_system_reference_id2,
2395 ORG_ID = p_org_id,
2396 CLS_ENABLED = NVL(p_cls_enabled,'N'),
2397 ATTRIBUTE_CATEGORY = p_attribute_category,
2398 ATTRIBUTE1 = p_attribute1,
2399 ATTRIBUTE2 = p_attribute2,
2400 ATTRIBUTE3 = p_attribute3,
2401 ATTRIBUTE4 = p_attribute4,
2402 ATTRIBUTE5 = p_attribute5,
2403 ATTRIBUTE6 = p_attribute6,
2404 ATTRIBUTE7 = p_attribute7,
2405 ATTRIBUTE8 = p_attribute8,
2406 ATTRIBUTE9 = p_attribute9,
2407 ATTRIBUTE10 = p_attribute10,
2408 ATTRIBUTE11 = p_attribute11,
2409 ATTRIBUTE12 = p_attribute12,
2410 ATTRIBUTE13 = p_attribute13,
2411 ATTRIBUTE14 = p_attribute14,
2412 ATTRIBUTE15 = p_attribute15,
2413 OBJECT_VERSION_NUMBER = p_object_version_number,
2414 LAST_UPDATED_BY = p_last_updated_by,
2415 LAST_UPDATE_LOGIN = p_last_update_login,
2416 LAST_UPDATE_DATE = p_last_update_date,
2417 --MLS for templates
2418 TRANSLATED_FROM_TMPL_ID = p_translated_from_tmpl_id,
2419 LANGUAGE = p_language
2420 WHERE TEMPLATE_ID = p_template_id;
2421
2422 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2423 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6500: Leaving Update_Row');
2424 END IF;
2425
2426 RETURN G_RET_STS_SUCCESS ;
2427
2428 EXCEPTION
2429 WHEN OTHERS THEN
2430
2431 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2432 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
2433 END IF;
2434
2435 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2436 p_msg_name => G_UNEXPECTED_ERROR,
2437 p_token1 => G_SQLCODE_TOKEN,
2438 p_token1_value => sqlcode,
2439 p_token2 => G_SQLERRM_TOKEN,
2440 p_token2_value => sqlerrm);
2441
2442 RETURN G_RET_STS_UNEXP_ERROR ;
2443
2444 END Update_Row;
2445
2446 -------------------------------------
2447 -- Update_Row for:OKC_TERMS_TEMPLATES_ALL --
2448 -------------------------------------
2449 PROCEDURE Update_Row(
2450 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2451
2452 x_return_status OUT NOCOPY VARCHAR2,
2453
2454 p_template_name IN VARCHAR2,
2455 p_template_id IN NUMBER,
2456 p_working_copy_flag IN VARCHAR2,
2457 p_intent IN VARCHAR2,
2458 p_status_code IN VARCHAR2,
2459 p_start_date IN DATE,
2460 p_end_date IN DATE,
2461 p_global_flag IN VARCHAR2,
2462 p_parent_template_id IN NUMBER,
2463 p_print_template_id IN NUMBER,
2464 p_contract_expert_enabled IN VARCHAR2,
2465 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
2466 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
2467 p_template_model_id IN NUMBER,
2468 p_instruction_text IN VARCHAR2,
2469 p_tmpl_numbering_scheme IN NUMBER,
2470 p_description IN VARCHAR2,
2471 p_approval_wf_key IN VARCHAR2 := NULL,
2472 p_cz_export_wf_key IN VARCHAR2 := NULL,
2473 p_orig_system_reference_code IN VARCHAR2,
2474 p_orig_system_reference_id1 IN NUMBER,
2475 p_orig_system_reference_id2 IN NUMBER,
2476 p_org_id IN NUMBER := NULL,
2477 p_cls_enabled IN VARCHAR2,
2478 p_attribute_category IN VARCHAR2 := NULL,
2479 p_attribute1 IN VARCHAR2 := NULL,
2480 p_attribute2 IN VARCHAR2 := NULL,
2481 p_attribute3 IN VARCHAR2 := NULL,
2482 p_attribute4 IN VARCHAR2 := NULL,
2483 p_attribute5 IN VARCHAR2 := NULL,
2484 p_attribute6 IN VARCHAR2 := NULL,
2485 p_attribute7 IN VARCHAR2 := NULL,
2486 p_attribute8 IN VARCHAR2 := NULL,
2487 p_attribute9 IN VARCHAR2 := NULL,
2488 p_attribute10 IN VARCHAR2 := NULL,
2489 p_attribute11 IN VARCHAR2 := NULL,
2490 p_attribute12 IN VARCHAR2 := NULL,
2491 p_attribute13 IN VARCHAR2 := NULL,
2492 p_attribute14 IN VARCHAR2 := NULL,
2493 p_attribute15 IN VARCHAR2 := NULL,
2494 p_translated_from_tmpl_id IN NUMBER,
2495 p_language IN VARCHAR2,
2496
2497 p_object_version_number IN NUMBER
2498
2499 ) IS
2500 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2501 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
2502 l_working_copy_flag OKC_TERMS_TEMPLATES_ALL.WORKING_COPY_FLAG%TYPE;
2503 l_intent OKC_TERMS_TEMPLATES_ALL.INTENT%TYPE;
2504 l_status_code OKC_TERMS_TEMPLATES_ALL.STATUS_CODE%TYPE;
2505 l_start_date OKC_TERMS_TEMPLATES_ALL.START_DATE%TYPE;
2506 l_end_date OKC_TERMS_TEMPLATES_ALL.END_DATE%TYPE;
2507 l_global_flag OKC_TERMS_TEMPLATES_ALL.GLOBAL_FLAG%TYPE;
2508 l_parent_template_id OKC_TERMS_TEMPLATES_ALL.PARENT_TEMPLATE_ID%TYPE;
2509 l_print_template_id OKC_TERMS_TEMPLATES_ALL.PRINT_TEMPLATE_ID%TYPE;
2510 l_contract_expert_enabled OKC_TERMS_TEMPLATES_ALL.CONTRACT_EXPERT_ENABLED%TYPE;
2511 l_cls_enabled OKC_TERMS_TEMPLATES_ALL.CLS_ENABLED%TYPE;
2512 l_xprt_clause_mandatory_flag OKC_TERMS_TEMPLATES_ALL.XPRT_CLAUSE_MANDATORY_FLAG%TYPE; -- Added for 11.5.10+:Contract Expert Changes
2513 l_xprt_scn_code OKC_TERMS_TEMPLATES_ALL.XPRT_SCN_CODE%TYPE; -- Added for 11.5.10+: Contract Expert Changes
2514 l_template_model_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_MODEL_ID%TYPE;
2515 l_instruction_text OKC_TERMS_TEMPLATES_ALL.INSTRUCTION_TEXT%TYPE;
2516 l_tmpl_numbering_scheme OKC_TERMS_TEMPLATES_ALL.TMPL_NUMBERING_SCHEME%TYPE;
2517 l_description OKC_TERMS_TEMPLATES_ALL.DESCRIPTION%TYPE;
2518 l_approval_wf_key OKC_TERMS_TEMPLATES_ALL.APPROVAL_WF_KEY%TYPE;
2519 l_cz_export_wf_key OKC_TERMS_TEMPLATES_ALL.CZ_EXPORT_WF_KEY%TYPE;
2520 l_orig_system_reference_code OKC_TERMS_TEMPLATES_ALL.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
2521 l_orig_system_reference_id1 OKC_TERMS_TEMPLATES_ALL.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
2522 l_orig_system_reference_id2 OKC_TERMS_TEMPLATES_ALL.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
2523 l_org_id OKC_TERMS_TEMPLATES_ALL.ORG_ID%TYPE;
2524 l_attribute_category OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE_CATEGORY%TYPE;
2525 l_attribute1 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE1%TYPE;
2526 l_attribute2 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE2%TYPE;
2527 l_attribute3 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE3%TYPE;
2528 l_attribute4 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE4%TYPE;
2529 l_attribute5 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE5%TYPE;
2530 l_attribute6 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE6%TYPE;
2531 l_attribute7 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE7%TYPE;
2532 l_attribute8 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE8%TYPE;
2533 l_attribute9 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE9%TYPE;
2534 l_attribute10 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE10%TYPE;
2535 l_attribute11 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE11%TYPE;
2536 l_attribute12 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE12%TYPE;
2537 l_attribute13 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE13%TYPE;
2538 l_attribute14 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE14%TYPE;
2539 l_attribute15 OKC_TERMS_TEMPLATES_ALL.ATTRIBUTE15%TYPE;
2540 l_object_version_number OKC_TERMS_TEMPLATES_ALL.OBJECT_VERSION_NUMBER%TYPE;
2541 l_created_by OKC_TERMS_TEMPLATES_ALL.CREATED_BY%TYPE;
2542 l_creation_date OKC_TERMS_TEMPLATES_ALL.CREATION_DATE%TYPE;
2543 l_last_updated_by OKC_TERMS_TEMPLATES_ALL.LAST_UPDATED_BY%TYPE;
2544 l_last_update_login OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_LOGIN%TYPE;
2545 l_last_update_date OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_DATE%TYPE;
2546 l_translated_from_tmpl_id OKC_TERMS_TEMPLATES_ALL.TRANSLATED_FROM_TMPL_ID%TYPE;
2547 l_language OKC_TERMS_TEMPLATES_ALL.LANGUAGE%TYPE;
2548
2549 l_xprt_enabled_flag OKC_TERMS_TEMPLATES_ALL.contract_expert_enabled%TYPE;
2550 CURSOR c_template_xprt_enabled_csr IS
2551 SELECT contract_expert_enabled
2552 FROM OKC_TERMS_TEMPLATES_ALL
2553 WHERE template_id = p_template_id;
2554
2555 l_msg_data VARCHAR2(1000);
2556 l_msg_count NUMBER;
2557
2558 BEGIN
2559
2560 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2561 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Entered Update_Row');
2562 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Locking _B row');
2563 END IF;
2564
2565 x_return_status := Lock_row(
2566 p_template_id => p_template_id,
2567 p_object_version_number => p_object_version_number
2568 );
2569 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2571 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2572 RAISE FND_API.G_EXC_ERROR;
2573 END IF;
2574
2575
2576 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7300: Setting attributes');
2578 END IF;
2579
2580 x_return_status := Set_Attributes(
2581 p_template_name => p_template_name,
2582 p_template_id => p_template_id,
2583 p_working_copy_flag => p_working_copy_flag,
2584 p_intent => p_intent,
2585 p_status_code => p_status_code,
2586 p_start_date => p_start_date,
2587 p_end_date => p_end_date,
2588 p_global_flag => p_global_flag,
2589 p_parent_template_id => p_parent_template_id,
2590 p_print_template_id => p_print_template_id,
2591 p_contract_expert_enabled => p_contract_expert_enabled,
2592 p_cls_enabled => p_cls_enabled,
2593 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
2594 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
2595 p_template_model_id => p_template_model_id,
2596 p_instruction_text => p_instruction_text,
2597 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
2598 p_description => p_description,
2599 p_approval_wf_key => p_approval_wf_key,
2600 p_cz_export_wf_key => p_cz_export_wf_key,
2601 p_orig_system_reference_code => p_orig_system_reference_code,
2602 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2603 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2604 p_org_id => p_org_id,
2605 p_attribute_category => p_attribute_category,
2606 p_attribute1 => p_attribute1,
2607 p_attribute2 => p_attribute2,
2608 p_attribute3 => p_attribute3,
2609 p_attribute4 => p_attribute4,
2610 p_attribute5 => p_attribute5,
2611 p_attribute6 => p_attribute6,
2612 p_attribute7 => p_attribute7,
2613 p_attribute8 => p_attribute8,
2614 p_attribute9 => p_attribute9,
2615 p_attribute10 => p_attribute10,
2616 p_attribute11 => p_attribute11,
2617 p_attribute12 => p_attribute12,
2618 p_attribute13 => p_attribute13,
2619 p_attribute14 => p_attribute14,
2620 p_attribute15 => p_attribute15,
2621 p_translated_from_tmpl_id => p_translated_from_tmpl_id,
2622 p_language => p_language,
2623 p_object_version_number => p_object_version_number,
2624 x_template_name => l_template_name,
2625 x_working_copy_flag => l_working_copy_flag,
2626 x_intent => l_intent,
2627 x_status_code => l_status_code,
2628 x_start_date => l_start_date,
2629 x_end_date => l_end_date,
2630 x_global_flag => l_global_flag,
2631 x_parent_template_id => l_parent_template_id,
2632 x_print_template_id => l_print_template_id,
2633 x_contract_expert_enabled => l_contract_expert_enabled,
2634 x_cls_enabled => l_cls_enabled,
2635 x_xprt_clause_mandatory_flag => l_xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
2636 x_xprt_scn_code => l_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
2637 x_template_model_id => l_template_model_id,
2638 x_instruction_text => l_instruction_text,
2639 x_tmpl_numbering_scheme => l_tmpl_numbering_scheme,
2640 x_description => l_description,
2641 x_approval_wf_key => l_approval_wf_key,
2642 x_cz_export_wf_key => l_cz_export_wf_key,
2643 x_orig_system_reference_code => l_orig_system_reference_code,
2644 x_orig_system_reference_id1 => l_orig_system_reference_id1,
2645 x_orig_system_reference_id2 => l_orig_system_reference_id2,
2646 x_org_id => l_org_id,
2647 x_attribute_category => l_attribute_category,
2648 x_attribute1 => l_attribute1,
2649 x_attribute2 => l_attribute2,
2650 x_attribute3 => l_attribute3,
2651 x_attribute4 => l_attribute4,
2652 x_attribute5 => l_attribute5,
2653 x_attribute6 => l_attribute6,
2654 x_attribute7 => l_attribute7,
2655 x_attribute8 => l_attribute8,
2656 x_attribute9 => l_attribute9,
2657 x_attribute10 => l_attribute10,
2658 x_attribute11 => l_attribute11,
2659 x_attribute12 => l_attribute12,
2660 x_attribute13 => l_attribute13,
2661 x_attribute14 => l_attribute14,
2662 x_attribute15 => l_attribute15,
2663 x_translated_from_tmpl_id => l_translated_from_tmpl_id,
2664 x_language => l_language
2665 );
2666 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2668 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2669 RAISE FND_API.G_EXC_ERROR;
2670 END IF;
2671
2672 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2673 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7400: Record Validation');
2674 END IF;
2675
2676 --- Validate all non-missing attributes
2677 x_return_status := Validate_Record(
2678 p_validation_level => p_validation_level,
2679 p_template_id => p_template_id,
2680 p_template_name => l_template_name,
2681 p_working_copy_flag => l_working_copy_flag,
2682 p_intent => l_intent,
2683 p_status_code => l_status_code,
2684 p_start_date => l_start_date,
2685 p_end_date => l_end_date,
2686 p_global_flag => l_global_flag,
2687 p_parent_template_id => l_parent_template_id,
2688 p_print_template_id => l_print_template_id,
2689 p_contract_expert_enabled => l_contract_expert_enabled,
2690 p_xprt_clause_mandatory_flag => l_xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
2691 p_xprt_scn_code => l_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
2692 p_template_model_id => l_template_model_id,
2693 p_instruction_text => l_instruction_text,
2694 p_tmpl_numbering_scheme => l_tmpl_numbering_scheme,
2695 p_description => l_description,
2696 p_approval_wf_key => l_approval_wf_key,
2697 p_cz_export_wf_key => l_cz_export_wf_key,
2698 p_orig_system_reference_code => l_orig_system_reference_code,
2699 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2700 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2701 p_org_id => l_org_id,
2702 p_attribute_category => l_attribute_category,
2703 p_attribute1 => l_attribute1,
2704 p_attribute2 => l_attribute2,
2705 p_attribute3 => l_attribute3,
2706 p_attribute4 => l_attribute4,
2707 p_attribute5 => l_attribute5,
2708 p_attribute6 => l_attribute6,
2709 p_attribute7 => l_attribute7,
2710 p_attribute8 => l_attribute8,
2711 p_attribute9 => l_attribute9,
2712 p_attribute10 => l_attribute10,
2713 p_attribute11 => l_attribute11,
2714 p_attribute12 => l_attribute12,
2715 p_attribute13 => l_attribute13,
2716 p_attribute14 => l_attribute14,
2717 p_attribute15 => l_attribute15,
2718 p_translated_from_tmpl_id => l_translated_from_tmpl_id,
2719 p_language => l_language
2720 );
2721 --- If any errors happen abort API
2722 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2724 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2725 RAISE FND_API.G_EXC_ERROR;
2726 END IF;
2727
2728 --Bug 4106468
2729 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2730 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7450: Deleting expert Rule association');
2731 END IF;
2732
2733 OPEN c_template_xprt_enabled_csr;
2734 FETCH c_template_xprt_enabled_csr INTO l_xprt_enabled_flag;
2735 CLOSE c_template_xprt_enabled_csr;
2736
2737 IF (l_xprt_enabled_flag = 'Y' AND p_contract_expert_enabled = 'N' ) THEN
2738 -- Setting expert flag to 'N' by user should remove template - rule associations
2739 OKC_XPRT_TMPL_RULE_ASSNS_PVT.delete_template_rule_assns
2740 (
2741 p_api_version => 1.0,
2742 p_init_msg_list => FND_API.G_FALSE,
2743 p_commit => FND_API.G_FALSE,
2744 p_template_id => p_template_id,
2745 x_return_status => x_return_status,
2746 x_msg_data => l_msg_data,
2747 x_msg_count => l_msg_count
2748 ) ;
2749
2750 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2751 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7450: Deleting expert Rule association return_status:'||x_return_status);
2752 END IF;
2753
2754 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2756 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2757 RAISE FND_API.G_EXC_ERROR;
2758 END IF;
2759 END IF;
2760
2761
2762 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2763 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Filling WHO columns');
2764 END IF;
2765
2766 -- Filling who columns
2767 l_last_update_date := SYSDATE;
2768 l_last_updated_by := FND_GLOBAL.USER_ID;
2769 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2770
2771 -- Object version increment
2772 IF Nvl(l_object_version_number, 0) >= 0 THEN
2773 l_object_version_number := Nvl(p_object_version_number, 0) + 1;
2774 END IF;
2775
2776 --------------------------------------------
2777 -- Call the Update_Row for each child record
2778 --------------------------------------------
2779 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2780 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: Updating Row');
2781 END IF;
2782
2783 x_return_status := Update_Row(
2784 p_template_id => p_template_id,
2785 p_template_name => l_template_name,
2786 p_working_copy_flag => l_working_copy_flag,
2787 p_intent => l_intent,
2788 p_status_code => l_status_code,
2789 p_start_date => l_start_date,
2790 p_end_date => l_end_date,
2791 p_global_flag => l_global_flag,
2792 p_parent_template_id => l_parent_template_id,
2793 p_print_template_id => l_print_template_id,
2794 p_contract_expert_enabled => l_contract_expert_enabled,
2795 p_xprt_clause_mandatory_flag => l_xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
2796 p_xprt_scn_code => l_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
2797 p_template_model_id => l_template_model_id,
2798 p_instruction_text => l_instruction_text,
2799 p_tmpl_numbering_scheme => l_tmpl_numbering_scheme,
2800 p_description => l_description,
2801 p_approval_wf_key => l_approval_wf_key,
2802 p_cz_export_wf_key => l_cz_export_wf_key,
2803 p_orig_system_reference_code => l_orig_system_reference_code,
2804 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2805 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2806 p_org_id => l_org_id,
2807 p_cls_enabled => p_cls_enabled,
2808 p_attribute_category => l_attribute_category,
2809 p_attribute1 => l_attribute1,
2810 p_attribute2 => l_attribute2,
2811 p_attribute3 => l_attribute3,
2812 p_attribute4 => l_attribute4,
2813 p_attribute5 => l_attribute5,
2814 p_attribute6 => l_attribute6,
2815 p_attribute7 => l_attribute7,
2816 p_attribute8 => l_attribute8,
2817 p_attribute9 => l_attribute9,
2818 p_attribute10 => l_attribute10,
2819 p_attribute11 => l_attribute11,
2820 p_attribute12 => l_attribute12,
2821 p_attribute13 => l_attribute13,
2822 p_attribute14 => l_attribute14,
2823 p_attribute15 => l_attribute15,
2824 p_object_version_number => l_object_version_number,
2825 p_created_by => l_created_by,
2826 p_creation_date => l_creation_date,
2827 p_last_updated_by => l_last_updated_by,
2828 p_last_update_login => l_last_update_login,
2829 p_last_update_date => l_last_update_date,
2830 p_translated_from_tmpl_id => l_translated_from_tmpl_id,
2831 p_language => l_language
2832 );
2833 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2835 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2836 RAISE FND_API.G_EXC_ERROR;
2837 END IF;
2838
2839
2840 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2841 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7800: Leaving Update_Row');
2842 END IF;
2843
2844 EXCEPTION
2845 WHEN FND_API.G_EXC_ERROR THEN
2846 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2847 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception');
2848 END IF;
2849 x_return_status := G_RET_STS_ERROR;
2850
2851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2852 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2853 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2854 END IF;
2855 x_return_status := G_RET_STS_UNEXP_ERROR;
2856
2857 WHEN OTHERS THEN
2858 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2859 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
2860 END IF;
2861 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2862 p_msg_name => G_UNEXPECTED_ERROR,
2863 p_token1 => G_SQLCODE_TOKEN,
2864 p_token1_value => sqlcode,
2865 p_token2 => G_SQLERRM_TOKEN,
2866 p_token2_value => sqlerrm);
2867 x_return_status := G_RET_STS_UNEXP_ERROR;
2868
2869 END Update_Row;
2870
2871 ---------------------------------------------------------------------------
2872 -- PROCEDURE Delete_Row
2873 ---------------------------------------------------------------------------
2874 -------------------------------------
2875 -- Delete_Row for:OKC_TERMS_TEMPLATES_ALL --
2876 -------------------------------------
2877 FUNCTION Delete_Row(
2878 p_template_id IN NUMBER
2879 ) RETURN VARCHAR2 IS
2880 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
2881 BEGIN
2882
2883 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2884 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8200: Entered Delete_Row');
2885 END IF;
2886
2887 DELETE FROM OKC_TERMS_TEMPLATES_ALL WHERE TEMPLATE_ID = p_TEMPLATE_ID;
2888
2889 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2890 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8300: Leaving Delete_Row');
2891 END IF;
2892
2893 RETURN( G_RET_STS_SUCCESS );
2894
2895 EXCEPTION
2896 WHEN OTHERS THEN
2897
2898 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2899 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
2900 END IF;
2901
2902 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2903 p_msg_name => G_UNEXPECTED_ERROR,
2904 p_token1 => G_SQLCODE_TOKEN,
2905 p_token1_value => sqlcode,
2906 p_token2 => G_SQLERRM_TOKEN,
2907 p_token2_value => sqlerrm);
2908
2909 RETURN( G_RET_STS_UNEXP_ERROR );
2910
2911 END Delete_Row;
2912
2913 -------------------------------------
2914 -- Delete_Row for:OKC_TERMS_TEMPLATES_ALL --
2915 -------------------------------------
2916 PROCEDURE Delete_Row(
2917 x_return_status OUT NOCOPY VARCHAR2,
2918 p_template_id IN NUMBER,
2919 p_object_version_number IN NUMBER,
2920 p_delete_parent_yn IN VARCHAR2 := 'N' --If set to 'Y', delete template without checking for translated templates.
2921 ) IS
2922 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
2923 l_template_model_id OKC_TERMS_TEMPLATES_ALL.template_model_id%TYPE;
2924 l_orig_sys_ref okc_exprt_import_refs.orig_sys_ref%TYPE;
2925 l_msg_count NUMBER;
2926 l_msg_data VARCHAR2(4000);
2927 l_status_code VARCHAR2(30);
2928 l_dummy_var VARCHAR2(1);
2929 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
2930
2931 CURSOR c_chk_template_csr IS
2932 SELECT template_model_id, status_code
2933 FROM OKC_TERMS_TEMPLATES_ALL
2934 WHERE template_id = p_template_id;
2935
2936 CURSOR csr_template_model_details IS
2937 SELECT orig_sys_ref
2938 FROM cz_src_devl_projects_v
2939 WHERE product_key='510:'||p_template_id ;
2940
2941 CURSOR c_chk_template_trans IS
2942 SELECT '!' , parent.template_name
2943 FROM okc_terms_templates_all translated,
2944 okc_terms_templates_all parent
2945 WHERE translated.translated_from_tmpl_id = p_template_id
2946 AND parent.template_id = translated.translated_from_tmpl_id;
2947 BEGIN
2948
2949 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2950 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8800: Entered Delete_Row');
2951 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8900: Locking _B row');
2952 END IF;
2953
2954 --------------------------------------------
2955 -- check if template has any translated dependents
2956 l_dummy_var := '?';
2957 OPEN c_chk_template_trans;
2958 FETCH c_chk_template_trans INTO l_dummy_var,l_template_name;
2959 CLOSE c_chk_template_trans;
2960
2961 IF (l_dummy_var = '!' AND p_delete_parent_yn = 'N') THEN
2962 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2963 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8910: - Error:Template has translated references and cannot be deleted');
2964 END IF;
2965 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_REF_DEL','TMPL1' , l_template_name);
2966 x_return_status := G_RET_STS_ERROR;
2967 RETURN ;
2968 END IF;
2969
2970 --------------------------------------------
2971
2972
2973 --------------------------------------------
2974 -- check if template has model and if so get model details
2975 OPEN c_chk_template_csr;
2976 FETCH c_chk_template_csr INTO l_template_model_id, l_status_code;
2977 CLOSE c_chk_template_csr;
2978
2979 IF l_template_model_id IS NOT NULL THEN
2980 OPEN csr_template_model_details;
2981 FETCH csr_template_model_details INTO l_orig_sys_ref;
2982 CLOSE csr_template_model_details;
2983 END IF; -- l_template_model_id is not null
2984
2985 --------------------------------------------
2986
2987
2988 x_return_status := Lock_row(
2989 p_template_id => p_template_id,
2990 p_object_version_number => p_object_version_number
2991 );
2992 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2994 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2995 RAISE FND_API.G_EXC_ERROR;
2996 END IF;
2997
2998
2999 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3000 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9100: Removing _B row');
3001 END IF;
3002 x_return_status := Delete_Row( p_template_id => p_template_id );
3003
3004 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3006 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3007 RAISE FND_API.G_EXC_ERROR;
3008 END IF;
3009
3010 --------------------------------------------
3011 -- if template was expert enabled then delete the model
3012 -- bug 3420123
3013 --------------------------------------------
3014
3015 --------------------------------------------
3016 -- Additionally delete the model only if the status is in DRAFT
3017 -- bug 3441068
3018 --------------------------------------------
3019 IF (l_orig_sys_ref IS NOT NULL AND l_template_model_id IS NOT NULL AND l_status_code = 'DRAFT') THEN
3020 -- delete old import
3021 --Modified 11.5.10+ CE
3022 /* OKC_XPRT_CZ_INT_PVT.delete_model
3023 (p_api_version => 1.0,
3024 p_model_id => l_template_model_id,
3025 p_orig_sys_ref => l_orig_sys_ref,
3026 x_return_status => x_return_status,
3027 x_msg_count => l_msg_count,
3028 x_msg_data => l_msg_data); */
3029
3030
3031 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3032 RAISE FND_API.G_EXC_ERROR;
3033 END IF; -- delete model successful
3034
3035 -- delete record from okc_exprt_import_refs
3036 /* DELETE FROM okc_exprt_import_refs
3037 WHERE object_type_code = 'TEMPLATEMODEL'
3038 AND model_id = l_template_model_id ; */
3039
3040 END IF; -- template_model_id is NOT NULL
3041
3042 --------------------------------------------
3043
3044
3045 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3046 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9300: Leaving Delete_Row');
3047 END IF;
3048
3049 EXCEPTION
3050 WHEN FND_API.G_EXC_ERROR THEN
3051 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3052 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception');
3053 END IF;
3054 x_return_status := G_RET_STS_ERROR;
3055
3056 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3057 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3058 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
3059 END IF;
3060 x_return_status := G_RET_STS_UNEXP_ERROR;
3061
3062 WHEN OTHERS THEN
3063 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3064 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
3065 END IF;
3066 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3067 p_msg_name => G_UNEXPECTED_ERROR,
3068 p_token1 => G_SQLCODE_TOKEN,
3069 p_token1_value => sqlcode,
3070 p_token2 => G_SQLERRM_TOKEN,
3071 p_token2_value => sqlerrm);
3072 x_return_status := G_RET_STS_UNEXP_ERROR;
3073
3074 END Delete_Row;
3075
3076 PROCEDURE Update_Template_Id(
3077 x_return_status OUT NOCOPY VARCHAR2,
3078 p_old_template_id IN NUMBER,
3079 p_new_template_id IN NUMBER
3080 ) IS
3081 l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Id';
3082 BEGIN
3083 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3084 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9700: Entered Update_Template_Id');
3085 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9800: Locking Row');
3086 END IF;
3087 --------------------------------------------
3088 Lock_Row(
3089 x_return_status => x_return_status,
3090 p_template_id => p_old_template_id,
3091 p_object_version_number => NULL
3092 );
3093 --------------------------------------------
3094 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3095 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3096 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3097 RAISE FND_API.G_EXC_ERROR ;
3098 END IF;
3099 --------------------------------------------
3100 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3101 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9900: Updating Row');
3102 END IF;
3103 UPDATE OKC_TERMS_TEMPLATES_ALL
3104 SET TEMPLATE_ID = p_new_template_id,
3105 WORKING_COPY_FLAG = 'N',
3106 PARENT_TEMPLATE_ID = NULL,
3107 STATUS_CODE = 'APPROVED',
3108 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
3109 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3110 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
3111 LAST_UPDATE_DATE = Sysdate
3112 WHERE template_id= p_old_template_id;
3113 --------------------------------------------
3114 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3115 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9910: Leaving Update_Template_Id');
3116 END IF;
3117 EXCEPTION
3118 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3119 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3120 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9920: Leaving Update_Template_Id: G_EXCEPTION_UNEXPECTED_ERROR Exception');
3121
3122 END IF;
3123 WHEN OTHERS THEN
3124 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3125 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9930: Leaving Update_Template_Id because of EXCEPTION: '||sqlerrm);
3126 END IF;
3127
3128 x_return_status := G_RET_STS_UNEXP_ERROR ;
3129 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3130 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3131 END IF;
3132 END Update_Template_Id;
3133
3134 END OKC_TERMS_TEMPLATES_PVT;