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