[Home] [Help]
PACKAGE BODY: APPS.OKC_TEMPLATE_USAGES_GRP
Source
1 PACKAGE BODY OKC_TEMPLATE_USAGES_GRP AS
2 /* $Header: OKCGTMPLUSGB.pls 120.3.12020000.2 2012/07/18 10:17:25 harchand ship $ */
3
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 ---------------------------------------------------------------------------
10 -- GLOBAL VARIABLES
11 ---------------------------------------------------------------------------
12 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TEMPLATE_USAGES_GRP';
13 G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsq.'||G_PKG_NAME||'.';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15 G_INTERNAL_PARTY_CODE CONSTANT VARCHAR2(30) := 'INTERNAL_ORG';
16
17 ------------------------------------------------------------------------------
18 -- GLOBAL CONSTANTS
19 ------------------------------------------------------------------------------
20 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
21 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
22 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
23 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
24 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
25
26 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
27 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
28 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
29
30 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
31 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
32 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
33
34 ---------------------------------------
35 -- PROCEDURE validate_row --
36 ---------------------------------------
37 PROCEDURE validate_row(
38 p_api_version IN NUMBER,
39 p_init_msg_list IN VARCHAR2 ,
40 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
41
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2,
45
46 p_document_type IN VARCHAR2,
47 p_document_id IN NUMBER,
48 p_template_id IN NUMBER,
49 p_doc_numbering_scheme IN NUMBER,
50 p_document_number IN VARCHAR2,
51 p_article_effective_date IN DATE,
52 p_config_header_id IN NUMBER,
53 p_config_revision_number IN NUMBER,
54 p_valid_config_yn IN VARCHAR2,
55 p_orig_system_reference_code IN VARCHAR2 := NULL,
56 p_orig_system_reference_id1 IN NUMBER := NULL,
57 p_orig_system_reference_id2 IN NUMBER := NULL,
58
59 p_approval_abstract_text IN CLOB := NULL,
60 p_contract_source_code IN VARCHAR2 := 'STRUCTURED',
61 p_authoring_party_code IN VARCHAR2 := NULL,
62 p_autogen_deviations_flag IN VARCHAR2 := NULL,
63 --Fix for bug# 3990983
64 p_source_change_allowed_flag IN VARCHAR2 := 'Y',
65
66 p_object_version_number IN NUMBER,
67 p_lock_terms_flag IN VARCHAR2 := NULL,
68 p_enable_reporting_flag IN VARCHAR2 := NULL,
69 p_contract_admin_id IN NUMBER := NULL,
70 p_legal_contact_id IN NUMBER := NULL,
71 p_locked_by_user_id IN NUMBER := NULL
72
73 ) IS
74 l_api_version CONSTANT NUMBER := 1;
75 l_api_name CONSTANT VARCHAR2(30) := 'g_validate_row';
76 l_authoring_party_code OKC_TEMPLATE_USAGES.authoring_party_code%type;
77
78 BEGIN
79
80 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
81 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered validate_row');
82 END IF;
83
84 -- Standard Start of API savepoint
85 SAVEPOINT g_validate_row_GRP;
86 -- Standard call to check for call compatibility.
87 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90 -- Initialize message list if p_init_msg_list is set to TRUE.
91 IF FND_API.to_Boolean( p_init_msg_list ) THEN
92 FND_MSG_PUB.initialize;
93 END IF;
94 -- Initialize API return status to success
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96
97 --added for 10+ word integration and deviations report
98 --Set default value for p_authoring_party_code
99 l_authoring_party_code := p_authoring_party_code;
100 IF p_authoring_party_code is NULL THEN
101
102 l_authoring_party_code := G_INTERNAL_PARTY_CODE;
103
104 END IF;
105
106 --------------------------------------------
107 -- Calling Simple API for Validation
108 --------------------------------------------
109 OKC_TEMPLATE_USAGES_PVT.Validate_Row(
110 p_validation_level => p_validation_level,
111 x_return_status => x_return_status,
112 p_document_type => p_document_type,
113 p_document_id => p_document_id,
114 p_template_id => p_template_id,
115 p_doc_numbering_scheme => p_doc_numbering_scheme,
116 p_document_number => p_document_number,
117 p_article_effective_date => p_article_effective_date,
118 p_config_header_id => p_config_header_id,
119 p_config_revision_number => p_config_revision_number,
120 p_valid_config_yn => p_valid_config_yn,
121 p_orig_system_reference_code => p_orig_system_reference_code,
122 p_orig_system_reference_id1 => p_orig_system_reference_id1,
123 p_orig_system_reference_id2 => p_orig_system_reference_id2,
124 p_object_version_number => p_object_version_number,
125
126 p_approval_abstract_text => p_approval_abstract_text,
127 p_contract_source_code => p_contract_source_code ,
128 p_authoring_party_code => l_authoring_party_code ,
129 p_autogen_deviations_flag => p_autogen_deviations_flag ,
130 --Fix for bug# 3990983
131 p_source_change_allowed_flag => p_source_change_allowed_flag,
132 p_lock_terms_flag => p_lock_terms_flag,
133 p_enable_reporting_flag => p_enable_reporting_flag,
134 p_contract_admin_id => p_contract_admin_id,
135 p_legal_contact_id => p_legal_contact_id,
136 p_locked_by_user_id => p_locked_by_user_id
137 );
138 --------------------------------------------
139 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
141 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
142 RAISE FND_API.G_EXC_ERROR ;
143 END IF;
144 --------------------------------------------
145
146 -- Standard call to get message count and if count is 1, get message info.
147 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
148
149 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
150 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Leaving validate_row');
151 END IF;
152
153 EXCEPTION
154 WHEN FND_API.G_EXC_ERROR THEN
155 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
156 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'300: Leaving Validate_Row: OKC_API.G_EXCEPTION_ERROR Exception');
157 END IF;
158 ROLLBACK TO g_validate_row_GRP;
159 x_return_status := G_RET_STS_ERROR ;
160 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
161
162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
163 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
164 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving Validate_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
165 END IF;
166 ROLLBACK TO g_validate_row_GRP;
167 x_return_status := G_RET_STS_UNEXP_ERROR ;
168 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
169
170 WHEN OTHERS THEN
171 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
172 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm);
173 END IF;
174
175 ROLLBACK TO g_validate_row_GRP;
176 x_return_status := G_RET_STS_UNEXP_ERROR ;
177 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
178 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
179 END IF;
180 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
181
182 END validate_row;
183
184 -------------------------------------
185 -- PROCEDURE create_template_usages
186 -------------------------------------
187 PROCEDURE create_template_usages(
188 p_api_version IN NUMBER,
189 p_init_msg_list IN VARCHAR2 ,
190 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
191 p_commit IN VARCHAR2 := FND_API.G_FALSE,
192
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2,
196
197 p_document_type IN VARCHAR2,
198 p_document_id IN NUMBER,
199 p_template_id IN NUMBER,
200 p_doc_numbering_scheme IN NUMBER,
201 p_document_number IN VARCHAR2,
202 p_article_effective_date IN DATE,
203 p_config_header_id IN NUMBER,
204 p_config_revision_number IN NUMBER,
205 p_valid_config_yn IN VARCHAR2,
206 p_orig_system_reference_code IN VARCHAR2 := NULL,
207 p_orig_system_reference_id1 IN NUMBER := NULL,
208 p_orig_system_reference_id2 IN NUMBER := NULL,
209
210 p_approval_abstract_text IN CLOB := NULL,
211 p_contract_source_code IN VARCHAR2 := 'STRUCTURED',
212 p_authoring_party_code IN VARCHAR2 := NULL,
213 p_autogen_deviations_flag IN VARCHAR2 := NULL,
214 -- Fix for bug# 3990983
215 p_source_change_allowed_flag IN VARCHAR2 := 'Y',
216 x_document_type OUT NOCOPY VARCHAR2,
217 x_document_id OUT NOCOPY NUMBER,
218 p_lock_terms_flag IN VARCHAR2 := NULL,
219 p_enable_reporting_flag IN VARCHAR2 := NULL,
220 p_contract_admin_id IN NUMBER := NULL,
221 p_legal_contact_id IN NUMBER := NULL,
222 p_locked_by_user_id IN NUMBER := NULL,
223 p_contract_expert_finish_flag IN VARCHAR2 := NULL
224
225 ) IS
226
227 l_api_version CONSTANT NUMBER := 1;
228 l_api_name CONSTANT VARCHAR2(30) := 'create_template_usages';
229 l_object_version_number OKC_TEMPLATE_USAGES.OBJECT_VERSION_NUMBER%TYPE := 1;
230 l_created_by OKC_TEMPLATE_USAGES.CREATED_BY%TYPE;
231 l_creation_date OKC_TEMPLATE_USAGES.CREATION_DATE%TYPE;
232 l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
233 l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
234 l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
235 l_authoring_party_code OKC_TEMPLATE_USAGES.authoring_party_code%type;
236
237 BEGIN
238
239 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
240 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: Entered create_template_usages');
241 END IF;
242
243 -- Standard Start of API savepoint
244 SAVEPOINT g_insert_row_GRP;
245 -- Standard call to check for call compatibility.
246 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 END IF;
249 -- Initialize message list if p_init_msg_list is set to TRUE.
250 IF FND_API.to_Boolean( p_init_msg_list ) THEN
251 FND_MSG_PUB.initialize;
252 END IF;
253 -- Initialize API return status to success
254 x_return_status := FND_API.G_RET_STS_SUCCESS;
255 --added for 10+ word integration and deviations report
256 --Set default value for p_authoring_party_code
257
258 -- Fix for bug# 4116433. l_authoring_party_code is to be initialized with the value from p_authoring_party_code
259 l_authoring_party_code := p_authoring_party_code;
260
261 IF p_authoring_party_code is NULL THEN
262
263 l_authoring_party_code := G_INTERNAL_PARTY_CODE;
264
265 END IF;
266
267 --------------------------------------------
268 -- Calling Simple API for Creating A Row
269 --------------------------------------------
270 OKC_TEMPLATE_USAGES_PVT.Insert_Row(
271 p_validation_level => p_validation_level,
272 x_return_status => x_return_status,
273 p_document_type => p_document_type,
274 p_document_id => p_document_id,
275 p_template_id => p_template_id,
276 p_doc_numbering_scheme => p_doc_numbering_scheme,
277 p_document_number => p_document_number,
278 p_article_effective_date => p_article_effective_date,
279 p_config_header_id => p_config_header_id,
280 p_config_revision_number => p_config_revision_number,
281 p_valid_config_yn => p_valid_config_yn,
282 p_orig_system_reference_code => p_orig_system_reference_code,
283 p_orig_system_reference_id1 => p_orig_system_reference_id1,
284 p_orig_system_reference_id2 => p_orig_system_reference_id2,
285 x_document_type => x_document_type,
286 x_document_id => x_document_id,
287
288 p_approval_abstract_text => p_approval_abstract_text,
289 p_contract_source_code => p_contract_source_code ,
290 p_authoring_party_code => l_authoring_party_code ,
291 p_autogen_deviations_flag => p_autogen_deviations_flag,
292 --Fix for bug# 3990983
293 p_source_change_allowed_flag => p_source_change_allowed_flag,
294 p_lock_terms_flag => p_lock_terms_flag,
295 p_enable_reporting_flag => p_enable_reporting_flag,
296 p_contract_admin_id => p_contract_admin_id,
297 p_legal_contact_id => p_legal_contact_id,
298 p_locked_by_user_id => p_locked_by_user_id,
299 p_contract_expert_finish_flag => p_contract_expert_finish_flag
300 );
301 --------------------------------------------
302 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
304 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
305 RAISE FND_API.G_EXC_ERROR ;
306 END IF;
307 --------------------------------------------
308
309 -- Standard check of p_commit
310 IF FND_API.To_Boolean( p_commit ) THEN
311 COMMIT WORK;
312 END IF;
313 -- Standard call to get message count and if count is 1, get message info.
314 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
315
316 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
317 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Leaving create_template_usages');
318 END IF;
319
320 EXCEPTION
321 WHEN FND_API.G_EXC_ERROR THEN
322 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
323 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving create_template_usages: OKC_API.G_EXCEPTION_ERROR Exception');
324 END IF;
325 ROLLBACK TO g_insert_row_GRP;
326 x_return_status := G_RET_STS_ERROR ;
327 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
328
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
331 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving insert_row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
332 END IF;
333 ROLLBACK TO g_insert_row_GRP;
334 x_return_status := G_RET_STS_UNEXP_ERROR ;
335 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
336
337 WHEN OTHERS THEN
338 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
339 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving insert_row because of EXCEPTION: '||sqlerrm);
340 END IF;
341
342 ROLLBACK TO g_insert_row_GRP;
343 x_return_status := G_RET_STS_UNEXP_ERROR ;
344 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
345 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
346 END IF;
347 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
348
349 END create_template_usages;
350 ---------------------------------------------------------------------------
351 -- PROCEDURE lock_row
352 ---------------------------------------------------------------------------
353 PROCEDURE lock_row(
354 p_api_version IN NUMBER,
355 p_init_msg_list IN VARCHAR2 ,
356
357 x_return_status OUT NOCOPY VARCHAR2,
358 x_msg_count OUT NOCOPY NUMBER,
359 x_msg_data OUT NOCOPY VARCHAR2,
360
361 p_document_type IN VARCHAR2,
362 p_document_id IN NUMBER,
363 p_object_version_number IN NUMBER
364 ) IS
365 l_api_version CONSTANT NUMBER := 1;
366 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
367 BEGIN
368
369 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
370 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Entered lock_row');
371 END IF;
372
373 -- Standard Start of API savepoint
374 SAVEPOINT g_lock_row_GRP;
375 -- Standard call to check for call compatibility.
376 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END IF;
379 -- Initialize message list if p_init_msg_list is set to TRUE.
380 IF FND_API.to_Boolean( p_init_msg_list ) THEN
381 FND_MSG_PUB.initialize;
382 END IF;
383 -- Initialize API return status to success
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385
386 --------------------------------------------
387 -- Calling Simple API for Locking A Row
388 --------------------------------------------
389 OKC_TEMPLATE_USAGES_PVT.lock_row(
390 x_return_status => x_return_status,
391 p_document_type => p_document_type,
392 p_document_id => p_document_id,
393 p_object_version_number => p_object_version_number
394 );
395 --------------------------------------------
396 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
398 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
399 RAISE FND_API.G_EXC_ERROR ;
400 END IF;
401 --------------------------------------------
402
403 -- Standard call to get message count and if count is 1, get message info.
404 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
405
406 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
407 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Leaving lock_row');
408 END IF;
409
410 EXCEPTION
411 WHEN FND_API.G_EXC_ERROR THEN
412 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
413 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1300: Leaving lock_Row: OKC_API.G_EXCEPTION_ERROR Exception');
414 END IF;
415 ROLLBACK TO g_lock_row_GRP;
416 x_return_status := G_RET_STS_ERROR ;
417 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
418
419 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
420 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
421 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1400: Leaving lock_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
422 END IF;
423 ROLLBACK TO g_lock_row_GRP;
424 x_return_status := G_RET_STS_UNEXP_ERROR ;
425 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
426
427 WHEN OTHERS THEN
428 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
429 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1500: Leaving lock_Row because of EXCEPTION: '||sqlerrm);
430 END IF;
431
432 ROLLBACK TO g_lock_row_GRP;
433 x_return_status := G_RET_STS_UNEXP_ERROR ;
434 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
435 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
436 END IF;
437 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
438
439 END lock_row;
440 ---------------------------------------------------------------------------
441 -- PROCEDURE update_template_usages
442 ---------------------------------------------------------------------------
443 PROCEDURE update_template_usages(
444 p_api_version IN NUMBER,
445 p_init_msg_list IN VARCHAR2 ,
446 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
447 p_commit IN VARCHAR2 := FND_API.G_FALSE,
448
449 x_return_status OUT NOCOPY VARCHAR2,
450 x_msg_count OUT NOCOPY NUMBER,
451 x_msg_data OUT NOCOPY VARCHAR2,
452
453 p_document_type IN VARCHAR2,
454 p_document_id IN NUMBER,
455 p_template_id IN NUMBER := NULL,
456 p_doc_numbering_scheme IN NUMBER := NULL,
457 p_document_number IN VARCHAR2 := NULL,
458 p_article_effective_date IN DATE := NULL,
459 p_config_header_id IN NUMBER := NULL,
460 p_config_revision_number IN NUMBER := NULL,
461 p_valid_config_yn IN VARCHAR2 := NULL,
462 p_orig_system_reference_code IN VARCHAR2 := NULL,
463 p_orig_system_reference_id1 IN NUMBER := NULL,
464 p_orig_system_reference_id2 IN NUMBER := NULL,
465
466 p_approval_abstract_text IN CLOB := NULL,
467 p_contract_source_code IN VARCHAR2 := NULL,
468 p_authoring_party_code IN VARCHAR2 := NULL,
469 p_autogen_deviations_flag IN VARCHAR2 := NULL,
470 -- Fix for bug# 3990983
471 p_source_change_allowed_flag IN VARCHAR2 := NULL,
472
473 p_object_version_number IN NUMBER := NULL,
474 p_lock_terms_flag IN VARCHAR2 := NULL,
475 p_enable_reporting_flag IN VARCHAR2 := NULL,
476 p_contract_admin_id IN NUMBER := NULL,
477 p_legal_contact_id IN NUMBER := NULL,
478 p_locked_by_user_id IN NUMBER := NULL
479
480 ) IS
481
482 l_api_version CONSTANT NUMBER := 1;
483 l_api_name CONSTANT VARCHAR2(30) := 'g_update_template_usages';
484
485 BEGIN
486
487 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
488 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Entered update_template_usages');
489 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1700: Locking row');
490 END IF;
491
492 -- Standard Start of API savepoint
493 SAVEPOINT g_update_row_GRP;
494 -- Standard call to check for call compatibility.
495 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497 END IF;
498 -- Initialize message list if p_init_msg_list is set to TRUE.
499 IF FND_API.to_Boolean( p_init_msg_list ) THEN
500 FND_MSG_PUB.initialize;
501 END IF;
502 -- Initialize API return status to success
503 x_return_status := FND_API.G_RET_STS_SUCCESS;
504
505 --------------------------------------------
506 -- Calling Simple API for Updating A Row
507 --------------------------------------------
508 OKC_TEMPLATE_USAGES_PVT.Update_Row(
509 p_validation_level => p_validation_level,
510 x_return_status => x_return_status,
511 p_document_type => p_document_type,
512 p_document_id => p_document_id,
513 p_template_id => p_template_id,
514 p_doc_numbering_scheme => p_doc_numbering_scheme,
515 p_document_number => p_document_number,
516 p_article_effective_date => p_article_effective_date,
517 p_config_header_id => p_config_header_id,
518 p_config_revision_number => p_config_revision_number,
519 p_valid_config_yn => p_valid_config_yn,
520 p_orig_system_reference_code => p_orig_system_reference_code,
521 p_orig_system_reference_id1 => p_orig_system_reference_id1,
522 p_orig_system_reference_id2 => p_orig_system_reference_id2,
523 p_object_version_number => p_object_version_number,
524 p_contract_source_code => p_contract_source_code,
525 p_authoring_party_code => p_authoring_party_code,
526 p_autogen_deviations_flag => p_autogen_deviations_flag,
527 -- Fix for bug# 3990983
528 p_source_change_allowed_flag => p_source_change_allowed_flag,
529 p_lock_terms_flag => p_lock_terms_flag,
530 p_enable_reporting_flag => p_enable_reporting_flag,
531 p_contract_admin_id => p_contract_admin_id,
532 p_legal_contact_id => p_legal_contact_id,
533 p_locked_by_user_id => p_locked_by_user_id
534 );
535 --------------------------------------------
536 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
538 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
539 RAISE FND_API.G_EXC_ERROR ;
540 END IF;
541 --------------------------------------------
542
543 -- Standard check of p_commit
544 IF FND_API.To_Boolean( p_commit ) THEN
545 COMMIT WORK;
546 END IF;
547 -- Standard call to get message count and if count is 1, get message info.
548 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
549
550 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Leaving update_template_usages');
552 END IF;
553
554 EXCEPTION
555 WHEN FND_API.G_EXC_ERROR THEN
556 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
557 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1900: Leaving update_template_usages: OKC_API.G_EXCEPTION_ERROR Exception');
558 END IF;
559 ROLLBACK TO g_update_row_GRP;
560 x_return_status := G_RET_STS_ERROR ;
561 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
562
563 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
564 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
565 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving update_template_usages: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
566 END IF;
567 ROLLBACK TO g_update_row_GRP;
568 x_return_status := G_RET_STS_UNEXP_ERROR ;
569 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
570
571 WHEN OTHERS THEN
572 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
573 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2100: Leaving update_template_usages because of EXCEPTION: '||sqlerrm);
574 END IF;
575
576 ROLLBACK TO g_update_row_GRP;
577 x_return_status := G_RET_STS_UNEXP_ERROR ;
578 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
579 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
580 END IF;
581 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
582
583 END update_template_usages;
584
585 ---------------------------------------------------------------------------
586 -- PROCEDURE delete_template_usages
587 ---------------------------------------------------------------------------
588 PROCEDURE delete_template_usages(
589 p_api_version IN NUMBER,
590 p_init_msg_list IN VARCHAR2 ,
591 p_commit IN VARCHAR2 := FND_API.G_FALSE,
592
593 x_return_status OUT NOCOPY VARCHAR2,
594 x_msg_count OUT NOCOPY NUMBER,
595 x_msg_data OUT NOCOPY VARCHAR2,
596
597 p_document_type IN VARCHAR2,
598 p_document_id IN NUMBER,
599 p_object_version_number IN NUMBER
600 ) IS
601 l_api_version CONSTANT NUMBER := 1;
602 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_template_usages';
603 BEGIN
604
605 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
606 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: Entered delete_template_usages');
607 END IF;
608
609 -- Standard Start of API savepoint
610 SAVEPOINT g_delete_row_GRP;
611 -- Standard call to check for call compatibility.
612 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
614 END IF;
615 -- Initialize message list if p_init_msg_list is set to TRUE.
616 IF FND_API.to_Boolean( p_init_msg_list ) THEN
617 FND_MSG_PUB.initialize;
618 END IF;
619 -- Initialize API return status to success
620 x_return_status := FND_API.G_RET_STS_SUCCESS;
621
622 --------------------------------------------
623 -- Calling Simple API for Deleting A Row
624 --------------------------------------------
625 OKC_TEMPLATE_USAGES_PVT.Delete_Row(
626 x_return_status => x_return_status,
627 p_document_type => p_document_type,
628 p_document_id => p_document_id,
629 p_object_version_number => p_object_version_number
630 );
631 --------------------------------------------
632 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
634 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
635 RAISE FND_API.G_EXC_ERROR ;
636 END IF;
637 --------------------------------------------
638
639 -- Standard check of p_commit
640 IF FND_API.To_Boolean( p_commit ) THEN
641 COMMIT WORK;
642 END IF;
643 -- Standard call to get message count and if count is 1, get message info.
644 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
645
646 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving delete_template_usages');
648 END IF;
649
650 EXCEPTION
651 WHEN FND_API.G_EXC_ERROR THEN
652 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
653 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving delete_template_usages: OKC_API.G_EXCEPTION_ERROR Exception');
654 END IF;
655 ROLLBACK TO g_delete_row_GRP;
656 x_return_status := G_RET_STS_ERROR ;
657 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
658
659 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
660 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
661 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving delete_template_usages: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
662 END IF;
663 ROLLBACK TO g_delete_row_GRP;
664 x_return_status := G_RET_STS_UNEXP_ERROR ;
665 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
666
667 WHEN OTHERS THEN
668 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2600: Leaving delete_template_usages because of EXCEPTION: '||sqlerrm);
670 END IF;
671
672 ROLLBACK TO g_delete_row_GRP;
673 x_return_status := G_RET_STS_UNEXP_ERROR ;
674 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
675 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
676 END IF;
677 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
678
679 END delete_template_usages;
680
681 PROCEDURE Set_Contract_Source(
682 p_api_version IN NUMBER,
683 p_init_msg_list IN VARCHAR2 ,
684 p_commit IN VARCHAR2 := FND_API.G_FALSE,
685
686 x_return_status OUT NOCOPY VARCHAR2,
687 x_msg_count OUT NOCOPY NUMBER,
688 x_msg_data OUT NOCOPY VARCHAR2,
689
690 p_document_type IN VARCHAR2,
691 p_document_id IN NUMBER,
692 p_contract_source_code IN VARCHAR2,
693 p_authoring_party_code IN VARCHAR2,
694 p_validation_string IN VARCHAR2,
695
696 p_document_number IN VARCHAR2
697 ) IS
698 l_api_version CONSTANT NUMBER := 1;
699 l_api_name CONSTANT VARCHAR2(30) := 'g_Set_Contract_Source';
700 l_value VARCHAR2(1) := 'N';
701 l_document_type OKC_TEMPLATE_USAGES.DOCUMENT_TYPE%TYPE;
702 l_document_id OKC_TEMPLATE_USAGES.DOCUMENT_ID%TYPE;
703 l_contract_source_code VARCHAR2(30);
704 l_authoring_party_code VARCHAR2(30);
705
706 CURSOR l_template_usages_csr IS
707 SELECT 'Y'
708 FROM OKC_TEMPLATE_USAGES
709 WHERE document_id = p_document_id
710 AND document_type = p_document_type;
711
712 CURSOR l_template_usages_details_csr IS
713 SELECT contract_source_code,
714 authoring_party_code
715 FROM OKC_TEMPLATE_USAGES
716 WHERE document_type = p_document_type
717 AND document_id = p_document_id;
718
719 BEGIN
720
721 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
722 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: Entered Set_Contract_Source');
723 END IF;
724
725 -- Standard Start of API savepoint
726 SAVEPOINT g_set_contract_source_GRP;
727
728 -- Standard call to check for call compatibility.
729 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
731 END IF;
732
733 -- Initialize message list if p_init_msg_list is set to TRUE.
734 IF FND_API.to_Boolean( p_init_msg_list ) THEN
735 FND_MSG_PUB.initialize;
736 END IF;
737
738 -- Check if anything changed. If no change, then nothing to update, return.
739 OPEN l_template_usages_csr;
740 FETCH l_template_usages_csr into l_value;
741 CLOSE l_template_usages_csr;
742
743 IF l_value = 'Y' THEN
744 OPEN l_template_usages_details_csr;
745 FETCH l_template_usages_details_csr into l_contract_source_code, l_authoring_party_code;
746 CLOSE l_template_usages_details_csr;
747
748 IF ( l_contract_source_code = p_contract_source_code
749 AND l_authoring_party_code = p_authoring_party_code ) THEN
750 RETURN;
751 END IF;
752 END IF;
753
754 -- Bug 4003064. Added call to check if ok_to_commit()
755 IF NOT FND_API.To_Boolean( OKC_TERMS_UTIL_GRP.ok_to_commit (
756 p_api_version => l_api_version,
757 p_init_msg_list => FND_API.G_FALSE,
758 p_doc_type => p_document_type,
759 p_doc_id => p_document_id,
760 p_validation_string => p_validation_string,
761 p_tmpl_change => 'Y',
762 x_return_status => x_return_status,
763 x_msg_data => x_msg_data,
764 x_msg_count => x_msg_count )
765 ) THEN
766 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
767 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'700: Issue with document header Record.Cannot commit');
768 END IF;
769 RAISE FND_API.G_EXC_ERROR;
770 END IF;
771
772 -- Initialize API return status to success
773 x_return_status := FND_API.G_RET_STS_SUCCESS;
774
775 OPEN l_template_usages_csr;
776 FETCH l_template_usages_csr into l_value;
777 CLOSE l_template_usages_csr;
778
779 IF p_contract_source_code = 'STRUCTURED' THEN
780 OKC_CONTRACT_DOCS_GRP.Clear_Primary_Doc_Flag(
781 p_document_type => p_document_type,
782 p_document_id => p_document_id ,
783 x_return_status => x_return_status );
784 END IF;
785
786 IF l_value = 'Y' THEN
787 --update existing template usages record.
788 OKC_TEMPLATE_USAGES_PVT.update_row(
789 x_return_status => x_return_status ,
790
791 p_document_type => p_document_type ,
792 p_document_id => p_document_id ,
793 p_authoring_party_code => p_authoring_party_code,
794 p_contract_source_code => p_contract_source_code,
795 p_document_number => p_document_number);
796
797 ELSE
798 --create new template usages record
799 OKC_TEMPLATE_USAGES_PVT.insert_row(
800 x_return_status => x_return_status,
801
802 p_document_type => p_document_type ,
803 p_document_id => p_document_id ,
804 p_template_id => null,
805 p_doc_numbering_scheme => null,
806 p_document_number => p_document_number,
807 p_article_effective_date => null,
808 p_config_header_id => null,
809 p_config_revision_number => null,
810 p_valid_config_yn => null,
811 p_authoring_party_code => p_authoring_party_code,
812 p_contract_source_code => p_contract_source_code,
813
814 x_document_type => l_document_type,
815 x_document_id => l_document_id );
816
817 END IF;
818
819 --------------------------------------------
820 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
822 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
823 RAISE FND_API.G_EXC_ERROR ;
824 END IF;
825 --------------------------------------------
826
827 -- Standard check of p_commit
828 IF FND_API.To_Boolean( p_commit ) THEN
829 COMMIT WORK;
830 END IF;
831 -- Standard call to get message count and if count is 1, get message info.
832 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
833
834 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
835 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving Set_Contract_Source');
836 END IF;
837
838 EXCEPTION
839 WHEN FND_API.G_EXC_ERROR THEN
840 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving Set_Contract_Source: OKC_API.G_EXCEPTION_ERROR Exception');
842 END IF;
843 ROLLBACK TO g_set_contract_source_GRP;
844 x_return_status := G_RET_STS_ERROR ;
845 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
846
847 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
848 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
849 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving Set_Contract_Source: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
850 END IF;
851 ROLLBACK TO g_set_contract_source_GRP;
852 x_return_status := G_RET_STS_UNEXP_ERROR ;
853 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
854
855 WHEN OTHERS THEN
856 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2600: Leaving Set_Contract_Source because of EXCEPTION: '||sqlerrm);
858 END IF;
859
860 ROLLBACK TO g_set_contract_source_GRP;
861 x_return_status := G_RET_STS_UNEXP_ERROR ;
862 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
863 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
864 END IF;
865 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
866
867 END Set_Contract_Source;
868
869 PROCEDURE Set_Contract_Source_details(
870 p_api_version IN NUMBER,
871 p_init_msg_list IN VARCHAR2 ,
872 p_commit IN VARCHAR2 := FND_API.G_FALSE,
873
874 x_return_status OUT NOCOPY VARCHAR2,
875 x_msg_count OUT NOCOPY NUMBER,
876 x_msg_data OUT NOCOPY VARCHAR2,
877
878 p_document_type IN VARCHAR2,
879 p_document_id IN NUMBER,
880 p_contract_source_code IN VARCHAR2,
881 p_authoring_party_code IN VARCHAR2,
882 p_validation_string IN VARCHAR2,
883
884 p_document_number IN VARCHAR2,
885 p_enable_reporting_flag IN VARCHAR2 := NULL,
886 p_contract_admin_id IN NUMBER := NULL,
887 p_legal_contact_id IN NUMBER := NULL
888
889 ) IS
890 l_api_version CONSTANT NUMBER := 1;
891 l_api_name CONSTANT VARCHAR2(30) := 'g_Set_Contract_Source_details';
892 l_value VARCHAR2(1) := 'N';
893 l_document_type OKC_TEMPLATE_USAGES.DOCUMENT_TYPE%TYPE;
894 l_document_id OKC_TEMPLATE_USAGES.DOCUMENT_ID%TYPE;
895 l_contract_source_code OKC_TEMPLATE_USAGES.contract_source_code%TYPE;
896 l_authoring_party_code OKC_TEMPLATE_USAGES.authoring_party_code%TYPE;
897
898 l_contract_admin_id OKC_TEMPLATE_USAGES.CONTRACT_ADMIN_ID%TYPE;
899 l_legal_contact_id OKC_TEMPLATE_USAGES.LEGAL_CONTACT_ID%TYPE;
900 CURSOR l_template_usages_csr IS
901 SELECT 'Y'
902 FROM OKC_TEMPLATE_USAGES
903 WHERE document_id = p_document_id
904 AND document_type = p_document_type;
905
906 CURSOR l_template_usages_details_csr IS
907 SELECT contract_source_code,
908 authoring_party_code
909 FROM OKC_TEMPLATE_USAGES
910 WHERE document_type = p_document_type
911 AND document_id = p_document_id;
912
913 BEGIN
914
915 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
916 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: Entered Set_Contract_Source');
917 END IF;
918
919 -- Standard Start of API savepoint
920 SAVEPOINT g_set_contract_src_dtls_GRP;
921
922 -- Standard call to check for call compatibility.
923 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
924 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
925 END IF;
926
927 -- Initialize message list if p_init_msg_list is set to TRUE.
928 IF FND_API.to_Boolean( p_init_msg_list ) THEN
929 FND_MSG_PUB.initialize;
930 END IF;
931
932 -- Check if anything changed. If no change, then nothing to update, return.
933 OPEN l_template_usages_csr;
934 FETCH l_template_usages_csr into l_value;
935 CLOSE l_template_usages_csr;
936
937
938
939 -- Bug 4003064. Added call to check if ok_to_commit()
940 IF NOT FND_API.To_Boolean( OKC_TERMS_UTIL_GRP.ok_to_commit (
941 p_api_version => l_api_version,
942 p_init_msg_list => FND_API.G_FALSE,
943 p_doc_type => p_document_type,
944 p_doc_id => p_document_id,
945 p_validation_string => p_validation_string,
946 p_tmpl_change => 'Y',
947 x_return_status => x_return_status,
948 x_msg_data => x_msg_data,
949 x_msg_count => x_msg_count )
950 ) THEN
951 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
952 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'700: Issue with document header Record.Cannot commit');
953 END IF;
954 RAISE FND_API.G_EXC_ERROR;
955 END IF;
956
957 -- Initialize API return status to success
958 x_return_status := FND_API.G_RET_STS_SUCCESS;
959
960 OPEN l_template_usages_csr;
961 FETCH l_template_usages_csr into l_value;
962 CLOSE l_template_usages_csr;
963
964 IF p_contract_source_code = 'STRUCTURED' THEN
965 OKC_CONTRACT_DOCS_GRP.Clear_Primary_Doc_Flag(
966 p_document_type => p_document_type,
967 p_document_id => p_document_id ,
968 x_return_status => x_return_status );
969 END IF;
970
971 IF l_value = 'Y' THEN
972 --update existing template usages record.
973 OKC_TEMPLATE_USAGES_PVT.update_row(
974 x_return_status => x_return_status ,
975
976 p_document_type => p_document_type ,
977 p_document_id => p_document_id ,
978 p_authoring_party_code => p_authoring_party_code,
979 p_contract_source_code => p_contract_source_code,
980 p_document_number => p_document_number,
981 p_enable_reporting_flag => p_enable_reporting_flag,
982 p_contract_admin_id => p_contract_admin_id,
983 p_legal_contact_id => p_legal_contact_id
984 );
985
986 ELSE
987 --create new template usages record
988
989 --Fix for bug# 4733056
990 l_contract_admin_id := p_contract_admin_id;
991 l_legal_contact_id := p_legal_contact_id;
992 if(p_contract_admin_id = G_MISS_NUM) then
993 l_contract_admin_id := null;
994 end if;
995
996 if(p_legal_contact_id = G_MISS_NUM) then
997 l_legal_contact_id := null;
998 end if;
999
1000
1001 OKC_TEMPLATE_USAGES_PVT.insert_row(
1002 x_return_status => x_return_status,
1003
1004 p_document_type => p_document_type ,
1005 p_document_id => p_document_id ,
1006 p_template_id => null,
1007 p_doc_numbering_scheme => null,
1008 p_document_number => p_document_number,
1009 p_article_effective_date => null,
1010 p_config_header_id => null,
1011 p_config_revision_number => null,
1012 p_valid_config_yn => null,
1013 p_authoring_party_code => p_authoring_party_code,
1014 p_contract_source_code => p_contract_source_code,
1015 p_enable_reporting_flag => p_enable_reporting_flag,
1016 p_contract_admin_id => l_contract_admin_id,
1017 p_legal_contact_id => l_legal_contact_id,
1018
1019 x_document_type => l_document_type,
1020 x_document_id => l_document_id );
1021
1022 END IF;
1023
1024 --------------------------------------------
1025 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1027 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1028 RAISE FND_API.G_EXC_ERROR ;
1029 END IF;
1030 --------------------------------------------
1031
1032 -- Standard check of p_commit
1033 IF FND_API.To_Boolean( p_commit ) THEN
1034 COMMIT WORK;
1035 END IF;
1036 -- Standard call to get message count and if count is 1, get message info.
1037 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1038
1039 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1040 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving Set_Contract_Source_details');
1041 END IF;
1042
1043 EXCEPTION
1044 WHEN FND_API.G_EXC_ERROR THEN
1045 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1046 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving Set_Contract_Source_details: OKC_API.G_EXCEPTION_ERROR Exception');
1047 END IF;
1048 ROLLBACK TO g_set_contract_src_dtls_GRP;
1049 x_return_status := G_RET_STS_ERROR ;
1050 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1051
1052 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1053 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1054 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving Set_Contract_Source_details: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1055 END IF;
1056 ROLLBACK TO g_set_contract_src_dtls_GRP;
1057 x_return_status := G_RET_STS_UNEXP_ERROR ;
1058 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1059
1060 WHEN OTHERS THEN
1061 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2600: Leaving Set_Contract_Source_details because of EXCEPTION: '||sqlerrm);
1063 END IF;
1064
1065 ROLLBACK TO g_set_contract_src_dtls_GRP;
1066 x_return_status := G_RET_STS_UNEXP_ERROR ;
1067 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1068 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1069 END IF;
1070 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
1071
1072 END Set_Contract_Source_details;
1073
1074 END OKC_TEMPLATE_USAGES_GRP;