[Home] [Help]
PACKAGE BODY: APPS.OKC_TEMPLATE_USAGES_PVT
Source
1 PACKAGE BODY OKC_TEMPLATE_USAGES_PVT AS
2 /* $Header: OKCVTMPLUSGB.pls 120.3.12020000.2 2012/07/18 11:17:23 harchand 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 p_contract_expert_finish_flag IN VARCHAR2
1060 ) RETURN VARCHAR2 IS
1061 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1062
1063 BEGIN
1064
1065 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1066 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Entered Insert_Row function');
1067 END IF;
1068
1069 INSERT INTO OKC_TEMPLATE_USAGES(
1070 DOCUMENT_TYPE,
1071 DOCUMENT_ID,
1072 TEMPLATE_ID,
1073 DOC_NUMBERING_SCHEME,
1074 DOCUMENT_NUMBER,
1075 ARTICLE_EFFECTIVE_DATE,
1076 CONFIG_HEADER_ID,
1077 CONFIG_REVISION_NUMBER,
1078 VALID_CONFIG_YN,
1079 ORIG_SYSTEM_REFERENCE_CODE,
1080 ORIG_SYSTEM_REFERENCE_ID1,
1081 ORIG_SYSTEM_REFERENCE_ID2,
1082 OBJECT_VERSION_NUMBER,
1083 CREATED_BY,
1084 CREATION_DATE,
1085 LAST_UPDATED_BY,
1086 LAST_UPDATE_LOGIN,
1087 LAST_UPDATE_DATE,
1088
1089 AUTHORING_PARTY_CODE,
1090 CONTRACT_SOURCE_CODE ,
1091 APPROVAL_ABSTRACT_TEXT ,
1092 AUTOGEN_DEVIATIONS_FLAG,
1093 --Fix for bug# 3990983
1094 SOURCE_CHANGE_ALLOWED_FLAG,
1095 lock_terms_flag,
1096 enable_reporting_flag,
1097 contract_admin_id,
1098 legal_contact_id,
1099 locked_by_user_id,
1100 contract_expert_finish_flag)
1101 VALUES (
1102 p_document_type,
1103 p_document_id,
1104 p_template_id,
1105 p_doc_numbering_scheme,
1106 p_document_number,
1107 p_article_effective_date,
1108 p_config_header_id,
1109 p_config_revision_number,
1110 p_valid_config_yn,
1111 p_orig_system_reference_code,
1112 p_orig_system_reference_id1,
1113 p_orig_system_reference_id2,
1114 p_object_version_number,
1115 p_created_by,
1116 p_creation_date,
1117 p_last_updated_by,
1118 p_last_update_login,
1119 p_last_update_date,
1120
1121 p_authoring_party_code,
1122 p_contract_source_code,
1123 p_approval_abstract_text,
1124 p_autogen_deviations_flag,
1125 -- Fix for bug# 3990983
1126 p_source_change_allowed_flag,
1127 p_lock_terms_flag,
1128 p_enable_reporting_flag,
1129 p_contract_admin_id,
1130 p_legal_contact_id,
1131 p_locked_by_user_id,
1132 p_contract_expert_finish_flag
1133 );
1134
1135 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1136 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Leaving Insert_Row');
1137 END IF;
1138
1139 RETURN( G_RET_STS_SUCCESS );
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143
1144 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1145 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'3800: Leaving Insert_Row:OTHERS Exception');
1146 END IF;
1147
1148 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1149 p_msg_name => G_UNEXPECTED_ERROR,
1150 p_token1 => G_SQLCODE_TOKEN,
1151 p_token1_value => sqlcode,
1152 p_token2 => G_SQLERRM_TOKEN,
1153 p_token2_value => sqlerrm);
1154
1155 RETURN( G_RET_STS_UNEXP_ERROR );
1156
1157 END Insert_Row;
1158
1159
1160 -------------------------------------
1161 -- Insert_Row for:OKC_TEMPLATE_USAGES --
1162 -------------------------------------
1163 PROCEDURE Insert_Row(
1164 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1165 x_return_status OUT NOCOPY VARCHAR2,
1166
1167 p_document_type IN VARCHAR2,
1168 p_document_id IN NUMBER,
1169 p_template_id IN NUMBER,
1170 p_doc_numbering_scheme IN NUMBER,
1171 p_document_number IN VARCHAR2,
1172 p_article_effective_date IN DATE,
1173 p_config_header_id IN NUMBER,
1174 p_config_revision_number IN NUMBER,
1175 p_valid_config_yn IN VARCHAR2,
1176 p_orig_system_reference_code IN VARCHAR2,
1177 p_orig_system_reference_id1 IN NUMBER,
1178 p_orig_system_reference_id2 IN NUMBER,
1179
1180 --added for 10+ word integration and deviations report
1181 p_authoring_party_code IN VARCHAR2,
1182 p_contract_source_code IN VARCHAR2,
1183 p_approval_abstract_text IN CLOB,
1184 p_autogen_deviations_flag IN VARCHAR2,
1185 --Fix for bug# 3990983
1186 p_source_change_allowed_flag IN VARCHAR2,
1187
1188 x_document_type OUT NOCOPY VARCHAR2,
1189 x_document_id OUT NOCOPY NUMBER,
1190 p_lock_terms_flag IN VARCHAR2 := NULL,
1191 p_enable_reporting_flag IN VARCHAR2 := NULL,
1192 p_contract_admin_id IN NUMBER := NULL,
1193 p_legal_contact_id IN NUMBER := NULL,
1194 p_locked_by_user_id IN NUMBER := NULL,
1195 p_contract_expert_finish_flag IN VARCHAR2 := NULL
1196
1197 ) IS
1198 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1199 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1200 l_created_by OKC_TEMPLATE_USAGES.CREATED_BY%TYPE;
1201 l_creation_date OKC_TEMPLATE_USAGES.CREATION_DATE%TYPE;
1202 l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
1203 l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
1204 l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
1205 l_authoring_party_code OKC_TEMPLATE_USAGES.authoring_party_code%type;
1206
1207
1208 BEGIN
1209
1210 x_return_status := G_RET_STS_SUCCESS;
1211
1212 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1213 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4200: Entered Insert_Row');
1214 END IF;
1215
1216 -- Set Internal columns
1217 l_object_version_number := 1;
1218 l_creation_date := Sysdate;
1219 l_created_by := Fnd_Global.User_Id;
1220 l_last_update_date := l_creation_date;
1221 l_last_updated_by := l_created_by;
1222 l_last_update_login := Fnd_Global.Login_Id;
1223
1224 --added for 10+ word integration and deviations report
1225 --Set default value for p_authoring_party_code
1226 l_authoring_party_code := p_authoring_party_code;
1227 IF p_authoring_party_code is NULL THEN
1228
1229 l_authoring_party_code := G_INTERNAL_PARTY_CODE;
1230
1231 END IF;
1232
1233 --- Validate all non-missing attributes
1234 x_return_status := Validate_Record(
1235 p_validation_level => p_validation_level,
1236 p_document_type => p_document_type,
1237 p_document_id => p_document_id,
1238 p_template_id => p_template_id,
1239 p_doc_numbering_scheme => p_doc_numbering_scheme,
1240 p_document_number => p_document_number,
1241 p_article_effective_date => p_article_effective_date,
1242 p_config_header_id => p_config_header_id,
1243 p_config_revision_number => p_config_revision_number,
1244 p_valid_config_yn => p_valid_config_yn,
1245 p_orig_system_reference_code => p_orig_system_reference_code,
1246 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1247 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1248
1249 p_authoring_party_code => l_authoring_party_code,
1250 p_contract_source_code => p_contract_source_code,
1251 p_approval_abstract_text => p_approval_abstract_text,
1252 p_autogen_deviations_flag => p_autogen_deviations_flag,
1253 -- Fix for bug# 3990983
1254 p_source_change_allowed_flag => p_source_change_allowed_flag,
1255 p_lock_terms_flag => p_lock_terms_flag ,
1256 p_enable_reporting_flag => p_enable_reporting_flag,
1257 p_contract_admin_id => p_contract_admin_id ,
1258 p_legal_contact_id => p_legal_contact_id,
1259 p_locked_by_user_id => p_locked_by_user_id
1260 );
1261 --- If any errors happen abort API
1262 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1264 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1265 RAISE FND_API.G_EXC_ERROR;
1266 END IF;
1267
1268 --------------------------------------------
1269 -- Call the internal Insert_Row for each child record
1270 --------------------------------------------
1271 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1272 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4300: Call the internal Insert_Row for Base Table');
1273 END IF;
1274
1275 x_return_status := Insert_Row(
1276 p_document_type => p_document_type,
1277 p_document_id => p_document_id,
1278 p_template_id => p_template_id,
1279 p_doc_numbering_scheme => p_doc_numbering_scheme,
1280 p_document_number => p_document_number,
1281 p_article_effective_date => p_article_effective_date,
1282 p_config_header_id => p_config_header_id,
1283 p_config_revision_number => p_config_revision_number,
1284 p_valid_config_yn => p_valid_config_yn,
1285 p_orig_system_reference_code => p_orig_system_reference_code,
1286 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1287 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1288 p_object_version_number => l_object_version_number,
1289 p_created_by => l_created_by,
1290 p_creation_date => l_creation_date,
1291 p_last_updated_by => l_last_updated_by,
1292 p_last_update_login => l_last_update_login,
1293 p_last_update_date => l_last_update_date,
1294
1295 p_authoring_party_code => l_authoring_party_code,
1296 p_contract_source_code => p_contract_source_code,
1297 p_approval_abstract_text => p_approval_abstract_text,
1298 p_autogen_deviations_flag => p_autogen_deviations_flag,
1299 -- Fix for bug# 3990983
1300 p_source_change_allowed_flag => p_source_change_allowed_flag,
1301 p_lock_terms_flag => p_lock_terms_flag,
1302 p_enable_reporting_flag => p_enable_reporting_flag,
1303 p_contract_admin_id => p_contract_admin_id,
1304 p_legal_contact_id => p_legal_contact_id,
1305 p_locked_by_user_id => p_locked_by_user_id,
1306 p_contract_expert_finish_flag => p_contract_expert_finish_flag
1307 );
1308 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1310 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1311 RAISE FND_API.G_EXC_ERROR;
1312 END IF;
1313
1314
1315
1316 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1317 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Leaving Insert_Row');
1318 END IF;
1319
1320 EXCEPTION
1321 WHEN FND_API.G_EXC_ERROR THEN
1322 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1323 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception');
1324 END IF;
1325 x_return_status := G_RET_STS_ERROR;
1326
1327 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1328 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1329 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1330 END IF;
1331 x_return_status := G_RET_STS_UNEXP_ERROR;
1332
1333 WHEN OTHERS THEN
1334 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1335 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm);
1336 END IF;
1337 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1338 p_msg_name => G_UNEXPECTED_ERROR,
1339 p_token1 => G_SQLCODE_TOKEN,
1340 p_token1_value => sqlcode,
1341 p_token2 => G_SQLERRM_TOKEN,
1342 p_token2_value => sqlerrm);
1343 x_return_status := G_RET_STS_UNEXP_ERROR;
1344
1345 END Insert_Row;
1346 ---------------------------------------------------------------------------
1347 -- PROCEDURE Lock_Row
1348 ---------------------------------------------------------------------------
1349 -----------------------------------
1350 -- Lock_Row for:OKC_TEMPLATE_USAGES --
1351 -----------------------------------
1352 FUNCTION Lock_Row(
1353 p_document_type IN VARCHAR2,
1354 p_document_id IN NUMBER,
1355 p_object_version_number IN NUMBER
1356 ) RETURN VARCHAR2 IS
1357
1358 E_Resource_Busy EXCEPTION;
1359 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1360
1361 CURSOR lock_csr (cp_document_type VARCHAR2, cp_document_id NUMBER, cp_object_version_number NUMBER) IS
1362 SELECT object_version_number
1363 FROM OKC_TEMPLATE_USAGES
1364 WHERE DOCUMENT_TYPE = cp_document_type AND DOCUMENT_ID = cp_document_id
1365 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1366 FOR UPDATE OF object_version_number NOWAIT;
1367
1368 CURSOR lchk_csr (cp_document_type VARCHAR2, cp_document_id NUMBER) IS
1369 SELECT object_version_number
1370 FROM OKC_TEMPLATE_USAGES
1371 WHERE DOCUMENT_TYPE = cp_document_type AND DOCUMENT_ID = cp_document_id;
1372
1373 l_return_status VARCHAR2(1);
1374 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1375 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1376
1377 l_row_notfound BOOLEAN := FALSE;
1378 BEGIN
1379
1380 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1381 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4900: Entered Lock_Row');
1382 END IF;
1383
1384
1385 BEGIN
1386
1387 OPEN lock_csr( p_document_type, p_document_id, p_object_version_number );
1388 FETCH lock_csr INTO l_object_version_number;
1389 l_row_notfound := lock_csr%NOTFOUND;
1390 CLOSE lock_csr;
1391
1392 EXCEPTION
1393 WHEN E_Resource_Busy THEN
1394
1395 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1396 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Leaving Lock_Row:E_Resource_Busy Exception');
1397 END IF;
1398
1399 IF (lock_csr%ISOPEN) THEN
1400 CLOSE lock_csr;
1401 END IF;
1402 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1403 RETURN( G_RET_STS_ERROR );
1404 END;
1405
1406 IF ( l_row_notfound ) THEN
1407 l_return_status := G_RET_STS_ERROR;
1408
1409 OPEN lchk_csr(p_document_type, p_document_id);
1410 FETCH lchk_csr INTO l_object_version_number;
1411 l_row_notfound := lchk_csr%NOTFOUND;
1412 CLOSE lchk_csr;
1413
1414 IF (l_row_notfound) THEN
1415 Okc_Api.Set_Message(G_FND_APP,G_LOCK_RECORD_DELETED,
1416 'ENTITYNAME','OKC_TEMPLATE_USAGES',
1417 'PKEY',p_document_type||':'||p_document_id,
1418 'OVN',p_object_version_number
1419 );
1420 ELSIF l_object_version_number > p_object_version_number THEN
1421 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1422 ELSIF l_object_version_number = -1 THEN
1423 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1424 ELSE -- it can be the only above condition. It can happen after restore version
1425 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1426 END IF;
1427 ELSE
1428 l_return_status := G_RET_STS_SUCCESS;
1429 END IF;
1430
1431 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1432 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5100: Leaving Lock_Row');
1433 END IF;
1434
1435 RETURN( l_return_status );
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439
1440 IF (lock_csr%ISOPEN) THEN
1441 CLOSE lock_csr;
1442 END IF;
1443 IF (lchk_csr%ISOPEN) THEN
1444 CLOSE lchk_csr;
1445 END IF;
1446
1447 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1448 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
1449 END IF;
1450
1451 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1452 p_msg_name => G_UNEXPECTED_ERROR,
1453 p_token1 => G_SQLCODE_TOKEN,
1454 p_token1_value => sqlcode,
1455 p_token2 => G_SQLERRM_TOKEN,
1456 p_token2_value => sqlerrm);
1457
1458 RETURN( G_RET_STS_UNEXP_ERROR );
1459 END Lock_Row;
1460
1461 -----------------------------------
1462 -- Lock_Row for:OKC_TEMPLATE_USAGES --
1463 -----------------------------------
1464 PROCEDURE Lock_Row(
1465 x_return_status OUT NOCOPY VARCHAR2,
1466
1467 p_document_type IN VARCHAR2,
1468 p_document_id IN NUMBER,
1469 p_object_version_number IN NUMBER
1470 ) IS
1471 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1472 BEGIN
1473
1474 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1475 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5700: Entered Lock_Row');
1476 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5800: Locking Row for Base Table');
1477 END IF;
1478
1479 --------------------------------------------
1480 -- Call the LOCK_ROW for each _B child record
1481 --------------------------------------------
1482 x_return_status := Lock_Row(
1483 p_document_type => p_document_type,
1484 p_document_id => p_document_id,
1485 p_object_version_number => p_object_version_number
1486 );
1487 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1489 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1490 RAISE FND_API.G_EXC_ERROR;
1491 END IF;
1492
1493
1494
1495 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1496 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: Leaving Lock_Row');
1497 END IF;
1498
1499 EXCEPTION
1500 WHEN FND_API.G_EXC_ERROR THEN
1501 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1502 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception');
1503 END IF;
1504 x_return_status := G_RET_STS_ERROR;
1505
1506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1507 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1508 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1509 END IF;
1510 x_return_status := G_RET_STS_UNEXP_ERROR;
1511
1512 WHEN OTHERS THEN
1513 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1514 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
1515 END IF;
1516 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1517 p_msg_name => G_UNEXPECTED_ERROR,
1518 p_token1 => G_SQLCODE_TOKEN,
1519 p_token1_value => sqlcode,
1520 p_token2 => G_SQLERRM_TOKEN,
1521 p_token2_value => sqlerrm);
1522 x_return_status := G_RET_STS_UNEXP_ERROR;
1523
1524 END Lock_Row;
1525 ---------------------------------------------------------------------------
1526 -- PROCEDURE Update_Row
1527 ---------------------------------------------------------------------------
1528 -------------------------------------
1529 -- Update_Row for:OKC_TEMPLATE_USAGES --
1530 -------------------------------------
1531 FUNCTION Update_Row(
1532 p_document_type IN VARCHAR2,
1533 p_document_id IN NUMBER,
1534 p_template_id IN NUMBER,
1535 p_doc_numbering_scheme IN NUMBER,
1536 p_document_number IN VARCHAR2,
1537 p_article_effective_date IN DATE,
1538 p_config_header_id IN NUMBER,
1539 p_config_revision_number IN NUMBER,
1540 p_valid_config_yn IN VARCHAR2,
1541 p_orig_system_reference_code IN VARCHAR2,
1542 p_orig_system_reference_id1 IN NUMBER,
1543 p_orig_system_reference_id2 IN NUMBER,
1544 p_object_version_number IN NUMBER,
1545 --p_created_by IN NUMBER,
1546 --p_creation_date IN DATE,
1547 p_last_updated_by IN NUMBER,
1548 p_last_update_login IN NUMBER,
1549 p_last_update_date IN DATE,
1550
1551 --added for 10+ word integration and deviations report
1552 p_authoring_party_code IN VARCHAR2,
1553 p_contract_source_code IN VARCHAR2,
1554 p_approval_abstract_text IN CLOB,
1555 p_autogen_deviations_flag IN VARCHAR2,
1556 -- Fix for bug# 3990983
1557 p_source_change_allowed_flag IN VARCHAR2,
1558 p_lock_terms_flag IN VARCHAR2 ,
1559 p_enable_reporting_flag IN VARCHAR2 ,
1560 p_contract_admin_id IN NUMBER ,
1561 p_legal_contact_id IN NUMBER,
1562 p_locked_by_user_id IN NUMBER
1563 ) RETURN VARCHAR2 IS
1564 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1565 BEGIN
1566
1567 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1568 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6400: Entered Update_Row');
1569 END IF;
1570
1571 UPDATE OKC_TEMPLATE_USAGES
1572 SET TEMPLATE_ID = p_template_id,
1573 DOC_NUMBERING_SCHEME = p_doc_numbering_scheme,
1574 DOCUMENT_NUMBER = p_document_number,
1575 ARTICLE_EFFECTIVE_DATE = p_article_effective_date,
1576 CONFIG_HEADER_ID = p_config_header_id,
1577 CONFIG_REVISION_NUMBER = p_config_revision_number,
1578 VALID_CONFIG_YN = p_valid_config_yn,
1579 ORIG_SYSTEM_REFERENCE_CODE = p_orig_system_reference_code,
1580 ORIG_SYSTEM_REFERENCE_ID1 = p_orig_system_reference_id1,
1581 ORIG_SYSTEM_REFERENCE_ID2 = p_orig_system_reference_id2,
1582 OBJECT_VERSION_NUMBER = p_object_version_number,
1583 --CREATED_BY = p_created_by,
1584 --CREATION_DATE = p_creation_date,
1585 LAST_UPDATED_BY = p_last_updated_by,
1586 LAST_UPDATE_LOGIN = p_last_update_login,
1587 LAST_UPDATE_DATE = p_last_update_date,
1588
1589 AUTHORING_PARTY_CODE = p_authoring_party_code,
1590 CONTRACT_SOURCE_CODE = p_contract_source_code,
1591 APPROVAL_ABSTRACT_TEXT = p_approval_abstract_text,
1592 AUTOGEN_DEVIATIONS_FLAG = p_autogen_deviations_flag,
1593 -- Fix for bug# 3990983
1594 SOURCE_CHANGE_ALLOWED_FLAG = p_source_change_allowed_flag,
1595 lock_terms_flag = p_lock_terms_flag,
1596 enable_reporting_flag = p_enable_reporting_flag,
1597 contract_admin_id = p_contract_admin_id,
1598 legal_contact_id = p_legal_contact_id,
1599 locked_by_user_id = p_locked_by_user_id
1600 WHERE DOCUMENT_TYPE = p_document_type AND DOCUMENT_ID = p_document_id;
1601
1602 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1603 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6500: Leaving Update_Row');
1604 END IF;
1605
1606 RETURN G_RET_STS_SUCCESS ;
1607
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610
1611 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1612 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
1613 END IF;
1614
1615 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1616 p_msg_name => G_UNEXPECTED_ERROR,
1617 p_token1 => G_SQLCODE_TOKEN,
1618 p_token1_value => sqlcode,
1619 p_token2 => G_SQLERRM_TOKEN,
1620 p_token2_value => sqlerrm);
1621
1622 RETURN G_RET_STS_UNEXP_ERROR ;
1623
1624 END Update_Row;
1625
1626 -------------------------------------
1627 -- Update_Row for:OKC_TEMPLATE_USAGES --
1628 -------------------------------------
1629 PROCEDURE Update_Row(
1630 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1631
1632 x_return_status OUT NOCOPY VARCHAR2,
1633
1634 p_document_type IN VARCHAR2,
1635 p_document_id IN NUMBER,
1636 p_template_id IN NUMBER := NULL,
1637 p_doc_numbering_scheme IN NUMBER := NULL,
1638 p_document_number IN VARCHAR2 := NULL,
1639 p_article_effective_date IN DATE := NULL,
1640 p_config_header_id IN NUMBER := NULL,
1641 p_config_revision_number IN NUMBER := NULL,
1642 p_valid_config_yn IN VARCHAR2 := NULL,
1643 p_orig_system_reference_code IN VARCHAR2 := NULL,
1644 p_orig_system_reference_id1 IN NUMBER := NULL,
1645 p_orig_system_reference_id2 IN NUMBER := NULL,
1646
1647 --added for 10+ word integration and deviations report
1648 p_object_version_number IN NUMBER := NULL,
1649 p_authoring_party_code IN VARCHAR2 := NULL,
1650 p_contract_source_code IN VARCHAR2 := NULL,
1651 p_approval_abstract_text IN CLOB := NULL,
1652 p_autogen_deviations_flag IN VARCHAR2 := NULL,
1653 -- Fix for bug# 3990983
1654 p_source_change_allowed_flag IN VARCHAR2:= NULL ,
1655 p_lock_terms_flag IN VARCHAR2 := NULL,
1656 p_enable_reporting_flag IN VARCHAR2 := NULL,
1657 p_contract_admin_id IN NUMBER := NULL,
1658 p_legal_contact_id IN NUMBER := NULL,
1659 p_locked_by_user_id IN NUMBER := NULL
1660 ) IS
1661 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1662 l_template_id OKC_TEMPLATE_USAGES.TEMPLATE_ID%TYPE;
1663 l_doc_numbering_scheme OKC_TEMPLATE_USAGES.DOC_NUMBERING_SCHEME%TYPE;
1664 l_document_number OKC_TEMPLATE_USAGES.DOCUMENT_NUMBER%TYPE;
1665 l_article_effective_date OKC_TEMPLATE_USAGES.ARTICLE_EFFECTIVE_DATE%TYPE;
1666 l_config_header_id OKC_TEMPLATE_USAGES.CONFIG_HEADER_ID%TYPE;
1667 l_config_revision_number OKC_TEMPLATE_USAGES.CONFIG_REVISION_NUMBER%TYPE;
1668 l_valid_config_yn OKC_TEMPLATE_USAGES.VALID_CONFIG_YN%TYPE;
1669 l_orig_system_reference_code OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
1670 l_orig_system_reference_id1 OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
1671 l_orig_system_reference_id2 OKC_TEMPLATE_USAGES.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
1672
1673 l_authoring_party_code OKC_TEMPLATE_USAGES.AUTHORING_PARTY_CODE%TYPE;
1674 l_contract_source_code OKC_TEMPLATE_USAGES.CONTRACT_SOURCE_CODE%TYPE;
1675 l_approval_abstract_text OKC_TEMPLATE_USAGES.APPROVAL_ABSTRACT_TEXT%TYPE;
1676 l_autogen_deviations_flag OKC_TEMPLATE_USAGES.AUTOGEN_DEVIATIONS_FLAG%TYPE;
1677 -- Fix for bug# 3990983
1678 l_source_change_allowed_flag OKC_TEMPLATE_USAGES.SOURCE_CHANGE_ALLOWED_FLAG%TYPE;
1679
1680 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1681 l_created_by OKC_TEMPLATE_USAGES.CREATED_BY%TYPE;
1682 l_creation_date OKC_TEMPLATE_USAGES.CREATION_DATE%TYPE;
1683 l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
1684 l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
1685 l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
1686
1687
1688 l_lock_terms_flag OKC_TEMPLATE_USAGES.LOCK_TERMS_FLAG%TYPE;
1689 l_enable_reporting_flag OKC_TEMPLATE_USAGES.ENABLE_REPORTING_FLAG%TYPE;
1690 l_contract_admin_id OKC_TEMPLATE_USAGES.CONTRACT_ADMIN_ID%TYPE;
1691 l_legal_contact_id OKC_TEMPLATE_USAGES.LEGAL_CONTACT_ID%TYPE;
1692 l_locked_by_user_id OKC_TEMPLATE_USAGES.LOCKED_BY_USER_ID%TYPE;
1693
1694 BEGIN
1695
1696 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1697 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7000: Entered Update_Row');
1698 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7100: Locking _B row');
1699 END IF;
1700
1701 x_return_status := Lock_row(
1702 p_document_type => p_document_type,
1703 p_document_id => p_document_id,
1704 p_object_version_number => p_object_version_number
1705 );
1706 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1708 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1709 RAISE FND_API.G_EXC_ERROR;
1710 END IF;
1711
1712
1713 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1714 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7300: Setting attributes');
1715 END IF;
1716
1717 x_return_status := Set_Attributes(
1718 p_document_type => p_document_type,
1719 p_document_id => p_document_id,
1720 p_template_id => p_template_id,
1721 p_doc_numbering_scheme => p_doc_numbering_scheme,
1722 p_document_number => p_document_number,
1723 p_article_effective_date => p_article_effective_date,
1724 p_config_header_id => p_config_header_id,
1725 p_config_revision_number => p_config_revision_number,
1726 p_valid_config_yn => p_valid_config_yn,
1727 p_orig_system_reference_code => p_orig_system_reference_code,
1728 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1729 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1730 p_object_version_number => p_object_version_number,
1731 p_authoring_party_code => p_authoring_party_code,
1732 p_contract_source_code => p_contract_source_code,
1733 p_approval_abstract_text => p_approval_abstract_text,
1734 p_autogen_deviations_flag => p_autogen_deviations_flag,
1735 -- Fix for bug# 3990983
1736 p_source_change_allowed_flag => p_source_change_allowed_flag,
1737
1738 x_template_id => l_template_id,
1739 x_doc_numbering_scheme => l_doc_numbering_scheme,
1740 x_document_number => l_document_number,
1741 x_article_effective_date => l_article_effective_date,
1742 x_config_header_id => l_config_header_id,
1743 x_config_revision_number => l_config_revision_number,
1744 x_valid_config_yn => l_valid_config_yn,
1745 x_orig_system_reference_code => l_orig_system_reference_code,
1746 x_orig_system_reference_id1 => l_orig_system_reference_id1,
1747 x_orig_system_reference_id2 => l_orig_system_reference_id2,
1748 x_authoring_party_code => l_authoring_party_code,
1749 x_contract_source_code => l_contract_source_code,
1750 x_approval_abstract_text => l_approval_abstract_text,
1751 x_autogen_deviations_flag => l_autogen_deviations_flag,
1752 -- Fix for bug# 3990983
1753 x_source_change_allowed_flag => l_source_change_allowed_flag,
1754 p_lock_terms_flag => p_lock_terms_flag,
1755 p_enable_reporting_flag => p_enable_reporting_flag,
1756 p_contract_admin_id => p_contract_admin_id,
1757 p_legal_contact_id => p_legal_contact_id,
1758 p_locked_by_user_id => p_locked_by_user_id,
1759
1760 x_lock_terms_flag => l_lock_terms_flag,
1761 x_enable_reporting_flag => l_enable_reporting_flag,
1762 x_contract_admin_id => l_contract_admin_id,
1763 x_legal_contact_id => l_legal_contact_id,
1764 x_locked_by_user_id => l_locked_by_user_id
1765 );
1766 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1767 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1768 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1769 RAISE FND_API.G_EXC_ERROR;
1770 END IF;
1771
1772 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1773 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7400: Record Validation');
1774 END IF;
1775
1776 --- Validate all non-missing attributes
1777 x_return_status := Validate_Record(
1778 p_validation_level => p_validation_level,
1779 p_document_type => p_document_type,
1780 p_document_id => p_document_id,
1781 p_template_id => l_template_id,
1782 p_doc_numbering_scheme => l_doc_numbering_scheme,
1783 p_document_number => l_document_number,
1784 p_article_effective_date => l_article_effective_date,
1785 p_config_header_id => l_config_header_id,
1786 p_config_revision_number => l_config_revision_number,
1787 p_valid_config_yn => l_valid_config_yn,
1788 p_orig_system_reference_code => l_orig_system_reference_code,
1789 p_orig_system_reference_id1 => l_orig_system_reference_id1,
1790 p_orig_system_reference_id2 => l_orig_system_reference_id2,
1791 p_authoring_party_code => l_authoring_party_code,
1792 p_contract_source_code => l_contract_source_code,
1793 p_approval_abstract_text => l_approval_abstract_text,
1794 p_autogen_deviations_flag => l_autogen_deviations_flag,
1795 -- Fix for bug# 3990983
1796 p_source_change_allowed_flag => l_source_change_allowed_flag,
1797 p_lock_terms_flag => l_lock_terms_flag,
1798 p_enable_reporting_flag => l_enable_reporting_flag,
1799 p_contract_admin_id => l_contract_admin_id,
1800 p_legal_contact_id => l_legal_contact_id,
1801 p_locked_by_user_id => l_locked_by_user_id
1802 );
1803 --- If any errors happen abort API
1804 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1806 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1807 RAISE FND_API.G_EXC_ERROR;
1808 END IF;
1809
1810 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1811 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7500: Filling WHO columns');
1812 END IF;
1813
1814 -- Filling who columns
1815 l_last_update_date := SYSDATE;
1816 l_last_updated_by := FND_GLOBAL.USER_ID;
1817 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1818
1819 -- Object version increment
1820 IF Nvl(l_object_version_number, 0) >= 0 THEN
1821 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
1822 END IF;
1823
1824 --------------------------------------------
1825 -- Call the Update_Row for each child record
1826 --------------------------------------------
1827 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1828 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7600: Updating Row');
1829 END IF;
1830
1831 x_return_status := Update_Row(
1832 p_document_type => p_document_type,
1833 p_document_id => p_document_id,
1834 p_template_id => l_template_id,
1835 p_doc_numbering_scheme => l_doc_numbering_scheme,
1836 p_document_number => l_document_number,
1837 p_article_effective_date => l_article_effective_date,
1838 p_config_header_id => l_config_header_id,
1839 p_config_revision_number => l_config_revision_number,
1840 p_valid_config_yn => l_valid_config_yn,
1841 p_orig_system_reference_code => l_orig_system_reference_code,
1842 p_orig_system_reference_id1 => l_orig_system_reference_id1,
1843 p_orig_system_reference_id2 => l_orig_system_reference_id2,
1844 p_object_version_number => l_object_version_number,
1845 --p_created_by => l_created_by,
1846 --p_creation_date => l_creation_date,
1847 p_last_updated_by => l_last_updated_by,
1848 p_last_update_login => l_last_update_login,
1849 p_last_update_date => l_last_update_date,
1850
1851 p_authoring_party_code => l_authoring_party_code,
1852 p_contract_source_code => l_contract_source_code,
1853 p_approval_abstract_text => l_approval_abstract_text,
1854 p_autogen_deviations_flag => l_autogen_deviations_flag,
1855 -- Fix for bug# 3990983
1856 p_source_change_allowed_flag => l_source_change_allowed_flag,
1857 p_lock_terms_flag => l_lock_terms_flag,
1858 p_enable_reporting_flag => l_enable_reporting_flag,
1859 p_contract_admin_id => l_contract_admin_id,
1860 p_legal_contact_id => l_legal_contact_id,
1861 p_locked_by_user_id => l_locked_by_user_id
1862 );
1863 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1865 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1866 RAISE FND_API.G_EXC_ERROR;
1867 END IF;
1868
1869
1870 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1871 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7800: Leaving Update_Row');
1872 END IF;
1873
1874 EXCEPTION
1875 WHEN FND_API.G_EXC_ERROR THEN
1876 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1877 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception');
1878 END IF;
1879 x_return_status := G_RET_STS_ERROR;
1880
1881 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1882 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1883 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1884 END IF;
1885 x_return_status := G_RET_STS_UNEXP_ERROR;
1886
1887 WHEN OTHERS THEN
1888 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1889 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
1890 END IF;
1891 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1892 p_msg_name => G_UNEXPECTED_ERROR,
1893 p_token1 => G_SQLCODE_TOKEN,
1894 p_token1_value => sqlcode,
1895 p_token2 => G_SQLERRM_TOKEN,
1896 p_token2_value => sqlerrm);
1897 x_return_status := G_RET_STS_UNEXP_ERROR;
1898
1899 END Update_Row;
1900
1901 ---------------------------------------------------------------------------
1902 -- PROCEDURE Delete_Row
1903 ---------------------------------------------------------------------------
1904 -------------------------------------
1905 -- Delete_Row for:OKC_TEMPLATE_USAGES --
1906 -------------------------------------
1907 FUNCTION Delete_Row(
1908 p_document_type IN VARCHAR2,
1909 p_document_id IN NUMBER
1910 ) RETURN VARCHAR2 IS
1911 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
1912 BEGIN
1913
1914 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1915 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8200: Entered Delete_Row');
1916 END IF;
1917
1918 DELETE FROM OKC_TEMPLATE_USAGES WHERE DOCUMENT_TYPE = p_DOCUMENT_TYPE AND DOCUMENT_ID = p_DOCUMENT_ID;
1919
1920 IF nvl(fnd_profile.value('OKC_USE_CONTRACTS_RULES_ENGINE'), 'N') = 'Y' THEN
1921 DELETE FROM OKC_XPRT_DOC_QUES_RESPONSE WHERE DOC_TYPE = p_DOCUMENT_TYPE AND DOC_ID = p_DOCUMENT_ID;
1922 END IF;
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,'8300: Leaving Delete_Row');
1926 END IF;
1927
1928 RETURN( G_RET_STS_SUCCESS );
1929
1930 EXCEPTION
1931 WHEN OTHERS THEN
1932
1933 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1934 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
1935 END IF;
1936
1937 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1938 p_msg_name => G_UNEXPECTED_ERROR,
1939 p_token1 => G_SQLCODE_TOKEN,
1940 p_token1_value => sqlcode,
1941 p_token2 => G_SQLERRM_TOKEN,
1942 p_token2_value => sqlerrm);
1943
1944 RETURN( G_RET_STS_UNEXP_ERROR );
1945
1946 END Delete_Row;
1947
1948 -------------------------------------
1949 -- Delete_Row for:OKC_TEMPLATE_USAGES --
1950 -------------------------------------
1951 PROCEDURE Delete_Row(
1952 x_return_status OUT NOCOPY VARCHAR2,
1953 p_document_type IN VARCHAR2,
1954 p_document_id IN NUMBER,
1955 p_object_version_number IN NUMBER
1956 , p_retain_lock_xprt_yn IN VARCHAR2 := 'N' -- Conc Mod Changes
1957 ) IS
1958 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
1959
1960 BEGIN
1961
1962 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1963 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8800: Entered Delete_Row');
1964 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8900: Locking _B row');
1965 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'8910: p_retain_lock_xprt_yn : '|| p_retain_lock_xprt_yn);
1966 END IF;
1967
1968 -- Conc Mod Changes Start
1969 IF (p_retain_lock_xprt_yn = 'Y'
1970 AND okc_k_entity_locks_grp.isLockExists(P_ENTITY_NAME => okc_k_entity_locks_grp.G_XPRT_ENTITY,
1971 p_LOCK_BY_DOCUMENT_TYPE => p_document_type,
1972 p_LOCK_BY_DOCUMENT_ID => p_document_id
1973 ) = 'Y')
1974 THEN
1975 -- Lock exists so do not delete data.
1976 NULL;
1977 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1978 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9090: Lock Exists so not delteing data');
1979 END IF;
1980 ELSE
1981 -- Conc Mod Changes End
1982 x_return_status := Lock_row(
1983 p_document_type => p_document_type,
1984 p_document_id => p_document_id,
1985 p_object_version_number => p_object_version_number
1986 );
1987 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1988 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1989 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1990 RAISE FND_API.G_EXC_ERROR;
1991 END IF;
1992
1993
1994 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1995 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9100: Removing _B row');
1996 END IF;
1997 x_return_status := Delete_Row( p_document_type => p_document_type,p_document_id => p_document_id );
1998 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2000 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2001 RAISE FND_API.G_EXC_ERROR;
2002 END IF;
2003 END IF;
2004
2005 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2006 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9300: Leaving Delete_Row');
2007 END IF;
2008
2009 EXCEPTION
2010 WHEN FND_API.G_EXC_ERROR THEN
2011 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2012 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception');
2013 END IF;
2014 x_return_status := G_RET_STS_ERROR;
2015
2016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2017 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2018 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
2019 END IF;
2020 x_return_status := G_RET_STS_UNEXP_ERROR;
2021
2022 WHEN OTHERS THEN
2023 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2024 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
2025 END IF;
2026 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2027 p_msg_name => G_UNEXPECTED_ERROR,
2028 p_token1 => G_SQLCODE_TOKEN,
2029 p_token1_value => sqlcode,
2030 p_token2 => G_SQLERRM_TOKEN,
2031 p_token2_value => sqlerrm);
2032 x_return_status := G_RET_STS_UNEXP_ERROR;
2033
2034 END Delete_Row;
2035
2036
2037 FUNCTION Create_Version(
2038 p_doc_type IN VARCHAR2,
2039 p_doc_id IN NUMBER,
2040 p_major_version IN NUMBER
2041 ) RETURN VARCHAR2 IS
2042 l_api_name CONSTANT VARCHAR2(30) := 'create_version';
2043 BEGIN
2044
2045 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2046 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9700: Entered create_version');
2047 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'9800: Saving Base Table');
2048 END IF;
2049
2050 -----------------------------------------
2051 -- Saving Base Table
2052 -----------------------------------------
2053 INSERT INTO OKC_TEMPLATE_USAGES_H (
2054 major_version,
2055 DOCUMENT_TYPE,
2056 DOCUMENT_ID,
2057 TEMPLATE_ID,
2058 DOC_NUMBERING_SCHEME,
2059 DOCUMENT_NUMBER,
2060 ARTICLE_EFFECTIVE_DATE,
2061 CONFIG_HEADER_ID,
2062 CONFIG_REVISION_NUMBER,
2063 VALID_CONFIG_YN,
2064 ORIG_SYSTEM_REFERENCE_CODE,
2065 ORIG_SYSTEM_REFERENCE_ID1,
2066 ORIG_SYSTEM_REFERENCE_ID2,
2067 OBJECT_VERSION_NUMBER,
2068 CREATED_BY,
2069 CREATION_DATE,
2070 LAST_UPDATED_BY,
2071 LAST_UPDATE_LOGIN,
2072 LAST_UPDATE_DATE,
2073
2074 AUTHORING_PARTY_CODE,
2075 CONTRACT_SOURCE_CODE,
2076 APPROVAL_ABSTRACT_TEXT,
2077 AUTOGEN_DEVIATIONS_FLAG,
2078 -- Fix for bug# 3990983
2079 SOURCE_CHANGE_ALLOWED_FLAG,
2080 LOCK_TERMS_FLAG,
2081 ENABLE_REPORTING_FLAG,
2082 CONTRACT_ADMIN_ID,
2083 LEGAL_CONTACT_ID,
2084 LOCKED_BY_USER_ID,
2085 CONTRACT_EXPERT_FINISH_FLAG)
2086 SELECT
2087 p_major_version,
2088 DOCUMENT_TYPE,
2089 DOCUMENT_ID,
2090 TEMPLATE_ID,
2091 DOC_NUMBERING_SCHEME,
2092 DOCUMENT_NUMBER,
2093 ARTICLE_EFFECTIVE_DATE,
2094 CONFIG_HEADER_ID,
2095 CONFIG_REVISION_NUMBER,
2096 VALID_CONFIG_YN,
2097 ORIG_SYSTEM_REFERENCE_CODE,
2098 ORIG_SYSTEM_REFERENCE_ID1,
2099 ORIG_SYSTEM_REFERENCE_ID2,
2100 OBJECT_VERSION_NUMBER,
2101 CREATED_BY,
2102 CREATION_DATE,
2103 LAST_UPDATED_BY,
2104 LAST_UPDATE_LOGIN,
2105 LAST_UPDATE_DATE,
2106
2107 AUTHORING_PARTY_CODE,
2108 CONTRACT_SOURCE_CODE,
2109 APPROVAL_ABSTRACT_TEXT,
2110 AUTOGEN_DEVIATIONS_FLAG,
2111 -- Fix for bug# 3990983
2112 SOURCE_CHANGE_ALLOWED_FLAG,
2113 LOCK_TERMS_FLAG,
2114 ENABLE_REPORTING_FLAG,
2115 CONTRACT_ADMIN_ID,
2116 LEGAL_CONTACT_ID,
2117 LOCKED_BY_USER_ID,
2118 CONTRACT_EXPERT_FINISH_FLAG
2119 FROM OKC_TEMPLATE_USAGES
2120 WHERE document_type = p_doc_type and document_id = p_doc_id;
2121
2122 --If contract expert uses new okc rules engine, then save responses also into history table.
2123 IF nvl(fnd_profile.value('OKC_USE_CONTRACTS_RULES_ENGINE'), 'N') = 'Y' THEN --okc rules engine
2124 OKC_XPRT_RULES_ENGINE_PVT.create_xprt_responses_version(p_doc_id, p_doc_type, p_major_version); -- stores responses into history table
2125 END IF;
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,'10000: Leaving create_version');
2129 END IF;
2130
2131 RETURN( G_RET_STS_SUCCESS );
2132
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135
2136 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2137 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10100: Leaving create_version because of EXCEPTION: '||sqlerrm);
2138 END IF;
2139
2140 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2141 p_msg_name => G_UNEXPECTED_ERROR,
2142 p_token1 => G_SQLCODE_TOKEN,
2143 p_token1_value => sqlcode,
2144 p_token2 => G_SQLERRM_TOKEN,
2145 p_token2_value => sqlerrm);
2146
2147 RETURN G_RET_STS_UNEXP_ERROR ;
2148
2149 END create_version;
2150
2151
2152 FUNCTION Restore_Version(
2153 p_doc_type IN VARCHAR2,
2154 p_doc_id IN NUMBER,
2155 p_major_version IN NUMBER
2156 ) RETURN VARCHAR2 IS
2157 l_api_name CONSTANT VARCHAR2(30) := 'restore_version';
2158 BEGIN
2159
2160 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2161 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10200: Entered restore_version');
2162 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10300: Restoring Base Table');
2163 END IF;
2164
2165 -----------------------------------------
2166 -- Restoring Base Table
2167 -----------------------------------------
2168 INSERT INTO OKC_TEMPLATE_USAGES (
2169 DOCUMENT_TYPE,
2170 DOCUMENT_ID,
2171 TEMPLATE_ID,
2172 DOC_NUMBERING_SCHEME,
2173 DOCUMENT_NUMBER,
2174 ARTICLE_EFFECTIVE_DATE,
2175 CONFIG_HEADER_ID,
2176 CONFIG_REVISION_NUMBER,
2177 VALID_CONFIG_YN,
2178 ORIG_SYSTEM_REFERENCE_CODE,
2179 ORIG_SYSTEM_REFERENCE_ID1,
2180 ORIG_SYSTEM_REFERENCE_ID2,
2181 OBJECT_VERSION_NUMBER,
2182 CREATED_BY,
2183 CREATION_DATE,
2184 LAST_UPDATED_BY,
2185 LAST_UPDATE_LOGIN,
2186 LAST_UPDATE_DATE,
2187
2188 AUTHORING_PARTY_CODE,
2189 CONTRACT_SOURCE_CODE,
2190 APPROVAL_ABSTRACT_TEXT,
2191 AUTOGEN_DEVIATIONS_FLAG,
2192 -- Fix for bug# 3990983
2193 SOURCE_CHANGE_ALLOWED_FLAG,
2194 LOCK_TERMS_FLAG,
2195 ENABLE_REPORTING_FLAG,
2196 CONTRACT_ADMIN_ID,
2197 LEGAL_CONTACT_ID,
2198 LOCKED_BY_USER_ID,
2199 CONTRACT_EXPERT_FINISH_FLAG)
2200 SELECT
2201 DOCUMENT_TYPE,
2202 DOCUMENT_ID,
2203 TEMPLATE_ID,
2204 DOC_NUMBERING_SCHEME,
2205 DOCUMENT_NUMBER,
2206 ARTICLE_EFFECTIVE_DATE,
2207 CONFIG_HEADER_ID,
2208 CONFIG_REVISION_NUMBER,
2209 VALID_CONFIG_YN,
2210 ORIG_SYSTEM_REFERENCE_CODE,
2211 ORIG_SYSTEM_REFERENCE_ID1,
2212 ORIG_SYSTEM_REFERENCE_ID2,
2213 OBJECT_VERSION_NUMBER,
2214 CREATED_BY,
2215 CREATION_DATE,
2216 LAST_UPDATED_BY,
2217 LAST_UPDATE_LOGIN,
2218 LAST_UPDATE_DATE,
2219
2220 AUTHORING_PARTY_CODE,
2221 CONTRACT_SOURCE_CODE,
2222 APPROVAL_ABSTRACT_TEXT,
2223 AUTOGEN_DEVIATIONS_FLAG,
2224 -- Fix for bug# 3990983
2225 SOURCE_CHANGE_ALLOWED_FLAG,
2226 LOCK_TERMS_FLAG,
2227 ENABLE_REPORTING_FLAG,
2228 CONTRACT_ADMIN_ID,
2229 LEGAL_CONTACT_ID,
2230 LOCKED_BY_USER_ID,
2231 CONTRACT_EXPERT_FINISH_FLAG
2232 FROM OKC_TEMPLATE_USAGES_H
2233 WHERE document_type = p_doc_type and document_id = p_doc_id AND major_version = p_major_version;
2234
2235 --If contract expert uses new okc rules engine, then copy resposnes from history table to base table.
2236 IF nvl(fnd_profile.value('OKC_USE_CONTRACTS_RULES_ENGINE'), 'N') = 'Y' THEN --okc rules engine
2237 OKC_XPRT_RULES_ENGINE_PVT.restore_xprt_responses_version(p_doc_id, p_doc_type, p_major_version); -- copying responses from history table
2238 END IF;
2239
2240 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2241 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'10500: Leaving restore_version');
2242 END IF;
2243
2244 RETURN( G_RET_STS_SUCCESS );
2245
2246 EXCEPTION
2247 WHEN OTHERS THEN
2248
2249 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2250 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10600: Leaving restore_version because of EXCEPTION: '||sqlerrm);
2251 END IF;
2252
2253 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2254 p_msg_name => G_UNEXPECTED_ERROR,
2255 p_token1 => G_SQLCODE_TOKEN,
2256 p_token1_value => sqlcode,
2257 p_token2 => G_SQLERRM_TOKEN,
2258 p_token2_value => sqlerrm);
2259
2260 RETURN G_RET_STS_UNEXP_ERROR ;
2261
2262 END restore_version;
2263
2264 FUNCTION Delete_Version(
2265 p_doc_type IN VARCHAR2,
2266 p_doc_id IN NUMBER,
2267 p_major_version IN NUMBER
2268 ) RETURN VARCHAR2 IS
2269 l_api_name CONSTANT VARCHAR2(30) := 'delete_version';
2270 BEGIN
2271
2272 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2273 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7200: Entered Delete_Version');
2274 END IF;
2275
2276 -----------------------------------------
2277 -- Restoring Base Table
2278 -----------------------------------------
2279 DELETE
2280 FROM OKC_TEMPLATE_USAGES_H
2281 WHERE document_type = p_doc_type
2282 AND document_id = p_doc_id
2283 AND major_version = p_major_version;
2284
2285 --If contract expert uses new okc rules engine, then delete resposnes from history table.
2286 IF nvl(fnd_profile.value('OKC_USE_CONTRACTS_RULES_ENGINE'), 'N') = 'Y' THEN --okc rules engine
2287 OKC_XPRT_RULES_ENGINE_PVT.delete_xprt_responses_version(p_doc_id, p_doc_type, p_major_version); -- deleting responses from history table
2288 END IF;
2289
2290 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2291 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'7300: Leaving Delete_Version');
2292 END IF;
2293
2294 RETURN( G_RET_STS_SUCCESS );
2295
2296 EXCEPTION
2297 WHEN OTHERS THEN
2298
2299 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2300 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm);
2301 END IF;
2302
2303 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2304 p_msg_name => G_UNEXPECTED_ERROR,
2305 p_token1 => G_SQLCODE_TOKEN,
2306 p_token1_value => sqlcode,
2307 p_token2 => G_SQLERRM_TOKEN,
2308 p_token2_value => sqlerrm);
2309
2310 RETURN G_RET_STS_UNEXP_ERROR ;
2311
2312 END Delete_Version;
2313
2314
2315 PROCEDURE Update_Template_Id(
2316 x_return_status OUT NOCOPY VARCHAR2,
2317 p_old_template_id IN NUMBER,
2318 p_new_template_id IN NUMBER
2319 ) IS
2320 l_api_name CONSTANT VARCHAR2(30) := 'Update_Template_Id';
2321 E_Resource_Busy EXCEPTION;
2322 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2323 CURSOR lock_csr IS
2324 SELECT template_id
2325 FROM OKC_ALLOWED_TMPL_USAGES
2326 WHERE TEMPLATE_ID = p_old_template_id
2327 FOR UPDATE OF template_id NOWAIT;
2328 BEGIN
2329 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2330 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Entered Update_Template_Id');
2331 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Locking the Set');
2332 END IF;
2333 --------------------------------------------
2334 -- making OPEN/CLOSE cursor to lock records
2335 OPEN lock_csr;
2336 CLOSE lock_csr;
2337 --------------------------------------------
2338 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2339 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1300: Updating the Set');
2340 END IF;
2341 UPDATE okc_allowed_tmpl_usages
2342 SET template_id = p_new_template_id ,
2343 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
2344 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2345 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2346 LAST_UPDATE_DATE = Sysdate
2347 WHERE template_id= p_old_template_id;
2348 --------------------------------------------
2349 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2350 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: Leaving Update_Template_Id');
2351 END IF;
2352 EXCEPTION
2353 WHEN E_Resource_Busy THEN
2354 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2355 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1500: Leaving Update_Template_Id: E_Resource_Busy Exception');
2356 END IF;
2357
2358 IF (lock_csr%ISOPEN) THEN
2359 CLOSE lock_csr;
2360 END IF;
2361 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2362 x_return_status := G_RET_STS_ERROR ;
2363 WHEN OTHERS THEN
2364 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2365 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1600: Leaving Update_Template_Id because of EXCEPTION: '||sqlerrm);
2366 END IF;
2367
2368 x_return_status := G_RET_STS_UNEXP_ERROR ;
2369 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2370 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2371 END IF;
2372 END Update_Template_Id;
2373
2374 --
2375
2376 PROCEDURE Delete_Set(
2377 x_return_status OUT NOCOPY VARCHAR2,
2378 p_template_id IN NUMBER
2379 ) IS
2380 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Set';
2381 E_Resource_Busy EXCEPTION;
2382 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2383 CURSOR lock_csr IS
2384 SELECT rowid
2385 FROM OKC_ALLOWED_TMPL_USAGES
2386 WHERE TEMPLATE_ID = p_template_id
2387 FOR UPDATE NOWAIT;
2388 BEGIN
2389 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2390 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered Delete_Set');
2391 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Locking the Set');
2392 END IF;
2393 --------------------------------------------
2394 -- making OPEN/CLOSE cursor to lock records
2395 OPEN lock_csr;
2396 CLOSE lock_csr;
2397 --------------------------------------------
2398 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2399 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Deleting the Set');
2400 END IF;
2401 DELETE
2402 FROM OKC_ALLOWED_TMPL_USAGES
2403 WHERE TEMPLATE_ID = p_template_id;
2404 --------------------------------------------
2405 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2406 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving Delete_Set');
2407 END IF;
2408 EXCEPTION
2409 WHEN E_Resource_Busy THEN
2410 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2411 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Delete_Set:E_Resource_Busy Exception');
2412 END IF;
2413
2414 IF (lock_csr%ISOPEN) THEN
2415 CLOSE lock_csr;
2416 END IF;
2417 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2418 x_return_status := G_RET_STS_ERROR ;
2419 WHEN OTHERS THEN
2420 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2421 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Delete_Set because of EXCEPTION: '||sqlerrm);
2422 END IF;
2423
2424 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2425 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2426 END IF;
2427 x_return_status := G_RET_STS_UNEXP_ERROR ;
2428 END Delete_Set;
2429 END OKC_TEMPLATE_USAGES_PVT;