1 package body BSC_KPI_SHELL_CMDS_PKG as
2 /* $Header: BSCKSHLB.pls 115.6 2003/02/12 14:26:03 adrao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INDICATOR in NUMBER,
6 X_SHELL_CMD_ID in NUMBER,
7 X_COMMAND in VARCHAR2,
8 X_NAME in VARCHAR2
9 ) is
10 cursor C is select ROWID from BSC_KPI_SHELL_CMDS_TL
11 where INDICATOR = X_INDICATOR
12 and SHELL_CMD_ID = X_SHELL_CMD_ID
13 and LANGUAGE = userenv('LANG')
14 ;
15 begin
16 insert into BSC_KPI_SHELL_CMDS_TL (
17 INDICATOR,
18 SHELL_CMD_ID,
19 NAME,
20 COMMAND,
21 LANGUAGE,
22 SOURCE_LANG
23 ) select
24 X_INDICATOR,
25 X_SHELL_CMD_ID,
26 X_NAME,
27 X_COMMAND,
28 L.LANGUAGE_CODE,
29 userenv('LANG')
30 from FND_LANGUAGES L
31 where L.INSTALLED_FLAG in ('I', 'B')
32 and not exists
33 (select NULL
34 from BSC_KPI_SHELL_CMDS_TL T
35 where T.INDICATOR = X_INDICATOR
36 and T.SHELL_CMD_ID = X_SHELL_CMD_ID
37 and T.LANGUAGE = L.LANGUAGE_CODE);
38
39 open c;
40 fetch c into X_ROWID;
41 if (c%notfound) then
42 close c;
43 raise no_data_found;
44 end if;
45 close c;
46
47 end INSERT_ROW;
48
49 procedure LOCK_ROW (
50 X_INDICATOR in NUMBER,
51 X_SHELL_CMD_ID in NUMBER,
52 X_COMMAND in VARCHAR2,
53 X_NAME in VARCHAR2
54 ) is
55 cursor c1 is select
56 COMMAND,
57 NAME,
58 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
59 from BSC_KPI_SHELL_CMDS_TL
60 where INDICATOR = X_INDICATOR
61 and SHELL_CMD_ID = X_SHELL_CMD_ID
62 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
63 for update of INDICATOR nowait;
64 begin
65 for tlinfo in c1 loop
66 if (tlinfo.BASELANG = 'Y') then
67 if ( (tlinfo.NAME = X_NAME)
68 AND ((tlinfo.COMMAND = X_COMMAND)
69 OR ((tlinfo.COMMAND is null) AND (X_COMMAND is null)))
70 ) then
71 null;
72 else
73 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
74 app_exception.raise_exception;
75 end if;
76 end if;
77 end loop;
78 return;
79 end LOCK_ROW;
80
81 procedure UPDATE_ROW (
82 X_INDICATOR in NUMBER,
83 X_SHELL_CMD_ID in NUMBER,
84 X_COMMAND in VARCHAR2,
85 X_NAME in VARCHAR2
86 ) is
87 begin
88 update BSC_KPI_SHELL_CMDS_TL set
89 COMMAND = X_COMMAND,
90 NAME = X_NAME,
91 SOURCE_LANG = userenv('LANG')
92 where INDICATOR = X_INDICATOR
93 and SHELL_CMD_ID = X_SHELL_CMD_ID
94 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
95
96 if (sql%notfound) then
97 raise no_data_found;
98 end if;
99 end UPDATE_ROW;
100
101 procedure DELETE_ROW (
102 X_INDICATOR in NUMBER,
103 X_SHELL_CMD_ID in NUMBER
104 ) is
105 begin
106 delete from BSC_KPI_SHELL_CMDS_TL
107 where INDICATOR = X_INDICATOR
108 and SHELL_CMD_ID = X_SHELL_CMD_ID;
109
110 if (sql%notfound) then
111 raise no_data_found;
112 end if;
113
114 end DELETE_ROW;
115
116 procedure ADD_LANGUAGE
117 is
118 begin
119 update BSC_KPI_SHELL_CMDS_TL T set (
120 NAME
121 ) = (select
122 B.NAME
123 from BSC_KPI_SHELL_CMDS_TL B
124 where B.INDICATOR = T.INDICATOR
125 and B.SHELL_CMD_ID = T.SHELL_CMD_ID
126 and B.LANGUAGE = T.SOURCE_LANG)
127 where (
128 T.INDICATOR,
129 T.SHELL_CMD_ID,
130 T.LANGUAGE
131 ) in (select
132 SUBT.INDICATOR,
133 SUBT.SHELL_CMD_ID,
134 SUBT.LANGUAGE
135 from BSC_KPI_SHELL_CMDS_TL SUBB, BSC_KPI_SHELL_CMDS_TL SUBT
136 where SUBB.INDICATOR = SUBT.INDICATOR
137 and SUBB.SHELL_CMD_ID = SUBT.SHELL_CMD_ID
138 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
139 and (SUBB.NAME <> SUBT.NAME
140 ));
141
142 insert into BSC_KPI_SHELL_CMDS_TL (
143 INDICATOR,
144 SHELL_CMD_ID,
145 NAME,
146 COMMAND,
147 LANGUAGE,
148 SOURCE_LANG
149 ) select
150 B.INDICATOR,
151 B.SHELL_CMD_ID,
152 B.NAME,
153 B.COMMAND,
154 L.LANGUAGE_CODE,
155 B.SOURCE_LANG
156 from BSC_KPI_SHELL_CMDS_TL B, FND_LANGUAGES L
157 where L.INSTALLED_FLAG in ('I', 'B')
158 and B.LANGUAGE = userenv('LANG')
159 and not exists
160 (select NULL
161 from BSC_KPI_SHELL_CMDS_TL T
162 where T.INDICATOR = B.INDICATOR
163 and T.SHELL_CMD_ID = B.SHELL_CMD_ID
164 and T.LANGUAGE = L.LANGUAGE_CODE);
165 end ADD_LANGUAGE;
166
167 end BSC_KPI_SHELL_CMDS_PKG;
[Home] [Help]