[Home] [Help]
PACKAGE BODY: APPS.BSC_TAB_VIEWS_PKG
Source
1 package body BSC_TAB_VIEWS_PKG as
2 /* $Header: BSCTBVWB.pls 115.6 2003/03/27 11:59:53 adeulgao ship $ */
3 PROCEDURE TRANSLATE_ROW
4 (
5 X_TAB_ID IN NUMBER,
6 X_TAB_VIEW_ID IN NUMBER,
7 X_NAME IN VARCHAR2,
8 X_HELP IN VARCHAR2,
9 X_LAST_UPDATE_DATE IN DATE,
10 X_LAST_UPDATED_BY IN NUMBER,
11 X_LAST_UPDATE_LOGIN IN NUMBER
12 ) IS
13 L_TAB NUMBER := -1;
14 BEGIN
15 SELECT COUNT(*) INTO L_TAB FROM BSC_TAB_VIEWS_TL WHERE TAB_ID = X_TAB_ID AND TAB_VIEW_ID = X_TAB_VIEW_ID;
16 IF (L_TAB > 0) THEN
17 UPDATE BSC_TAB_VIEWS_TL SET
18 NAME = NVL(X_NAME, NAME),
19 HELP = NVL(X_HELP, HELP),
20 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE, SYSDATE),
21 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
22 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
23 SOURCE_LANG = USERENV('LANG')
24 WHERE USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
25 AND TAB_ID = X_TAB_ID
26 AND TAB_VIEW_ID = X_TAB_VIEW_ID
27 AND LAST_UPDATE_DATE <= X_LAST_UPDATE_DATE;
28 ELSE
29 RAISE NO_DATA_FOUND;
30 END IF;
31
32 END TRANSLATE_ROW;
33
34 procedure INSERT_ROW (
35 X_ROWID in out NOCOPY VARCHAR2,
36 X_TAB_ID in NUMBER,
37 X_TAB_VIEW_ID in NUMBER,
38 X_ENABLED_FLAG in NUMBER,
39 X_NAME in VARCHAR2,
40 X_HELP in VARCHAR2,
41 X_CREATION_DATE in DATE,
42 X_CREATED_BY in NUMBER,
43 X_LAST_UPDATE_DATE in DATE,
44 X_LAST_UPDATED_BY in NUMBER,
45 X_LAST_UPDATE_LOGIN in NUMBER
46 ) is
47 cursor C is select ROWID from BSC_TAB_VIEWS_B
48 where TAB_ID = X_TAB_ID
49 and TAB_VIEW_ID = X_TAB_VIEW_ID
50 ;
51 begin
52 insert into BSC_TAB_VIEWS_B (
53 TAB_ID,
54 TAB_VIEW_ID,
55 ENABLED_FLAG,
56 CREATION_DATE ,
57 CREATED_BY,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY ,
60 LAST_UPDATE_LOGIN
61 ) values (
62 X_TAB_ID,
63 X_TAB_VIEW_ID,
64 X_ENABLED_FLAG,
65 NVL(X_CREATION_DATE, SYSDATE),
66 X_CREATED_BY,
67 NVL(X_LAST_UPDATE_DATE, SYSDATE),
68 X_LAST_UPDATED_BY ,
69 X_LAST_UPDATE_LOGIN
70 );
71 insert into BSC_TAB_VIEWS_TL (
72 TAB_ID,
73 TAB_VIEW_ID,
74 NAME,
75 HELP,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY ,
80 LAST_UPDATE_LOGIN,
81 LANGUAGE,
82 SOURCE_LANG
83 ) select
84 X_TAB_ID,
85 X_TAB_VIEW_ID,
86 X_NAME,
87 X_HELP,
88 NVL(X_CREATION_DATE , SYSDATE),
89 X_CREATED_BY,
90 NVL(X_LAST_UPDATE_DATE, SYSDATE),
91 X_LAST_UPDATED_BY,
92 X_LAST_UPDATE_LOGIN,
93 L.LANGUAGE_CODE,
94 userenv('LANG')
95 from FND_LANGUAGES L
96 where L.INSTALLED_FLAG in ('I', 'B')
97 and not exists
98 (select NULL
99 from BSC_TAB_VIEWS_TL T
100 where T.TAB_ID = X_TAB_ID
101 and T.TAB_VIEW_ID = X_TAB_VIEW_ID
102 and T.LANGUAGE = L.LANGUAGE_CODE);
103 open c;
104 fetch c into X_ROWID;
105 if (c%notfound) then
106 close c;
107 raise no_data_found;
108 end if;
109 close c;
110
111 end INSERT_ROW;
112
113 procedure LOCK_ROW (
114 X_TAB_ID in NUMBER,
115 X_TAB_VIEW_ID in NUMBER,
116 X_ENABLED_FLAG in NUMBER,
117 X_NAME in VARCHAR2,
118 X_HELP in VARCHAR2
119 ) is
120 cursor c is select
121 ENABLED_FLAG
122 from BSC_TAB_VIEWS_B
123 where TAB_ID = X_TAB_ID
124 and TAB_VIEW_ID = X_TAB_VIEW_ID
125 for update of TAB_ID nowait;
126 recinfo c%rowtype;
127
128 cursor c1 is select
129 NAME,
130 HELP,
131 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
132 from BSC_TAB_VIEWS_TL
133 where TAB_ID = X_TAB_ID
134 and TAB_VIEW_ID = X_TAB_VIEW_ID
135 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
136 for update of TAB_ID nowait;
137 begin
138 open c;
139 fetch c into recinfo;
140 if (c%notfound) then
141 close c;
142 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
143 app_exception.raise_exception;
144 end if;
145 close c;
146 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
147 ) then
148 null;
149 else
150 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
151 app_exception.raise_exception;
152 end if;
153
154 for tlinfo in c1 loop
155 if (tlinfo.BASELANG = 'Y') then
156 if ( (tlinfo.NAME = X_NAME)
157 AND (tlinfo.HELP = X_HELP)
158 ) then
159 null;
160 else
161 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
162 app_exception.raise_exception;
163 end if;
164 end if;
165 end loop;
166 return;
167 end LOCK_ROW;
168
169 procedure UPDATE_ROW (
170 X_TAB_ID in NUMBER,
171 X_TAB_VIEW_ID in NUMBER,
172 X_ENABLED_FLAG in NUMBER,
173 X_NAME in VARCHAR2,
174 X_HELP in VARCHAR2,
175 X_LAST_UPDATE_DATE in DATE,
176 X_LAST_UPDATED_BY in NUMBER,
177 X_LAST_UPDATE_LOGIN in NUMBER
178 ) is
179 begin
180 update BSC_TAB_VIEWS_B
181 SET ENABLED_FLAG = NVL(X_ENABLED_FLAG, ENABLED_FLAG),
182 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE, SYSDATE),
183 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY, LAST_UPDATED_BY),
184 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN, LAST_UPDATE_LOGIN)
185 where TAB_ID = X_TAB_ID
186 AND TAB_VIEW_ID = X_TAB_VIEW_ID
187 AND LAST_UPDATE_DATE <= NVL(X_LAST_UPDATE_DATE, SYSDATE);
188 if (sql%notfound) then
189 raise no_data_found;
190 end if;
191 update BSC_TAB_VIEWS_TL set
192 NAME = NVL(X_NAME, NAME),
193 HELP = NVL(X_HELP, HELP),
194 SOURCE_LANG = userenv('LANG')
195 where TAB_ID = X_TAB_ID
196 and TAB_VIEW_ID = X_TAB_VIEW_ID
197 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end UPDATE_ROW;
202
203 procedure DELETE_ROW (
204 X_TAB_ID in NUMBER,
205 X_TAB_VIEW_ID in NUMBER
206 ) is
207 begin
208 delete from BSC_TAB_VIEWS_TL
209 where TAB_ID = X_TAB_ID
210 and TAB_VIEW_ID = X_TAB_VIEW_ID;
211 if (sql%notfound) then
212 raise no_data_found;
213 end if;
214
215 delete from BSC_TAB_VIEWS_B
216 where TAB_ID = X_TAB_ID
217 and TAB_VIEW_ID = X_TAB_VIEW_ID;
218 if (sql%notfound) then
219 raise no_data_found;
220 end if;
221 end DELETE_ROW;
222
223 procedure ADD_LANGUAGE
224 is
225 l_user NUMBER;
226 begin
227
228 SELECT VS.USER#
229 INTO l_user
230 FROM V$SESSION VS WHERE VS.AUDSID =USERENV('SESSIONID');
231
232 delete from BSC_TAB_VIEWS_TL T
233 where not exists
234 (select NULL
235 from BSC_TAB_VIEWS_B B
236 where B.TAB_ID = T.TAB_ID
237 and B.TAB_VIEW_ID = T.TAB_VIEW_ID
238 );
239
240 update BSC_TAB_VIEWS_TL T set (
241 NAME,
242 HELP
243 ) = (select
244 B.NAME,
245 B.HELP
246 from BSC_TAB_VIEWS_TL B
247 where B.TAB_ID = T.TAB_ID
248 and B.TAB_VIEW_ID = T.TAB_VIEW_ID
249 and B.LANGUAGE = T.SOURCE_LANG)
250 where (
251 T.TAB_ID,
252 T.TAB_VIEW_ID,
253 T.LANGUAGE
254 ) in (select
255 SUBT.TAB_ID,
256 SUBT.TAB_VIEW_ID,
257 SUBT.LANGUAGE
258 from BSC_TAB_VIEWS_TL SUBB, BSC_TAB_VIEWS_TL SUBT
259 where SUBB.TAB_ID = SUBT.TAB_ID
260 and SUBB.TAB_VIEW_ID = SUBT.TAB_VIEW_ID
261 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
262 and (SUBB.NAME <> SUBT.NAME
263 or SUBB.HELP <> SUBT.HELP
264 ));
265
266 insert into BSC_TAB_VIEWS_TL (
267 TAB_ID,
268 TAB_VIEW_ID,
269 NAME,
270 HELP,
271 CREATION_DATE ,
272 CREATED_BY,
273 LAST_UPDATE_DATE,
274 LAST_UPDATED_BY ,
275 LAST_UPDATE_LOGIN,
276 LANGUAGE,
277 SOURCE_LANG
278 ) select
279 B.TAB_ID,
280 B.TAB_VIEW_ID,
281 B.NAME,
282 B.HELP,
283 SYSDATE,
284 l_user,
285 SYSDATE,
286 l_user,
287 l_user,
288 L.LANGUAGE_CODE,
289 B.SOURCE_LANG
290 from BSC_TAB_VIEWS_TL B, FND_LANGUAGES L
291 where L.INSTALLED_FLAG in ('I', 'B')
292 and B.LANGUAGE = userenv('LANG')
293 and not exists
294 (select NULL
295 from BSC_TAB_VIEWS_TL T
296 where T.TAB_ID = B.TAB_ID
297 and T.TAB_VIEW_ID = B.TAB_VIEW_ID
298 and T.LANGUAGE = L.LANGUAGE_CODE);
299 end ADD_LANGUAGE;
300
301 end BSC_TAB_VIEWS_PKG;