[Home] [Help]
PACKAGE BODY: APPS.HZ_WORD_LISTS_PKG
Source
1 PACKAGE BODY HZ_WORD_LISTS_PKG AS
2 /* $Header: ARHDQWLB.pls 120.5 2005/06/16 21:11:22 jhuang noship $ */
3 PROCEDURE Insert_Row(
4 X_WORD_LIST_ID IN OUT NOCOPY NUMBER,
5 X_WORD_LIST_NAME VARCHAR2,
6 X_LANGUAGE VARCHAR2,
7 X_SOURCE_NAME VARCHAR2,
8 X_CREATED_BY NUMBER,
9 X_CREATION_DATE DATE,
10 X_LAST_UPDATE_LOGIN NUMBER,
11 X_LAST_UPDATE_DATE DATE,
12 X_LAST_UPDATED_BY NUMBER,
13 X_OBJECT_VERSION_NUMBER NUMBER,
14 X_MSG_COUNT IN OUT NOCOPY NUMBER,
15 X_NON_DELIMITED_FLAG VARCHAR2 DEFAULT 'N')
16 IS
17 l_count NUMBER;
18 BEGIN
19 IF x_WORD_LIST_ID IS NOT NULL THEN
20 x_msg_count := 0;
21 /* Word List should be unique */
22 SELECT count(*) INTO l_count
23 FROM hz_word_lists
24 WHERE word_list_name = UPPER(X_WORD_LIST_NAME);
25
26 IF l_count > 0 THEN
27 x_msg_count := x_msg_count +1;
28 l_count := 0;
29 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DUPL_WORD_LIST' );
30 FND_MSG_PUB.ADD;
31 return;
32 END IF;
33 END IF;
34 Insert_Row(
35 X_WORD_LIST_ID,
36 X_WORD_LIST_NAME,
37 X_LANGUAGE,
38 X_SOURCE_NAME,
39 X_CREATED_BY,
40 X_CREATION_DATE,
41 X_LAST_UPDATE_LOGIN,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_OBJECT_VERSION_NUMBER,
45 X_NON_DELIMITED_FLAG);
46 END Insert_Row;
47 PROCEDURE Insert_Row(
48 X_WORD_LIST_ID IN OUT NOCOPY NUMBER,
49 X_WORD_LIST_NAME VARCHAR2,
50 X_LANGUAGE VARCHAR2,
51 X_SOURCE_NAME VARCHAR2,
52 X_CREATED_BY NUMBER,
53 X_CREATION_DATE DATE,
54 X_LAST_UPDATE_LOGIN NUMBER,
55 X_LAST_UPDATE_DATE DATE,
56 X_LAST_UPDATED_BY NUMBER,
57 X_OBJECT_VERSION_NUMBER NUMBER,
58 X_NON_DELIMITED_FLAG VARCHAR2 DEFAULT 'N')
59 IS
60 CURSOR C2 IS SELECT HZ_WORD_LISTS_s.nextval FROM sys.dual;
61 BEGIN
62 IF ( X_WORD_LIST_ID IS NULL) OR (X_WORD_LIST_ID = FND_API.G_MISS_NUM) THEN
63 OPEN C2;
64 FETCH C2 INTO X_WORD_LIST_ID;
65 CLOSE C2;
66 END IF;
67
68 INSERT INTO HZ_WORD_LISTS(
69 WORD_LIST_ID,
70 WORD_LIST_NAME,
71 LANGUAGE,
72 SOURCE_NAME,
73 CREATED_BY,
74 CREATION_DATE,
75 LAST_UPDATE_LOGIN,
76 LAST_UPDATE_DATE,
77 LAST_UPDATED_BY,
78 OBJECT_VERSION_NUMBER,
79 NON_DELIMITED_FLAG
80 )
81 VALUES (
82 decode(X_WORD_LIST_ID, FND_API.G_MISS_NUM, NULL,
83 X_WORD_LIST_ID),
84 decode(X_WORD_LIST_NAME, FND_API.G_MISS_CHAR, NULL,
85 UPPER(X_WORD_LIST_NAME)),
86 decode(X_LANGUAGE, FND_API.G_MISS_CHAR, NULL,
87 X_LANGUAGE),
88 decode(X_SOURCE_NAME, FND_API.G_MISS_CHAR, NULL,
89 X_SOURCE_NAME),
90 decode(X_created_by, FND_API.G_MISS_NUM, NULL,
91 X_created_by),
92 decode(X_creation_date, FND_API.G_MISS_DATE, NULL,
93 X_creation_date),
94 decode(X_last_update_login, FND_API.G_MISS_NUM, NULL,
95 X_last_update_login),
96 decode(X_last_update_date, FND_API.G_MISS_DATE, NULL,
97 X_last_update_date),
98 decode(X_last_updated_by, FND_API.G_MISS_NUM, NULL,
99 X_last_updated_by),
100 1,
101 decode(X_NON_DELIMITED_FLAG,FND_API.G_MISS_CHAR,NULL,X_NON_DELIMITED_FLAG)
102 );
103
104 End Insert_Row;
105
106 PROCEDURE Update_Row(
107 X_WORD_LIST_ID NUMBER,
108 X_WORD_LIST_NAME VARCHAR2,
109 X_LANGUAGE VARCHAR2,
110 X_SOURCE_NAME VARCHAR2,
111 X_CREATED_BY NUMBER,
112 X_CREATION_DATE DATE,
113 X_LAST_UPDATE_LOGIN NUMBER,
114 X_LAST_UPDATE_DATE DATE,
115 X_LAST_UPDATED_BY NUMBER,
116 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
117 X_MSG_COUNT IN OUT NOCOPY NUMBER,
118 X_NON_DELIMITED_FLAG VARCHAR2 DEFAULT 'N')
119 IS
120 l_count NUMBER;
121 BEGIN
122 IF x_WORD_LIST_ID IS NOT NULL THEN
123 x_msg_count := 0;
124 /* Word List should be unique */
125 SELECT count(*) INTO l_count
126 FROM hz_word_lists
127 WHERE word_list_name = UPPER(x_WORD_LIST_name)
128 AND WORD_LIST_ID <> X_WORD_LIST_ID;
129
130 IF l_count > 0 THEN
131 x_msg_count := x_msg_count +1;
132 l_count := 0;
133 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DUPL_WORD_LIST' );
134 FND_MSG_PUB.ADD;
135 return;
136 END IF;
137 END IF;
138 Update_Row(
139 X_WORD_LIST_ID,
140 X_WORD_LIST_NAME,
141 X_LANGUAGE,
142 X_SOURCE_NAME,
143 X_CREATED_BY,
144 X_CREATION_DATE,
145 X_LAST_UPDATE_LOGIN,
146 X_LAST_UPDATE_DATE,
147 X_LAST_UPDATED_BY,
148 X_OBJECT_VERSION_NUMBER,
149 X_NON_DELIMITED_FLAG);
150 END;
151 PROCEDURE Update_Row(
152 X_WORD_LIST_ID NUMBER,
153 X_WORD_LIST_NAME VARCHAR2,
154 X_LANGUAGE VARCHAR2,
155 X_SOURCE_NAME VARCHAR2,
156 X_CREATED_BY NUMBER,
157 X_CREATION_DATE DATE,
158 X_LAST_UPDATE_LOGIN NUMBER,
159 X_LAST_UPDATE_DATE DATE,
160 X_LAST_UPDATED_BY NUMBER,
161 X_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
162 X_NON_DELIMITED_FLAG VARCHAR2 DEFAULT 'N')
163 IS
164 p_object_version_number number;
165
166 BEGIN
167
168 p_object_version_number := NVL(X_object_version_number, 1) + 1;
169
170
171 UPDATE HZ_WORD_LISTS
172 SET
173 WORD_LIST_NAME = decode(X_WORD_LIST_NAME, FND_API.G_MISS_CHAR, WORD_LIST_NAME,
174 UPPER(X_WORD_LIST_NAME)),
175 LANGUAGE = decode(X_LANGUAGE , FND_API.G_MISS_CHAR , LANGUAGE,
176 X_LANGUAGE),
177 SOURCE_NAME = decode(X_SOURCE_NAME, FND_API.G_MISS_CHAR , SOURCE_NAME,
178 X_SOURCE_NAME),
179 -- bug 3032780
180 /*
181 CREATED_BY = decode(X_created_by,FND_API.G_MISS_NUM, CREATED_BY,
182 X_created_by),
183 CREATION_DATE = decode(X_CREATION_DATE,FND_API.G_MISS_DATE, CREATION_DATE,
184 X_CREATION_DATE),*/
185 LAST_UPDATE_LOGIN = decode(X_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN,
186 X_LAST_UPDATE_LOGIN),
187 LAST_UPDATE_DATE = decode(X_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,
188 X_LAST_UPDATE_DATE),
189 LAST_UPDATED_BY = decode(X_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,
190 X_LAST_UPDATED_BY),
191 OBJECT_VERSION_NUMBER = p_object_version_number,
192 NON_DELIMITED_FLAG = decode(X_NON_DELIMITED_FLAG,FND_API.G_MISS_CHAR,NON_DELIMITED_FLAG,
193 X_NON_DELIMITED_FLAG)
194 WHERE WORD_LIST_ID = X_WORD_LIST_ID;
195
196
197 If (SQL%NOTFOUND) then
198 RAISE NO_DATA_FOUND;
199 End If;
200
201 X_OBJECT_VERSION_NUMBER := p_object_version_number;
202
203 END Update_Row;
204
205 PROCEDURE Delete_Row(X_WORD_LIST_ID NUMBER)
206 IS
207 BEGIN
208 --Delete all the word replacements
209 HZ_WORD_REPLACEMENTS1_PKG.Delete_Row(X_WORD_LIST_ID); --Bug No: 3868758.
210
211 DELETE FROM HZ_WORD_LISTS
212 WHERE WORD_LIST_ID = X_WORD_LIST_ID;
213
214 If (SQL%NOTFOUND) then
215 RAISE NO_DATA_FOUND;
216 End If;
217 END Delete_Row;
218
219 PROCEDURE Lock_Row(
220 X_WORD_LIST_ID IN OUT NOCOPY NUMBER,
221 X_OBJECT_VERSION_NUMBER IN NUMBER)
222 IS
223 CURSOR C IS
224 SELECT OBJECT_VERSION_NUMBER
225 FROM HZ_WORD_LISTS
226 WHERE WORD_LIST_ID = X_WORD_LIST_ID
227 FOR UPDATE OF word_list_id NOWAIT;
228 Recinfo C%ROWTYPE;
229 BEGIN
230 OPEN C;
231 FETCH C INTO Recinfo;
232 If (C%NOTFOUND) then
233 CLOSE C;
234 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
235 APP_EXCEPTION.RAISE_EXCEPTION;
236 End If;
237 CLOSE C;
238 if(
239 ( recinfo.OBJECT_VERSION_NUMBER IS NULL AND X_object_version_number IS NULL )
240 OR ( recinfo.OBJECT_VERSION_NUMBER IS NOT NULL AND
241 X_object_version_number IS NOT NULL AND
242 recinfo.OBJECT_VERSION_NUMBER = X_object_version_number )
243 ) then
244 null;
245 else
246 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
247 app_exception.raise_exception;
248 end if;
249
250 END Lock_Row;
251 END HZ_WORD_LISTS_PKG;