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