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