[Home] [Help]
PACKAGE BODY: APPS.CN_COLUMN_MAPS_PKG
Source
4 -- Public Procedures
1 PACKAGE BODY cn_column_maps_pkg AS
2 -- $Header: cncocmb.pls 120.7 2005/09/21 20:51:29 sjustina noship $
3 --
5 --
6 PROCEDURE insert_row (
7 X_rowid OUT NOCOPY ROWID,
8 X_column_map_id IN OUT NOCOPY cn_column_maps.column_map_id%TYPE,
9 X_destination_column_id cn_column_maps.destination_column_id%TYPE,
10 X_table_map_id cn_column_maps.table_map_id%TYPE,
11 X_expression cn_column_maps.expression%TYPE,
12 X_editable cn_column_maps.editable%TYPE,
13 X_modified cn_column_maps.modified%TYPE,
14 X_update_clause cn_column_maps.update_clause%TYPE,
15 X_calc_ext_table_id cn_column_maps.calc_ext_table_id%TYPE,
16 X_creation_date cn_column_maps.creation_date%TYPE,
17 X_created_by cn_column_maps.created_by%TYPE,
18 X_org_id cn_column_maps.org_id%TYPE) IS
19
20 X_primary_key cn_column_maps.column_map_id%TYPE;
21 BEGIN
22
23 IF x_column_map_id IS NULL THEN
24 SELECT cn_column_maps_s.NEXTVAL
25 INTO X_column_map_id
26 FROM dual;
27 END IF;
28
29 INSERT INTO cn_column_maps
30 (object_version_number,
31 column_map_id,
32 destination_column_id,
33 table_map_id,
34 expression,
35 editable,
36 modified,
37 update_clause,
38 calc_ext_table_id,
39 org_id)
40 VALUES
41 (1,
42 X_column_map_id,
43 X_destination_column_id,
44 X_table_map_id,
45 X_expression,
46 X_editable,
47 X_modified,
48 X_update_clause,
49 X_calc_ext_table_id,
50 X_org_id);
51
52 SELECT ROWID
53 INTO X_rowid
54 FROM cn_column_maps
55 WHERE column_map_id = X_column_map_id
56 AND org_id = X_org_id;
57
58 IF (SQL%NOTFOUND) THEN
59 RAISE NO_DATA_FOUND;
63
60 END IF;
61
62 END insert_row;
64 /*
65 PROCEDURE select_row (
66 x_recinfo IN OUT NOCOPY cn_column_maps%ROWTYPE) IS
67 BEGIN
68 -- select row based on column_map_id (primary key)
69 IF (x_recinfo.column_map_id IS NOT NULL) THEN
70
71 SELECT * INTO x_recinfo
72 FROM cn_column_maps ccm
73 WHERE ccm.column_map_id = x_recinfo.column_map_id;
74
75 END IF;
76 END select_row;
77
78 PROCEDURE lock_row (
79 X_column_map_id cn_column_maps.column_map_id%TYPE,
80 X_destination_column_id cn_column_maps.destination_column_id%TYPE,
81 X_table_map_id cn_column_maps.table_map_id%TYPE,
82 X_expression cn_column_maps.expression%TYPE,
83 X_editable cn_column_maps.editable%TYPE,
84 X_modified cn_column_maps.modified%TYPE,
85 X_update_clause cn_column_maps.update_clause%TYPE,
86 X_calc_ext_table_id cn_column_maps.calc_ext_table_id%TYPE) IS
87 --
88 CURSOR c1 IS SELECT
89 destination_column_id,
90 table_map_id,
91 expression,
92 editable,
93 modified,
94 update_clause,
95 calc_ext_table_id
96 FROM cn_column_maps
97 WHERE column_map_id = x_column_map_id
98 FOR UPDATE OF column_map_id NOWAIT;
99
100 tlinfo c1%ROWTYPE;
101 BEGIN
102 OPEN c1;
103 FETCH c1 INTO tlinfo;
104
105 IF tlinfo.table_map_id = x_table_map_id
106 AND tlinfo.destination_column_id = x_destination_column_id
107 AND tlinfo.modified = x_modified
108 AND (tlinfo.editable = x_editable OR
109 (tlinfo.editable IS NULL AND x_editable IS NULL))
110 AND (tlinfo.expression = x_expression OR
111 (tlinfo.expression IS NULL AND x_expression IS NULL))
112 AND (tlinfo.update_clause = x_update_clause OR
113 (tlinfo.update_clause IS NULL AND x_update_clause IS NULL))
114 AND (tlinfo.calc_ext_table_id = x_calc_ext_table_id OR
115 (tlinfo.calc_ext_table_id IS NULL AND x_calc_ext_table_id IS NULL))
116 THEN
117 NULL;
118 ELSE
119 CLOSE c1;
120 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
121 app_exception.raise_exception;
122 END IF;
123 CLOSE c1;
124 RETURN;
125 END lock_row;
126 */
127
128 PROCEDURE update_row (
129 X_column_map_id cn_column_maps.column_map_id%TYPE,
130 X_destination_column_id cn_column_maps.destination_column_id%TYPE,
131 X_table_map_id cn_column_maps.table_map_id%TYPE,
132 X_expression cn_column_maps.expression%TYPE,
133 X_editable cn_column_maps.editable%TYPE,
134 X_modified cn_column_maps.modified%TYPE,
135 X_update_clause cn_column_maps.update_clause%TYPE,
136 X_calc_ext_table_id cn_column_maps.calc_ext_table_id%TYPE,
137 X_last_update_date cn_column_maps.last_update_date%TYPE,
138 X_last_updated_by cn_column_maps.last_updated_by%TYPE,
139 X_last_update_login cn_column_maps.last_update_login%TYPE,
140 X_object_version_number IN OUT NOCOPY NUMBER ,
141 x_org_id IN NUMBER) IS
142
143 CURSOR l_ovn_csr IS
144 SELECT object_version_number
145 FROM cn_column_maps
146 WHERE column_map_id = x_column_map_id
147 and org_id = x_org_id;
148
149 l_ovn NUMBER;
150
151 BEGIN
152
153 OPEN l_ovn_csr;
154 FETCH l_ovn_csr INTO l_ovn;
155 CLOSE l_ovn_csr;
156
157 SELECT DECODE(x_object_version_number, cn_api.G_MISS_NUM,
158 l_ovn,x_object_version_number)
159 INTO l_ovn FROM dual;
160
161 UPDATE cn_column_maps set
162 destination_column_id = x_destination_column_id,
163 table_map_id = x_table_map_id,
164 expression = x_expression,
165 editable = x_editable,
166 modified = x_modified,
167 update_clause = x_update_clause,
168 calc_ext_table_id = x_calc_ext_table_id,
169 last_update_date = x_last_update_date,
170 last_updated_by = x_last_updated_by,
171 last_update_login = x_last_update_login,
172 object_version_number = Nvl(l_ovn,0) + 1
173 WHERE column_map_id = x_column_map_id
174 AND org_id = x_org_id;
175
176 X_object_version_number := l_ovn;
177
178 IF (SQL%NOTFOUND) THEN
179 RAISE NO_DATA_FOUND;
180 END IF;
181 END update_row;
182
183 PROCEDURE delete_row (
184 X_column_map_id IN NUMBER,
185 x_org_id IN NUMBER
186 ) IS
187 BEGIN
188 DELETE FROM cn_column_maps
189 WHERE column_map_id = x_column_map_id
190 AND org_id = x_org_id;
191
192 IF (SQL%NOTFOUND) THEN
193 RAISE NO_DATA_FOUND;
194 END IF;
195
196 END delete_row;
197
198 PROCEDURE load_row(x_COLUMN_MAP_ID in varchar2,
199 x_DESTINATION_COLUMN_ID in varchar2,
200 x_TABLE_MAP_ID in varchar2,
201 x_LAST_UPDATE_DATE in varchar2,
202 x_LAST_UPDATED_BY in varchar2,
203 x_CREATION_DATE in varchar2,
204 x_CREATED_BY in varchar2,
205 x_LAST_UPDATE_LOGIN in varchar2,
206 x_SOURCE_COLUMN_ID in varchar2,
207 x_DRIVING_COLUMN_ID in varchar2,
208 x_EXPRESSION in varchar2,
209 x_AGGREGATE_FUNCTION in varchar2,
210 x_SEED_COLUMN_MAP_ID in varchar2,
214 x_UPDATE_CLAUSE in varchar2,
211 x_GROUP_BY_FLAG in varchar2,
212 x_UNIQUE_FLAG in varchar2,
213 x_ORG_ID in varchar2,
215 x_MODIFIED in varchar2,
216 x_EDITABLE in varchar2,
217 x_CALC_EXT_TABLE_ID in varchar2,
218 x_OBJECT_VERSION_NUMBER in varchar2,
219 x_SECURITY_GROUP_ID in varchar2,
220 x_APPLICATION_SHORT_NAME in varchar2,
221 x_OWNER in varchar2)
222 IS
223 USER_ID NUMBER;
224 BEGIN
225 if (x_COLUMN_MAP_ID is NOT NULL) then
226
227 -- Check whether SEED Data or Custom Data you are uploading
228 IF (X_OWNER IS NOT NULL) AND (X_OWNER = 'SEED') THEN
229 USER_ID := 1;
230 ELSE
231 USER_ID := 0;
232 END IF;
233
234 update cn_column_maps_all set
235 DESTINATION_COLUMN_ID = to_number(x_DESTINATION_COLUMN_ID),
236 TABLE_MAP_ID = to_number(x_TABLE_MAP_ID),
237 LAST_UPDATE_DATE = to_date(x_LAST_UPDATE_DATE, 'DD-MM-YYYY'),
238 LAST_UPDATED_BY = to_number(x_LAST_UPDATED_BY),
239 CREATION_DATE = to_date(x_CREATION_DATE, 'DD-MM-YYYY'),
240 CREATED_BY = to_number(x_CREATED_BY),
241 LAST_UPDATE_LOGIN = to_number(x_LAST_UPDATE_LOGIN),
242 SOURCE_COLUMN_ID = to_number(x_SOURCE_COLUMN_ID),
243 DRIVING_COLUMN_ID = to_number(x_DRIVING_COLUMN_ID),
244 EXPRESSION = x_EXPRESSION,
245 AGGREGATE_FUNCTION = x_AGGREGATE_FUNCTION,
246 SEED_COLUMN_MAP_ID = to_number(x_SEED_COLUMN_MAP_ID),
247 GROUP_BY_FLAG = x_GROUP_BY_FLAG,
248 UNIQUE_FLAG = x_UNIQUE_FLAG,
249 ORG_ID = to_number(x_ORG_ID),
250 UPDATE_CLAUSE = x_UPDATE_CLAUSE,
251 MODIFIED = x_MODIFIED,
252 EDITABLE = x_EDITABLE,
253 CALC_EXT_TABLE_ID = to_number(x_CALC_EXT_TABLE_ID),
254 OBJECT_VERSION_NUMBER = to_number(x_OBJECT_VERSION_NUMBER),
255 SECURITY_GROUP_ID = to_number(x_SECURITY_GROUP_ID)
256 where COLUMN_MAP_ID = x_COLUMN_MAP_ID
257 and ORG_ID = x_ORG_ID;
258
259 IF (SQL%NOTFOUND) THEN
260 -- Insert new record to CN_OBJECTS_TABLE table
261 insert into cn_column_maps_all
262 (COLUMN_MAP_ID,
263 DESTINATION_COLUMN_ID,
264 TABLE_MAP_ID,
265 LAST_UPDATE_DATE,
266 LAST_UPDATED_BY,
267 CREATION_DATE,
268 CREATED_BY,
269 LAST_UPDATE_LOGIN,
270 SOURCE_COLUMN_ID,
271 DRIVING_COLUMN_ID,
272 EXPRESSION,
273 AGGREGATE_FUNCTION,
274 SEED_COLUMN_MAP_ID,
275 GROUP_BY_FLAG,
276 UNIQUE_FLAG,
277 ORG_ID,
278 UPDATE_CLAUSE,
279 MODIFIED,
280 EDITABLE,
281 CALC_EXT_TABLE_ID,
282 OBJECT_VERSION_NUMBER,
283 SECURITY_GROUP_ID
284 )
285 values
286 (to_number(x_COLUMN_MAP_ID),
287 to_number(x_DESTINATION_COLUMN_ID),
288 to_number(x_TABLE_MAP_ID),
289 to_date(x_LAST_UPDATE_DATE, 'DD-MM-YYYY'),
290 to_number(x_LAST_UPDATED_BY),
291 to_date(x_CREATION_DATE, 'DD-MM-YYYY'),
292 to_number(x_CREATED_BY),
293 to_number(x_LAST_UPDATE_LOGIN),
294 to_number(x_SOURCE_COLUMN_ID),
295 to_number(x_DRIVING_COLUMN_ID),
296 x_EXPRESSION,
297 x_AGGREGATE_FUNCTION,
298 to_number(x_SEED_COLUMN_MAP_ID),
299 x_GROUP_BY_FLAG,
300 x_UNIQUE_FLAG,
301 to_number(x_ORG_ID),
302 x_UPDATE_CLAUSE,
303 x_MODIFIED,
304 x_EDITABLE,
305 to_number(x_CALC_EXT_TABLE_ID),
306 to_number(x_OBJECT_VERSION_NUMBER),
307 to_number(x_SECURITY_GROUP_ID)
308 );
309 end if;
310 end if;
311 END load_row;
312
313 PROCEDURE load_seed_row(x_UPLOAD_MODE in varchar2,
314 x_COLUMN_MAP_ID in varchar2,
315 x_DESTINATION_COLUMN_ID in varchar2,
316 x_TABLE_MAP_ID in varchar2,
317 x_LAST_UPDATE_DATE in varchar2,
318 x_LAST_UPDATED_BY in varchar2,
319 x_CREATION_DATE in varchar2,
320 x_CREATED_BY in varchar2,
321 x_LAST_UPDATE_LOGIN in varchar2,
322 x_SOURCE_COLUMN_ID in varchar2,
323 x_DRIVING_COLUMN_ID in varchar2,
324 x_EXPRESSION in varchar2,
325 x_AGGREGATE_FUNCTION in varchar2,
326 x_SEED_COLUMN_MAP_ID in varchar2,
327 x_GROUP_BY_FLAG in varchar2,
328 x_UNIQUE_FLAG in varchar2,
329 x_ORG_ID in varchar2,
330 x_UPDATE_CLAUSE in varchar2,
331 x_MODIFIED in varchar2,
332 x_EDITABLE in varchar2,
333 x_CALC_EXT_TABLE_ID in varchar2,
334 x_OBJECT_VERSION_NUMBER in varchar2,
335 x_SECURITY_GROUP_ID in varchar2,
336 x_APPLICATION_SHORT_NAME in varchar2,
337 x_OWNER in varchar2)
338 IS
339 BEGIN
343 null;
340 if (x_upload_mode = 'NLS') then
341 --CN_COLUMN_MAPS_PKG.TRANSLATE_ROW(x_owner);
342 -- As this ldt is not required to loaded translated data, you could leave it blank.
344 else
345 CN_COLUMN_MAPS_PKG.load_row(x_COLUMN_MAP_ID,
346 x_DESTINATION_COLUMN_ID,
347 x_TABLE_MAP_ID,
348 x_LAST_UPDATE_DATE,
349 x_LAST_UPDATED_BY,
350 x_CREATION_DATE,
351 x_CREATED_BY,
352 x_LAST_UPDATE_LOGIN,
353 x_SOURCE_COLUMN_ID,
354 x_DRIVING_COLUMN_ID,
355 x_EXPRESSION,
356 x_AGGREGATE_FUNCTION,
357 x_SEED_COLUMN_MAP_ID,
358 x_GROUP_BY_FLAG,
359 x_UNIQUE_FLAG,
360 x_ORG_ID,
361 x_UPDATE_CLAUSE,
362 x_MODIFIED,
363 x_EDITABLE,
364 x_CALC_EXT_TABLE_ID,
365 x_OBJECT_VERSION_NUMBER,
366 x_SECURITY_GROUP_ID,
367 x_APPLICATION_SHORT_NAME,
368 x_OWNER);
369 null;
370 end if;
371 END load_seed_row;
372
373
374 END cn_column_maps_pkg;