[Home] [Help]
PACKAGE BODY: APPS.FND_SEQ_CATEGORIES_PKG
Source
1 PACKAGE BODY FND_SEQ_CATEGORIES_PKG AS
2 /* $Header: AFSNCATB.pls 115.0 99/07/16 23:30:56 porting ship $ */
3
4 -- ************************************************************************
5 --
6 -- PRIVATE FUNCTIONS
7 --
8 -- ************************************************************************
9 FUNCTION is_dublicate_cat( x_applicatio_id NUMBER,
10 x_category_code VARCHAR2) RETURN NUMBER IS
11
12 Dummy VARCHAR2(10);
13 CURSOR c_cat IS
14 SELECT 'Duplicate'
15 FROM fnd_doc_sequence_categories fcat
16 WHERE fcat.code = x_category_code
17 AND fcat.application_id = x_applicatio_id;
18 BEGIN
19 OPEN c_cat;
20 FETCH c_cat into Dummy;
21
22 IF c_cat%FOUND THEN
23 CLOSE c_cat;
24 RETURN(1);
25 ELSE
26 CLOSE c_cat;
27 RETURN(0);
28 END IF;
29 END is_dublicate_cat;
30
31 -- ************************************************************************
32 --
33 -- PUBLIC FUNCTIONS
34 --
35 -- ************************************************************************
36 PROCEDURE check_unique_cat( x_application_id NUMBER,
37 x_category_code VARCHAR2) IS
38
39 BEGIN
40
41 IF is_dublicate_cat(x_application_id,x_category_code) = 1 THEN
42 fnd_message.set_name('FND', 'UNIQUE-DUPLICATE CODE');
43 app_exception.raise_exception;
44 END IF;
45
46
47 EXCEPTION
48 WHEN app_exceptions.application_exception THEN
49 RAISE;
50 WHEN OTHERS THEN
51 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
52 fnd_message.set_token('ROUTINE',
53 'FND_SEQ_CATEGORIES_PKG.check_unique_cat');
54 fnd_message.set_token('ERRNO',SQLCODE);
55 fnd_message.set_token('REASON',SQLERRM);
56 RAISE;
57
58
59 END check_unique_cat;
60
61 -- ************************************************************************
62 PROCEDURE insert_cat( x_application_id NUMBER,
63 x_category_code VARCHAR2,
64 x_category_name VARCHAR2,
65 x_description VARCHAR2,
66 x_table_name VARCHAR2,
67 x_last_updated_by NUMBER,
68 x_created_by NUMBER,
69 x_last_update_login NUMBER ) IS
70
71 BEGIN
72 IF (is_dublicate_cat(x_application_id,x_category_code)=0) THEN
73 INSERT INTO fnd_doc_sequence_categories (
74 application_id, last_update_date, last_updated_by,
75 code, name, description,
76 table_name, created_by, creation_date,
77 last_update_login )
78 SELECT x_application_id, sysdate, x_last_updated_by,
79 x_category_code, x_category_name, x_description,
80 x_table_name, x_created_by, sysdate,
81 x_last_update_login
82 FROM sys.dual;
83
84 END IF;
85
86 EXCEPTION
87 WHEN app_exceptions.application_exception THEN
88 RAISE;
89 WHEN OTHERS THEN
90 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
91 fnd_message.set_token('ROUTINE',
92 'FND_SEQ_CATEGORIES_PKG.insert_cat');
93 fnd_message.set_token('ERRNO',SQLCODE);
94 fnd_message.set_token('REASON',SQLERRM);
95 RAISE;
96
97 END insert_cat;
98
99 -- ************************************************************************
100 PROCEDURE update_cat( x_application_id NUMBER,
101 x_category_code VARCHAR2,
102 x_category_name VARCHAR2,
103 x_description VARCHAR2,
104 x_last_updated_by NUMBER ) IS
105 cat FND_DOC_SEQUENCE_CATEGORIES.code%TYPE;
106
107 BEGIN
108 IF is_dublicate_cat(x_application_id,x_category_code)=1 THEN
109 UPDATE fnd_doc_sequence_categories fcat
110 SET fcat.name = x_category_name,
111 fcat.description = x_description,
112 fcat.last_update_date = sysdate,
113 fcat.last_updated_by = x_last_updated_by
114 WHERE fcat.application_id = x_application_id
115 AND fcat.code = x_category_code;
116 END IF;
117
118 EXCEPTION
119 WHEN app_exceptions.application_exception THEN
120 RAISE;
121 WHEN OTHERS THEN
122 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
123 fnd_message.set_token('ROUTINE',
124 'FND_SEQ_CATEGORIES_PKG.update_cat');
125 fnd_message.set_token('ERRNO',SQLCODE);
126 fnd_message.set_token('REASON',SQLERRM);
127
128 END update_cat;
129
130
131 END FND_SEQ_CATEGORIES_PKG;