[Home] [Help]
PACKAGE BODY: APPS.CN_TABLE_MAPS_PKG
Source
1 PACKAGE BODY CN_TABLE_MAPS_PKG AS
2 -- $Header: cncotmb.pls 120.5 2005/10/24 00:45:41 apink noship $
3 --
4 -- Public Procedures
5 --
6
7 PROCEDURE insert_row (
8 X_rowid OUT NOCOPY ROWID,
9 X_table_map_id IN OUT NOCOPY cn_table_maps.table_map_id%TYPE,
10 X_mapping_type cn_table_maps.mapping_type%TYPE,
11 X_module_id cn_table_maps.module_id%TYPE,
12 X_source_table_id cn_table_maps.source_table_id%TYPE,
13 X_source_tbl_pkcol_id cn_table_maps.source_tbl_pkcol_id%TYPE,
14 X_destination_table_id cn_table_maps.destination_table_id%TYPE,
15 X_source_hdr_tbl_pkcol_id cn_table_maps.source_hdr_tbl_pkcol_id%TYPE,
16 X_source_tbl_hdr_fkcol_id cn_table_maps.source_tbl_hdr_fkcol_id%TYPE,
17 X_notify_where cn_table_maps.notify_where%TYPE,
18 X_collect_where cn_table_maps.collect_where%TYPE,
19 X_delete_flag cn_table_maps.delete_flag%TYPE,
20 X_creation_date cn_table_maps.creation_date%TYPE,
21 X_created_by cn_table_maps.created_by%TYPE,
22 X_org_id cn_table_maps.org_id%TYPE) IS
23
24 BEGIN
25
26 IF X_table_map_id IS NULL THEN
27 SELECT cn_table_maps_s.NEXTVAL
28 INTO X_table_map_id
29 FROM dual;
30 END IF;
31
32 INSERT INTO cn_table_maps (
33 object_version_number,
34 table_map_id,
35 mapping_type,
36 module_id,
37 source_table_id,
38 source_tbl_pkcol_id,
39 destination_table_id,
40 source_hdr_tbl_pkcol_id,
41 source_tbl_hdr_fkcol_id,
42 notify_where,
43 collect_where,
44 delete_flag,
45 org_id, -- Modified For R12 MOAC
46 creation_date, -- Modified For R12
47 created_by) -- Modified For R12
48 VALUES (1,
49 X_table_map_id,
50 X_mapping_type,
51 X_module_id,
52 X_source_table_id,
53 X_source_tbl_pkcol_id,
54 X_destination_table_id,
55 X_source_hdr_tbl_pkcol_id,
56 X_source_tbl_hdr_fkcol_id,
57 X_notify_where,
58 X_collect_where,
59 X_delete_flag,
60 X_org_id, -- Modified For R12 MOAC
61 X_creation_date, -- Modified For R12
62 X_created_by); -- Modified For R12
63
64 SELECT ROWID
65 INTO X_rowid
66 FROM cn_table_maps
67 WHERE table_map_id = X_table_map_id
68 AND org_id = X_org_id; -- Modified For R12 MOAC
69
70 IF (SQL%NOTFOUND) THEN
71 RAISE NO_DATA_FOUND;
72 END IF;
73
74 END insert_row;
75
76 PROCEDURE lock_row (
77 X_table_map_id cn_table_maps.table_map_id%TYPE,
78 X_mapping_type cn_table_maps.mapping_type%TYPE,
79 X_module_id cn_table_maps.module_id%TYPE,
80 X_source_table_id cn_table_maps.source_table_id%TYPE,
81 X_source_tbl_pkcol_id cn_table_maps.source_tbl_pkcol_id%TYPE,
82 X_destination_table_id cn_table_maps.destination_table_id%TYPE,
83 X_source_hdr_tbl_pkcol_id cn_table_maps.source_hdr_tbl_pkcol_id%TYPE,
84 X_source_tbl_hdr_fkcol_id cn_table_maps.source_tbl_hdr_fkcol_id%TYPE,
85 X_notify_where cn_table_maps.notify_where%TYPE,
86 X_collect_where cn_table_maps.collect_where%TYPE,
87 X_delete_flag cn_table_maps.delete_flag%TYPE) IS
88 --
89 CURSOR c1 IS SELECT
90 mapping_type,
91 module_id,
92 source_table_id,
93 source_tbl_pkcol_id,
94 destination_table_id,
95 source_hdr_tbl_pkcol_id,
96 source_tbl_hdr_fkcol_id,
97 notify_where,
98 collect_where,
99 delete_flag
100 FROM cn_table_maps
101 WHERE table_map_id = x_table_map_id
102 FOR UPDATE OF table_map_id NOWAIT;
103
104 tlinfo c1%ROWTYPE;
105 BEGIN
106 OPEN c1;
107 FETCH c1 INTO tlinfo;
108
109 if tlinfo.mapping_type = x_mapping_type
110 AND tlinfo.module_id = x_module_id
111 AND tlinfo.source_table_id = x_source_table_id
112 AND tlinfo.destination_table_id = x_destination_table_id
113 AND (tlinfo.source_tbl_pkcol_id = x_source_tbl_pkcol_id OR
114 (tlinfo.source_tbl_pkcol_id IS NULL AND x_source_tbl_pkcol_id IS NULL))
115 AND (tlinfo.source_hdr_tbl_pkcol_id = x_source_hdr_tbl_pkcol_id OR
116 (tlinfo.source_hdr_tbl_pkcol_id IS NULL AND x_source_hdr_tbl_pkcol_id IS NULL))
117 AND (tlinfo.source_tbl_hdr_fkcol_id = x_source_tbl_hdr_fkcol_id OR
118 (tlinfo.source_tbl_hdr_fkcol_id IS NULL AND x_source_tbl_hdr_fkcol_id IS NULL))
119 AND (tlinfo.notify_where = x_notify_where OR
120 (tlinfo.notify_where IS NULL AND x_notify_where IS NULL))
121 AND (tlinfo.collect_where = x_collect_where OR
122 (tlinfo.collect_where IS NULL AND x_collect_where IS NULL))
123 AND (tlinfo.delete_flag = x_delete_flag OR
124 (tlinfo.delete_flag IS NULL AND x_delete_flag IS NULL))
125 THEN
126 NULL;
127 ELSE
128 CLOSE c1;
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 END IF;
132 CLOSE c1;
133 RETURN;
134 END lock_row;
135
136 PROCEDURE update_row (
137 X_table_map_id cn_table_maps.table_map_id%TYPE,
138 X_mapping_type cn_table_maps.mapping_type%TYPE,
139 X_module_id cn_table_maps.module_id%TYPE,
140 X_source_table_id cn_table_maps.source_table_id%TYPE,
141 X_source_tbl_pkcol_id cn_table_maps.source_tbl_pkcol_id%TYPE,
142 X_destination_table_id cn_table_maps.destination_table_id%TYPE,
143 X_source_hdr_tbl_pkcol_id cn_table_maps.source_hdr_tbl_pkcol_id%TYPE,
144 X_source_tbl_hdr_fkcol_id cn_table_maps.source_tbl_hdr_fkcol_id%TYPE,
145 X_notify_where cn_table_maps.notify_where%TYPE,
146 X_collect_where cn_table_maps.collect_where%TYPE,
147 X_delete_flag cn_table_maps.delete_flag%TYPE,
148 X_last_update_date cn_table_maps.last_update_date%TYPE,
149 X_last_updated_by cn_table_maps.last_updated_by%TYPE,
150 X_last_update_login cn_table_maps.last_update_login%TYPE,
151 x_object_version_number NUMBER := cn_api.G_MISS_NUM,
152 x_org_id cn_table_maps.org_id%TYPE) IS
153
154
155 CURSOR l_ovn_csr IS
156 SELECT object_version_number
157 FROM cn_table_maps
158 WHERE table_map_id = x_table_map_id
159 AND org_id = x_org_id;
160
161 l_ovn NUMBER;
162 BEGIN
163
164 OPEN l_ovn_csr;
165 FETCH l_ovn_csr INTO l_ovn;
166 CLOSE l_ovn_csr;
167
168 SELECT DECODE(x_object_version_number, cn_api.G_MISS_NUM,
169 l_ovn,x_object_version_number)
170 INTO l_ovn FROM dual;
171
172 UPDATE cn_table_maps SET
173 mapping_type = x_mapping_type,
174 module_id = x_module_id,
175 source_table_id = x_source_table_id,
176 source_tbl_pkcol_id = x_source_tbl_pkcol_id,
177 destination_table_id = x_destination_table_id,
178 source_hdr_tbl_pkcol_id = x_source_hdr_tbl_pkcol_id,
179 source_tbl_hdr_fkcol_id = x_source_tbl_hdr_fkcol_id,
180 notify_where = x_notify_where,
181 collect_where = x_collect_where,
182 delete_flag = x_delete_flag,
183 last_update_date = x_last_update_date,
184 last_updated_by = x_last_updated_by,
185 last_update_login = x_last_update_login,
186 object_version_number = Nvl(l_ovn,0) + 1
187 WHERE table_map_id = x_table_map_id
188 AND org_id = x_org_id;
189
190 IF (SQL%NOTFOUND) THEN
191 RAISE NO_DATA_FOUND;
192 END IF;
193 END update_row;
194
195 PROCEDURE delete_row (
196 X_table_map_id IN NUMBER,
197 X_org_id IN NUMBER
198 ) IS
199 BEGIN
200 DELETE FROM cn_table_maps
201 WHERE table_map_id = x_table_map_id
202 AND org_id = X_org_id;
203
204 IF (SQL%NOTFOUND) THEN
205 RAISE NO_DATA_FOUND;
206 END IF;
207
208 END delete_row;
209
210 PROCEDURE load_row(x_TABLE_MAP_ID in varchar2,
211 x_MAPPING_TYPE in varchar2,
212 x_SOURCE_TABLE_ID in varchar2,
213 x_DESTINATION_TABLE_ID in varchar2,
214 x_MODULE_ID in varchar2,
215 x_LAST_UPDATE_DATE in varchar2,
216 x_LAST_UPDATED_BY in varchar2,
217 x_CREATION_DATE in varchar2,
218 x_CREATED_BY in varchar2,
219 x_DESCRIPTION in varchar2,
220 x_SEED_TABLE_MAP_ID in varchar2,
221 x_ORG_ID in varchar2,
222 x_LAST_UPDATE_LOGIN in varchar2,
223 x_SOURCE_TBL_PKCOL_ID in varchar2,
224 x_DELETE_FLAG in varchar2,
225 x_SOURCE_HDR_TBL_PKCOL_ID in varchar2,
226 x_SOURCE_TBL_HDR_FKCOL_ID in varchar2,
227 x_NOTIFY_WHERE in varchar2,
228 x_COLLECT_WHERE in varchar2,
229 x_OBJECT_VERSION_NUMBER in varchar2,
230 x_SECURITY_GROUP_ID in varchar2,
231 x_FILTER in varchar2,
232 x_APPLICATION_SHORT_NAME in varchar2,
233 x_OWNER in varchar2)
234 IS
235 USER_ID NUMBER;
236 BEGIN
237 if (x_TABLE_MAP_ID is NOT NULL) then
238
239 -- Check whether SEED Data or Custom Data you are uploading
240 IF (X_OWNER IS NOT NULL) AND (X_OWNER = 'SEED') THEN
241 USER_ID := 1;
242 ELSE
243 USER_ID := 0;
244 END IF;
245
246 update cn_table_maps_all set
247 MAPPING_TYPE = x_MAPPING_TYPE,
248 SOURCE_TABLE_ID = to_number(x_SOURCE_TABLE_ID),
249 DESTINATION_TABLE_ID = to_number(x_DESTINATION_TABLE_ID),
250 MODULE_ID = to_number(x_MODULE_ID),
251 LAST_UPDATE_DATE = to_date(x_LAST_UPDATE_DATE, 'DD-MM-YYYY'),
252 LAST_UPDATED_BY = to_number(x_LAST_UPDATED_BY),
253 CREATION_DATE = to_date(x_CREATION_DATE, 'DD-MM-YYYY'),
254 CREATED_BY = to_number(x_CREATED_BY),
255 DESCRIPTION = x_DESCRIPTION,
256 SEED_TABLE_MAP_ID = to_number(x_SEED_TABLE_MAP_ID),
257 ORG_ID = to_number(x_ORG_ID),
258 LAST_UPDATE_LOGIN = to_number(x_LAST_UPDATE_LOGIN),
259 SOURCE_TBL_PKCOL_ID = to_number(x_SOURCE_TBL_PKCOL_ID),
260 DELETE_FLAG = x_DELETE_FLAG,
261 SOURCE_HDR_TBL_PKCOL_ID = to_number(x_SOURCE_HDR_TBL_PKCOL_ID),
262 SOURCE_TBL_HDR_FKCOL_ID = to_number(x_SOURCE_TBL_HDR_FKCOL_ID),
263 NOTIFY_WHERE = x_NOTIFY_WHERE,
264 COLLECT_WHERE = x_COLLECT_WHERE,
265 OBJECT_VERSION_NUMBER = to_number(x_OBJECT_VERSION_NUMBER),
266 SECURITY_GROUP_ID = to_number(x_SECURITY_GROUP_ID),
267 FILTER = x_FILTER
268 where TABLE_MAP_ID = x_TABLE_MAP_ID
269 and ORG_ID = x_ORG_ID;
270
271 IF (SQL%NOTFOUND) THEN
272 -- Insert new record to CN_OBJECTS_TABLE table
273 insert into cn_table_maps_all
274 (TABLE_MAP_ID,
275 MAPPING_TYPE,
276 SOURCE_TABLE_ID,
277 DESTINATION_TABLE_ID,
278 MODULE_ID,
279 LAST_UPDATE_DATE,
280 LAST_UPDATED_BY,
281 CREATION_DATE,
282 CREATED_BY,
283 DESCRIPTION,
284 SEED_TABLE_MAP_ID,
285 ORG_ID,
286 LAST_UPDATE_LOGIN,
287 SOURCE_TBL_PKCOL_ID,
288 DELETE_FLAG,
289 SOURCE_HDR_TBL_PKCOL_ID,
290 SOURCE_TBL_HDR_FKCOL_ID,
291 NOTIFY_WHERE,
292 COLLECT_WHERE,
293 OBJECT_VERSION_NUMBER,
294 SECURITY_GROUP_ID,
295 FILTER
296 )
297 values
298 (to_number(x_TABLE_MAP_ID),
299 x_MAPPING_TYPE,
300 to_number(x_SOURCE_TABLE_ID),
301 to_number(x_DESTINATION_TABLE_ID),
302 to_number(x_MODULE_ID),
303 to_date(x_LAST_UPDATE_DATE, 'DD-MM-YYYY'),
304 to_number(x_LAST_UPDATED_BY),
305 to_date(x_CREATION_DATE, 'DD-MM-YYYY'),
306 to_number(x_CREATED_BY),
307 x_DESCRIPTION,
308 to_number(x_SEED_TABLE_MAP_ID),
309 to_number(x_ORG_ID),
310 to_number(x_LAST_UPDATE_LOGIN),
311 to_number(x_SOURCE_TBL_PKCOL_ID),
312 x_DELETE_FLAG,
313 to_number(x_SOURCE_HDR_TBL_PKCOL_ID),
314 to_number(x_SOURCE_TBL_HDR_FKCOL_ID),
315 x_NOTIFY_WHERE,
316 x_COLLECT_WHERE,
317 to_number(x_OBJECT_VERSION_NUMBER),
318 to_number(x_SECURITY_GROUP_ID),
319 x_FILTER
320 );
321 end if;
322 end if;
323 END load_row;
324
325 PROCEDURE load_seed_row(x_UPLOAD_MODE in varchar2,
326 x_TABLE_MAP_ID in varchar2,
327 x_MAPPING_TYPE in varchar2,
328 x_SOURCE_TABLE_ID in varchar2,
329 x_DESTINATION_TABLE_ID in varchar2,
330 x_MODULE_ID in varchar2,
331 x_LAST_UPDATE_DATE in varchar2,
332 x_LAST_UPDATED_BY in varchar2,
333 x_CREATION_DATE in varchar2,
334 x_CREATED_BY in varchar2,
335 x_DESCRIPTION in varchar2,
336 x_SEED_TABLE_MAP_ID in varchar2,
337 x_ORG_ID in varchar2,
338 x_LAST_UPDATE_LOGIN in varchar2,
339 x_SOURCE_TBL_PKCOL_ID in varchar2,
340 x_DELETE_FLAG in varchar2,
341 x_SOURCE_HDR_TBL_PKCOL_ID in varchar2,
342 x_SOURCE_TBL_HDR_FKCOL_ID in varchar2,
343 x_NOTIFY_WHERE in varchar2,
344 x_COLLECT_WHERE in varchar2,
345 x_OBJECT_VERSION_NUMBER in varchar2,
346 x_SECURITY_GROUP_ID in varchar2,
347 x_FILTER in varchar2,
348 x_APPLICATION_SHORT_NAME in varchar2,
349 x_OWNER in varchar2)
350 IS
351 BEGIN
352 if (x_upload_mode = 'NLS') then
353 --CN_TABLE_MAPS_PKG.TRANSLATE_ROW(x_owner);
354 -- As this ldt is not required to loaded translated data, you could leave it blank.
355 null;
356 else
357 CN_TABLE_MAPS_PKG.load_row(x_TABLE_MAP_ID,
358 x_MAPPING_TYPE,
359 x_SOURCE_TABLE_ID,
360 x_DESTINATION_TABLE_ID,
361 x_MODULE_ID,
362 x_LAST_UPDATE_DATE,
363 x_LAST_UPDATED_BY,
364 x_CREATION_DATE,
365 x_CREATED_BY,
366 x_DESCRIPTION,
367 x_SEED_TABLE_MAP_ID,
368 x_ORG_ID,
369 x_LAST_UPDATE_LOGIN,
370 x_SOURCE_TBL_PKCOL_ID,
371 x_DELETE_FLAG,
372 x_SOURCE_HDR_TBL_PKCOL_ID,
373 x_SOURCE_TBL_HDR_FKCOL_ID,
374 x_NOTIFY_WHERE,
375 x_COLLECT_WHERE,
376 x_OBJECT_VERSION_NUMBER,
377 x_SECURITY_GROUP_ID,
378 x_FILTER,
379 x_APPLICATION_SHORT_NAME,
380 x_OWNER);
381 null;
382 end if;
383 END load_seed_row;
384
385 END cn_table_maps_pkg;