[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_TEMPLATES_GRP
Source
1 PACKAGE BODY OKC_TERMS_TEMPLATES_GRP AS
2 /* $Header: OKCGTERMTMPLB.pls 120.1 2005/06/07 04:46:02 appldev $ */
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_TERMS_TEMPLATES_GRP';
13 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
22 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
23 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
24
25 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
26 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
27 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
28
29 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
30 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
31 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
32
33 ---------------------------------------
34 -- PROCEDURE validate_row --
35 ---------------------------------------
36 PROCEDURE validate_row(
37 p_api_version IN NUMBER,
38 p_init_msg_list IN VARCHAR2 ,
39 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
40
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2,
44
45 p_template_name IN VARCHAR2,
46 p_template_id IN NUMBER,
47 p_working_copy_flag IN VARCHAR2,
48 p_intent IN VARCHAR2,
49 p_status_code IN VARCHAR2,
50 p_start_date IN DATE,
51 p_end_date IN DATE,
52 p_global_flag IN VARCHAR2,
53 p_parent_template_id IN NUMBER,
54 p_print_template_id IN NUMBER,
55 p_contract_expert_enabled IN VARCHAR2,
56 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
57 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
58 p_template_model_id IN NUMBER,
59 p_instruction_text IN VARCHAR2,
60 p_tmpl_numbering_scheme IN NUMBER,
61 p_description IN VARCHAR2,
62 p_approval_wf_key IN VARCHAR2,
63 p_cz_export_wf_key IN VARCHAR2,
64 p_orig_system_reference_code IN VARCHAR2,
65 p_orig_system_reference_id1 IN NUMBER,
66 p_orig_system_reference_id2 IN NUMBER,
67 p_org_id IN NUMBER,
68
69 p_attribute_category IN VARCHAR2 := NULL,
70 p_attribute1 IN VARCHAR2 := NULL,
71 p_attribute2 IN VARCHAR2 := NULL,
72 p_attribute3 IN VARCHAR2 := NULL,
73 p_attribute4 IN VARCHAR2 := NULL,
74 p_attribute5 IN VARCHAR2 := NULL,
75 p_attribute6 IN VARCHAR2 := NULL,
76 p_attribute7 IN VARCHAR2 := NULL,
77 p_attribute8 IN VARCHAR2 := NULL,
78 p_attribute9 IN VARCHAR2 := NULL,
79 p_attribute10 IN VARCHAR2 := NULL,
80 p_attribute11 IN VARCHAR2 := NULL,
81 p_attribute12 IN VARCHAR2 := NULL,
82 p_attribute13 IN VARCHAR2 := NULL,
83 p_attribute14 IN VARCHAR2 := NULL,
84 p_attribute15 IN VARCHAR2 := NULL,
85 p_translated_from_tmpl_id IN NUMBER := NULL,
86 p_language IN VARCHAR2 := NULL,
87
88 p_object_version_number IN NUMBER
89 ) IS
90 l_api_version CONSTANT NUMBER := 1;
91 l_api_name CONSTANT VARCHAR2(30) := 'g_validate_row';
92
93 BEGIN
94
95 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
96 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered validate_row');
97 END IF;
98
99 -- Standard Start of API savepoint
100 SAVEPOINT g_validate_row_GRP;
101 -- Standard call to check for call compatibility.
102 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105 -- Initialize message list if p_init_msg_list is set to TRUE.
106 IF FND_API.to_Boolean( p_init_msg_list ) THEN
107 FND_MSG_PUB.initialize;
108 END IF;
109 -- Initialize API return status to success
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 --------------------------------------------
113 -- Calling Simple API for Validation
114 --------------------------------------------
115 OKC_TERMS_TEMPLATES_PVT.Validate_Row(
116 p_validation_level => p_validation_level,
117 x_return_status => x_return_status,
118 p_template_name => p_template_name,
119 p_template_id => p_template_id,
120 p_working_copy_flag => p_working_copy_flag,
121 p_intent => p_intent,
122 p_status_code => p_status_code,
123 p_start_date => p_start_date,
124 p_end_date => p_end_date,
125 p_global_flag => p_global_flag,
126 p_parent_template_id => p_parent_template_id,
127 p_print_template_id => p_print_template_id,
128 p_contract_expert_enabled => p_contract_expert_enabled,
129 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
130 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
131 p_template_model_id => p_template_model_id,
132 p_instruction_text => p_instruction_text,
133 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
134 p_description => p_description,
135 p_approval_wf_key => p_approval_wf_key,
136 p_cz_export_wf_key => p_cz_export_wf_key,
137 p_orig_system_reference_code => p_orig_system_reference_code,
138 p_orig_system_reference_id1 => p_orig_system_reference_id1,
139 p_orig_system_reference_id2 => p_orig_system_reference_id1,
140 p_org_id => p_org_id,
141 p_attribute_category => p_attribute_category,
142 p_attribute1 => p_attribute1,
143 p_attribute2 => p_attribute2,
144 p_attribute3 => p_attribute3,
145 p_attribute4 => p_attribute4,
146 p_attribute5 => p_attribute5,
147 p_attribute6 => p_attribute6,
148 p_attribute7 => p_attribute7,
149 p_attribute8 => p_attribute8,
150 p_attribute9 => p_attribute9,
151 p_attribute10 => p_attribute10,
152 p_attribute11 => p_attribute11,
153 p_attribute12 => p_attribute12,
154 p_attribute13 => p_attribute13,
155 p_attribute14 => p_attribute14,
156 p_attribute15 => p_attribute15,
157 --MLS for templates
158 p_translated_from_tmpl_id => p_translated_from_tmpl_id,
159 p_language => p_language,
160 p_object_version_number => p_object_version_number
161 );
162 --------------------------------------------
163 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
165 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
166 RAISE FND_API.G_EXC_ERROR ;
167 END IF;
168 --------------------------------------------
169
170 -- Standard call to get message count and if count is 1, get message info.
171 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
172
173 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
174 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Leaving validate_row');
175 END IF;
176
177 EXCEPTION
178 WHEN FND_API.G_EXC_ERROR THEN
179 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
180 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'300: Leaving Validate_Row: OKC_API.G_EXCEPTION_ERROR Exception');
181 END IF;
182 ROLLBACK TO g_validate_row_GRP;
183 x_return_status := G_RET_STS_ERROR ;
184 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
185
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
188 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving Validate_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
189 END IF;
190 ROLLBACK TO g_validate_row_GRP;
191 x_return_status := G_RET_STS_UNEXP_ERROR ;
192 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
193
194 WHEN OTHERS THEN
195 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
196 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm);
197 END IF;
198
199 ROLLBACK TO g_validate_row_GRP;
200 x_return_status := G_RET_STS_UNEXP_ERROR ;
201 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
202 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
203 END IF;
204 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
205
206 END validate_row;
207
208 -------------------------------------
209 -- PROCEDURE create_template
210 -------------------------------------
211 PROCEDURE create_template(
212 p_api_version IN NUMBER,
213 p_init_msg_list IN VARCHAR2 ,
214 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
215 p_commit IN VARCHAR2 := FND_API.G_FALSE,
216
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2,
220
221 p_template_name IN VARCHAR2,
222 p_template_id IN NUMBER,
223 p_working_copy_flag IN VARCHAR2,
224 p_intent IN VARCHAR2,
225 p_status_code IN VARCHAR2,
226 p_start_date IN DATE,
227 p_end_date IN DATE,
228 p_global_flag IN VARCHAR2,
229 p_parent_template_id IN NUMBER,
230 p_print_template_id IN NUMBER,
231 p_contract_expert_enabled IN VARCHAR2,
232 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
233 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
234 p_template_model_id IN NUMBER,
235 p_instruction_text IN VARCHAR2,
236 p_tmpl_numbering_scheme IN NUMBER,
237 p_description IN VARCHAR2,
238 p_approval_wf_key IN VARCHAR2 := NULL,
239 p_cz_export_wf_key IN VARCHAR2 := NULL,
240 p_orig_system_reference_code IN VARCHAR2 := NULL,
241 p_orig_system_reference_id1 IN NUMBER := NULL,
242 p_orig_system_reference_id2 IN NUMBER := NULL,
243 p_org_id IN NUMBER,
244
245 p_attribute_category IN VARCHAR2 := NULL,
246 p_attribute1 IN VARCHAR2 := NULL,
247 p_attribute2 IN VARCHAR2 := NULL,
248 p_attribute3 IN VARCHAR2 := NULL,
249 p_attribute4 IN VARCHAR2 := NULL,
250 p_attribute5 IN VARCHAR2 := NULL,
251 p_attribute6 IN VARCHAR2 := NULL,
252 p_attribute7 IN VARCHAR2 := NULL,
253 p_attribute8 IN VARCHAR2 := NULL,
254 p_attribute9 IN VARCHAR2 := NULL,
255 p_attribute10 IN VARCHAR2 := NULL,
256 p_attribute11 IN VARCHAR2 := NULL,
257 p_attribute12 IN VARCHAR2 := NULL,
258 p_attribute13 IN VARCHAR2 := NULL,
259 p_attribute14 IN VARCHAR2 := NULL,
260 p_attribute15 IN VARCHAR2 := NULL,
261 p_translated_from_tmpl_id IN NUMBER := NULL,
262 p_language IN VARCHAR2 := NULL,
263
264 x_template_id OUT NOCOPY NUMBER
265
266 ) IS
267
268 l_api_version CONSTANT NUMBER := 1;
269 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
270 l_object_version_number OKC_TERMS_TEMPLATES_ALL.OBJECT_VERSION_NUMBER%TYPE := 1;
271 l_created_by OKC_TERMS_TEMPLATES_ALL.CREATED_BY%TYPE;
272 l_creation_date OKC_TERMS_TEMPLATES_ALL.CREATION_DATE%TYPE;
273 l_last_updated_by OKC_TERMS_TEMPLATES_ALL.LAST_UPDATED_BY%TYPE;
274 l_last_update_login OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_LOGIN%TYPE;
275 l_last_update_date OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_DATE%TYPE;
276 l_cz_export_wf_key OKC_TERMS_TEMPLATES_ALL.CZ_EXPORT_WF_KEY%TYPE;
277
278 BEGIN
279
280 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
281 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: Entered create_template');
282 END IF;
283
284 -- Standard Start of API savepoint
285 SAVEPOINT g_insert_row_GRP;
286 -- Standard call to check for call compatibility.
287 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
289 END IF;
290 -- Initialize message list if p_init_msg_list is set to TRUE.
291 IF FND_API.to_Boolean( p_init_msg_list ) THEN
292 FND_MSG_PUB.initialize;
293 END IF;
294 -- Initialize API return status to success
295 x_return_status := FND_API.G_RET_STS_SUCCESS;
296
297 --------------------------------------------
298 -- Calling Simple API for Creating A Row
299 --------------------------------------------
300 OKC_TERMS_TEMPLATES_PVT.Insert_Row(
301 p_validation_level => p_validation_level,
302 x_return_status => x_return_status,
303 p_template_name => p_template_name,
304 p_template_id => p_template_id,
305 p_working_copy_flag => p_working_copy_flag,
306 p_intent => p_intent,
307 p_status_code => p_status_code,
308 p_start_date => p_start_date,
309 p_end_date => p_end_date,
310 p_global_flag => p_global_flag,
311 p_parent_template_id => p_parent_template_id,
312 p_print_template_id => p_print_template_id,
313 p_contract_expert_enabled => p_contract_expert_enabled,
314 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
315 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
316 p_template_model_id => p_template_model_id,
317 p_instruction_text => p_instruction_text,
318 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
319 p_description => p_description,
320 p_approval_wf_key => p_approval_wf_key,
321 p_cz_export_wf_key => p_cz_export_wf_key,
322 p_orig_system_reference_code => p_orig_system_reference_code,
323 p_orig_system_reference_id1 => p_orig_system_reference_id1,
324 p_orig_system_reference_id2 => p_orig_system_reference_id1,
325 p_org_id => p_org_id,
326 p_attribute_category => p_attribute_category,
327 p_attribute1 => p_attribute1,
328 p_attribute2 => p_attribute2,
329 p_attribute3 => p_attribute3,
330 p_attribute4 => p_attribute4,
331 p_attribute5 => p_attribute5,
332 p_attribute6 => p_attribute6,
333 p_attribute7 => p_attribute7,
334 p_attribute8 => p_attribute8,
335 p_attribute9 => p_attribute9,
336 p_attribute10 => p_attribute10,
337 p_attribute11 => p_attribute11,
338 p_attribute12 => p_attribute12,
339 p_attribute13 => p_attribute13,
340 p_attribute14 => p_attribute14,
341 p_attribute15 => p_attribute15,
342 --MLS for templates
343 p_translated_from_tmpl_id => p_translated_from_tmpl_id,
344 p_language => p_language,
345 x_template_id => x_template_id
346 );
347 --------------------------------------------
348 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
350 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
351 RAISE FND_API.G_EXC_ERROR ;
352 END IF;
353 --------------------------------------------
354
355 -- Standard check of p_commit
356 IF FND_API.To_Boolean( p_commit ) THEN
357 COMMIT WORK;
358 END IF;
359 -- Standard call to get message count and if count is 1, get message info.
360 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
361
362 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
363 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Leaving create_template');
364 END IF;
365
366 EXCEPTION
367 WHEN FND_API.G_EXC_ERROR THEN
368 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
369 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving create_template: OKC_API.G_EXCEPTION_ERROR Exception');
370 END IF;
371 ROLLBACK TO g_insert_row_GRP;
372 x_return_status := G_RET_STS_ERROR ;
373 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
374
375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
376 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
377 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving create_template: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
378 END IF;
379 ROLLBACK TO g_insert_row_GRP;
380 x_return_status := G_RET_STS_UNEXP_ERROR ;
381 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
382
383 WHEN OTHERS THEN
384 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
385 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving create_template because of EXCEPTION: '||sqlerrm);
386 END IF;
387
388 ROLLBACK TO g_insert_row_GRP;
389 x_return_status := G_RET_STS_UNEXP_ERROR ;
390 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
391 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
392 END IF;
393 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
394
395 END create_template;
396 ---------------------------------------------------------------------------
397 -- PROCEDURE lock_row
398 ---------------------------------------------------------------------------
399 PROCEDURE lock_row(
400 p_api_version IN NUMBER,
401 p_init_msg_list IN VARCHAR2 ,
402
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_data OUT NOCOPY VARCHAR2,
406
407 p_template_id IN NUMBER,
408 p_object_version_number IN NUMBER
409 ) IS
410 l_api_version CONSTANT NUMBER := 1;
411 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
412 BEGIN
413
414 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
415 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Entered lock_row');
416 END IF;
417
418 -- Standard Start of API savepoint
419 SAVEPOINT g_lock_row_GRP;
420 -- Standard call to check for call compatibility.
421 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
423 END IF;
424 -- Initialize message list if p_init_msg_list is set to TRUE.
425 IF FND_API.to_Boolean( p_init_msg_list ) THEN
426 FND_MSG_PUB.initialize;
427 END IF;
428 -- Initialize API return status to success
429 x_return_status := FND_API.G_RET_STS_SUCCESS;
430
431 --------------------------------------------
432 -- Calling Simple API for Locking A Row
433 --------------------------------------------
434 OKC_TERMS_TEMPLATES_PVT.lock_row(
435 x_return_status => x_return_status,
436 p_template_id => p_template_id,
437 p_object_version_number => p_object_version_number
438 );
439 --------------------------------------------
440 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
442 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
443 RAISE FND_API.G_EXC_ERROR ;
444 END IF;
445 --------------------------------------------
446
447 -- Standard call to get message count and if count is 1, get message info.
448 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
449
450 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
451 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Leaving lock_row');
452 END IF;
453
454 EXCEPTION
455 WHEN FND_API.G_EXC_ERROR THEN
456 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
457 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1300: Leaving lock_Row: OKC_API.G_EXCEPTION_ERROR Exception');
458 END IF;
459 ROLLBACK TO g_lock_row_GRP;
460 x_return_status := G_RET_STS_ERROR ;
461 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
462
463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
464 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
465 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1400: Leaving lock_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
466 END IF;
467 ROLLBACK TO g_lock_row_GRP;
468 x_return_status := G_RET_STS_UNEXP_ERROR ;
469 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
470
471 WHEN OTHERS THEN
472 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
473 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1500: Leaving lock_Row because of EXCEPTION: '||sqlerrm);
474 END IF;
475
476 ROLLBACK TO g_lock_row_GRP;
477 x_return_status := G_RET_STS_UNEXP_ERROR ;
478 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
479 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
480 END IF;
481 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
482
483 END lock_row;
484 ---------------------------------------------------------------------------
485 -- PROCEDURE update_template
486 ---------------------------------------------------------------------------
487 PROCEDURE update_template(
488 p_api_version IN NUMBER,
489 p_init_msg_list IN VARCHAR2 ,
490 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
491 p_commit IN VARCHAR2 := FND_API.G_FALSE,
492
493 x_return_status OUT NOCOPY VARCHAR2,
494 x_msg_count OUT NOCOPY NUMBER,
495 x_msg_data OUT NOCOPY VARCHAR2,
496
497 p_template_name IN VARCHAR2,
498 p_template_id IN NUMBER,
499 p_working_copy_flag IN VARCHAR2,
500 p_intent IN VARCHAR2,
501 p_status_code IN VARCHAR2,
502 p_start_date IN DATE,
503 p_end_date IN DATE,
504 p_global_flag IN VARCHAR2,
505 p_parent_template_id IN NUMBER,
506 p_print_template_id IN NUMBER,
507 p_contract_expert_enabled IN VARCHAR2,
508 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
509 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
510 p_template_model_id IN NUMBER,
511 p_instruction_text IN VARCHAR2,
512 p_tmpl_numbering_scheme IN NUMBER,
513 p_description IN VARCHAR2,
514 p_approval_wf_key IN VARCHAR2 := NULL,
515 p_cz_export_wf_key IN VARCHAR2 := NULL,
516 p_orig_system_reference_code IN VARCHAR2 := NULL,
517 p_orig_system_reference_id1 IN NUMBER := NULL,
518 p_orig_system_reference_id2 IN NUMBER := NULL,
519 p_org_id IN NUMBER,
520
521 p_attribute_category IN VARCHAR2 := NULL,
522 p_attribute1 IN VARCHAR2 := NULL,
523 p_attribute2 IN VARCHAR2 := NULL,
524 p_attribute3 IN VARCHAR2 := NULL,
525 p_attribute4 IN VARCHAR2 := NULL,
526 p_attribute5 IN VARCHAR2 := NULL,
527 p_attribute6 IN VARCHAR2 := NULL,
528 p_attribute7 IN VARCHAR2 := NULL,
529 p_attribute8 IN VARCHAR2 := NULL,
530 p_attribute9 IN VARCHAR2 := NULL,
531 p_attribute10 IN VARCHAR2 := NULL,
532 p_attribute11 IN VARCHAR2 := NULL,
533 p_attribute12 IN VARCHAR2 := NULL,
534 p_attribute13 IN VARCHAR2 := NULL,
535 p_attribute14 IN VARCHAR2 := NULL,
536 p_attribute15 IN VARCHAR2 := NULL,
537 p_translated_from_tmpl_id IN NUMBER := NULL,
538 p_language IN VARCHAR2 := NULL,
539
540 p_object_version_number IN NUMBER
541
542 ) IS
543
544 l_api_version CONSTANT NUMBER := 1;
545 l_api_name CONSTANT VARCHAR2(30) := 'g_update_template';
546 l_cz_export_wf_key OKC_TERMS_TEMPLATES_ALL.CZ_EXPORT_WF_KEY%TYPE;
547
548
549 BEGIN
550
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,'1600: Entered update_template');
553 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1700: Locking row');
554 END IF;
555
556 -- Standard Start of API savepoint
557 SAVEPOINT g_update_row_GRP;
558 -- Standard call to check for call compatibility.
559 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
561 END IF;
562 -- Initialize message list if p_init_msg_list is set to TRUE.
563 IF FND_API.to_Boolean( p_init_msg_list ) THEN
564 FND_MSG_PUB.initialize;
565 END IF;
566 -- Initialize API return status to success
567 x_return_status := FND_API.G_RET_STS_SUCCESS;
568
569 --------------------------------------------
570 -- Calling Simple API for Updating A Row
571 --------------------------------------------
572 OKC_TERMS_TEMPLATES_PVT.Update_Row(
573 p_validation_level => p_validation_level,
574 x_return_status => x_return_status,
575 p_template_name => p_template_name,
576 p_template_id => p_template_id,
577 p_working_copy_flag => p_working_copy_flag,
578 p_intent => p_intent,
579 p_status_code => p_status_code,
580 p_start_date => p_start_date,
581 p_end_date => p_end_date,
582 p_global_flag => p_global_flag,
583 p_parent_template_id => p_parent_template_id,
584 p_print_template_id => p_print_template_id,
585 p_contract_expert_enabled => p_contract_expert_enabled,
586 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
587 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
588 p_template_model_id => p_template_model_id,
589 p_instruction_text => p_instruction_text,
590 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
591 p_description => p_description,
592 p_approval_wf_key => p_approval_wf_key,
593 p_cz_export_wf_key => p_cz_export_wf_key,
594 p_orig_system_reference_code => p_orig_system_reference_code,
595 p_orig_system_reference_id1 => p_orig_system_reference_id1,
596 p_orig_system_reference_id2 => p_orig_system_reference_id1,
597 p_org_id => p_org_id,
598 p_attribute_category => p_attribute_category,
599 p_attribute1 => p_attribute1,
600 p_attribute2 => p_attribute2,
601 p_attribute3 => p_attribute3,
602 p_attribute4 => p_attribute4,
603 p_attribute5 => p_attribute5,
604 p_attribute6 => p_attribute6,
605 p_attribute7 => p_attribute7,
606 p_attribute8 => p_attribute8,
607 p_attribute9 => p_attribute9,
608 p_attribute10 => p_attribute10,
609 p_attribute11 => p_attribute11,
610 p_attribute12 => p_attribute12,
611 p_attribute13 => p_attribute13,
612 p_attribute14 => p_attribute14,
613 p_attribute15 => p_attribute15,
614 --MLS for templates
615 p_translated_from_tmpl_id => p_translated_from_tmpl_id,
616 p_language => p_language,
617 p_object_version_number => p_object_version_number
618 );
619 --------------------------------------------
620 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
622 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
623 RAISE FND_API.G_EXC_ERROR ;
624 END IF;
625 --------------------------------------------
626
627
628 -- Standard check of p_commit
629 IF FND_API.To_Boolean( p_commit ) THEN
630 COMMIT WORK;
631 END IF;
632 -- Standard call to get message count and if count is 1, get message info.
633 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
634
635 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
636 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Leaving update_template');
637 END IF;
638
639 EXCEPTION
640 WHEN FND_API.G_EXC_ERROR THEN
641 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
642 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1900: Leaving update_template: OKC_API.G_EXCEPTION_ERROR Exception');
643 END IF;
644 ROLLBACK TO g_update_row_GRP;
645 x_return_status := G_RET_STS_ERROR ;
646 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
647
648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
649 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
650 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving update_template: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
651 END IF;
652 ROLLBACK TO g_update_row_GRP;
653 x_return_status := G_RET_STS_UNEXP_ERROR ;
654 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
655
656 WHEN OTHERS THEN
657 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
658 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2100: Leaving update_template because of EXCEPTION: '||sqlerrm);
659 END IF;
660
661 ROLLBACK TO g_update_row_GRP;
662 x_return_status := G_RET_STS_UNEXP_ERROR ;
663 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
664 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
665 END IF;
666 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
667
668 END update_template;
669
670 ---------------------------------------------------------------------------
671 -- PROCEDURE delete_template
672 ---------------------------------------------------------------------------
673 PROCEDURE delete_template(
674 p_api_version IN NUMBER,
675 p_init_msg_list IN VARCHAR2 ,
676 p_commit IN VARCHAR2 := FND_API.G_FALSE,
677
678 x_return_status OUT NOCOPY VARCHAR2,
679 x_msg_count OUT NOCOPY NUMBER,
680 x_msg_data OUT NOCOPY VARCHAR2,
681
682 p_template_id IN NUMBER,
683 p_object_version_number IN NUMBER
684 ) IS
685 l_api_version CONSTANT NUMBER := 1;
686 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_template';
687 BEGIN
688
689 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
690 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: Entered delete_template');
691 END IF;
692
693 -- Standard Start of API savepoint
694 SAVEPOINT g_delete_row_GRP;
695 -- Standard call to check for call compatibility.
696 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
698 END IF;
699 -- Initialize message list if p_init_msg_list is set to TRUE.
700 IF FND_API.to_Boolean( p_init_msg_list ) THEN
701 FND_MSG_PUB.initialize;
702 END IF;
703 -- Initialize API return status to success
704 x_return_status := FND_API.G_RET_STS_SUCCESS;
705
706 --------------------------------------------
707 -- Calling Simple API for Deleting A Row
708 --------------------------------------------
709 OKC_TERMS_TEMPLATES_PVT.Delete_Row(
710 x_return_status => x_return_status,
711 p_template_id => p_template_id,
712 p_object_version_number => p_object_version_number
713 );
714 --------------------------------------------
715 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
717 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
718 RAISE FND_API.G_EXC_ERROR ;
719 END IF;
720 --------------------------------------------
721
722 -- Standard check of p_commit
723 IF FND_API.To_Boolean( p_commit ) THEN
724 COMMIT WORK;
725 END IF;
726 -- Standard call to get message count and if count is 1, get message info.
727 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
728
729 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
730 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving delete_template');
731 END IF;
732
733 EXCEPTION
734 WHEN FND_API.G_EXC_ERROR THEN
735 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
736 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving delete_template: OKC_API.G_EXCEPTION_ERROR Exception');
737 END IF;
738 ROLLBACK TO g_delete_row_GRP;
739 x_return_status := G_RET_STS_ERROR ;
740 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
741
742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
743 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
744 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving delete_template: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
745 END IF;
746 ROLLBACK TO g_delete_row_GRP;
747 x_return_status := G_RET_STS_UNEXP_ERROR ;
748 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
749
750 WHEN OTHERS THEN
751 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
752 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2600: Leaving delete_template because of EXCEPTION: '||sqlerrm);
753 END IF;
754
755 ROLLBACK TO g_delete_row_GRP;
756 x_return_status := G_RET_STS_UNEXP_ERROR ;
757 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
758 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
759 END IF;
760 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
761
762 END delete_template;
763
764 END OKC_TERMS_TEMPLATES_GRP;