DBA Data[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;