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