[Home] [Help]
PACKAGE BODY: APPS.OKC_ALLOWED_TMPL_USAGES_PVT
Source
1 PACKAGE BODY OKC_ALLOWED_TMPL_USAGES_PVT AS
2 /* $Header: OKCVALDTMPLUSGB.pls 120.0 2005/05/25 18:48:22 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
11 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
12 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
13 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
15 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
16 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
17 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
18 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
19 ---------------------------------------------------------------------------
20 -- VALIDATION LEVELS
21 ---------------------------------------------------------------------------
22 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
23 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
24 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
25 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
26 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
27 ---------------------------------------------------------------------------
28 -- GLOBAL VARIABLES
29 ---------------------------------------------------------------------------
30 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ALDTMPLUSG_PVT';
31 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
32
33 ------------------------------------------------------------------------------
34 -- GLOBAL CONSTANTS
35 ------------------------------------------------------------------------------
36 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
37 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
38 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
39 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
40 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
41
42 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
43 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
44 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
45
46 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
47 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
48 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
49
50 ------------------------------------------------------------------------------
51 -- GLOBAL EXCEPTION
52 ------------------------------------------------------------------------------
53 E_Resource_Busy EXCEPTION;
54 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
55 ---------------------------------------------------------------------------
56 -- FUNCTION get_seq_id
57 ---------------------------------------------------------------------------
58 FUNCTION Get_Seq_Id (
59 p_allowed_tmpl_usages_id IN NUMBER,
60 x_allowed_tmpl_usages_id OUT NOCOPY NUMBER
61 ) RETURN VARCHAR2 IS
62 CURSOR l_seq_csr IS
63 SELECT OKC_ALLOWED_TMPL_USAGES_S.NEXTVAL FROM DUAL;
64 BEGIN
65 IF (l_debug = 'Y') THEN
66 Okc_Debug.Log('100: Entered get_seq_id', 2);
67 END IF;
68
69 IF( p_allowed_tmpl_usages_id IS NULL ) THEN
70 OPEN l_seq_csr;
71 FETCH l_seq_csr INTO x_allowed_tmpl_usages_id;
72 IF l_seq_csr%NOTFOUND THEN
73 RAISE NO_DATA_FOUND;
74 END IF;
75 CLOSE l_seq_csr;
76 END IF;
77
78 IF (l_debug = 'Y') THEN
79 Okc_Debug.Log('200: Leaving get_seq_id', 2);
80 END IF;
81 RETURN G_RET_STS_SUCCESS;
82 EXCEPTION
83 WHEN OTHERS THEN
84
85 IF (l_debug = 'Y') THEN
86 Okc_Debug.Log('300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm, 2);
87 END IF;
88
89 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
90 p_msg_name => G_UNEXPECTED_ERROR,
91 p_token1 => G_SQLCODE_TOKEN,
92 p_token1_value => sqlcode,
93 p_token2 => G_SQLERRM_TOKEN,
94 p_token2_value => sqlerrm);
95
96 IF l_seq_csr%ISOPEN THEN
97 CLOSE l_seq_csr;
98 END IF;
99
100 RETURN G_RET_STS_UNEXP_ERROR ;
101
102 END Get_Seq_Id;
103
104 ---------------------------------------------------------------------------
105 -- FUNCTION get_rec for: OKC_ALLOWED_TMPL_USAGES
106 ---------------------------------------------------------------------------
107 FUNCTION Get_Rec (
108 p_allowed_tmpl_usages_id IN NUMBER,
109
110 x_template_id OUT NOCOPY NUMBER,
111 x_document_type OUT NOCOPY VARCHAR2,
112 x_default_yn OUT NOCOPY VARCHAR2,
113 x_attribute_category OUT NOCOPY VARCHAR2,
114 x_attribute1 OUT NOCOPY VARCHAR2,
115 x_attribute2 OUT NOCOPY VARCHAR2,
116 x_attribute3 OUT NOCOPY VARCHAR2,
117 x_attribute4 OUT NOCOPY VARCHAR2,
118 x_attribute5 OUT NOCOPY VARCHAR2,
119 x_attribute6 OUT NOCOPY VARCHAR2,
120 x_attribute7 OUT NOCOPY VARCHAR2,
121 x_attribute8 OUT NOCOPY VARCHAR2,
122 x_attribute9 OUT NOCOPY VARCHAR2,
123 x_attribute10 OUT NOCOPY VARCHAR2,
124 x_attribute11 OUT NOCOPY VARCHAR2,
125 x_attribute12 OUT NOCOPY VARCHAR2,
126 x_attribute13 OUT NOCOPY VARCHAR2,
127 x_attribute14 OUT NOCOPY VARCHAR2,
128 x_attribute15 OUT NOCOPY VARCHAR2,
129 x_object_version_number OUT NOCOPY NUMBER,
130 x_created_by OUT NOCOPY NUMBER,
131 x_creation_date OUT NOCOPY DATE,
132 x_last_updated_by OUT NOCOPY NUMBER,
133 x_last_update_login OUT NOCOPY NUMBER,
134 x_last_update_date OUT NOCOPY DATE
135
136 ) RETURN VARCHAR2 IS
137 CURSOR OKC_ALLOWED_TMPL_USAGES_pk_csr (cp_allowed_tmpl_usages_id IN NUMBER) IS
138 SELECT
139 TEMPLATE_ID,
140 DOCUMENT_TYPE,
141 DEFAULT_YN,
142 ATTRIBUTE_CATEGORY,
143 ATTRIBUTE1,
144 ATTRIBUTE2,
145 ATTRIBUTE3,
146 ATTRIBUTE4,
147 ATTRIBUTE5,
148 ATTRIBUTE6,
149 ATTRIBUTE7,
150 ATTRIBUTE8,
151 ATTRIBUTE9,
152 ATTRIBUTE10,
153 ATTRIBUTE11,
154 ATTRIBUTE12,
155 ATTRIBUTE13,
156 ATTRIBUTE14,
157 ATTRIBUTE15,
158 OBJECT_VERSION_NUMBER,
159 CREATED_BY,
160 CREATION_DATE,
161 LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN,
163 LAST_UPDATE_DATE
164 FROM OKC_ALLOWED_TMPL_USAGES t
165 WHERE t.ALLOWED_TMPL_USAGES_ID = cp_allowed_tmpl_usages_id;
166 BEGIN
167
168 IF (l_debug = 'Y') THEN
169 Okc_Debug.Log('400: Entered get_rec', 2);
170 END IF;
171
172 -- Get current database values
173 OPEN OKC_ALLOWED_TMPL_USAGES_pk_csr (p_allowed_tmpl_usages_id);
174 FETCH OKC_ALLOWED_TMPL_USAGES_pk_csr INTO
175 x_template_id,
176 x_document_type,
177 x_default_yn,
178 x_attribute_category,
179 x_attribute1,
180 x_attribute2,
181 x_attribute3,
182 x_attribute4,
183 x_attribute5,
184 x_attribute6,
185 x_attribute7,
186 x_attribute8,
187 x_attribute9,
188 x_attribute10,
189 x_attribute11,
190 x_attribute12,
191 x_attribute13,
192 x_attribute14,
193 x_attribute15,
194 x_object_version_number,
195 x_created_by,
196 x_creation_date,
197 x_last_updated_by,
198 x_last_update_login,
199 x_last_update_date;
200 IF OKC_ALLOWED_TMPL_USAGES_pk_csr%NOTFOUND THEN
201 RAISE NO_DATA_FOUND;
202 END IF;
203 CLOSE OKC_ALLOWED_TMPL_USAGES_pk_csr;
204
205 IF (l_debug = 'Y') THEN
206 Okc_Debug.Log('500: Leaving get_rec ', 2);
207 END IF;
208
209 RETURN G_RET_STS_SUCCESS ;
210
211 EXCEPTION
212 WHEN OTHERS THEN
213
214 IF (l_debug = 'Y') THEN
215 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
216 END IF;
217
218 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
219 p_msg_name => G_UNEXPECTED_ERROR,
220 p_token1 => G_SQLCODE_TOKEN,
221 p_token1_value => sqlcode,
222 p_token2 => G_SQLERRM_TOKEN,
223 p_token2_value => sqlerrm);
224
225 IF OKC_ALLOWED_TMPL_USAGES_pk_csr%ISOPEN THEN
226 CLOSE OKC_ALLOWED_TMPL_USAGES_pk_csr;
227 END IF;
228
229 RETURN G_RET_STS_UNEXP_ERROR ;
230
231 END Get_Rec;
232
233 -----------------------------------------
234 -- Set_Attributes for:OKC_ALLOWED_TMPL_USAGES --
235 -----------------------------------------
236 FUNCTION Set_Attributes(
237 p_template_id IN NUMBER,
238 p_document_type IN VARCHAR2,
239 p_default_yn IN VARCHAR2,
240 p_allowed_tmpl_usages_id IN NUMBER,
241 p_attribute_category IN VARCHAR2,
242 p_attribute1 IN VARCHAR2,
243 p_attribute2 IN VARCHAR2,
244 p_attribute3 IN VARCHAR2,
245 p_attribute4 IN VARCHAR2,
246 p_attribute5 IN VARCHAR2,
247 p_attribute6 IN VARCHAR2,
248 p_attribute7 IN VARCHAR2,
249 p_attribute8 IN VARCHAR2,
250 p_attribute9 IN VARCHAR2,
251 p_attribute10 IN VARCHAR2,
252 p_attribute11 IN VARCHAR2,
253 p_attribute12 IN VARCHAR2,
254 p_attribute13 IN VARCHAR2,
255 p_attribute14 IN VARCHAR2,
256 p_attribute15 IN VARCHAR2,
257 p_object_version_number IN NUMBER,
258
259 x_template_id OUT NOCOPY NUMBER,
260 x_document_type OUT NOCOPY VARCHAR2,
261 x_default_yn OUT NOCOPY VARCHAR2,
262 x_attribute_category OUT NOCOPY VARCHAR2,
263 x_attribute1 OUT NOCOPY VARCHAR2,
264 x_attribute2 OUT NOCOPY VARCHAR2,
265 x_attribute3 OUT NOCOPY VARCHAR2,
266 x_attribute4 OUT NOCOPY VARCHAR2,
267 x_attribute5 OUT NOCOPY VARCHAR2,
268 x_attribute6 OUT NOCOPY VARCHAR2,
269 x_attribute7 OUT NOCOPY VARCHAR2,
270 x_attribute8 OUT NOCOPY VARCHAR2,
271 x_attribute9 OUT NOCOPY VARCHAR2,
272 x_attribute10 OUT NOCOPY VARCHAR2,
273 x_attribute11 OUT NOCOPY VARCHAR2,
274 x_attribute12 OUT NOCOPY VARCHAR2,
275 x_attribute13 OUT NOCOPY VARCHAR2,
276 x_attribute14 OUT NOCOPY VARCHAR2,
277 x_attribute15 OUT NOCOPY VARCHAR2
278 ) RETURN VARCHAR2 IS
279 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
280 l_object_version_number OKC_ALLOWED_TMPL_USAGES.OBJECT_VERSION_NUMBER%TYPE;
281 l_created_by OKC_ALLOWED_TMPL_USAGES.CREATED_BY%TYPE;
282 l_creation_date OKC_ALLOWED_TMPL_USAGES.CREATION_DATE%TYPE;
283 l_last_updated_by OKC_ALLOWED_TMPL_USAGES.LAST_UPDATED_BY%TYPE;
284 l_last_update_login OKC_ALLOWED_TMPL_USAGES.LAST_UPDATE_LOGIN%TYPE;
285 l_last_update_date OKC_ALLOWED_TMPL_USAGES.LAST_UPDATE_DATE%TYPE;
286 BEGIN
287 IF (l_debug = 'Y') THEN
288 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
289 END IF;
290
291 IF( p_allowed_tmpl_usages_id IS NOT NULL ) THEN
292 -- Get current database values
293 l_return_status := Get_Rec(
294 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
295 x_template_id => x_template_id,
296 x_document_type => x_document_type,
297 x_default_yn => x_default_yn,
298 x_attribute_category => x_attribute_category,
299 x_attribute1 => x_attribute1,
300 x_attribute2 => x_attribute2,
301 x_attribute3 => x_attribute3,
302 x_attribute4 => x_attribute4,
303 x_attribute5 => x_attribute5,
304 x_attribute6 => x_attribute6,
305 x_attribute7 => x_attribute7,
306 x_attribute8 => x_attribute8,
307 x_attribute9 => x_attribute9,
308 x_attribute10 => x_attribute10,
309 x_attribute11 => x_attribute11,
310 x_attribute12 => x_attribute12,
311 x_attribute13 => x_attribute13,
312 x_attribute14 => x_attribute14,
313 x_attribute15 => x_attribute15,
314 x_object_version_number => l_object_version_number,
315 x_created_by => l_created_by,
316 x_creation_date => l_creation_date,
317 x_last_updated_by => l_last_updated_by,
318 x_last_update_login => l_last_update_login,
319 x_last_update_date => l_last_update_date
320 );
321 --- If any errors happen abort API
322 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
325 RAISE FND_API.G_EXC_ERROR;
326 END IF;
327
328 --- Reversing G_MISS/NULL values logic
329
330 IF (p_template_id = G_MISS_NUM) THEN
331 x_template_id := NULL;
332 ELSIF (p_template_id IS NOT NULL) THEN
333 x_template_id := p_template_id;
334 END IF;
335
336 IF (p_document_type = G_MISS_CHAR) THEN
337 x_document_type := NULL;
338 ELSIF (p_document_type IS NOT NULL) THEN
339 x_document_type := p_document_type;
340 END IF;
341
342 IF (p_default_yn = G_MISS_CHAR) THEN
343 x_default_yn := NULL;
344 ELSIF (p_default_yn IS NOT NULL) THEN
345 x_default_yn := Upper( p_default_yn );
346 END IF;
347
348 IF (p_attribute_category = G_MISS_CHAR) THEN
349 x_attribute_category := NULL;
350 ELSIF (p_attribute_category IS NOT NULL) THEN
351 x_attribute_category := p_attribute_category;
352 END IF;
353
354 IF (p_attribute1 = G_MISS_CHAR) THEN
355 x_attribute1 := NULL;
356 ELSIF (p_attribute1 IS NOT NULL) THEN
357 x_attribute1 := p_attribute1;
358 END IF;
359
360 IF (p_attribute2 = G_MISS_CHAR) THEN
361 x_attribute2 := NULL;
362 ELSIF (p_attribute2 IS NOT NULL) THEN
363 x_attribute2 := p_attribute2;
364 END IF;
365
366 IF (p_attribute3 = G_MISS_CHAR) THEN
367 x_attribute3 := NULL;
371
368 ELSIF (p_attribute3 IS NOT NULL) THEN
369 x_attribute3 := p_attribute3;
370 END IF;
372 IF (p_attribute4 = G_MISS_CHAR) THEN
373 x_attribute4 := NULL;
374 ELSIF (p_attribute4 IS NOT NULL) THEN
375 x_attribute4 := p_attribute4;
376 END IF;
377
378 IF (p_attribute5 = G_MISS_CHAR) THEN
379 x_attribute5 := NULL;
380 ELSIF (p_attribute5 IS NOT NULL) THEN
381 x_attribute5 := p_attribute5;
382 END IF;
383
384 IF (p_attribute6 = G_MISS_CHAR) THEN
385 x_attribute6 := NULL;
386 ELSIF (p_attribute6 IS NOT NULL) THEN
387 x_attribute6 := p_attribute6;
388 END IF;
389
390 IF (p_attribute7 = G_MISS_CHAR) THEN
391 x_attribute7 := NULL;
392 ELSIF (p_attribute7 IS NOT NULL) THEN
393 x_attribute7 := p_attribute7;
394 END IF;
395
396 IF (p_attribute8 = G_MISS_CHAR) THEN
397 x_attribute8 := NULL;
398 ELSIF (p_attribute8 IS NOT NULL) THEN
399 x_attribute8 := p_attribute8;
400 END IF;
401
402 IF (p_attribute9 = G_MISS_CHAR) THEN
403 x_attribute9 := NULL;
404 ELSIF (p_attribute9 IS NOT NULL) THEN
405 x_attribute9 := p_attribute9;
406 END IF;
407
408 IF (p_attribute10 = G_MISS_CHAR) THEN
409 x_attribute10 := NULL;
410 ELSIF (p_attribute10 IS NOT NULL) THEN
411 x_attribute10 := p_attribute10;
412 END IF;
413
414 IF (p_attribute11 = G_MISS_CHAR) THEN
415 x_attribute11 := NULL;
416 ELSIF (p_attribute11 IS NOT NULL) THEN
417 x_attribute11 := p_attribute11;
418 END IF;
419
420 IF (p_attribute12 = G_MISS_CHAR) THEN
421 x_attribute12 := NULL;
422 ELSIF (p_attribute12 IS NOT NULL) THEN
423 x_attribute12 := p_attribute12;
424 END IF;
425
426 IF (p_attribute13 = G_MISS_CHAR) THEN
427 x_attribute13 := NULL;
428 ELSIF (p_attribute13 IS NOT NULL) THEN
429 x_attribute13 := p_attribute13;
430 END IF;
431
432 IF (p_attribute14 = G_MISS_CHAR) THEN
433 x_attribute14 := NULL;
434 ELSIF (p_attribute14 IS NOT NULL) THEN
435 x_attribute14 := p_attribute14;
436 END IF;
437
438 IF (p_attribute15 = G_MISS_CHAR) THEN
439 x_attribute15 := NULL;
440 ELSIF (p_attribute15 IS NOT NULL) THEN
441 x_attribute15 := p_attribute15;
442 END IF;
443
444
445 END IF;
446
447 IF (l_debug = 'Y') THEN
448 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
449 END IF;
450
451 RETURN G_RET_STS_SUCCESS ;
452 EXCEPTION
453 WHEN FND_API.G_EXC_ERROR THEN
454 IF (l_debug = 'Y') THEN
455 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
456 END IF;
457 RETURN G_RET_STS_ERROR;
458
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
460 IF (l_debug = 'Y') THEN
461 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
462 END IF;
463 RETURN G_RET_STS_UNEXP_ERROR;
464
465 WHEN OTHERS THEN
466 IF (l_debug = 'Y') THEN
467 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
468 END IF;
469 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
470 p_msg_name => G_UNEXPECTED_ERROR,
471 p_token1 => G_SQLCODE_TOKEN,
472 p_token1_value => sqlcode,
473 p_token2 => G_SQLERRM_TOKEN,
474 p_token2_value => sqlerrm);
475 RETURN G_RET_STS_UNEXP_ERROR;
476
477 END Set_Attributes ;
478
479 ----------------------------------------------
480 -- Validate_Attributes for: OKC_ALLOWED_TMPL_USAGES --
481 ----------------------------------------------
482 FUNCTION Validate_Attributes (
483 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
484
485 p_template_id IN NUMBER,
486 p_document_type IN VARCHAR2,
487 p_default_yn IN VARCHAR2,
488 p_allowed_tmpl_usages_id IN NUMBER,
489 p_attribute_category IN VARCHAR2,
490 p_attribute1 IN VARCHAR2,
491 p_attribute2 IN VARCHAR2,
492 p_attribute3 IN VARCHAR2,
493 p_attribute4 IN VARCHAR2,
494 p_attribute5 IN VARCHAR2,
495 p_attribute6 IN VARCHAR2,
496 p_attribute7 IN VARCHAR2,
497 p_attribute8 IN VARCHAR2,
498 p_attribute9 IN VARCHAR2,
499 p_attribute10 IN VARCHAR2,
500 p_attribute11 IN VARCHAR2,
501 p_attribute12 IN VARCHAR2,
502 p_attribute13 IN VARCHAR2,
503 p_attribute14 IN VARCHAR2,
504 p_attribute15 IN VARCHAR2
505 ) RETURN VARCHAR2 IS
506 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
507 l_dummy_var VARCHAR2(1) := '?';
511
508 l_template_intent OKC_TERMS_TEMPLATES_ALL.INTENT%TYPE;
509 l_doc_intent OKC_BUS_DOC_TYPES_B.INTENT%TYPE;
510 l_doc_type_name OKC_BUS_DOC_TYPES_TL.NAME%TYPE;
512 CURSOR l_template_id_csr is
513 SELECT '!'
514 FROM okc_terms_templates_all
515 WHERE TEMPLATE_ID = p_template_id;
516
517 CURSOR l_document_type_csr is
518 SELECT '!'
519 FROM okc_bus_doc_types_b
520 WHERE DOCUMENT_TYPE = p_document_type;
521
522 CURSOR l_template_intent_csr IS
523 SELECT intent
524 FROM okc_terms_templates_all
525 WHERE template_id = p_template_id;
526
527 CURSOR l_doc_intent_csr IS
528 SELECT intent
529 FROM OKC_BUS_DOC_TYPES_B
530 WHERE document_type = p_document_type;
531
532 CURSOR l_doc_name_csr IS
533 SELECT name
534 FROM okc_bus_doc_types_vl
535 WHERE document_type = p_document_type;
536
537 BEGIN
538
539 IF (l_debug = 'Y') THEN
540 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
541 END IF;
542
543 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
544 IF (l_debug = 'Y') THEN
545 Okc_Debug.Log('1300: required values validation', 2);
546 END IF;
547
548 IF ( p_document_type IS NULL) THEN
549 IF (l_debug = 'Y') THEN
550 Okc_Debug.Log('1210: - attribute DOCUMENT_TYPE is null', 2);
551 END IF;
552 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'DOCUMENT_TYPE');
553 l_return_status := G_RET_STS_ERROR;
554 END IF;
555
556 END IF;
557
558 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
559 IF (l_debug = 'Y') THEN
560 Okc_Debug.Log('1600: static values and range validation', 2);
561 END IF;
562
563 IF (l_debug = 'Y') THEN
564 Okc_Debug.Log('1700: - attribute DEFAULT_YN ', 2);
565 END IF;
566 IF ( p_default_yn NOT IN ('Y','N') AND p_default_yn IS NOT NULL) THEN
567 IF (l_debug = 'Y') THEN
568 Okc_Debug.Log('1800: - attribute DEFAULT_YN is invalid', 2);
569 END IF;
570 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'DEFAULT_YN');
571 l_return_status := G_RET_STS_ERROR;
572 END IF;
573
574 END IF;
575
576 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
577 IF (l_debug = 'Y') THEN
578 Okc_Debug.Log('1900: lookup codes validation', 2);
579 END IF;
580 END IF;
581
582 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
583 IF (l_debug = 'Y') THEN
584 Okc_Debug.Log('2100: foreigh keys validation ', 2);
585 END IF;
586
587 IF (l_debug = 'Y') THEN
588 Okc_Debug.Log('2200: - attribute TEMPLATE_ID ', 2);
589 END IF;
590 IF p_template_id IS NOT NULL THEN
591 l_dummy_var := '?';
592 OPEN l_template_id_csr;
593 FETCH l_template_id_csr INTO l_dummy_var;
594 CLOSE l_template_id_csr;
595 IF (l_dummy_var = '?') THEN
596 IF (l_debug = 'Y') THEN
597 Okc_Debug.Log('2300: - attribute TEMPLATE_ID is invalid', 2);
598 END IF;
599 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TEMPLATE_ID');
600 l_return_status := G_RET_STS_ERROR;
601 END IF;
602 END IF;
603
604 IF (l_debug = 'Y') THEN
605 Okc_Debug.Log('2200: - attribute ALLOWED_TMPL_USAGES_ID ', 2);
606 END IF;
607 IF p_document_type IS NOT NULL THEN
608 l_dummy_var := '?';
609 OPEN l_document_type_csr;
610 FETCH l_document_type_csr INTO l_dummy_var;
611 CLOSE l_document_type_csr;
612 IF (l_dummy_var = '?') THEN
613 IF (l_debug = 'Y') THEN
614 Okc_Debug.Log('2300: - attribute DOCUMENT_TYPE is invalid', 2);
615 END IF;
616 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DOCUMENT_TYPE');
617 l_return_status := G_RET_STS_ERROR;
618 END IF;
619 END IF;
620
621 IF (p_template_id IS NOT NULL AND p_document_type IS NOT NULL) THEN
622
623 OPEN l_template_intent_csr;
624 FETCH l_template_intent_csr INTO l_template_intent;
625 CLOSE l_template_intent_csr;
626
627 OPEN l_doc_intent_csr;
628 FETCH l_doc_intent_csr INTO l_doc_intent;
629 CLOSE l_doc_intent_csr;
630 IF l_template_intent <> l_doc_intent THEN
631 IF (l_debug = 'Y') THEN
632 Okc_Debug.Log('2310: - Intents of Template and Document Type do not match', 2);
633 END IF;
634 OPEN l_doc_name_csr;
635 FETCH l_doc_name_csr INTO l_doc_type_name;
636 CLOSE l_doc_name_csr;
637
638 IF l_doc_type_name IS NULL THEN
639 l_doc_type_name := p_document_type;
640 END IF;
641 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_ALWD_USG_WRONG_INTENT',
642 'DOCUMENT_TYPE',l_doc_type_name);
643 l_return_status := G_RET_STS_ERROR;
644 END IF;
645 END IF;
646
647 END IF;
648
649
653
650 IF (l_debug = 'Y') THEN
651 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
652 END IF;
654 RETURN l_return_status;
655
656 EXCEPTION
657 WHEN OTHERS THEN
658 Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
659 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
660 p_msg_name => G_UNEXPECTED_ERROR,
661 p_token1 => G_SQLCODE_TOKEN,
662 p_token1_value => sqlcode,
663 p_token2 => G_SQLERRM_TOKEN,
664 p_token2_value => sqlerrm);
665
666
667 IF l_template_id_csr%ISOPEN THEN
668 CLOSE l_template_id_csr;
669 END IF;
670
671 IF l_document_type_csr%ISOPEN THEN
672 CLOSE l_document_type_csr;
673 END IF;
674
675 RETURN G_RET_STS_UNEXP_ERROR;
676
677 END Validate_Attributes;
678
679
680 ---------------------------------------------------------------------------
681 -- PROCEDURE Validate_Record
682 -- It calls Item Level Validations and then makes Record Level Validations
683 ---------------------------------------------------------------------------
684 ------------------------------------------
685 -- Validate_Record for:OKC_ALLOWED_TMPL_USAGES --
686 ------------------------------------------
687 FUNCTION Validate_Record (
688 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
689
690 p_template_id IN NUMBER,
691 p_document_type IN VARCHAR2,
692 p_default_yn IN VARCHAR2,
693 p_allowed_tmpl_usages_id IN NUMBER,
694 p_attribute_category IN VARCHAR2,
695 p_attribute1 IN VARCHAR2,
696 p_attribute2 IN VARCHAR2,
697 p_attribute3 IN VARCHAR2,
698 p_attribute4 IN VARCHAR2,
699 p_attribute5 IN VARCHAR2,
700 p_attribute6 IN VARCHAR2,
701 p_attribute7 IN VARCHAR2,
702 p_attribute8 IN VARCHAR2,
703 p_attribute9 IN VARCHAR2,
704 p_attribute10 IN VARCHAR2,
705 p_attribute11 IN VARCHAR2,
706 p_attribute12 IN VARCHAR2,
707 p_attribute13 IN VARCHAR2,
708 p_attribute14 IN VARCHAR2,
709 p_attribute15 IN VARCHAR2
710 ) RETURN VARCHAR2 IS
711 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
712 l_dummy_var VARCHAR2(1) := '?';
713 l_parent_tmpl_id NUMBER;
714 l_def_tmpl_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
715 l_doc_type_name OKC_BUS_DOC_TYPES_TL.NAME%TYPE;
716 l_org_id OKC_TERMS_TEMPLATES_ALL.ORG_ID%TYPE;
717
718 CURSOR l_default_tmpl_csr IS
719 SELECT parent_template_id,org_id
720 FROM okc_terms_templates_all
721 WHERE template_id = p_template_id;
722
723 CURSOR l_default_doc_type_csr(pc_parent_tmpl_id NUMBER,
724 pc_org_id NUMBER) is
725 SELECT tmpl.template_name
726 FROM okc_allowed_tmpl_usages usg,
727 okc_terms_templates_all tmpl
728 WHERE document_type = p_document_type
729 AND allowed_tmpl_usages_id <> nvl(p_allowed_tmpl_usages_id, -9999)
730 AND nvl(usg.default_yn,'N') = 'Y'
731 AND ((tmpl.template_id <> pc_parent_tmpl_id AND pc_parent_tmpl_id IS NOT NULL) OR
732 (pc_parent_tmpl_id IS NULL))
733 AND NVL(tmpl.org_id,-99) = NVL(pc_org_id,-99)
734 AND usg.template_id = tmpl.template_id;
735 CURSOR l_allowed_doc_exists_csr IS
736 SELECT '!'
737 FROM okc_allowed_tmpl_usages
738 WHERE template_id = p_template_id
739 AND document_type = p_document_type
740 AND allowed_tmpl_usages_id <> nvl(p_allowed_tmpl_usages_id, -9999);
741 CURSOR l_doc_name_csr IS
742 SELECT name
743 FROM okc_bus_doc_types_vl
744 WHERE document_type = p_document_type;
745
746 BEGIN
747
748 IF (l_debug = 'Y') THEN
749 Okc_Debug.Log('2600: Entered Validate_Record', 2);
750 END IF;
751
752 --- Validate all non-missing attributes (Item Level Validation)
753 l_return_status := Validate_Attributes(
754 p_validation_level => p_validation_level,
755
756 p_template_id => p_template_id,
757 p_document_type => p_document_type,
758 p_default_yn => p_default_yn,
759 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
760 p_attribute_category => p_attribute_category,
761 p_attribute1 => p_attribute1,
762 p_attribute2 => p_attribute2,
763 p_attribute3 => p_attribute3,
764 p_attribute4 => p_attribute4,
765 p_attribute5 => p_attribute5,
766 p_attribute6 => p_attribute6,
767 p_attribute7 => p_attribute7,
768 p_attribute8 => p_attribute8,
769 p_attribute9 => p_attribute9,
770 p_attribute10 => p_attribute10,
771 p_attribute11 => p_attribute11,
772 p_attribute12 => p_attribute12,
776 );
773 p_attribute13 => p_attribute13,
774 p_attribute14 => p_attribute14,
775 p_attribute15 => p_attribute15
777 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
778 IF (l_debug = 'Y') THEN
779 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
780 END IF;
781 RETURN G_RET_STS_UNEXP_ERROR;
782 END IF;
783
784 --- Record Level Validation
785 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
786 IF (l_debug = 'Y') THEN
787 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
788 END IF;
789 /*+++++++++++++start of hand code +++++++++++++++++++*/
790 -- ?? manual coding for Record Level Validations if required ??
791 IF p_document_type IS NOT NULL AND p_default_yn = 'Y' THEN
792 OPEN l_default_tmpl_csr;
793 FETCH l_default_tmpl_csr INTO l_parent_tmpl_id,l_org_id;
794 CLOSE l_default_tmpl_csr;
795
796 OPEN l_default_doc_type_csr(l_parent_tmpl_id,l_org_id);
797 FETCH l_default_doc_type_csr INTO l_def_tmpl_name;
798 IF l_default_doc_type_csr%FOUND THEN
799 IF (l_debug = 'Y') THEN
800 Okc_Debug.Log('2810: - default template for this document type already exists', 2);
801 END IF;
802 OPEN l_doc_name_csr;
803 FETCH l_doc_name_csr INTO l_doc_type_name;
804 CLOSE l_doc_name_csr;
805
806 IF l_doc_type_name IS NULL THEN
807 l_doc_type_name := p_document_type;
808 END IF;
809 Okc_Api.Set_Message(G_APP_NAME, 'OKC_TMPL_MLTPL_DEF_USG',
810 'DOCUMENT_TYPE',l_doc_type_name,
811 'TEMPLATE_NAME',l_def_tmpl_name);
812 l_return_status := G_RET_STS_ERROR;
813 END IF;
814 CLOSE l_default_doc_type_csr;
815 END IF;
816
817 OPEN l_allowed_doc_exists_csr;
818 FETCH l_allowed_doc_exists_csr INTO l_dummy_var;
819 IF l_allowed_doc_exists_csr%FOUND THEN
820 IF (l_debug = 'Y') THEN
821 Okc_Debug.Log('2810: - This document type already exists', 2);
822 END IF;
823
824 OPEN l_doc_name_csr;
825 FETCH l_doc_name_csr INTO l_doc_type_name;
826 CLOSE l_doc_name_csr;
827
828 IF l_doc_type_name IS NULL THEN
829 l_doc_type_name := p_document_type;
830 END IF;
831
832 Okc_Api.Set_Message(G_APP_NAME, 'OKC_ALLOWED_TMPL_USG_DUP',
833 'DOCUMENT_TYPE',l_doc_type_name);
834 l_return_status := G_RET_STS_ERROR;
835 END IF;
836 CLOSE l_allowed_doc_exists_csr;
837 /*+++++++++++++End of hand code +++++++++++++++++++*/
838 END IF;
839
840 IF (l_debug = 'Y') THEN
841 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
842 END IF;
843 RETURN l_return_status ;
844
845 EXCEPTION
846 WHEN OTHERS THEN
847
848 IF (l_debug = 'Y') THEN
849 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
850 END IF;
851
852 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
853 p_msg_name => G_UNEXPECTED_ERROR,
854 p_token1 => G_SQLCODE_TOKEN,
855 p_token1_value => sqlcode,
856 p_token2 => G_SQLERRM_TOKEN,
857 p_token2_value => sqlerrm);
858 RETURN G_RET_STS_UNEXP_ERROR ;
859
860 END Validate_Record;
861
862 ---------------------------------------------------------------------------
863 -- PROCEDURE validate_row
864 ---------------------------------------------------------------------------
865 ---------------------------------------
866 -- validate_row for:OKC_ALLOWED_TMPL_USAGES --
867 ---------------------------------------
868 PROCEDURE validate_row(
869 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
870
871 x_return_status OUT NOCOPY VARCHAR2,
872
873 p_template_id IN NUMBER,
874 p_document_type IN VARCHAR2,
875 p_default_yn IN VARCHAR2,
876 p_allowed_tmpl_usages_id IN NUMBER,
877
878 p_attribute_category IN VARCHAR2 := NULL,
879 p_attribute1 IN VARCHAR2 := NULL,
880 p_attribute2 IN VARCHAR2 := NULL,
881 p_attribute3 IN VARCHAR2 := NULL,
882 p_attribute4 IN VARCHAR2 := NULL,
883 p_attribute5 IN VARCHAR2 := NULL,
884 p_attribute6 IN VARCHAR2 := NULL,
885 p_attribute7 IN VARCHAR2 := NULL,
886 p_attribute8 IN VARCHAR2 := NULL,
887 p_attribute9 IN VARCHAR2 := NULL,
888 p_attribute10 IN VARCHAR2 := NULL,
889 p_attribute11 IN VARCHAR2 := NULL,
890 p_attribute12 IN VARCHAR2 := NULL,
891 p_attribute13 IN VARCHAR2 := NULL,
892 p_attribute14 IN VARCHAR2 := NULL,
893 p_attribute15 IN VARCHAR2 := NULL,
894
895 p_object_version_number IN NUMBER
896 ) IS
897 l_template_id OKC_ALLOWED_TMPL_USAGES.TEMPLATE_ID%TYPE;
898 l_document_type OKC_ALLOWED_TMPL_USAGES.DOCUMENT_TYPE%TYPE;
902 l_attribute2 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE2%TYPE;
899 l_default_yn OKC_ALLOWED_TMPL_USAGES.DEFAULT_YN%TYPE;
900 l_attribute_category OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE_CATEGORY%TYPE;
901 l_attribute1 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE1%TYPE;
903 l_attribute3 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE3%TYPE;
904 l_attribute4 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE4%TYPE;
905 l_attribute5 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE5%TYPE;
906 l_attribute6 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE6%TYPE;
907 l_attribute7 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE7%TYPE;
908 l_attribute8 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE8%TYPE;
909 l_attribute9 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE9%TYPE;
910 l_attribute10 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE10%TYPE;
911 l_attribute11 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE11%TYPE;
912 l_attribute12 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE12%TYPE;
913 l_attribute13 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE13%TYPE;
914 l_attribute14 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE14%TYPE;
915 l_attribute15 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE15%TYPE;
916 l_object_version_number OKC_ALLOWED_TMPL_USAGES.OBJECT_VERSION_NUMBER%TYPE;
917 l_created_by OKC_ALLOWED_TMPL_USAGES.CREATED_BY%TYPE;
918 l_creation_date OKC_ALLOWED_TMPL_USAGES.CREATION_DATE%TYPE;
919 l_last_updated_by OKC_ALLOWED_TMPL_USAGES.LAST_UPDATED_BY%TYPE;
920 l_last_update_login OKC_ALLOWED_TMPL_USAGES.LAST_UPDATE_LOGIN%TYPE;
921 l_last_update_date OKC_ALLOWED_TMPL_USAGES.LAST_UPDATE_DATE%TYPE;
922 BEGIN
923
924 IF (l_debug = 'Y') THEN
925 Okc_Debug.Log('3100: Entered validate_row', 2);
926 END IF;
927
928 -- Setting attributes
929 x_return_status := Set_Attributes(
930 p_template_id => p_template_id,
931 p_document_type => p_document_type,
932 p_default_yn => p_default_yn,
933 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
934 p_attribute_category => p_attribute_category,
935 p_attribute1 => p_attribute1,
936 p_attribute2 => p_attribute2,
937 p_attribute3 => p_attribute3,
938 p_attribute4 => p_attribute4,
939 p_attribute5 => p_attribute5,
940 p_attribute6 => p_attribute6,
941 p_attribute7 => p_attribute7,
942 p_attribute8 => p_attribute8,
943 p_attribute9 => p_attribute9,
944 p_attribute10 => p_attribute10,
945 p_attribute11 => p_attribute11,
946 p_attribute12 => p_attribute12,
947 p_attribute13 => p_attribute13,
948 p_attribute14 => p_attribute14,
949 p_attribute15 => p_attribute15,
950 p_object_version_number => p_object_version_number,
951 x_template_id => l_template_id,
952 x_document_type => l_document_type,
953 x_default_yn => l_default_yn,
954 x_attribute_category => l_attribute_category,
955 x_attribute1 => l_attribute1,
956 x_attribute2 => l_attribute2,
957 x_attribute3 => l_attribute3,
958 x_attribute4 => l_attribute4,
959 x_attribute5 => l_attribute5,
960 x_attribute6 => l_attribute6,
961 x_attribute7 => l_attribute7,
962 x_attribute8 => l_attribute8,
963 x_attribute9 => l_attribute9,
964 x_attribute10 => l_attribute10,
965 x_attribute11 => l_attribute11,
966 x_attribute12 => l_attribute12,
967 x_attribute13 => l_attribute13,
968 x_attribute14 => l_attribute14,
969 x_attribute15 => l_attribute15
970 );
971 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
973 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
974 RAISE FND_API.G_EXC_ERROR;
975 END IF;
976
977 -- Validate all non-missing attributes (Item Level Validation)
978 x_return_status := Validate_Record(
979 p_validation_level => p_validation_level,
980 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
981 p_template_id => l_template_id,
982 p_document_type => l_document_type,
983 p_default_yn => l_default_yn,
984 p_attribute_category => l_attribute_category,
985 p_attribute1 => l_attribute1,
986 p_attribute2 => l_attribute2,
987 p_attribute3 => l_attribute3,
988 p_attribute4 => l_attribute4,
989 p_attribute5 => l_attribute5,
990 p_attribute6 => l_attribute6,
991 p_attribute7 => l_attribute7,
992 p_attribute8 => l_attribute8,
993 p_attribute9 => l_attribute9,
994 p_attribute10 => l_attribute10,
995 p_attribute11 => l_attribute11,
996 p_attribute12 => l_attribute12,
997 p_attribute13 => l_attribute13,
998 p_attribute14 => l_attribute14,
999 p_attribute15 => l_attribute15
1000 );
1001
1002 IF (l_debug = 'Y') THEN
1006 EXCEPTION
1003 Okc_Debug.Log('3200: Leaving validate_row', 2);
1004 END IF;
1005
1007 WHEN FND_API.G_EXC_ERROR THEN
1008 IF (l_debug = 'Y') THEN
1009 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
1010 END IF;
1011 x_return_status := G_RET_STS_ERROR;
1012
1013 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1014 IF (l_debug = 'Y') THEN
1015 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1016 END IF;
1017 x_return_status := G_RET_STS_UNEXP_ERROR;
1018
1019 WHEN OTHERS THEN
1020 IF (l_debug = 'Y') THEN
1021 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
1022 END IF;
1023 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1024 p_msg_name => G_UNEXPECTED_ERROR,
1025 p_token1 => G_SQLCODE_TOKEN,
1026 p_token1_value => sqlcode,
1027 p_token2 => G_SQLERRM_TOKEN,
1028 p_token2_value => sqlerrm);
1029 x_return_status := G_RET_STS_UNEXP_ERROR;
1030
1031 END Validate_Row;
1032
1033 ---------------------------------------------------------------------------
1034 -- PROCEDURE Insert_Row
1035 ---------------------------------------------------------------------------
1036 -------------------------------------
1037 -- Insert_Row for:OKC_ALLOWED_TMPL_USAGES --
1038 -------------------------------------
1039 FUNCTION Insert_Row(
1040 p_template_id IN NUMBER,
1041 p_document_type IN VARCHAR2,
1042 p_default_yn IN VARCHAR2,
1043 p_allowed_tmpl_usages_id IN NUMBER,
1044 p_attribute_category IN VARCHAR2,
1045 p_attribute1 IN VARCHAR2,
1046 p_attribute2 IN VARCHAR2,
1047 p_attribute3 IN VARCHAR2,
1048 p_attribute4 IN VARCHAR2,
1049 p_attribute5 IN VARCHAR2,
1050 p_attribute6 IN VARCHAR2,
1051 p_attribute7 IN VARCHAR2,
1052 p_attribute8 IN VARCHAR2,
1053 p_attribute9 IN VARCHAR2,
1054 p_attribute10 IN VARCHAR2,
1055 p_attribute11 IN VARCHAR2,
1056 p_attribute12 IN VARCHAR2,
1057 p_attribute13 IN VARCHAR2,
1058 p_attribute14 IN VARCHAR2,
1059 p_attribute15 IN VARCHAR2,
1060 p_object_version_number IN NUMBER,
1061 p_created_by IN NUMBER,
1062 p_creation_date IN DATE,
1063 p_last_updated_by IN NUMBER,
1064 p_last_update_login IN NUMBER,
1065 p_last_update_date IN DATE
1066
1067 ) RETURN VARCHAR2 IS
1068
1069 BEGIN
1070
1071 IF (l_debug = 'Y') THEN
1072 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
1073 END IF;
1074
1075 INSERT INTO OKC_ALLOWED_TMPL_USAGES(
1076 TEMPLATE_ID,
1077 DOCUMENT_TYPE,
1078 DEFAULT_YN,
1079 ALLOWED_TMPL_USAGES_ID,
1080 ATTRIBUTE_CATEGORY,
1081 ATTRIBUTE1,
1082 ATTRIBUTE2,
1083 ATTRIBUTE3,
1084 ATTRIBUTE4,
1085 ATTRIBUTE5,
1086 ATTRIBUTE6,
1087 ATTRIBUTE7,
1088 ATTRIBUTE8,
1089 ATTRIBUTE9,
1090 ATTRIBUTE10,
1091 ATTRIBUTE11,
1092 ATTRIBUTE12,
1093 ATTRIBUTE13,
1094 ATTRIBUTE14,
1095 ATTRIBUTE15,
1096 OBJECT_VERSION_NUMBER,
1097 CREATED_BY,
1098 CREATION_DATE,
1099 LAST_UPDATED_BY,
1100 LAST_UPDATE_LOGIN,
1101 LAST_UPDATE_DATE)
1102 VALUES (
1103 p_template_id,
1104 p_document_type,
1105 p_default_yn,
1106 p_allowed_tmpl_usages_id,
1107 p_attribute_category,
1108 p_attribute1,
1109 p_attribute2,
1110 p_attribute3,
1111 p_attribute4,
1112 p_attribute5,
1113 p_attribute6,
1114 p_attribute7,
1115 p_attribute8,
1116 p_attribute9,
1117 p_attribute10,
1118 p_attribute11,
1119 p_attribute12,
1120 p_attribute13,
1121 p_attribute14,
1122 p_attribute15,
1123 p_object_version_number,
1124 p_created_by,
1125 p_creation_date,
1126 p_last_updated_by,
1127 p_last_update_login,
1128 p_last_update_date);
1129
1130 IF (l_debug = 'Y') THEN
1131 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
1132 END IF;
1133
1134 RETURN( G_RET_STS_SUCCESS );
1135
1136 EXCEPTION
1137 WHEN OTHERS THEN
1138
1139 IF (l_debug = 'Y') THEN
1140 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
1141 END IF;
1142
1143 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1144 p_msg_name => G_UNEXPECTED_ERROR,
1145 p_token1 => G_SQLCODE_TOKEN,
1146 p_token1_value => sqlcode,
1147 p_token2 => G_SQLERRM_TOKEN,
1148 p_token2_value => sqlerrm);
1149
1153
1150 RETURN( G_RET_STS_UNEXP_ERROR );
1151
1152 END Insert_Row;
1154
1155 -------------------------------------
1156 -- Insert_Row for:OKC_ALLOWED_TMPL_USAGES --
1157 -------------------------------------
1158 PROCEDURE Insert_Row(
1159 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1160 x_return_status OUT NOCOPY VARCHAR2,
1161
1162 p_template_id IN NUMBER,
1163 p_document_type IN VARCHAR2,
1164 p_default_yn IN VARCHAR2,
1165 p_allowed_tmpl_usages_id IN NUMBER := NULL,
1166
1167 p_attribute_category IN VARCHAR2 := NULL,
1168 p_attribute1 IN VARCHAR2 := NULL,
1169 p_attribute2 IN VARCHAR2 := NULL,
1170 p_attribute3 IN VARCHAR2 := NULL,
1171 p_attribute4 IN VARCHAR2 := NULL,
1172 p_attribute5 IN VARCHAR2 := NULL,
1173 p_attribute6 IN VARCHAR2 := NULL,
1174 p_attribute7 IN VARCHAR2 := NULL,
1175 p_attribute8 IN VARCHAR2 := NULL,
1176 p_attribute9 IN VARCHAR2 := NULL,
1177 p_attribute10 IN VARCHAR2 := NULL,
1178 p_attribute11 IN VARCHAR2 := NULL,
1179 p_attribute12 IN VARCHAR2 := NULL,
1180 p_attribute13 IN VARCHAR2 := NULL,
1181 p_attribute14 IN VARCHAR2 := NULL,
1182 p_attribute15 IN VARCHAR2 := NULL,
1183
1184 x_allowed_tmpl_usages_id OUT NOCOPY NUMBER
1185
1186 ) IS
1187
1188 l_object_version_number OKC_ALLOWED_TMPL_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1189 l_created_by OKC_ALLOWED_TMPL_USAGES.CREATED_BY%TYPE;
1190 l_creation_date OKC_ALLOWED_TMPL_USAGES.CREATION_DATE%TYPE;
1191 l_last_updated_by OKC_ALLOWED_TMPL_USAGES.LAST_UPDATED_BY%TYPE;
1192 l_last_update_login OKC_ALLOWED_TMPL_USAGES.LAST_UPDATE_LOGIN%TYPE;
1193 l_last_update_date OKC_ALLOWED_TMPL_USAGES.LAST_UPDATE_DATE%TYPE;
1194 BEGIN
1195
1196 x_return_status := G_RET_STS_SUCCESS;
1197
1198 IF (l_debug = 'Y') THEN
1199 Okc_Debug.Log('4200: Entered Insert_Row', 2);
1200 END IF;
1201
1202 --- Setting item attributes
1203 -- Set primary key value
1204 IF( p_allowed_tmpl_usages_id IS NULL ) THEN
1205 x_return_status := Get_Seq_Id(
1206 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
1207 x_allowed_tmpl_usages_id => x_allowed_tmpl_usages_id
1208 );
1209 --- If any errors happen abort API
1210 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1213 RAISE FND_API.G_EXC_ERROR;
1214 END IF;
1215 ELSE
1216 x_allowed_tmpl_usages_id := p_allowed_tmpl_usages_id;
1217 END IF;
1218 -- Set Internal columns
1219 l_object_version_number := 1;
1220 l_creation_date := Sysdate;
1221 l_created_by := Fnd_Global.User_Id;
1222 l_last_update_date := l_creation_date;
1223 l_last_updated_by := l_created_by;
1224 l_last_update_login := Fnd_Global.Login_Id;
1225
1226
1227 --- Validate all non-missing attributes
1228 x_return_status := Validate_Record(
1229 p_validation_level => p_validation_level,
1230 p_allowed_tmpl_usages_id => x_allowed_tmpl_usages_id,
1231 p_template_id => p_template_id,
1232 p_document_type => p_document_type,
1233 p_default_yn => p_default_yn,
1234 p_attribute_category => p_attribute_category,
1235 p_attribute1 => p_attribute1,
1236 p_attribute2 => p_attribute2,
1237 p_attribute3 => p_attribute3,
1238 p_attribute4 => p_attribute4,
1239 p_attribute5 => p_attribute5,
1240 p_attribute6 => p_attribute6,
1241 p_attribute7 => p_attribute7,
1242 p_attribute8 => p_attribute8,
1243 p_attribute9 => p_attribute9,
1244 p_attribute10 => p_attribute10,
1245 p_attribute11 => p_attribute11,
1246 p_attribute12 => p_attribute12,
1247 p_attribute13 => p_attribute13,
1248 p_attribute14 => p_attribute14,
1249 p_attribute15 => p_attribute15
1250 );
1251 --- If any errors happen abort API
1252 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1254 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1255 RAISE FND_API.G_EXC_ERROR;
1256 END IF;
1257
1258 --------------------------------------------
1259 -- Call the internal Insert_Row for each child record
1260 --------------------------------------------
1261 IF (l_debug = 'Y') THEN
1262 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
1263 END IF;
1264
1265 x_return_status := Insert_Row(
1266 p_allowed_tmpl_usages_id => x_allowed_tmpl_usages_id,
1267 p_template_id => p_template_id,
1268 p_document_type => p_document_type,
1269 p_default_yn => p_default_yn,
1270 p_attribute_category => p_attribute_category,
1271 p_attribute1 => p_attribute1,
1275 p_attribute5 => p_attribute5,
1272 p_attribute2 => p_attribute2,
1273 p_attribute3 => p_attribute3,
1274 p_attribute4 => p_attribute4,
1276 p_attribute6 => p_attribute6,
1277 p_attribute7 => p_attribute7,
1278 p_attribute8 => p_attribute8,
1279 p_attribute9 => p_attribute9,
1280 p_attribute10 => p_attribute10,
1281 p_attribute11 => p_attribute11,
1282 p_attribute12 => p_attribute12,
1283 p_attribute13 => p_attribute13,
1284 p_attribute14 => p_attribute14,
1285 p_attribute15 => p_attribute15,
1286 p_object_version_number => l_object_version_number,
1287 p_created_by => l_created_by,
1288 p_creation_date => l_creation_date,
1289 p_last_updated_by => l_last_updated_by,
1290 p_last_update_login => l_last_update_login,
1291 p_last_update_date => l_last_update_date
1292 );
1293 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1295 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1296 RAISE FND_API.G_EXC_ERROR;
1297 END IF;
1298
1299
1300
1301 IF (l_debug = 'Y') THEN
1302 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
1303 END IF;
1304
1305 EXCEPTION
1306 WHEN FND_API.G_EXC_ERROR THEN
1307 IF (l_debug = 'Y') THEN
1308 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
1309 END IF;
1310 x_return_status := G_RET_STS_ERROR;
1311
1312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1313 IF (l_debug = 'Y') THEN
1314 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1315 END IF;
1316 x_return_status := G_RET_STS_UNEXP_ERROR;
1317
1318 WHEN OTHERS THEN
1319 IF (l_debug = 'Y') THEN
1320 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
1321 END IF;
1322 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1323 p_msg_name => G_UNEXPECTED_ERROR,
1324 p_token1 => G_SQLCODE_TOKEN,
1325 p_token1_value => sqlcode,
1326 p_token2 => G_SQLERRM_TOKEN,
1327 p_token2_value => sqlerrm);
1328 x_return_status := G_RET_STS_UNEXP_ERROR;
1329
1330 END Insert_Row;
1331 ---------------------------------------------------------------------------
1332 -- PROCEDURE Lock_Row
1333 ---------------------------------------------------------------------------
1334 -----------------------------------
1335 -- Lock_Row for:OKC_ALLOWED_TMPL_USAGES --
1336 -----------------------------------
1337 FUNCTION Lock_Row(
1338 p_allowed_tmpl_usages_id IN NUMBER,
1339 p_object_version_number IN NUMBER
1340 ) RETURN VARCHAR2 IS
1341
1342 l_return_status VARCHAR2(1);
1343 l_object_version_number OKC_ALLOWED_TMPL_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1344 l_row_notfound BOOLEAN := FALSE;
1345
1346 CURSOR lock_csr (cp_allowed_tmpl_usages_id NUMBER, cp_object_version_number NUMBER) IS
1347 SELECT object_version_number
1348 FROM OKC_ALLOWED_TMPL_USAGES
1349 WHERE ALLOWED_TMPL_USAGES_ID = cp_allowed_tmpl_usages_id
1350 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1351 FOR UPDATE OF object_version_number NOWAIT;
1352
1353 CURSOR lchk_csr (cp_allowed_tmpl_usages_id NUMBER) IS
1354 SELECT object_version_number
1355 FROM OKC_ALLOWED_TMPL_USAGES
1356 WHERE ALLOWED_TMPL_USAGES_ID = cp_allowed_tmpl_usages_id;
1357 BEGIN
1358
1359 IF (l_debug = 'Y') THEN
1360 Okc_Debug.Log('4900: Entered Lock_Row', 2);
1361 END IF;
1362
1363
1364 BEGIN
1365
1366 OPEN lock_csr( p_allowed_tmpl_usages_id, p_object_version_number );
1367 FETCH lock_csr INTO l_object_version_number;
1368 l_row_notfound := lock_csr%NOTFOUND;
1369 CLOSE lock_csr;
1370
1371 EXCEPTION
1372 WHEN E_Resource_Busy THEN
1373
1374 IF (l_debug = 'Y') THEN
1375 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
1376 END IF;
1377
1378 IF (lock_csr%ISOPEN) THEN
1379 CLOSE lock_csr;
1380 END IF;
1381 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1382 RETURN( G_RET_STS_ERROR );
1383 END;
1384
1385 IF ( l_row_notfound ) THEN
1386 l_return_status := G_RET_STS_ERROR;
1387
1388 OPEN lchk_csr(p_allowed_tmpl_usages_id);
1389 FETCH lchk_csr INTO l_object_version_number;
1390 l_row_notfound := lchk_csr%NOTFOUND;
1391 CLOSE lchk_csr;
1392
1393 IF (l_row_notfound) THEN
1394 Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
1395 ELSIF l_object_version_number > p_object_version_number THEN
1396 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1397 ELSIF l_object_version_number = -1 THEN
1398 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1402 ELSE
1399 ELSE -- it can be the only above condition. It can happen after restore version
1400 Okc_Api.Set_Message(G_FND_APP,G_RECORD_CHANGED);
1401 END IF;
1403 l_return_status := G_RET_STS_SUCCESS;
1404 END IF;
1405
1406 IF (l_debug = 'Y') THEN
1407 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
1408 END IF;
1409
1410 RETURN( l_return_status );
1411
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414
1415 IF (lock_csr%ISOPEN) THEN
1416 CLOSE lock_csr;
1417 END IF;
1418 IF (lchk_csr%ISOPEN) THEN
1419 CLOSE lchk_csr;
1420 END IF;
1421
1422 IF (l_debug = 'Y') THEN
1423 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1424 END IF;
1425
1426 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1427 p_msg_name => G_UNEXPECTED_ERROR,
1428 p_token1 => G_SQLCODE_TOKEN,
1429 p_token1_value => sqlcode,
1430 p_token2 => G_SQLERRM_TOKEN,
1431 p_token2_value => sqlerrm);
1432
1433 RETURN( G_RET_STS_UNEXP_ERROR );
1434 END Lock_Row;
1435
1436 -----------------------------------
1437 -- Lock_Row for:OKC_ALLOWED_TMPL_USAGES --
1438 -----------------------------------
1439 PROCEDURE Lock_Row(
1440 x_return_status OUT NOCOPY VARCHAR2,
1441
1442 p_allowed_tmpl_usages_id IN NUMBER,
1443 p_object_version_number IN NUMBER
1444 ) IS
1445 BEGIN
1446
1447 IF (l_debug = 'Y') THEN
1448 Okc_Debug.Log('5700: Entered Lock_Row', 2);
1449 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
1450 END IF;
1451
1452 --------------------------------------------
1453 -- Call the LOCK_ROW for each _B child record
1454 --------------------------------------------
1455 x_return_status := Lock_Row(
1456 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
1457 p_object_version_number => p_object_version_number
1458 );
1459 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1462 RAISE FND_API.G_EXC_ERROR;
1463 END IF;
1464
1465
1466
1467 IF (l_debug = 'Y') THEN
1468 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
1469 END IF;
1470
1471 EXCEPTION
1472 WHEN FND_API.G_EXC_ERROR THEN
1473 IF (l_debug = 'Y') THEN
1474 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
1475 END IF;
1476 x_return_status := G_RET_STS_ERROR;
1477
1478 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1479 IF (l_debug = 'Y') THEN
1480 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1481 END IF;
1482 x_return_status := G_RET_STS_UNEXP_ERROR;
1483
1484 WHEN OTHERS THEN
1485 IF (l_debug = 'Y') THEN
1486 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
1487 END IF;
1488 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1489 p_msg_name => G_UNEXPECTED_ERROR,
1490 p_token1 => G_SQLCODE_TOKEN,
1491 p_token1_value => sqlcode,
1492 p_token2 => G_SQLERRM_TOKEN,
1493 p_token2_value => sqlerrm);
1494 x_return_status := G_RET_STS_UNEXP_ERROR;
1495
1496 END Lock_Row;
1497 ---------------------------------------------------------------------------
1498 -- PROCEDURE Update_Row
1499 ---------------------------------------------------------------------------
1500 -------------------------------------
1501 -- Update_Row for:OKC_ALLOWED_TMPL_USAGES --
1502 -------------------------------------
1503 FUNCTION Update_Row(
1504 p_allowed_tmpl_usages_id IN NUMBER,
1505 p_template_id IN NUMBER,
1506 p_document_type IN VARCHAR2,
1507 p_default_yn IN VARCHAR2,
1508 p_attribute_category IN VARCHAR2,
1509 p_attribute1 IN VARCHAR2,
1510 p_attribute2 IN VARCHAR2,
1511 p_attribute3 IN VARCHAR2,
1512 p_attribute4 IN VARCHAR2,
1513 p_attribute5 IN VARCHAR2,
1514 p_attribute6 IN VARCHAR2,
1515 p_attribute7 IN VARCHAR2,
1516 p_attribute8 IN VARCHAR2,
1517 p_attribute9 IN VARCHAR2,
1518 p_attribute10 IN VARCHAR2,
1519 p_attribute11 IN VARCHAR2,
1520 p_attribute12 IN VARCHAR2,
1521 p_attribute13 IN VARCHAR2,
1522 p_attribute14 IN VARCHAR2,
1523 p_attribute15 IN VARCHAR2,
1524 p_object_version_number IN NUMBER,
1525 p_last_updated_by IN NUMBER,
1526 p_last_update_login IN NUMBER,
1527 p_last_update_date IN DATE
1528 ) RETURN VARCHAR2 IS
1529
1530 BEGIN
1531
1532 IF (l_debug = 'Y') THEN
1533 Okc_Debug.Log('6400: Entered Update_Row', 2);
1534 END IF;
1535
1536 UPDATE OKC_ALLOWED_TMPL_USAGES
1537 SET TEMPLATE_ID = p_template_id,
1541 ATTRIBUTE1 = p_attribute1,
1538 DOCUMENT_TYPE = p_document_type,
1539 DEFAULT_YN = p_default_yn,
1540 ATTRIBUTE_CATEGORY = p_attribute_category,
1542 ATTRIBUTE2 = p_attribute2,
1543 ATTRIBUTE3 = p_attribute3,
1544 ATTRIBUTE4 = p_attribute4,
1545 ATTRIBUTE5 = p_attribute5,
1546 ATTRIBUTE6 = p_attribute6,
1547 ATTRIBUTE7 = p_attribute7,
1548 ATTRIBUTE8 = p_attribute8,
1549 ATTRIBUTE9 = p_attribute9,
1550 ATTRIBUTE10 = p_attribute10,
1551 ATTRIBUTE11 = p_attribute11,
1552 ATTRIBUTE12 = p_attribute12,
1553 ATTRIBUTE13 = p_attribute13,
1554 ATTRIBUTE14 = p_attribute14,
1555 ATTRIBUTE15 = p_attribute15,
1556 OBJECT_VERSION_NUMBER = p_object_version_number,
1557 LAST_UPDATED_BY = p_last_updated_by,
1558 LAST_UPDATE_LOGIN = p_last_update_login,
1559 LAST_UPDATE_DATE = p_last_update_date
1560 WHERE ALLOWED_TMPL_USAGES_ID = p_allowed_tmpl_usages_id;
1561
1562 IF (l_debug = 'Y') THEN
1563 Okc_Debug.Log('6500: Leaving Update_Row', 2);
1564 END IF;
1565
1566 RETURN G_RET_STS_SUCCESS ;
1567
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570
1571 IF (l_debug = 'Y') THEN
1572 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1573 END IF;
1574
1575 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1576 p_msg_name => G_UNEXPECTED_ERROR,
1577 p_token1 => G_SQLCODE_TOKEN,
1578 p_token1_value => sqlcode,
1579 p_token2 => G_SQLERRM_TOKEN,
1580 p_token2_value => sqlerrm);
1581
1582 RETURN G_RET_STS_UNEXP_ERROR ;
1583
1584 END Update_Row;
1585
1586 -------------------------------------
1587 -- Update_Row for:OKC_ALLOWED_TMPL_USAGES --
1588 -------------------------------------
1589 PROCEDURE Update_Row(
1590 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1591
1592 x_return_status OUT NOCOPY VARCHAR2,
1593
1594 p_allowed_tmpl_usages_id IN NUMBER,
1595
1596 p_template_id IN NUMBER := NULL,
1597 p_document_type IN VARCHAR2 := NULL,
1598 p_default_yn IN VARCHAR2 := NULL,
1599 p_attribute_category IN VARCHAR2 := NULL,
1600 p_attribute1 IN VARCHAR2 := NULL,
1601 p_attribute2 IN VARCHAR2 := NULL,
1602 p_attribute3 IN VARCHAR2 := NULL,
1603 p_attribute4 IN VARCHAR2 := NULL,
1604 p_attribute5 IN VARCHAR2 := NULL,
1605 p_attribute6 IN VARCHAR2 := NULL,
1606 p_attribute7 IN VARCHAR2 := NULL,
1607 p_attribute8 IN VARCHAR2 := NULL,
1608 p_attribute9 IN VARCHAR2 := NULL,
1609 p_attribute10 IN VARCHAR2 := NULL,
1610 p_attribute11 IN VARCHAR2 := NULL,
1611 p_attribute12 IN VARCHAR2 := NULL,
1612 p_attribute13 IN VARCHAR2 := NULL,
1613 p_attribute14 IN VARCHAR2 := NULL,
1614 p_attribute15 IN VARCHAR2 := NULL,
1615
1616 p_object_version_number IN NUMBER
1617
1618 ) IS
1619
1620 l_template_id OKC_ALLOWED_TMPL_USAGES.TEMPLATE_ID%TYPE;
1621 l_document_type OKC_ALLOWED_TMPL_USAGES.DOCUMENT_TYPE%TYPE;
1622 l_default_yn OKC_ALLOWED_TMPL_USAGES.DEFAULT_YN%TYPE;
1623 l_attribute_category OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE_CATEGORY%TYPE;
1624 l_attribute1 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE1%TYPE;
1625 l_attribute2 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE2%TYPE;
1626 l_attribute3 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE3%TYPE;
1627 l_attribute4 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE4%TYPE;
1628 l_attribute5 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE5%TYPE;
1629 l_attribute6 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE6%TYPE;
1630 l_attribute7 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE7%TYPE;
1631 l_attribute8 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE8%TYPE;
1632 l_attribute9 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE9%TYPE;
1633 l_attribute10 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE10%TYPE;
1634 l_attribute11 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE11%TYPE;
1635 l_attribute12 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE12%TYPE;
1636 l_attribute13 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE13%TYPE;
1637 l_attribute14 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE14%TYPE;
1638 l_attribute15 OKC_ALLOWED_TMPL_USAGES.ATTRIBUTE15%TYPE;
1639 l_object_version_number OKC_ALLOWED_TMPL_USAGES.OBJECT_VERSION_NUMBER%TYPE;
1640 l_last_updated_by OKC_ALLOWED_TMPL_USAGES.LAST_UPDATED_BY%TYPE;
1641 l_last_update_login OKC_ALLOWED_TMPL_USAGES.LAST_UPDATE_LOGIN%TYPE;
1642 l_last_update_date OKC_ALLOWED_TMPL_USAGES.LAST_UPDATE_DATE%TYPE;
1643
1644 BEGIN
1645
1646 IF (l_debug = 'Y') THEN
1647 Okc_Debug.Log('7000: Entered Update_Row', 2);
1648 Okc_Debug.Log('7100: Locking _B row', 2);
1649 END IF;
1653 p_object_version_number => p_object_version_number
1650
1651 x_return_status := Lock_row(
1652 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
1654 );
1655 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1657 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1658 RAISE FND_API.G_EXC_ERROR;
1659 END IF;
1660
1661
1662 IF (l_debug = 'Y') THEN
1663 Okc_Debug.Log('7300: Setting attributes', 2);
1664 END IF;
1665
1666 x_return_status := Set_Attributes(
1667 p_template_id => p_template_id,
1668 p_document_type => p_document_type,
1669 p_default_yn => p_default_yn,
1670 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
1671 p_attribute_category => p_attribute_category,
1672 p_attribute1 => p_attribute1,
1673 p_attribute2 => p_attribute2,
1674 p_attribute3 => p_attribute3,
1675 p_attribute4 => p_attribute4,
1676 p_attribute5 => p_attribute5,
1677 p_attribute6 => p_attribute6,
1678 p_attribute7 => p_attribute7,
1679 p_attribute8 => p_attribute8,
1680 p_attribute9 => p_attribute9,
1681 p_attribute10 => p_attribute10,
1682 p_attribute11 => p_attribute11,
1683 p_attribute12 => p_attribute12,
1684 p_attribute13 => p_attribute13,
1685 p_attribute14 => p_attribute14,
1686 p_attribute15 => p_attribute15,
1687 p_object_version_number => p_object_version_number,
1688 x_template_id => l_template_id,
1689 x_document_type => l_document_type,
1690 x_default_yn => l_default_yn,
1691 x_attribute_category => l_attribute_category,
1692 x_attribute1 => l_attribute1,
1693 x_attribute2 => l_attribute2,
1694 x_attribute3 => l_attribute3,
1695 x_attribute4 => l_attribute4,
1696 x_attribute5 => l_attribute5,
1697 x_attribute6 => l_attribute6,
1698 x_attribute7 => l_attribute7,
1699 x_attribute8 => l_attribute8,
1700 x_attribute9 => l_attribute9,
1701 x_attribute10 => l_attribute10,
1702 x_attribute11 => l_attribute11,
1703 x_attribute12 => l_attribute12,
1704 x_attribute13 => l_attribute13,
1705 x_attribute14 => l_attribute14,
1706 x_attribute15 => l_attribute15
1707 );
1708 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1710 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1711 RAISE FND_API.G_EXC_ERROR;
1712 END IF;
1713
1714 IF (l_debug = 'Y') THEN
1715 Okc_Debug.Log('7400: Record Validation', 2);
1716 END IF;
1717
1718 --- Validate all non-missing attributes
1719 x_return_status := Validate_Record(
1720 p_validation_level => p_validation_level,
1721 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
1722 p_template_id => l_template_id,
1723 p_document_type => l_document_type,
1724 p_default_yn => l_default_yn,
1725 p_attribute_category => l_attribute_category,
1726 p_attribute1 => l_attribute1,
1727 p_attribute2 => l_attribute2,
1728 p_attribute3 => l_attribute3,
1729 p_attribute4 => l_attribute4,
1730 p_attribute5 => l_attribute5,
1731 p_attribute6 => l_attribute6,
1732 p_attribute7 => l_attribute7,
1736 p_attribute11 => l_attribute11,
1733 p_attribute8 => l_attribute8,
1734 p_attribute9 => l_attribute9,
1735 p_attribute10 => l_attribute10,
1737 p_attribute12 => l_attribute12,
1738 p_attribute13 => l_attribute13,
1739 p_attribute14 => l_attribute14,
1740 p_attribute15 => l_attribute15
1741 );
1742 --- If any errors happen abort API
1743 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1745 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1746 RAISE FND_API.G_EXC_ERROR;
1747 END IF;
1748
1749 IF (l_debug = 'Y') THEN
1750 Okc_Debug.Log('7500: Filling WHO columns', 2);
1751 END IF;
1752
1753 -- Filling who columns
1754 l_last_update_date := SYSDATE;
1755 l_last_updated_by := FND_GLOBAL.USER_ID;
1756 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1757
1758 -- Object version increment
1759 IF Nvl(l_object_version_number, 0) >= 0 THEN
1760 l_object_version_number := Nvl(p_object_version_number, 0) + 1;
1761 END IF;
1762
1763 --------------------------------------------
1764 -- Call the Update_Row for each child record
1765 --------------------------------------------
1766 IF (l_debug = 'Y') THEN
1767 Okc_Debug.Log('7600: Updating Row', 2);
1768 END IF;
1769
1770 x_return_status := Update_Row(
1771 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
1772 p_template_id => l_template_id,
1773 p_document_type => l_document_type,
1774 p_default_yn => l_default_yn,
1775 p_attribute_category => l_attribute_category,
1776 p_attribute1 => l_attribute1,
1777 p_attribute2 => l_attribute2,
1778 p_attribute3 => l_attribute3,
1779 p_attribute4 => l_attribute4,
1780 p_attribute5 => l_attribute5,
1781 p_attribute6 => l_attribute6,
1782 p_attribute7 => l_attribute7,
1783 p_attribute8 => l_attribute8,
1784 p_attribute9 => l_attribute9,
1785 p_attribute10 => l_attribute10,
1786 p_attribute11 => l_attribute11,
1787 p_attribute12 => l_attribute12,
1791 p_object_version_number => l_object_version_number,
1788 p_attribute13 => l_attribute13,
1789 p_attribute14 => l_attribute14,
1790 p_attribute15 => l_attribute15,
1792 p_last_updated_by => l_last_updated_by,
1793 p_last_update_login => l_last_update_login,
1794 p_last_update_date => l_last_update_date
1795 );
1796 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1798 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1799 RAISE FND_API.G_EXC_ERROR;
1800 END IF;
1801
1802
1803 IF (l_debug = 'Y') THEN
1804 Okc_Debug.Log('7800: Leaving Update_Row', 2);
1805 END IF;
1806
1807 EXCEPTION
1808 WHEN FND_API.G_EXC_ERROR THEN
1809 IF (l_debug = 'Y') THEN
1810 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
1811 END IF;
1812 x_return_status := G_RET_STS_ERROR;
1813
1814 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1815 IF (l_debug = 'Y') THEN
1816 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1817 END IF;
1818 x_return_status := G_RET_STS_UNEXP_ERROR;
1819
1820 WHEN OTHERS THEN
1821 IF (l_debug = 'Y') THEN
1822 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
1823 END IF;
1824 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1825 p_msg_name => G_UNEXPECTED_ERROR,
1826 p_token1 => G_SQLCODE_TOKEN,
1827 p_token1_value => sqlcode,
1828 p_token2 => G_SQLERRM_TOKEN,
1829 p_token2_value => sqlerrm);
1830 x_return_status := G_RET_STS_UNEXP_ERROR;
1831
1832 END Update_Row;
1833
1834 ---------------------------------------------------------------------------
1835 -- PROCEDURE Delete_Row
1836 ---------------------------------------------------------------------------
1837 -------------------------------------
1838 -- Delete_Row for:OKC_ALLOWED_TMPL_USAGES --
1839 -------------------------------------
1840 FUNCTION Delete_Row(
1841 p_allowed_tmpl_usages_id IN NUMBER
1842 ) RETURN VARCHAR2 IS
1843
1844 BEGIN
1845
1846 IF (l_debug = 'Y') THEN
1847 Okc_Debug.Log('8200: Entered Delete_Row', 2);
1848 END IF;
1849
1850 DELETE FROM OKC_ALLOWED_TMPL_USAGES
1851 WHERE ALLOWED_TMPL_USAGES_ID = p_ALLOWED_TMPL_USAGES_ID;
1852
1853 IF (l_debug = 'Y') THEN
1854 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
1855 END IF;
1856
1857 RETURN( G_RET_STS_SUCCESS );
1858
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861
1862 IF (l_debug = 'Y') THEN
1863 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1864 END IF;
1865
1866 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1867 p_msg_name => G_UNEXPECTED_ERROR,
1868 p_token1 => G_SQLCODE_TOKEN,
1869 p_token1_value => sqlcode,
1870 p_token2 => G_SQLERRM_TOKEN,
1871 p_token2_value => sqlerrm);
1872
1873 RETURN( G_RET_STS_UNEXP_ERROR );
1874
1875 END Delete_Row;
1876
1877 -------------------------------------
1878 -- Delete_Row for:OKC_ALLOWED_TMPL_USAGES --
1879 -------------------------------------
1880 PROCEDURE Delete_Row(
1881 x_return_status OUT NOCOPY VARCHAR2,
1882 p_allowed_tmpl_usages_id IN NUMBER,
1883 p_object_version_number IN NUMBER
1884 ) IS
1885 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
1886 BEGIN
1887
1888 IF (l_debug = 'Y') THEN
1889 Okc_Debug.Log('8800: Entered Delete_Row', 2);
1890 Okc_Debug.Log('8900: Locking _B row', 2);
1891 END IF;
1892
1893 x_return_status := Lock_row(
1894 p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id,
1895 p_object_version_number => p_object_version_number
1896 );
1897 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1899 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1900 RAISE FND_API.G_EXC_ERROR;
1901 END IF;
1902
1903
1904 IF (l_debug = 'Y') THEN
1905 Okc_Debug.Log('9100: Removing _B row', 2);
1906 END IF;
1907 x_return_status := Delete_Row( p_allowed_tmpl_usages_id => p_allowed_tmpl_usages_id );
1908 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1910 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1911 RAISE FND_API.G_EXC_ERROR;
1912 END IF;
1913
1914
1915 IF (l_debug = 'Y') THEN
1916 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
1917 END IF;
1918
1919 EXCEPTION
1920 WHEN FND_API.G_EXC_ERROR THEN
1921 IF (l_debug = 'Y') THEN
1922 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
1923 END IF;
1924 x_return_status := G_RET_STS_ERROR;
1925
1926 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1927 IF (l_debug = 'Y') THEN
1928 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1929 END IF;
1930 x_return_status := G_RET_STS_UNEXP_ERROR;
1931
1932 WHEN OTHERS THEN
1933 IF (l_debug = 'Y') THEN
1934 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
1935 END IF;
1936 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1940 p_token2 => G_SQLERRM_TOKEN,
1937 p_msg_name => G_UNEXPECTED_ERROR,
1938 p_token1 => G_SQLCODE_TOKEN,
1939 p_token1_value => sqlcode,
1941 p_token2_value => sqlerrm);
1942 x_return_status := G_RET_STS_UNEXP_ERROR;
1943
1944 END Delete_Row;
1945
1946 END OKC_ALLOWED_TMPL_USAGES_PVT;