[Home] [Help]
PACKAGE BODY: APPS.CN_REASONS_PKG
Source
1 PACKAGE BODY CN_REASONS_PKG AS
2 /* $Header: cntresnb.pls 115.1 2002/04/24 12:04:58 pkm ship $*/
3
4
5 -- * -------------------------------------------------------------------------*
6 -- Procedure Name
7 -- Insert_row
8 -- Purpose
9 -- Main insert procedure
10 -- Note
11 -- 1. Primary key should be populated from sequence before call
12 -- this procedure. No refernece to sequence in this procedure.
13 -- 2. All paramaters are IN parameter.
14 -- * -------------------------------------------------------------------------*
15 PROCEDURE insert_row
16 ( p_reasons_all_rec IN REASONS_ALL_REC_TYPE) IS
17 --
18 l_reason VARCHAR2(8000);
19 --
20 BEGIN
21 l_reason := p_reasons_all_rec.reason;
22 INSERT into CN_REASONS
23 ( REASON_ID,
24 UPDATED_TABLE,
25 UPD_TABLE_ID,
26 REASON,
27 ATTRIBUTE_CATEGORY,
28 ATTRIBUTE1,
29 ATTRIBUTE2,
30 ATTRIBUTE3,
31 ATTRIBUTE4,
32 ATTRIBUTE5,
33 ATTRIBUTE6,
34 ATTRIBUTE7,
35 ATTRIBUTE8,
36 ATTRIBUTE9,
37 ATTRIBUTE10,
38 ATTRIBUTE11,
39 ATTRIBUTE12,
40 ATTRIBUTE13,
41 ATTRIBUTE14,
42 ATTRIBUTE15,
43 CREATED_BY,
44 CREATION_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATE_LOGIN,
48 OBJECT_VERSION_NUMBER,
49 LOOKUP_TYPE,
50 REASON_CODE,
51 UPDATE_FLAG)
52 select
53 DECODE(p_reasons_all_rec.REASON_ID, FND_API.G_MISS_NUM, NULL,
54 p_reasons_all_rec.REASON_ID),
55 DECODE(p_reasons_all_rec.UPDATED_TABLE, FND_API.G_MISS_CHAR, NULL,
56 p_reasons_all_rec.UPDATED_TABLE),
57 DECODE(p_reasons_all_rec.UPD_TABLE_ID, FND_API.G_MISS_NUM, NULL,
58 p_reasons_all_rec.UPD_TABLE_ID),
59 --EMPTY_CLOB(),
60 l_reason,
61 DECODE(p_reasons_all_rec.ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,
62 p_reasons_all_rec.ATTRIBUTE_CATEGORY),
63 DECODE(p_reasons_all_rec.ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,
64 p_reasons_all_rec.ATTRIBUTE1),
65 DECODE(p_reasons_all_rec.ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,
66 p_reasons_all_rec.ATTRIBUTE2),
67 DECODE(p_reasons_all_rec.ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,
68 p_reasons_all_rec.ATTRIBUTE3),
69 DECODE(p_reasons_all_rec.ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,
70 p_reasons_all_rec.ATTRIBUTE4),
71 DECODE(p_reasons_all_rec.ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,
72 p_reasons_all_rec.ATTRIBUTE5),
73 DECODE(p_reasons_all_rec.ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,
74 p_reasons_all_rec.ATTRIBUTE6),
75 DECODE(p_reasons_all_rec.ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,
76 p_reasons_all_rec.ATTRIBUTE7),
77 DECODE(p_reasons_all_rec.ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,
78 p_reasons_all_rec.ATTRIBUTE8),
79 DECODE(p_reasons_all_rec.ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,
80 p_reasons_all_rec.ATTRIBUTE9),
81 DECODE(p_reasons_all_rec.ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,
82 p_reasons_all_rec.ATTRIBUTE10),
83 DECODE(p_reasons_all_rec.ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,
84 p_reasons_all_rec.ATTRIBUTE11),
85 DECODE(p_reasons_all_rec.ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,
86 p_reasons_all_rec.ATTRIBUTE12),
87 DECODE(p_reasons_all_rec.ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,
88 p_reasons_all_rec.ATTRIBUTE13),
89 DECODE(p_reasons_all_rec.ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,
90 p_reasons_all_rec.ATTRIBUTE14),
91 DECODE(p_reasons_all_rec.ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,
92 p_reasons_all_rec.ATTRIBUTE15),
93 fnd_global.user_id,
94 Sysdate,
95 fnd_global.user_id,
96 Sysdate,
97 fnd_global.login_id,
98 1,
99 DECODE(p_reasons_all_rec.LOOKUP_TYPE, FND_API.G_MISS_CHAR, NULL,
100 p_reasons_all_rec.LOOKUP_TYPE),
101 DECODE(p_reasons_all_rec.REASON_CODE, FND_API.G_MISS_CHAR, NULL,
102 p_reasons_all_rec.REASON_CODE),
103 DECODE(p_reasons_all_rec.UPDATE_FLAG, FND_API.G_MISS_CHAR, NULL,
104 p_reasons_all_rec.UPDATE_FLAG)
105 from dual;
106
107 END insert_row;
108
109
110 -- * -------------------------------------------------------------------------*
111 -- Procedure Name
112 -- update_row
113 -- Purpose
114 -- Main update procedure
115 -- Note
116 -- 1. No object version checking, overwrite may happen
117 -- 2. Calling lock_update for object version checking
118 -- 3. All paramaters are IN parameter.
119 -- 4. Raise NO_DATA_FOUND exception if no reocrd updated (??)
120 -- * -------------------------------------------------------------------------*
121 PROCEDURE update_row
122 ( p_reasons_all_rec IN REASONS_ALL_REC_TYPE) IS
123 --
124 l_reason VARCHAR2(8000);
125 --
126 BEGIN
127 l_reason := p_reasons_all_rec.REASON;
128 UPDATE CN_REASONS_ALL oldrec
129 SET
130 UPDATED_TABLE = DECODE(p_reasons_all_rec.UPDATED_TABLE,
131 FND_API.G_MISS_CHAR,
132 oldrec.UPDATED_TABLE,
133 p_reasons_all_rec.UPDATED_TABLE),
134 UPD_TABLE_ID = DECODE(p_reasons_all_rec.UPD_TABLE_ID,
135 FND_API.G_MISS_NUM,
136 oldrec.UPD_TABLE_ID,
137 p_reasons_all_rec.UPD_TABLE_ID),
138 REASON = l_reason,
139 ATTRIBUTE_CATEGORY = DECODE(p_reasons_all_rec.ATTRIBUTE_CATEGORY,
140 FND_API.G_MISS_CHAR,
141 oldrec.ATTRIBUTE_CATEGORY,
142 p_reasons_all_rec.ATTRIBUTE_CATEGORY),
143 ATTRIBUTE1 = DECODE(p_reasons_all_rec.ATTRIBUTE1,
144 FND_API.G_MISS_CHAR,
145 oldrec.ATTRIBUTE1,
146 p_reasons_all_rec.ATTRIBUTE1),
147 ATTRIBUTE2 = DECODE(p_reasons_all_rec.ATTRIBUTE2,
148 FND_API.G_MISS_CHAR,
149 oldrec.ATTRIBUTE2,
150 p_reasons_all_rec.ATTRIBUTE2),
151 ATTRIBUTE3 = DECODE(p_reasons_all_rec.ATTRIBUTE3,
152 FND_API.G_MISS_CHAR,
153 oldrec.ATTRIBUTE3,
154 p_reasons_all_rec.ATTRIBUTE3),
155 ATTRIBUTE4 = DECODE(p_reasons_all_rec.ATTRIBUTE4,
156 FND_API.G_MISS_CHAR,
157 oldrec.ATTRIBUTE4,
158 p_reasons_all_rec.ATTRIBUTE4),
159 ATTRIBUTE5 = DECODE(p_reasons_all_rec.ATTRIBUTE5,
160 FND_API.G_MISS_CHAR,
161 oldrec.ATTRIBUTE5,
162 p_reasons_all_rec.ATTRIBUTE5),
163 ATTRIBUTE6 = DECODE(p_reasons_all_rec.ATTRIBUTE6,
164 FND_API.G_MISS_CHAR,
165 oldrec.ATTRIBUTE6,
166 p_reasons_all_rec.ATTRIBUTE6),
167 ATTRIBUTE7 = DECODE(p_reasons_all_rec.ATTRIBUTE7,
168 FND_API.G_MISS_CHAR,
169 oldrec.ATTRIBUTE7,
170 p_reasons_all_rec.ATTRIBUTE7),
171 ATTRIBUTE8 = DECODE(p_reasons_all_rec.ATTRIBUTE8,
172 FND_API.G_MISS_CHAR,
173 oldrec.ATTRIBUTE8,
174 p_reasons_all_rec.ATTRIBUTE8),
175 ATTRIBUTE9 = DECODE(p_reasons_all_rec.ATTRIBUTE9,
176 FND_API.G_MISS_CHAR,
177 oldrec.ATTRIBUTE9,
178 p_reasons_all_rec.ATTRIBUTE9),
179 ATTRIBUTE10 = DECODE(p_reasons_all_rec.ATTRIBUTE10,
180 FND_API.G_MISS_CHAR,
181 oldrec.ATTRIBUTE10,
182 p_reasons_all_rec.ATTRIBUTE10),
183 ATTRIBUTE11 = DECODE(p_reasons_all_rec.ATTRIBUTE11,
184 FND_API.G_MISS_CHAR,
185 oldrec.ATTRIBUTE11,
186 p_reasons_all_rec.ATTRIBUTE11),
187 ATTRIBUTE12 = DECODE(p_reasons_all_rec.ATTRIBUTE12,
188 FND_API.G_MISS_CHAR,
189 oldrec.ATTRIBUTE12,
190 p_reasons_all_rec.ATTRIBUTE12),
191 ATTRIBUTE13 = DECODE(p_reasons_all_rec.ATTRIBUTE13,
192 FND_API.G_MISS_CHAR,
193 oldrec.ATTRIBUTE13,
194 p_reasons_all_rec.ATTRIBUTE13),
195 ATTRIBUTE14 = DECODE(p_reasons_all_rec.ATTRIBUTE14,
196 FND_API.G_MISS_CHAR,
197 oldrec.ATTRIBUTE14,
198 p_reasons_all_rec.ATTRIBUTE14),
199 ATTRIBUTE15 = DECODE(p_reasons_all_rec.ATTRIBUTE15,
200 FND_API.G_MISS_CHAR,
201 oldrec.ATTRIBUTE15,
202 p_reasons_all_rec.ATTRIBUTE15),
203 LAST_UPDATED_BY = fnd_global.user_id,
204 LAST_UPDATE_DATE = Sysdate,
205 LAST_UPDATE_LOGIN = fnd_global.login_id,
206 OBJECT_VERSION_NUMBER = oldrec.OBJECT_VERSION_NUMBER + 1,
207 LOOKUP_TYPE = DECODE(p_reasons_all_rec.LOOKUP_TYPE,
208 FND_API.G_MISS_CHAR,
209 oldrec.LOOKUP_TYPE,
210 p_reasons_all_rec.LOOKUP_TYPE),
211 REASON_CODE = DECODE(p_reasons_all_rec.REASON_CODE,
212 FND_API.G_MISS_CHAR,
213 oldrec.REASON_CODE,
214 p_reasons_all_rec.REASON_CODE)
215 WHERE reason_id = p_reasons_all_rec.reason_id;
216
217 IF (SQL%ROWCOUNT=0) THEN
218 RAISE NO_DATA_FOUND;
219 END IF;
220
221 END update_row;
222
223
224 -- * -------------------------------------------------------------------------*
225 -- Procedure Name
226 -- lock_update_row
227 -- Purpose
228 -- Main lcok and update procedure
229 -- Note
230 -- 1. Object version checking is performed before checking
231 -- 2. Calling update_row if you don not want object version checking
232 -- 3. All paramaters are IN parameter.
233 -- 4. Raise NO_DATA_FOUND exception if no reocrd updated (??)
234 -- * -------------------------------------------------------------------------*
235 PROCEDURE lock_update_row
236 ( p_reasons_all_rec IN REASONS_ALL_REC_TYPE) IS
237
238 CURSOR c IS
239 SELECT object_version_number
240 FROM CN_REASONS_ALL
241 WHERE reason_id = p_reasons_all_rec.reason_id;
242
243 tlinfo c%ROWTYPE ;
244 --
245 l_reason VARCHAR2(8000);
246 --
247 BEGIN
248 open c;
249 fetch c into tlinfo;
250 if (c%notfound) then
251 close c;
252 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
253 fnd_msg_pub.add;
254 raise fnd_api.g_exc_unexpected_error;
255 end if;
256 close c;
257 if (tlinfo.object_version_number <> p_reasons_all_rec.object_version_number) then
258 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
259 fnd_msg_pub.add;
260 raise fnd_api.g_exc_unexpected_error;
261 end if;
262 l_reason := p_reasons_all_rec.REASON;
263 UPDATE CN_REASONS_ALL oldrec
264 SET
265 UPDATED_TABLE = DECODE(p_reasons_all_rec.UPDATED_TABLE,
266 FND_API.G_MISS_CHAR,
267 oldrec.UPDATED_TABLE,
268 p_reasons_all_rec.UPDATED_TABLE),
269 UPD_TABLE_ID = DECODE(p_reasons_all_rec.UPD_TABLE_ID,
270 FND_API.G_MISS_NUM,
271 oldrec.UPD_TABLE_ID,
272 p_reasons_all_rec.UPD_TABLE_ID),
273 REASON = l_reason,
274 ATTRIBUTE_CATEGORY = DECODE(p_reasons_all_rec.ATTRIBUTE_CATEGORY,
275 FND_API.G_MISS_CHAR,
276 oldrec.ATTRIBUTE_CATEGORY,
277 p_reasons_all_rec.ATTRIBUTE_CATEGORY),
278 ATTRIBUTE1 = DECODE(p_reasons_all_rec.ATTRIBUTE1,
279 FND_API.G_MISS_CHAR,
280 oldrec.ATTRIBUTE1,
281 p_reasons_all_rec.ATTRIBUTE1),
282 ATTRIBUTE2 = DECODE(p_reasons_all_rec.ATTRIBUTE2,
283 FND_API.G_MISS_CHAR,
284 oldrec.ATTRIBUTE2,
285 p_reasons_all_rec.ATTRIBUTE2),
286 ATTRIBUTE3 = DECODE(p_reasons_all_rec.ATTRIBUTE3,
287 FND_API.G_MISS_CHAR,
288 oldrec.ATTRIBUTE3,
289 p_reasons_all_rec.ATTRIBUTE3),
290 ATTRIBUTE4 = DECODE(p_reasons_all_rec.ATTRIBUTE4,
291 FND_API.G_MISS_CHAR,
292 oldrec.ATTRIBUTE4,
293 p_reasons_all_rec.ATTRIBUTE4),
294 ATTRIBUTE5 = DECODE(p_reasons_all_rec.ATTRIBUTE5,
295 FND_API.G_MISS_CHAR,
296 oldrec.ATTRIBUTE5,
297 p_reasons_all_rec.ATTRIBUTE5),
298 ATTRIBUTE6 = DECODE(p_reasons_all_rec.ATTRIBUTE6,
299 FND_API.G_MISS_CHAR,
300 oldrec.ATTRIBUTE6,
301 p_reasons_all_rec.ATTRIBUTE6),
302 ATTRIBUTE7 = DECODE(p_reasons_all_rec.ATTRIBUTE7,
303 FND_API.G_MISS_CHAR,
304 oldrec.ATTRIBUTE7,
305 p_reasons_all_rec.ATTRIBUTE7),
306 ATTRIBUTE8 = DECODE(p_reasons_all_rec.ATTRIBUTE8,
307 FND_API.G_MISS_CHAR,
308 oldrec.ATTRIBUTE8,
309 p_reasons_all_rec.ATTRIBUTE8),
310 ATTRIBUTE9 = DECODE(p_reasons_all_rec.ATTRIBUTE9,
311 FND_API.G_MISS_CHAR,
312 oldrec.ATTRIBUTE9,
313 p_reasons_all_rec.ATTRIBUTE9),
314 ATTRIBUTE10 = DECODE(p_reasons_all_rec.ATTRIBUTE10,
315 FND_API.G_MISS_CHAR,
316 oldrec.ATTRIBUTE10,
317 p_reasons_all_rec.ATTRIBUTE10),
318 ATTRIBUTE11 = DECODE(p_reasons_all_rec.ATTRIBUTE11,
319 FND_API.G_MISS_CHAR,
320 oldrec.ATTRIBUTE11,
321 p_reasons_all_rec.ATTRIBUTE11),
322 ATTRIBUTE12 = DECODE(p_reasons_all_rec.ATTRIBUTE12,
323 FND_API.G_MISS_CHAR,
324 oldrec.ATTRIBUTE12,
325 p_reasons_all_rec.ATTRIBUTE12),
326 ATTRIBUTE13 = DECODE(p_reasons_all_rec.ATTRIBUTE13,
327 FND_API.G_MISS_CHAR,
328 oldrec.ATTRIBUTE13,
329 p_reasons_all_rec.ATTRIBUTE13),
330 ATTRIBUTE14 = DECODE(p_reasons_all_rec.ATTRIBUTE14,
331 FND_API.G_MISS_CHAR,
332 oldrec.ATTRIBUTE14,
333 p_reasons_all_rec.ATTRIBUTE14),
334 ATTRIBUTE15 = DECODE(p_reasons_all_rec.ATTRIBUTE15,
335 FND_API.G_MISS_CHAR,
336 oldrec.ATTRIBUTE15,
337 p_reasons_all_rec.ATTRIBUTE15),
338 LAST_UPDATED_BY = fnd_global.user_id,
339 LAST_UPDATE_DATE = Sysdate,
340 LAST_UPDATE_LOGIN = fnd_global.login_id,
341 OBJECT_VERSION_NUMBER = oldrec.OBJECT_VERSION_NUMBER + 1,
342 LOOKUP_TYPE = DECODE(p_reasons_all_rec.LOOKUP_TYPE,
343 FND_API.G_MISS_CHAR,
344 oldrec.LOOKUP_TYPE,
345 p_reasons_all_rec.LOOKUP_TYPE),
346 REASON_CODE = DECODE(p_reasons_all_rec.REASON_CODE,
347 FND_API.G_MISS_CHAR,
348 oldrec.REASON_CODE,
349 p_reasons_all_rec.REASON_CODE)
350 WHERE reason_id = p_reasons_all_rec.reason_id;
351 END lock_update_row;
352
353
354 -- * -------------------------------------------------------------------------*
355 -- Procedure Name
356 -- delete_row
357 -- Purpose
358 -- Main lcok and update procedure
359 -- Note
360 -- 1. All paramaters are IN parameter.
361 -- 2. Raise NO_DATA_FOUND exception if no reocrd deleted (??)
362 -- * -------------------------------------------------------------------------*
363 PROCEDURE delete_row(
364 p_reason_id NUMBER) IS
365 BEGIN
366 DELETE FROM CN_REASONS_ALL
367 WHERE reason_id = p_reason_id;
368 END Delete_row;
369 --
370 END CN_REASONS_PKG;