1 package body RRS_SITESTH_PKG as
2 /* $Header: RRSSTHPB.pls 120.2 2005/12/29 06:31 pfarkade noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_SITE_ID in NUMBER,
6 X_SITE_IDENTIFICATION_NUMBER in VARCHAR2,
7 X_SITE_TYPE_CODE in VARCHAR2,
8 X_SITE_STATUS_CODE in VARCHAR2,
9 X_BRANDNAME_CODE in VARCHAR2,
10 X_CALENDAR_CODE in VARCHAR2,
11 X_LOCATION_ID in NUMBER,
12 X_SITE_PARTY_ID in NUMBER,
13 X_PARTY_SITE_ID in NUMBER,
14 X_LE_PARTY_ID in NUMBER,
15 X_PROPERTY_LOCATION_ID in NUMBER,
16 X_TEMPLATE_COUNTRY in VARCHAR2,
17 X_START_DATE in DATE,
18 X_END_DATE in DATE,
19 X_IS_TEMPLATE_FLAG in VARCHAR2,
20 X_OBJECT_VERSION_NUMBER in NUMBER,
21 X_NAME in VARCHAR2,
22 X_CREATION_DATE in DATE,
23 X_CREATED_BY in NUMBER,
24 X_LAST_UPDATE_DATE in DATE,
25 X_LAST_UPDATED_BY in NUMBER,
26 X_LAST_UPDATE_LOGIN in NUMBER,
27 X_ORGANIZATION_ID in NUMBER,
28 X_DESCRIPTION in VARCHAR2
29 ) is
30 l_site_id NUMBER;
31
32 cursor C is select ROWID from RRS_SITES_B
33 where SITE_ID = l_site_id
34 ;
35
36 begin
37
38 select nvl(X_SITE_ID ,RRS_SITES_S.nextval)
39 into l_site_id
40 from dual;
41
42 insert into RRS_SITES_B (
43 SITE_ID,
44 SITE_IDENTIFICATION_NUMBER,
45 SITE_TYPE_CODE,
46 SITE_STATUS_CODE,
47 BRANDNAME_CODE,
48 CALENDAR_CODE,
49 LOCATION_ID,
50 SITE_PARTY_ID,
51 PARTY_SITE_ID,
52 LE_PARTY_ID,
53 PROPERTY_LOCATION_ID,
54 TEMPLATE_COUNTRY,
55 START_DATE,
56 END_DATE,
57 IS_TEMPLATE_FLAG,
58 OBJECT_VERSION_NUMBER,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN,
64 ORGANIZATION_ID
65 ) values (
66 l_site_id,
67 X_SITE_IDENTIFICATION_NUMBER,
68 X_SITE_TYPE_CODE,
69 X_SITE_STATUS_CODE,
70 X_BRANDNAME_CODE,
71 X_CALENDAR_CODE,
72 X_LOCATION_ID,
73 X_SITE_PARTY_ID,
74 X_PARTY_SITE_ID,
75 X_LE_PARTY_ID,
76 X_PROPERTY_LOCATION_ID,
77 X_TEMPLATE_COUNTRY,
78 X_START_DATE,
79 X_END_DATE,
80 X_IS_TEMPLATE_FLAG,
81 X_OBJECT_VERSION_NUMBER,
82 X_CREATION_DATE,
83 X_CREATED_BY,
84 X_LAST_UPDATE_DATE,
85 X_LAST_UPDATED_BY,
86 X_LAST_UPDATE_LOGIN,
87 X_ORGANIZATION_ID
88 );
89
90 insert into RRS_SITES_TL (
91 SITE_ID,
92 NAME,
93 CREATED_BY,
94 CREATION_DATE,
95 LAST_UPDATED_BY,
96 LAST_UPDATE_DATE,
97 LAST_UPDATE_LOGIN,
98 LANGUAGE,
99 SOURCE_LANG,
100 DESCRIPTION
101 ) select
102 l_site_id,
103 X_NAME,
104 X_CREATED_BY,
105 X_CREATION_DATE,
106 X_LAST_UPDATED_BY,
107 X_LAST_UPDATE_DATE,
108 X_LAST_UPDATE_LOGIN,
109 L.LANGUAGE_CODE,
110 userenv('LANG'),
111 X_DESCRIPTION
112 from FND_LANGUAGES L
113 where L.INSTALLED_FLAG in ('I', 'B')
114 and not exists
115 (select NULL
116 from RRS_SITES_TL T
117 where T.SITE_ID = l_site_id
118 and T.LANGUAGE = L.LANGUAGE_CODE);
119
120 open c;
121 fetch c into X_ROWID;
122 if (c%notfound) then
123 close c;
124 raise no_data_found;
125 end if;
126 close c;
127
128 end INSERT_ROW;
129
130 procedure LOCK_ROW (
131 X_SITE_ID in NUMBER,
132 X_SITE_IDENTIFICATION_NUMBER in VARCHAR2,
133 X_SITE_TYPE_CODE in VARCHAR2,
134 X_SITE_STATUS_CODE in VARCHAR2,
135 X_BRANDNAME_CODE in VARCHAR2,
136 X_CALENDAR_CODE in VARCHAR2,
137 X_LOCATION_ID in NUMBER,
138 X_SITE_PARTY_ID in NUMBER,
139 X_PARTY_SITE_ID in NUMBER,
140 X_LE_PARTY_ID in NUMBER,
141 X_PROPERTY_LOCATION_ID in NUMBER,
142 X_TEMPLATE_COUNTRY in VARCHAR2,
143 X_START_DATE in DATE,
144 X_END_DATE in DATE,
145 X_IS_TEMPLATE_FLAG in VARCHAR2,
146 X_OBJECT_VERSION_NUMBER in NUMBER,
147 X_NAME in VARCHAR2,
148 X_ORGANIZATION_ID in NUMBER,
149 X_DESCRIPTION in VARCHAR2
150 ) is
151 cursor c is select
152 SITE_IDENTIFICATION_NUMBER,
153 SITE_TYPE_CODE,
154 SITE_STATUS_CODE,
155 BRANDNAME_CODE,
156 CALENDAR_CODE,
157 LOCATION_ID,
158 SITE_PARTY_ID,
159 PARTY_SITE_ID,
160 LE_PARTY_ID,
161 PROPERTY_LOCATION_ID,
162 TEMPLATE_COUNTRY,
163 START_DATE,
164 END_DATE,
165 IS_TEMPLATE_FLAG,
166 OBJECT_VERSION_NUMBER,
167 ORGANIZATION_ID
168 from RRS_SITES_B
169 where SITE_ID = X_SITE_ID
170 for update of SITE_ID nowait;
171 recinfo c%rowtype;
172
173 cursor c1 is select
174 NAME,
175 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG,
176 DESCRIPTION
177 from RRS_SITES_TL
178 where SITE_ID = X_SITE_ID
179 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
180 for update of SITE_ID nowait;
181 begin
182 open c;
183 fetch c into recinfo;
184 if (c%notfound) then
185 close c;
186 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
187 app_exception.raise_exception;
188 end if;
189 close c;
190 if ( (recinfo.SITE_IDENTIFICATION_NUMBER = X_SITE_IDENTIFICATION_NUMBER)
191 AND (recinfo.SITE_TYPE_CODE = X_SITE_TYPE_CODE)
192 AND (recinfo.SITE_STATUS_CODE = X_SITE_STATUS_CODE)
193 AND ((recinfo.BRANDNAME_CODE = X_BRANDNAME_CODE)
194 OR ((recinfo.BRANDNAME_CODE is null) AND (X_BRANDNAME_CODE is null)))
195 AND ((recinfo.CALENDAR_CODE = X_CALENDAR_CODE)
196 OR ((recinfo.CALENDAR_CODE is null) AND (X_CALENDAR_CODE is null)))
197 AND ((recinfo.LOCATION_ID = X_LOCATION_ID)
198 OR ((recinfo.LOCATION_ID is null) AND (X_LOCATION_ID is null)))
199 AND ((recinfo.SITE_PARTY_ID = X_SITE_PARTY_ID)
200 OR ((recinfo.SITE_PARTY_ID is null) AND (X_SITE_PARTY_ID is null)))
201 AND ((recinfo.PARTY_SITE_ID = X_PARTY_SITE_ID)
202 OR ((recinfo.PARTY_SITE_ID is null) AND (X_PARTY_SITE_ID is null)))
203 AND ((recinfo.LE_PARTY_ID = X_LE_PARTY_ID)
204 OR ((recinfo.LE_PARTY_ID is null) AND (X_LE_PARTY_ID is null)))
205 AND ((recinfo.PROPERTY_LOCATION_ID = X_PROPERTY_LOCATION_ID)
206 OR ((recinfo.PROPERTY_LOCATION_ID is null) AND (X_PROPERTY_LOCATION_ID is null)))
207 AND ((recinfo.TEMPLATE_COUNTRY = X_TEMPLATE_COUNTRY)
208 OR ((recinfo.TEMPLATE_COUNTRY is null) AND (X_TEMPLATE_COUNTRY is null)))
209 AND ((recinfo.START_DATE = X_START_DATE)
213 AND ((recinfo.IS_TEMPLATE_FLAG = X_IS_TEMPLATE_FLAG)
210 OR ((recinfo.START_DATE is null) AND (X_START_DATE is null)))
211 AND ((recinfo.END_DATE = X_END_DATE)
212 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
214 OR ((recinfo.IS_TEMPLATE_FLAG is null) AND (X_IS_TEMPLATE_FLAG is null)))
215 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
216 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
217 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
218 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
219 ) then
220 null;
221 else
222 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
223 app_exception.raise_exception;
224 end if;
225
226 for tlinfo in c1 loop
227 if (tlinfo.BASELANG = 'Y') then
228 if ( (tlinfo.NAME = X_NAME)
229 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
230 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
231 ) then
232 null;
233 else
234 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
235 app_exception.raise_exception;
236 end if;
237 end if;
238 end loop;
239 return;
240 end LOCK_ROW;
241
242 procedure UPDATE_ROW (
243 X_SITE_ID in NUMBER,
244 X_SITE_IDENTIFICATION_NUMBER in VARCHAR2,
245 X_SITE_TYPE_CODE in VARCHAR2,
246 X_SITE_STATUS_CODE in VARCHAR2,
247 X_BRANDNAME_CODE in VARCHAR2,
248 X_CALENDAR_CODE in VARCHAR2,
249 X_LOCATION_ID in NUMBER,
250 X_SITE_PARTY_ID in NUMBER,
251 X_PARTY_SITE_ID in NUMBER,
252 X_LE_PARTY_ID in NUMBER,
253 X_PROPERTY_LOCATION_ID in NUMBER,
254 X_TEMPLATE_COUNTRY in VARCHAR2,
255 X_START_DATE in DATE,
256 X_END_DATE in DATE,
257 X_IS_TEMPLATE_FLAG in VARCHAR2,
258 X_OBJECT_VERSION_NUMBER in NUMBER,
259 X_NAME in VARCHAR2,
260 X_LAST_UPDATE_DATE in DATE,
261 X_LAST_UPDATED_BY in NUMBER,
262 X_LAST_UPDATE_LOGIN in NUMBER,
263 X_ORGANIZATION_ID in NUMBER,
264 X_DESCRIPTION in VARCHAR2
265 ) is
266 begin
267 update RRS_SITES_B set
268 SITE_IDENTIFICATION_NUMBER = X_SITE_IDENTIFICATION_NUMBER,
269 SITE_TYPE_CODE = X_SITE_TYPE_CODE,
270 SITE_STATUS_CODE = X_SITE_STATUS_CODE,
271 BRANDNAME_CODE = X_BRANDNAME_CODE,
272 CALENDAR_CODE = X_CALENDAR_CODE,
273 LOCATION_ID = X_LOCATION_ID,
274 SITE_PARTY_ID = X_SITE_PARTY_ID,
275 PARTY_SITE_ID = X_PARTY_SITE_ID,
276 LE_PARTY_ID = X_LE_PARTY_ID,
277 PROPERTY_LOCATION_ID = X_PROPERTY_LOCATION_ID,
278 TEMPLATE_COUNTRY = X_TEMPLATE_COUNTRY,
279 START_DATE = X_START_DATE,
280 END_DATE = X_END_DATE,
281 IS_TEMPLATE_FLAG = X_IS_TEMPLATE_FLAG,
282 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
283 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
284 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
285 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
286 ORGANIZATION_ID = X_ORGANIZATION_ID
287 where SITE_ID = X_SITE_ID;
288
289 if (sql%notfound) then
290 raise no_data_found;
291 end if;
292
293 update RRS_SITES_TL set
294 NAME = X_NAME,
295 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
296 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
297 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
298 SOURCE_LANG = userenv('LANG'),
299 DESCRIPTION = X_DESCRIPTION
300 where SITE_ID = X_SITE_ID
301 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
302
303 if (sql%notfound) then
304 raise no_data_found;
305 end if;
306 end UPDATE_ROW;
307
308 procedure DELETE_ROW (
309 X_SITE_ID in NUMBER
310 ) is
311 begin
312 delete from RRS_SITES_TL
313 where SITE_ID = X_SITE_ID;
314
315 if (sql%notfound) then
316 raise no_data_found;
317 end if;
318
319 delete from RRS_SITES_B
320 where SITE_ID = X_SITE_ID;
321
322 if (sql%notfound) then
323 raise no_data_found;
324 end if;
325 end DELETE_ROW;
326
327 procedure ADD_LANGUAGE
328 is
329 begin
330 delete from RRS_SITES_TL T
331 where not exists
332 (select NULL
333 from RRS_SITES_B B
334 where B.SITE_ID = T.SITE_ID
335 );
336
337 update RRS_SITES_TL T set (
338 NAME,
339 DESCRIPTION
340 ) = (select
341 B.NAME,
342 B.DESCRIPTION
343 from RRS_SITES_TL B
344 where B.SITE_ID = T.SITE_ID
345 and B.LANGUAGE = T.SOURCE_LANG)
346 where (
347 T.SITE_ID,
348 T.LANGUAGE
349 ) in (select
350 SUBT.SITE_ID,
351 SUBT.LANGUAGE
352 from RRS_SITES_TL SUBB, RRS_SITES_TL SUBT
353 where SUBB.SITE_ID = SUBT.SITE_ID
354 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
355 and (SUBB.NAME <> SUBT.NAME
356 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
357 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
358 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
359 ));
360
361 insert into RRS_SITES_TL (
362 SITE_ID,
363 NAME,
364 CREATED_BY,
365 CREATION_DATE,
366 LAST_UPDATED_BY,
367 LAST_UPDATE_DATE,
368 LAST_UPDATE_LOGIN,
369 LANGUAGE,
370 SOURCE_LANG,
371 DESCRIPTION
372 ) select /*+ ORDERED */
373 B.SITE_ID,
374 B.NAME,
375 B.CREATED_BY,
376 B.CREATION_DATE,
377 B.LAST_UPDATED_BY,
378 B.LAST_UPDATE_DATE,
379 B.LAST_UPDATE_LOGIN,
380 L.LANGUAGE_CODE,
381 B.SOURCE_LANG,
385 and B.LANGUAGE = userenv('LANG')
382 B.DESCRIPTION
383 from RRS_SITES_TL B, FND_LANGUAGES L
384 where L.INSTALLED_FLAG in ('I', 'B')
386 and not exists
387 (select NULL
388 from RRS_SITES_TL T
389 where T.SITE_ID = B.SITE_ID
390 and T.LANGUAGE = L.LANGUAGE_CODE);
391 end ADD_LANGUAGE;
392
393 end RRS_SITESTH_PKG;
[Home] [Help]