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