1 PACKAGE BODY CUSTOM_CLASS_PKG AS
2 /* $Header: MWACTCLB.pls 115.3 2004/04/08 22:37:25 vchitili ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_CLASSFILEID in number,
6 X_CLASSOLDFILE in VARCHAR2,
7 X_CLASSNEWFILE in VARCHAR2,
8 X_ENABLED in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER,
14 X_ATTRIBUTE_CATEGORY in VARCHAR2,
15 X_ATTRIBUTE1 in VARCHAR2,
16 X_ATTRIBUTE2 in VARCHAR2,
17 X_ATTRIBUTE3 in VARCHAR2,
18 X_ATTRIBUTE4 in VARCHAR2,
19 X_ATTRIBUTE5 in VARCHAR2,
20 X_ATTRIBUTE6 in VARCHAR2,
21 X_ATTRIBUTE7 in VARCHAR2,
22 X_ATTRIBUTE8 in VARCHAR2,
23 X_ATTRIBUTE9 in VARCHAR2,
24 X_ATTRIBUTE10 in VARCHAR2,
25 X_ATTRIBUTE11 in VARCHAR2,
26 X_ATTRIBUTE12 in VARCHAR2,
27 X_ATTRIBUTE13 in VARCHAR2,
28 X_ATTRIBUTE14 in VARCHAR2,
29 X_ATTRIBUTE15 in VARCHAR2
30 ) is
31 cursor get_row is
32 select ROWID from MWA_CLASS_CUSTOM_FILES
33 where CLASSFILEID = X_CLASSFILEID;
34 begin
35 insert into MWA_CLASS_CUSTOM_FILES (
36 CLASSFILEID,
37 CLASSOLDFILE,
38 CLASSNEWFILE,
39 ENABLED,
40 CREATION_DATE,
41 CREATED_BY,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_LOGIN,
45 ATTRIBUTE_CATEGORY,
46 ATTRIBUTE1,
47 ATTRIBUTE2,
48 ATTRIBUTE3,
49 ATTRIBUTE4,
50 ATTRIBUTE5,
51 ATTRIBUTE6,
52 ATTRIBUTE7,
53 ATTRIBUTE8,
54 ATTRIBUTE9,
55 ATTRIBUTE10,
56 ATTRIBUTE11,
57 ATTRIBUTE12,
58 ATTRIBUTE13,
59 ATTRIBUTE14,
60 ATTRIBUTE15)
61 values (
62 X_CLASSFILEID,
63 X_CLASSOLDFILE,
64 X_CLASSNEWFILE,
65 X_ENABLED,
66 X_CREATION_DATE,
67 X_CREATED_BY,
68 X_LAST_UPDATE_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_LOGIN,
71 X_ATTRIBUTE_CATEGORY,
72 X_ATTRIBUTE1,
73 X_ATTRIBUTE2,
74 X_ATTRIBUTE3,
75 X_ATTRIBUTE4,
76 X_ATTRIBUTE5,
77 X_ATTRIBUTE6,
78 X_ATTRIBUTE7,
79 X_ATTRIBUTE8,
80 X_ATTRIBUTE9,
81 X_ATTRIBUTE10,
82 X_ATTRIBUTE11,
83 X_ATTRIBUTE12,
84 X_ATTRIBUTE13,
85 X_ATTRIBUTE14,
86 X_ATTRIBUTE15
87 );
88 open get_row;
89 fetch get_row into X_ROWID;
90 if (get_row%notfound) then
91 close get_row;
92 raise no_data_found;
93 end if;
94 close get_row;
95
96 end insert_row;
97
98 /*****************************************************************/
99
100 procedure LOCK_ROW (
101 X_ROWID in out VARCHAR2,
102 X_CLASSFILEID in number,
103 X_CLASSOLDFILE in VARCHAR2,
104 X_CLASSNEWFILE in VARCHAR2,
105 X_ENABLED in VARCHAR2,
106 X_CREATION_DATE in DATE,
107 X_CREATED_BY in NUMBER,
108 X_LAST_UPDATE_DATE in DATE,
109 X_LAST_UPDATED_BY in NUMBER,
110 X_LAST_UPDATE_LOGIN in NUMBER,
111 X_ATTRIBUTE_CATEGORY in VARCHAR2,
112 X_ATTRIBUTE1 in VARCHAR2,
113 X_ATTRIBUTE2 in VARCHAR2,
114 X_ATTRIBUTE3 in VARCHAR2,
115 X_ATTRIBUTE4 in VARCHAR2,
116 X_ATTRIBUTE5 in VARCHAR2,
117 X_ATTRIBUTE6 in VARCHAR2,
118 X_ATTRIBUTE7 in VARCHAR2,
119 X_ATTRIBUTE8 in VARCHAR2,
120 X_ATTRIBUTE9 in VARCHAR2,
121 X_ATTRIBUTE10 in VARCHAR2,
122 X_ATTRIBUTE11 in VARCHAR2,
123 X_ATTRIBUTE12 in VARCHAR2,
124 X_ATTRIBUTE13 in VARCHAR2,
125 X_ATTRIBUTE14 in VARCHAR2,
126 X_ATTRIBUTE15 in VARCHAR2)
127 is
128 cursor get_lock is select
129 CLASSFILEID,
130 CLASSOLDFILE,
131 CLASSNEWFILE,
132 ENABLED,
133 CREATION_DATE,
134 CREATED_BY,
135 LAST_UPDATE_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN,
138 ATTRIBUTE_CATEGORY,
139 ATTRIBUTE1,
140 ATTRIBUTE2,
141 ATTRIBUTE3,
142 ATTRIBUTE4,
143 ATTRIBUTE5,
144 ATTRIBUTE6,
145 ATTRIBUTE7,
146 ATTRIBUTE8,
147 ATTRIBUTE9,
148 ATTRIBUTE10,
149 ATTRIBUTE11,
150 ATTRIBUTE12,
151 ATTRIBUTE13,
152 ATTRIBUTE14,
153 ATTRIBUTE15
154 from MWA_CLASS_CUSTOM_FILES
155 where ROWID = X_ROWID
156 for update of CLASSFILEID nowait;
157 recinfo get_lock%rowtype;
158
159 begin
160 open get_lock;
161 fetch get_lock into recinfo;
162 if (get_lock%notfound) then
163 close get_lock;
164 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
165 app_exception.raise_exception;
166 end if;
167 close get_lock;
168
169 if
170 -- check if the mandatory columns match values in the form
171 ((recinfo.CLASSFILEID = X_CLASSFILEID)
172 AND (recinfo.CLASSOLDFILE = X_CLASSOLDFILE)
173 AND (recinfo.CLASSNEWFILE = X_CLASSNEWFILE)
174 AND (recinfo.ENABLED = X_ENABLED)
175 AND (recinfo.CREATION_DATE = X_CREATION_DATE)
176 AND (recinfo.CREATED_BY = X_CREATED_BY)
177 AND (recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE)
178 AND (recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY)
179 AND (recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN)
180 AND ((recinfo.attribute_category = X_attribute_category)
181 OR ((recinfo.attribute_category is null) AND (X_attribute_category is null)))
182 AND ((recinfo.attribute1 = X_attribute1)
183 OR ((recinfo.attribute1 is null) AND (X_attribute1 is null)))
184 AND ((recinfo.attribute2 = X_attribute2)
185 OR ((recinfo.attribute2 is null) AND (X_attribute2 is null)))
186 AND ((recinfo.attribute3 = X_attribute3)
187 OR ((recinfo.attribute3 is null) AND (X_attribute3 is null)))
188 AND ((recinfo.attribute4 = X_attribute4)
189 OR ((recinfo.attribute4 is null) AND (X_attribute4 is null)))
190 AND ((recinfo.attribute5 = X_attribute5)
191 OR ((recinfo.attribute5 is null) AND (X_attribute5 is null)))
192 AND ((recinfo.attribute6 = X_attribute6)
193 OR ((recinfo.attribute6 is null) AND (X_attribute6 is null)))
194 AND ((recinfo.attribute7 = X_attribute7)
195 OR ((recinfo.attribute7 is null) AND (X_attribute7 is null)))
196 AND ((recinfo.attribute8 = X_attribute8)
197 OR ((recinfo.attribute8 is null) AND (X_attribute8 is null)))
198 AND ((recinfo.attribute9 = X_attribute9)
199 OR ((recinfo.attribute9 is null) AND (X_attribute9 is null)))
200 AND ((recinfo.attribute10 = X_attribute10)
201 OR ((recinfo.attribute10 is null) AND (X_attribute10 is null)))
202 AND ((recinfo.attribute11 = X_attribute11)
203 OR ((recinfo.attribute11 is null) AND (X_attribute11 is null)))
204 AND ((recinfo.attribute12 = X_attribute12)
205 OR ((recinfo.attribute12 is null) AND (X_attribute12 is null)))
206 AND ((recinfo.attribute13 = X_attribute13)
207 OR ((recinfo.attribute13 is null) AND (X_attribute13 is null)))
208 AND ((recinfo.attribute14 = X_attribute14)
209 OR ((recinfo.attribute14 is null) AND (X_attribute14 is null)))
210 AND ((recinfo.attribute15 = X_attribute15)
211 OR ((recinfo.attribute15 is null) AND (X_attribute15 is null)))
212 )
213 then
214 return;
215 else
216 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
217 app_exception.raise_exception;
218 end if;
219
220 end LOCK_ROW;
221
222 /*******************************************************************************/
223
224 procedure UPDATE_ROW (
225 X_ROWID in out VARCHAR2,
226 X_CLASSFILEID in NUMBER,
227 X_CLASSOLDFILE in Varchar2,
228 X_CLASSNEWFILE in Varchar2,
229 X_ENABLED in VARCHAR2,
230 X_LAST_UPDATE_DATE in DATE,
231 X_LAST_UPDATED_BY in NUMBER,
232 X_LAST_UPDATE_LOGIN in NUMBER,
233 X_ATTRIBUTE_CATEGORY in VARCHAR2,
234 X_ATTRIBUTE1 in VARCHAR2,
235 X_ATTRIBUTE2 in VARCHAR2,
236 X_ATTRIBUTE3 in VARCHAR2,
237 X_ATTRIBUTE4 in VARCHAR2,
238 X_ATTRIBUTE5 in VARCHAR2,
239 X_ATTRIBUTE6 in VARCHAR2,
240 X_ATTRIBUTE7 in VARCHAR2,
241 X_ATTRIBUTE8 in VARCHAR2,
242 X_ATTRIBUTE9 in VARCHAR2,
243 X_ATTRIBUTE10 in VARCHAR2,
244 X_ATTRIBUTE11 in VARCHAR2,
245 X_ATTRIBUTE12 in VARCHAR2,
246 X_ATTRIBUTE13 in VARCHAR2,
247 X_ATTRIBUTE14 in VARCHAR2,
248 X_ATTRIBUTE15 in VARCHAR2)
249 is
250 begin
251 update MWA_CLASS_CUSTOM_FILES set
252 CLASSOLDFILE = X_CLASSOLDFILE,
253 CLASSNEWFILE = X_CLASSNEWFILE,
254 ENABLED = X_ENABLED,
255 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
256 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
257 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
258 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
259 ATTRIBUTE1 = X_ATTRIBUTE1,
260 ATTRIBUTE2 = X_ATTRIBUTE2,
261 ATTRIBUTE3 = X_ATTRIBUTE3,
262 ATTRIBUTE4 = X_ATTRIBUTE4,
263 ATTRIBUTE5 = X_ATTRIBUTE5,
264 ATTRIBUTE6 = X_ATTRIBUTE6,
265 ATTRIBUTE7 = X_ATTRIBUTE7,
266 ATTRIBUTE8 = X_ATTRIBUTE8,
267 ATTRIBUTE9 = X_ATTRIBUTE9,
268 ATTRIBUTE10 = X_ATTRIBUTE10,
269 ATTRIBUTE11 = X_ATTRIBUTE11,
270 ATTRIBUTE12 = X_ATTRIBUTE12,
271 ATTRIBUTE13 = X_ATTRIBUTE13,
272 ATTRIBUTE14 = X_ATTRIBUTE14,
273 ATTRIBUTE15 = X_ATTRIBUTE15
274 where CLASSFILEID = X_CLASSFILEID ;
275
276 if (sql%notfound) then
277 raise no_data_found;
278 end if;
279
280 end UPDATE_ROW;
281
282 /********************************************************************************/
283
284 procedure DELETE_ROW (X_CLASSFILEID in NUMBER)
285 is
286 begin
287 delete from MWA_CLASS_CUSTOM_FILES
288 where CLASSFILEID = X_CLASSFILEID;
289
290 if (sql%notfound) then
291 raise no_data_found;
292 end if;
293
294 end DELETE_ROW;
295
296 /*******************************************************************************/
297
298 end CUSTOM_CLASS_PKG;