1 package body bsc_dimension_edit as
2 /* $Header: BSCEDITB.pls 120.4 2007/02/08 09:41:57 ankgoel ship $*/
3 /*===========================================================================+
4 | Copyright (c) 1995 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 | FILENAME |
8 | BSCEDITB.pls |
9 | |
10 | DESCRIPTION |
11 | Package for backend logic of BSC edit dimension page. |
12 | 20-Aug-03 Adeulgao fixed bug#3008243 eliminated hard coding |
13 | of schema name |
14 | 04-NOV-2003 PAJOHRI Bug #3232366 |
15 | 07-Jan-04 SMULYE Bug 3343979 Fixed API Delete_Codes_CascadeMN to |
16 | delete only from base tables , not system tables. |
17 | |
18 | 14-Jan-04 MREZA Bug 3363584 Fixed API checkUsercodeChange to |
19 | increase width of l_old_user_code. |
20 | 16-NOV-2006 ankgoel Color By KPI enh#5244136 |
21 | |
22 +==========================================================================+*/
23
24 h_installed_languages BSC_UPDATE_UTIL.t_array_of_varchar2;
25 h_num_installed_languages NUMBER;
26
27 CURSOR LEVEL is
28 SELECT DISTINCT L.DIM_LEVEL_ID, D.LEVEL_TABLE_NAME
29 FROM BSC_SYS_COM_DIM_LEVELS L, BSC_SYS_DIM_LEVELS_B D
30 WHERE L.DIM_LEVEL_ID = D.DIM_LEVEL_ID;
31
32 CURSOR CHILD (l_table_name varchar2) is
33 SELECT T.LEVEL_TABLE_NAME AS CHILDTABLE , P.level_pk_col
34 FROM BSC_SYS_DIM_LEVELS_B T, BSC_SYS_DIM_LEVEL_RELS R, BSC_SYS_DIM_LEVELS_B P
35 WHERE T.DIM_LEVEL_ID = R.DIM_LEVEL_ID
36 and R.PARENT_DIM_LEVEL_ID =P.dim_level_id
37 AND R.RELATION_TYPE = 1
38 and P.level_table_name=l_table_name;
39
40 UNIQUE_CONSTRAINT_VIOLATED exception;
41
42 procedure security_sync is
43 l_stmt varchar2(3000);
44 L_LEVEL_ID NUMBER;
45 L_TABLE_NAME VARCHAR2(30);
46 e_unexpected_error EXCEPTION;
47 begin
48 IF (LEVEL%isopen) THEN
49 close LEVEL;
50 END IF;
51 open LEVEL;
52 LOOP
53 fetch LEVEL into L_LEVEL_ID, L_TABLE_NAME;
54 exit when LEVEL%notfound;
55 l_stmt:='DELETE FROM BSC_USER_LIST_ACCESS '||
56 ' WHERE (RESPONSIBILITY_ID, TAB_ID) IN '||
57 '( SELECT LA.RESPONSIBILITY_ID, LA.TAB_ID '||
58 ' FROM BSC_SYS_COM_DIM_LEVELS L, BSC_USER_LIST_ACCESS LA '||
59 ' WHERE L.TAB_ID = LA.TAB_ID '||
60 ' AND L.DIM_LEVEL_INDEX = LA.DIM_LEVEL_INDEX '||
61 ' AND L.DIM_LEVEL_ID = :1 '||
62 ' AND LA.DIM_LEVEL_VALUE <> 0 '||
63 ' AND LA.DIM_LEVEL_VALUE NOT IN ( '||
64 ' SELECT CODE FROM '||L_TABLE_NAME||'))';
65 execute immediate l_stmt using L_LEVEL_ID;
66 commit;
67 END LOOP;
68
69 close LEVEL;
70
71 h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);
72 IF h_num_installed_languages = -1 THEN
73 RAISE e_unexpected_error;
74 END IF;
75
76 end security_sync;
77
78 procedure deleteNormalRow( l_dim_table IN VARCHAR2,
79 l_deleted_code IN NUMBER) is
80 deleted_code BSC_UPDATE_UTIL.t_array_of_number;
81 temp boolean;
82 begin
83 deleted_code(1):=l_deleted_code;
84 temp:=BSC_UPDATE_DIM.Delete_Codes_Cascade(
85 l_dim_table,
86 deleted_code, 1);
87 --commit;
88 /* if (temp=FALSE) then raise program_error; end if; */
89 end;
90
91 procedure deleteMNRow(l_dim_table IN VARCHAR2,
92 l_key_column1 IN VARCHAR2,
93 l_key_column2 IN VARCHAR2,
94 l_rowid IN VARCHAR2) is
95 TYPE CURSORTYPE IS REF CURSOR;
96 l_cursor CURSORTYPE;
97 l_code1 NUMBER;
98 l_code2 NUMBER;
99 l_stmt VARCHAR2(2000);
100 result BOOLEAN :=false;
101 begin
102 l_stmt := 'SELECT '||l_key_column1||', '||l_key_column2||' FROM '||l_dim_table||' WHERE rowid='''||l_rowid||'''';
103 open l_cursor for l_stmt;
104 fetch l_cursor into l_code1, l_code2;
105 close l_cursor;
106 result := Delete_Codes_CascadeMN(
107 l_dim_table,
108 l_key_column1,
109 l_key_column2,
110 l_code1 ,
111 l_code2
112 );
113 --commit;
114 EXCEPTION
115 WHEN OTHERS THEN
116 BSC_MESSAGE.Add(x_message => SQLERRM,
117 x_source => 'BSC_UPDATE_DIM.deleteMNRow');
118 end deleteMNRow;
119
120 procedure markKPI( l_dim_table IN VARCHAR2) is
121 begin
122 UPDATE BSC_KPIS_B K
123 SET PROTOTYPE_FLAG = 6,
124 LAST_UPDATED_BY = BSC_APPS.fnd_global_user_id,
125 LAST_UPDATE_DATE = SYSDATE
126 WHERE INDICATOR IN (SELECT D.INDICATOR
127 FROM BSC_KPI_DIM_LEVELS_B D
128 WHERE K.INDICATOR = D.INDICATOR AND
129 (UPPER(D.LEVEL_TABLE_NAME) = UPPER(l_dim_table) OR
130 UPPER(D.TABLE_RELATION) = UPPER(l_dim_table))) AND
131 PROTOTYPE_FLAG in (0, 6, 7);
132
133 -- Color By KPI: Mark KPIs for color re-calculation
134 UPDATE bsc_kpi_analysis_measures_b k
135 SET prototype_flag = 7
136 WHERE indicator IN (SELECT D.INDICATOR
137 FROM BSC_KPI_DIM_LEVELS_B D
138 WHERE K.INDICATOR = D.INDICATOR AND
139 (UPPER(D.LEVEL_TABLE_NAME) = UPPER(l_dim_table) OR
140 UPPER(D.TABLE_RELATION) = UPPER(l_dim_table)));
141 commit;
142 end markKPI;
143
144 procedure saveNormalRowNW(l_dim_table in varchar2, l_fk in varchar2,
145 l_fk_user in varchar2, l_code in number, l_user_code in varchar2,
146 l_name in varchar2, l_fkcode in number, l_fkusercode in varchar2,
147 l_message out nocopy varchar2) is
148 l_code_number number;
149 l_fkcode_number number;
150 l_name2 varchar2(2000);
151 begin
152 l_fkcode_number:=to_number(l_fkcode);
153 l_name2:=removeComma(l_name);
154 if (l_code is not null) then
155 l_code_number:=to_number(l_code);
156 updateNormalRowNW(l_dim_table,l_fk,l_fk_user,l_code_number,
157 l_user_code, l_name2, l_fkcode_number, l_fkusercode, l_message);
158 else
159 insertNormalRowNW(l_dim_table,l_fk,l_fk_user,
160 l_user_code, l_name2, l_fkcode_number, l_fkusercode, l_message);
161 end if;
162 end saveNormalRowNW;
163
164 procedure saveNormalRowNO(l_dim_table in varchar2, l_code in varchar2,
165 l_user_code in varchar2, l_name in varchar2, l_message out nocopy varchar2) is
166 l_code_number number;
167 l_name2 varchar2(2000);
168 begin
169 l_name2:=removeComma(l_name);
170 if (l_code is not null) then
171 l_code_number:=to_number(l_code);
172 updateNormalRowNO(l_dim_table,l_code_number,l_user_code,l_name2,l_message);
173 else
174 insertNormalRowNO(l_dim_table,l_user_code,l_name2, l_message);
175 end if;
176 end saveNormalRowNO;
177
178 procedure saveMNRow(l_dim_table in varchar2,
179 l_key_column1 in varchar2, l_key_column2 in varchar2,
180 l_code1 in number, l_code2 in number, l_rowid in varchar2,
181 l_message out nocopy varchar2 ) is
182 l_stmt varchar2(3000);
183 begin
184 if (l_rowid is not null) then
185 if (checkMNrecord(l_dim_table, l_key_column1,l_key_column2,
186 l_code1,l_code2, l_rowid)) then
187 l_message:=fnd_message.get_string('BSC','BSC_PMD_LDR_UNICONSTR_VIOLATE');
188 else
189 l_stmt:='update '||l_dim_table ||' set '||
190 l_key_column1 ||'=:1, '||
191 l_key_column2 ||'=:2 ' ||
192 ' where rowid=:3 ';
193 execute immediate l_stmt using l_code1,l_code2, l_rowid;
194 end if;
195 else
196 if (checkMNrecord(l_dim_table, l_key_column1,l_key_column2, l_code1,l_code2)) then
197 l_message:=fnd_message.get_string('BSC','BSC_PMD_LDR_UNICONSTR_VIOLATE');
198 --raise_application_error(-20000,'UNIQUE_CONSTRAINT_VIOLATED');
199 else
200 l_stmt:='insert into '||l_dim_table ||'( '||
201 l_key_column1 ||','|| l_key_column2 ||')'||
202 'values(:1, :2)';
203 execute immediate l_stmt using l_code1,l_code2;
204 --commit;
205 end if;
206 end if;
207
208 end saveMNRow;
209
210 procedure updateNormalRowNO(l_dim_table in varchar2, l_code in number,
211 l_user_code in varchar2, l_name in varchar2,l_message out nocopy varchar2) is
212 l_stmt varchar2(3000);
213 l_source_lang varchar2(4);
214 UsercodeChange boolean ;
215 begin
216 if (checkrecord(l_dim_table, l_user_code, l_name, l_code)) then
217 l_message:=fnd_message.get_string('BSC','BSC_PMD_LDR_UNICONSTR_VIOLATE');
218 else
219 UsercodeChange:=checkUsercodeChange(l_dim_table, l_code, l_user_code);
220 l_source_lang:=USERENV('LANG');
221 l_stmt:=' update '||l_dim_table||' set '||
222 ' user_code=:1 where code=:2 ';
223 execute immediate l_stmt using l_user_code, l_code;
224 --commit;
225 --l_stmt:=' update '||l_dim_table||' set '||
226 -- ' name=:1, source_lang=:2 where code=:3 and language=:4 ';
227 --execute immediate l_stmt using l_name, l_source_lang, l_code, l_source_lang;
228 l_stmt:=' update '||l_dim_table||' set '||
229 ' name=:1, source_lang=:2 where code=:3 and (language=:4 or source_lang=:5) ';
230 execute immediate l_stmt using l_name, l_source_lang, l_code, l_source_lang, l_source_lang;
231 --commit;
232 if (UsercodeChange and checkChild(l_dim_table) > 0) then
233 cascadeUsercodeChange(l_dim_table, l_code, l_user_code);
234 end if;
235 end if;
236 end updateNormalRowNO;
237
238 procedure insertNormalRowNO(l_dim_table in varchar2,
239 l_user_code in varchar2, l_name in varchar2, l_message out nocopy varchar2)is
240 l_stmt varchar2(3000);
241 l_code number;
242 l_source_lang varchar2(4);
243 e_unexpected_error EXCEPTION;
244 begin
245 if (checkrecord(l_dim_table, l_user_code, l_name)) then
246 l_message:=fnd_message.get_string('BSC','BSC_PMD_LDR_UNICONSTR_VIOLATE');
247 -- raise_application_error(-20000,'UNIQUE_CONSTRAINT_VIOLATED');
248 else
249 l_code:=BSC_UPDATE_DIM.get_new_code(l_dim_table);
250 l_source_lang:=USERENV('LANG');
251
252 IF h_num_installed_languages is NULL THEN
253 h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);
254 IF h_num_installed_languages = -1 THEN
255 RAISE e_unexpected_error;
256 END IF;
257 END IF;
258
259
260 FOR h_i IN 1 .. h_num_installed_languages LOOP
261 l_stmt := 'INSERT INTO '||l_dim_table||' ('||
262 ' code, user_code, name, language, source_lang)'||
263 ' values (:1,:2,:3,:4,:5)';
264 execute immediate l_stmt using l_code,l_user_code, l_name, h_installed_languages(h_i),l_source_lang;
265 END LOOP;
266 --commit;
267 end if;
268
269 end insertNormalRowNO;
270
271 procedure updateNormalRowNW(l_dim_table in varchar2, l_fk in varchar2,
272 l_fk_user in varchar2, l_code in number, l_user_code in varchar2,
273 l_name in varchar2, l_fkcode in number, l_fkusercode in varchar2,
274 l_message out nocopy varchar2)is
275 TYPE curcode IS REF CURSOR;
276 cv curcode;
277 l_code_number number;
278 l_stmt varchar2(3000);
279 l_source_lang varchar2(4);
280 UsercodeChange boolean ;
281 begin
282 l_message := null;
283 if (checkrecord(l_dim_table, l_user_code, l_name, l_code)) then
284 l_stmt:='select code from '||l_dim_table ||' where (user_code=:1 or name=:2) and ('||l_fk||' is null or '||l_fk_user||' is null) and code!=:3';
285 open cv for l_stmt using l_user_code, l_name, l_code;
286 fetch cv into l_code_number;
287 close cv;
288 if (l_code_number is not null) then
289 deleteNormalRow(l_dim_table, l_code_number);
290 else
291 l_message:=fnd_message.get_string('BSC','BSC_PMD_LDR_UNICONSTR_VIOLATE');
292 end if;
293 end if;
294 if (l_message is null) then
295 UsercodeChange:=checkUsercodeChange(l_dim_table, l_code, l_user_code);
296 l_source_lang:=USERENV('LANG');
297 l_stmt:=' update '||l_dim_table||' set '||
298 ' user_code=:1, '||l_fk||'=:2, '||l_fk_user||'=:3 where code=:4 ';
299 execute immediate l_stmt using l_user_code, l_fkcode, l_fkusercode, l_code;
300 --commit;
301 --l_stmt:=' update '||l_dim_table||' set '||
302 -- ' name=:1, source_lang=:2 where code=:3 and language=:4 ';
303 --execute immediate l_stmt using l_name, l_source_lang, l_code, l_source_lang;
304 l_stmt:=' update '||l_dim_table||' set '||
305 ' name=:1, source_lang=:2 where code=:3 and (language=:4 or source_lang=:5) ';
306 execute immediate l_stmt using l_name, l_source_lang, l_code, l_source_lang, l_source_lang;
307 --commit;
308 if (UsercodeChange and checkChild(l_dim_table) > 0) then
309 cascadeUsercodeChange(l_dim_table, l_code, l_user_code);
310 end if;
311 end if;
312 end updateNormalRowNW;
313
314 procedure insertNormalRowNW(l_dim_table in varchar2, l_fk in varchar2,
315 l_fk_user in varchar2, l_user_code in varchar2,
316 l_name in varchar2, l_fkcode in number, l_fkusercode in varchar2,
317 l_message out nocopy varchar2) is
318 TYPE curcode IS REF CURSOR;
319 cv curcode;
320 l_stmt varchar2(3000);
321 l_code number;
322 l_source_lang varchar2(4);
323 e_unexpected_error EXCEPTION;
324 begin
325 if (checkrecord(l_dim_table, l_user_code, l_name)) then
326 l_stmt:='select code from '||l_dim_table ||' where (user_code=:1 or name=:2) and ('||l_fk||' is null or '||l_fk_user||' is null)';
327 open cv for l_stmt using l_user_code, l_name;
328 fetch cv into l_code;
329 close cv;
330 if (l_code is not null) then
331 updateNormalRowNW(l_dim_table, l_fk, l_fk_user, l_code, l_user_code, l_name, l_fkcode, l_fkusercode, l_message);
332 else
333 l_message:=fnd_message.get_string('BSC','BSC_PMD_LDR_UNICONSTR_VIOLATE');
334 --raise_application_error(-20000,'UNIQUE_CONSTRAINT_VIOLATED');
335 end if;
336 else
337 l_code:=BSC_UPDATE_DIM.get_new_code(l_dim_table);
338 l_source_lang:=USERENV('LANG');
339
340 IF h_num_installed_languages is NULL THEN
341 h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);
342 IF h_num_installed_languages = -1 THEN
343 RAISE e_unexpected_error;
344 END IF;
345 END IF;
346
347 FOR h_i IN 1 .. h_num_installed_languages LOOP
348 l_stmt := 'INSERT INTO '||l_dim_table||' ('||
349 ' code, user_code, name, language, source_lang,'||
350 l_fk||','||l_fk_user||')'||
351 ' values (:1,:2,:3,:4,:5,:6,:7)';
352 execute immediate l_stmt using l_code,l_user_code, l_name, h_installed_languages(h_i),l_source_lang,l_fkcode, l_fkusercode;
353 END LOOP;
354 --commit;
355 end if;
356
357 end insertNormalRowNW;
358
359 FUNCTION checkrecord(l_dim_table in varchar2, l_user_code in varchar2,
360 l_name in varchar2) return boolean is
361 TYPE curtyp IS REF CURSOR;
362 cv curtyp;
363 l_stmt varchar2(3000);
364 l_count number:=0;
365 begin
366 l_stmt:='select count(*) from '||l_dim_table ||' where user_code=:1';
367 open cv for l_stmt using l_user_code;
368 fetch cv into l_count;
369 close cv;
370 if (l_count>0) then
371 --dbms_output.put_line('UNIQUE_CONSTRAINT_VIOLATED');
372 return true;
373 end if;
374 l_stmt:='select count(*) from '||l_dim_table ||' where name=:1';
375 open cv for l_stmt using l_name;
376 fetch cv into l_count;
377 close cv;
378 if (l_count>0) then
379 --dbms_output.put_line('UNIQUE_CONSTRAINT_VIOLATED');
380 return true;
381 end if;
382 return false;
383 end;
384
385 FUNCTION checkrecord(l_dim_table in varchar2, l_user_code in varchar2,
386 l_name in varchar2, l_code in number ) return boolean is
387 TYPE curtyp IS REF CURSOR;
388 cv curtyp;
389 l_stmt varchar2(3000);
390 l_count number:=0;
391 begin
392 l_stmt:='select count(*) from '||l_dim_table ||' where user_code=:1 and code!=:2';
393 open cv for l_stmt using l_user_code, l_code;
394 fetch cv into l_count;
395 close cv;
396 if (l_count>0) then
397 --dbms_output.put_line('UNIQUE_CONSTRAINT_VIOLATED');
398 return true;
399 end if;
400 l_stmt:='select count(*) from '||l_dim_table ||' where name=:1 and code!=:2';
401 open cv for l_stmt using l_name, l_code;
402 fetch cv into l_count;
403 close cv;
404 if (l_count>0) then
405 --dbms_output.put_line('UNIQUE_CONSTRAINT_VIOLATED');
406 return true;
407 end if;
408 return false;
409 end;
410
411 FUNCTION checkMNrecord(l_dim_table in varchar2,
412 l_key_column1 in varchar2, l_key_column2 in varchar2,
413 l_code1 in number, l_code2 in number) return boolean is
414 TYPE curtyp IS REF CURSOR;
415 cv curtyp;
416 l_stmt varchar2(3000);
417 l_count number:=0;
418 begin
419 l_stmt:='select count(*) from '||l_dim_table ||' where '||l_key_column1
420 ||'=:1 and '||l_key_column2||' =:2';
421 open cv for l_stmt using l_code1, l_code2;
422 fetch cv into l_count;
423 close cv;
424 if (l_count>0) then
425 --dbms_output.put_line('UNIQUE_CONSTRAINT_VIOLATED');
426 return true;
427 end if;
428 return false;
429 end;
430
431 FUNCTION checkMNrecord(l_dim_table in varchar2,
432 l_key_column1 in varchar2, l_key_column2 in varchar2,
433 l_code1 in number, l_code2 in number, l_rowid in varchar2) return boolean is
434 TYPE curtyp IS REF CURSOR;
435 cv curtyp;
436 l_stmt varchar2(3000);
437 l_count number:=0;
438 begin
439 l_stmt:='select count(*) from '||l_dim_table ||' where '||l_key_column1
440 ||'=:1 and '||l_key_column2||' =:2 and rowid!=:3 ';
441 open cv for l_stmt using l_code1, l_code2, l_rowid;
442 fetch cv into l_count;
443 close cv;
444 if (l_count>0) then
445 --dbms_output.put_line('UNIQUE_CONSTRAINT_VIOLATED');
446 return true;
447 end if;
448 return false;
449 end;
450
451 function removeComma(l_name varchar2) return varchar2
452 is
453 l_name2 varchar2(2000):=null;
454 l_position number;
455 TYPE curtyp IS REF CURSOR;
456 cv curtyp;
457 --l_stmt varchar2(2000);
458 begin
459 /*l_stmt:='select instr('||''''||l_name||''''||','','') from dual';
460 open cv for l_stmt;
461 fetch cv into l_position;
462 close cv; */
463 l_position:= instr(l_name,',');
464 if (l_position>0) then
465 l_name2:=substr(l_name,1,l_position-1)||substr(l_name,l_position+1,length(l_name));
466 else l_name2:=l_name;
467 end if;
468 /*l_stmt:='select instr('||''''||l_name2||''''||','','') from dual';
469 open cv for l_stmt;
470 fetch cv into l_position;
471 close cv; */
472 l_position:= instr(l_name2,',');
473 if (l_position>0) then
474 l_name2:=removeComma(l_name2);
475 end if;
476 return l_name2;
477 end;
478
479 FUNCTION Delete_Codes_CascadeMN(
480 x_dim_table IN VARCHAR2,
481 x_key_column1 IN VARCHAR2,
482 x_key_column2 IN VARCHAR2,
483 x_deleted_codes1 IN number,
484 x_deleted_codes2 IN number
485 ) RETURN BOOLEAN IS
486
487 h_condition VARCHAR2(32700);
488 h_i NUMBER;
489
490 TYPE t_cursor IS REF CURSOR;
491
492 c_base_tables t_cursor; -- x_key_column1, x_key_column2, h_column_type_p
493
494 c_base_tables_sql VARCHAR2(2000) := 'SELECT DISTINCT bt.table_name '||
495 ' FROM (SELECT DISTINCT table_name'||
496 ' FROM bsc_db_tables_rels'||
497 ' WHERE source_table_name IN '||
498 ' ( SELECT table_name FROM bsc_db_tables '||
499 ' WHERE table_type = 0 ) )bt,'||
500 ' bsc_db_tables_cols c '||
501 ' WHERE bt.table_name = c.table_name AND '||
502 ' (UPPER(c.column_name) = UPPER(:1) OR '||
503 ' UPPER(c.column_name) = UPPER(:2)) AND '||
504 ' c.column_type = :3 '||
505 ' GROUP BY bt.table_name '||
506 ' HAVING COUNT(*) = 2 ';
507
508 h_column_type_p VARCHAR2(1) := 'P';
509
510 h_base_table VARCHAR2(30);
511 h_sql VARCHAR2(32700);
512
513 BEGIN
514 h_condition := x_key_column1||'='||x_deleted_codes1||' and '
515 ||x_key_column2||'='||x_deleted_codes2;
516
517 -- Delete from system tables
518 OPEN c_base_tables FOR c_base_tables_sql USING x_key_column1, x_key_column2, h_column_type_p;
519 FETCH c_base_tables INTO h_base_table;
520 While c_base_tables%FOUND LOOP
521 h_sql := 'DELETE FROM '||h_base_table||
522 ' WHERE '||h_condition;
523 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
524 FETCH c_base_tables INTO h_base_table;
525 END LOOP;
526 CLOSE c_base_tables;
527
528
529 -- Delete from dimension table
530 h_sql := 'DELETE FROM '||x_dim_table||
531 ' WHERE '||h_condition;
532 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
533
534 RETURN TRUE;
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 BSC_MESSAGE.Add(x_message => SQLERRM,
539 x_source => 'BSC_UPDATE_DIM.Delete_Codes_CascadeMN');
540 RETURN FALSE;
541
542 END Delete_Codes_CascadeMN;
543
544 FUNCTION checkMVnot(l_table in varchar2) return boolean is
545 l_mv varchar2(100);
546 BEGIN
547 l_mv := UPPER(l_table)||'_MV';
548 IF (BSC_UTILITY.is_MV_Exists(l_mv)) THEN
549 RETURN FALSE;
550 --dbms_output.put_line('UNIQUE_CONSTRAINT_VIOLATED');
551 ELSE
552 RETURN TRUE;
553 END IF;
554 end;
555
556 procedure insertNormalRowNWM(l_dim_table in varchar2,
557 l_user_code in varchar2, l_name in varchar2, l_parentcount in number,
558 l_fklist in BSC_EDIT_VLIST,
559 l_fkvaluelist in BSC_EDIT_VLIST, l_fkuservaluelist in BSC_EDIT_VLIST,
560 l_message out nocopy varchar2) is
561 TYPE curcode IS REF CURSOR;
562 cv curcode;
563 l_stmt varchar2(5000);
564 l_code number;
565 l_source_lang varchar2(4);
566 l_t1 varchar2(1000):=null;
567 l_t2 varchar2(1000):=null;
568 l_t3 varchar2(1000):=null;
569 e_unexpected_error EXCEPTION;
570 begin
571 if (checkrecord(l_dim_table, l_user_code, l_name)) then
572 if (l_parentcount >= 1) then
573 l_t3 := '('||l_fklist(1)||' is null or '||l_fklist(1)||'_USR is null';
574 for i in 2 ..l_parentcount loop
575 l_t3 := l_t3||' or '||l_fklist(i)||' is null or '||l_fklist(i)||'_USR is null';
576 end loop;
577 l_t3 := l_t3||')';
578 end if;
579 l_stmt := 'select code from '||l_dim_table ||' where (user_code=:1 or name=:2) and '||l_t3;
580 open cv for l_stmt using l_user_code, l_name;
581 fetch cv into l_code;
582 close cv;
583 if (l_code is not null) then
584 updateNormalRowNWM(l_dim_table, l_code, l_user_code, l_name, l_parentcount, l_fklist, l_fkvaluelist, l_fkuservaluelist, l_message);
585 else
586 l_message:=fnd_message.get_string('BSC','BSC_PMD_LDR_UNICONSTR_VIOLATE');
587 --raise_application_error(-20000,'UNIQUE_CONSTRAINT_VIOLATED');
588 end if;
589 else
590 l_code:=BSC_UPDATE_DIM.get_new_code(l_dim_table);
591 l_source_lang:=USERENV('LANG');
592 for i in 1 ..l_parentcount loop
593 l_t1:=l_t1||' , '||l_fklist(i)||' , '||l_fklist(i)||'_USR';
594 l_t2:=l_t2||' , '||to_number(l_fkvaluelist(i))||' , '''||l_fkuservaluelist(i)||'''';
595 end loop;
596
597
598 IF h_num_installed_languages is NULL THEN
599 h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);
600 IF h_num_installed_languages = -1 THEN
601 RAISE e_unexpected_error;
602 END IF;
603 END IF;
604
605
606
607 FOR h_i IN 1 .. h_num_installed_languages LOOP
608 l_stmt := 'INSERT INTO '||l_dim_table||' ('||
609 ' code, user_code, name, language, source_lang '||
610 l_t1||
611 ' ) values (:1,:2,:3,:4,:5 ' ||l_t2 ||' )';
612 execute immediate l_stmt using l_code,l_user_code, l_name, h_installed_languages(h_i),l_source_lang;
613 END LOOP;
614 --commit;
615 end if;
616
617 end insertNormalRowNWM;
618
619 procedure updateNormalRowNWM(l_dim_table in varchar2, l_code in number,
620 l_user_code in varchar2, l_name in varchar2,l_parentcount in number,
621 l_fklist in BSC_EDIT_VLIST,
622 l_fkvaluelist in BSC_EDIT_VLIST, l_fkuservaluelist in BSC_EDIT_VLIST,
623 l_message out nocopy varchar2
624 )is
625 TYPE curcode IS REF CURSOR;
626 cv curcode;
627 l_code_number number;
628 l_stmt varchar2(3000);
629 l_t1 varchar2(1000):=null;
630 l_t2 varchar2(1000):=null;
631 l_source_lang varchar2(4);
632 UsercodeChange boolean ;
633 begin
634 l_message := null;
635 if (checkrecord(l_dim_table, l_user_code, l_name, l_code)) then
636 if (l_parentcount >= 1) then
637 l_t2 := '('||l_fklist(1)||' is null or '||l_fklist(1)||'_USR is null';
638 for i in 2 ..l_parentcount loop
639 l_t2 := l_t2||' or '||l_fklist(i)||' is null or '||l_fklist(i)||'_USR is null';
640 end loop;
641 l_t2 := l_t2||')';
642 end if;
643 l_stmt:='select code from '||l_dim_table||' where (user_code=:1 or name=:2) and code!=:3 and '||l_t2;
644 open cv for l_stmt using l_user_code, l_name, l_code;
645 fetch cv into l_code_number;
646 close cv;
647 if (l_code_number is not null) then
648 deleteNormalRow(l_dim_table, l_code_number);
649 else
650 l_message:=fnd_message.get_string('BSC','BSC_PMD_LDR_UNICONSTR_VIOLATE');
651 end if;
652 end if;
653 if (l_message is null) then
654 UsercodeChange:=checkUsercodeChange(l_dim_table, l_code, l_user_code);
655 l_source_lang:=USERENV('LANG');
656 for i in 1 ..l_parentcount loop
657 l_t1:=l_t1||' , '||l_fklist(i)||'='||to_number(l_fkvaluelist(i))||
658 ' , '||l_fklist(i)||'_USR='''||l_fkuservaluelist(i)||'''';
659 end loop;
660 l_stmt:=' update '||l_dim_table||' set '||
661 ' user_code=:1 '||l_t1||' where code=:2 ';
662 execute immediate l_stmt using l_user_code, l_code;
663 --commit;
664 --l_stmt:=' update '||l_dim_table||' set '||
665 -- ' name=:1, source_lang=:2 where code=:3 and language=:4 ';
666 --execute immediate l_stmt using l_name, l_source_lang, l_code, l_source_lang;
667 l_stmt:=' update '||l_dim_table||' set '||
668 ' name=:1, source_lang=:2 where code=:3 and (language=:4 or source_lang=:5) ';
669 execute immediate l_stmt using l_name, l_source_lang, l_code, l_source_lang, l_source_lang;
670 --commit;
671
672 if (UsercodeChange and checkChild(l_dim_table) > 0) then
673 cascadeUsercodeChange(l_dim_table, l_code, l_user_code);
674 end if;
675 end if;
676 end updateNormalRowNWM;
677
678 procedure saveNormalRowNWM(l_dim_table in varchar2, l_code in number,
679 l_user_code in varchar2, l_name in varchar2, l_parentcount in number,
680 l_fklist in BSC_EDIT_VLIST,
681 l_fkvaluelist in BSC_EDIT_VLIST, l_fkuservaluelist in BSC_EDIT_VLIST,
682 l_message out nocopy varchar2) is
683 l_code_number number;
684 l_fkcode_number number;
685 l_name2 varchar2(2000);
686 begin
687 l_name2:=removeComma(l_name);
688 if (l_code is not null) then
689 l_code_number:=to_number(l_code);
690 updateNormalRowNWM(l_dim_table,l_code_number,
691 l_user_code,l_name2,l_parentcount,l_fklist,
692 l_fkvaluelist,l_fkuservaluelist,l_message );
693 else
694 insertNormalRowNWM(l_dim_table,
695 l_user_code,l_name2,l_parentcount,l_fklist,
696 l_fkvaluelist,l_fkuservaluelist, l_message );
697 end if;
698 end saveNormalRowNWM;
699
700 FUNCTION checkUsercodeChange(l_dim_table in varchar2, l_code in number,
701 l_user_code in varchar2) return boolean is
702 TYPE curtyp IS REF CURSOR;
703 cv curtyp;
704 l_stmt varchar2(400);
705 l_old_user_code varchar2(2000);
706 begin
707 l_stmt:='select user_code from '||l_dim_table ||' where code=:1';
708 open cv for l_stmt using l_code;
709 fetch cv into l_old_user_code;
710 close cv;
711 if (l_old_user_code=l_user_code) then
712 return false;
713 else
714 return true;
715 end if;
716 end;
717
718 Function checkChild(l_dim_table in varchar2) return number is
719 TYPE curtyp IS REF CURSOR;
720 cv curtyp;
721 l_stmt varchar2(1500);
722 l_count number;
723 begin
724 l_stmt:='SELECT count(*) FROM BSC_SYS_DIM_LEVELS_B T, BSC_SYS_DIM_LEVEL_RELS R, BSC_SYS_DIM_LEVELS_B P WHERE T.DIM_LEVEL_ID = R.DIM_LEVEL_ID and R.PARENT_DIM_LEVEL_ID =P.dim_level_id AND R.RELATION_TYPE = 1 and P.level_table_name=:1';
725 open cv for l_stmt using l_dim_table;
726 fetch cv into l_count;
727 close cv;
728 return l_count;
729 end;
730
731 procedure cascadeUsercodeChange(l_dim_table in varchar2, l_code in number,
732 l_user_code in varchar2) is
733 l_childtable varchar2(50);
734 l_pk varchar2(100);
735 l_stmt varchar2(800);
736 begin
737
738 IF (CHILD%isopen) THEN
739 close CHILD;
740 END IF;
741 open CHILD(l_dim_table);
742 LOOP
743 fetch CHILD into l_childtable, l_pk;
744 exit when CHILD%notfound;
745 l_stmt:='update '||l_childtable ||' set '||l_pk||'_USR =:1 '||
746 ' where '||l_pk||' = :2';
747 execute immediate l_stmt using l_user_code, l_code;
748 commit;
749 END LOOP;
750
751 close CHILD;
752 end;
753
754 procedure checkViewExist(p_view_name varchar2,l_message out nocopy varchar2) is
755 l_sql varchar2(2000);
756 begin
757 l_sql:='select ''1'' from '||p_view_name||' where rownum=1';
758 execute immediate l_sql;
759 l_message:=null;
760 exception
761 when others then
762 l_message:=null;
763 fnd_message.set_name('BSC','BSC_PMD_LDR_VIEW_NOT_EXIST');
764 fnd_message.set_token('VIEW_NAME',p_view_name);
765 l_message:=fnd_message.get;
766
767
768 end;
769
770 procedure checkMetadata(p_table_name varchar2,p_query varchar2, l_message out nocopy varchar2) is
771 l_sql varchar2(2000);
772 begin
773 -- l_sql:='select ''1'' from '||p_view_name||' where rownum=1';
774 execute immediate p_query;
775 l_message:=null;
776 exception
777 when others then
778 l_message:=null;
779 fnd_message.set_name('BSC','BSC_PMD_LDR_METADATACORRUPTION');
780 fnd_message.set_token('TABLE_NAME',p_table_name);
781 l_message:=fnd_message.get;
782
783
784 end;
785
786 End bsc_dimension_edit;
[Home] [Help]