[Home] [Help]
PACKAGE BODY: APPS.CN_DIMENSIONS_PKG
Source
1 PACKAGE BODY CN_DIMENSIONS_PKG as
2 -- $Header: cndidimb.pls 120.7 2006/01/13 03:58:07 hanaraya noship $
3
4
5 -- Procedure Name
6 -- insert_row
7 -- History
8 -- 12/28/93 Paul Mitchell Created
9
10 procedure INSERT_ROW (
11 X_DIMENSION_ID in NUMBER,
12 X_DESCRIPTION in VARCHAR2,
13 X_SOURCE_TABLE_ID in NUMBER,
14 X_NAME in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER,
20 --R12 MOAC Changes--Start
21 X_ORG_ID in NUMBER
22 --R12 MOAC Changes--End
23 ) is
24 begin
25 insert into CN_DIMENSIONS_ALL_B (
26 DIMENSION_ID,
27 DESCRIPTION,
28 SOURCE_TABLE_ID,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN,
34 --R12 MOAC Changes--Start
35 ORG_ID
36 --R12 MOAC Changes--End
37 ) values (
38 X_DIMENSION_ID,
39 X_DESCRIPTION,
40 X_SOURCE_TABLE_ID,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN,
46 --R12 MOAC Changes--Start
47 X_ORG_ID
48 --R12 MOAC Changes--End
49 );
50
51 insert into CN_DIMENSIONS_ALL_TL (
52 DIMENSION_ID,
53 NAME,
54 CREATION_DATE,
55 CREATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_LOGIN,
59 --R12 MOAC Changes--Start
60 ORG_ID,
61 --R12 MOAC Changes--End
62 LANGUAGE,
63
64 SOURCE_LANG
65
66 ) select
67 X_DIMENSION_ID,
68 X_NAME,
69 X_CREATION_DATE,
70 X_CREATED_BY,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATED_BY,
73 X_LAST_UPDATE_LOGIN,
74 --R12 MOAC Changes--Start
75 X_ORG_ID,
76 --R12 MOAC Changes--End
77 L.LANGUAGE_CODE,
78 userenv('LANG')
79 from FND_LANGUAGES L
80 where L.INSTALLED_FLAG in ('I', 'B')
81 and not exists
82 (select NULL
83 from CN_DIMENSIONS_ALL_TL T
84 where T.DIMENSION_ID = X_DIMENSION_ID
85 and T.LANGUAGE = L.language_code AND
86 ORG_ID = X_ORG_ID
87 );
88
89
90
91 end INSERT_ROW;
92
93
94 -- Procedure Name
95 -- update_row
96 -- Purpose
97 -- Update a row in cn_dimensions.
98 -- History
99 -- 12/28/93 Paul Mitchell Created
100
101 procedure UPDATE_ROW (
102 X_DIMENSION_ID in NUMBER,
103 X_DESCRIPTION in VARCHAR2,
104 X_SOURCE_TABLE_ID in NUMBER,
105 X_NAME in VARCHAR2,
106 X_LAST_UPDATE_DATE in DATE,
107 X_LAST_UPDATED_BY in NUMBER,
108 X_LAST_UPDATE_LOGIN in NUMBER,
109 --R12 MOAC Changes--Start
110 X_ORG_ID in NUMBER,
111 --R12 MOAC Changes--End
112 X_OBJECT_VERSION_NUMBER in out NOCOPY CN_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE
113 ) is
114 begin
115
116 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
117
118 update CN_DIMENSIONS_ALL_B set
119 DESCRIPTION = X_DESCRIPTION,
120 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
121 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
122 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
123 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
124 where DIMENSION_ID = x_dimension_id AND
125 --R12 MOAC Changes--Start
126 ORG_ID = X_ORG_ID;
127 --R12 MOAC Changes--End
128
129
130
131 if (sql%notfound) then
132 raise no_data_found;
133 end if;
134
135 update CN_DIMENSIONS_ALL_TL set
136 NAME = X_NAME,
137 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
138 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
139 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
140 SOURCE_LANG = userenv('LANG')
141 where DIMENSION_ID = x_dimension_id
142 --R12 MOAC Changes--Start
143 and ORG_ID = X_ORG_ID
144 --R12 MOAC Changes--End
145 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
146
147
148 if (sql%notfound) then
149 raise no_data_found;
150 end if;
151 end UPDATE_ROW;
152
153
154
155 -- Procedure Name
156 -- delete_row
157 -- Purpose
158 -- Delete a row from cn_dimensions.
159 -- History
160 -- 12/28/93 Paul Mitchell Created
161 -- PROCEDURE delete_row(
162 -- X_rowid varchar2) IS
163
164 procedure DELETE_ROW (
165 X_DIMENSION_ID in NUMBER,
166 --R12 MOAC Changes--Start
167 X_ORG_ID in NUMBER
171 l_table_id number;
168 --R12 MOAC Changes--End
169 ) is
170 l_env_org_id number;
172 begin
173 -- get environment org ID
174 select X_ORG_ID
175 into l_env_org_id from dual;
176
177 -- clean up cn_objects and remove pointers to non-existent dimension ID
178 select source_table_id into l_table_id
179 from cn_dimensions where dimension_id = X_DIMENSION_ID
180 --R12 MOAC Changes--Start
181 and org_id = X_ORG_ID;
182 --R12 MOAC Changes--End
183
184 update cn_objects set dimension_id = null,
185 primary_key = null, user_column_name = null
186 where table_id = l_table_id
187 --R12 MOAC Changes--Start
188 and org_id = X_ORG_ID;
189 --R12 MOAC Changes--End
190
191 -- remove dangling head hierarchies, dim hierarchies and tree fragments
192 delete from cn_hierarchy_nodes e where dim_hierarchy_id in
193 (select d.dim_hierarchy_id
194 from cn_dim_hierarchies d, cn_head_hierarchies h
195 where h.dimension_id = X_DIMENSION_ID
196 --R12 MOAC Changes--Start
197 and h.org_id = X_ORG_ID
198 and h.org_id = d.org_id
199 --R12 MOAC Changes--End
200 and d.header_dim_hierarchy_id = h.head_hierarchy_id);
201 delete from cn_hierarchy_edges e where dim_hierarchy_id in
202 (select d.dim_hierarchy_id
203 from cn_dim_hierarchies d, cn_head_hierarchies h
204 where h.dimension_id = X_DIMENSION_ID
205 --R12 MOAC Changes--Start
206 and h.org_id = X_ORG_ID
207 and h.org_id = d.org_id
208 --R12 MOAC Changes--End
209 and d.header_dim_hierarchy_id = h.head_hierarchy_id);
210 delete from cn_dim_hierarchies d where header_dim_hierarchy_id in
211 (select head_hierarchy_id from cn_head_hierarchies
212 where dimension_id = X_DIMENSION_ID
213 --R12 MOAC Changes--Start
214 and org_id = X_ORG_ID);
215 --R12 MOAC Changes--End
216
217 -- delete from cn_head_hierarchies and dimensions - handle MLS delete
218 delete from CN_HEAD_HIERARCHIES_ALL_TL
219 where dimension_id = X_DIMENSION_ID
220 and ORG_ID = X_ORG_ID;
221
222 delete from CN_HEAD_HIERARCHIES_ALL_B
223 where dimension_id = X_DIMENSION_ID
224 and ORG_ID = X_ORG_ID;
225
226 delete from CN_DIMENSIONS_ALL_TL
227 where dimension_id = X_DIMENSION_ID
228 and ORG_ID = X_ORG_ID;
229
230 delete from CN_DIMENSIONS_ALL_B
231 where dimension_id = X_DIMENSION_ID
232 and ORG_ID = X_ORG_ID;
233
234 if (sql%notfound) then
235 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
236 fnd_msg_pub.add;
237 raise fnd_api.g_exc_error;
238 end if;
239
240 end DELETE_ROW;
241
242
243
244
245 procedure ADD_LANGUAGE
246 is
247 begin
248 delete from CN_DIMENSIONS_ALL_TL T
249 where not exists
250 (select NULL
251 from CN_DIMENSIONS_ALL_B B
252 where B.DIMENSION_ID = T.dimension_id
253 and B.ORG_ID = T.ORG_ID
254 );
255
256 update CN_DIMENSIONS_ALL_TL T set (
257 NAME
258 ) = (select
259 B.NAME
260 from CN_DIMENSIONS_ALL_TL B
261 where B.DIMENSION_ID = T.DIMENSION_ID
262 and B.LANGUAGE = T.source_lang
263 and B.ORG_ID = T.ORG_ID
264 )
265 where (
266 T.DIMENSION_ID,
267 T.LANGUAGE
268 ) in (select
269 SUBT.DIMENSION_ID,
270 SUBT.LANGUAGE
271 from CN_DIMENSIONS_ALL_TL SUBB, CN_DIMENSIONS_ALL_TL SUBT
272 where SUBB.DIMENSION_ID = SUBT.DIMENSION_ID
273 and SUBB.LANGUAGE = SUBT.source_lang
274 and SUBB.ORG_ID = SUBT.ORG_ID
275 and (SUBB.NAME <> SUBT.NAME
276 or (SUBB.NAME is null and SUBT.NAME is not null)
277 or (SUBB.NAME is not null and SUBT.NAME is null)
278 ));
279
280 insert into CN_DIMENSIONS_ALL_TL (
281 ORG_ID,
282 DIMENSION_ID,
283 NAME,
284 CREATION_DATE,
285 CREATED_BY,
286 LAST_UPDATE_DATE,
287 LAST_UPDATED_BY,
288 LAST_UPDATE_LOGIN,
289 LANGUAGE,
290 SOURCE_LANG
291 ) select
292 B.ORG_ID,
293 B.DIMENSION_ID,
294 B.NAME,
295 B.CREATION_DATE,
296 B.CREATED_BY,
297 B.LAST_UPDATE_DATE,
298 B.LAST_UPDATED_BY,
299 B.LAST_UPDATE_LOGIN,
300 L.LANGUAGE_CODE,
301 B.SOURCE_LANG
302 from CN_DIMENSIONS_ALL_TL B, FND_LANGUAGES L
303 where L.INSTALLED_FLAG in ('I', 'B')
304 and B.LANGUAGE = userenv('LANG')
305 and not exists
306 (select NULL
307 from CN_DIMENSIONS_ALL_TL T
308 where T.DIMENSION_ID = B.DIMENSION_ID
309 and T.LANGUAGE = L.language_code
310 and T.ORG_ID = B.ORG_ID
311 );
312
313 end ADD_LANGUAGE;
314
315 -- --------------------------------------------------------------------+
316 -- Procedure : LOAD_ROW
317 -- Description : Called by FNDLOAD to upload seed datas, this procedure
318 -- only handle seed datas. ORG_ID = -3113
319 -- --------------------------------------------------------------------+
320 PROCEDURE LOAD_ROW
321 ( x_dimension_id IN NUMBER,
322 x_org_id in NUMBER, -- R12 change
323 x_description IN VARCHAR2,
324 x_source_table_id IN NUMBER,
325 x_name IN VARCHAR2,
326 x_owner IN VARCHAR2) IS
327 user_id NUMBER;
328
329 BEGIN
330 -- Validate input data
331 IF (x_dimension_id IS NULL) OR (x_source_table_id IS NULL)
332 OR (x_name IS NULL) THEN
333 GOTO end_load_row;
334 END IF;
335
336 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
337 user_id := 1;
338 ELSE
339 user_id := 0;
340 END IF;
341 -- Load The record to _B table
342 UPDATE cn_dimensions_all_b SET
343 description = x_description,
344 source_table_id = x_source_table_id,
345 last_update_date = sysdate,
346 last_updated_by = user_id,
347 last_update_login = 0
348 WHERE dimension_id = x_dimension_id
349 AND org_id = x_org_id; --R12 change
350
351 IF (SQL%NOTFOUND) THEN
352 -- Insert new record to _B table
353 INSERT INTO cn_dimensions_all_b
354 (dimension_id,
355 org_id, -- R12 change
356 description,
357 source_table_id,
358 creation_date,
359 created_by,
360 last_update_date,
361 last_updated_by,
362 last_update_login
363 ) VALUES
364 (x_dimension_id,
365 x_org_id, -- R12 change
366 x_description,
367 x_source_table_id,
368 sysdate,
369 user_id,
370 sysdate,
371 user_id,
372 0
373 );
374 END IF;
375 -- Load The record to _TL table
376 UPDATE cn_dimensions_all_tl SET
377 name = x_name,
378 last_update_date = sysdate,
379 last_updated_by = user_id,
380 last_update_login = 0,
381 source_lang = userenv('LANG')
382 WHERE dimension_id = x_dimension_id
383 AND org_id = x_org_id --R12 change
384 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
385
386 IF (SQL%NOTFOUND) THEN
387 -- Insert new record to _TL table
388 INSERT INTO cn_dimensions_all_tl
389 (dimension_id,
390 org_id, -- R12 change
391 name,
392 creation_date,
393 created_by,
394 last_update_date,
395 last_updated_by,
396 last_update_login,
397 language,
398 source_lang)
399 SELECT
400 x_dimension_id,
401 x_org_id, -- R12 change
402 x_name,
403 sysdate,
404 user_id,
405 sysdate,
406 user_id,
407 0,
408 l.language_code,
409 userenv('LANG')
410 FROM fnd_languages l
411 WHERE l.installed_flag IN ('I', 'B')
412 AND NOT EXISTS
413 (SELECT NULL
414 FROM cn_dimensions_all_tl t
415 WHERE t.dimension_id = x_dimension_id
416 AND t.org_id = x_org_id --R12 change
417 AND t.language = l.language_code);
418 END IF;
419 << end_load_row >>
420 NULL;
421 END LOAD_ROW ;
422
423 -- --------------------------------------------------------------------+
424 -- Procedure : TRANSLATE_ROW
425 -- Description : Called by FNDLOAD to translate seed datas, this procedure
426 -- only handle seed datas. ORG_ID = -3113
427 -- --------------------------------------------------------------------+
428 PROCEDURE TRANSLATE_ROW
429 ( x_dimension_id IN NUMBER,
430 x_name IN VARCHAR2,
431 x_owner IN VARCHAR2) IS
432 user_id NUMBER;
433 BEGIN
434 -- Validate input data
435 IF (x_dimension_id IS NULL) OR (x_name IS NULL) THEN
436 GOTO end_translate_row;
437 END IF;
438
439 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
440 user_id := 1;
441 ELSE
442 user_id := 0;
443 END IF;
444 -- Update the translation
445 UPDATE cn_dimensions_all_tl SET
446 name = x_name,
447 last_update_date = sysdate,
448 last_updated_by = user_id,
449 last_update_login = 0,
450 source_lang = userenv('LANG')
451 WHERE dimension_id = x_dimension_id
452 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
453
454 << end_translate_row >>
455 NULL;
456 END TRANSLATE_ROW ;
457
458 FUNCTION New_Dimension RETURN NUMBER IS
459 Ret_Val NUMBER(15);
460 BEGIN
461
462 SELECT cn_dimensions_s.nextval INTO Ret_Val FROM dual;
463
464 RETURN Ret_Val;
465
466 END New_Dimension;
467
468
469
470 END CN_DIMENSIONS_PKG;