[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_TEMPLATES_GRP
Source
1 PACKAGE BODY OKC_TERMS_TEMPLATES_GRP AS
2 /* $Header: OKCGTERMTMPLB.pls 120.2.12020000.2 2013/01/31 08:10:17 skavutha 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_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 p_cls_enabled IN VARCHAR2 := 'N',
245
246 p_attribute_category IN VARCHAR2 := NULL,
247 p_attribute1 IN VARCHAR2 := NULL,
248 p_attribute2 IN VARCHAR2 := NULL,
249 p_attribute3 IN VARCHAR2 := NULL,
250 p_attribute4 IN VARCHAR2 := NULL,
251 p_attribute5 IN VARCHAR2 := NULL,
252 p_attribute6 IN VARCHAR2 := NULL,
253 p_attribute7 IN VARCHAR2 := NULL,
254 p_attribute8 IN VARCHAR2 := NULL,
255 p_attribute9 IN VARCHAR2 := NULL,
256 p_attribute10 IN VARCHAR2 := NULL,
257 p_attribute11 IN VARCHAR2 := NULL,
258 p_attribute12 IN VARCHAR2 := NULL,
259 p_attribute13 IN VARCHAR2 := NULL,
260 p_attribute14 IN VARCHAR2 := NULL,
261 p_attribute15 IN VARCHAR2 := NULL,
262 p_translated_from_tmpl_id IN NUMBER := NULL,
263 p_language IN VARCHAR2 := NULL,
264
265 x_template_id OUT NOCOPY NUMBER
266
267 ) IS
268
269 l_api_version CONSTANT NUMBER := 1;
270 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
271 l_object_version_number OKC_TERMS_TEMPLATES_ALL.OBJECT_VERSION_NUMBER%TYPE := 1;
272 l_created_by OKC_TERMS_TEMPLATES_ALL.CREATED_BY%TYPE;
273 l_creation_date OKC_TERMS_TEMPLATES_ALL.CREATION_DATE%TYPE;
274 l_last_updated_by OKC_TERMS_TEMPLATES_ALL.LAST_UPDATED_BY%TYPE;
275 l_last_update_login OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_LOGIN%TYPE;
276 l_last_update_date OKC_TERMS_TEMPLATES_ALL.LAST_UPDATE_DATE%TYPE;
277 l_cz_export_wf_key OKC_TERMS_TEMPLATES_ALL.CZ_EXPORT_WF_KEY%TYPE;
278
279 BEGIN
280
281 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
282 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: Entered create_template');
283 END IF;
284
285 -- Standard Start of API savepoint
286 SAVEPOINT g_insert_row_GRP;
287 -- Standard call to check for call compatibility.
288 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290 END IF;
291 -- Initialize message list if p_init_msg_list is set to TRUE.
292 IF FND_API.to_Boolean( p_init_msg_list ) THEN
293 FND_MSG_PUB.initialize;
294 END IF;
295 -- Initialize API return status to success
296 x_return_status := FND_API.G_RET_STS_SUCCESS;
297
298 --------------------------------------------
299 -- Calling Simple API for Creating A Row
300 --------------------------------------------
301 OKC_TERMS_TEMPLATES_PVT.Insert_Row(
302 p_validation_level => p_validation_level,
303 x_return_status => x_return_status,
304 p_template_name => p_template_name,
305 p_template_id => p_template_id,
306 p_working_copy_flag => p_working_copy_flag,
307 p_intent => p_intent,
308 p_status_code => p_status_code,
309 p_start_date => p_start_date,
310 p_end_date => p_end_date,
311 p_global_flag => p_global_flag,
312 p_parent_template_id => p_parent_template_id,
313 p_print_template_id => p_print_template_id,
314 p_contract_expert_enabled => p_contract_expert_enabled,
315 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+: Contract Expert Changes
316 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+: Contract Expert Changes
317 p_template_model_id => p_template_model_id,
318 p_instruction_text => p_instruction_text,
319 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
320 p_description => p_description,
321 p_approval_wf_key => p_approval_wf_key,
322 p_cz_export_wf_key => p_cz_export_wf_key,
323 p_orig_system_reference_code => p_orig_system_reference_code,
324 p_orig_system_reference_id1 => p_orig_system_reference_id1,
325 p_orig_system_reference_id2 => p_orig_system_reference_id1,
326 p_org_id => p_org_id,
327 p_cls_enabled => p_cls_enabled,
328 p_attribute_category => p_attribute_category,
329 p_attribute1 => p_attribute1,
330 p_attribute2 => p_attribute2,
331 p_attribute3 => p_attribute3,
332 p_attribute4 => p_attribute4,
333 p_attribute5 => p_attribute5,
334 p_attribute6 => p_attribute6,
335 p_attribute7 => p_attribute7,
336 p_attribute8 => p_attribute8,
337 p_attribute9 => p_attribute9,
338 p_attribute10 => p_attribute10,
339 p_attribute11 => p_attribute11,
340 p_attribute12 => p_attribute12,
341 p_attribute13 => p_attribute13,
342 p_attribute14 => p_attribute14,
343 p_attribute15 => p_attribute15,
344 --MLS for templates
345 p_translated_from_tmpl_id => p_translated_from_tmpl_id,
346 p_language => p_language,
347 x_template_id => x_template_id
348 );
349 --------------------------------------------
350 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
352 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
353 RAISE FND_API.G_EXC_ERROR ;
354 END IF;
355 --------------------------------------------
356
357 -- Standard check of p_commit
358 IF FND_API.To_Boolean( p_commit ) THEN
359 COMMIT WORK;
360 END IF;
361 -- Standard call to get message count and if count is 1, get message info.
362 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
363
364 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
365 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Leaving create_template');
366 END IF;
367
368 EXCEPTION
369 WHEN FND_API.G_EXC_ERROR THEN
370 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
371 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'800: Leaving create_template: OKC_API.G_EXCEPTION_ERROR Exception');
372 END IF;
373 ROLLBACK TO g_insert_row_GRP;
374 x_return_status := G_RET_STS_ERROR ;
375 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
376
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
379 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving create_template: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
380 END IF;
381 ROLLBACK TO g_insert_row_GRP;
382 x_return_status := G_RET_STS_UNEXP_ERROR ;
383 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
384
385 WHEN OTHERS THEN
386 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
387 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1000: Leaving create_template because of EXCEPTION: '||sqlerrm);
388 END IF;
389
390 ROLLBACK TO g_insert_row_GRP;
391 x_return_status := G_RET_STS_UNEXP_ERROR ;
392 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
393 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
394 END IF;
395 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
396
397 END create_template;
398 ---------------------------------------------------------------------------
399 -- PROCEDURE lock_row
400 ---------------------------------------------------------------------------
401 PROCEDURE lock_row(
402 p_api_version IN NUMBER,
403 p_init_msg_list IN VARCHAR2 ,
404
405 x_return_status OUT NOCOPY VARCHAR2,
406 x_msg_count OUT NOCOPY NUMBER,
407 x_msg_data OUT NOCOPY VARCHAR2,
408
409 p_template_id IN NUMBER,
410 p_object_version_number IN NUMBER
411 ) IS
412 l_api_version CONSTANT NUMBER := 1;
413 l_api_name CONSTANT VARCHAR2(30) := 'g_lock_row';
414 BEGIN
415
416 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
417 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Entered lock_row');
418 END IF;
419
420 -- Standard Start of API savepoint
421 SAVEPOINT g_lock_row_GRP;
422 -- Standard call to check for call compatibility.
423 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 END IF;
426 -- Initialize message list if p_init_msg_list is set to TRUE.
427 IF FND_API.to_Boolean( p_init_msg_list ) THEN
428 FND_MSG_PUB.initialize;
429 END IF;
430 -- Initialize API return status to success
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 --------------------------------------------
434 -- Calling Simple API for Locking A Row
435 --------------------------------------------
436 OKC_TERMS_TEMPLATES_PVT.lock_row(
437 x_return_status => x_return_status,
438 p_template_id => p_template_id,
439 p_object_version_number => p_object_version_number
440 );
441 --------------------------------------------
442 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
444 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
445 RAISE FND_API.G_EXC_ERROR ;
446 END IF;
447 --------------------------------------------
448
449 -- Standard call to get message count and if count is 1, get message info.
450 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
451
452 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Leaving lock_row');
454 END IF;
455
456 EXCEPTION
457 WHEN FND_API.G_EXC_ERROR THEN
458 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
459 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1300: Leaving lock_Row: OKC_API.G_EXCEPTION_ERROR Exception');
460 END IF;
461 ROLLBACK TO g_lock_row_GRP;
462 x_return_status := G_RET_STS_ERROR ;
463 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
464
465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
466 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
467 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1400: Leaving lock_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
468 END IF;
469 ROLLBACK TO g_lock_row_GRP;
470 x_return_status := G_RET_STS_UNEXP_ERROR ;
471 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
472
473 WHEN OTHERS THEN
474 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
475 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1500: Leaving lock_Row because of EXCEPTION: '||sqlerrm);
476 END IF;
477
478 ROLLBACK TO g_lock_row_GRP;
479 x_return_status := G_RET_STS_UNEXP_ERROR ;
480 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
481 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
482 END IF;
483 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
484
485 END lock_row;
486 ---------------------------------------------------------------------------
487 -- PROCEDURE update_template
488 ---------------------------------------------------------------------------
489 PROCEDURE update_template(
490 p_api_version IN NUMBER,
491 p_init_msg_list IN VARCHAR2 ,
492 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
493 p_commit IN VARCHAR2 := FND_API.G_FALSE,
494
495 x_return_status OUT NOCOPY VARCHAR2,
496 x_msg_count OUT NOCOPY NUMBER,
497 x_msg_data OUT NOCOPY VARCHAR2,
498
499 p_template_name IN VARCHAR2,
500 p_template_id IN NUMBER,
501 p_working_copy_flag IN VARCHAR2,
502 p_intent IN VARCHAR2,
503 p_status_code IN VARCHAR2,
504 p_start_date IN DATE,
505 p_end_date IN DATE,
506 p_global_flag IN VARCHAR2,
507 p_parent_template_id IN NUMBER,
508 p_print_template_id IN NUMBER,
509 p_contract_expert_enabled IN VARCHAR2,
510 p_xprt_clause_mandatory_flag IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
511 p_xprt_scn_code IN VARCHAR2, -- Added for 11.5.10+: Contract Expert Changes
512 p_template_model_id IN NUMBER,
513 p_instruction_text IN VARCHAR2,
514 p_tmpl_numbering_scheme IN NUMBER,
515 p_description IN VARCHAR2,
516 p_approval_wf_key IN VARCHAR2 := NULL,
517 p_cz_export_wf_key IN VARCHAR2 := NULL,
518 p_orig_system_reference_code IN VARCHAR2 := NULL,
519 p_orig_system_reference_id1 IN NUMBER := NULL,
520 p_orig_system_reference_id2 IN NUMBER := NULL,
521 p_org_id IN NUMBER,
522 p_cls_enabled IN VARCHAR2 := 'N',
523
524 p_attribute_category IN VARCHAR2 := NULL,
525 p_attribute1 IN VARCHAR2 := NULL,
526 p_attribute2 IN VARCHAR2 := NULL,
527 p_attribute3 IN VARCHAR2 := NULL,
528 p_attribute4 IN VARCHAR2 := NULL,
529 p_attribute5 IN VARCHAR2 := NULL,
530 p_attribute6 IN VARCHAR2 := NULL,
531 p_attribute7 IN VARCHAR2 := NULL,
532 p_attribute8 IN VARCHAR2 := NULL,
533 p_attribute9 IN VARCHAR2 := NULL,
534 p_attribute10 IN VARCHAR2 := NULL,
535 p_attribute11 IN VARCHAR2 := NULL,
536 p_attribute12 IN VARCHAR2 := NULL,
537 p_attribute13 IN VARCHAR2 := NULL,
538 p_attribute14 IN VARCHAR2 := NULL,
539 p_attribute15 IN VARCHAR2 := NULL,
540 p_translated_from_tmpl_id IN NUMBER := NULL,
541 p_language IN VARCHAR2 := NULL,
542
543 p_object_version_number IN NUMBER
544
545 ) IS
546
547 l_api_version CONSTANT NUMBER := 1;
548 l_api_name CONSTANT VARCHAR2(30) := 'g_update_template';
549 l_cz_export_wf_key OKC_TERMS_TEMPLATES_ALL.CZ_EXPORT_WF_KEY%TYPE;
550
551
552 BEGIN
553
554 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
555 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Entered update_template');
556 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1700: Locking row');
557 END IF;
558
559 -- Standard Start of API savepoint
560 SAVEPOINT g_update_row_GRP;
561 -- Standard call to check for call compatibility.
562 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
564 END IF;
565 -- Initialize message list if p_init_msg_list is set to TRUE.
566 IF FND_API.to_Boolean( p_init_msg_list ) THEN
567 FND_MSG_PUB.initialize;
568 END IF;
569 -- Initialize API return status to success
570 x_return_status := FND_API.G_RET_STS_SUCCESS;
571
572 --------------------------------------------
573 -- Calling Simple API for Updating A Row
574 --------------------------------------------
575 OKC_TERMS_TEMPLATES_PVT.Update_Row(
576 p_validation_level => p_validation_level,
577 x_return_status => x_return_status,
578 p_template_name => p_template_name,
579 p_template_id => p_template_id,
580 p_working_copy_flag => p_working_copy_flag,
581 p_intent => p_intent,
582 p_status_code => p_status_code,
583 p_start_date => p_start_date,
584 p_end_date => p_end_date,
585 p_global_flag => p_global_flag,
586 p_parent_template_id => p_parent_template_id,
587 p_print_template_id => p_print_template_id,
588 p_contract_expert_enabled => p_contract_expert_enabled,
589 p_xprt_clause_mandatory_flag => p_xprt_clause_mandatory_flag, -- Added for 11.5.10+ : Contract Expert Changes
590 p_xprt_scn_code => p_xprt_scn_code, -- Added for 11.5.10+ : Contract Expert Changes
591 p_template_model_id => p_template_model_id,
592 p_instruction_text => p_instruction_text,
593 p_tmpl_numbering_scheme => p_tmpl_numbering_scheme,
594 p_description => p_description,
595 p_approval_wf_key => p_approval_wf_key,
596 p_cz_export_wf_key => p_cz_export_wf_key,
597 p_orig_system_reference_code => p_orig_system_reference_code,
598 p_orig_system_reference_id1 => p_orig_system_reference_id1,
599 p_orig_system_reference_id2 => p_orig_system_reference_id1,
600 p_org_id => p_org_id,
601 p_cls_enabled => p_cls_enabled,
602 p_attribute_category => p_attribute_category,
603 p_attribute1 => p_attribute1,
604 p_attribute2 => p_attribute2,
605 p_attribute3 => p_attribute3,
606 p_attribute4 => p_attribute4,
607 p_attribute5 => p_attribute5,
608 p_attribute6 => p_attribute6,
609 p_attribute7 => p_attribute7,
610 p_attribute8 => p_attribute8,
611 p_attribute9 => p_attribute9,
612 p_attribute10 => p_attribute10,
613 p_attribute11 => p_attribute11,
614 p_attribute12 => p_attribute12,
615 p_attribute13 => p_attribute13,
616 p_attribute14 => p_attribute14,
617 p_attribute15 => p_attribute15,
618 --MLS for templates
619 p_translated_from_tmpl_id => p_translated_from_tmpl_id,
620 p_language => p_language,
621 p_object_version_number => p_object_version_number
622 );
623 --------------------------------------------
624 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
626 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
627 RAISE FND_API.G_EXC_ERROR ;
628 END IF;
629 --------------------------------------------
630
631
632 -- Standard check of p_commit
633 IF FND_API.To_Boolean( p_commit ) THEN
634 COMMIT WORK;
635 END IF;
636 -- Standard call to get message count and if count is 1, get message info.
637 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
638
639 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
640 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Leaving update_template');
641 END IF;
642
643 EXCEPTION
644 WHEN FND_API.G_EXC_ERROR THEN
645 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
646 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1900: Leaving update_template: OKC_API.G_EXCEPTION_ERROR Exception');
647 END IF;
648 ROLLBACK TO g_update_row_GRP;
649 x_return_status := G_RET_STS_ERROR ;
650 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
651
652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
653 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
654 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving update_template: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
655 END IF;
656 ROLLBACK TO g_update_row_GRP;
657 x_return_status := G_RET_STS_UNEXP_ERROR ;
658 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
659
660 WHEN OTHERS THEN
661 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
662 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2100: Leaving update_template because of EXCEPTION: '||sqlerrm);
663 END IF;
664
665 ROLLBACK TO g_update_row_GRP;
666 x_return_status := G_RET_STS_UNEXP_ERROR ;
667 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
668 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
669 END IF;
670 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
671
672 END update_template;
673
674 ---------------------------------------------------------------------------
675 -- PROCEDURE delete_template
676 ---------------------------------------------------------------------------
677 PROCEDURE delete_template(
678 p_api_version IN NUMBER,
679 p_init_msg_list IN VARCHAR2 ,
680 p_commit IN VARCHAR2 := FND_API.G_FALSE,
681
682 x_return_status OUT NOCOPY VARCHAR2,
683 x_msg_count OUT NOCOPY NUMBER,
684 x_msg_data OUT NOCOPY VARCHAR2,
685
686 p_template_id IN NUMBER,
687 p_object_version_number IN NUMBER
688 ) IS
689 l_api_version CONSTANT NUMBER := 1;
690 l_api_name CONSTANT VARCHAR2(30) := 'g_delete_template';
691 BEGIN
692
693 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
694 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: Entered delete_template');
695 END IF;
696
697 -- Standard Start of API savepoint
698 SAVEPOINT g_delete_row_GRP;
699 -- Standard call to check for call compatibility.
700 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END IF;
703 -- Initialize message list if p_init_msg_list is set to TRUE.
704 IF FND_API.to_Boolean( p_init_msg_list ) THEN
705 FND_MSG_PUB.initialize;
706 END IF;
707 -- Initialize API return status to success
708 x_return_status := FND_API.G_RET_STS_SUCCESS;
709
710 --------------------------------------------
711 -- Calling Simple API for Deleting A Row
712 --------------------------------------------
713 OKC_TERMS_TEMPLATES_PVT.Delete_Row(
714 x_return_status => x_return_status,
715 p_template_id => p_template_id,
716 p_object_version_number => p_object_version_number
717 );
718 --------------------------------------------
719 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
721 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
722 RAISE FND_API.G_EXC_ERROR ;
723 END IF;
724 --------------------------------------------
725
726 -- Standard check of p_commit
727 IF FND_API.To_Boolean( p_commit ) THEN
728 COMMIT WORK;
729 END IF;
730 -- Standard call to get message count and if count is 1, get message info.
731 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
732
733 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
734 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving delete_template');
735 END IF;
736
737 EXCEPTION
738 WHEN FND_API.G_EXC_ERROR THEN
739 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
740 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving delete_template: OKC_API.G_EXCEPTION_ERROR Exception');
741 END IF;
742 ROLLBACK TO g_delete_row_GRP;
743 x_return_status := G_RET_STS_ERROR ;
744 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
745
746 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
747 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
748 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving delete_template: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
749 END IF;
750 ROLLBACK TO g_delete_row_GRP;
751 x_return_status := G_RET_STS_UNEXP_ERROR ;
752 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
753
754 WHEN OTHERS THEN
755 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
756 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2600: Leaving delete_template because of EXCEPTION: '||sqlerrm);
757 END IF;
758
759 ROLLBACK TO g_delete_row_GRP;
760 x_return_status := G_RET_STS_UNEXP_ERROR ;
761 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
762 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
763 END IF;
764 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
765
766 END delete_template;
767
768 END OKC_TERMS_TEMPLATES_GRP;