1 package body BSC_SYS_LABELS_PKG as
2 /* $Header: BSCSLBSB.pls 115.6 2003/02/12 14:29:19 adeulgao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SOURCE_TYPE in NUMBER,
6 X_SOURCE_CODE in NUMBER,
7 X_LABEL_ID in NUMBER,
8 X_LEFT_POSITION in NUMBER,
9 X_TOP_POSITION in NUMBER,
10 X_FONT_SIZE in NUMBER,
11 X_FONT_STYLE in NUMBER,
12 X_NAME in VARCHAR2
13 ) is
14 cursor C is select ROWID from BSC_SYS_LABELS_B
15 where SOURCE_TYPE = X_SOURCE_TYPE
16 and SOURCE_CODE = X_SOURCE_CODE
17 and LABEL_ID = X_LABEL_ID
18 ;
19 begin
20 insert into BSC_SYS_LABELS_B (
21 SOURCE_TYPE,
22 SOURCE_CODE,
23 LABEL_ID,
24 LEFT_POSITION,
25 TOP_POSITION,
26 FONT_SIZE,
27 FONT_STYLE
28 ) values (
29 X_SOURCE_TYPE,
30 X_SOURCE_CODE,
31 X_LABEL_ID,
32 X_LEFT_POSITION,
33 X_TOP_POSITION,
34 X_FONT_SIZE,
35 X_FONT_STYLE
36 );
37
38 insert into BSC_SYS_LABELS_TL (
39 SOURCE_TYPE,
40 SOURCE_CODE,
41 LABEL_ID,
42 NAME,
43 LANGUAGE,
44 SOURCE_LANG
45 ) select
46 X_SOURCE_TYPE,
47 X_SOURCE_CODE,
48 X_LABEL_ID,
49 X_NAME,
50 L.LANGUAGE_CODE,
51 userenv('LANG')
52 from FND_LANGUAGES L
53 where L.INSTALLED_FLAG in ('I', 'B')
54 and not exists
55 (select NULL
56 from BSC_SYS_LABELS_TL T
57 where T.SOURCE_TYPE = X_SOURCE_TYPE
58 and T.SOURCE_CODE = X_SOURCE_CODE
59 and T.LABEL_ID = X_LABEL_ID
60 and T.LANGUAGE = L.LANGUAGE_CODE);
61
62 open c;
63 fetch c into X_ROWID;
64 if (c%notfound) then
65 close c;
66 raise no_data_found;
67 end if;
68 close c;
69
70 end INSERT_ROW;
71
72 procedure LOCK_ROW (
73 X_SOURCE_TYPE in NUMBER,
74 X_SOURCE_CODE in NUMBER,
75 X_LABEL_ID in NUMBER,
76 X_LEFT_POSITION in NUMBER,
77 X_TOP_POSITION in NUMBER,
78 X_FONT_SIZE in NUMBER,
79 X_FONT_STYLE in NUMBER,
80 X_NAME in VARCHAR2
81 ) is
82 cursor c is select
83 LEFT_POSITION,
84 TOP_POSITION,
85 FONT_SIZE,
86 FONT_STYLE
87 from BSC_SYS_LABELS_B
88 where SOURCE_TYPE = X_SOURCE_TYPE
89 and SOURCE_CODE = X_SOURCE_CODE
90 and LABEL_ID = X_LABEL_ID
91 for update of SOURCE_TYPE nowait;
92 recinfo c%rowtype;
93
94 cursor c1 is select
95 NAME,
96 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
97 from BSC_SYS_LABELS_TL
98 where SOURCE_TYPE = X_SOURCE_TYPE
99 and SOURCE_CODE = X_SOURCE_CODE
100 and LABEL_ID = X_LABEL_ID
101 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
102 for update of SOURCE_TYPE nowait;
103 begin
104 open c;
105 fetch c into recinfo;
106 if (c%notfound) then
107 close c;
108 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
109 app_exception.raise_exception;
110 end if;
111 close c;
112 if ( ((recinfo.LEFT_POSITION = X_LEFT_POSITION)
113 OR ((recinfo.LEFT_POSITION is null) AND (X_LEFT_POSITION is null)))
114 AND ((recinfo.TOP_POSITION = X_TOP_POSITION)
115 OR ((recinfo.TOP_POSITION is null) AND (X_TOP_POSITION is null)))
116 AND ((recinfo.FONT_SIZE = X_FONT_SIZE)
117 OR ((recinfo.FONT_SIZE is null) AND (X_FONT_SIZE is null)))
118 AND ((recinfo.FONT_STYLE = X_FONT_STYLE)
119 OR ((recinfo.FONT_STYLE is null) AND (X_FONT_STYLE is null)))
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126
127 for tlinfo in c1 loop
128 if (tlinfo.BASELANG = 'Y') then
129 if ( (tlinfo.NAME = X_NAME)
130 ) then
131 null;
132 else
133 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 end if;
136 end if;
137 end loop;
138 return;
139 end LOCK_ROW;
140
141 procedure UPDATE_ROW (
142 X_SOURCE_TYPE in NUMBER,
143 X_SOURCE_CODE in NUMBER,
144 X_LABEL_ID in NUMBER,
145 X_LEFT_POSITION in NUMBER,
146 X_TOP_POSITION in NUMBER,
147 X_FONT_SIZE in NUMBER,
148 X_FONT_STYLE in NUMBER,
149 X_NAME in VARCHAR2
150 ) is
151 begin
152 update BSC_SYS_LABELS_B set
153 LEFT_POSITION = X_LEFT_POSITION,
154 TOP_POSITION = X_TOP_POSITION,
155 FONT_SIZE = X_FONT_SIZE,
156 FONT_STYLE = X_FONT_STYLE
157 where SOURCE_TYPE = X_SOURCE_TYPE
158 and SOURCE_CODE = X_SOURCE_CODE
159 and LABEL_ID = X_LABEL_ID;
160
161 if (sql%notfound) then
162 raise no_data_found;
163 end if;
164
165 update BSC_SYS_LABELS_TL set
166 NAME = X_NAME,
167 SOURCE_LANG = userenv('LANG')
168 where SOURCE_TYPE = X_SOURCE_TYPE
169 and SOURCE_CODE = X_SOURCE_CODE
170 and LABEL_ID = X_LABEL_ID
171 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176 end UPDATE_ROW;
177
178 procedure DELETE_ROW (
179 X_SOURCE_TYPE in NUMBER,
180 X_SOURCE_CODE in NUMBER,
181 X_LABEL_ID in NUMBER
182 ) is
183 begin
184 delete from BSC_SYS_LABELS_TL
185 where SOURCE_TYPE = X_SOURCE_TYPE
186 and SOURCE_CODE = X_SOURCE_CODE
187 and LABEL_ID = X_LABEL_ID;
188
189 if (sql%notfound) then
190 raise no_data_found;
191 end if;
192
193 delete from BSC_SYS_LABELS_B
194 where SOURCE_TYPE = X_SOURCE_TYPE
195 and SOURCE_CODE = X_SOURCE_CODE
196 and LABEL_ID = X_LABEL_ID;
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end DELETE_ROW;
202
203 procedure ADD_LANGUAGE
204 is
205 begin
206 delete from BSC_SYS_LABELS_TL T
207 where not exists
208 (select NULL
209 from BSC_SYS_LABELS_B B
210 where B.SOURCE_TYPE = T.SOURCE_TYPE
211 and B.SOURCE_CODE = T.SOURCE_CODE
212 and B.LABEL_ID = T.LABEL_ID
213 );
214
215 update BSC_SYS_LABELS_TL T set (
216 NAME
217 ) = (select
218 B.NAME
219 from BSC_SYS_LABELS_TL B
220 where B.SOURCE_TYPE = T.SOURCE_TYPE
221 and B.SOURCE_CODE = T.SOURCE_CODE
222 and B.LABEL_ID = T.LABEL_ID
223 and B.LANGUAGE = T.SOURCE_LANG)
224 where (
225 T.SOURCE_TYPE,
226 T.SOURCE_CODE,
227 T.LABEL_ID,
228 T.LANGUAGE
229 ) in (select
230 SUBT.SOURCE_TYPE,
231 SUBT.SOURCE_CODE,
232 SUBT.LABEL_ID,
233 SUBT.LANGUAGE
234 from BSC_SYS_LABELS_TL SUBB, BSC_SYS_LABELS_TL SUBT
235 where SUBB.SOURCE_TYPE = SUBT.SOURCE_TYPE
236 and SUBB.SOURCE_CODE = SUBT.SOURCE_CODE
237 and SUBB.LABEL_ID = SUBT.LABEL_ID
238 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
239 and (SUBB.NAME <> SUBT.NAME
240 ));
241
242 insert into BSC_SYS_LABELS_TL (
243 SOURCE_TYPE,
244 SOURCE_CODE,
245 LABEL_ID,
246 NAME,
247 LANGUAGE,
248 SOURCE_LANG
249 ) select
250 B.SOURCE_TYPE,
251 B.SOURCE_CODE,
252 B.LABEL_ID,
253 B.NAME,
254 L.LANGUAGE_CODE,
255 B.SOURCE_LANG
256 from BSC_SYS_LABELS_TL B, FND_LANGUAGES L
257 where L.INSTALLED_FLAG in ('I', 'B')
258 and B.LANGUAGE = userenv('LANG')
259 and not exists
260 (select NULL
261 from BSC_SYS_LABELS_TL T
262 where T.SOURCE_TYPE = B.SOURCE_TYPE
263 and T.SOURCE_CODE = B.SOURCE_CODE
264 and T.LABEL_ID = B.LABEL_ID
265 and T.LANGUAGE = L.LANGUAGE_CODE);
266 end ADD_LANGUAGE;
267
268 end BSC_SYS_LABELS_PKG;
[Home] [Help]