[Home] [Help]
PACKAGE BODY: APPS.MSD_HIERARCHIES_PKG
Source
1 package body MSD_HIERARCHIES_PKG as
2 /* $Header: msdhpkgb.pls 120.0 2005/05/25 20:40:47 appldev noship $ */
3 PROCEDURE LOAD_ROW(
4 X_HIERARCHY_ID varchar2,
5 X_PLAN_TYPE varchar2,--- Added to include PLAN_TYPE
6 X_HIERARCHY_NAME varchar2,
7 X_DESCRIPTION varchar2,
8 X_DIMENSION_CODE varchar2,
9 X_VALID_FLAG varchar2,
10 X_ATTRIBUTE_CATEGORY varchar2,
11 X_ATTRIBUTE1 varchar2,
12 X_ATTRIBUTE2 varchar2,
13 X_ATTRIBUTE3 varchar2,
14 X_ATTRIBUTE4 varchar2,
15 X_ATTRIBUTE5 varchar2,
16 X_ATTRIBUTE6 varchar2,
17 X_ATTRIBUTE7 varchar2,
18 X_ATTRIBUTE8 varchar2,
19 X_ATTRIBUTE9 varchar2,
20 X_ATTRIBUTE10 varchar2,
21 X_ATTRIBUTE11 varchar2,
22 X_ATTRIBUTE12 varchar2,
23 X_ATTRIBUTE13 varchar2,
24 X_ATTRIBUTE14 varchar2,
25 X_ATTRIBUTE15 varchar2,
26 x_last_update_date in varchar2,
27 x_owner in varchar2,
28 x_custom_mode in varchar2) IS
29
30 f_luby number; -- entity owner in file
31 f_ludate date; -- entity update date in file
32 db_luby number; -- entity owner in db
33 db_ludate date; -- entity update date in db
34 begin
35 -- Translate owner to file_last_updated_by
36 if (x_owner = 'SEED') then
37 f_luby := 1;
38 else
39 f_luby := 0;
40 end if;
41
42 -- Translate char last_update_date to date
43 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
44
45 begin
46 select LAST_UPDATED_BY, LAST_UPDATE_DATE
47 into db_luby, db_ludate
48 from MSD_HIERARCHIES
49 where HIERARCHY_ID = to_number(x_HIERARCHY_id)
50 and nvl(plan_type,-1) = nvl(to_char(x_plan_type),-1) ;
51
52 -- Update record, honoring customization mode.
53 -- Record should be updated only if:
54 -- a. CUSTOM_MODE = FORCE, or
55 -- b. file owner is CUSTOM, db owner is SEED
56 -- c. owners are the same, and file_date > db_date
57 if ((x_custom_mode = 'FORCE') or
58 ((f_luby = 0) and (db_luby = 1)) or
59 ((f_luby = db_luby) and (f_ludate > db_ludate)))
60 then
61 update MSD_HIERARCHIES set
62 HIERARCHY_NAME = X_HIERARCHY_NAME,
63 DESCRIPTION = X_DESCRIPTION,
64 DIMENSION_CODE = X_DIMENSION_CODE,
65 VALID_FLAG = to_number(X_VALID_FLAG),
66 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
67 ATTRIBUTE1 = X_ATTRIBUTE1,
68 ATTRIBUTE2 = X_ATTRIBUTE2,
69 ATTRIBUTE3 = X_ATTRIBUTE3,
70 ATTRIBUTE4 = X_ATTRIBUTE4,
71 ATTRIBUTE5 = X_ATTRIBUTE5,
72 ATTRIBUTE6 = X_ATTRIBUTE6,
73 ATTRIBUTE7 = X_ATTRIBUTE7,
74 ATTRIBUTE8 = X_ATTRIBUTE8,
75 ATTRIBUTE9 = X_ATTRIBUTE9,
76 ATTRIBUTE10 = X_ATTRIBUTE10,
77 ATTRIBUTE11 = X_ATTRIBUTE11,
78 ATTRIBUTE12 = X_ATTRIBUTE12,
79 ATTRIBUTE13 = X_ATTRIBUTE13,
80 ATTRIBUTE14 = X_ATTRIBUTE14,
81 ATTRIBUTE15 = X_ATTRIBUTE15,
82 LAST_UPDATE_DATE = f_ludate,
83 LAST_UPDATED_BY = f_luby,
84 LAST_UPDATE_LOGIN = 0
85 where HIERARCHY_ID = to_number(X_HIERARCHY_ID) and
86 nvl( plan_type , -1) =nvl( to_char( x_plan_type) , -1);
87 end if;
88 exception
89 when no_data_found then
90 -- Record doesn't exist - insert in all cases
91 insert into MSD_HIERARCHIES (
92 HIERARCHY_ID,
93 PLAN_TYPE,
94 HIERARCHY_NAME,
95 DESCRIPTION,
96 DIMENSION_CODE,
97 VALID_FLAG,
98 ATTRIBUTE_CATEGORY,
99 ATTRIBUTE1,
100 ATTRIBUTE2,
101 ATTRIBUTE3,
102 ATTRIBUTE4,
103 ATTRIBUTE5,
104 ATTRIBUTE6,
105 ATTRIBUTE7,
106 ATTRIBUTE8,
107 ATTRIBUTE9,
108 ATTRIBUTE10,
109 ATTRIBUTE11,
110 ATTRIBUTE12,
111 ATTRIBUTE13,
112 ATTRIBUTE14,
113 ATTRIBUTE15,
114 CREATION_DATE,
115 CREATED_BY,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_LOGIN
119 ) values (
120 to_number(X_HIERARCHY_ID),
121 to_char(X_PLAN_TYPE),
122 X_HIERARCHY_NAME,
123 X_DESCRIPTION,
124 X_DIMENSION_CODE,
125 to_number(X_VALID_FLAG),
126 X_ATTRIBUTE_CATEGORY,
127 X_ATTRIBUTE1,
128 X_ATTRIBUTE2,
129 X_ATTRIBUTE3,
130 X_ATTRIBUTE4,
131 X_ATTRIBUTE5,
132 X_ATTRIBUTE6,
133 X_ATTRIBUTE7,
134 X_ATTRIBUTE8,
135 X_ATTRIBUTE9,
136 X_ATTRIBUTE10,
137 X_ATTRIBUTE11,
138 X_ATTRIBUTE12,
139 X_ATTRIBUTE13,
140 X_ATTRIBUTE14,
141 X_ATTRIBUTE15,
142 f_ludate,
143 f_luby,
144 f_ludate,
145 f_luby,
146 0);
147 end;
148 end LOAD_ROW;
149
150
151 PROCEDURE TRANSLATE_ROW(
152 x_hierarchy_id in varchar2,
153 x_plan_type in varchar2,
154 x_hierarchy_name varchar2,
155 x_description varchar2,
156 x_last_update_date in varchar2,
157 x_owner in varchar2,
158 x_custom_mode in varchar2) is
159
160 secgrp_id number;
161 view_appid number;
162 owner_id number;
163 ludate date;
164 f_luby number; -- entity owner in file
165 f_ludate date; -- entity update date in file
166 db_luby number; -- entity owner in db
167 db_ludate date; -- entity update date in db
168 begin
169 -- Translate owner to file_last_updated_by
170 if (x_owner = 'SEED') then
171 f_luby := 1;
172 else
173 f_luby := 0;
174 end if;
175
176 -- Translate char last_update_date to date
177 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
178
179 --
180 -- update the translation
181 --
182 begin
183 select LAST_UPDATED_BY, LAST_UPDATE_DATE
184 into db_luby, db_ludate
185 from MSD_HIERARCHIES
186 where HIERARCHY_id = to_number(x_HIERARCHY_id) and
187 nvl(plan_type,-1) =nvl( to_char(x_plan_type),-1) ;
188
189 -- Update record, honoring customization mode.
190 -- Record should be updated only if:
191 -- a. CUSTOM_MODE = FORCE, or
192 -- b. file owner is CUSTOM, db owner is SEED
193 -- c. owners are the same, and file_date > db_date
194 if ((x_custom_mode = 'FORCE') or
195 ((f_luby = 0) and (db_luby = 1)) or
196 ((f_luby = db_luby) and (f_ludate > db_ludate)))
197 then
198 update MSD_HIERARCHIES
199 set
200 HIERARCHY_name = nvl(x_HIERARCHY_name, HIERARCHY_name),
201 DESCRIPTION = nvl(x_description, DESCRIPTION),
202 LAST_UPDATE_DATE = f_ludate,
203 LAST_UPDATED_BY = f_luby,
204 LAST_UPDATE_LOGIN = 0
205 where HIERARCHY_id = to_number(x_HIERARCHY_id)
206 and nvl( plan_type ,-1)= nvl( to_char(x_plan_type) , -1)
207 and userenv('LANG') = (select language_code
208 from FND_LANGUAGES
209 where installed_flag = 'B');
210 end if;
211 EXCEPTION
212 when no_data_found then null;
213 end;
214 end TRANSLATE_ROW;
215
216
217 PROCEDURE LOAD_HIERARCHY_LEVEL_ROW(
218 x_HIERARCHY_ID in varchar2,
219 x_plan_type in varchar2,
220 x_LEVEL_ID in varchar2,
221 x_PARENT_LEVEL_ID in varchar2,
222 x_RELATIONSHIP_VIEW in varchar2,
223 x_LEVEL_VALUE_COLUMN in varchar2,
224 x_LEVEL_VALUE_PK_COLUMN in varchar2,
225 x_LEVEL_VALUE_DESC_COLUMN in varchar2,
226 x_PARENT_VALUE_COLUMN in varchar2,
227 x_PARENT_VALUE_PK_COLUMN in varchar2,
228 x_PARENT_VALUE_DESC_COLUMN in varchar2,
229 x_last_update_date in varchar2,
230 x_owner in varchar2,
231 x_custom_mode in varchar2) IS
232
233 f_luby number; -- entity owner in file
234 f_ludate date; -- entity update date in file
235 db_luby number; -- entity owner in db
236 db_ludate date; -- entity update date in db
237 h_mod number; -- was any hierarchy level modified?
238
239 begin
240 -- Translate owner to file_last_updated_by
241 if (x_owner = 'SEED') then
242 f_luby := 1;
243 else
244 f_luby := 0;
245 end if;
246
247 -- Translate char last_update_date to date
248 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
249
250 -- check if any hierarchy levels exist that were updated by !seed
251 begin
252 select 1 into h_mod from dual
253 where exists(select 1 from msd_hierarchy_levels
254 where hierarchy_id = to_number(x_HIERARCHY_id)
255 and nvl( plan_type,-1) = nvl( to_char(x_plan_type),-1)
256 and last_updated_by <> 1);
257 exception
258 when no_data_found then
259 h_mod := 0;
260 end;
261
262 begin
263 select LAST_UPDATED_BY, LAST_UPDATE_DATE
264 into db_luby, db_ludate
265 from MSD_HIERARCHY_LEVELS
266 where HIERARCHY_ID = to_number(x_HIERARCHY_id)
267 and level_id = to_number(x_level_id)
268 and parent_level_id = to_number(x_parent_level_id)
269 and nvl( plan_type,-1) = nvl( to_char(x_plan_type),-1) ;
270
271
272 -- Update record, honoring customization mode.
273 -- Record should be updated only if:
274 -- a. CUSTOM_MODE = FORCE, or
275 -- b. file owner is CUSTOM, db owner is SEED
276 -- c. owners are the same, and file_date > db_date
277 if ((x_custom_mode = 'FORCE') or
278 ((f_luby = 0) and (db_luby = 1)) or
279 ((f_luby = db_luby) and (f_ludate > db_ludate)))
280 then
281 update MSD_HIERARCHY_LEVELS set
282 relationship_view = x_RELATIONSHIP_VIEW,
283 level_value_column = x_LEVEL_VALUE_COLUMN,
284 level_value_pk_column = x_LEVEL_VALUE_PK_COLUMN,
285 level_value_desc_column = x_LEVEL_VALUE_DESC_COLUMN,
286 parent_value_column = x_PARENT_VALUE_COLUMN,
287 parent_value_pk_column = x_PARENT_VALUE_PK_COLUMN,
288 parent_value_desc_column = x_PARENT_VALUE_DESC_COLUMN,
289 LAST_UPDATE_DATE = f_ludate,
290 LAST_UPDATED_BY = f_luby,
291 LAST_UPDATE_LOGIN = 0
292 where HIERARCHY_ID = to_number(X_HIERARCHY_ID)
293 and level_id = to_number(x_level_id)
294 and parent_level_id = to_number(x_parent_level_id)
295 and nvl(plan_type,-1) = nvl(to_char(x_plan_type),-1) ;
296 end if;
297 EXCEPTION
298 when no_data_found then
299 /* Record doesn't exist - do not insert if the hierarchy has
300 been modified and the file owner is seed unless running
301 in FORCE mode */
302 if ((x_custom_mode = 'FORCE') or
303 not(h_mod = 1 and x_owner = 'SEED'))
304 then
305 /* do not insert if hierarchy is complete unless
306 running in FORCE mode */
307 if ((x_custom_mode = 'FORCE') or
308 not(is_hierarchy_complete(to_number(X_HIERARCHY_ID),to_char(x_plan_type))))
309 then
310 insert into MSD_HIERARCHY_LEVELS(
311 HIERARCHY_ID,
312 PLAN_TYPE ,
313 LEVEL_ID,
314 PARENT_LEVEL_ID,
315 RELATIONSHIP_VIEW,
316 LEVEL_VALUE_COLUMN,
317 LEVEL_VALUE_PK_COLUMN,
318 level_value_desc_column,
319 PARENT_VALUE_COLUMN,
320 PARENT_VALUE_PK_COLUMN,
321 parent_value_desc_column,
322 CREATION_DATE,
323 CREATED_BY,
324 LAST_UPDATE_DATE,
325 LAST_UPDATED_BY,
326 LAST_UPDATE_LOGIN
327 ) SELECT
328 to_number(X_HIERARCHY_ID),
329 to_char(X_PLAN_TYPE) ,
330 to_number(X_LEVEL_ID),
331 to_number(X_PARENT_LEVEL_ID),
332 X_RELATIONSHIP_VIEW,
333 X_LEVEL_VALUE_COLUMN,
334 X_LEVEL_VALUE_PK_COLUMN,
335 x_level_value_desc_column,
336 X_PARENT_VALUE_COLUMN,
337 X_PARENT_VALUE_PK_COLUMN,
338 x_parent_value_desc_column,
339 f_ludate,
340 f_luby,
341 f_ludate,
342 f_luby,
343 0
344 FROM dual
345 WHERE
346 (x_custom_mode = 'FORCE')
347 OR
348 (
349 /* child level does not already have a parent */
350 not exists(select level_id from msd_hierarchy_levels
351 where HIERARCHY_ID = to_number(x_HIERARCHY_id)
352 and nvl(plan_type,-1) =nvl( to_char(x_plan_type),-1)
353 and level_id = to_number(x_level_id))
354 AND
355 /* parent level does not already have a child */
356 not exists(select level_id from msd_hierarchy_levels
357 where HIERARCHY_ID = to_number(x_HIERARCHY_id)
358 and nvl(plan_type,-1) =nvl( to_char(x_plan_type),-1)
359 and parent_level_id = to_number(x_parent_level_id))
360 AND
361 /* child level is not topmost */
362 not exists(select level_id from msd_levels
363 where level_id = to_number(x_level_id)
364 and level_type_code = '1'
365 and nvl(plan_type,-1) =nvl( to_char(x_plan_type),-1) )
366 AND
367 /* parent level is not bottom-most */
368 not exists(select level_id from msd_levels
369 where level_id = to_number(x_parent_level_id)
370 and level_type_code = '2'
371 and nvl(plan_type,-1) =nvl( to_char(x_plan_type),-1) )
372 );
373 end if;
374 end if;
375 end;
376 END LOAD_HIERARCHY_LEVEL_ROW;
377
378
379 function is_hierarchy_complete(hid number, p_plan_type varchar2 ) return boolean is
380
381 lvl number;
382 lvl_type msd_levels.level_type_code%TYPE;
383 hcount number;
384 ctr number := 0;
385
386 begin
387 /* get bound on hierarchy levels */
388 select count(*)
389 into hcount
390 from msd_hierarchy_levels
391 where hierarchy_id = hid
392 and nvl(plan_type,-1) =nvl( p_plan_type,-1) ;
393
394 /* get bottom level */
395 begin
396 select l.level_id
397 into lvl
398 from msd_levels l, msd_hierarchies h
399 where h.hierarchy_id = hid
400 and l.dimension_code = h.dimension_code
401 and l.level_type_code = 2
402 and nvl(h.plan_type,-1) = nvl( p_plan_type,-1)
403 and nvl( l.plan_type,-1) = nvl(p_plan_type,-1) ;
404
405 EXCEPTION
406 when NO_DATA_FOUND then
407 return false;
408 end;
409
410 /* try to loop until top level is reached */
411 loop
412
413 ctr := ctr+1;
414
415 /* get parent of level in this hierarchy */
416 begin
417 select l.level_id, l.level_type_code
418 into lvl, lvl_type
419 from msd_hierarchy_levels mhl, msd_levels l
420 where mhl.level_id = lvl
421 and mhl.hierarchy_id = hid
422 and mhl.parent_level_id = l.level_id
423 and nvl(mhl.plan_type,-1) = nvl( p_plan_type,-1)
424 and nvl( l.plan_type,-1) = nvl( p_plan_type,-1) ;
425
426 EXCEPTION
427 when NO_DATA_FOUND then
428 return false;
429 end;
430
431 /* is this the top level? */
432 if (lvl_type = '1') then
433 return true;
434 end if;
435
436 /* does hierarchy have a loop? */
437 if (ctr > hcount) then
438 return false;
439 end if;
440 end loop;
441
442 end is_hierarchy_complete;
443
444 end MSD_HIERARCHIES_PKG;