[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_EXT_TABLE_PKG
Source
1 PACKAGE BODY CN_CALC_EXT_TABLE_PKG AS
2 /* $Header: cntexttb.pls 115.6 2002/11/21 21:09:24 hlchen ship $ */
3 --
4 -- Package Name
5 -- CN_CALC_EXT_TABLE_PKG
6 -- Purpose
7 -- Table Handler for CN_CALC_EXT_TABLE
8 --
9 -- History
10 -- 02-feb-01 Kumar Sivasankaran
11 -- ==========================================================================
12 -- |
13 -- | PRIVATE VARIABLES
14 -- |
15 -- ==========================================================================
16 g_program_type VARCHAR2(30) := NULL;
17 -- ==========================================================================
18 -- |
19 -- | PRIVATE ROUTINES
20 -- |
21 -- ==========================================================================
22
23 -- ==========================================================================
24 -- | Custom Validation
25 -- ==========================================================================
26
27 -- ==========================================================================
28 -- Procedure Name
29 -- Get_UID
30 -- Purpose
31 -- Get the Sequence Number to Create a new quota pay element
32 -- ==========================================================================
33 PROCEDURE Get_UID( x_calc_ext_table_id IN OUT NOCOPY NUMBER) IS
34
35 BEGIN
36
37 SELECT cn_calc_ext_tables_s.nextval
38 INTO X_calc_ext_table_id
39 FROM dual;
40
41 END Get_UID;
42
43 -- ==========================================================================
44 -- Procedure Name
45 -- Insert_row
46 -- Purpose
47 -- Main insert procedure
48 -- ==========================================================================
49 PROCEDURE insert_row
50 (x_calc_ext_table_id IN OUT NOCOPY NUMBER
51 ,p_name VARCHAR2 := NULL
52 ,p_description VARCHAR2 := NULL
53 ,p_internal_table_id NUMBER := NULL
54 ,p_external_table_id NUMBER := NULL
55 ,P_USED_FLAG VARCHAR2 := NULL
56 ,P_SCHEMA VARCHAR2 := NULL
57 ,P_EXTERNAL_TABLE_NAME VARCHAR2 := NULL
58 ,P_ALIAS VARCHAR2 := NULL
59 ,p_attribute_category VARCHAR2 := NULL
60 ,p_attribute1 VARCHAR2 := NULL
61 ,p_attribute2 VARCHAR2 := NULL
62 ,p_attribute3 VARCHAR2 := NULL
63 ,p_attribute4 VARCHAR2 := NULL
64 ,p_attribute5 VARCHAR2 := NULL
65 ,p_attribute6 VARCHAR2 := NULL
66 ,p_attribute7 VARCHAR2 := NULL
67 ,p_attribute8 VARCHAR2 := NULL
68 ,p_attribute9 VARCHAR2 := NULL
69 ,p_attribute10 VARCHAR2 := NULL
70 ,p_attribute11 VARCHAR2 := NULL
71 ,p_attribute12 VARCHAR2 := NULL
72 ,p_attribute13 VARCHAR2 := NULL
73 ,p_attribute14 VARCHAR2 := NULL
74 ,p_attribute15 VARCHAR2 := NULL
75 ,p_Created_By NUMBER
76 ,p_Creation_Date DATE
77 ,p_Last_Updated_By NUMBER
78 ,p_Last_Update_Date DATE
79 ,p_Last_Update_Login NUMBER )
80 IS
81 l_dummy NUMBER;
82
83 BEGIN
84
85 Get_UID( x_calc_ext_table_id );
86
87 INSERT INTO cn_calc_ext_tables
88 ( calc_ext_table_id
89 ,name
90 ,DESCRIPTION
91 ,INTERNAL_TABLE_ID
92 ,EXTERNAL_TABLE_ID
93 ,USED_FLAG
94 ,SCHEMA
95 ,EXTERNAL_TABLE_NAME
96 ,ALIAS
97 ,attribute_category
98 ,attribute1
99 ,attribute2
100 ,attribute3
101 ,attribute4
102 ,attribute5
103 ,attribute6
104 ,attribute7
105 ,attribute8
106 ,attribute9
107 ,attribute10
108 ,attribute11
109 ,attribute12
110 ,attribute13
111 ,attribute14
112 ,attribute15
113 ,Created_By
114 ,Creation_Date
115 ,Last_Updated_By
116 ,Last_Update_Date
117 ,Last_Update_Login)
118 VALUES
119 ( x_calc_ext_table_id
120 ,p_name
121 ,p_DESCRIPTION
122 ,p_INTERNAL_TABLE_ID
123 ,p_EXTERNAL_TABLE_ID
124 ,p_USED_FLAG
125 ,p_SCHEMA
126 ,p_EXTERNAL_TABLE_NAME
127 ,nvl(p_ALIAS, 'CN'||x_calc_ext_table_id)
128 ,p_attribute_category
129 ,p_attribute1
130 ,p_attribute2
131 ,p_attribute3
132 ,p_attribute4
133 ,p_attribute5
134 ,p_attribute6
135 ,p_attribute7
136 ,p_attribute8
137 ,p_attribute9
138 ,p_attribute10
139 ,p_attribute11
140 ,p_attribute12
141 ,p_attribute13
142 ,p_attribute14
143 ,p_attribute15
144 ,p_Created_By
145 ,p_Creation_Date
146 ,p_Last_Updated_By
147 ,p_Last_Update_Date
148 ,p_Last_Update_Login
149 );
150
151 select 1 INTO l_dummy from CN_CALC_EXT_TABLES
152 where calc_ext_table_id = x_calc_ext_table_id;
153
154 END Insert_row;
155
156 -- ==========================================================================
157 -- Procedure Name
158 -- Lock_row
159 -- Purpose
160 -- Lock db row after form record is changed
161 -- Notes
162 -- ==========================================================================
163 PROCEDURE lock_row
164 (p_calc_ext_table_id NUMBER
165 ,p_name VARCHAR2 := NULL
166 ,p_description VARCHAR2 := NULL
167 ,p_internal_table_id NUMBER := NULL
168 ,p_external_table_id NUMBER := NULL
169 ,P_USED_FLAG VARCHAR2 := NULL
170 ,P_SCHEMA VARCHAR2 := NULL
171 ,P_EXTERNAL_TABLE_NAME VARCHAR2 := NULL
172 ,P_ALIAS VARCHAR2 := NULL
173 ,p_attribute_category VARCHAR2 := NULL
174 ,p_attribute1 VARCHAR2 := NULL
175 ,p_attribute2 VARCHAR2 := NULL
176 ,p_attribute3 VARCHAR2 := NULL
177 ,p_attribute4 VARCHAR2 := NULL
178 ,p_attribute5 VARCHAR2 := NULL
179 ,p_attribute6 VARCHAR2 := NULL
180 ,p_attribute7 VARCHAR2 := NULL
181 ,p_attribute8 VARCHAR2 := NULL
182 ,p_attribute9 VARCHAR2 := NULL
183 ,p_attribute10 VARCHAR2 := NULL
184 ,p_attribute11 VARCHAR2 := NULL
185 ,p_attribute12 VARCHAR2 := NULL
186 ,p_attribute13 VARCHAR2 := NULL
187 ,p_attribute14 VARCHAR2 := NULL
188 ,p_attribute15 VARCHAR2 := NULL
189 ) IS
190
191 CURSOR C IS
192 SELECT *
193 FROM cn_calc_ext_tables
194 WHERE calc_ext_table_id = p_calc_ext_table_id
195 FOR UPDATE of calc_ext_table_id NOWAIT;
196
197 tlinfo C%ROWTYPE;
198
199 BEGIN
200 OPEN C;
201 FETCH C INTO tlinfo;
202
203 IF (C%NOTFOUND) then
204 CLOSE C;
205 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
206 app_exception.raise_exception;
207 END IF;
208 CLOSE C;
209 NULL;
210
211 END Lock_row;
212
213 -- ==========================================================================
214 -- Procedure Name
215 -- Update Record
216 -- Purpose
217 --
218 -- ==========================================================================
219 PROCEDURE update_row
220 (p_calc_ext_table_id NUMBER
221 ,p_name VARCHAR2
222 ,p_description VARCHAR2
223 ,p_internal_table_id NUMBER
224 ,p_external_table_id NUMBER
225 ,P_USED_FLAG VARCHAR2
226 ,P_SCHEMA VARCHAR2
227 ,P_EXTERNAL_TABLE_NAME VARCHAR2
228 ,P_ALIAS VARCHAR2
229 ,p_attribute_category VARCHAR2
230 ,p_attribute1 VARCHAR2
231 ,p_attribute2 VARCHAR2
232 ,p_attribute3 VARCHAR2
233 ,p_attribute4 VARCHAR2
234 ,p_attribute5 VARCHAR2
235 ,p_attribute6 VARCHAR2
236 ,p_attribute7 VARCHAR2
237 ,p_attribute8 VARCHAR2
238 ,p_attribute9 VARCHAR2
239 ,p_attribute10 VARCHAR2
240 ,p_attribute11 VARCHAR2
241 ,p_attribute12 VARCHAR2
242 ,p_attribute13 VARCHAR2
243 ,p_attribute14 VARCHAR2
244 ,p_attribute15 VARCHAR2
245 ,p_Last_Updated_By NUMBER
246 ,p_Last_Update_Date DATE
247 ,p_Last_Update_Login NUMBER ) IS
248
249
250 l_calc_ext_table_id cn_calc_ext_tables.calc_ext_table_id%TYPE;
251 l_name cn_calc_ext_tables.name%TYPE;
252 l_description cn_calc_ext_tables.description%TYPE;
253 l_internal_table_id cn_calc_ext_tables.internal_table_id%TYPE;
254 l_external_table_id cn_calc_ext_tables.external_table_id%TYPE;
255 l_USED_FLAG cn_calc_ext_tables.used_flag%TYPE;
256 l_SCHEMA cn_calc_ext_tables.schema%TYPE;
257 l_EXTERNAL_TABLE_NAME cn_calc_ext_tables.external_table_name%TYPE;
258 l_ALIAS cn_calc_ext_tables.alias%TYPE;
259 l_attribute_category cn_calc_ext_tables.attribute_category%TYPE;
260 l_attribute1 cn_calc_ext_tables.attribute1%TYPE;
261 l_attribute2 cn_calc_ext_tables.attribute2%TYPE;
262 l_attribute3 cn_calc_ext_tables.attribute3%TYPE;
263 l_attribute4 cn_calc_ext_tables.attribute4%TYPE;
264 l_attribute5 cn_calc_ext_tables.attribute5%TYPE;
265 l_attribute6 cn_calc_ext_tables.attribute6%TYPE;
266 l_attribute7 cn_calc_ext_tables.attribute7%TYPE;
267 l_attribute8 cn_calc_ext_tables.attribute8%TYPE;
268 l_attribute9 cn_calc_ext_tables.attribute9%TYPE;
269 l_attribute10 cn_calc_ext_tables.attribute10%TYPE;
270 l_attribute11 cn_calc_ext_tables.attribute11%TYPE;
271 l_attribute12 cn_calc_ext_tables.attribute12%TYPE;
272 l_attribute13 cn_calc_ext_tables.attribute13%TYPE;
273 l_attribute14 cn_calc_ext_tables.attribute14%TYPE;
274 l_attribute15 cn_calc_ext_tables.attribute15%TYPE;
275
276 CURSOR C IS
277 SELECT *
278 FROM cn_calc_ext_tables
279 WHERE calc_ext_table_id = p_calc_ext_table_id
280 FOR UPDATE of calc_ext_table_id NOWAIT;
281 oldrow C%ROWTYPE;
282
283 BEGIN
284 OPEN C;
285 FETCH C INTO oldrow;
286
287 IF (C%NOTFOUND) then
288 CLOSE C;
289 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
290 app_exception.raise_exception;
291 END IF;
292 CLOSE C;
293
294
295 SELECT decode(p_calc_ext_table_id,
296 fnd_api.g_miss_num, oldrow.calc_ext_table_id,
297 p_calc_ext_table_id),
298 decode(p_name,
299 fnd_api.g_miss_char, oldrow.name,
300 p_name),
301 decode(p_description,
302 fnd_api.g_miss_char, oldrow.description,
303 p_description),
304 decode(p_internal_table_id,
305 fnd_api.g_miss_num, oldrow.internal_table_id,
306 p_internal_table_id),
307 decode(p_external_table_id,
308 fnd_api.g_miss_num, oldrow.external_table_id,
309 p_external_table_id),
310 decode(p_used_flag,
311 fnd_api.g_miss_char, oldrow.used_flag,
312 p_used_flag),
313 decode(p_schema,
314 fnd_api.g_miss_char, oldrow.schema,
315 p_schema),
316 decode(p_external_table_name,
317 fnd_api.g_miss_char, oldrow.external_table_name,
318 p_external_table_name),
319 decode(p_alias,
320 fnd_api.g_miss_char, oldrow.alias,
321 p_alias),
322 decode(p_attribute_category,
323 fnd_api.g_miss_char, oldrow.attribute_category,
324 p_attribute_category),
325 decode(p_attribute1,
326 fnd_api.g_miss_char, oldrow.attribute1,
327 p_attribute1),
328 decode(p_attribute2,
329 fnd_api.g_miss_char, oldrow.attribute2,
330 p_attribute2),
331 decode(p_attribute3,
332 fnd_api.g_miss_char, oldrow.attribute3,
333 p_attribute3),
334 decode(p_attribute4,
335 fnd_api.g_miss_char, oldrow.attribute4,
336 p_attribute4),
337 decode(p_attribute5,
338 fnd_api.g_miss_char, oldrow.attribute5,
339 p_attribute5),
340 decode(p_attribute6,
341 fnd_api.g_miss_char, oldrow.attribute6,
342 p_attribute6),
343 decode(p_attribute7,
344 fnd_api.g_miss_char, oldrow.attribute7,
345 p_attribute7),
346 decode(p_attribute8,
347 fnd_api.g_miss_char, oldrow.attribute8,
348 p_attribute8),
349 decode(p_attribute9,
350 fnd_api.g_miss_char, oldrow.attribute9,
351 p_attribute9),
355 decode(p_attribute11,
352 decode(p_attribute10,
353 fnd_api.g_miss_char, oldrow.attribute10,
354 p_attribute10),
356 fnd_api.g_miss_char, oldrow.attribute11,
357 p_attribute11),
358 decode(p_attribute12,
359 fnd_api.g_miss_char, oldrow.attribute12,
360 p_attribute12),
361 decode(p_attribute13,
362 fnd_api.g_miss_char, oldrow.attribute13,
363 p_attribute13),
364 decode(p_attribute14,
365 fnd_api.g_miss_char, oldrow.attribute14,
366 p_attribute14),
367 decode(p_attribute15,
368 fnd_api.g_miss_char, oldrow.attribute15,
369 p_attribute15)
370 INTO
371 l_calc_ext_table_id,
372 l_name ,
373 l_description ,
374 l_internal_table_id,
375 l_external_table_id,
376 l_USED_FLAG ,
377 l_SCHEMA ,
378 l_EXTERNAL_TABLE_NAME,
379 l_ALIAS,
380 l_attribute_category,
381 l_attribute1,
382 l_attribute2,
383 l_attribute3,
384 l_attribute4,
385 l_attribute5,
386 l_attribute6,
387 l_attribute7,
388 l_attribute8,
389 l_attribute9,
390 l_attribute10,
391 l_attribute11,
392 l_attribute12,
393 l_attribute13,
394 l_attribute14,
395 l_attribute15
396 FROM dual;
397
398 UPDATE cn_calc_ext_tables
399 SET
400 calc_ext_table_id = l_calc_ext_table_id,
401 name = l_name,
402 description = l_description,
403 internal_table_id = l_internal_table_id,
404 external_table_id = l_external_table_id,
405 used_flag = l_USED_FLAG,
406 schema = l_SCHEMA,
407 external_table_name = l_EXTERNAL_TABLE_NAME,
408 alias = nvl(l_ALIAS,'CN'||l_calc_ext_table_id),
409 attribute_category = l_attribute_category,
410 attribute1 = l_attribute1,
411 attribute2 = l_attribute2,
412 attribute3 = l_attribute3,
413 attribute4 = l_attribute4,
414 attribute5 = l_attribute5,
415 attribute6 = l_attribute6,
416 attribute7 = l_attribute7,
417 attribute8 = l_attribute8,
418 attribute9 = l_attribute9,
419 attribute10 = l_attribute10,
420 attribute11 = l_attribute11,
421 attribute12 = l_attribute12,
422 attribute13 = l_attribute13,
423 attribute14 = l_attribute14,
424 attribute15 = l_attribute15,
425 last_update_date = p_Last_Update_Date,
426 last_updated_by = p_Last_Updated_By,
427 last_update_login = p_Last_Update_Login
428 WHERE calc_ext_table_id = p_calc_ext_table_id;
429
430 IF (SQL%NOTFOUND) THEN
431 RAISE NO_DATA_FOUND;
432 END IF;
433
434 END Update_row;
435
436 -- ==========================================================================
437 -- Procedure Name
438 -- Delete_row
439 -- Purpose
440 -- ==========================================================================
441
442 PROCEDURE Delete_row( p_calc_ext_table_id NUMBER ) IS
443 BEGIN
444
445 DELETE FROM cn_calc_ext_tables
446 WHERE calc_ext_table_id = p_calc_ext_table_id;
447 IF (SQL%NOTFOUND) THEN
448 RAISE NO_DATA_FOUND;
449 END IF;
450
451 END Delete_row;
452
453 END CN_CALC_EXT_TABLE_PKG;