[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_SECTIONS_PVT
Source
1 PACKAGE BODY OKC_TERMS_SECTIONS_PVT AS
2 /* $Header: OKCVSCNB.pls 120.1 2005/10/07 07:05:23 ndoddi 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 G_LOCK_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_LOCK_RECORD_DELETED;
20 ---------------------------------------------------------------------------
21 -- VALIDATION LEVELS
22 ---------------------------------------------------------------------------
23 G_REQUIRED_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_REQUIRED_VALUE_VALID_LEVEL;
24 G_VALID_VALUE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_VALID_VALUE_VALID_LEVEL;
25 G_LOOKUP_CODE_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_LOOKUP_CODE_VALID_LEVEL;
26 G_FOREIGN_KEY_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_FOREIGN_KEY_VALID_LEVEL;
27 G_RECORD_VALID_LEVEL CONSTANT NUMBER := OKC_API.G_RECORD_VALID_LEVEL;
28 ---------------------------------------------------------------------------
29 -- GLOBAL VARIABLES
30 ---------------------------------------------------------------------------
31 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_SECTIONS_PVT';
32 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
33
34 ------------------------------------------------------------------------------
35 -- GLOBAL CONSTANTS
36 ------------------------------------------------------------------------------
37 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
38 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
39 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
40 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
41 G_MISS_DATE CONSTANT DATE := FND_API.G_MISS_DATE;
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 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
46 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
47 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
48
49 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
50 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
51 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
52
53 E_Resource_Busy EXCEPTION;
54 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
55
56 ---------------------------------------------------------------------------
57 -- FUNCTION get_seq_id
58 ---------------------------------------------------------------------------
59 FUNCTION Get_Seq_Id (
60 p_id IN NUMBER,
61 x_id OUT NOCOPY NUMBER
62 ) RETURN VARCHAR2 IS
63 CURSOR l_seq_csr IS
64 SELECT OKC_SECTIONS_B_S.NEXTVAL FROM DUAL;
65 BEGIN
66 /*IF (l_debug = 'Y') THEN
67 Okc_Debug.Log('100: Entered get_seq_id', 2);
68 END IF;*/
69
70 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
71 FND_LOG.STRING(G_PROC_LEVEL,
72 G_PKG_NAME, '100: Entered get_seq_id' );
73 END IF;
74
75 IF( p_id IS NULL ) THEN
76 OPEN l_seq_csr;
77 FETCH l_seq_csr INTO x_id ;
78 IF l_seq_csr%NOTFOUND THEN
79 RAISE NO_DATA_FOUND;
80 END IF;
81 CLOSE l_seq_csr;
82 END IF;
83
84 /*IF (l_debug = 'Y') THEN
85 Okc_Debug.Log('200: Leaving get_seq_id', 2);
86 END IF;*/
87
88 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
89 FND_LOG.STRING(G_PROC_LEVEL,
90 G_PKG_NAME, '200: Leaving get_seq_id' );
91 END IF;
92 RETURN G_RET_STS_SUCCESS;
93 EXCEPTION
94 WHEN OTHERS THEN
95
96 /*IF (l_debug = 'Y') THEN
97 Okc_Debug.Log('300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm, 2);
98 END IF;*/
99
100 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
101 FND_LOG.STRING(G_EXCP_LEVEL,
102 G_PKG_NAME, '300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm );
103 END IF;
104
105 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
106 p_msg_name => G_UNEXPECTED_ERROR,
107 p_token1 => G_SQLCODE_TOKEN,
108 p_token1_value => sqlcode,
109 p_token2 => G_SQLERRM_TOKEN,
110 p_token2_value => sqlerrm);
111
112 IF l_seq_csr%ISOPEN THEN
113 CLOSE l_seq_csr;
114 END IF;
115
116 RETURN G_RET_STS_UNEXP_ERROR ;
117
118 END Get_Seq_Id;
119
120 ---------------------------------------------------------------------------
121 -- FUNCTION get_rec for: OKC_SECTIONS_B
122 ---------------------------------------------------------------------------
123 FUNCTION Get_Rec (
124 p_id IN NUMBER,
125
126 x_section_sequence OUT NOCOPY NUMBER,
127 x_label OUT NOCOPY VARCHAR2,
128 x_scn_id OUT NOCOPY NUMBER,
129 x_heading OUT NOCOPY VARCHAR2,
130 x_description OUT NOCOPY VARCHAR2,
131 x_document_type OUT NOCOPY VARCHAR2,
132 x_document_id OUT NOCOPY NUMBER,
133 x_scn_code OUT NOCOPY VARCHAR2,
134 x_amendment_description OUT NOCOPY VARCHAR2,
135 x_amendment_operation_code OUT NOCOPY VARCHAR2,
136 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
137 x_orig_system_reference_id1 OUT NOCOPY NUMBER,
138 x_orig_system_reference_id2 OUT NOCOPY NUMBER,
139 x_print_yn OUT NOCOPY VARCHAR2,
140 x_attribute_category OUT NOCOPY VARCHAR2,
141 x_attribute1 OUT NOCOPY VARCHAR2,
142 x_attribute2 OUT NOCOPY VARCHAR2,
143 x_attribute3 OUT NOCOPY VARCHAR2,
144 x_attribute4 OUT NOCOPY VARCHAR2,
145 x_attribute5 OUT NOCOPY VARCHAR2,
146 x_attribute6 OUT NOCOPY VARCHAR2,
147 x_attribute7 OUT NOCOPY VARCHAR2,
148 x_attribute8 OUT NOCOPY VARCHAR2,
149 x_attribute9 OUT NOCOPY VARCHAR2,
150 x_attribute10 OUT NOCOPY VARCHAR2,
151 x_attribute11 OUT NOCOPY VARCHAR2,
152 x_attribute12 OUT NOCOPY VARCHAR2,
153 x_attribute13 OUT NOCOPY VARCHAR2,
154 x_attribute14 OUT NOCOPY VARCHAR2,
155 x_attribute15 OUT NOCOPY VARCHAR2,
156 x_summary_amend_operation_code OUT NOCOPY VARCHAR2,
157 x_object_version_number OUT NOCOPY NUMBER,
158 x_created_by OUT NOCOPY NUMBER,
159 x_creation_date OUT NOCOPY DATE,
160 x_last_updated_by OUT NOCOPY NUMBER,
161 x_last_update_login OUT NOCOPY NUMBER,
162 x_last_update_date OUT NOCOPY DATE,
163 x_last_amended_by OUT NOCOPY NUMBER,
164 x_last_amendment_date OUT NOCOPY DATE
165
166 ) RETURN VARCHAR2 IS
167 CURSOR OKC_SECTIONS_B_pk_csr (cp_id IN NUMBER) IS
168 SELECT
169 SECTION_SEQUENCE,
170 LABEL,
171 SCN_ID,
172 HEADING,
173 DESCRIPTION,
174 DOCUMENT_TYPE,
175 DOCUMENT_ID,
176 SCN_CODE,
177 AMENDMENT_DESCRIPTION,
178 AMENDMENT_OPERATION_CODE,
179 ORIG_SYSTEM_REFERENCE_CODE,
180 ORIG_SYSTEM_REFERENCE_ID1,
181 ORIG_SYSTEM_REFERENCE_ID2,
182 PRINT_YN,
183 ATTRIBUTE_CATEGORY,
184 ATTRIBUTE1,
185 ATTRIBUTE2,
186 ATTRIBUTE3,
187 ATTRIBUTE4,
188 ATTRIBUTE5,
189 ATTRIBUTE6,
190 ATTRIBUTE7,
191 ATTRIBUTE8,
192 ATTRIBUTE9,
193 ATTRIBUTE10,
194 ATTRIBUTE11,
195 ATTRIBUTE12,
196 ATTRIBUTE13,
197 ATTRIBUTE14,
198 ATTRIBUTE15,
199 SUMMARY_AMEND_OPERATION_CODE,
200 OBJECT_VERSION_NUMBER,
201 CREATED_BY,
202 CREATION_DATE,
203 LAST_UPDATED_BY,
204 LAST_UPDATE_LOGIN,
205 LAST_UPDATE_DATE,
206 LAST_AMENDED_BY,
207 LAST_AMENDMENT_DATE
208 FROM OKC_SECTIONS_B t
209 WHERE t.ID = cp_id;
210 BEGIN
211
212 /*IF (l_debug = 'Y') THEN
213 Okc_Debug.Log('400: Entered get_rec', 2);
214 END IF;*/
215
216 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
217 FND_LOG.STRING(G_PROC_LEVEL,
218 G_PKG_NAME, '400: Entered get_rec' );
219 END IF;
220
221 -- Get current database values
222 OPEN OKC_SECTIONS_B_pk_csr (p_id);
223 FETCH OKC_SECTIONS_B_pk_csr INTO
224 x_section_sequence,
225 x_label,
226 x_scn_id,
227 x_heading,
228 x_description,
229 x_document_type,
230 x_document_id,
231 x_scn_code,
232 x_amendment_description,
233 x_amendment_operation_code,
234 x_orig_system_reference_code,
235 x_orig_system_reference_id1,
236 x_orig_system_reference_id2,
237 x_print_yn,
238 x_attribute_category,
239 x_attribute1,
240 x_attribute2,
241 x_attribute3,
242 x_attribute4,
243 x_attribute5,
244 x_attribute6,
245 x_attribute7,
246 x_attribute8,
247 x_attribute9,
248 x_attribute10,
249 x_attribute11,
250 x_attribute12,
251 x_attribute13,
252 x_attribute14,
253 x_attribute15,
254 x_summary_amend_operation_code,
255 x_object_version_number,
256 x_created_by,
257 x_creation_date,
258 x_last_updated_by,
259 x_last_update_login,
260 x_last_update_date,
261 x_last_amended_by,
262 x_last_amendment_date;
263 IF OKC_SECTIONS_B_pk_csr%NOTFOUND THEN
264 RAISE NO_DATA_FOUND;
265 END IF;
266 CLOSE OKC_SECTIONS_B_pk_csr;
267
268 /*IF (l_debug = 'Y') THEN
269 Okc_Debug.Log('500: Leaving get_rec ', 2);
270 END IF;*/
271
272 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
273 FND_LOG.STRING(G_PROC_LEVEL,
274 G_PKG_NAME, '500: Leaving get_rec ' );
275 END IF;
276
277 RETURN G_RET_STS_SUCCESS ;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281
282 /*IF (l_debug = 'Y') THEN
283 Okc_Debug.Log('600: Leaving get_rec because of EXCEPTION: '||sqlerrm, 2);
284 END IF;*/
285
286 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
287 FND_LOG.STRING(G_EXCP_LEVEL,
288 G_PKG_NAME, '600: Leaving get_rec because of EXCEPTION: '||sqlerrm);
289 END IF;
290
291 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
292 p_msg_name => G_UNEXPECTED_ERROR,
293 p_token1 => G_SQLCODE_TOKEN,
294 p_token1_value => sqlcode,
295 p_token2 => G_SQLERRM_TOKEN,
296 p_token2_value => sqlerrm);
297
298 IF OKC_SECTIONS_B_pk_csr%ISOPEN THEN
299 CLOSE OKC_SECTIONS_B_pk_csr;
300 END IF;
301
302 RETURN G_RET_STS_UNEXP_ERROR ;
303
304 END Get_Rec;
305
306 -----------------------------------------
307 -- Set_Attributes for:OKC_SECTIONS_B --
308 -----------------------------------------
309 FUNCTION Set_Attributes(
310 p_id IN NUMBER,
311 p_section_sequence IN NUMBER,
312 p_label IN VARCHAR2,
313 p_scn_id IN NUMBER,
314 p_heading IN VARCHAR2,
315 p_description IN VARCHAR2,
316 p_document_type IN VARCHAR2,
317 p_document_id IN NUMBER,
318 p_scn_code IN VARCHAR2,
319 p_amendment_description IN VARCHAR2,
320 p_amendment_operation_code IN VARCHAR2,
321 p_orig_system_reference_code IN VARCHAR2,
322 p_orig_system_reference_id1 IN NUMBER,
326 p_attribute1 IN VARCHAR2,
323 p_orig_system_reference_id2 IN NUMBER,
324 p_print_yn IN VARCHAR2,
325 p_attribute_category IN VARCHAR2,
327 p_attribute2 IN VARCHAR2,
328 p_attribute3 IN VARCHAR2,
329 p_attribute4 IN VARCHAR2,
330 p_attribute5 IN VARCHAR2,
331 p_attribute6 IN VARCHAR2,
332 p_attribute7 IN VARCHAR2,
333 p_attribute8 IN VARCHAR2,
334 p_attribute9 IN VARCHAR2,
335 p_attribute10 IN VARCHAR2,
336 p_attribute11 IN VARCHAR2,
337 p_attribute12 IN VARCHAR2,
338 p_attribute13 IN VARCHAR2,
339 p_attribute14 IN VARCHAR2,
340 p_attribute15 IN VARCHAR2,
341 p_summary_amend_operation_code IN VARCHAR2,
342 p_object_version_number IN NUMBER,
343 p_last_amended_by IN NUMBER,
344 p_last_amendment_date IN DATE,
345 x_section_sequence OUT NOCOPY NUMBER,
346 x_label OUT NOCOPY VARCHAR2,
347 x_scn_id OUT NOCOPY NUMBER,
348 x_heading OUT NOCOPY VARCHAR2,
349 x_object_version_number OUT NOCOPY VARCHAR2,
350 x_description OUT NOCOPY VARCHAR2,
351 x_document_type OUT NOCOPY VARCHAR2,
352 x_document_id OUT NOCOPY NUMBER,
353 x_scn_code OUT NOCOPY VARCHAR2,
354 x_amendment_description OUT NOCOPY VARCHAR2,
355 x_amendment_operation_code OUT NOCOPY VARCHAR2,
356 x_orig_system_reference_code OUT NOCOPY VARCHAR2,
357 x_orig_system_reference_id1 OUT NOCOPY NUMBER,
358 x_orig_system_reference_id2 OUT NOCOPY NUMBER,
359 x_print_yn OUT NOCOPY VARCHAR2,
360 x_attribute_category OUT NOCOPY VARCHAR2,
361 x_attribute1 OUT NOCOPY VARCHAR2,
362 x_attribute2 OUT NOCOPY VARCHAR2,
363 x_attribute3 OUT NOCOPY VARCHAR2,
364 x_attribute4 OUT NOCOPY VARCHAR2,
365 x_attribute5 OUT NOCOPY VARCHAR2,
366 x_attribute6 OUT NOCOPY VARCHAR2,
367 x_attribute7 OUT NOCOPY VARCHAR2,
368 x_attribute8 OUT NOCOPY VARCHAR2,
369 x_attribute9 OUT NOCOPY VARCHAR2,
370 x_attribute10 OUT NOCOPY VARCHAR2,
371 x_attribute11 OUT NOCOPY VARCHAR2,
372 x_attribute12 OUT NOCOPY VARCHAR2,
373 x_attribute13 OUT NOCOPY VARCHAR2,
374 x_attribute14 OUT NOCOPY VARCHAR2,
375 x_attribute15 OUT NOCOPY VARCHAR2,
376 x_summary_amend_operation_code OUT NOCOPY VARCHAR2,
377 x_last_amended_by OUT NOCOPY NUMBER,
378 x_last_amendment_date OUT NOCOPY DATE
379 ) RETURN VARCHAR2 IS
380 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
381 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE;
382 l_created_by OKC_SECTIONS_B.CREATED_BY%TYPE;
383 l_creation_date OKC_SECTIONS_B.CREATION_DATE%TYPE;
384 l_last_updated_by OKC_SECTIONS_B.LAST_UPDATED_BY%TYPE;
385 l_last_update_login OKC_SECTIONS_B.LAST_UPDATE_LOGIN%TYPE;
386 l_last_update_date OKC_SECTIONS_B.LAST_UPDATE_DATE%TYPE;
387 BEGIN
388 /*IF (l_debug = 'Y') THEN
389 Okc_Debug.Log('700: Entered Set_Attributes ', 2);
390 END IF;*/
391
392 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
393 FND_LOG.STRING(G_PROC_LEVEL,
394 G_PKG_NAME, '700: Entered Set_Attributes ' );
395 END IF;
396
397 IF( p_id IS NOT NULL ) THEN
398 -- Get current database values
399 l_return_status := Get_Rec(
400 p_id => p_id,
401 x_section_sequence => x_section_sequence,
402 x_label => x_label,
403 x_scn_id => x_scn_id,
404 x_heading => x_heading,
405 x_description => x_description,
406 x_document_type => x_document_type,
407 x_document_id => x_document_id,
408 x_scn_code => x_scn_code,
409 x_amendment_description => x_amendment_description,
410 x_amendment_operation_code => x_amendment_operation_code,
411 x_orig_system_reference_code => x_orig_system_reference_code,
412 x_orig_system_reference_id1 => x_orig_system_reference_id1,
413 x_orig_system_reference_id2 => x_orig_system_reference_id2,
414 x_print_yn => x_print_yn,
415 x_attribute_category => x_attribute_category,
416 x_attribute1 => x_attribute1,
417 x_attribute2 => x_attribute2,
418 x_attribute3 => x_attribute3,
419 x_attribute4 => x_attribute4,
420 x_attribute5 => x_attribute5,
421 x_attribute6 => x_attribute6,
422 x_attribute7 => x_attribute7,
423 x_attribute8 => x_attribute8,
424 x_attribute9 => x_attribute9,
425 x_attribute10 => x_attribute10,
426 x_attribute11 => x_attribute11,
427 x_attribute12 => x_attribute12,
428 x_attribute13 => x_attribute13,
429 x_attribute14 => x_attribute14,
430 x_attribute15 => x_attribute15,
431 x_summary_amend_operation_code => x_summary_amend_operation_code,
432 x_object_version_number => x_object_version_number,
433 x_created_by => l_created_by,
434 x_creation_date => l_creation_date,
435 x_last_updated_by => l_last_updated_by,
436 x_last_update_login => l_last_update_login,
437 x_last_update_date => l_last_update_date,
438 x_last_amended_by => x_last_amended_by,
439 x_last_amendment_date => x_last_amendment_date
440 );
441 --- If any errors happen abort API
442 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
444 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
445 RAISE FND_API.G_EXC_ERROR;
446 END IF;
447
448 --- Reversing G_MISS/NULL values logic
449
450 IF (p_section_sequence = G_MISS_NUM) THEN
451 x_section_sequence := NULL;
452 ELSIF (p_SECTION_SEQUENCE IS NOT NULL) THEN
453 x_section_sequence := p_section_sequence;
454 END IF;
455
456 IF (p_label = G_MISS_CHAR) THEN
457 x_label := NULL;
458 ELSIF (p_LABEL IS NOT NULL) THEN
459 x_label := p_label;
460 END IF;
461
462 IF (p_scn_id = G_MISS_NUM) THEN
463 x_scn_id := NULL;
464 ELSIF (p_SCN_ID IS NOT NULL) THEN
465 x_scn_id := p_scn_id;
466 END IF;
467
468 IF (p_heading = G_MISS_CHAR) THEN
469 x_heading := NULL;
470 ELSIF (p_HEADING IS NOT NULL) THEN
471 x_heading := p_heading;
472 END IF;
473
474 IF (p_description = G_MISS_CHAR) THEN
475 x_description := NULL;
476 ELSIF (p_description IS NOT NULL) THEN
477 x_description := p_description;
478 END IF;
479
480 IF (p_document_type = G_MISS_CHAR) THEN
481 x_document_type := NULL;
482 ELSIF (p_DOCUMENT_TYPE IS NOT NULL) THEN
483 x_document_type := p_document_type;
484 END IF;
485
486 IF (p_document_id = G_MISS_NUM) THEN
487 x_document_id := NULL;
488 ELSIF (p_DOCUMENT_ID IS NOT NULL) THEN
489 x_document_id := p_document_id;
490 END IF;
491
492 IF (p_scn_code = G_MISS_CHAR) THEN
493 x_scn_code := NULL;
494 ELSIF (p_SCN_CODE IS NOT NULL) THEN
495 x_scn_code := p_scn_code;
496 END IF;
497
498 IF (p_amendment_description = G_MISS_CHAR) THEN
499 x_amendment_description := NULL;
500 ELSIF (p_AMENDMENT_DESCRIPTION IS NOT NULL) THEN
501 x_amendment_description := p_amendment_description;
502 END IF;
503
504 IF (p_amendment_operation_code = G_MISS_CHAR) THEN
505 x_amendment_operation_code := NULL;
506 ELSIF (p_AMENDMENT_OPERATION_CODE IS NOT NULL) THEN
507 x_amendment_operation_code := p_amendment_operation_code;
508 END IF;
509
510 IF p_amendment_operation_code IS NOT NULL
514 x_last_amendment_date := Sysdate;
511 AND p_amendment_operation_code <> G_MISS_CHAR
512 AND x_amendment_operation_code IS NOT NULL
513 THEN
515 x_last_amended_by := Fnd_Global.User_Id;
516 END IF;
517
518 IF (p_last_amended_by = G_MISS_NUM) THEN
519 x_last_amended_by := NULL;
520 END IF;
521
522 IF (p_last_amendment_date = G_MISS_DATE) THEN
523 x_last_amendment_date := NULL;
524 END IF;
525
526 IF (p_orig_system_reference_code = G_MISS_CHAR) THEN
527 x_orig_system_reference_code := NULL;
528 ELSIF (p_ORIG_SYSTEM_REFERENCE_CODE IS NOT NULL) THEN
529 x_orig_system_reference_code := p_orig_system_reference_code;
530 END IF;
531
532 IF (p_orig_system_reference_id1 = G_MISS_NUM) THEN
533 x_orig_system_reference_id1 := NULL;
534 ELSIF (p_ORIG_SYSTEM_REFERENCE_ID1 IS NOT NULL) THEN
535 x_orig_system_reference_id1 := p_orig_system_reference_id1;
536 END IF;
537
538 IF (p_orig_system_reference_id2 = G_MISS_NUM) THEN
539 x_orig_system_reference_id2 := NULL;
540 ELSIF (p_ORIG_SYSTEM_REFERENCE_ID2 IS NOT NULL) THEN
541 x_orig_system_reference_id2 := p_orig_system_reference_id2;
542 END IF;
543
544 IF nvl(p_scn_code,'?')='UNASSIGNED' THEN
545 x_print_yn:='N';
546 ELSE
547 x_print_yn:='Y';
548 END IF;
549
550 IF (p_attribute_category = G_MISS_CHAR) THEN
551 x_attribute_category := NULL;
552 ELSIF (p_ATTRIBUTE_CATEGORY IS NOT NULL) THEN
553 x_attribute_category := p_attribute_category;
554 END IF;
555
556 IF (p_attribute1 = G_MISS_CHAR) THEN
557 x_attribute1 := NULL;
558 ELSIF (p_ATTRIBUTE1 IS NOT NULL) THEN
559 x_attribute1 := p_attribute1;
560 END IF;
561
562 IF (p_attribute2 = G_MISS_CHAR) THEN
563 x_attribute2 := NULL;
564 ELSIF (p_ATTRIBUTE2 IS NOT NULL) THEN
565 x_attribute2 := p_attribute2;
566 END IF;
567
568 IF (p_attribute3 = G_MISS_CHAR) THEN
569 x_attribute3 := NULL;
570 ELSIF (p_ATTRIBUTE3 IS NOT NULL) THEN
571 x_attribute3 := p_attribute3;
572 END IF;
573
574 IF (p_attribute4 = G_MISS_CHAR) THEN
575 x_attribute4 := NULL;
576 ELSIF (p_ATTRIBUTE4 IS NOT NULL) THEN
577 x_attribute4 := p_attribute4;
578 END IF;
579
580 IF (p_attribute5 = G_MISS_CHAR) THEN
581 x_attribute5 := NULL;
582 ELSIF (p_ATTRIBUTE5 IS NOT NULL) THEN
583 x_attribute5 := p_attribute5;
584 END IF;
585
586 IF (p_attribute6 = G_MISS_CHAR) THEN
587 x_attribute6 := NULL;
588 ELSIF (p_ATTRIBUTE6 IS NOT NULL) THEN
589 x_attribute6 := p_attribute6;
590 END IF;
591
592 IF (p_attribute7 = G_MISS_CHAR) THEN
593 x_attribute7 := NULL;
594 ELSIF (p_ATTRIBUTE7 IS NOT NULL) THEN
595 x_attribute7 := p_attribute7;
596 END IF;
597
598 IF (p_attribute8 = G_MISS_CHAR) THEN
599 x_attribute8 := NULL;
600 ELSIF (p_ATTRIBUTE8 IS NOT NULL) THEN
601 x_attribute8 := p_attribute8;
602 END IF;
603
604 IF (p_attribute9 = G_MISS_CHAR) THEN
605 x_attribute9 := NULL;
606 ELSIF (p_ATTRIBUTE9 IS NOT NULL) THEN
607 x_attribute9 := p_attribute9;
608 END IF;
609
610 IF (p_attribute10 = G_MISS_CHAR) THEN
611 x_attribute10 := NULL;
612 ELSIF (p_ATTRIBUTE10 IS NOT NULL) THEN
613 x_attribute10 := p_attribute10;
614 END IF;
615
616 IF (p_attribute11 = G_MISS_CHAR) THEN
617 x_attribute11 := NULL;
618 ELSIF (p_ATTRIBUTE11 IS NOT NULL) THEN
619 x_attribute11 := p_attribute11;
620 END IF;
621
622 IF (p_attribute12 = G_MISS_CHAR) THEN
623 x_attribute12 := NULL;
624 ELSIF (p_ATTRIBUTE12 IS NOT NULL) THEN
625 x_attribute12 := p_attribute12;
626 END IF;
627
628 IF (p_attribute13 = G_MISS_CHAR) THEN
629 x_attribute13 := NULL;
630 ELSIF (p_ATTRIBUTE13 IS NOT NULL) THEN
631 x_attribute13 := p_attribute13;
632 END IF;
633
634 IF (p_attribute14 = G_MISS_CHAR) THEN
635 x_attribute14 := NULL;
636 ELSIF (p_ATTRIBUTE14 IS NOT NULL) THEN
637 x_attribute14 := p_attribute14;
638 END IF;
639
640 IF (p_attribute15 = G_MISS_CHAR) THEN
641 x_attribute15 := NULL;
642 ELSIF (p_ATTRIBUTE15 IS NOT NULL) THEN
643 x_attribute15 := p_attribute15;
644 END IF;
645
646 IF (p_summary_amend_operation_code = G_MISS_CHAR) THEN
647 x_summary_amend_operation_code := NULL;
648 ELSIF (p_summary_amend_operation_code IS NOT NULL) THEN
649 x_summary_amend_operation_code := p_summary_amend_operation_code;
650 END IF;
651 END IF;
652
653 /*IF (l_debug = 'Y') THEN
654 Okc_Debug.Log('800: Leaving Set_Attributes ', 2);
655 END IF;*/
656
657 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
658 FND_LOG.STRING(G_PROC_LEVEL,
662 RETURN G_RET_STS_SUCCESS ;
659 G_PKG_NAME, '800: Leaving Set_Attributes ' );
660 END IF;
661
663 EXCEPTION
664 WHEN FND_API.G_EXC_ERROR THEN
665 /*IF (l_debug = 'Y') THEN
666 Okc_Debug.Log('900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception', 2);
667 END IF;*/
668
669 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
670 FND_LOG.STRING(G_EXCP_LEVEL,
671 G_PKG_NAME, '900: Leaving Set_Attributes:FND_API.G_EXC_ERROR Exception');
672 END IF;
673 RETURN G_RET_STS_ERROR;
674
675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676 /*IF (l_debug = 'Y') THEN
677 Okc_Debug.Log('1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
678 END IF;*/
679
680 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
681 FND_LOG.STRING(G_EXCP_LEVEL,
682 G_PKG_NAME, '1000: Leaving Set_Attributes:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
683 END IF;
684 RETURN G_RET_STS_UNEXP_ERROR;
685
686 WHEN OTHERS THEN
687 /*IF (l_debug = 'Y') THEN
688 Okc_Debug.Log('1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm, 2);
689 END IF;*/
690
691 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
692 FND_LOG.STRING(G_EXCP_LEVEL,
693 G_PKG_NAME, '1100: Leaving Set_Attributes because of EXCEPTION: '||sqlerrm);
694 END IF;
695 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
696 p_msg_name => G_UNEXPECTED_ERROR,
697 p_token1 => G_SQLCODE_TOKEN,
698 p_token1_value => sqlcode,
699 p_token2 => G_SQLERRM_TOKEN,
700 p_token2_value => sqlerrm);
701 RETURN G_RET_STS_UNEXP_ERROR;
702
703 END Set_Attributes ;
704
705 ----------------------------------------------
706 -- Validate_Attributes for: OKC_SECTIONS_B --
707 ----------------------------------------------
708 FUNCTION Validate_Attributes (
709 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
710
711 p_id IN NUMBER,
712 p_section_sequence IN NUMBER,
713 p_label IN VARCHAR2,
714 p_scn_id IN NUMBER,
715 p_heading IN VARCHAR2,
716 p_description IN VARCHAR2,
717 p_document_type IN VARCHAR2,
718 p_document_id IN NUMBER,
719 p_scn_code IN VARCHAR2,
720 p_amendment_description IN VARCHAR2,
721 p_amendment_operation_code IN VARCHAR2,
722 p_orig_system_reference_code IN VARCHAR2,
723 p_orig_system_reference_id1 IN NUMBER,
724 p_orig_system_reference_id2 IN NUMBER,
725 p_print_yn IN VARCHAR2,
726 p_attribute_category IN VARCHAR2,
727 p_attribute1 IN VARCHAR2,
728 p_attribute2 IN VARCHAR2,
729 p_attribute3 IN VARCHAR2,
730 p_attribute4 IN VARCHAR2,
731 p_attribute5 IN VARCHAR2,
732 p_attribute6 IN VARCHAR2,
733 p_attribute7 IN VARCHAR2,
734 p_attribute8 IN VARCHAR2,
735 p_attribute9 IN VARCHAR2,
736 p_attribute10 IN VARCHAR2,
737 p_attribute11 IN VARCHAR2,
738 p_attribute12 IN VARCHAR2,
739 p_attribute13 IN VARCHAR2,
740 p_attribute14 IN VARCHAR2,
741 p_attribute15 IN VARCHAR2,
742 p_summary_amend_operation_code IN VARCHAR2
743 ) RETURN VARCHAR2 IS
744 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
745 l_dummy_var VARCHAR2(1) := '?';
746
747 CURSOR l_scn_id_csr is
748 SELECT '!'
749 FROM OKC_SECTIONS_B
750 WHERE ID = p_scn_id;
751
752 CURSOR l_doc_type_csr is
753 SELECT '!'
754 FROM OKC_BUS_DOC_TYPES_V
755 WHERE document_type = p_document_type;
756
757 BEGIN
758
759 /*IF (l_debug = 'Y') THEN
760 Okc_Debug.Log('1200: Entered Validate_Attributes', 2);
761 END IF;*/
762
763 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
764 FND_LOG.STRING(G_PROC_LEVEL,
765 G_PKG_NAME, '1200: Entered Validate_Attributes' );
766 END IF;
767
768 IF p_validation_level > G_REQUIRED_VALUE_VALID_LEVEL THEN
769 /*IF (l_debug = 'Y') THEN
770 Okc_Debug.Log('1300: required values validation', 2);
771 END IF;*/
772
773 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
774 FND_LOG.STRING(G_PROC_LEVEL,
775 G_PKG_NAME, '1300: required values validation' );
776 END IF;
777
778 /*IF (l_debug = 'Y') THEN
779 Okc_Debug.Log('1400: - attribute ID ', 2);
780 END IF;*/
781
782 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
783 FND_LOG.STRING(G_PROC_LEVEL,
784 G_PKG_NAME, '1400: - attribute ID ' );
785 END IF;
786
787 IF ( p_id IS NULL) THEN
788 /*IF (l_debug = 'Y') THEN
789 Okc_Debug.Log('1500: - attribute ID is invalid', 2);
790 END IF;*/
791
792 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
793 FND_LOG.STRING(G_PROC_LEVEL,
797 l_return_status := G_RET_STS_ERROR;
794 G_PKG_NAME, '1500: - attribute ID is invalid' );
795 END IF;
796 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'ID');
798 END IF;
799
800 /*IF (l_debug = 'Y') THEN
801 Okc_Debug.Log('1400: - attribute DOCUMENT_TYPE ', 2);
802 END IF;*/
803
804 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
805 FND_LOG.STRING(G_PROC_LEVEL,
806 G_PKG_NAME, '1400: - attribute DOCUMENT_TYPE ' );
807 END IF;
808 IF ( p_document_type IS NULL) THEN
809 /*IF (l_debug = 'Y') THEN
810 Okc_Debug.Log('1500: - attribute DOCUMENT_TYPE is invalid', 2);
811 END IF;*/
812
813 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
814 FND_LOG.STRING(G_PROC_LEVEL,
815 G_PKG_NAME, '1500: - attribute DOCUMENT_TYPE is invalid' );
816 END IF;
817 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'DOCUMENT_TYPE');
818 l_return_status := G_RET_STS_ERROR;
819 END IF;
820
821 /*IF (l_debug = 'Y') THEN
822 Okc_Debug.Log('1400: - attribute DOCUMENT_ID ', 2);
823 END IF;*/
824
825 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
826 FND_LOG.STRING(G_PROC_LEVEL,
827 G_PKG_NAME, '1400: - attribute DOCUMENT_ID ' );
828 END IF;
829 IF ( p_document_id IS NULL) THEN
830 /*IF (l_debug = 'Y') THEN
831 Okc_Debug.Log('1500: - attribute DOCUMENT_ID is invalid', 2);
832 END IF;*/
833
834 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
835 FND_LOG.STRING(G_PROC_LEVEL,
836 G_PKG_NAME, '1500: - attribute DOCUMENT_ID is invalid' );
837 END IF;
838 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'DOCUMENT_ID');
839 l_return_status := G_RET_STS_ERROR;
840 END IF;
841
842 /*IF (l_debug = 'Y') THEN
843 Okc_Debug.Log('1400: - attribute SECTION_SEQUENCE ', 2);
844 END IF;*/
845
846 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
847 FND_LOG.STRING(G_PROC_LEVEL,
848 G_PKG_NAME, '1400: - attribute SECTION_SEQUENCE ' );
849 END IF;
850 IF ( p_section_sequence IS NULL) THEN
851 /*IF (l_debug = 'Y') THEN
852 Okc_Debug.Log('1500: - attribute SECTION_SEQUENCE is invalid', 2);
853 END IF;*/
854
855 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
856 FND_LOG.STRING(G_PROC_LEVEL,
857 G_PKG_NAME, '1500: - attribute SECTION_SEQUENCE is invalid' );
858 END IF;
859 Okc_Api.Set_Message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'SECTION_SEQUENCE');
860 l_return_status := G_RET_STS_ERROR;
861 END IF;
862
863 END IF;
864
865 IF p_validation_level > G_VALID_VALUE_VALID_LEVEL THEN
866 /*IF (l_debug = 'Y') THEN
867 Okc_Debug.Log('1600: static values and range validation', 2);
868 END IF;*/
869
870 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
871 FND_LOG.STRING(G_PROC_LEVEL,
872 G_PKG_NAME, '1600: static values and range validation' );
873 END IF;
874
875 /*IF (l_debug = 'Y') THEN
876 Okc_Debug.Log('1700: - attribute PRINT_YN ', 2);
877 END IF;*/
878
879 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
880 FND_LOG.STRING(G_PROC_LEVEL,
881 G_PKG_NAME, '1700: - attribute PRINT_YN ' );
882 END IF;
883 IF ( p_print_yn NOT IN ('Y','N') AND p_print_yn IS NOT NULL) THEN
884 /*IF (l_debug = 'Y') THEN
885 Okc_Debug.Log('1800: - attribute PRINT_YN is invalid', 2);
886 END IF;*/
887
888 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
889 FND_LOG.STRING(G_PROC_LEVEL,
890 G_PKG_NAME, '1800: - attribute PRINT_YN is invalid' );
891 END IF;
892 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'PRINT_YN');
893 l_return_status := G_RET_STS_ERROR;
894 END IF;
895
896 END IF;
897
898 IF p_validation_level > G_LOOKUP_CODE_VALID_LEVEL THEN
899 /*IF (l_debug = 'Y') THEN
900 Okc_Debug.Log('1900: lookup codes validation', 2);
901 END IF;*/
902
903 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
904 FND_LOG.STRING(G_PROC_LEVEL,
905 G_PKG_NAME, '1900: lookup codes validation' );
906 END IF;
907
908
909 /*IF (l_debug = 'Y') THEN
910 Okc_Debug.Log('2000: - attribute SCN_CODE ', 2);
911 END IF;*/
912
913 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
914 FND_LOG.STRING(G_PROC_LEVEL,
915 G_PKG_NAME, '2000: - attribute SCN_CODE ' );
916 END IF;
917
918 /* bug 3676155
919
920 IF p_scn_code IS NOT NULL THEN
921 l_return_status := Okc_Util.Check_Lookup_Code('OKC_ARTICLE_SECTION',p_scn_code);
922 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
923 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'SCN_CODE');
924 l_return_status := G_RET_STS_ERROR;
925 END IF;
926 END IF;
927 */
928
929 /*IF (l_debug = 'Y') THEN
930 Okc_Debug.Log('2000: - attribute AMENDMENT_OPERATION_CODE ', 2);
934 FND_LOG.STRING(G_PROC_LEVEL,
931 END IF;*/
932
933 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
935 G_PKG_NAME, '2000: - attribute AMENDMENT_OPERATION_CODE ' );
936 END IF;
937 IF p_amendment_operation_code IS NOT NULL THEN
938 l_return_status := Okc_Util.Check_Lookup_Code('OKC_AMEND_OPN_CODE',p_amendment_operation_code);
939 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
940 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'AMENDMENT_OPERATION_CODE');
941 l_return_status := G_RET_STS_ERROR;
942 END IF;
943 END IF;
944
945 END IF;
946
947 IF p_validation_level > G_FOREIGN_KEY_VALID_LEVEL THEN
948 /*IF (l_debug = 'Y') THEN
949 Okc_Debug.Log('2100: foreigh keys validation ', 2);
950 END IF;*/
951
952 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
953 FND_LOG.STRING(G_PROC_LEVEL,
954 G_PKG_NAME, '2100: foreigh keys validation ' );
955 END IF;
956
957 /*IF (l_debug = 'Y') THEN
958 Okc_Debug.Log('2190: - attribute DOCUMENT_TYPE ', 2);
959 END IF;*/
960
961 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
962 FND_LOG.STRING(G_PROC_LEVEL,
963 G_PKG_NAME, '2190: - attribute DOCUMENT_TYPE ' );
964 END IF;
965
966 IF p_document_type IS NOT NULL THEN
967 l_dummy_var := '?';
968 OPEN l_doc_type_csr;
969 FETCH l_doc_type_csr INTO l_dummy_var;
970 CLOSE l_doc_type_csr;
971 IF (l_dummy_var = '?') THEN
972 /*IF (l_debug = 'Y') THEN
973 Okc_Debug.Log('2300: - attribute DOCUMENT_TYPE is invalid', 2);
974 END IF;*/
975
976 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
977 FND_LOG.STRING(G_PROC_LEVEL,
978 G_PKG_NAME, '2300: - attribute DOCUMENT_TYPE is invalid' );
979 END IF;
980
981 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DOCUMENT_TYPE');
982 l_return_status := G_RET_STS_ERROR;
983 END IF;
984 END IF;
985
986 /*IF (l_debug = 'Y') THEN
987 Okc_Debug.Log('2200: - attribute SCN_ID ', 2);
988 END IF;*/
989
990 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
991 FND_LOG.STRING(G_PROC_LEVEL,
992 G_PKG_NAME, '2200: - attribute SCN_ID ' );
993 END IF;
994
995 IF p_scn_id IS NOT NULL THEN
996 l_dummy_var := '?';
997 OPEN l_scn_id_csr;
998 FETCH l_scn_id_csr INTO l_dummy_var;
999 CLOSE l_scn_id_csr;
1000 IF (l_dummy_var = '?') THEN
1001 /*IF (l_debug = 'Y') THEN
1002 Okc_Debug.Log('2300: - attribute SCN_ID is invalid', 2);
1003 END IF;*/
1004
1005 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1006 FND_LOG.STRING(G_PROC_LEVEL,
1007 G_PKG_NAME, '2300: - attribute SCN_ID is invalid' );
1008 END IF;
1009 Okc_Api.Set_Message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SCN_ID');
1010 l_return_status := G_RET_STS_ERROR;
1011 END IF;
1012 END IF;
1013
1014 END IF;
1015
1016
1017 /*IF (l_debug = 'Y') THEN
1018 Okc_Debug.Log('2400: Leaving Validate_Attributes ', 2);
1019 END IF;*/
1020
1021 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1022 FND_LOG.STRING(G_PROC_LEVEL,
1023 G_PKG_NAME, '2400: Leaving Validate_Attributes ' );
1024 END IF;
1025
1026 RETURN l_return_status;
1027
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 --Okc_Debug.Log('2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm, 2);
1031 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1032 FND_LOG.STRING(G_EXCP_LEVEL,
1033 G_PKG_NAME, '2500: Leaving Validate_Attributes because of EXCEPTION: '||sqlerrm);
1034 END IF;
1035 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1036 p_msg_name => G_UNEXPECTED_ERROR,
1037 p_token1 => G_SQLCODE_TOKEN,
1038 p_token1_value => sqlcode,
1042
1039 p_token2 => G_SQLERRM_TOKEN,
1040 p_token2_value => sqlerrm);
1041
1043
1044 IF l_scn_id_csr%ISOPEN THEN
1045 CLOSE l_scn_id_csr;
1046 END IF;
1047
1048 IF l_doc_type_csr%ISOPEN THEN
1049 CLOSE l_doc_type_csr;
1050 END IF;
1051
1052 RETURN G_RET_STS_UNEXP_ERROR;
1053
1054 END Validate_Attributes;
1055
1056
1057 ---------------------------------------------------------------------------
1058 -- PROCEDURE Validate_Record
1059 -- It calls Item Level Validations and then makes Record Level Validations
1060 ---------------------------------------------------------------------------
1061 ------------------------------------------
1062 -- Validate_Record for:OKC_SECTIONS_B --
1063 ------------------------------------------
1064 FUNCTION Validate_Record (
1065 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1066 p_id IN NUMBER,
1067 p_section_sequence IN NUMBER,
1068 p_label IN VARCHAR2,
1069 p_scn_id IN NUMBER,
1070 p_heading IN VARCHAR2,
1071 p_description IN VARCHAR2,
1072 p_document_type IN VARCHAR2,
1073 p_document_id IN NUMBER,
1074 p_scn_code IN VARCHAR2,
1075 p_amendment_description IN VARCHAR2,
1076 p_amendment_operation_code IN VARCHAR2,
1077 p_orig_system_reference_code IN VARCHAR2,
1078 p_orig_system_reference_id1 IN NUMBER,
1079 p_orig_system_reference_id2 IN NUMBER,
1080 p_print_yn IN VARCHAR2,
1081 p_attribute_category IN VARCHAR2,
1082 p_attribute1 IN VARCHAR2,
1083 p_attribute2 IN VARCHAR2,
1084 p_attribute3 IN VARCHAR2,
1085 p_attribute4 IN VARCHAR2,
1086 p_attribute5 IN VARCHAR2,
1087 p_attribute6 IN VARCHAR2,
1088 p_attribute7 IN VARCHAR2,
1089 p_attribute8 IN VARCHAR2,
1090 p_attribute9 IN VARCHAR2,
1091 p_attribute10 IN VARCHAR2,
1092 p_attribute11 IN VARCHAR2,
1093 p_attribute12 IN VARCHAR2,
1094 p_attribute13 IN VARCHAR2,
1095 p_attribute14 IN VARCHAR2,
1096 p_attribute15 IN VARCHAR2,
1097 p_summary_amend_operation_code IN VARCHAR2
1098 ) RETURN VARCHAR2 IS
1099 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1100 BEGIN
1101
1102 /*IF (l_debug = 'Y') THEN
1103 Okc_Debug.Log('2600: Entered Validate_Record', 2);
1104 END IF;*/
1105
1106 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1107 FND_LOG.STRING(G_PROC_LEVEL,
1108 G_PKG_NAME, '2600: Entered Validate_Record' );
1109 END IF;
1110
1111 --- Validate all non-missing attributes (Item Level Validation)
1112 l_return_status := Validate_Attributes(
1113 p_validation_level => p_validation_level,
1114 p_id => p_id,
1115 p_section_sequence => p_section_sequence,
1116 p_label => p_label,
1117 p_scn_id => p_scn_id,
1118 p_heading => p_heading,
1119 p_description => p_description,
1120 p_document_type => p_document_type,
1121 p_document_id => p_document_id,
1122 p_scn_code => p_scn_code,
1123 p_amendment_description => p_amendment_description,
1124 p_amendment_operation_code => p_amendment_operation_code,
1125 p_orig_system_reference_code => p_orig_system_reference_code,
1126 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1127 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1128 p_print_yn => p_print_yn,
1129 p_attribute_category => p_attribute_category,
1130 p_attribute1 => p_attribute1,
1131 p_attribute2 => p_attribute2,
1132 p_attribute3 => p_attribute3,
1133 p_attribute4 => p_attribute4,
1134 p_attribute5 => p_attribute5,
1135 p_attribute6 => p_attribute6,
1136 p_attribute7 => p_attribute7,
1137 p_attribute8 => p_attribute8,
1138 p_attribute9 => p_attribute9,
1139 p_attribute10 => p_attribute10,
1140 p_attribute11 => p_attribute11,
1141 p_attribute12 => p_attribute12,
1142 p_attribute13 => p_attribute13,
1143 p_attribute14 => p_attribute14,
1144 p_attribute15 => p_attribute15,
1145 p_summary_amend_operation_code => p_summary_amend_operation_code
1146 );
1147 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1148 /*IF (l_debug = 'Y') THEN
1149 Okc_Debug.Log('2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm, 2);
1150 END IF;*/
1151
1152 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1153 FND_LOG.STRING(G_PROC_LEVEL,
1154 G_PKG_NAME, '2700: Leaving Validate_Record because of UNEXP_ERROR in Validate_Attributes: '||sqlerrm );
1155 END IF;
1156 RETURN G_RET_STS_UNEXP_ERROR;
1160 IF p_validation_level > G_RECORD_VALID_LEVEL THEN
1157 END IF;
1158
1159 --- Record Level Validation
1161 /*IF (l_debug = 'Y') THEN
1162 Okc_Debug.Log('2800: Entered Record Level Validations', 2);
1163 END IF;*/
1164
1165 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1166 FND_LOG.STRING(G_PROC_LEVEL,
1167 G_PKG_NAME, '2800: Entered Record Level Validations' );
1168 END IF;
1169 /*+++++++++++++start of hand code +++++++++++++++++++*/
1170 -- ?? manual coding for Record Level Validations if required ??
1171 /*+++++++++++++End of hand code +++++++++++++++++++*/
1172 END IF;
1173
1174 /*IF (l_debug = 'Y') THEN
1175 Okc_Debug.Log('2900: Leaving Validate_Record : '||sqlerrm, 2);
1176 END IF;*/
1177
1178 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1179 FND_LOG.STRING(G_PROC_LEVEL,
1180 G_PKG_NAME, '2900: Leaving Validate_Record : '||sqlerrm);
1181 END IF;
1182 RETURN l_return_status ;
1183
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186
1187 /*IF (l_debug = 'Y') THEN
1188 Okc_Debug.Log('3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm, 2);
1189 END IF;*/
1190
1191 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1192 FND_LOG.STRING(G_EXCP_LEVEL,
1193 G_PKG_NAME, '3000: Leaving Validate_Record because of EXCEPTION: '||sqlerrm );
1194 END IF;
1195
1196 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1197 p_msg_name => G_UNEXPECTED_ERROR,
1198 p_token1 => G_SQLCODE_TOKEN,
1199 p_token1_value => sqlcode,
1200 p_token2 => G_SQLERRM_TOKEN,
1201 p_token2_value => sqlerrm);
1202 RETURN G_RET_STS_UNEXP_ERROR ;
1203
1204 END Validate_Record;
1205
1206 ---------------------------------------------------------------------------
1207 -- PROCEDURE validate_row
1208 ---------------------------------------------------------------------------
1209 ---------------------------------------
1210 -- validate_row for:OKC_SECTIONS_B --
1211 ---------------------------------------
1212 PROCEDURE validate_row(
1213 p_validation_level IN NUMBER,
1214 x_return_status OUT NOCOPY VARCHAR2,
1215 p_id IN NUMBER,
1216 p_section_sequence IN NUMBER,
1217 p_label IN VARCHAR2,
1218 p_scn_id IN NUMBER,
1219 p_heading IN VARCHAR2,
1220 p_description IN VARCHAR2,
1221 p_document_type IN VARCHAR2,
1222 p_document_id IN NUMBER,
1223 p_scn_code IN VARCHAR2,
1224 p_amendment_description IN VARCHAR2,
1225 p_amendment_operation_code IN VARCHAR2,
1226 p_orig_system_reference_code IN VARCHAR2,
1227 p_orig_system_reference_id1 IN NUMBER,
1228 p_orig_system_reference_id2 IN NUMBER,
1229 p_print_yn IN VARCHAR2,
1230 p_attribute_category IN VARCHAR2,
1231 p_attribute1 IN VARCHAR2,
1232 p_attribute2 IN VARCHAR2,
1233 p_attribute3 IN VARCHAR2,
1234 p_attribute4 IN VARCHAR2,
1235 p_attribute5 IN VARCHAR2,
1236 p_attribute6 IN VARCHAR2,
1237 p_attribute7 IN VARCHAR2,
1238 p_attribute8 IN VARCHAR2,
1239 p_attribute9 IN VARCHAR2,
1240 p_attribute10 IN VARCHAR2,
1241 p_attribute11 IN VARCHAR2,
1242 p_attribute12 IN VARCHAR2,
1243 p_attribute13 IN VARCHAR2,
1244 p_attribute14 IN VARCHAR2,
1245 p_attribute15 IN VARCHAR2,
1246 p_summary_amend_operation_code IN VARCHAR2,
1247 p_object_version_number IN NUMBER
1248 ) IS
1249 l_section_sequence OKC_SECTIONS_B.SECTION_SEQUENCE%TYPE;
1250 l_label OKC_SECTIONS_B.LABEL%TYPE;
1251 l_scn_id OKC_SECTIONS_B.SCN_ID%TYPE;
1252 l_heading OKC_SECTIONS_B.HEADING%TYPE;
1253 l_description OKC_SECTIONS_B.DESCRIPTION%TYPE;
1254 l_document_type OKC_SECTIONS_B.DOCUMENT_TYPE%TYPE;
1255 l_document_id OKC_SECTIONS_B.DOCUMENT_ID%TYPE;
1256 l_scn_code OKC_SECTIONS_B.SCN_CODE%TYPE;
1257 l_amendment_description OKC_SECTIONS_B.AMENDMENT_DESCRIPTION%TYPE;
1258 l_amendment_operation_code OKC_SECTIONS_B.AMENDMENT_OPERATION_CODE%TYPE;
1259 l_orig_system_reference_code OKC_SECTIONS_B.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
1260 l_orig_system_reference_id1 OKC_SECTIONS_B.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
1261 l_orig_system_reference_id2 OKC_SECTIONS_B.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
1262 l_print_yn OKC_SECTIONS_B.PRINT_YN%TYPE;
1263 l_attribute_category OKC_SECTIONS_B.ATTRIBUTE_CATEGORY%TYPE;
1264 l_attribute1 OKC_SECTIONS_B.ATTRIBUTE1%TYPE;
1265 l_attribute2 OKC_SECTIONS_B.ATTRIBUTE2%TYPE;
1266 l_attribute3 OKC_SECTIONS_B.ATTRIBUTE3%TYPE;
1267 l_attribute4 OKC_SECTIONS_B.ATTRIBUTE4%TYPE;
1268 l_attribute5 OKC_SECTIONS_B.ATTRIBUTE5%TYPE;
1272 l_attribute9 OKC_SECTIONS_B.ATTRIBUTE9%TYPE;
1269 l_attribute6 OKC_SECTIONS_B.ATTRIBUTE6%TYPE;
1270 l_attribute7 OKC_SECTIONS_B.ATTRIBUTE7%TYPE;
1271 l_attribute8 OKC_SECTIONS_B.ATTRIBUTE8%TYPE;
1273 l_attribute10 OKC_SECTIONS_B.ATTRIBUTE10%TYPE;
1274 l_attribute11 OKC_SECTIONS_B.ATTRIBUTE11%TYPE;
1275 l_attribute12 OKC_SECTIONS_B.ATTRIBUTE12%TYPE;
1276 l_attribute13 OKC_SECTIONS_B.ATTRIBUTE13%TYPE;
1277 l_attribute14 OKC_SECTIONS_B.ATTRIBUTE14%TYPE;
1278 l_attribute15 OKC_SECTIONS_B.ATTRIBUTE15%TYPE;
1279 l_summary_amend_operation_code OKC_SECTIONS_B.summary_amend_operation_code%TYPE;
1280 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE;
1281 l_created_by OKC_SECTIONS_B.CREATED_BY%TYPE;
1282 l_creation_date OKC_SECTIONS_B.CREATION_DATE%TYPE;
1283 l_last_updated_by OKC_SECTIONS_B.LAST_UPDATED_BY%TYPE;
1284 l_last_update_login OKC_SECTIONS_B.LAST_UPDATE_LOGIN%TYPE;
1285 l_last_update_date OKC_SECTIONS_B.LAST_UPDATE_DATE%TYPE;
1286 l_last_amended_by OKC_SECTIONS_B.LAST_AMENDED_BY%TYPE;
1287 l_last_amendment_date OKC_SECTIONS_B.LAST_AMENDMENT_DATE%TYPE;
1288 BEGIN
1289
1290 /*IF (l_debug = 'Y') THEN
1291 Okc_Debug.Log('3100: Entered validate_row', 2);
1292 END IF;*/
1293
1294 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1295 FND_LOG.STRING(G_PROC_LEVEL,
1296 G_PKG_NAME, '3100: Entered validate_row');
1297 END IF;
1298
1299 -- Setting attributes
1300 x_return_status := Set_Attributes(
1301 p_id => p_id,
1302 p_section_sequence => p_section_sequence,
1303 p_label => p_label,
1304 p_scn_id => p_scn_id,
1305 p_heading => p_heading,
1306 p_description => p_description,
1307 p_document_type => p_document_type,
1308 p_document_id => p_document_id,
1309 p_scn_code => p_scn_code,
1310 p_amendment_description => p_amendment_description,
1311 p_amendment_operation_code => p_amendment_operation_code,
1312 p_orig_system_reference_code => p_orig_system_reference_code,
1313 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1314 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1315 p_print_yn => p_print_yn,
1316 p_attribute_category => p_attribute_category,
1317 p_attribute1 => p_attribute1,
1318 p_attribute2 => p_attribute2,
1319 p_attribute3 => p_attribute3,
1320 p_attribute4 => p_attribute4,
1321 p_attribute5 => p_attribute5,
1322 p_attribute6 => p_attribute6,
1323 p_attribute7 => p_attribute7,
1324 p_attribute8 => p_attribute8,
1325 p_attribute9 => p_attribute9,
1326 p_attribute10 => p_attribute10,
1327 p_attribute11 => p_attribute11,
1328 p_attribute12 => p_attribute12,
1329 p_attribute13 => p_attribute13,
1330 p_attribute14 => p_attribute14,
1331 p_attribute15 => p_attribute15,
1332 p_summary_amend_operation_code => p_summary_amend_operation_code,
1333 p_object_version_number => p_object_version_number,
1334 p_last_amended_by => NULL,
1335 p_last_amendment_date => NULL,
1336 x_section_sequence => l_section_sequence,
1337 x_label => l_label,
1338 x_scn_id => l_scn_id,
1339 x_heading => l_heading,
1340 x_object_version_number => l_object_version_number,
1341 x_description => l_description,
1342 x_document_type => l_document_type,
1343 x_document_id => l_document_id,
1344 x_scn_code => l_scn_code,
1345 x_amendment_description => l_amendment_description,
1346 x_amendment_operation_code => l_amendment_operation_code,
1347 x_orig_system_reference_code => l_orig_system_reference_code,
1348 x_orig_system_reference_id1 => l_orig_system_reference_id1,
1349 x_orig_system_reference_id2 => l_orig_system_reference_id2,
1350 x_print_yn => l_print_yn,
1351 x_attribute_category => l_attribute_category,
1352 x_attribute1 => l_attribute1,
1353 x_attribute2 => l_attribute2,
1354 x_attribute3 => l_attribute3,
1355 x_attribute4 => l_attribute4,
1356 x_attribute5 => l_attribute5,
1357 x_attribute6 => l_attribute6,
1358 x_attribute7 => l_attribute7,
1359 x_attribute8 => l_attribute8,
1360 x_attribute9 => l_attribute9,
1361 x_attribute10 => l_attribute10,
1362 x_attribute11 => l_attribute11,
1363 x_attribute12 => l_attribute12,
1364 x_attribute13 => l_attribute13,
1365 x_attribute14 => l_attribute14,
1369 x_last_amendment_date => l_last_amendment_date
1366 x_attribute15 => l_attribute15,
1367 x_summary_amend_operation_code => l_summary_amend_operation_code,
1368 x_last_amended_by => l_last_amended_by,
1370 );
1371 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1373 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1374 RAISE FND_API.G_EXC_ERROR;
1375 END IF;
1376
1377 -- Validate all non-missing attributes (Item Level Validation)
1378 x_return_status := Validate_Record(
1379 p_validation_level => p_validation_level,
1380 p_id => p_id,
1381 p_section_sequence => l_section_sequence,
1382 p_label => l_label,
1383 p_scn_id => l_scn_id,
1384 p_heading => l_heading,
1385 p_description => l_description,
1386 p_document_type => l_document_type,
1387 p_document_id => l_document_id,
1388 p_scn_code => l_scn_code,
1389 p_amendment_description => l_amendment_description,
1390 p_amendment_operation_code => l_amendment_operation_code,
1391 p_orig_system_reference_code => l_orig_system_reference_code,
1392 p_orig_system_reference_id1 => l_orig_system_reference_id1,
1393 p_orig_system_reference_id2 => l_orig_system_reference_id2,
1394 p_print_yn => l_print_yn,
1395 p_attribute_category => l_attribute_category,
1396 p_attribute1 => l_attribute1,
1397 p_attribute2 => l_attribute2,
1398 p_attribute3 => l_attribute3,
1399 p_attribute4 => l_attribute4,
1400 p_attribute5 => l_attribute5,
1401 p_attribute6 => l_attribute6,
1402 p_attribute7 => l_attribute7,
1403 p_attribute8 => l_attribute8,
1404 p_attribute9 => l_attribute9,
1405 p_attribute10 => l_attribute10,
1406 p_attribute11 => l_attribute11,
1407 p_attribute12 => l_attribute12,
1408 p_attribute13 => l_attribute13,
1409 p_attribute14 => l_attribute14,
1410 p_attribute15 => l_attribute15,
1411 p_summary_amend_operation_code => l_summary_amend_operation_code
1412 );
1413
1414 /*IF (l_debug = 'Y') THEN
1415 Okc_Debug.Log('3200: Leaving validate_row', 2);
1416 END IF;*/
1417
1418 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1419 FND_LOG.STRING(G_PROC_LEVEL,
1420 G_PKG_NAME, '3200: Leaving validate_row' );
1421 END IF;
1422
1423 EXCEPTION
1424 WHEN FND_API.G_EXC_ERROR THEN
1425 /*IF (l_debug = 'Y') THEN
1426 Okc_Debug.Log('3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception', 2);
1427 END IF;*/
1428
1429 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1430 FND_LOG.STRING(G_EXCP_LEVEL,
1431 G_PKG_NAME, '3300: Leaving Validate_Row:FND_API.G_EXC_ERROR Exception');
1432 END IF;
1433 x_return_status := G_RET_STS_ERROR;
1434
1435 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1436 /*IF (l_debug = 'Y') THEN
1437 Okc_Debug.Log('3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1438 END IF;*/
1439
1440 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1441 FND_LOG.STRING(G_EXCP_LEVEL,
1442 G_PKG_NAME, '3400: Leaving Validate_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1443 END IF;
1444 x_return_status := G_RET_STS_UNEXP_ERROR;
1445
1446 WHEN OTHERS THEN
1447 /*IF (l_debug = 'Y') THEN
1448 Okc_Debug.Log('3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm, 2);
1449 END IF;*/
1450
1451 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1452 FND_LOG.STRING(G_EXCP_LEVEL,
1453 G_PKG_NAME, '3500: Leaving Validate_Row because of EXCEPTION: '||sqlerrm);
1454 END IF;
1455 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1456 p_msg_name => G_UNEXPECTED_ERROR,
1457 p_token1 => G_SQLCODE_TOKEN,
1458 p_token1_value => sqlcode,
1459 p_token2 => G_SQLERRM_TOKEN,
1460 p_token2_value => sqlerrm);
1461 x_return_status := G_RET_STS_UNEXP_ERROR;
1462
1463 END Validate_Row;
1464
1465 ---------------------------------------------------------------------------
1466 -- PROCEDURE Insert_Row
1467 ---------------------------------------------------------------------------
1468 -------------------------------------
1469 -- Insert_Row for:OKC_SECTIONS_B --
1470 -------------------------------------
1471 FUNCTION Insert_Row(
1472 p_id IN NUMBER,
1473 p_section_sequence IN NUMBER,
1474 p_label IN VARCHAR2,
1475 p_scn_id IN NUMBER,
1476 p_heading IN VARCHAR2,
1477 p_description IN VARCHAR2,
1478 p_document_type IN VARCHAR2,
1479 p_document_id IN NUMBER,
1480 p_scn_code IN VARCHAR2,
1481 p_amendment_description IN VARCHAR2,
1482 p_amendment_operation_code IN VARCHAR2,
1483 p_orig_system_reference_code IN VARCHAR2,
1487 p_attribute_category IN VARCHAR2,
1484 p_orig_system_reference_id1 IN NUMBER,
1485 p_orig_system_reference_id2 IN NUMBER,
1486 p_print_yn IN VARCHAR2,
1488 p_attribute1 IN VARCHAR2,
1489 p_attribute2 IN VARCHAR2,
1490 p_attribute3 IN VARCHAR2,
1491 p_attribute4 IN VARCHAR2,
1492 p_attribute5 IN VARCHAR2,
1493 p_attribute6 IN VARCHAR2,
1494 p_attribute7 IN VARCHAR2,
1495 p_attribute8 IN VARCHAR2,
1496 p_attribute9 IN VARCHAR2,
1497 p_attribute10 IN VARCHAR2,
1498 p_attribute11 IN VARCHAR2,
1499 p_attribute12 IN VARCHAR2,
1500 p_attribute13 IN VARCHAR2,
1501 p_attribute14 IN VARCHAR2,
1502 p_attribute15 IN VARCHAR2,
1503 p_summary_amend_operation_code IN VARCHAR2,
1504 p_object_version_number IN NUMBER,
1505 p_created_by IN NUMBER,
1506 p_creation_date IN DATE,
1507 p_last_updated_by IN NUMBER,
1508 p_last_update_login IN NUMBER,
1509 p_last_update_date IN DATE,
1510 p_last_amended_by IN NUMBER,
1511 p_last_amendment_date IN DATE
1512
1513 ) RETURN VARCHAR2 IS
1514
1515 l_chr_id NUMBER;
1516
1517 BEGIN
1518
1519 /*IF (l_debug = 'Y') THEN
1520 Okc_Debug.Log('3600: Entered Insert_Row function', 2);
1521 END IF;*/
1522
1523 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1524 FND_LOG.STRING(G_PROC_LEVEL,
1525 G_PKG_NAME, '3600: Entered Insert_Row function' );
1526 END IF;
1527 IF p_document_type IN ('OKC_SELL','OKC_BUY','OKO','OKS','OKE_SELL','OKE_BUY','OKL') THEN
1528 l_chr_id := okc_terms_util_pvt.get_chr_id_for_doc_id(p_document_id);
1529 END IF;
1530
1531 INSERT INTO OKC_SECTIONS_B(
1532 ID,
1533 SECTION_SEQUENCE,
1534 SCN_TYPE,
1535 CHR_ID,
1536 SAT_CODE,
1537 LABEL,
1538 SCN_ID,
1539 HEADING,
1540 DESCRIPTION,
1541 DOCUMENT_TYPE,
1542 DOCUMENT_ID,
1543 SCN_CODE,
1544 AMENDMENT_DESCRIPTION,
1545 AMENDMENT_OPERATION_CODE,
1546 ORIG_SYSTEM_REFERENCE_CODE,
1547 ORIG_SYSTEM_REFERENCE_ID1,
1548 ORIG_SYSTEM_REFERENCE_ID2,
1549 PRINT_YN,
1550 ATTRIBUTE_CATEGORY,
1551 ATTRIBUTE1,
1552 ATTRIBUTE2,
1553 ATTRIBUTE3,
1554 ATTRIBUTE4,
1555 ATTRIBUTE5,
1556 ATTRIBUTE6,
1557 ATTRIBUTE7,
1558 ATTRIBUTE8,
1559 ATTRIBUTE9,
1560 ATTRIBUTE10,
1561 ATTRIBUTE11,
1562 ATTRIBUTE12,
1563 ATTRIBUTE13,
1564 ATTRIBUTE14,
1565 ATTRIBUTE15,
1566 SUMMARY_AMEND_OPERATION_CODE,
1567 OBJECT_VERSION_NUMBER,
1568 CREATED_BY,
1569 CREATION_DATE,
1570 LAST_UPDATED_BY,
1571 LAST_UPDATE_LOGIN,
1572 LAST_UPDATE_DATE,
1573 LAST_AMENDED_BY,
1574 LAST_AMENDMENT_DATE)
1575 VALUES (
1576 p_id,
1577 p_section_sequence,
1578 Null,
1579 l_chr_id,
1580 Null,
1581 p_label,
1582 p_scn_id,
1583 p_heading,
1584 p_description,
1585 p_document_type,
1586 p_document_id,
1587 p_scn_code,
1588 p_amendment_description,
1589 p_amendment_operation_code,
1590 p_orig_system_reference_code,
1591 p_orig_system_reference_id1,
1592 p_orig_system_reference_id2,
1593 p_print_yn,
1594 p_attribute_category,
1595 p_attribute1,
1596 p_attribute2,
1597 p_attribute3,
1598 p_attribute4,
1599 p_attribute5,
1600 p_attribute6,
1601 p_attribute7,
1602 p_attribute8,
1603 p_attribute9,
1604 p_attribute10,
1605 p_attribute11,
1606 p_attribute12,
1607 p_attribute13,
1608 p_attribute14,
1609 p_attribute15,
1610 p_summary_amend_operation_code,
1611 p_object_version_number,
1612 p_created_by,
1613 p_creation_date,
1614 p_last_updated_by,
1615 p_last_update_login,
1616 p_last_update_date,
1617 p_last_amended_by,
1618 p_last_amendment_date);
1619
1620 /*IF (l_debug = 'Y') THEN
1621 Okc_Debug.Log('3700: Leaving Insert_Row', 2);
1622 END IF;*/
1623
1624 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1625 FND_LOG.STRING(G_PROC_LEVEL,
1626 G_PKG_NAME, '3700: Leaving Insert_Row' );
1627 END IF;
1628
1629 RETURN( G_RET_STS_SUCCESS );
1630
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633
1634 /*IF (l_debug = 'Y') THEN
1635 Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
1636 END IF;*/
1637
1638 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1639 FND_LOG.STRING(G_EXCP_LEVEL,
1640 G_PKG_NAME, '3800: Leaving Insert_Row:OTHERS Exception' );
1641 END IF;
1645 p_token1_value => sqlcode,
1642 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1643 p_msg_name => G_UNEXPECTED_ERROR,
1644 p_token1 => G_SQLCODE_TOKEN,
1646 p_token2 => G_SQLERRM_TOKEN,
1647 p_token2_value => sqlerrm);
1648
1649 RETURN( G_RET_STS_UNEXP_ERROR );
1650
1651 END Insert_Row;
1652
1653
1654 -------------------------------------
1655 -- Insert_Row for:OKC_SECTIONS_B --
1656 -------------------------------------
1657 PROCEDURE Insert_Row(
1658 p_validation_level IN NUMBER,
1659 x_return_status OUT NOCOPY VARCHAR2,
1660 p_id IN NUMBER,
1661 p_section_sequence IN NUMBER,
1662 p_label IN VARCHAR2,
1663 p_scn_id IN NUMBER,
1664 p_heading IN VARCHAR2,
1665 p_description IN VARCHAR2,
1666 p_document_type IN VARCHAR2,
1667 p_document_id IN NUMBER,
1668 p_scn_code IN VARCHAR2,
1669 p_amendment_description IN VARCHAR2,
1670 p_amendment_operation_code IN VARCHAR2,
1671 p_orig_system_reference_code IN VARCHAR2,
1672 p_orig_system_reference_id1 IN NUMBER,
1673 p_orig_system_reference_id2 IN NUMBER,
1674 p_print_yn IN VARCHAR2,
1675 p_attribute_category IN VARCHAR2,
1676 p_attribute1 IN VARCHAR2,
1677 p_attribute2 IN VARCHAR2,
1678 p_attribute3 IN VARCHAR2,
1679 p_attribute4 IN VARCHAR2,
1680 p_attribute5 IN VARCHAR2,
1681 p_attribute6 IN VARCHAR2,
1682 p_attribute7 IN VARCHAR2,
1683 p_attribute8 IN VARCHAR2,
1684 p_attribute9 IN VARCHAR2,
1685 p_attribute10 IN VARCHAR2,
1686 p_attribute11 IN VARCHAR2,
1687 p_attribute12 IN VARCHAR2,
1688 p_attribute13 IN VARCHAR2,
1689 p_attribute14 IN VARCHAR2,
1690 p_attribute15 IN VARCHAR2,
1691 p_summary_amend_operation_code IN VARCHAR2,
1692 x_id OUT NOCOPY NUMBER
1693
1694 ) IS
1695
1696 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE;
1697 l_created_by OKC_SECTIONS_B.CREATED_BY%TYPE;
1698 l_creation_date OKC_SECTIONS_B.CREATION_DATE%TYPE;
1699 l_last_updated_by OKC_SECTIONS_B.LAST_UPDATED_BY%TYPE;
1700 l_last_update_login OKC_SECTIONS_B.LAST_UPDATE_LOGIN%TYPE;
1701 l_last_update_date OKC_SECTIONS_B.LAST_UPDATE_DATE%TYPE;
1702 l_last_amended_by OKC_SECTIONS_B.LAST_AMENDED_BY%TYPE;
1703 l_last_amendment_date OKC_SECTIONS_B.LAST_AMENDMENT_DATE%TYPE;
1704 BEGIN
1705
1706 x_return_status := G_RET_STS_SUCCESS;
1707
1708 /*IF (l_debug = 'Y') THEN
1709 Okc_Debug.Log('4200: Entered Insert_Row', 2);
1710 END IF;*/
1711
1712 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1713 FND_LOG.STRING(G_PROC_LEVEL,
1714 G_PKG_NAME, '4200: Entered Insert_Row' );
1715 END IF;
1716
1717 --- Setting item attributes
1718 -- Set primary key value
1719 IF( p_id IS NULL ) THEN
1720 x_return_status := Get_Seq_Id(
1721 p_id => p_id,
1722 x_id => x_id
1723 );
1724 --- If any errors happen abort API
1725 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1727 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1728 RAISE FND_API.G_EXC_ERROR;
1729 END IF;
1730 ELSE
1731 x_id := p_id;
1732 END IF;
1733 -- Set Internal columns
1734 l_object_version_number := 1;
1735 l_creation_date := Sysdate;
1736 l_created_by := Fnd_Global.User_Id;
1737 l_last_update_date := l_creation_date;
1738 l_last_updated_by := l_created_by;
1739 l_last_update_login := Fnd_Global.Login_Id;
1740 IF p_amendment_operation_code IS NOT NULL THEN
1741 l_last_amendment_date := l_creation_date;
1742 l_last_amended_by := l_created_by;
1743 END IF;
1744
1745
1746 --- Validate all non-missing attributes
1747 x_return_status := Validate_Record(
1748 p_validation_level => p_validation_level,
1749 p_id => x_id,
1750 p_section_sequence => p_section_sequence,
1751 p_label => p_label,
1752 p_scn_id => p_scn_id,
1753 p_heading => p_heading,
1754 p_description => p_description,
1755 p_document_type => p_document_type,
1756 p_document_id => p_document_id,
1757 p_scn_code => p_scn_code,
1758 p_amendment_description => p_amendment_description,
1759 p_amendment_operation_code => p_amendment_operation_code,
1760 p_orig_system_reference_code => p_orig_system_reference_code,
1761 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1762 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1763 p_print_yn => p_print_yn,
1767 p_attribute3 => p_attribute3,
1764 p_attribute_category => p_attribute_category,
1765 p_attribute1 => p_attribute1,
1766 p_attribute2 => p_attribute2,
1768 p_attribute4 => p_attribute4,
1769 p_attribute5 => p_attribute5,
1770 p_attribute6 => p_attribute6,
1771 p_attribute7 => p_attribute7,
1772 p_attribute8 => p_attribute8,
1773 p_attribute9 => p_attribute9,
1774 p_attribute10 => p_attribute10,
1775 p_attribute11 => p_attribute11,
1776 p_attribute12 => p_attribute12,
1777 p_attribute13 => p_attribute13,
1778 p_attribute14 => p_attribute14,
1779 p_attribute15 => p_attribute15,
1780 p_summary_amend_operation_code => p_summary_amend_operation_code
1781 );
1782 --- If any errors happen abort API
1783 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1785 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1786 RAISE FND_API.G_EXC_ERROR;
1787 END IF;
1788
1789 --------------------------------------------
1790 -- Call the internal Insert_Row for each child record
1791 --------------------------------------------
1792 /*IF (l_debug = 'Y') THEN
1793 Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
1794 END IF;*/
1795
1796 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1797 FND_LOG.STRING(G_PROC_LEVEL,
1798 G_PKG_NAME, '4300: Call the internal Insert_Row for Base Table' );
1799 END IF;
1800
1801 x_return_status := Insert_Row(
1802 p_id => x_id,
1803 p_section_sequence => p_section_sequence,
1804 p_label => p_label,
1805 p_scn_id => p_scn_id,
1806 p_heading => p_heading,
1807 p_description => p_description,
1808 p_document_type => p_document_type,
1809 p_document_id => p_document_id,
1810 p_scn_code => p_scn_code,
1811 p_amendment_description => p_amendment_description,
1812 p_amendment_operation_code => p_amendment_operation_code,
1813 p_orig_system_reference_code => p_orig_system_reference_code,
1814 p_orig_system_reference_id1 => p_orig_system_reference_id1,
1815 p_orig_system_reference_id2 => p_orig_system_reference_id2,
1816 p_print_yn => p_print_yn,
1817 p_attribute_category => p_attribute_category,
1818 p_attribute1 => p_attribute1,
1819 p_attribute2 => p_attribute2,
1820 p_attribute3 => p_attribute3,
1821 p_attribute4 => p_attribute4,
1822 p_attribute5 => p_attribute5,
1823 p_attribute6 => p_attribute6,
1824 p_attribute7 => p_attribute7,
1825 p_attribute8 => p_attribute8,
1826 p_attribute9 => p_attribute9,
1827 p_attribute10 => p_attribute10,
1828 p_attribute11 => p_attribute11,
1829 p_attribute12 => p_attribute12,
1830 p_attribute13 => p_attribute13,
1831 p_attribute14 => p_attribute14,
1832 p_attribute15 => p_attribute15,
1833 p_summary_amend_operation_code => p_summary_amend_operation_code,
1834 p_object_version_number => l_object_version_number,
1835 p_created_by => l_created_by,
1836 p_creation_date => l_creation_date,
1837 p_last_updated_by => l_last_updated_by,
1838 p_last_update_login => l_last_update_login,
1839 p_last_update_date => l_last_update_date,
1840 p_last_amended_by => l_last_amended_by,
1841 p_last_amendment_date => l_last_amendment_date
1842 );
1843 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1845 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1846 RAISE FND_API.G_EXC_ERROR;
1847 END IF;
1848
1849
1850
1851 /*IF (l_debug = 'Y') THEN
1852 Okc_Debug.Log('4500: Leaving Insert_Row', 2);
1853 END IF;*/
1854
1855 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1856 FND_LOG.STRING(G_PROC_LEVEL,
1857 G_PKG_NAME, '4500: Leaving Insert_Row');
1858 END IF;
1859
1860 EXCEPTION
1861 WHEN FND_API.G_EXC_ERROR THEN
1862 /*IF (l_debug = 'Y') THEN
1863 Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
1864 END IF;*/
1865
1866 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1867 FND_LOG.STRING(G_EXCP_LEVEL,
1868 G_PKG_NAME, '4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception' );
1869 END IF;
1870 x_return_status := G_RET_STS_ERROR;
1871
1872 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1873 /*IF (l_debug = 'Y') THEN
1874 Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
1875 END IF;*/
1876
1877 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1878 FND_LOG.STRING(G_EXCP_LEVEL,
1882
1879 G_PKG_NAME, '4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
1880 END IF;
1881 x_return_status := G_RET_STS_UNEXP_ERROR;
1883 WHEN OTHERS THEN
1884 /*IF (l_debug = 'Y') THEN
1885 Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
1886 END IF;*/
1887
1888 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1889 FND_LOG.STRING(G_EXCP_LEVEL,
1890 G_PKG_NAME, '4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm);
1891 END IF;
1892 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1893 p_msg_name => G_UNEXPECTED_ERROR,
1894 p_token1 => G_SQLCODE_TOKEN,
1895 p_token1_value => sqlcode,
1896 p_token2 => G_SQLERRM_TOKEN,
1897 p_token2_value => sqlerrm);
1898 x_return_status := G_RET_STS_UNEXP_ERROR;
1899
1900 END Insert_Row;
1901 ---------------------------------------------------------------------------
1902 -- PROCEDURE Lock_Row
1903 ---------------------------------------------------------------------------
1904 -----------------------------------
1905 -- Lock_Row for:OKC_SECTIONS_B --
1906 -----------------------------------
1907 FUNCTION Lock_Row(
1908 p_id IN NUMBER,
1909 p_object_version_number IN NUMBER
1910 ) RETURN VARCHAR2 IS
1911
1912
1913 CURSOR lock_csr (cp_id NUMBER, cp_object_version_number NUMBER) IS
1914 SELECT object_version_number
1915 FROM OKC_SECTIONS_B
1916 WHERE ID = cp_id
1917 AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
1918 FOR UPDATE OF object_version_number NOWAIT;
1919
1920 CURSOR lchk_csr (cp_id NUMBER) IS
1921 SELECT object_version_number
1922 FROM OKC_SECTIONS_B
1923 WHERE ID = cp_id;
1924
1925 l_return_status VARCHAR2(1);
1926
1927 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE;
1928
1929 l_row_notfound BOOLEAN := FALSE;
1930 BEGIN
1931
1932 /*IF (l_debug = 'Y') THEN
1933 Okc_Debug.Log('4900: Entered Lock_Row', 2);
1934 END IF;*/
1935
1936 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1937 FND_LOG.STRING(G_PROC_LEVEL,
1938 G_PKG_NAME, '4900: Entered Lock_Row');
1939 END IF;
1940
1941 BEGIN
1942
1943 OPEN lock_csr( p_id, p_object_version_number );
1944 FETCH lock_csr INTO l_object_version_number;
1945 l_row_notfound := lock_csr%NOTFOUND;
1946 CLOSE lock_csr;
1947
1948 EXCEPTION
1949 WHEN E_Resource_Busy THEN
1950
1951 /*IF (l_debug = 'Y') THEN
1952 Okc_Debug.Log('5000: Leaving Lock_Row:E_Resource_Busy Exception', 2);
1953 END IF;*/
1954
1955 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1956 FND_LOG.STRING(G_EXCP_LEVEL,
1957 G_PKG_NAME, '5000: Leaving Lock_Row:E_Resource_Busy Exception' );
1958 END IF;
1959
1960 IF (lock_csr%ISOPEN) THEN
1961 CLOSE lock_csr;
1962 END IF;
1963 Okc_Api.Set_Message(G_FND_APP,G_UNABLE_TO_RESERVE_REC);
1964 RETURN( G_RET_STS_ERROR );
1965 END;
1966
1967 IF ( l_row_notfound ) THEN
1968 l_return_status := G_RET_STS_ERROR;
1969
1970 OPEN lchk_csr(p_id);
1971 FETCH lchk_csr INTO l_object_version_number;
1972 l_row_notfound := lchk_csr%NOTFOUND;
1973 CLOSE lchk_csr;
1974
1975 IF (l_row_notfound) THEN
1976 Okc_Api.Set_Message(G_APP_NAME,G_LOCK_RECORD_DELETED,
1977 'ENTITYNAME','OKC_SECTIONS_B',
1978 'PKEY',p_id,
1979 'OVN',p_object_version_number
1980 );
1981 ELSIF l_object_version_number > p_object_version_number THEN
1982 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
1983 ELSIF l_object_version_number = -1 THEN
1984 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1985 ELSE -- it can be the only above condition. It can happen after restore version
1986 Okc_Api.Set_Message(G_APP_NAME,G_RECORD_CHANGED);
1987 END IF;
1988 ELSE
1989 l_return_status := G_RET_STS_SUCCESS;
1990 END IF;
1991
1992 /*IF (l_debug = 'Y') THEN
1993 Okc_Debug.Log('5100: Leaving Lock_Row', 2);
1994 END IF;*/
1995
1996 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1997 FND_LOG.STRING(G_PROC_LEVEL,
1998 G_PKG_NAME, '5100: Leaving Lock_Row');
1999 END IF;
2000
2001 RETURN( l_return_status );
2002
2003 EXCEPTION
2004 WHEN OTHERS THEN
2005
2006 IF (lock_csr%ISOPEN) THEN
2007 CLOSE lock_csr;
2008 END IF;
2009 IF (lchk_csr%ISOPEN) THEN
2010 CLOSE lchk_csr;
2011 END IF;
2012
2013 /*IF (l_debug = 'Y') THEN
2014 Okc_Debug.Log('5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
2015 END IF;*/
2016
2017 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2018 FND_LOG.STRING(G_PROC_LEVEL,
2019 G_PKG_NAME, '5200: Leaving Lock_Row because of EXCEPTION: '||sqlerrm);
2020 END IF;
2021
2022 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2023 p_msg_name => G_UNEXPECTED_ERROR,
2024 p_token1 => G_SQLCODE_TOKEN,
2025 p_token1_value => sqlcode,
2026 p_token2 => G_SQLERRM_TOKEN,
2027 p_token2_value => sqlerrm);
2028
2029 RETURN( G_RET_STS_UNEXP_ERROR );
2030 END Lock_Row;
2031
2032 -----------------------------------
2033 -- Lock_Row for:OKC_SECTIONS_B --
2034 -----------------------------------
2035 PROCEDURE Lock_Row(
2036 x_return_status OUT NOCOPY VARCHAR2,
2037 p_id IN NUMBER,
2038 p_object_version_number IN NUMBER
2039 ) IS
2040 BEGIN
2041
2042 /*IF (l_debug = 'Y') THEN
2043 Okc_Debug.Log('5700: Entered Lock_Row', 2);
2044 Okc_Debug.Log('5800: Locking Row for Base Table', 2);
2045 END IF;*/
2046
2047 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2048 FND_LOG.STRING(G_PROC_LEVEL,
2049 G_PKG_NAME, '5700: Entered Lock_Row');
2050 FND_LOG.STRING(G_PROC_LEVEL,
2051 G_PKG_NAME, '5800: Locking Row for Base Table');
2052 END IF;
2053
2054 --------------------------------------------
2055 -- Call the LOCK_ROW for each _B child record
2056 --------------------------------------------
2057 x_return_status := Lock_Row(
2058 p_id => p_id,
2059 p_object_version_number => p_object_version_number
2060 );
2061 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2063 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2064 RAISE FND_API.G_EXC_ERROR;
2065 END IF;
2066
2067 /*IF (l_debug = 'Y') THEN
2068 Okc_Debug.Log('6000: Leaving Lock_Row', 2);
2069 END IF;*/
2070
2071 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2072 FND_LOG.STRING(G_PROC_LEVEL,
2073 G_PKG_NAME, '6000: Leaving Lock_Row');
2074 END IF;
2075
2076 EXCEPTION
2077 WHEN FND_API.G_EXC_ERROR THEN
2078 /*IF (l_debug = 'Y') THEN
2079 Okc_Debug.Log('6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception', 2);
2080 END IF;*/
2081
2082 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2083 FND_LOG.STRING(G_EXCP_LEVEL,
2084 G_PKG_NAME, '6100: Leaving Lock_Row:FND_API.G_EXC_ERROR Exception' );
2085 END IF;
2086 x_return_status := G_RET_STS_ERROR;
2087
2088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2089 /*IF (l_debug = 'Y') THEN
2093 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2090 Okc_Debug.Log('6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2091 END IF;*/
2092
2094 FND_LOG.STRING(G_EXCP_LEVEL,
2095 G_PKG_NAME, '6200: Leaving Lock_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
2096 END IF;
2097 x_return_status := G_RET_STS_UNEXP_ERROR;
2098
2099 WHEN OTHERS THEN
2100 /*IF (l_debug = 'Y') THEN
2101 Okc_Debug.Log('6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm, 2);
2102 END IF;*/
2103
2104 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2105 FND_LOG.STRING(G_EXCP_LEVEL,
2106 G_PKG_NAME, '6300: Leaving Lock_Row because of EXCEPTION: '||sqlerrm );
2107 END IF;
2108 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2109 p_msg_name => G_UNEXPECTED_ERROR,
2110 p_token1 => G_SQLCODE_TOKEN,
2111 p_token1_value => sqlcode,
2112 p_token2 => G_SQLERRM_TOKEN,
2113 p_token2_value => sqlerrm);
2114 x_return_status := G_RET_STS_UNEXP_ERROR;
2115
2116 END Lock_Row;
2117 ---------------------------------------------------------------------------
2118 -- PROCEDURE Update_Row
2119 ---------------------------------------------------------------------------
2120 -------------------------------------
2121 -- Update_Row for:OKC_SECTIONS_B --
2122 -------------------------------------
2123 FUNCTION Update_Row(
2124 p_id IN NUMBER,
2125 p_section_sequence IN NUMBER,
2126 p_label IN VARCHAR2,
2127 p_scn_id IN NUMBER,
2128 p_heading IN VARCHAR2,
2129 p_description IN VARCHAR2,
2130 p_document_type IN VARCHAR2,
2131 p_document_id IN NUMBER,
2132 p_scn_code IN VARCHAR2,
2133 p_amendment_description IN VARCHAR2,
2134 p_amendment_operation_code IN VARCHAR2,
2135 p_orig_system_reference_code IN VARCHAR2,
2136 p_orig_system_reference_id1 IN NUMBER,
2137 p_orig_system_reference_id2 IN NUMBER,
2138 p_print_yn IN VARCHAR2,
2139 p_attribute_category IN VARCHAR2,
2140 p_attribute1 IN VARCHAR2,
2141 p_attribute2 IN VARCHAR2,
2142 p_attribute3 IN VARCHAR2,
2143 p_attribute4 IN VARCHAR2,
2144 p_attribute5 IN VARCHAR2,
2145 p_attribute6 IN VARCHAR2,
2146 p_attribute7 IN VARCHAR2,
2147 p_attribute8 IN VARCHAR2,
2148 p_attribute9 IN VARCHAR2,
2149 p_attribute10 IN VARCHAR2,
2150 p_attribute11 IN VARCHAR2,
2151 p_attribute12 IN VARCHAR2,
2152 p_attribute13 IN VARCHAR2,
2153 p_attribute14 IN VARCHAR2,
2154 p_attribute15 IN VARCHAR2,
2155 p_summary_amend_operation_code IN VARCHAR2,
2156 p_object_version_number IN NUMBER,
2157 p_created_by IN NUMBER,
2158 p_creation_date IN DATE,
2159 p_last_updated_by IN NUMBER,
2160 p_last_update_login IN NUMBER,
2161 p_last_update_date IN DATE,
2162 p_last_amended_by IN NUMBER,
2163 p_last_amendment_date IN DATE
2164 ) RETURN VARCHAR2 IS
2165
2166 l_chr_id NUMBER;
2167
2168 BEGIN
2169
2170 /*IF (l_debug = 'Y') THEN
2171 Okc_Debug.Log('6400: Entered Update_Row', 2);
2172 END IF;*/
2173
2174 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2175 FND_LOG.STRING(G_PROC_LEVEL,
2176 G_PKG_NAME, '6400: Entered Update_Row');
2177 END IF;
2178 IF p_document_type IN ('OKC_SELL','OKC_BUY','OKO','OKS','OKE_SELL','OKE_BUY','OKL') THEN
2179 l_chr_id := okc_terms_util_pvt.get_chr_id_for_doc_id(p_document_id);
2180 END IF;
2181
2182 UPDATE OKC_SECTIONS_B
2183 SET SECTION_SEQUENCE = p_section_sequence,
2184 LABEL = p_label,
2185 SCN_ID = p_scn_id,
2186 HEADING = p_heading,
2187 DESCRIPTION = p_description,
2188 DOCUMENT_TYPE = p_document_type,
2189 DOCUMENT_ID = p_document_id,
2190 CHR_ID = l_chr_id,
2191 SCN_CODE = p_scn_code,
2192 AMENDMENT_DESCRIPTION = p_amendment_description,
2193 AMENDMENT_OPERATION_CODE = p_amendment_operation_code,
2194 ORIG_SYSTEM_REFERENCE_CODE = p_orig_system_reference_code,
2195 ORIG_SYSTEM_REFERENCE_ID1 = p_orig_system_reference_id1,
2196 ORIG_SYSTEM_REFERENCE_ID2 = p_orig_system_reference_id2,
2197 PRINT_YN = p_print_yn,
2198 ATTRIBUTE_CATEGORY = p_attribute_category,
2199 ATTRIBUTE1 = p_attribute1,
2200 ATTRIBUTE2 = p_attribute2,
2201 ATTRIBUTE3 = p_attribute3,
2202 ATTRIBUTE4 = p_attribute4,
2203 ATTRIBUTE5 = p_attribute5,
2204 ATTRIBUTE6 = p_attribute6,
2205 ATTRIBUTE7 = p_attribute7,
2209 ATTRIBUTE11 = p_attribute11,
2206 ATTRIBUTE8 = p_attribute8,
2207 ATTRIBUTE9 = p_attribute9,
2208 ATTRIBUTE10 = p_attribute10,
2210 ATTRIBUTE12 = p_attribute12,
2211 ATTRIBUTE13 = p_attribute13,
2212 ATTRIBUTE14 = p_attribute14,
2213 ATTRIBUTE15 = p_attribute15,
2214 SUMMARY_AMEND_OPERATION_CODE = p_summary_amend_operation_code,
2215 OBJECT_VERSION_NUMBER = p_object_version_number,
2216 LAST_UPDATED_BY = p_last_updated_by,
2217 LAST_UPDATE_LOGIN = p_last_update_login,
2218 LAST_UPDATE_DATE = p_last_update_date,
2219 LAST_AMENDED_BY = p_last_amended_by,
2220 LAST_AMENDMENT_DATE = p_last_amendment_date
2221 WHERE ID = p_id;
2222
2223 /*IF (l_debug = 'Y') THEN
2224 Okc_Debug.Log('6500: Leaving Update_Row', 2);
2225 END IF;*/
2226
2227 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2228 FND_LOG.STRING(G_PROC_LEVEL,
2229 G_PKG_NAME, '6500: Leaving Update_Row');
2230 END IF;
2231
2232 RETURN G_RET_STS_SUCCESS ;
2233
2234 EXCEPTION
2235 WHEN OTHERS THEN
2236
2237 /*IF (l_debug = 'Y') THEN
2238 Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
2239 END IF;*/
2240
2241 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2242 FND_LOG.STRING(G_EXCP_LEVEL,
2243 G_PKG_NAME, '6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
2244 END IF;
2245
2246 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2247 p_msg_name => G_UNEXPECTED_ERROR,
2248 p_token1 => G_SQLCODE_TOKEN,
2249 p_token1_value => sqlcode,
2250 p_token2 => G_SQLERRM_TOKEN,
2251 p_token2_value => sqlerrm);
2252
2253 RETURN G_RET_STS_UNEXP_ERROR ;
2254
2255 END Update_Row;
2256
2257 -------------------------------------
2258 -- Update_Row for:OKC_SECTIONS_B --
2259 -------------------------------------
2260 PROCEDURE Update_Row(
2261 p_validation_level IN NUMBER,
2262 x_return_status OUT NOCOPY VARCHAR2,
2263 p_id IN NUMBER,
2264 p_section_sequence IN NUMBER,
2265 p_label IN VARCHAR2,
2266 p_scn_id IN NUMBER,
2267 p_heading IN VARCHAR2,
2268 p_description IN VARCHAR2,
2269 p_document_type IN VARCHAR2,
2270 p_document_id IN NUMBER,
2271 p_scn_code IN VARCHAR2,
2272 p_amendment_description IN VARCHAR2,
2273 p_amendment_operation_code IN VARCHAR2,
2274 p_orig_system_reference_code IN VARCHAR2,
2275 p_orig_system_reference_id1 IN NUMBER,
2276 p_orig_system_reference_id2 IN NUMBER,
2277 p_print_yn IN VARCHAR2,
2278 p_attribute_category IN VARCHAR2,
2279 p_attribute1 IN VARCHAR2,
2280 p_attribute2 IN VARCHAR2,
2281 p_attribute3 IN VARCHAR2,
2282 p_attribute4 IN VARCHAR2,
2283 p_attribute5 IN VARCHAR2,
2284 p_attribute6 IN VARCHAR2,
2285 p_attribute7 IN VARCHAR2,
2286 p_attribute8 IN VARCHAR2,
2287 p_attribute9 IN VARCHAR2,
2288 p_attribute10 IN VARCHAR2,
2289 p_attribute11 IN VARCHAR2,
2290 p_attribute12 IN VARCHAR2,
2291 p_attribute13 IN VARCHAR2,
2292 p_attribute14 IN VARCHAR2,
2293 p_attribute15 IN VARCHAR2,
2294 p_summary_amend_operation_code IN VARCHAR2,
2295 p_last_amended_by IN NUMBER,
2296 p_last_amendment_date IN DATE,
2297 p_object_version_number IN NUMBER
2298
2299 ) IS
2300
2301 l_section_sequence OKC_SECTIONS_B.SECTION_SEQUENCE%TYPE;
2302 l_label OKC_SECTIONS_B.LABEL%TYPE;
2303 l_scn_id OKC_SECTIONS_B.SCN_ID%TYPE;
2304 l_heading OKC_SECTIONS_B.HEADING%TYPE;
2305 l_description OKC_SECTIONS_B.DESCRIPTION%TYPE;
2306 l_document_type OKC_SECTIONS_B.DOCUMENT_TYPE%TYPE;
2307 l_document_id OKC_SECTIONS_B.DOCUMENT_ID%TYPE;
2308 l_scn_code OKC_SECTIONS_B.SCN_CODE%TYPE;
2309 l_amendment_description OKC_SECTIONS_B.AMENDMENT_DESCRIPTION%TYPE;
2310 l_amendment_operation_code OKC_SECTIONS_B.AMENDMENT_OPERATION_CODE%TYPE;
2311 l_orig_system_reference_code OKC_SECTIONS_B.ORIG_SYSTEM_REFERENCE_CODE%TYPE;
2312 l_orig_system_reference_id1 OKC_SECTIONS_B.ORIG_SYSTEM_REFERENCE_ID1%TYPE;
2313 l_orig_system_reference_id2 OKC_SECTIONS_B.ORIG_SYSTEM_REFERENCE_ID2%TYPE;
2314 l_print_yn OKC_SECTIONS_B.PRINT_YN%TYPE;
2315 l_attribute_category OKC_SECTIONS_B.ATTRIBUTE_CATEGORY%TYPE;
2316 l_attribute1 OKC_SECTIONS_B.ATTRIBUTE1%TYPE;
2317 l_attribute2 OKC_SECTIONS_B.ATTRIBUTE2%TYPE;
2318 l_attribute3 OKC_SECTIONS_B.ATTRIBUTE3%TYPE;
2319 l_attribute4 OKC_SECTIONS_B.ATTRIBUTE4%TYPE;
2320 l_attribute5 OKC_SECTIONS_B.ATTRIBUTE5%TYPE;
2321 l_attribute6 OKC_SECTIONS_B.ATTRIBUTE6%TYPE;
2322 l_attribute7 OKC_SECTIONS_B.ATTRIBUTE7%TYPE;
2323 l_attribute8 OKC_SECTIONS_B.ATTRIBUTE8%TYPE;
2324 l_attribute9 OKC_SECTIONS_B.ATTRIBUTE9%TYPE;
2325 l_attribute10 OKC_SECTIONS_B.ATTRIBUTE10%TYPE;
2326 l_attribute11 OKC_SECTIONS_B.ATTRIBUTE11%TYPE;
2327 l_attribute12 OKC_SECTIONS_B.ATTRIBUTE12%TYPE;
2328 l_attribute13 OKC_SECTIONS_B.ATTRIBUTE13%TYPE;
2329 l_attribute14 OKC_SECTIONS_B.ATTRIBUTE14%TYPE;
2330 l_attribute15 OKC_SECTIONS_B.ATTRIBUTE15%TYPE;
2331 l_summary_amend_operation_code OKC_SECTIONS_B.SUMMARY_AMEND_OPERATION_CODE%TYPE;
2332 l_object_version_number OKC_SECTIONS_B.OBJECT_VERSION_NUMBER%TYPE;
2333 l_created_by OKC_SECTIONS_B.CREATED_BY%TYPE;
2334 l_creation_date OKC_SECTIONS_B.CREATION_DATE%TYPE;
2335 l_last_updated_by OKC_SECTIONS_B.LAST_UPDATED_BY%TYPE;
2336 l_last_update_login OKC_SECTIONS_B.LAST_UPDATE_LOGIN%TYPE;
2337 l_last_update_date OKC_SECTIONS_B.LAST_UPDATE_DATE%TYPE;
2338 l_last_amended_by OKC_SECTIONS_B.LAST_AMENDED_BY%TYPE;
2339 l_last_amendment_date OKC_SECTIONS_B.LAST_AMENDMENT_DATE%TYPE;
2340
2341 BEGIN
2342
2343 /*IF (l_debug = 'Y') THEN
2344 Okc_Debug.Log('7000: Entered Update_Row', 2);
2345 Okc_Debug.Log('7100: Locking _B row', 2);
2346 END IF;*/
2347
2348 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2349 FND_LOG.STRING(G_PROC_LEVEL,
2350 G_PKG_NAME, '7000: Entered Update_Row');
2351 FND_LOG.STRING(G_PROC_LEVEL,
2352 G_PKG_NAME, '7100: Locking _B row');
2353 END IF;
2354
2355 x_return_status := Lock_row(
2356 p_id => p_id,
2357 p_object_version_number => p_object_version_number
2358 );
2359 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2361 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2362 RAISE FND_API.G_EXC_ERROR;
2363 END IF;
2364
2365
2366 /*IF (l_debug = 'Y') THEN
2367 Okc_Debug.Log('7300: Setting attributes', 2);
2368 END IF;*/
2369
2370 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2371 FND_LOG.STRING(G_PROC_LEVEL,
2372 G_PKG_NAME, '7300: Setting attributes');
2373 END IF;
2374
2375 x_return_status := Set_Attributes(
2376 p_id => p_id,
2377 p_section_sequence => p_section_sequence,
2378 p_label => p_label,
2379 p_scn_id => p_scn_id,
2380 p_heading => p_heading,
2381 p_description => p_description,
2382 p_document_type => p_document_type,
2383 p_document_id => p_document_id,
2384 p_scn_code => p_scn_code,
2385 p_amendment_description => p_amendment_description,
2386 p_amendment_operation_code => p_amendment_operation_code,
2387 p_orig_system_reference_code => p_orig_system_reference_code,
2388 p_orig_system_reference_id1 => p_orig_system_reference_id1,
2389 p_orig_system_reference_id2 => p_orig_system_reference_id2,
2390 p_print_yn => p_print_yn,
2391 p_attribute_category => p_attribute_category,
2392 p_attribute1 => p_attribute1,
2393 p_attribute2 => p_attribute2,
2394 p_attribute3 => p_attribute3,
2395 p_attribute4 => p_attribute4,
2396 p_attribute5 => p_attribute5,
2397 p_attribute6 => p_attribute6,
2398 p_attribute7 => p_attribute7,
2399 p_attribute8 => p_attribute8,
2400 p_attribute9 => p_attribute9,
2401 p_attribute10 => p_attribute10,
2402 p_attribute11 => p_attribute11,
2403 p_attribute12 => p_attribute12,
2404 p_attribute13 => p_attribute13,
2405 p_attribute14 => p_attribute14,
2406 p_attribute15 => p_attribute15,
2407 p_summary_amend_operation_code => p_summary_amend_operation_code,
2408 p_object_version_number => p_object_version_number,
2409 p_last_amended_by => p_last_amended_by,
2410 p_last_amendment_date => p_last_amendment_date,
2411 x_section_sequence => l_section_sequence,
2412 x_label => l_label,
2413 x_scn_id => l_scn_id,
2414 x_heading => l_heading,
2415 x_object_version_number => l_object_version_number,
2416 x_description => l_description,
2417 x_document_type => l_document_type,
2418 x_document_id => l_document_id,
2419 x_scn_code => l_scn_code,
2420 x_amendment_description => l_amendment_description,
2421 x_amendment_operation_code => l_amendment_operation_code,
2422 x_orig_system_reference_code => l_orig_system_reference_code,
2423 x_orig_system_reference_id1 => l_orig_system_reference_id1,
2424 x_orig_system_reference_id2 => l_orig_system_reference_id2,
2425 x_print_yn => l_print_yn,
2426 x_attribute_category => l_attribute_category,
2427 x_attribute1 => l_attribute1,
2431 x_attribute5 => l_attribute5,
2428 x_attribute2 => l_attribute2,
2429 x_attribute3 => l_attribute3,
2430 x_attribute4 => l_attribute4,
2432 x_attribute6 => l_attribute6,
2433 x_attribute7 => l_attribute7,
2434 x_attribute8 => l_attribute8,
2435 x_attribute9 => l_attribute9,
2436 x_attribute10 => l_attribute10,
2437 x_attribute11 => l_attribute11,
2438 x_attribute12 => l_attribute12,
2439 x_attribute13 => l_attribute13,
2440 x_attribute14 => l_attribute14,
2441 x_attribute15 => l_attribute15,
2442 x_summary_amend_operation_code => l_summary_amend_operation_code,
2443 x_last_amended_by => l_last_amended_by,
2444 x_last_amendment_date => l_last_amendment_date
2445 );
2446 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2448 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2449 RAISE FND_API.G_EXC_ERROR;
2450 END IF;
2451
2452 /*IF (l_debug = 'Y') THEN
2453 Okc_Debug.Log('7400: Record Validation', 2);
2454 END IF;*/
2455
2456 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2457 FND_LOG.STRING(G_PROC_LEVEL,
2458 G_PKG_NAME, '7400: Record Validation');
2459 END IF;
2460
2461 --- Validate all non-missing attributes
2462 x_return_status := Validate_Record(
2463 p_validation_level => p_validation_level,
2464 p_id => p_id,
2465 p_section_sequence => l_section_sequence,
2466 p_label => l_label,
2467 p_scn_id => l_scn_id,
2468 p_heading => l_heading,
2469 p_description => l_description,
2470 p_document_type => l_document_type,
2471 p_document_id => l_document_id,
2472 p_scn_code => l_scn_code,
2473 p_amendment_description => l_amendment_description,
2474 p_amendment_operation_code => l_amendment_operation_code,
2475 p_orig_system_reference_code => l_orig_system_reference_code,
2476 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2477 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2478 p_print_yn => l_print_yn,
2479 p_attribute_category => l_attribute_category,
2480 p_attribute1 => l_attribute1,
2481 p_attribute2 => l_attribute2,
2482 p_attribute3 => l_attribute3,
2483 p_attribute4 => l_attribute4,
2484 p_attribute5 => l_attribute5,
2485 p_attribute6 => l_attribute6,
2486 p_attribute7 => l_attribute7,
2487 p_attribute8 => l_attribute8,
2488 p_attribute9 => l_attribute9,
2489 p_attribute10 => l_attribute10,
2490 p_attribute11 => l_attribute11,
2491 p_attribute12 => l_attribute12,
2492 p_attribute13 => l_attribute13,
2493 p_attribute14 => l_attribute14,
2494 p_attribute15 => l_attribute15,
2495 p_summary_amend_operation_code => l_summary_amend_operation_code
2496 );
2497 --- If any errors happen abort API
2498 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2500 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2501 RAISE FND_API.G_EXC_ERROR;
2502 END IF;
2503
2504 /*IF (l_debug = 'Y') THEN
2505 Okc_Debug.Log('7500: Filling WHO columns', 2);
2506 END IF;*/
2507
2508 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2509 FND_LOG.STRING(G_PROC_LEVEL,
2510 G_PKG_NAME, '7500: Filling WHO columns');
2511 END IF;
2512
2513 -- Filling who columns
2514 l_last_update_date := SYSDATE;
2515 l_last_updated_by := FND_GLOBAL.USER_ID;
2516 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2517
2518 -- Object version increment
2519 IF Nvl(l_object_version_number, 0) >= 0 THEN
2520 l_object_version_number := Nvl(l_object_version_number, 0) + 1;
2521 END IF;
2522
2523 --------------------------------------------
2524 -- Call the Update_Row for each child record
2525 --------------------------------------------
2526 /*IF (l_debug = 'Y') THEN
2527 Okc_Debug.Log('7600: Updating Row', 2);
2528 END IF;*/
2529
2530 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2531 FND_LOG.STRING(G_PROC_LEVEL,
2532 G_PKG_NAME, '7600: Updating Row');
2533 END IF;
2534
2535 x_return_status := Update_Row(
2536 p_id => p_id,
2537 p_section_sequence => l_section_sequence,
2538 p_label => l_label,
2539 p_scn_id => l_scn_id,
2540 p_heading => l_heading,
2541 p_description => l_description,
2542 p_document_type => l_document_type,
2543 p_document_id => l_document_id,
2544 p_scn_code => l_scn_code,
2545 p_amendment_description => l_amendment_description,
2546 p_amendment_operation_code => l_amendment_operation_code,
2547 p_orig_system_reference_code => l_orig_system_reference_code,
2548 p_orig_system_reference_id1 => l_orig_system_reference_id1,
2549 p_orig_system_reference_id2 => l_orig_system_reference_id2,
2550 p_print_yn => l_print_yn,
2551 p_attribute_category => l_attribute_category,
2552 p_attribute1 => l_attribute1,
2553 p_attribute2 => l_attribute2,
2554 p_attribute3 => l_attribute3,
2555 p_attribute4 => l_attribute4,
2556 p_attribute5 => l_attribute5,
2557 p_attribute6 => l_attribute6,
2558 p_attribute7 => l_attribute7,
2559 p_attribute8 => l_attribute8,
2560 p_attribute9 => l_attribute9,
2561 p_attribute10 => l_attribute10,
2562 p_attribute11 => l_attribute11,
2563 p_attribute12 => l_attribute12,
2564 p_attribute13 => l_attribute13,
2565 p_attribute14 => l_attribute14,
2566 p_attribute15 => l_attribute15,
2567 p_summary_amend_operation_code => l_summary_amend_operation_code,
2568 p_object_version_number => l_object_version_number,
2569 p_created_by => l_created_by,
2570 p_creation_date => l_creation_date,
2571 p_last_updated_by => l_last_updated_by,
2572 p_last_update_login => l_last_update_login,
2573 p_last_update_date => l_last_update_date,
2574 p_last_amended_by => l_last_amended_by,
2575 p_last_amendment_date => l_last_amendment_date
2576 );
2577 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2579 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2580 RAISE FND_API.G_EXC_ERROR;
2581 END IF;
2582
2583
2584 /*IF (l_debug = 'Y') THEN
2585 Okc_Debug.Log('7800: Leaving Update_Row', 2);
2586 END IF;*/
2587
2588 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2589 FND_LOG.STRING(G_PROC_LEVEL,
2590 G_PKG_NAME, '7800: Leaving Update_Row');
2591 END IF;
2592
2593 EXCEPTION
2594 WHEN FND_API.G_EXC_ERROR THEN
2595 /*IF (l_debug = 'Y') THEN
2596 Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
2597 END IF;*/
2598
2599 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2600 FND_LOG.STRING(G_EXCP_LEVEL,
2601 G_PKG_NAME, '7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception' );
2602 END IF;
2603 x_return_status := G_RET_STS_ERROR;
2604
2605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2606 /*IF (l_debug = 'Y') THEN
2607 Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2608 END IF;*/
2609
2610 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2611 FND_LOG.STRING(G_EXCP_LEVEL,
2612 G_PKG_NAME, '8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
2613 END IF;
2614 x_return_status := G_RET_STS_UNEXP_ERROR;
2615
2616 WHEN OTHERS THEN
2617 /*IF (l_debug = 'Y') THEN
2618 Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
2619 END IF;*/
2620
2621 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2622 FND_LOG.STRING(G_EXCP_LEVEL,
2623 G_PKG_NAME, '8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm);
2624 END IF;
2625 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2626 p_msg_name => G_UNEXPECTED_ERROR,
2627 p_token1 => G_SQLCODE_TOKEN,
2628 p_token1_value => sqlcode,
2629 p_token2 => G_SQLERRM_TOKEN,
2630 p_token2_value => sqlerrm);
2631 x_return_status := G_RET_STS_UNEXP_ERROR;
2632
2633 END Update_Row;
2634
2635 ---------------------------------------------------------------------------
2636 -- PROCEDURE Delete_Row
2637 ---------------------------------------------------------------------------
2638 -------------------------------------
2639 -- Delete_Row for:OKC_SECTIONS_B --
2640 -------------------------------------
2641 FUNCTION Delete_Row(
2642 p_id IN NUMBER
2643 ) RETURN VARCHAR2 IS
2644
2645 BEGIN
2646
2647 /*IF (l_debug = 'Y') THEN
2648 Okc_Debug.Log('8200: Entered Delete_Row', 2);
2649 END IF;*/
2650
2651 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2652 FND_LOG.STRING(G_PROC_LEVEL,
2653 G_PKG_NAME, '8200: Entered Delete_Row');
2654 END IF;
2655
2656 DELETE FROM OKC_SECTIONS_B WHERE ID = p_ID;
2657
2658 /*IF (l_debug = 'Y') THEN
2659 Okc_Debug.Log('8300: Leaving Delete_Row', 2);
2660 END IF;*/
2661
2662 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2663 FND_LOG.STRING(G_PROC_LEVEL,
2664 G_PKG_NAME, '8300: Leaving Delete_Row');
2665 END IF;
2666
2667 RETURN( G_RET_STS_SUCCESS );
2668
2669 EXCEPTION
2670 WHEN OTHERS THEN
2671
2672 /*IF (l_debug = 'Y') THEN
2673 Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
2674 END IF;*/
2675
2676 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2677 FND_LOG.STRING(G_EXCP_LEVEL,
2678 G_PKG_NAME, '8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm );
2679 END IF;
2680
2681 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2682 p_msg_name => G_UNEXPECTED_ERROR,
2683 p_token1 => G_SQLCODE_TOKEN,
2684 p_token1_value => sqlcode,
2685 p_token2 => G_SQLERRM_TOKEN,
2686 p_token2_value => sqlerrm);
2687
2688 RETURN( G_RET_STS_UNEXP_ERROR );
2689
2690 END Delete_Row;
2691
2692 -------------------------------------
2693 -- Delete_Row for:OKC_SECTIONS_B --
2694 -------------------------------------
2695 PROCEDURE Delete_Row(
2696 x_return_status OUT NOCOPY VARCHAR2,
2697 p_id IN NUMBER,
2698 p_object_version_number IN NUMBER
2699 ) IS
2700 l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
2701 BEGIN
2702
2703 /*IF (l_debug = 'Y') THEN
2704 Okc_Debug.Log('8800: Entered Delete_Row', 2);
2705 Okc_Debug.Log('8900: Locking _B row', 2);
2706 END IF;*/
2707
2708 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2709 FND_LOG.STRING(G_PROC_LEVEL,
2710 G_PKG_NAME, '8800: Entered Delete_Row');
2711 FND_LOG.STRING(G_PROC_LEVEL,
2712 G_PKG_NAME, '8900: Locking _B row');
2713 END IF;
2714
2715 x_return_status := Lock_row(
2716 p_id => p_id,
2717 p_object_version_number => p_object_version_number
2718 );
2719 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2721 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2722 RAISE FND_API.G_EXC_ERROR;
2723 END IF;
2724
2725 /*IF (l_debug = 'Y') THEN
2726 Okc_Debug.Log('9100: Removing _B row', 2);
2727 END IF;*/
2728
2729 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2730 FND_LOG.STRING(G_PROC_LEVEL,
2731 G_PKG_NAME, '9100: Removing _B row');
2732 END IF;
2733 x_return_status := Delete_Row( p_id => p_id );
2734 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2736 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2737 RAISE FND_API.G_EXC_ERROR;
2738 END IF;
2739
2740 /*IF (l_debug = 'Y') THEN
2741 Okc_Debug.Log('9300: Leaving Delete_Row', 2);
2742 END IF;*/
2743
2744 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2745 FND_LOG.STRING(G_PROC_LEVEL,
2746 G_PKG_NAME, '9300: Leaving Delete_Row');
2747 END IF;
2748
2749 EXCEPTION
2750 WHEN FND_API.G_EXC_ERROR THEN
2751 /*IF (l_debug = 'Y') THEN
2752 Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
2753 END IF;*/
2754
2755 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2756 FND_LOG.STRING(G_EXCP_LEVEL,
2757 G_PKG_NAME, '9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception' );
2758 END IF;
2759 x_return_status := G_RET_STS_ERROR;
2760
2761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2762 /*IF (l_debug = 'Y') THEN
2763 Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2764 END IF;*/
2765 x_return_status := G_RET_STS_UNEXP_ERROR;
2766
2767 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2768 FND_LOG.STRING(G_EXCP_LEVEL,
2769 G_PKG_NAME, '9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
2770 END IF;
2771
2772 WHEN OTHERS THEN
2773 /*IF (l_debug = 'Y') THEN
2774 Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
2775 END IF;*/
2776
2777 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2778 FND_LOG.STRING(G_EXCP_LEVEL,
2779 G_PKG_NAME, '9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm);
2780 END IF;
2781 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2782 p_msg_name => G_UNEXPECTED_ERROR,
2783 p_token1 => G_SQLCODE_TOKEN,
2784 p_token1_value => sqlcode,
2785 p_token2 => G_SQLERRM_TOKEN,
2786 p_token2_value => sqlerrm);
2787 x_return_status := G_RET_STS_UNEXP_ERROR;
2788
2789 END Delete_Row;
2790
2791 PROCEDURE delete_set(
2792 x_return_status OUT NOCOPY VARCHAR2,
2793 p_doc_type IN VARCHAR2,
2794 p_doc_id IN NUMBER
2795 ) IS
2796 CURSOR lock_csr IS
2797 SELECT rowid FROM OKC_SECTIONS_B
2798 WHERE document_type = p_doc_type and
2799 document_id = p_doc_id
2800 FOR UPDATE NOWAIT;
2801 BEGIN
2802 /*IF (l_debug = 'Y') THEN
2803 Okc_Debug.Log('9700: Entered Delete_Set', 2);
2804 END IF;*/
2805
2806 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2807 FND_LOG.STRING(G_PROC_LEVEL,
2808 G_PKG_NAME, '9700: Entered Delete_Set');
2809 END IF;
2810
2811 -- making OPEN/CLOSE cursor to lock records
2812 OPEN lock_csr;
2813 CLOSE lock_csr;
2814
2815 DELETE FROM OKC_SECTIONS_B
2816 WHERE document_type = p_doc_type and
2817 document_id = p_doc_id ;
2818
2819 /*IF (l_debug = 'Y') THEN
2820 Okc_Debug.Log('11000: Leaving Delete_set', 2);
2821 END IF;*/
2822
2823 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2824 FND_LOG.STRING(G_PROC_LEVEL,
2825 G_PKG_NAME, '11000: Leaving Delete_set');
2826 END IF;
2827
2828 EXCEPTION
2829 WHEN E_Resource_Busy THEN
2830 /*IF (l_debug = 'Y') THEN
2831 Okc_Debug.Log('000: Leaving Delete_set:E_Resource_Busy Exception', 2);
2832 END IF;*/
2833
2834 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2835 FND_LOG.STRING(G_EXCP_LEVEL,
2836 G_PKG_NAME, '000: Leaving Delete_set:E_Resource_Busy Exception' );
2837 END IF;
2838
2839 IF (lock_csr%ISOPEN) THEN
2840 CLOSE lock_csr;
2841 END IF;
2842 Okc_Api.Set_Message( G_FND_APP, G_UNABLE_TO_RESERVE_REC);
2843 x_return_status := G_RET_STS_ERROR ;
2844
2845 WHEN FND_API.G_EXC_ERROR THEN
2846 /*IF (l_debug = 'Y') THEN
2847 Okc_Debug.Log('11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception', 2);
2848 END IF;*/
2849
2850 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2851 FND_LOG.STRING(G_EXCP_LEVEL,
2852 G_PKG_NAME, '11100: Leaving Delete_Set:FND_API.G_EXC_ERROR Exception' );
2853 END IF;
2854
2855 IF (lock_csr%ISOPEN) THEN
2856 CLOSE lock_csr;
2857 END IF;
2858 x_return_status := G_RET_STS_ERROR;
2859
2860 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2861 /*IF (l_debug = 'Y') THEN
2862 Okc_Debug.Log('11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
2863 END IF;*/
2864
2865 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2866 FND_LOG.STRING(G_EXCP_LEVEL,
2867 G_PKG_NAME, '11200: Leaving Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception' );
2868 END IF;
2869
2870 IF (lock_csr%ISOPEN) THEN
2871 CLOSE lock_csr;
2872 END IF;
2873 x_return_status := G_RET_STS_UNEXP_ERROR;
2874
2875 WHEN OTHERS THEN
2876 /*IF (l_debug = 'Y') THEN
2877 Okc_Debug.Log('11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm, 2);
2878 END IF;*/
2879
2880 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2881 FND_LOG.STRING(G_EXCP_LEVEL,
2882 G_PKG_NAME, '11300: Leaving Delete_Set because of EXCEPTION: '||sqlerrm );
2883 END IF;
2884
2885 IF (lock_csr%ISOPEN) THEN
2886 CLOSE lock_csr;
2887 END IF;
2888 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2889 p_msg_name => G_UNEXPECTED_ERROR,
2890 p_token1 => G_SQLCODE_TOKEN,
2891 p_token1_value => sqlcode,
2892 p_token2 => G_SQLERRM_TOKEN,
2893 p_token2_value => sqlerrm);
2894 x_return_status := G_RET_STS_UNEXP_ERROR;
2895
2896 END Delete_Set;
2897
2898
2899 --This function is to be called from versioning API OKC_VERSION_PVT
2900 FUNCTION Create_Version(
2901 p_doc_type IN VARCHAR2,
2902 p_doc_id IN NUMBER,
2903 p_major_version IN NUMBER
2904 ) RETURN VARCHAR2 IS
2905 BEGIN
2906
2907 /*IF (l_debug = 'Y') THEN
2908 Okc_Debug.Log('9700: Entered create_version', 2);
2909 Okc_Debug.Log('9800: Saving Base Table', 2);
2910 END IF;*/
2911
2912 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2913 FND_LOG.STRING(G_PROC_LEVEL,
2914 G_PKG_NAME, '9700: Entered create_version' );
2915 FND_LOG.STRING(G_PROC_LEVEL,
2916 G_PKG_NAME, '9800: Saving Base Table' );
2917 END IF;
2918
2919 -----------------------------------------
2920 -- Saving Base Table
2921 -----------------------------------------
2922 INSERT INTO OKC_SECTIONS_BH (
2923 major_version,
2924 ID,
2925 SECTION_SEQUENCE,
2926 SCN_TYPE,
2927 CHR_ID,
2928 SAT_CODE,
2929 LABEL,
2930 SCN_ID,
2931 HEADING,
2932 DESCRIPTION,
2933 DOCUMENT_TYPE,
2934 DOCUMENT_ID,
2935 SCN_CODE,
2936 AMENDMENT_DESCRIPTION,
2937 AMENDMENT_OPERATION_CODE,
2938 ORIG_SYSTEM_REFERENCE_CODE,
2939 ORIG_SYSTEM_REFERENCE_ID1,
2940 ORIG_SYSTEM_REFERENCE_ID2,
2941 PRINT_YN,
2942 ATTRIBUTE_CATEGORY,
2943 ATTRIBUTE1,
2944 ATTRIBUTE2,
2945 ATTRIBUTE3,
2946 ATTRIBUTE4,
2947 ATTRIBUTE5,
2948 ATTRIBUTE6,
2949 ATTRIBUTE7,
2950 ATTRIBUTE8,
2951 ATTRIBUTE9,
2952 ATTRIBUTE10,
2953 ATTRIBUTE11,
2954 ATTRIBUTE12,
2955 ATTRIBUTE13,
2956 ATTRIBUTE14,
2957 ATTRIBUTE15,
2958 SUMMARY_AMEND_OPERATION_CODE,
2959 OBJECT_VERSION_NUMBER,
2960 CREATED_BY,
2961 CREATION_DATE,
2962 LAST_UPDATED_BY,
2963 LAST_UPDATE_LOGIN,
2964 LAST_UPDATE_DATE,
2965 LAST_AMENDED_BY,
2966 LAST_AMENDMENT_DATE)
2967 SELECT
2968 p_major_version,
2969 ID,
2970 SECTION_SEQUENCE,
2971 SCN_TYPE,
2972 CHR_ID,
2973 SAT_CODE,
2974 LABEL,
2975 SCN_ID,
2976 HEADING,
2977 DESCRIPTION,
2978 DOCUMENT_TYPE,
2979 DOCUMENT_ID,
2980 SCN_CODE,
2981 AMENDMENT_DESCRIPTION,
2982 AMENDMENT_OPERATION_CODE,
2983 ORIG_SYSTEM_REFERENCE_CODE,
2984 ORIG_SYSTEM_REFERENCE_ID1,
2985 ORIG_SYSTEM_REFERENCE_ID2,
2986 PRINT_YN,
2987 ATTRIBUTE_CATEGORY,
2988 ATTRIBUTE1,
2989 ATTRIBUTE2,
2990 ATTRIBUTE3,
2991 ATTRIBUTE4,
2992 ATTRIBUTE5,
2993 ATTRIBUTE6,
2994 ATTRIBUTE7,
2995 ATTRIBUTE8,
2996 ATTRIBUTE9,
2997 ATTRIBUTE10,
2998 ATTRIBUTE11,
2999 ATTRIBUTE12,
3000 ATTRIBUTE13,
3001 ATTRIBUTE14,
3002 ATTRIBUTE15,
3003 SUMMARY_AMEND_OPERATION_CODE,
3004 OBJECT_VERSION_NUMBER,
3005 CREATED_BY,
3006 CREATION_DATE,
3007 LAST_UPDATED_BY,
3008 LAST_UPDATE_LOGIN,
3009 LAST_UPDATE_DATE,
3010 LAST_AMENDED_BY,
3011 LAST_AMENDMENT_DATE
3012 FROM OKC_SECTIONS_B
3013 WHERE document_type = p_doc_type
3014 AND document_id = p_doc_id;
3015
3016 /*IF (l_debug = 'Y') THEN
3017 Okc_Debug.Log('10000: Leaving create_version', 2);
3018 END IF;*/
3019
3020 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3021 FND_LOG.STRING(G_PROC_LEVEL,
3022 G_PKG_NAME, '10000: Leaving create_version' );
3023 END IF;
3024
3025 RETURN( G_RET_STS_SUCCESS );
3026
3027 EXCEPTION
3028 WHEN OTHERS THEN
3029
3030 /*IF (l_debug = 'Y') THEN
3031 Okc_Debug.Log('10100: Leaving create_version because of EXCEPTION: '||sqlerrm, 2);
3032 END IF;*/
3033
3034 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
3035 FND_LOG.STRING(G_EXCP_LEVEL,
3036 G_PKG_NAME, '10100: Leaving create_version because of EXCEPTION: '||sqlerrm );
3037 END IF;
3038
3039 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3040 p_msg_name => G_UNEXPECTED_ERROR,
3041 p_token1 => G_SQLCODE_TOKEN,
3042 p_token1_value => sqlcode,
3043 p_token2 => G_SQLERRM_TOKEN,
3044 p_token2_value => sqlerrm);
3045
3046 RETURN G_RET_STS_UNEXP_ERROR ;
3047
3048 END create_version;
3049
3050 --This Function is called from Versioning API OKC_VERSION_PVT
3051
3052 FUNCTION Restore_Version(
3053 p_doc_type IN VARCHAR2,
3054 p_doc_id IN NUMBER,
3055 p_major_version IN NUMBER
3056 ) RETURN VARCHAR2 IS
3057
3058 BEGIN
3059
3060 /*IF (l_debug = 'Y') THEN
3061 Okc_Debug.Log('10200: Entered restore_version', 2);
3062 Okc_Debug.Log('10300: Restoring Base Table', 2);
3063 END IF;*/
3064
3065 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3066 FND_LOG.STRING(G_PROC_LEVEL,
3067 G_PKG_NAME, '10200: Entered restore_version' );
3068 FND_LOG.STRING(G_PROC_LEVEL,
3069 G_PKG_NAME, '10300: Restoring Base Table' );
3070 END IF;
3071
3072 -----------------------------------------
3073 -- Restoring Base Table
3074 -----------------------------------------
3075 INSERT INTO OKC_SECTIONS_B (
3076 ID,
3077 SECTION_SEQUENCE,
3078 SCN_TYPE,
3079 CHR_ID,
3080 SAT_CODE,
3081 LABEL,
3082 SCN_ID,
3083 HEADING,
3084 DESCRIPTION,
3085 DOCUMENT_TYPE,
3086 DOCUMENT_ID,
3087 SCN_CODE,
3088 AMENDMENT_DESCRIPTION,
3089 AMENDMENT_OPERATION_CODE,
3090 ORIG_SYSTEM_REFERENCE_CODE,
3091 ORIG_SYSTEM_REFERENCE_ID1,
3092 ORIG_SYSTEM_REFERENCE_ID2,
3093 PRINT_YN,
3094 ATTRIBUTE_CATEGORY,
3095 ATTRIBUTE1,
3096 ATTRIBUTE2,
3097 ATTRIBUTE3,
3098 ATTRIBUTE4,
3099 ATTRIBUTE5,
3100 ATTRIBUTE6,
3101 ATTRIBUTE7,
3102 ATTRIBUTE8,
3103 ATTRIBUTE9,
3104 ATTRIBUTE10,
3105 ATTRIBUTE11,
3106 ATTRIBUTE12,
3107 ATTRIBUTE13,
3108 ATTRIBUTE14,
3109 ATTRIBUTE15,
3110 SUMMARY_AMEND_OPERATION_CODE,
3111 OBJECT_VERSION_NUMBER,
3112 CREATED_BY,
3113 CREATION_DATE,
3114 LAST_UPDATED_BY,
3115 LAST_UPDATE_LOGIN,
3116 LAST_UPDATE_DATE,
3117 LAST_AMENDED_BY,
3118 LAST_AMENDMENT_DATE)
3119 SELECT
3120 ID,
3121 SECTION_SEQUENCE,
3122 SCN_TYPE,
3123 CHR_ID,
3124 SAT_CODE,
3125 LABEL,
3126 SCN_ID,
3127 HEADING,
3128 DESCRIPTION,
3129 DOCUMENT_TYPE,
3130 DOCUMENT_ID,
3131 SCN_CODE,
3132 AMENDMENT_DESCRIPTION,
3133 AMENDMENT_OPERATION_CODE,
3134 ORIG_SYSTEM_REFERENCE_CODE,
3135 ORIG_SYSTEM_REFERENCE_ID1,
3136 ORIG_SYSTEM_REFERENCE_ID2,
3137 PRINT_YN,
3138 ATTRIBUTE_CATEGORY,
3139 ATTRIBUTE1,
3140 ATTRIBUTE2,
3141 ATTRIBUTE3,
3142 ATTRIBUTE4,
3143 ATTRIBUTE5,
3144 ATTRIBUTE6,
3145 ATTRIBUTE7,
3146 ATTRIBUTE8,
3147 ATTRIBUTE9,
3148 ATTRIBUTE10,
3149 ATTRIBUTE11,
3150 ATTRIBUTE12,
3151 ATTRIBUTE13,
3152 ATTRIBUTE14,
3153 ATTRIBUTE15,
3154 SUMMARY_AMEND_OPERATION_CODE,
3155 OBJECT_VERSION_NUMBER,
3156 CREATED_BY,
3157 CREATION_DATE,
3158 LAST_UPDATED_BY,
3159 LAST_UPDATE_LOGIN,
3160 LAST_UPDATE_DATE,
3161 LAST_AMENDED_BY,
3162 LAST_AMENDMENT_DATE
3163 FROM OKC_SECTIONS_BH
3164 WHERE document_type = p_doc_type and document_id = p_doc_id AND major_version = p_major_version;
3165
3166 /*IF (l_debug = 'Y') THEN
3167 Okc_Debug.Log('10500: Leaving restore_version', 2);
3168 END IF;*/
3169
3170 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3171 FND_LOG.STRING(G_PROC_LEVEL,
3172 G_PKG_NAME, '10500: Leaving restore_version' );
3173 END IF;
3174
3175 RETURN( G_RET_STS_SUCCESS );
3176
3177 EXCEPTION
3178 WHEN OTHERS THEN
3179
3180 /*IF (l_debug = 'Y') THEN
3181 Okc_Debug.Log('10600: Leaving restore_version because of EXCEPTION: '||sqlerrm, 2);
3182 END IF;*/
3183
3184 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
3185 FND_LOG.STRING(G_EXCP_LEVEL,
3186 G_PKG_NAME, '10600: Leaving restore_version because of EXCEPTION: '||sqlerrm);
3187 END IF;
3188
3189 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3190 p_msg_name => G_UNEXPECTED_ERROR,
3191 p_token1 => G_SQLCODE_TOKEN,
3192 p_token1_value => sqlcode,
3193 p_token2 => G_SQLERRM_TOKEN,
3194 p_token2_value => sqlerrm);
3195
3196 RETURN G_RET_STS_UNEXP_ERROR ;
3197
3198 END restore_version;
3199
3200 --This Function is called from Versioning API OKC_VERSION_PVT
3201 -- to delete sections for specified version of document
3202
3203 FUNCTION Delete_Version(
3204 p_doc_type IN VARCHAR2,
3205 p_doc_id IN NUMBER,
3206 p_major_version IN NUMBER
3207 ) RETURN VARCHAR2 IS
3208
3209 BEGIN
3210
3211 /*IF (l_debug = 'Y') THEN
3212 Okc_Debug.Log('7200: Entered Delete_Version', 2);
3213 END IF;*/
3214
3215 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3216 FND_LOG.STRING(G_PROC_LEVEL,
3217 G_PKG_NAME, '7200: Entered Delete_Version' );
3218 END IF;
3219
3220 -----------------------------------------
3221 -- Restoring Base Table
3222 -----------------------------------------
3223 DELETE
3224 FROM OKC_SECTIONS_BH
3225 WHERE document_type = p_doc_type and document_id = p_doc_id AND major_version = p_major_version;
3226
3227 /*IF (l_debug = 'Y') THEN
3228 Okc_Debug.Log('7300: Leaving Delete_Version', 2);
3229 END IF;*/
3230
3231 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3232 FND_LOG.STRING(G_PROC_LEVEL,
3233 G_PKG_NAME, '7300: Leaving Delete_Version' );
3234 END IF;
3235
3236 RETURN( G_RET_STS_SUCCESS );
3237
3238 EXCEPTION
3239 WHEN OTHERS THEN
3240
3241 /*IF (l_debug = 'Y') THEN
3242 Okc_Debug.Log('7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm, 2);
3243 END IF;*/
3244
3245 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
3246 FND_LOG.STRING(G_EXCP_LEVEL,
3247 G_PKG_NAME, '7400: Leaving Delete_Version because of EXCEPTION: '||sqlerrm );
3248 END IF;
3249
3250 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3251 p_msg_name => G_UNEXPECTED_ERROR,
3252 p_token1 => G_SQLCODE_TOKEN,
3253 p_token1_value => sqlcode,
3254 p_token2 => G_SQLERRM_TOKEN,
3255 p_token2_value => sqlerrm);
3256
3257 RETURN G_RET_STS_UNEXP_ERROR ;
3258
3259 END Delete_Version;
3260
3261 END OKC_TERMS_SECTIONS_PVT;