[Home] [Help]
PACKAGE BODY: APPS.OKC_TEMPLATE_USAGES_PVT
Source
1 PACKAGE BODY OKC_TEMPLATE_USAGES_PVT AS
2 /* $Header: OKCVTMPLUSGB.pls 120.1.12000000.3 2007/02/26 22:30:51 ssivarap ship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
10 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
11 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
12 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
13 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
14 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
15 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
16 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
17 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
18 G_LOCK_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_LOCK_RECORD_DELETED;
19 ---------------------------------------------------------------------------
20 -- VALIDATION LEVELS
21 ---------------------------------------------------------------------------
22 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
23 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
24 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
25 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
26 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
27 ---------------------------------------------------------------------------
28 -- GLOBAL VARIABLES
29 ---------------------------------------------------------------------------
30 G_PKG_NAME CONSTANT VARCHAR2(40) := 'OKC_TEMPLATE_USAGES_PVT';
31 G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME||'.';
32 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
33 G_INTERNAL_PARTY_CODE CONSTANT VARCHAR2(30) := 'INTERNAL_ORG';
34
35 ------------------------------------------------------------------------------
36 -- GLOBAL CONSTANTS
37 ------------------------------------------------------------------------------
38 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
39 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
40 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
41 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
42 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
43
44 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
45 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
46 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
47
48 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
49 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
50 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
51
52
53 ---------------------------------------------------------------------------
54 -- FUNCTION get_rec for: OKC_TEMPLATE_USAGES
55 ---------------------------------------------------------------------------
56 FUNCTION Get_Rec (
57 p_document_type IN VARCHAR2,
58 p_document_id IN NUMBER,
59
60 x_template_id OUT NOCOPY NUMBER,
61 x_doc_numbering_scheme OUT NOCOPY NUMBER,
62 x_document_number OUT NOCOPY VARCHAR2,
63 x_article_effective_date OUT NOCOPY DATE,
64 x_config_header_id OUT NOCOPY NUMBER,
65 x_config_revision_number OUT NOCOPY NUMBER,
66 x_valid_config_yn OUT NOCOPY VARCHAR2,
67 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
68 x_orig_system_reference_id1 OUT NOCOPY NUMBER,
69 x_orig_system_reference_id2 OUT NOCOPY NUMBER,
70 x_object_version_number OUT NOCOPY NUMBER,
71 x_created_by OUT NOCOPY NUMBER,
72 x_creation_date OUT NOCOPY DATE,
73 x_last_updated_by OUT NOCOPY NUMBER,
74 x_last_update_login OUT NOCOPY NUMBER,
75 x_last_update_date OUT NOCOPY DATE,
76 --added for 10+ word integration and deviations report
77 x_authoring_party_code OUT NOCOPY VARCHAR2,
78 x_contract_source_code OUT NOCOPY VARCHAR2,
79 x_approval_abstract_text OUT NOCOPY CLOB,
80 x_autogen_deviations_flag OUT NOCOPY VARCHAR2,
81 --added for bug# 3990983
82 x_source_change_allowed_flag OUT NOCOPY VARCHAR2,
83 x_lock_terms_flag OUT NOCOPY VARCHAR2,
84 x_enable_reporting_flag OUT NOCOPY VARCHAR2,
85 x_contract_admin_id OUT NOCOPY NUMBER,
86 x_legal_contact_id OUT NOCOPY NUMBER,
87 x_locked_by_user_id OUT NOCOPY NUMBER
88
89 ) RETURN VARCHAR2 IS
90 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rec';
91 CURSOR OKC_TEMPLATE_USAGES_pk_csr (cp_document_type IN VARCHAR2,cp_document_id IN NUMBER) IS
92 SELECT
93 TEMPLATE_ID,
94 DOC_NUMBERING_SCHEME,
95 DOCUMENT_NUMBER,
96 ARTICLE_EFFECTIVE_DATE,
97 CONFIG_HEADER_ID,
98 CONFIG_REVISION_NUMBER,
99 VALID_CONFIG_YN,
100 ORIG_SYSTEM_REFERENCE_CODE,
101 ORIG_SYSTEM_REFERENCE_ID1,
102 ORIG_SYSTEM_REFERENCE_ID2,
103 OBJECT_VERSION_NUMBER,
104 CREATED_BY,
105 CREATION_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_LOGIN,
108 LAST_UPDATE_DATE,
109 AUTHORING_PARTY_CODE,
110 CONTRACT_SOURCE_CODE,
111 APPROVAL_ABSTRACT_TEXT ,
112 AUTOGEN_DEVIATIONS_FLAG,
113 -- Fix for bug# 3990983
114 SOURCE_CHANGE_ALLOWED_FLAG,
115 LOCK_TERMS_FLAG,
116 ENABLE_REPORTING_FLAG,
117 CONTRACT_ADMIN_ID,
118 LEGAL_CONTACT_ID,
119 LOCKED_BY_USER_ID
120 FROM OKC_TEMPLATE_USAGES t
121 WHERE t.DOCUMENT_TYPE = cp_document_type and
122 t.DOCUMENT_ID = cp_document_id;
123 BEGIN
124
125 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
126 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
127 '400: Entered get_rec');
128 END IF;
129
130
131 -- Get current database values
132 OPEN OKC_TEMPLATE_USAGES_pk_csr (p_document_type, p_document_id);
133 FETCH OKC_TEMPLATE_USAGES_pk_csr INTO
134 x_template_id,
135 x_doc_numbering_scheme,
136 x_document_number,
137 x_article_effective_date,
138 x_config_header_id,
139 x_config_revision_number,
140 x_valid_config_yn,
141 x_orig_system_reference_code,
142 x_orig_system_reference_id1,
143 x_orig_system_reference_id2,
144 x_object_version_number,
145 x_created_by,
146 x_creation_date,
147 x_last_updated_by,
148 x_last_update_login,
149 x_last_update_date,
150
151 x_authoring_party_code,
152 x_contract_source_code,
153 x_approval_abstract_text,
154 x_autogen_deviations_flag,
155 x_source_change_allowed_flag , -- Fix for bug# 3990983
156 x_lock_terms_flag,
157 x_enable_reporting_flag,
158 x_contract_admin_id,
159 x_legal_contact_id,
160 x_locked_by_user_id;
161
162 IF OKC_TEMPLATE_USAGES_pk_csr%NOTFOUND THEN
163 RAISE NO_DATA_FOUND;
164 END IF;
165 CLOSE OKC_TEMPLATE_USAGES_pk_csr;
166
167 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
168 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
169 '500: Leaving get_rec ');
170 END IF;
171
172 RETURN G_RET_STS_SUCCESS ;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176
177 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
178 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
179 '600: Leaving get_rec because of EXCEPTION: '||sqlerrm);
180 END IF;
181
182 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
183 p_msg_name => G_UNEXPECTED_ERROR,
184 p_token1 => G_SQLCODE_TOKEN,
185 p_token1_value => sqlcode,
186 p_token2 => G_SQLERRM_TOKEN,
187 p_token2_value => sqlerrm);
188
189 IF OKC_TEMPLATE_USAGES_pk_csr%ISOPEN THEN
190 CLOSE OKC_TEMPLATE_USAGES_pk_csr;
191 END IF;
192
193 RETURN G_RET_STS_UNEXP_ERROR ;
194
195 END Get_Rec;
196
197 -----------------------------------------
198 -- Set_Attributes for:OKC_TEMPLATE_USAGES --
199 -----------------------------------------
200 FUNCTION Set_Attributes(
201 p_document_type IN VARCHAR2,
202 p_document_id IN NUMBER,
203 p_template_id IN NUMBER,
204 p_doc_numbering_scheme IN NUMBER,
205 p_document_number IN VARCHAR2,
206 p_article_effective_date IN DATE,
207 p_config_header_id IN NUMBER,
208 p_config_revision_number IN NUMBER,
209 p_valid_config_yn IN VARCHAR2,
210 p_orig_system_reference_code IN VARCHAR2,
211 p_orig_system_reference_id1 IN NUMBER,
212 p_orig_system_reference_id2 IN NUMBER,
213 p_object_version_number IN NUMBER,
214 p_authoring_party_code IN VARCHAR2,
215 p_contract_source_code IN VARCHAR2,
216 p_approval_abstract_text IN CLOB,
217 p_autogen_deviations_flag IN VARCHAR2,
218 --added for bug# 3990983
219 p_source_change_allowed_flag IN VARCHAR2 ,
220
221 x_template_id OUT NOCOPY NUMBER,
222 x_doc_numbering_scheme OUT NOCOPY NUMBER,
223 x_document_number OUT NOCOPY VARCHAR2,
224 x_article_effective_date OUT NOCOPY DATE,
225 x_config_header_id OUT NOCOPY NUMBER,
226 x_config_revision_number OUT NOCOPY NUMBER,
227 x_valid_config_yn OUT NOCOPY VARCHAR2,
228 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
229 x_orig_system_reference_id1 OUT NOCOPY NUMBER,
230 x_orig_system_reference_id2 OUT NOCOPY NUMBER,
231
232 --added for 10+ word integration and deviations report
233 x_authoring_party_code OUT NOCOPY VARCHAR2,
234 x_contract_source_code OUT NOCOPY VARCHAR2,
235 x_approval_abstract_text OUT NOCOPY CLOB,
236 x_autogen_deviations_flag OUT NOCOPY VARCHAR2,
237 --added for bug# 3990983
238 x_source_change_allowed_flag OUT NOCOPY VARCHAR2,
239 p_lock_terms_flag IN VARCHAR2 ,
240 p_enable_reporting_flag IN VARCHAR2 ,
241 p_contract_admin_id IN NUMBER ,
242 p_legal_contact_id IN NUMBER ,
243 p_locked_by_user_id IN NUMBER,
244 x_lock_terms_flag OUT NOCOPY VARCHAR2,
245 x_enable_reporting_flag OUT NOCOPY VARCHAR2,
246 x_contract_admin_id OUT NOCOPY NUMBER,
247 x_legal_contact_id OUT NOCOPY NUMBER,
248 x_locked_by_user_id OUT NOCOPY NUMBER
249 ) RETURN VARCHAR2 IS
250 l_api_name CONSTANT VARCHAR2(30) := 'Set_Attributes';
251 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
252 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
253 l_created_by OKC_TEMPLATE_USAGES.CREATED_BY%TYPE;
254 l_creation_date OKC_TEMPLATE_USAGES.CREATION_DATE%TYPE;
255 l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
256 l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
257 l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
258 BEGIN
259 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
260 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
261 '700: Entered Set_Attributes ');
262 END IF;
263
264 IF( p_document_type IS NOT NULL AND p_document_id IS NOT NULL ) THEN
265 -- Get current database values
266 l_return_status := Get_Rec(
267 p_document_type => p_document_type,
268 p_document_id => p_document_id,
269 x_template_id => x_template_id,
270 x_doc_numbering_scheme => x_doc_numbering_scheme,
271 x_document_number => x_document_number,
272 x_article_effective_date => x_article_effective_date,
273 x_config_header_id => x_config_header_id,
274 x_config_revision_number => x_config_revision_number,
275 x_valid_config_yn => x_valid_config_yn,
276 x_orig_system_reference_code => x_orig_system_reference_code,
277 x_orig_system_reference_id1 => x_orig_system_reference_id1,
278 x_orig_system_reference_id2 => x_orig_system_reference_id2,
279 x_object_version_number => l_object_version_number,
280 x_created_by => l_created_by,
281 x_creation_date => l_creation_date,
282 x_last_updated_by => l_last_updated_by,
283 x_last_update_login => l_last_update_login,
284 x_last_update_date => l_last_update_date,
285 x_authoring_party_code => x_authoring_party_code ,
286 x_contract_source_code => x_contract_source_code ,
287 x_approval_abstract_text => x_approval_abstract_text,
288 x_autogen_deviations_flag => x_autogen_deviations_flag,
289 -- Fix for bug# 3990983
290 x_source_change_allowed_flag => x_source_change_allowed_flag,
291 x_lock_terms_flag => x_lock_terms_flag,
292 x_enable_reporting_flag => x_enable_reporting_flag,
293 x_contract_admin_id => x_contract_admin_id,
294 x_legal_contact_id => x_legal_contact_id,
295 x_locked_by_user_id => x_locked_by_user_id
296 );
297 --- If any errors happen abort API
298 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303
304 --- Reversing G_MISS/NULL values logic
305
306 IF (p_template_id = G_MISS_NUM) THEN
307 x_template_id := NULL;
308 ELSIF (p_TEMPLATE_ID IS NOT NULL) THEN
309 x_template_id := p_template_id;
310 END IF;
311
312 IF (p_doc_numbering_scheme = G_MISS_NUM) THEN
313 x_doc_numbering_scheme := NULL;
314 ELSIF (p_DOC_NUMBERING_SCHEME IS NOT NULL) THEN
315 x_doc_numbering_scheme := p_doc_numbering_scheme;
316 END IF;
317
318 IF (p_document_number = G_MISS_CHAR) THEN
319 x_document_number := NULL;
320 ELSIF (p_DOCUMENT_NUMBER IS NOT NULL) THEN
321 x_document_number := p_document_number;
322 END IF;
323
324 IF (p_article_effective_date = G_MISS_DATE) THEN
325 x_article_effective_date := NULL;
326 ELSIF (p_ARTICLE_EFFECTIVE_DATE IS NOT NULL) THEN
327 x_article_effective_date := p_article_effective_date;
328 END IF;
329
330 IF (p_config_header_id = G_MISS_NUM) THEN
331 x_config_header_id := NULL;
332 ELSIF (p_CONFIG_HEADER_ID IS NOT NULL) THEN
333 x_config_header_id := p_config_header_id;
334 END IF;
335
336 IF (p_config_revision_number = G_MISS_NUM) THEN
337 x_config_revision_number := NULL;
338 ELSIF (p_CONFIG_REVISION_NUMBER IS NOT NULL) THEN
339 x_config_revision_number := p_config_revision_number;
340 END IF;
341
342 IF (p_valid_config_yn = G_MISS_CHAR) THEN
343 x_valid_config_yn := NULL;
344 ELSIF (p_VALID_CONFIG_YN IS NOT NULL) THEN
345 x_valid_config_yn := p_valid_config_yn;
346 END IF;
347
348 IF (p_orig_system_reference_code = G_MISS_CHAR) THEN
349 x_orig_system_reference_code := NULL;
350 ELSIF (p_orig_system_reference_code IS NOT NULL) THEN
351 x_orig_system_reference_code := p_orig_system_reference_code;
352 END IF;
353
354 IF (p_orig_system_reference_id1 = G_MISS_NUM) THEN
355 x_orig_system_reference_id1 := NULL;
356 ELSIF (p_orig_system_reference_id1 IS NOT NULL) THEN
357 x_orig_system_reference_id1 := p_orig_system_reference_id1;
358 END IF;
359
360 IF (p_orig_system_reference_id2 = G_MISS_NUM) THEN
361 x_orig_system_reference_id2 := NULL;
362 ELSIF (p_orig_system_reference_id2 IS NOT NULL) THEN
363 x_orig_system_reference_id2 := p_orig_system_reference_id2;
364 END IF;
365
366
367 --added for 10+ word integration and deviations report
368 IF (p_authoring_party_code = G_MISS_CHAR) THEN
369 x_authoring_party_code := NULL;
370 ELSIF (p_authoring_party_code IS NOT NULL) THEN
371 x_authoring_party_code := p_authoring_party_code;
372 END IF;
373
374 IF (p_contract_source_code = G_MISS_CHAR) THEN
375 x_contract_source_code := NULL;
376 ELSIF (p_contract_source_code IS NOT NULL) THEN
377 x_contract_source_code := p_contract_source_code;
378 END IF;
379
380 IF dbms_lob.getlength(p_approval_abstract_text) = length(G_MISS_CHAR) THEN
381 IF (dbms_lob.substr(p_approval_abstract_text,dbms_lob.getlength(p_approval_abstract_text)) = G_MISS_CHAR) THEN
382 x_approval_abstract_text := NULL;
383 END IF;
384 ELSIF (p_approval_abstract_text IS NOT NULL) THEN
385 x_approval_abstract_text := p_approval_abstract_text;
386 END IF;
387
388 IF (p_autogen_deviations_flag = G_MISS_CHAR) THEN
389 x_autogen_deviations_flag := NULL;
390 ELSIF (p_autogen_deviations_flag IS NOT NULL) THEN
391 x_autogen_deviations_flag := p_autogen_deviations_flag;
392 x_autogen_deviations_flag := Upper( x_autogen_deviations_flag );
393 END IF;
394 IF (p_source_change_allowed_flag = G_MISS_CHAR) THEN
395 x_source_change_allowed_flag := NULL;
396 ELSIF (p_source_change_allowed_flag IS NOT NULL) THEN
397 x_source_change_allowed_flag := p_source_change_allowed_flag;
398 x_source_change_allowed_flag := Upper( x_source_change_allowed_flag );
399 END IF;
400
401 IF (p_lock_terms_flag = G_MISS_CHAR) THEN
402 x_lock_terms_flag := NULL;
403 ELSIF (p_lock_terms_flag IS NOT NULL) THEN
404 x_lock_terms_flag := p_lock_terms_flag;
405 x_lock_terms_flag := Upper( x_lock_terms_flag );
406 END IF;
407
408 IF (p_enable_reporting_flag = G_MISS_CHAR) THEN
409 x_enable_reporting_flag := NULL;
410 ELSIF (p_enable_reporting_flag IS NOT NULL) THEN
411 x_enable_reporting_flag := p_enable_reporting_flag;
412 x_enable_reporting_flag := Upper( x_enable_reporting_flag );
413 END IF;
414
415 IF (p_contract_admin_id = G_MISS_NUM) THEN
416 x_contract_admin_id := NULL;
417 ELSIF (p_contract_admin_id IS NOT NULL) THEN
418 x_contract_admin_id := p_contract_admin_id;
419 END IF;
420
421
422 IF (p_legal_contact_id = G_MISS_NUM) THEN
423 x_legal_contact_id := NULL;
424 ELSIF (p_legal_contact_id IS NOT NULL) THEN
425 x_legal_contact_id := p_legal_contact_id;
426 END IF;
427
428
429 IF (p_locked_by_user_id = G_MISS_NUM) THEN
430 x_locked_by_user_id := NULL;
431 ELSIF (p_locked_by_user_id IS NOT NULL) THEN
432 x_locked_by_user_id := p_locked_by_user_id;
433 END IF;
434
435
436 -- ?? converting to uppercase all _YN columns
437 -- ?? per performance reason it can be moved into corresponding
438 -- ?? ELSIF( column IS NOT NULL) section above
439 x_valid_config_yn := Upper( x_valid_config_yn );
440
441 END IF;
442
443 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
444 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
445 '800: Leaving Set_Attributes ');
446 END IF;
447
448 RETURN G_RET_STS_SUCCESS ;
449 EXCEPTION
450 WHEN FND_API.G_EXC_ERROR THEN
451 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
452 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
453 '900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception');
454 END IF;
455 RETURN G_RET_STS_ERROR;
456
457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
458 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
459 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
460 '1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
461 END IF;
462 RETURN G_RET_STS_UNEXP_ERROR;
463
464 WHEN OTHERS THEN
465 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
466 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
467 '1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm);
468 END IF;
469 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
470 p_msg_name => G_UNEXPECTED_ERROR,
471 p_token1 => G_SQLCODE_TOKEN,
472 p_token1_value => sqlcode,
473 p_token2 => G_SQLERRM_TOKEN,
474 p_token2_value => sqlerrm);
475 RETURN G_RET_STS_UNEXP_ERROR;
476
477 END Set_Attributes ;
478
479 ----------------------------------------------
480 -- Validate_Attributes for: OKC_TEMPLATE_USAGES --
481 ----------------------------------------------
482 FUNCTION Validate_Attributes (
483 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
484
485 p_document_type IN VARCHAR2,
486 p_document_id IN NUMBER,
487 p_template_id IN NUMBER,
488 p_doc_numbering_scheme IN NUMBER,
489 p_document_number IN VARCHAR2,
490 p_article_effective_date IN DATE,
491 p_config_header_id IN NUMBER,
492 p_config_revision_number IN NUMBER,
493 p_valid_config_yn IN VARCHAR2,
494 p_orig_system_reference_code IN VARCHAR2,
495 p_orig_system_reference_id1 IN NUMBER,
496 p_orig_system_reference_id2 IN NUMBER,
497
498 --added for 10+ word integration and deviations report
499 p_authoring_party_code IN VARCHAR2,
500 p_contract_source_code IN VARCHAR2,
501 p_approval_abstract_text IN CLOB,
502 p_autogen_deviations_flag IN VARCHAR2,
503 -- fix for bug# 3990983
504 p_source_change_allowed_flag IN VARCHAR2,
505 p_lock_terms_flag IN VARCHAR2 ,
506 p_enable_reporting_flag IN VARCHAR2 ,
507 p_contract_admin_id IN NUMBER ,
508 p_legal_contact_id IN NUMBER,
509 p_locked_by_user_id IN NUMBER
510 ) RETURN VARCHAR2 IS
511 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Attributes';
512 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
513 l_tmp_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
514 l_dummy_var VARCHAR2(1) := '?';
515
516 CURSOR l_template_id_csr is
517 SELECT '!'
518 FROM okc_terms_templates_all
519 WHERE TEMPLATE_ID = p_template_id;
520
521 CURSOR l_doc_number_scheme_csr is
522 SELECT '!'
523 FROM okc_number_schemes_b
524 WHERE num_scheme_id = p_doc_numbering_scheme;
525
526 /*
527 CURSOR l_config_header_id_csr is
528 SELECT '!'
529 FROM ??unknown_table??
530 WHERE ??CONFIG_HEADER_ID?? = p_config_header_id;
531
532 */
533
534 --added for 10+ word integration and deviations report
535 CURSOR l_authoring_party_csr(p_party_code IN VARCHAR2) is
536 SELECT 'Y'
537 FROM OKC_RESP_PARTIES_B del, OKC_BUS_DOC_TYPES_B types
538 WHERE del.document_type_class = types.document_type_class
539 AND types.document_type = p_document_type
540 AND del.resp_party_code = p_party_code;
541
542
543 BEGIN
544
545 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
546 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Entered Validate_Attributes');
547 END IF;
548
549
550 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
551 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
552 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1300: required values validation');
553 END IF;
554
555 END IF;
556
557 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
558 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
559 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: static values and range validation');
560 END IF;
561
562 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
563 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1700: - attribute VALID_CONFIG_YN ');
564 END IF;
565 IF ( p_valid_config_yn NOT IN ('Y','N') AND p_valid_config_yn IS NOT NULL) THEN
566 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
567 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: - attribute VALID_CONFIG_YN is invalid');
568 END IF;
569 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'VALID_CONFIG_YN');
570 l_return_status := G_RET_STS_ERROR;
571 END IF;
572
573
574 --added for 10+ word integration and deviations report
575 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
576 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: - attribute AUTOGEN_DEVIATIONS_FLAG ');
577 END IF;
578 IF (p_autogen_deviations_flag NOT IN ('Y','N') AND p_autogen_deviations_flag IS NOT NULL )THEN
579 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
580 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: - attribute AUTOGEN_DEVIATIONS_FLAG is invalid');
581 END IF;
582 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'AUTOGEN_DEVIATIONS_FLAG');
583 l_return_status := G_RET_STS_ERROR;
584 END IF;
585 END IF;
586
587
588 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
589 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
590 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1900: lookup codes validation');
591 END IF;
592
593 --added for 10+ word integration and deviations report
594 --Validate lookup for authoring_party_code
595 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
596 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute AUTHORING_PARTY_CODE ');
597 END IF;
598 IF p_authoring_party_code IS NOT NULL THEN
599 l_dummy_var := '?';
600 OPEN l_authoring_party_csr(p_authoring_party_code);
601 FETCH l_authoring_party_csr INTO l_dummy_var;
602 CLOSE l_authoring_party_csr;
603 IF (l_dummy_var = '?') THEN
604 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'AUTHORING_PARTY_CODE');
605 l_return_status := G_RET_STS_ERROR;
606 END IF;
607 END IF;
608
609 --added for 10+ word integration and deviations report
610 --Validate lookup for contract_source_code
611 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
612 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: - attribute CONTRACT_SOURCE_CODE ');
613 END IF;
614 l_tmp_return_status := Okc_Util.Check_Lookup_Code('OKC_CONTRACT_TERMS_SOURCES',p_contract_source_code);
615 IF (l_tmp_return_status <> G_RET_STS_SUCCESS) THEN
616 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'CONTRACT_SOURCE_CODE');
617 l_return_status := G_RET_STS_ERROR;
618 END IF;
619
620 END IF;
621
622 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
623 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
624 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: foreigh keys validation ');
625 END IF;
626
627 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
628 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - attribute DOC_NUMBERING_SCHEME ');
629 END IF;
630 IF p_doc_numbering_scheme IS NOT NULL THEN
631 l_dummy_var := '?';
632 OPEN l_doc_number_scheme_csr;
633 FETCH l_doc_number_scheme_csr INTO l_dummy_var;
634 CLOSE l_doc_number_scheme_csr;
635 IF (l_dummy_var = '?') THEN
636 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
637 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: - attribute DOC_NUMBERING_SCHEME is invalid');
638 END IF;
639 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DOC_NUMBERING_SCHEME');
640 l_return_status := G_RET_STS_ERROR;
641 END IF;
642 END IF;
643
644 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
645 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - attribute TEMPLATE_ID ');
646 END IF;
647 IF p_template_id IS NOT NULL THEN
648 l_dummy_var := '?';
649 OPEN l_template_id_csr;
650 FETCH l_template_id_csr INTO l_dummy_var;
651 CLOSE l_template_id_csr;
652 IF (l_dummy_var = '?') THEN
653 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
654 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: - attribute TEMPLATE_ID is invalid');
655 END IF;
656 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TEMPLATE_ID');
657 l_return_status := G_RET_STS_ERROR;
658 END IF;
659 END IF;
660
661 /*
662 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
663 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: - attribute CONFIG_HEADER_ID ');
664 END IF;
665 IF p_config_header_id IS NOT NULL THEN
666 l_dummy_var := '?';
667 OPEN l_config_header_id_csr;
668 FETCH l_config_header_id_csr INTO l_dummy_var;
669 CLOSE l_config_header_id_csr;
670 IF (l_dummy_var = '?') THEN
671 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
672 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: - attribute CONFIG_HEADER_ID is invalid');
673 END IF;
674 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CONFIG_HEADER_ID');
675 l_return_status := G_RET_STS_ERROR;
676 END IF;
677 END IF;
678
679 */
680 END IF;
681
682
683 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
684 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2400: Leaving Validate_Attributes ');
685 END IF;
686
687 RETURN l_return_status;
688
689 EXCEPTION
690 WHEN OTHERS THEN
691
692 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
693 p_msg_name => G_UNEXPECTED_ERROR,
694 p_token1 => G_SQLCODE_TOKEN,
695 p_token1_value => sqlcode,
696 p_token2 => G_SQLERRM_TOKEN,
697 p_token2_value => sqlerrm);
698
699 IF l_doc_number_scheme_csr%ISOPEN THEN
700 CLOSE l_doc_number_scheme_csr;
701 END IF;
702
703 IF l_template_id_csr%ISOPEN THEN
704 CLOSE l_template_id_csr;
705 END IF;
706
707 /*
708 IF l_config_header_id_csr%ISOPEN THEN
709 CLOSE l_config_header_id_csr;
710 END IF;
711 */
712
713 RETURN G_RET_STS_UNEXP_ERROR;
714
715 END Validate_Attributes;
716
717
718 ---------------------------------------------------------------------------
719 -- PROCEDURE Validate_Record
720 -- It calls Item Level Validations and then makes Record Level Validations
721 ---------------------------------------------------------------------------
722 ------------------------------------------
723 -- Validate_Record for:OKC_TEMPLATE_USAGES --
724 ------------------------------------------
725 FUNCTION Validate_Record (
726 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
727
728 p_document_type IN VARCHAR2,
729 p_document_id IN NUMBER,
730 p_template_id IN NUMBER,
731 p_doc_numbering_scheme IN NUMBER,
732 p_document_number IN VARCHAR2,
733 p_article_effective_date IN DATE,
734 p_config_header_id IN NUMBER,
735 p_config_revision_number IN NUMBER,
736 p_valid_config_yn IN VARCHAR2,
737 p_orig_system_reference_code IN VARCHAR2,
738 p_orig_system_reference_id1 IN NUMBER,
739 p_orig_system_reference_id2 IN NUMBER,
740
741 --added for 10+ word integration and deviations report
742 p_authoring_party_code IN VARCHAR2,
743 p_contract_source_code IN VARCHAR2,
744 p_approval_abstract_text IN CLOB,
745 p_autogen_deviations_flag IN VARCHAR2,
746 -- fix for bug# 3990983
747 p_source_change_allowed_flag IN VARCHAR2,
748 p_lock_terms_flag IN VARCHAR2 ,
749 p_enable_reporting_flag IN VARCHAR2 ,
750 p_contract_admin_id IN NUMBER ,
751 p_legal_contact_id IN NUMBER,
752 p_locked_by_user_id IN NUMBER
753 ) RETURN VARCHAR2 IS
754 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Record';
755 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
756 BEGIN
757
758 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
759 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2600: Entered Validate_Record');
760 END IF;
761
762 --- Validate all non-missing attributes (Item Level Validation)
763 l_return_status := Validate_Attributes(
764 p_validation_level => p_validation_level,
765
766 p_document_type => p_document_type,
767 p_document_id => p_document_id,
768 p_template_id => p_template_id,
769 p_doc_numbering_scheme => p_doc_numbering_scheme,
770 p_document_number => p_document_number,
771 p_article_effective_date => p_article_effective_date,
772 p_config_header_id => p_config_header_id,
773 p_config_revision_number => p_config_revision_number,
774 p_valid_config_yn => p_valid_config_yn,
775 p_orig_system_reference_code => p_orig_system_reference_code,
776 p_orig_system_reference_id1 => p_orig_system_reference_id1,
777 p_orig_system_reference_id2 => p_orig_system_reference_id2,
778
779 p_authoring_party_code => p_authoring_party_code,
780 p_contract_source_code => p_contract_source_code,
781 p_approval_abstract_text => p_approval_abstract_text,
782 p_autogen_deviations_flag => p_autogen_deviations_flag,
783 -- Fix for bug# 3990983
784 p_source_change_allowed_flag => p_source_change_allowed_flag,
785 p_lock_terms_flag => p_lock_terms_flag,
786 p_enable_reporting_flag => p_enable_reporting_flag,
787 p_contract_admin_id => p_contract_admin_id,
788 p_legal_contact_id => p_legal_contact_id,
789 p_locked_by_user_id => p_locked_by_user_id
790 );
791 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
792 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
793 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm);
794 END IF;
795 RETURN G_RET_STS_UNEXP_ERROR;
796 END IF;
797
798 --- Record Level Validation
799 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
800 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
801 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2800: Entered Record Level Validations');
802 END IF;
803 /*+++++++++++++start of hand code +++++++++++++++++++*/
804 -- ?? manual coding for Record Level Validations if required ??
805 /*+++++++++++++End of hand code +++++++++++++++++++*/
806 END IF;
807
808 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
809 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: Leaving Validate_Record : '||sqlerrm);
810 END IF;
811 RETURN l_return_status ;
812
813 EXCEPTION
814 WHEN OTHERS THEN
815
816 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm);
818 END IF;
819
820 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
821 p_msg_name => G_UNEXPECTED_ERROR,
822 p_token1 => G_SQLCODE_TOKEN,
823 p_token1_value => sqlcode,
824 p_token2 => G_SQLERRM_TOKEN,
825 p_token2_value => sqlerrm);
826 RETURN G_RET_STS_UNEXP_ERROR ;
827
828 END Validate_Record;
829
830 ---------------------------------------------------------------------------
831 -- PROCEDURE validate_row
832 ---------------------------------------------------------------------------
833 ---------------------------------------
834 -- validate_row for:OKC_TEMPLATE_USAGES --
835 ---------------------------------------
836 PROCEDURE validate_row(
837 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
838
839 x_return_status OUT NOCOPY VARCHAR2,
840
841 p_document_type IN VARCHAR2,
842 p_document_id IN NUMBER,
843 p_template_id IN NUMBER,
844 p_doc_numbering_scheme IN NUMBER,
845 p_document_number IN VARCHAR2,
846 p_article_effective_date IN DATE,
847 p_config_header_id IN NUMBER,
848 p_config_revision_number IN NUMBER,
849 p_valid_config_yn IN VARCHAR2,
850 p_orig_system_reference_code IN VARCHAR2,
851 p_orig_system_reference_id1 IN NUMBER,
852 p_orig_system_reference_id2 IN NUMBER,
853
854 p_object_version_number IN NUMBER,
855
856 --added for 10+ word integration and deviations report
857 p_authoring_party_code IN VARCHAR2,
858 p_contract_source_code IN VARCHAR2,
859 p_approval_abstract_text IN CLOB,
860 p_autogen_deviations_flag IN VARCHAR2,
861 --Fix for bug# 3990983
862 p_source_change_allowed_flag in VARCHAR2,
863 p_lock_terms_flag IN VARCHAR2 ,
864 p_enable_reporting_flag IN VARCHAR2 ,
865 p_contract_admin_id IN NUMBER ,
866 p_legal_contact_id IN NUMBER,
867 p_locked_by_user_id IN NUMBER
868 ) IS
869 l_api_name CONSTANT VARCHAR2(30) := 'validate_row';
870 l_template_id OKC_TEMPLATE_USAGES.TEMPLATE_ID%TYPE;
871 l_doc_numbering_scheme OKC_TEMPLATE_USAGES.DOC_NUMBERING_SCHEME%TYPE;
872 l_document_number OKC_TEMPLATE_USAGES.DOCUMENT_NUMBER%TYPE;
873 l_article_effective_date OKC_TEMPLATE_USAGES.ARTICLE_EFFECTIVE_DATE%TYPE;
874 l_config_header_id OKC_TEMPLATE_USAGES.CONFIG_HEADER_ID%TYPE;
875 l_config_revision_number OKC_TEMPLATE_USAGES.CONFIG_REVISION_NUMBER%TYPE;
876 l_valid_config_yn OKC_TEMPLATE_USAGES.VALID_CONFIG_YN%TYPE;
877 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
878 l_orig_system_reference_code OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
879 l_orig_system_reference_id1 OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
880 l_orig_system_reference_id2 OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
881
882 l_authoring_party_code OKC_TEMPLATE_USAGES.AUTHORING_PARTY_CODE%TYPE;
883 l_contract_source_code OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
884 l_approval_abstract_text OKC_TEMPLATE_USAGES.APPROVAL_ABSTRACT_TEXT%TYPE;
885 l_autogen_deviations_flag OKC_TEMPLATE_USAGES.AUTOGEN_DEVIATIONS_FLAG%TYPE;
886 -- Fix for bug# 3990983
887 l_source_change_allowed_flag OKC_TEMPLATE_USAGES.SOURCE_CHANGE_ALLOWED_FLAG%TYPE;
888 l_lock_terms_flag OKC_TEMPLATE_USAGES.LOCK_TERMS_FLAG%TYPE;
889 l_enable_reporting_flag OKC_TEMPLATE_USAGES.ENABLE_REPORTING_FLAG%TYPE;
890 l_contract_admin_id OKC_TEMPLATE_USAGES.CONTRACT_ADMIN_ID%TYPE;
891 l_legal_contact_id OKC_TEMPLATE_USAGES.LEGAL_CONTACT_ID%TYPE;
892 l_locked_by_user_id OKC_TEMPLATE_USAGES.LOCKED_BY_USER_ID%TYPE;
893
894 l_created_by OKC_TEMPLATE_USAGES.CREATED_BY%TYPE;
895 l_creation_date OKC_TEMPLATE_USAGES.CREATION_DATE%TYPE;
896 l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
897 l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
898 l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
899 BEGIN
900
901 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
902 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3100: Entered validate_row');
903 END IF;
904
905 -- Setting attributes
906 x_return_status := Set_Attributes(
907 p_document_type => p_document_type,
908 p_document_id => p_document_id,
909 p_template_id => p_template_id,
910 p_doc_numbering_scheme => p_doc_numbering_scheme,
911 p_document_number => p_document_number,
912 p_article_effective_date => p_article_effective_date,
913 p_config_header_id => p_config_header_id,
914 p_config_revision_number => p_config_revision_number,
915 p_valid_config_yn => p_valid_config_yn,
916 p_orig_system_reference_code => p_orig_system_reference_code,
917 p_orig_system_reference_id1 => p_orig_system_reference_id1,
918 p_orig_system_reference_id2 => p_orig_system_reference_id2,
919 p_object_version_number => p_object_version_number,
920 p_authoring_party_code => p_authoring_party_code,
921 p_contract_source_code => p_contract_source_code,
922 p_approval_abstract_text => p_approval_abstract_text,
923 p_autogen_deviations_flag => p_autogen_deviations_flag,
924 --Fix for bug# 3990983
925 p_source_change_allowed_flag => p_source_change_allowed_flag,
926
927 x_template_id => l_template_id,
928 x_doc_numbering_scheme => l_doc_numbering_scheme,
929 x_document_number => l_document_number,
930 x_article_effective_date => l_article_effective_date,
931 x_config_header_id => l_config_header_id,
932 x_config_revision_number => l_config_revision_number,
933 x_valid_config_yn => l_valid_config_yn,
934 x_orig_system_reference_code => l_orig_system_reference_code,
935 x_orig_system_reference_id1 => l_orig_system_reference_id1,
936 x_orig_system_reference_id2 => l_orig_system_reference_id2,
937 x_authoring_party_code => l_authoring_party_code,
938 x_contract_source_code => l_contract_source_code,
939 x_approval_abstract_text => l_approval_abstract_text,
940 x_autogen_deviations_flag => l_autogen_deviations_flag,
941 -- Fix for bug# 3990983
942 x_source_change_allowed_flag => l_source_change_allowed_flag,
943
944 x_lock_terms_flag => l_lock_terms_flag,
945 x_enable_reporting_flag => l_enable_reporting_flag,
946 x_contract_admin_id => l_contract_admin_id,
947 x_legal_contact_id => l_legal_contact_id,
948 x_locked_by_user_id => l_locked_by_user_id,
949 p_lock_terms_flag => p_lock_terms_flag,
950 p_enable_reporting_flag => p_enable_reporting_flag,
951 p_contract_admin_id => p_contract_admin_id,
952 p_legal_contact_id => p_legal_contact_id,
953 p_locked_by_user_id => p_locked_by_user_id
954 );
955 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
957 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
958 RAISE FND_API.G_EXC_ERROR;
959 END IF;
960
961 -- Validate all non-missing attributes (Item Level Validation)
962 x_return_status := Validate_Record(
963 p_validation_level => p_validation_level,
964 p_document_type => p_document_type,
965 p_document_id => p_document_id,
966 p_template_id => l_template_id,
967 p_doc_numbering_scheme => l_doc_numbering_scheme,
968 p_document_number => l_document_number,
969 p_article_effective_date => l_article_effective_date,
970 p_config_header_id => l_config_header_id,
971 p_config_revision_number => l_config_revision_number,
972 p_valid_config_yn => l_valid_config_yn,
973 p_orig_system_reference_code => l_orig_system_reference_code,
974 p_orig_system_reference_id1 => l_orig_system_reference_id1,
975 p_orig_system_reference_id2 => l_orig_system_reference_id2,
976
977 p_authoring_party_code => l_authoring_party_code,
978 p_contract_source_code => l_contract_source_code,
979 p_approval_abstract_text => l_approval_abstract_text,
980 p_autogen_deviations_flag => l_autogen_deviations_flag,
981 -- Fix for bug# 3990983
982 p_source_change_allowed_flag => l_source_change_allowed_flag,
983 p_lock_terms_flag => p_lock_terms_flag,
984 p_enable_reporting_flag => p_enable_reporting_flag,
985 p_contract_admin_id => p_contract_admin_id,
986 p_legal_contact_id => p_legal_contact_id,
987 p_locked_by_user_id => p_locked_by_user_id
988 );
989
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,'3200: Leaving validate_row');
992 END IF;
993
994 EXCEPTION
995 WHEN FND_API.G_EXC_ERROR THEN
996 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
997 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception');
998 END IF;
999 x_return_status := G_RET_STS_ERROR;
1000
1001 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1002 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1003 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1004 END IF;
1005 x_return_status := G_RET_STS_UNEXP_ERROR;
1006
1007 WHEN OTHERS THEN
1008 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1009 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm);
1010 END IF;
1011 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1012 p_msg_name => G_UNEXPECTED_ERROR,
1013 p_token1 => G_SQLCODE_TOKEN,
1014 p_token1_value => sqlcode,
1015 p_token2 => G_SQLERRM_TOKEN,
1016 p_token2_value => sqlerrm);
1017 x_return_status := G_RET_STS_UNEXP_ERROR;
1018
1019 END Validate_Row;
1020
1021 ---------------------------------------------------------------------------
1022 -- PROCEDURE Insert_Row
1023 ---------------------------------------------------------------------------
1024 -------------------------------------
1025 -- Insert_Row for:OKC_TEMPLATE_USAGES --
1026 -------------------------------------
1027 FUNCTION Insert_Row(
1028 p_document_type IN VARCHAR2,
1029 p_document_id IN NUMBER,
1030 p_template_id IN NUMBER,
1031 p_doc_numbering_scheme IN NUMBER,
1032 p_document_number IN VARCHAR2,
1033 p_article_effective_date IN DATE,
1034 p_config_header_id IN NUMBER,
1035 p_config_revision_number IN NUMBER,
1036 p_valid_config_yn IN VARCHAR2,
1037 p_orig_system_reference_code IN VARCHAR2,
1038 p_orig_system_reference_id1 IN NUMBER,
1039 p_orig_system_reference_id2 IN NUMBER,
1040 p_object_version_number IN NUMBER,
1041 p_created_by IN NUMBER,
1042 p_creation_date IN DATE,
1043 p_last_updated_by IN NUMBER,
1044 p_last_update_login IN NUMBER,
1045 p_last_update_date IN DATE,
1046
1047 --added for 10+ word integration and deviations report
1048 p_authoring_party_code IN VARCHAR2,
1049 p_contract_source_code IN VARCHAR2,
1050 p_approval_abstract_text IN CLOB,
1051 p_autogen_deviations_flag IN VARCHAR2,
1052 --Fix for bug# 3990983
1053 p_source_change_allowed_flag IN VARCHAR2,
1054 p_lock_terms_flag IN VARCHAR2 ,
1055 p_enable_reporting_flag IN VARCHAR2 ,
1056 p_contract_admin_id IN NUMBER ,
1057 p_legal_contact_id IN NUMBER,
1058 p_locked_by_user_id IN NUMBER
1059 ) RETURN VARCHAR2 IS
1060 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1061
1062 BEGIN
1063
1064 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1065 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Entered Insert_Row function');
1066 END IF;
1067
1068 INSERT INTO OKC_TEMPLATE_USAGES(
1069 DOCUMENT_TYPE,
1070 DOCUMENT_ID,
1071 TEMPLATE_ID,
1072 DOC_NUMBERING_SCHEME,
1073 DOCUMENT_NUMBER,
1074 ARTICLE_EFFECTIVE_DATE,
1075 CONFIG_HEADER_ID,
1076 CONFIG_REVISION_NUMBER,
1077 VALID_CONFIG_YN,
1078 ORIG_SYSTEM_REFERENCE_CODE,
1079 ORIG_SYSTEM_REFERENCE_ID1,
1080 ORIG_SYSTEM_REFERENCE_ID2,
1081 OBJECT_VERSION_NUMBER,
1082 CREATED_BY,
1083 CREATION_DATE,
1084 LAST_UPDATED_BY,
1085 LAST_UPDATE_LOGIN,
1086 LAST_UPDATE_DATE,
1087
1088 AUTHORING_PARTY_CODE,
1089 CONTRACT_SOURCE_CODE ,
1090 APPROVAL_ABSTRACT_TEXT ,
1091 AUTOGEN_DEVIATIONS_FLAG,
1092 --Fix for bug# 3990983
1093 SOURCE_CHANGE_ALLOWED_FLAG,
1094 lock_terms_flag,
1095 enable_reporting_flag,
1096 contract_admin_id,
1097 legal_contact_id,
1098 locked_by_user_id )
1099 VALUES (
1100 p_document_type,
1101 p_document_id,
1102 p_template_id,
1103 p_doc_numbering_scheme,
1104 p_document_number,
1105 p_article_effective_date,
1106 p_config_header_id,
1107 p_config_revision_number,
1108 p_valid_config_yn,
1109 p_orig_system_reference_code,
1110 p_orig_system_reference_id1,
1111 p_orig_system_reference_id2,
1112 p_object_version_number,
1113 p_created_by,
1114 p_creation_date,
1115 p_last_updated_by,
1116 p_last_update_login,
1117 p_last_update_date,
1118
1119 p_authoring_party_code,
1120 p_contract_source_code,
1121 p_approval_abstract_text,
1122 p_autogen_deviations_flag,
1123 -- Fix for bug# 3990983
1124 p_source_change_allowed_flag,
1125 p_lock_terms_flag,
1126 p_enable_reporting_flag,
1127 p_contract_admin_id,
1128 p_legal_contact_id,
1129 p_locked_by_user_id
1130 );
1131
1132 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1133 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Leaving Insert_Row');
1134 END IF;
1135
1136 RETURN( G_RET_STS_SUCCESS );
1137
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140
1141 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1142 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3800: Leaving Insert_Row:OTHERS Exception');
1143 END IF;
1144
1145 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1146 p_msg_name => G_UNEXPECTED_ERROR,
1147 p_token1 => G_SQLCODE_TOKEN,
1148 p_token1_value => sqlcode,
1149 p_token2 => G_SQLERRM_TOKEN,
1150 p_token2_value => sqlerrm);
1151
1152 RETURN( G_RET_STS_UNEXP_ERROR );
1153
1154 END Insert_Row;
1155
1156
1157 -------------------------------------
1158 -- Insert_Row for:OKC_TEMPLATE_USAGES --
1159 -------------------------------------
1160 PROCEDURE Insert_Row(
1161 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1162 x_return_status OUT NOCOPY VARCHAR2,
1163
1164 p_document_type IN VARCHAR2,
1165 p_document_id IN NUMBER,
1166 p_template_id IN NUMBER,
1167 p_doc_numbering_scheme IN NUMBER,
1168 p_document_number IN VARCHAR2,
1169 p_article_effective_date IN DATE,
1170 p_config_header_id IN NUMBER,
1171 p_config_revision_number IN NUMBER,
1172 p_valid_config_yn IN VARCHAR2,
1173 p_orig_system_reference_code IN VARCHAR2,
1174 p_orig_system_reference_id1 IN NUMBER,
1175 p_orig_system_reference_id2 IN NUMBER,
1176
1177 --added for 10+ word integration and deviations report
1178 p_authoring_party_code IN VARCHAR2,
1179 p_contract_source_code IN VARCHAR2,
1180 p_approval_abstract_text IN CLOB,
1181 p_autogen_deviations_flag IN VARCHAR2,
1182 --Fix for bug# 3990983
1183 p_source_change_allowed_flag IN VARCHAR2,
1184
1185 x_document_type OUT NOCOPY VARCHAR2,
1186 x_document_id OUT NOCOPY NUMBER,
1187 p_lock_terms_flag IN VARCHAR2 := NULL,
1188 p_enable_reporting_flag IN VARCHAR2 := NULL,
1189 p_contract_admin_id IN NUMBER := NULL,
1190 p_legal_contact_id IN NUMBER := NULL,
1191 p_locked_by_user_id IN NUMBER := NULL
1192
1193 ) IS
1194 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1195 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1196 l_created_by OKC_TEMPLATE_USAGES.CREATED_BY%TYPE;
1197 l_creation_date OKC_TEMPLATE_USAGES.CREATION_DATE%TYPE;
1198 l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
1199 l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
1200 l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
1201 l_authoring_party_code OKC_TEMPLATE_USAGES.authoring_party_code%type;
1202
1203
1204 BEGIN
1205
1206 x_return_status := G_RET_STS_SUCCESS;
1207
1208 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1209 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4200: Entered Insert_Row');
1210 END IF;
1211
1212 -- Set Internal columns
1213 l_object_version_number := 1;
1214 l_creation_date := Sysdate;
1215 l_created_by := Fnd_Global.User_Id;
1216 l_last_update_date := l_creation_date;
1217 l_last_updated_by := l_created_by;
1218 l_last_update_login := Fnd_Global.Login_Id;
1219
1220 --added for 10+ word integration and deviations report
1221 --Set default value for p_authoring_party_code
1222 l_authoring_party_code := p_authoring_party_code;
1223 IF p_authoring_party_code is NULL THEN
1224
1225 l_authoring_party_code := G_INTERNAL_PARTY_CODE;
1226
1227 END IF;
1228
1229 --- Validate all non-missing attributes
1230 x_return_status := Validate_Record(
1231 p_validation_level => p_validation_level,
1232 p_document_type => p_document_type,
1233 p_document_id => p_document_id,
1234 p_template_id => p_template_id,
1235 p_doc_numbering_scheme => p_doc_numbering_scheme,
1236 p_document_number => p_document_number,
1237 p_article_effective_date => p_article_effective_date,
1238 p_config_header_id => p_config_header_id,
1239 p_config_revision_number => p_config_revision_number,
1240 p_valid_config_yn => p_valid_config_yn,
1241 p_orig_system_reference_code => p_orig_system_reference_code,
1242 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1243 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1244
1245 p_authoring_party_code => l_authoring_party_code,
1246 p_contract_source_code => p_contract_source_code,
1247 p_approval_abstract_text => p_approval_abstract_text,
1248 p_autogen_deviations_flag => p_autogen_deviations_flag,
1249 -- Fix for bug# 3990983
1250 p_source_change_allowed_flag => p_source_change_allowed_flag,
1251 p_lock_terms_flag => p_lock_terms_flag ,
1252 p_enable_reporting_flag => p_enable_reporting_flag,
1253 p_contract_admin_id => p_contract_admin_id ,
1254 p_legal_contact_id => p_legal_contact_id,
1255 p_locked_by_user_id => p_locked_by_user_id
1256 );
1257 --- If any errors happen abort API
1258 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1260 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1261 RAISE FND_API.G_EXC_ERROR;
1262 END IF;
1263
1264 --------------------------------------------
1265 -- Call the internal Insert_Row for each child record
1266 --------------------------------------------
1267 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1268 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4300: Call the internal Insert_Row for Base Table');
1269 END IF;
1270
1271 x_return_status := Insert_Row(
1272 p_document_type => p_document_type,
1273 p_document_id => p_document_id,
1274 p_template_id => p_template_id,
1275 p_doc_numbering_scheme => p_doc_numbering_scheme,
1276 p_document_number => p_document_number,
1277 p_article_effective_date => p_article_effective_date,
1278 p_config_header_id => p_config_header_id,
1279 p_config_revision_number => p_config_revision_number,
1280 p_valid_config_yn => p_valid_config_yn,
1281 p_orig_system_reference_code => p_orig_system_reference_code,
1282 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1283 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1284 p_object_version_number => l_object_version_number,
1285 p_created_by => l_created_by,
1286 p_creation_date => l_creation_date,
1287 p_last_updated_by => l_last_updated_by,
1288 p_last_update_login => l_last_update_login,
1289 p_last_update_date => l_last_update_date,
1290
1291 p_authoring_party_code => l_authoring_party_code,
1292 p_contract_source_code => p_contract_source_code,
1293 p_approval_abstract_text => p_approval_abstract_text,
1294 p_autogen_deviations_flag => p_autogen_deviations_flag,
1295 -- Fix for bug# 3990983
1296 p_source_change_allowed_flag => p_source_change_allowed_flag,
1297 p_lock_terms_flag => p_lock_terms_flag,
1298 p_enable_reporting_flag => p_enable_reporting_flag,
1299 p_contract_admin_id => p_contract_admin_id,
1300 p_legal_contact_id => p_legal_contact_id,
1301 p_locked_by_user_id => p_locked_by_user_id
1302 );
1303 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1305 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1306 RAISE FND_API.G_EXC_ERROR;
1307 END IF;
1308
1309
1310
1311 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1312 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Leaving Insert_Row');
1313 END IF;
1314
1315 EXCEPTION
1316 WHEN FND_API.G_EXC_ERROR THEN
1317 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1318 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception');
1319 END IF;
1320 x_return_status := G_RET_STS_ERROR;
1321
1322 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1323 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1324 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1325 END IF;
1326 x_return_status := G_RET_STS_UNEXP_ERROR;
1327
1328 WHEN OTHERS THEN
1329 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1330 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm);
1331 END IF;
1332 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1333 p_msg_name => G_UNEXPECTED_ERROR,
1334 p_token1 => G_SQLCODE_TOKEN,
1335 p_token1_value => sqlcode,
1336 p_token2 => G_SQLERRM_TOKEN,
1337 p_token2_value => sqlerrm);
1338 x_return_status := G_RET_STS_UNEXP_ERROR;
1339
1340 END Insert_Row;
1341 ---------------------------------------------------------------------------
1342 -- PROCEDURE Lock_Row
1343 ---------------------------------------------------------------------------
1344 -----------------------------------
1345 -- Lock_Row for:OKC_TEMPLATE_USAGES --
1346 -----------------------------------
1347 FUNCTION Lock_Row(
1348 p_document_type IN VARCHAR2,
1349 p_document_id IN NUMBER,
1350 p_object_version_number IN NUMBER
1351 ) RETURN VARCHAR2 IS
1352
1353 E_Resource_Busy EXCEPTION;
1354 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1355
1356 CURSOR lock_csr (cp_document_type VARCHAR2, cp_document_id NUMBER, cp_object_version_number NUMBER) IS
1357 SELECT object_version_number
1358 FROM OKC_TEMPLATE_USAGES
1359 WHERE DOCUMENT_TYPE = cp_document_type AND DOCUMENT_ID = cp_document_id
1360 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1361 FOR UPDATE OF object_version_number NOWAIT;
1362
1363 CURSOR lchk_csr (cp_document_type VARCHAR2, cp_document_id NUMBER) IS
1364 SELECT object_version_number
1365 FROM OKC_TEMPLATE_USAGES
1366 WHERE DOCUMENT_TYPE = cp_document_type AND DOCUMENT_ID = cp_document_id;
1367
1368 l_return_status VARCHAR2(1);
1369 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1370 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1371
1372 l_row_notfound BOOLEAN := FALSE;
1373 BEGIN
1374
1375 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1376 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4900: Entered Lock_Row');
1377 END IF;
1378
1379
1380 BEGIN
1381
1382 OPEN lock_csr( p_document_type, p_document_id, p_object_version_number );
1383 FETCH lock_csr INTO l_object_version_number;
1384 l_row_notfound := lock_csr%NOTFOUND;
1385 CLOSE lock_csr;
1386
1387 EXCEPTION
1388 WHEN E_Resource_Busy THEN
1389
1390 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1391 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Leaving Lock_Row:E_Resource_Busy Exception');
1392 END IF;
1393
1394 IF (lock_csr%ISOPEN) THEN
1395 CLOSE lock_csr;
1396 END IF;
1397 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1398 RETURN( G_RET_STS_ERROR );
1399 END;
1400
1401 IF ( l_row_notfound ) THEN
1402 l_return_status := G_RET_STS_ERROR;
1403
1404 OPEN lchk_csr(p_document_type, p_document_id);
1405 FETCH lchk_csr INTO l_object_version_number;
1406 l_row_notfound := lchk_csr%NOTFOUND;
1407 CLOSE lchk_csr;
1408
1409 IF (l_row_notfound) THEN
1410 Okc_Api.Set_Message(G_FND_APP,G_LOCK_RECORD_DELETED,
1411 'ENTITYNAME','OKC_TEMPLATE_USAGES',
1412 'PKEY',p_document_type||':'||p_document_id,
1413 'OVN',p_object_version_number
1414 );
1415 ELSIF l_object_version_number > p_object_version_number THEN
1416 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1417 ELSIF l_object_version_number = -1 THEN
1418 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1419 ELSE -- it can be the only above condition. It can happen after restore version
1420 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1421 END IF;
1422 ELSE
1423 l_return_status := G_RET_STS_SUCCESS;
1424 END IF;
1425
1426 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5100: Leaving Lock_Row');
1428 END IF;
1429
1430 RETURN( l_return_status );
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434
1435 IF (lock_csr%ISOPEN) THEN
1436 CLOSE lock_csr;
1437 END IF;
1438 IF (lchk_csr%ISOPEN) THEN
1439 CLOSE lchk_csr;
1440 END IF;
1441
1442 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1443 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
1444 END IF;
1445
1446 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1447 p_msg_name => G_UNEXPECTED_ERROR,
1448 p_token1 => G_SQLCODE_TOKEN,
1449 p_token1_value => sqlcode,
1450 p_token2 => G_SQLERRM_TOKEN,
1451 p_token2_value => sqlerrm);
1452
1453 RETURN( G_RET_STS_UNEXP_ERROR );
1454 END Lock_Row;
1455
1456 -----------------------------------
1457 -- Lock_Row for:OKC_TEMPLATE_USAGES --
1458 -----------------------------------
1459 PROCEDURE Lock_Row(
1460 x_return_status OUT NOCOPY VARCHAR2,
1461
1462 p_document_type IN VARCHAR2,
1463 p_document_id IN NUMBER,
1464 p_object_version_number IN NUMBER
1465 ) IS
1466 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1467 BEGIN
1468
1469 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5700: Entered Lock_Row');
1471 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5800: Locking Row for Base Table');
1472 END IF;
1473
1474 --------------------------------------------
1475 -- Call the LOCK_ROW for each _B child record
1476 --------------------------------------------
1477 x_return_status := Lock_Row(
1478 p_document_type => p_document_type,
1479 p_document_id => p_document_id,
1480 p_object_version_number => p_object_version_number
1481 );
1482 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1484 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1485 RAISE FND_API.G_EXC_ERROR;
1486 END IF;
1487
1488
1489
1490 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1491 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: Leaving Lock_Row');
1492 END IF;
1493
1494 EXCEPTION
1495 WHEN FND_API.G_EXC_ERROR THEN
1496 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1497 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception');
1498 END IF;
1499 x_return_status := G_RET_STS_ERROR;
1500
1501 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1502 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1503 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1504 END IF;
1505 x_return_status := G_RET_STS_UNEXP_ERROR;
1506
1507 WHEN OTHERS THEN
1508 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1509 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
1510 END IF;
1511 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1512 p_msg_name => G_UNEXPECTED_ERROR,
1513 p_token1 => G_SQLCODE_TOKEN,
1514 p_token1_value => sqlcode,
1515 p_token2 => G_SQLERRM_TOKEN,
1516 p_token2_value => sqlerrm);
1517 x_return_status := G_RET_STS_UNEXP_ERROR;
1518
1519 END Lock_Row;
1520 ---------------------------------------------------------------------------
1521 -- PROCEDURE Update_Row
1522 ---------------------------------------------------------------------------
1523 -------------------------------------
1524 -- Update_Row for:OKC_TEMPLATE_USAGES --
1525 -------------------------------------
1526 FUNCTION Update_Row(
1527 p_document_type IN VARCHAR2,
1528 p_document_id IN NUMBER,
1529 p_template_id IN NUMBER,
1530 p_doc_numbering_scheme IN NUMBER,
1531 p_document_number IN VARCHAR2,
1532 p_article_effective_date IN DATE,
1533 p_config_header_id IN NUMBER,
1534 p_config_revision_number IN NUMBER,
1535 p_valid_config_yn IN VARCHAR2,
1536 p_orig_system_reference_code IN VARCHAR2,
1537 p_orig_system_reference_id1 IN NUMBER,
1538 p_orig_system_reference_id2 IN NUMBER,
1539 p_object_version_number IN NUMBER,
1540 --p_created_by IN NUMBER,
1541 --p_creation_date IN DATE,
1542 p_last_updated_by IN NUMBER,
1543 p_last_update_login IN NUMBER,
1544 p_last_update_date IN DATE,
1545
1546 --added for 10+ word integration and deviations report
1547 p_authoring_party_code IN VARCHAR2,
1548 p_contract_source_code IN VARCHAR2,
1549 p_approval_abstract_text IN CLOB,
1550 p_autogen_deviations_flag IN VARCHAR2,
1551 -- Fix for bug# 3990983
1552 p_source_change_allowed_flag IN VARCHAR2,
1553 p_lock_terms_flag IN VARCHAR2 ,
1554 p_enable_reporting_flag IN VARCHAR2 ,
1555 p_contract_admin_id IN NUMBER ,
1556 p_legal_contact_id IN NUMBER,
1557 p_locked_by_user_id IN NUMBER
1558 ) RETURN VARCHAR2 IS
1559 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1560 BEGIN
1561
1562 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1563 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6400: Entered Update_Row');
1564 END IF;
1565
1566 UPDATE OKC_TEMPLATE_USAGES
1567 SET TEMPLATE_ID = p_template_id,
1568 DOC_NUMBERING_SCHEME = p_doc_numbering_scheme,
1569 DOCUMENT_NUMBER = p_document_number,
1570 ARTICLE_EFFECTIVE_DATE = p_article_effective_date,
1571 CONFIG_HEADER_ID = p_config_header_id,
1572 CONFIG_REVISION_NUMBER = p_config_revision_number,
1573 VALID_CONFIG_YN = p_valid_config_yn,
1574 ORIG_SYSTEM_REFERENCE_CODE = p_orig_system_reference_code,
1575 ORIG_SYSTEM_REFERENCE_ID1 = p_orig_system_reference_id1,
1576 ORIG_SYSTEM_REFERENCE_ID2 = p_orig_system_reference_id2,
1577 OBJECT_VERSION_NUMBER = p_object_version_number,
1578 --CREATED_BY = p_created_by,
1579 --CREATION_DATE = p_creation_date,
1580 LAST_UPDATED_BY = p_last_updated_by,
1581 LAST_UPDATE_LOGIN = p_last_update_login,
1582 LAST_UPDATE_DATE = p_last_update_date,
1583
1584 AUTHORING_PARTY_CODE = p_authoring_party_code,
1585 CONTRACT_SOURCE_CODE = p_contract_source_code,
1586 APPROVAL_ABSTRACT_TEXT = p_approval_abstract_text,
1587 AUTOGEN_DEVIATIONS_FLAG = p_autogen_deviations_flag,
1588 -- Fix for bug# 3990983
1589 SOURCE_CHANGE_ALLOWED_FLAG = p_source_change_allowed_flag,
1590 lock_terms_flag = p_lock_terms_flag,
1591 enable_reporting_flag = p_enable_reporting_flag,
1592 contract_admin_id = p_contract_admin_id,
1593 legal_contact_id = p_legal_contact_id,
1594 locked_by_user_id = p_locked_by_user_id
1595 WHERE DOCUMENT_TYPE = p_document_type AND DOCUMENT_ID = p_document_id;
1596
1597 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1598 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6500: Leaving Update_Row');
1599 END IF;
1600
1601 RETURN G_RET_STS_SUCCESS ;
1602
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605
1606 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1607 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
1608 END IF;
1609
1610 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1611 p_msg_name => G_UNEXPECTED_ERROR,
1612 p_token1 => G_SQLCODE_TOKEN,
1613 p_token1_value => sqlcode,
1614 p_token2 => G_SQLERRM_TOKEN,
1615 p_token2_value => sqlerrm);
1616
1617 RETURN G_RET_STS_UNEXP_ERROR ;
1618
1619 END Update_Row;
1620
1621 -------------------------------------
1622 -- Update_Row for:OKC_TEMPLATE_USAGES --
1623 -------------------------------------
1624 PROCEDURE Update_Row(
1625 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1626
1627 x_return_status OUT NOCOPY VARCHAR2,
1628
1629 p_document_type IN VARCHAR2,
1630 p_document_id IN NUMBER,
1631 p_template_id IN NUMBER := NULL,
1632 p_doc_numbering_scheme IN NUMBER := NULL,
1633 p_document_number IN VARCHAR2 := NULL,
1634 p_article_effective_date IN DATE := NULL,
1635 p_config_header_id IN NUMBER := NULL,
1636 p_config_revision_number IN NUMBER := NULL,
1637 p_valid_config_yn IN VARCHAR2 := NULL,
1638 p_orig_system_reference_code IN VARCHAR2 := NULL,
1639 p_orig_system_reference_id1 IN NUMBER := NULL,
1640 p_orig_system_reference_id2 IN NUMBER := NULL,
1641
1642 --added for 10+ word integration and deviations report
1643 p_object_version_number IN NUMBER := NULL,
1644 p_authoring_party_code IN VARCHAR2 := NULL,
1645 p_contract_source_code IN VARCHAR2 := NULL,
1646 p_approval_abstract_text IN CLOB := NULL,
1647 p_autogen_deviations_flag IN VARCHAR2 := NULL,
1648 -- Fix for bug# 3990983
1649 p_source_change_allowed_flag IN VARCHAR2:= NULL ,
1650 p_lock_terms_flag IN VARCHAR2 := NULL,
1651 p_enable_reporting_flag IN VARCHAR2 := NULL,
1652 p_contract_admin_id IN NUMBER := NULL,
1653 p_legal_contact_id IN NUMBER := NULL,
1654 p_locked_by_user_id IN NUMBER := NULL
1655 ) IS
1656 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1657 l_template_id OKC_TEMPLATE_USAGES.TEMPLATE_ID%TYPE;
1658 l_doc_numbering_scheme OKC_TEMPLATE_USAGES.DOC_NUMBERING_SCHEME%TYPE;
1659 l_document_number OKC_TEMPLATE_USAGES.DOCUMENT_NUMBER%TYPE;
1660 l_article_effective_date OKC_TEMPLATE_USAGES.ARTICLE_EFFECTIVE_DATE%TYPE;
1661 l_config_header_id OKC_TEMPLATE_USAGES.CONFIG_HEADER_ID%TYPE;
1662 l_config_revision_number OKC_TEMPLATE_USAGES.CONFIG_REVISION_NUMBER%TYPE;
1663 l_valid_config_yn OKC_TEMPLATE_USAGES.VALID_CONFIG_YN%TYPE;
1664 l_orig_system_reference_code OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
1665 l_orig_system_reference_id1 OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
1666 l_orig_system_reference_id2 OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
1667
1668 l_authoring_party_code OKC_TEMPLATE_USAGES.AUTHORING_PARTY_CODE%TYPE;
1669 l_contract_source_code OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
1670 l_approval_abstract_text OKC_TEMPLATE_USAGES.APPROVAL_ABSTRACT_TEXT%TYPE;
1671 l_autogen_deviations_flag OKC_TEMPLATE_USAGES.AUTOGEN_DEVIATIONS_FLAG%TYPE;
1672 -- Fix for bug# 3990983
1673 l_source_change_allowed_flag OKC_TEMPLATE_USAGES.SOURCE_CHANGE_ALLOWED_FLAG%TYPE;
1674
1675 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1676 l_created_by OKC_TEMPLATE_USAGES.CREATED_BY%TYPE;
1677 l_creation_date OKC_TEMPLATE_USAGES.CREATION_DATE%TYPE;
1678 l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
1679 l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
1680 l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
1681
1682
1683 l_lock_terms_flag OKC_TEMPLATE_USAGES.LOCK_TERMS_FLAG%TYPE;
1684 l_enable_reporting_flag OKC_TEMPLATE_USAGES.ENABLE_REPORTING_FLAG%TYPE;
1685 l_contract_admin_id OKC_TEMPLATE_USAGES.CONTRACT_ADMIN_ID%TYPE;
1686 l_legal_contact_id OKC_TEMPLATE_USAGES.LEGAL_CONTACT_ID%TYPE;
1687 l_locked_by_user_id OKC_TEMPLATE_USAGES.LOCKED_BY_USER_ID%TYPE;
1688
1689 BEGIN
1690
1691 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1692 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Entered Update_Row');
1693 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Locking _B row');
1694 END IF;
1695
1696 x_return_status := Lock_row(
1697 p_document_type => p_document_type,
1698 p_document_id => p_document_id,
1699 p_object_version_number => p_object_version_number
1700 );
1701 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1703 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1704 RAISE FND_API.G_EXC_ERROR;
1705 END IF;
1706
1707
1708 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1709 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7300: Setting attributes');
1710 END IF;
1711
1712 x_return_status := Set_Attributes(
1713 p_document_type => p_document_type,
1714 p_document_id => p_document_id,
1715 p_template_id => p_template_id,
1716 p_doc_numbering_scheme => p_doc_numbering_scheme,
1717 p_document_number => p_document_number,
1718 p_article_effective_date => p_article_effective_date,
1719 p_config_header_id => p_config_header_id,
1720 p_config_revision_number => p_config_revision_number,
1721 p_valid_config_yn => p_valid_config_yn,
1722 p_orig_system_reference_code => p_orig_system_reference_code,
1723 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1724 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1725 p_object_version_number => p_object_version_number,
1726 p_authoring_party_code => p_authoring_party_code,
1727 p_contract_source_code => p_contract_source_code,
1728 p_approval_abstract_text => p_approval_abstract_text,
1729 p_autogen_deviations_flag => p_autogen_deviations_flag,
1730 -- Fix for bug# 3990983
1731 p_source_change_allowed_flag => p_source_change_allowed_flag,
1732
1733 x_template_id => l_template_id,
1734 x_doc_numbering_scheme => l_doc_numbering_scheme,
1735 x_document_number => l_document_number,
1736 x_article_effective_date => l_article_effective_date,
1737 x_config_header_id => l_config_header_id,
1738 x_config_revision_number => l_config_revision_number,
1739 x_valid_config_yn => l_valid_config_yn,
1740 x_orig_system_reference_code => l_orig_system_reference_code,
1741 x_orig_system_reference_id1 => l_orig_system_reference_id1,
1742 x_orig_system_reference_id2 => l_orig_system_reference_id2,
1743 x_authoring_party_code => l_authoring_party_code,
1744 x_contract_source_code => l_contract_source_code,
1745 x_approval_abstract_text => l_approval_abstract_text,
1746 x_autogen_deviations_flag => l_autogen_deviations_flag,
1747 -- Fix for bug# 3990983
1748 x_source_change_allowed_flag => l_source_change_allowed_flag,
1749 p_lock_terms_flag => p_lock_terms_flag,
1750 p_enable_reporting_flag => p_enable_reporting_flag,
1751 p_contract_admin_id => p_contract_admin_id,
1752 p_legal_contact_id => p_legal_contact_id,
1753 p_locked_by_user_id => p_locked_by_user_id,
1754
1755 x_lock_terms_flag => l_lock_terms_flag,
1756 x_enable_reporting_flag => l_enable_reporting_flag,
1757 x_contract_admin_id => l_contract_admin_id,
1758 x_legal_contact_id => l_legal_contact_id,
1759 x_locked_by_user_id => l_locked_by_user_id
1760 );
1761 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1763 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1764 RAISE FND_API.G_EXC_ERROR;
1765 END IF;
1766
1767 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1768 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7400: Record Validation');
1769 END IF;
1770
1771 --- Validate all non-missing attributes
1772 x_return_status := Validate_Record(
1773 p_validation_level => p_validation_level,
1774 p_document_type => p_document_type,
1775 p_document_id => p_document_id,
1776 p_template_id => l_template_id,
1777 p_doc_numbering_scheme => l_doc_numbering_scheme,
1778 p_document_number => l_document_number,
1779 p_article_effective_date => l_article_effective_date,
1780 p_config_header_id => l_config_header_id,
1781 p_config_revision_number => l_config_revision_number,
1782 p_valid_config_yn => l_valid_config_yn,
1783 p_orig_system_reference_code => l_orig_system_reference_code,
1784 p_orig_system_reference_id1 => l_orig_system_reference_id1,
1785 p_orig_system_reference_id2 => l_orig_system_reference_id2,
1786 p_authoring_party_code => l_authoring_party_code,
1787 p_contract_source_code => l_contract_source_code,
1788 p_approval_abstract_text => l_approval_abstract_text,
1789 p_autogen_deviations_flag => l_autogen_deviations_flag,
1790 -- Fix for bug# 3990983
1791 p_source_change_allowed_flag => l_source_change_allowed_flag,
1792 p_lock_terms_flag => l_lock_terms_flag,
1793 p_enable_reporting_flag => l_enable_reporting_flag,
1794 p_contract_admin_id => l_contract_admin_id,
1795 p_legal_contact_id => l_legal_contact_id,
1796 p_locked_by_user_id => l_locked_by_user_id
1797 );
1798 --- If any errors happen abort API
1799 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1801 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1802 RAISE FND_API.G_EXC_ERROR;
1803 END IF;
1804
1805 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1806 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Filling WHO columns');
1807 END IF;
1808
1809 -- Filling who columns
1810 l_last_update_date := SYSDATE;
1811 l_last_updated_by := FND_GLOBAL.USER_ID;
1812 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1813
1814 -- Object version increment
1815 IF Nvl(l_object_version_number, 0) >= 0 THEN
1816 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
1817 END IF;
1818
1819 --------------------------------------------
1820 -- Call the Update_Row for each child record
1821 --------------------------------------------
1822 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1823 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: Updating Row');
1824 END IF;
1825
1826 x_return_status := Update_Row(
1827 p_document_type => p_document_type,
1828 p_document_id => p_document_id,
1829 p_template_id => l_template_id,
1830 p_doc_numbering_scheme => l_doc_numbering_scheme,
1831 p_document_number => l_document_number,
1832 p_article_effective_date => l_article_effective_date,
1833 p_config_header_id => l_config_header_id,
1834 p_config_revision_number => l_config_revision_number,
1835 p_valid_config_yn => l_valid_config_yn,
1836 p_orig_system_reference_code => l_orig_system_reference_code,
1837 p_orig_system_reference_id1 => l_orig_system_reference_id1,
1838 p_orig_system_reference_id2 => l_orig_system_reference_id2,
1839 p_object_version_number => l_object_version_number,
1840 --p_created_by => l_created_by,
1841 --p_creation_date => l_creation_date,
1842 p_last_updated_by => l_last_updated_by,
1843 p_last_update_login => l_last_update_login,
1844 p_last_update_date => l_last_update_date,
1845
1846 p_authoring_party_code => l_authoring_party_code,
1847 p_contract_source_code => l_contract_source_code,
1848 p_approval_abstract_text => l_approval_abstract_text,
1849 p_autogen_deviations_flag => l_autogen_deviations_flag,
1850 -- Fix for bug# 3990983
1851 p_source_change_allowed_flag => l_source_change_allowed_flag,
1852 p_lock_terms_flag => l_lock_terms_flag,
1853 p_enable_reporting_flag => l_enable_reporting_flag,
1854 p_contract_admin_id => l_contract_admin_id,
1855 p_legal_contact_id => l_legal_contact_id,
1856 p_locked_by_user_id => l_locked_by_user_id
1857 );
1858 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1859 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1860 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1861 RAISE FND_API.G_EXC_ERROR;
1862 END IF;
1863
1864
1865 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1866 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7800: Leaving Update_Row');
1867 END IF;
1868
1869 EXCEPTION
1870 WHEN FND_API.G_EXC_ERROR THEN
1871 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1872 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception');
1873 END IF;
1874 x_return_status := G_RET_STS_ERROR;
1875
1876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1877 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1878 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1879 END IF;
1880 x_return_status := G_RET_STS_UNEXP_ERROR;
1881
1882 WHEN OTHERS THEN
1883 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1884 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
1885 END IF;
1886 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1887 p_msg_name => G_UNEXPECTED_ERROR,
1888 p_token1 => G_SQLCODE_TOKEN,
1889 p_token1_value => sqlcode,
1890 p_token2 => G_SQLERRM_TOKEN,
1891 p_token2_value => sqlerrm);
1892 x_return_status := G_RET_STS_UNEXP_ERROR;
1893
1894 END Update_Row;
1895
1896 ---------------------------------------------------------------------------
1897 -- PROCEDURE Delete_Row
1898 ---------------------------------------------------------------------------
1899 -------------------------------------
1900 -- Delete_Row for:OKC_TEMPLATE_USAGES --
1901 -------------------------------------
1902 FUNCTION Delete_Row(
1903 p_document_type IN VARCHAR2,
1904 p_document_id IN NUMBER
1905 ) RETURN VARCHAR2 IS
1906 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
1907 BEGIN
1908
1909 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1910 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8200: Entered Delete_Row');
1911 END IF;
1912
1913 DELETE FROM OKC_TEMPLATE_USAGES WHERE DOCUMENT_TYPE = p_DOCUMENT_TYPE AND DOCUMENT_ID = p_DOCUMENT_ID;
1914
1915 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1916 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8300: Leaving Delete_Row');
1917 END IF;
1918
1919 RETURN( G_RET_STS_SUCCESS );
1920
1921 EXCEPTION
1922 WHEN OTHERS THEN
1923
1924 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1925 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
1926 END IF;
1927
1928 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1929 p_msg_name => G_UNEXPECTED_ERROR,
1930 p_token1 => G_SQLCODE_TOKEN,
1931 p_token1_value => sqlcode,
1932 p_token2 => G_SQLERRM_TOKEN,
1933 p_token2_value => sqlerrm);
1934
1935 RETURN( G_RET_STS_UNEXP_ERROR );
1936
1937 END Delete_Row;
1938
1939 -------------------------------------
1940 -- Delete_Row for:OKC_TEMPLATE_USAGES --
1941 -------------------------------------
1942 PROCEDURE Delete_Row(
1943 x_return_status OUT NOCOPY VARCHAR2,
1944 p_document_type IN VARCHAR2,
1945 p_document_id IN NUMBER,
1946 p_object_version_number IN NUMBER
1947 ) IS
1948 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
1949 BEGIN
1950
1951 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1952 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8800: Entered Delete_Row');
1953 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8900: Locking _B row');
1954 END IF;
1955
1956 x_return_status := Lock_row(
1957 p_document_type => p_document_type,
1958 p_document_id => p_document_id,
1959 p_object_version_number => p_object_version_number
1960 );
1961 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1963 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1964 RAISE FND_API.G_EXC_ERROR;
1965 END IF;
1966
1967
1968 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1969 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9100: Removing _B row');
1970 END IF;
1971 x_return_status := Delete_Row( p_document_type => p_document_type,p_document_id => p_document_id );
1972 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1974 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1975 RAISE FND_API.G_EXC_ERROR;
1976 END IF;
1977
1978
1979 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1980 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9300: Leaving Delete_Row');
1981 END IF;
1982
1983 EXCEPTION
1984 WHEN FND_API.G_EXC_ERROR THEN
1985 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1986 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception');
1987 END IF;
1988 x_return_status := G_RET_STS_ERROR;
1989
1990 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1991 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1992 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1993 END IF;
1994 x_return_status := G_RET_STS_UNEXP_ERROR;
1995
1996 WHEN OTHERS THEN
1997 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1998 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
1999 END IF;
2000 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2001 p_msg_name => G_UNEXPECTED_ERROR,
2002 p_token1 => G_SQLCODE_TOKEN,
2003 p_token1_value => sqlcode,
2004 p_token2 => G_SQLERRM_TOKEN,
2005 p_token2_value => sqlerrm);
2006 x_return_status := G_RET_STS_UNEXP_ERROR;
2007
2008 END Delete_Row;
2009
2010
2011 FUNCTION Create_Version(
2012 p_doc_type IN VARCHAR2,
2013 p_doc_id IN NUMBER,
2014 p_major_version IN NUMBER
2015 ) RETURN VARCHAR2 IS
2016 l_api_name CONSTANT VARCHAR2(30) := 'create_version';
2017 BEGIN
2018
2019 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2020 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9700: Entered create_version');
2021 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9800: Saving Base Table');
2022 END IF;
2023
2024 -----------------------------------------
2025 -- Saving Base Table
2026 -----------------------------------------
2027 INSERT INTO OKC_TEMPLATE_USAGES_H (
2028 major_version,
2029 DOCUMENT_TYPE,
2030 DOCUMENT_ID,
2031 TEMPLATE_ID,
2032 DOC_NUMBERING_SCHEME,
2033 DOCUMENT_NUMBER,
2034 ARTICLE_EFFECTIVE_DATE,
2035 CONFIG_HEADER_ID,
2036 CONFIG_REVISION_NUMBER,
2037 VALID_CONFIG_YN,
2038 ORIG_SYSTEM_REFERENCE_CODE,
2039 ORIG_SYSTEM_REFERENCE_ID1,
2040 ORIG_SYSTEM_REFERENCE_ID2,
2041 OBJECT_VERSION_NUMBER,
2042 CREATED_BY,
2043 CREATION_DATE,
2044 LAST_UPDATED_BY,
2045 LAST_UPDATE_LOGIN,
2046 LAST_UPDATE_DATE,
2047
2048 AUTHORING_PARTY_CODE,
2049 CONTRACT_SOURCE_CODE,
2050 APPROVAL_ABSTRACT_TEXT,
2051 AUTOGEN_DEVIATIONS_FLAG,
2052 -- Fix for bug# 3990983
2053 SOURCE_CHANGE_ALLOWED_FLAG,
2054 LOCK_TERMS_FLAG,
2055 ENABLE_REPORTING_FLAG,
2056 CONTRACT_ADMIN_ID,
2057 LEGAL_CONTACT_ID,
2058 LOCKED_BY_USER_ID)
2059 SELECT
2060 p_major_version,
2061 DOCUMENT_TYPE,
2062 DOCUMENT_ID,
2063 TEMPLATE_ID,
2064 DOC_NUMBERING_SCHEME,
2065 DOCUMENT_NUMBER,
2066 ARTICLE_EFFECTIVE_DATE,
2067 CONFIG_HEADER_ID,
2068 CONFIG_REVISION_NUMBER,
2069 VALID_CONFIG_YN,
2070 ORIG_SYSTEM_REFERENCE_CODE,
2071 ORIG_SYSTEM_REFERENCE_ID1,
2072 ORIG_SYSTEM_REFERENCE_ID2,
2073 OBJECT_VERSION_NUMBER,
2074 CREATED_BY,
2075 CREATION_DATE,
2076 LAST_UPDATED_BY,
2077 LAST_UPDATE_LOGIN,
2078 LAST_UPDATE_DATE,
2079
2080 AUTHORING_PARTY_CODE,
2081 CONTRACT_SOURCE_CODE,
2082 APPROVAL_ABSTRACT_TEXT,
2083 AUTOGEN_DEVIATIONS_FLAG,
2084 -- Fix for bug# 3990983
2085 SOURCE_CHANGE_ALLOWED_FLAG,
2086 LOCK_TERMS_FLAG,
2087 ENABLE_REPORTING_FLAG,
2088 CONTRACT_ADMIN_ID,
2089 LEGAL_CONTACT_ID,
2090 LOCKED_BY_USER_ID
2091 FROM OKC_TEMPLATE_USAGES
2092 WHERE document_type = p_doc_type and document_id = p_doc_id;
2093
2094 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2095 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10000: Leaving create_version');
2096 END IF;
2097
2098 RETURN( G_RET_STS_SUCCESS );
2099
2100 EXCEPTION
2101 WHEN OTHERS THEN
2102
2103 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2104 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10100: Leaving create_version because of EXCEPTION: '||sqlerrm);
2105 END IF;
2106
2107 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2108 p_msg_name => G_UNEXPECTED_ERROR,
2109 p_token1 => G_SQLCODE_TOKEN,
2110 p_token1_value => sqlcode,
2111 p_token2 => G_SQLERRM_TOKEN,
2112 p_token2_value => sqlerrm);
2113
2114 RETURN G_RET_STS_UNEXP_ERROR ;
2115
2116 END create_version;
2117
2118
2119 FUNCTION Restore_Version(
2120 p_doc_type IN VARCHAR2,
2121 p_doc_id IN NUMBER,
2122 p_major_version IN NUMBER
2123 ) RETURN VARCHAR2 IS
2124 l_api_name CONSTANT VARCHAR2(30) := 'restore_version';
2125 BEGIN
2126
2127 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2128 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10200: Entered restore_version');
2129 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10300: Restoring Base Table');
2130 END IF;
2131
2132 -----------------------------------------
2133 -- Restoring Base Table
2134 -----------------------------------------
2135 INSERT INTO OKC_TEMPLATE_USAGES (
2136 DOCUMENT_TYPE,
2137 DOCUMENT_ID,
2138 TEMPLATE_ID,
2139 DOC_NUMBERING_SCHEME,
2140 DOCUMENT_NUMBER,
2141 ARTICLE_EFFECTIVE_DATE,
2142 CONFIG_HEADER_ID,
2143 CONFIG_REVISION_NUMBER,
2144 VALID_CONFIG_YN,
2145 ORIG_SYSTEM_REFERENCE_CODE,
2146 ORIG_SYSTEM_REFERENCE_ID1,
2147 ORIG_SYSTEM_REFERENCE_ID2,
2148 OBJECT_VERSION_NUMBER,
2149 CREATED_BY,
2150 CREATION_DATE,
2151 LAST_UPDATED_BY,
2152 LAST_UPDATE_LOGIN,
2153 LAST_UPDATE_DATE,
2154
2155 AUTHORING_PARTY_CODE,
2156 CONTRACT_SOURCE_CODE,
2157 APPROVAL_ABSTRACT_TEXT,
2158 AUTOGEN_DEVIATIONS_FLAG,
2159 -- Fix for bug# 3990983
2160 SOURCE_CHANGE_ALLOWED_FLAG,
2161 LOCK_TERMS_FLAG,
2162 ENABLE_REPORTING_FLAG,
2163 CONTRACT_ADMIN_ID,
2164 LEGAL_CONTACT_ID,
2165 LOCKED_BY_USER_ID)
2166 SELECT
2167 DOCUMENT_TYPE,
2168 DOCUMENT_ID,
2169 TEMPLATE_ID,
2170 DOC_NUMBERING_SCHEME,
2171 DOCUMENT_NUMBER,
2172 ARTICLE_EFFECTIVE_DATE,
2173 CONFIG_HEADER_ID,
2174 CONFIG_REVISION_NUMBER,
2175 VALID_CONFIG_YN,
2176 ORIG_SYSTEM_REFERENCE_CODE,
2177 ORIG_SYSTEM_REFERENCE_ID1,
2178 ORIG_SYSTEM_REFERENCE_ID2,
2179 OBJECT_VERSION_NUMBER,
2180 CREATED_BY,
2181 CREATION_DATE,
2182 LAST_UPDATED_BY,
2183 LAST_UPDATE_LOGIN,
2184 LAST_UPDATE_DATE,
2185
2186 AUTHORING_PARTY_CODE,
2187 CONTRACT_SOURCE_CODE,
2188 APPROVAL_ABSTRACT_TEXT,
2189 AUTOGEN_DEVIATIONS_FLAG,
2190 -- Fix for bug# 3990983
2191 SOURCE_CHANGE_ALLOWED_FLAG,
2192 LOCK_TERMS_FLAG,
2193 ENABLE_REPORTING_FLAG,
2194 CONTRACT_ADMIN_ID,
2195 LEGAL_CONTACT_ID,
2196 LOCKED_BY_USER_ID
2197 FROM OKC_TEMPLATE_USAGES_H
2198 WHERE document_type = p_doc_type and document_id = p_doc_id AND major_version = p_major_version;
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,'10500: Leaving restore_version');
2202 END IF;
2203
2204 RETURN( G_RET_STS_SUCCESS );
2205
2206 EXCEPTION
2207 WHEN OTHERS THEN
2208
2209 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2210 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10600: Leaving restore_version because of EXCEPTION: '||sqlerrm);
2211 END IF;
2212
2213 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2214 p_msg_name => G_UNEXPECTED_ERROR,
2215 p_token1 => G_SQLCODE_TOKEN,
2216 p_token1_value => sqlcode,
2217 p_token2 => G_SQLERRM_TOKEN,
2218 p_token2_value => sqlerrm);
2219
2220 RETURN G_RET_STS_UNEXP_ERROR ;
2221
2222 END restore_version;
2223
2224 FUNCTION Delete_Version(
2225 p_doc_type IN VARCHAR2,
2226 p_doc_id IN NUMBER,
2227 p_major_version IN NUMBER
2228 ) RETURN VARCHAR2 IS
2229 l_api_name CONSTANT VARCHAR2(30) := 'delete_version';
2230 BEGIN
2231
2232 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2233 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7200: Entered Delete_Version');
2234 END IF;
2235
2236 -----------------------------------------
2237 -- Restoring Base Table
2238 -----------------------------------------
2239 DELETE
2240 FROM OKC_TEMPLATE_USAGES_H
2241 WHERE document_type = p_doc_type
2242 AND document_id = p_doc_id
2243 AND major_version = p_major_version;
2244
2245 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2246 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7300: Leaving Delete_Version');
2247 END IF;
2248
2249 RETURN( G_RET_STS_SUCCESS );
2250
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253
2254 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2255 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm);
2256 END IF;
2257
2258 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2259 p_msg_name => G_UNEXPECTED_ERROR,
2260 p_token1 => G_SQLCODE_TOKEN,
2261 p_token1_value => sqlcode,
2262 p_token2 => G_SQLERRM_TOKEN,
2263 p_token2_value => sqlerrm);
2264
2265 RETURN G_RET_STS_UNEXP_ERROR ;
2266
2267 END Delete_Version;
2268
2269
2270 PROCEDURE Update_Template_Id(
2271 x_return_status OUT NOCOPY VARCHAR2,
2272 p_old_template_id IN NUMBER,
2273 p_new_template_id IN NUMBER
2274 ) IS
2275 l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Id';
2276 E_Resource_Busy EXCEPTION;
2277 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2278 CURSOR lock_csr IS
2279 SELECT template_id
2280 FROM OKC_ALLOWED_TMPL_USAGES
2281 WHERE TEMPLATE_ID = p_old_template_id
2282 FOR UPDATE OF template_id NOWAIT;
2283 BEGIN
2284 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2285 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Entered Update_Template_Id');
2286 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Locking the Set');
2287 END IF;
2288 --------------------------------------------
2289 -- making OPEN/CLOSE cursor to lock records
2290 OPEN lock_csr;
2291 CLOSE lock_csr;
2292 --------------------------------------------
2293 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2294 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1300: Updating the Set');
2295 END IF;
2296 UPDATE okc_allowed_tmpl_usages
2297 SET template_id = p_new_template_id ,
2298 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
2299 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2300 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2301 LAST_UPDATE_DATE = Sysdate
2302 WHERE template_id= p_old_template_id;
2303 --------------------------------------------
2304 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2305 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: Leaving Update_Template_Id');
2306 END IF;
2307 EXCEPTION
2308 WHEN E_Resource_Busy THEN
2309 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2310 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1500: Leaving Update_Template_Id: E_Resource_Busy Exception');
2311 END IF;
2312
2313 IF (lock_csr%ISOPEN) THEN
2314 CLOSE lock_csr;
2315 END IF;
2316 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2317 x_return_status := G_RET_STS_ERROR ;
2318 WHEN OTHERS THEN
2319 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2320 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1600: Leaving Update_Template_Id because of EXCEPTION: '||sqlerrm);
2321 END IF;
2322
2323 x_return_status := G_RET_STS_UNEXP_ERROR ;
2324 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2325 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2326 END IF;
2327 END Update_Template_Id;
2328
2329 --
2330
2331 PROCEDURE Delete_Set(
2332 x_return_status OUT NOCOPY VARCHAR2,
2333 p_template_id IN NUMBER
2334 ) IS
2335 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Set';
2336 E_Resource_Busy EXCEPTION;
2337 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2338 CURSOR lock_csr IS
2339 SELECT rowid
2340 FROM OKC_ALLOWED_TMPL_USAGES
2341 WHERE TEMPLATE_ID = p_template_id
2342 FOR UPDATE NOWAIT;
2343 BEGIN
2344 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2345 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered Delete_Set');
2346 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Locking the Set');
2347 END IF;
2348 --------------------------------------------
2349 -- making OPEN/CLOSE cursor to lock records
2350 OPEN lock_csr;
2351 CLOSE lock_csr;
2352 --------------------------------------------
2353 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2354 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Deleting the Set');
2355 END IF;
2356 DELETE
2357 FROM OKC_ALLOWED_TMPL_USAGES
2358 WHERE TEMPLATE_ID = p_template_id;
2359 --------------------------------------------
2360 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2361 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving Delete_Set');
2362 END IF;
2363 EXCEPTION
2364 WHEN E_Resource_Busy THEN
2365 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2366 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Delete_Set:E_Resource_Busy Exception');
2367 END IF;
2368
2369 IF (lock_csr%ISOPEN) THEN
2370 CLOSE lock_csr;
2371 END IF;
2372 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2373 x_return_status := G_RET_STS_ERROR ;
2374 WHEN OTHERS THEN
2375 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2376 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Delete_Set because of EXCEPTION: '||sqlerrm);
2377 END IF;
2378
2379 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2380 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2381 END IF;
2382 x_return_status := G_RET_STS_UNEXP_ERROR ;
2383 END Delete_Set;
2384 END OKC_TEMPLATE_USAGES_PVT;