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