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