1 PACKAGE BODY cn_calc_ext_tables_pkg as
2 /* $Header: cnextblb.pls 115.7 2002/11/21 21:02:45 hlchen ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_CALC_EXT_TABLE_ID in NUMBER,
6 X_SCHEMA in VARCHAR2,
7 X_NAME in VARCHAR2,
8 X_DESCRIPTION in VARCHAR2,
9 X_INTERNAL_TABLE_ID in NUMBER,
10 X_EXTERNAL_TABLE_ID in NUMBER,
11 X_USED_FLAG in VARCHAR2,
12 X_EXTERNAL_TABLE_NAME in VARCHAR2,
13 X_ALIAS in VARCHAR2,
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 X_CREATION_DATE in DATE,
31 X_CREATED_BY in NUMBER,
32 X_LAST_UPDATE_DATE in DATE,
33 X_LAST_UPDATED_BY in NUMBER,
34 X_LAST_UPDATE_LOGIN in NUMBER
35 ) is
36 cursor C is select ROWID from CN_CALC_EXT_TABLES
37 where CALC_EXT_TABLE_ID = X_CALC_EXT_TABLE_ID ;
38
39 l_calc_ext_table_id NUMBER;
40 BEGIN
41 IF (x_calc_ext_table_id IS NULL) THEN
42 SELECT cn_calc_ext_tables_s.NEXTVAL
43 INTO l_calc_ext_table_id
44 FROM dual;
45 ELSE
46 l_calc_ext_table_id := x_calc_ext_table_id;
47 END IF;
48
49 insert into CN_CALC_EXT_TABLES (
50 CALC_EXT_TABLE_ID,
51 NAME,
52 DESCRIPTION,
53 INTERNAL_TABLE_ID,
54 EXTERNAL_TABLE_ID,
55 USED_FLAG,
56 EXTERNAL_TABLE_NAME,
57 ALIAS,
58 SCHEMA,
59 ATTRIBUTE_CATEGORY,
60 ATTRIBUTE1,
61 ATTRIBUTE2,
62 ATTRIBUTE3,
63 ATTRIBUTE4,
64 ATTRIBUTE5,
65 ATTRIBUTE6,
66 ATTRIBUTE7,
67 ATTRIBUTE8,
68 ATTRIBUTE9,
69 ATTRIBUTE10,
70 ATTRIBUTE11,
71 ATTRIBUTE12,
72 ATTRIBUTE13,
73 ATTRIBUTE14,
74 ATTRIBUTE15,
75 CREATED_BY,
76 CREATION_DATE,
77 LAST_UPDATE_LOGIN,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY
80 ) VALUES (
81 l_CALC_EXT_TABLE_ID,
82 X_NAME,
83 X_DESCRIPTION,
84 X_INTERNAL_TABLE_ID,
85 X_EXTERNAL_TABLE_ID,
86 X_USED_FLAG,
87 X_EXTERNAL_TABLE_NAME,
88 X_ALIAS,
89 X_SCHEMA,
90 X_ATTRIBUTE_CATEGORY,
91 X_ATTRIBUTE1,
92 X_ATTRIBUTE2,
93 X_ATTRIBUTE3,
94 X_ATTRIBUTE4,
95 X_ATTRIBUTE5,
96 X_ATTRIBUTE6,
97 X_ATTRIBUTE7,
98 X_ATTRIBUTE8,
99 X_ATTRIBUTE9,
100 X_ATTRIBUTE10,
101 X_ATTRIBUTE11,
102 X_ATTRIBUTE12,
103 X_ATTRIBUTE13,
104 X_ATTRIBUTE14,
105 X_ATTRIBUTE15,
106 X_CREATED_BY,
107 X_CREATION_DATE,
108 X_LAST_UPDATE_LOGIN,
109 X_LAST_UPDATE_DATE,
110 X_LAST_UPDATED_BY);
111
112 open c;
113 fetch c into X_ROWID;
114 if (c%notfound) then
115 close c;
116 raise no_data_found;
117 end if;
118 close c;
119
120 end INSERT_ROW;
121
122 procedure LOCK_ROW (
123 X_CALC_EXT_TABLE_ID in NUMBER,
124 X_NAME in VARCHAR2,
125 X_DESCRIPTION in VARCHAR2,
126 X_INTERNAL_TABLE_ID in NUMBER,
127 X_EXTERNAL_TABLE_ID in NUMBER,
128 X_USED_FLAG in VARCHAR2,
129 X_EXTERNAL_TABLE_NAME in VARCHAR2,
130 X_ALIAS in VARCHAR2,
131 X_SCHEMA in VARCHAR2,
132 X_ATTRIBUTE_CATEGORY in VARCHAR2,
133 X_ATTRIBUTE1 in VARCHAR2,
134 X_ATTRIBUTE2 in VARCHAR2,
135 X_ATTRIBUTE3 in VARCHAR2,
136 X_ATTRIBUTE4 in VARCHAR2,
137 X_ATTRIBUTE5 in VARCHAR2,
138 X_ATTRIBUTE6 in VARCHAR2,
139 X_ATTRIBUTE7 in VARCHAR2,
140 X_ATTRIBUTE8 in VARCHAR2,
141 X_ATTRIBUTE9 in VARCHAR2,
142 X_ATTRIBUTE10 in VARCHAR2,
143 X_ATTRIBUTE11 in VARCHAR2,
144 X_ATTRIBUTE12 in VARCHAR2,
145 X_ATTRIBUTE13 in VARCHAR2,
146 X_ATTRIBUTE14 in VARCHAR2,
147 X_ATTRIBUTE15 in VARCHAR2
148 ) is
149 cursor c1 is select
150 NAME,
151 DESCRIPTION,
152 INTERNAL_TABLE_ID,
153 EXTERNAL_TABLE_ID,
154 USED_FLAG,
155 EXTERNAL_TABLE_NAME,
156 ALIAS,
157 SCHEMA,
158 ATTRIBUTE_CATEGORY,
159 ATTRIBUTE1,
160 ATTRIBUTE2,
161 ATTRIBUTE3,
162 ATTRIBUTE4,
163 ATTRIBUTE5,
164 ATTRIBUTE6,
165 ATTRIBUTE7,
166 ATTRIBUTE8,
167 ATTRIBUTE9,
168 ATTRIBUTE10,
169 ATTRIBUTE11,
170 ATTRIBUTE12,
171 ATTRIBUTE13,
172 ATTRIBUTE14,
173 ATTRIBUTE15
174 from CN_CALC_EXT_TABLES
175 where CALC_EXT_TABLE_ID = X_CALC_EXT_TABLE_ID
176 for update of CALC_EXT_TABLE_ID nowait;
177
178 tlinfo c1%ROWTYPE;
179 BEGIN
180 OPEN c1;
181 FETCH c1 INTO tlinfo;
182
183 if ( ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
184 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
185 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
186 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
187 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
188 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
189 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
190 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
191 AND ((tlinfo.SCHEMA = X_SCHEMA)
192 OR ((tlinfo.SCHEMA is null) AND (X_SCHEMA is null)))
193 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
194 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
195 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
196 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
197 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
198 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
199 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
200 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
201 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
202 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
203 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
204 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
205 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
206 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
207 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
208 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
209 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
210 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
211 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
212 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
213 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
214 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
215 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
216 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
217 AND (tlinfo.NAME = X_NAME)
218 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
219 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
220 AND (tlinfo.INTERNAL_TABLE_ID = X_INTERNAL_TABLE_ID)
221 AND (tlinfo.EXTERNAL_TABLE_ID = X_EXTERNAL_TABLE_ID)
222 AND ((tlinfo.USED_FLAG = X_USED_FLAG)
223 OR ((tlinfo.USED_FLAG is null) AND (X_USED_FLAG is null)))
224 AND ((tlinfo.EXTERNAL_TABLE_NAME = X_EXTERNAL_TABLE_NAME)
225 OR ((tlinfo.EXTERNAL_TABLE_NAME is null) AND (X_EXTERNAL_TABLE_NAME is null)))
226 AND ((tlinfo.ALIAS = X_ALIAS)
227 OR ((tlinfo.ALIAS is null) AND (X_ALIAS is null)))
228 ) then
229 null;
230 ELSE
231 CLOSE c1;
232 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
233 app_exception.raise_exception;
234 end if;
235 CLOSE c1;
236 return;
237 end LOCK_ROW;
238
239 procedure UPDATE_ROW (
240 X_CALC_EXT_TABLE_ID in NUMBER,
241 X_NAME in VARCHAR2,
242 X_DESCRIPTION in VARCHAR2,
243 X_INTERNAL_TABLE_ID in NUMBER,
244 X_EXTERNAL_TABLE_ID in NUMBER,
245 X_USED_FLAG in VARCHAR2,
246 X_EXTERNAL_TABLE_NAME in VARCHAR2,
247 X_ALIAS in VARCHAR2,
248 X_SCHEMA in VARCHAR2,
249 X_ATTRIBUTE_CATEGORY in VARCHAR2,
250 X_ATTRIBUTE1 in VARCHAR2,
251 X_ATTRIBUTE2 in VARCHAR2,
252 X_ATTRIBUTE3 in VARCHAR2,
253 X_ATTRIBUTE4 in VARCHAR2,
254 X_ATTRIBUTE5 in VARCHAR2,
255 X_ATTRIBUTE6 in VARCHAR2,
256 X_ATTRIBUTE7 in VARCHAR2,
257 X_ATTRIBUTE8 in VARCHAR2,
258 X_ATTRIBUTE9 in VARCHAR2,
259 X_ATTRIBUTE10 in VARCHAR2,
260 X_ATTRIBUTE11 in VARCHAR2,
261 X_ATTRIBUTE12 in VARCHAR2,
262 X_ATTRIBUTE13 in VARCHAR2,
263 X_ATTRIBUTE14 in VARCHAR2,
264 X_ATTRIBUTE15 in VARCHAR2,
265 X_LAST_UPDATE_DATE in DATE,
266 X_LAST_UPDATED_BY in NUMBER,
267 X_LAST_UPDATE_LOGIN in NUMBER
268 ) is
269 begin
270 update CN_CALC_EXT_TABLES set
271 ATTRIBUTE12 = X_ATTRIBUTE12,
272 ATTRIBUTE13 = X_ATTRIBUTE13,
273 ATTRIBUTE14 = X_ATTRIBUTE14,
274 ATTRIBUTE15 = X_ATTRIBUTE15,
275 SCHEMA = X_SCHEMA,
276 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
277 ATTRIBUTE1 = X_ATTRIBUTE1,
278 ATTRIBUTE2 = X_ATTRIBUTE2,
279 ATTRIBUTE3 = X_ATTRIBUTE3,
280 ATTRIBUTE4 = X_ATTRIBUTE4,
281 ATTRIBUTE5 = X_ATTRIBUTE5,
282 ATTRIBUTE6 = X_ATTRIBUTE6,
283 ATTRIBUTE7 = X_ATTRIBUTE7,
284 ATTRIBUTE8 = X_ATTRIBUTE8,
285 ATTRIBUTE9 = X_ATTRIBUTE9,
286 ATTRIBUTE10 = X_ATTRIBUTE10,
287 ATTRIBUTE11 = X_ATTRIBUTE11,
288 NAME = X_NAME,
289 DESCRIPTION = X_DESCRIPTION,
290 INTERNAL_TABLE_ID = X_INTERNAL_TABLE_ID,
291 EXTERNAL_TABLE_ID = X_EXTERNAL_TABLE_ID,
292 USED_FLAG = X_USED_FLAG,
293 EXTERNAL_TABLE_NAME = X_EXTERNAL_TABLE_NAME,
294 ALIAS = X_ALIAS,
295 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
296 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
297 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
298 where CALC_EXT_TABLE_ID = X_CALC_EXT_TABLE_ID;
299
300 if (sql%notfound) then
301 raise no_data_found;
302 end if;
303 end UPDATE_ROW;
304
305 procedure DELETE_ROW (
306 X_CALC_EXT_TABLE_ID in NUMBER
307 ) is
308 begin
309 delete from CN_CALC_EXT_TABLES
310 where CALC_EXT_TABLE_ID = X_CALC_EXT_TABLE_ID;
311
312 if (sql%notfound) then
313 raise no_data_found;
314 end if;
315
316 end DELETE_ROW;
317
318
319 END cn_calc_ext_tables_pkg ;
[Home] [Help]