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