[Home] [Help]
PACKAGE BODY: APPS.IBE_MSITE_RESP_PKG
Source
1 PACKAGE BODY Ibe_Msite_Resp_Pkg AS
2 /* $Header: IBETMRSB.pls 120.4 2006/07/04 11:48:45 apgupta noship $ */
3
4 -- HISTORY
5 -- 12/13/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
6 -- 07/28/03 JQU
7 -- Modified procedures for addition of group_code column
8 -- *********************************************************************************
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_MSITE_RESP_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBETMRSB.pls';
12
13 PROCEDURE insert_row
14 (
15 p_msite_resp_id IN NUMBER,
16 p_object_version_number IN NUMBER,
17 p_msite_id IN NUMBER,
18 p_responsibility_id IN NUMBER,
19 p_application_id IN NUMBER,
20 p_start_date_active IN DATE,
21 p_end_date_active IN DATE,
22 p_sort_order IN NUMBER,
23 p_display_name IN VARCHAR2,
24 p_group_code IN VARCHAR2,
25 p_creation_date IN DATE,
26 p_created_by IN NUMBER,
27 p_last_update_date IN DATE,
28 p_last_updated_by IN NUMBER,
29 p_last_update_login IN NUMBER,
30 x_rowid OUT NOCOPY VARCHAR2,
31 x_msite_resp_id OUT NOCOPY NUMBER
32 )
33 IS
34 CURSOR c IS SELECT rowid FROM ibe_msite_resps_b
35 WHERE msite_resp_id = x_msite_resp_id;
36 CURSOR c2 IS SELECT ibe_msite_resps_b_s1.nextval FROM dual;
37
38 BEGIN
39
40 -- Primary key validation check
41 x_msite_resp_id := p_msite_resp_id;
42 IF ((x_msite_resp_id IS NULL) OR
43 (x_msite_resp_id = FND_API.G_MISS_NUM))
44 THEN
45 OPEN c2;
46 FETCH c2 INTO x_msite_resp_id;
47 CLOSE c2;
48 END IF;
49
50 -- insert base
51 INSERT INTO ibe_msite_resps_b
52 (
53 msite_resp_id,
54 object_version_number,
55 msite_id,
56 responsibility_id,
57 application_id,
58 start_date_active,
59 end_date_active,
60 sort_order,
61 group_code,
62 creation_date,
63 created_by,
64 last_update_date,
65 last_updated_by,
66 last_update_login
67 )
68 VALUES
69 (
70 x_msite_resp_id,
71 p_object_version_number,
72 p_msite_id,
73 p_responsibility_id,
74 p_application_id,
75 p_start_date_active,
76 decode(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active),
77 decode(p_sort_order, FND_API.G_MISS_NUM, NULL, p_sort_order),
78 decode(p_group_code, FND_API.G_MISS_CHAR, NULL, p_group_code),
79 decode(p_creation_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
80 p_creation_date),
81 decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
82 NULL, FND_GLOBAL.user_id, p_created_by),
83 decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
84 p_last_update_date),
85 decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
86 NULL, FND_GLOBAL.user_id, p_last_updated_by),
87 decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
88 NULL, FND_GLOBAL.login_id, p_last_update_login)
89 );
90
91 OPEN c;
92 FETCH c INTO x_rowid;
93 IF (c%NOTFOUND)
94 THEN
95 CLOSE c;
96 RAISE NO_DATA_FOUND;
97 END IF;
98 CLOSE c;
99
100
101 -- insert tl
102 IF ((p_msite_resp_id IS NULL) OR
103 (p_msite_resp_id = FND_API.G_MISS_NUM))
104 THEN
105 INSERT INTO ibe_msite_resps_tl
106 (
107 msite_resp_id,
108 object_version_number,
109 created_by,
110 creation_date,
111 last_updated_by,
112 last_update_date,
113 last_update_login,
114 display_name,
115 language,
116 source_lang
117 )
118 SELECT
119 x_msite_resp_id,
120 p_object_version_number,
121 decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
122 NULL, FND_GLOBAL.user_id, p_created_by),
123 decode(p_creation_date, FND_API.G_MISS_DATE, sysdate,
124 NULL, sysdate, p_creation_date),
125 decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
126 NULL, FND_GLOBAL.user_id, p_last_updated_by),
127 decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate,
128 NULL, sysdate, p_last_update_date),
129 decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
130 NULL, FND_GLOBAL.login_id, p_last_update_login),
131 p_display_name,
132 L.language_code,
133 userenv('LANG')
134 FROM fnd_languages L
135 WHERE L.installed_flag IN ('I', 'B')
136 AND NOT EXISTS
137 (SELECT NULL
138 FROM ibe_msite_resps_tl T
139 WHERE T.msite_resp_id = x_msite_resp_id
140 AND T.language = L.language_code);
141
142 OPEN c;
143 FETCH c INTO x_rowid;
144 IF (c%NOTFOUND) THEN
145 CLOSE c;
146 RAISE NO_DATA_FOUND;
147 END if;
148 CLOSE c;
149 END IF;
150
151 END insert_row;
152
153 PROCEDURE update_row
154 (
155 p_msite_resp_id IN NUMBER,
156 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
157 p_start_date_active IN DATE,
158 p_end_date_active IN DATE,
159 p_sort_order IN NUMBER,
160 p_display_name IN VARCHAR2,
161 p_group_code IN VARCHAR2,
162 p_last_update_date IN DATE,
163 p_last_updated_by IN NUMBER,
164 p_last_update_login IN NUMBER
165 )
166 IS
167 BEGIN
168
169 -- update base
170 UPDATE ibe_msite_resps_b SET
171 object_version_number = object_version_number + 1,
172 start_date_active = decode(p_start_date_active, FND_API.G_MISS_DATE,
173 start_date_active, p_start_date_active),
174 end_date_active = decode(p_end_date_active, FND_API.G_MISS_DATE,
175 end_date_active, p_end_date_active),
176 sort_order = decode(p_sort_order, FND_API.G_MISS_NUM,
177 sort_order, p_sort_order),
178 group_code = decode(p_group_code, FND_API.G_MISS_CHAR,
179 group_code, p_group_code),
180 last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate,
181 NULL, sysdate, p_last_update_date),
182 last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
183 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
184 p_last_updated_by),
185 last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
186 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
187 p_last_update_login)
188 WHERE msite_resp_id = p_msite_resp_id;
189 -- AND object_version_number = decode(p_object_version_number,
190 -- FND_API.G_MISS_NUM,
191 -- object_version_number,
192 -- p_object_version_number);
193 IF (sql%NOTFOUND) THEN
194 RAISE NO_DATA_FOUND;
195 END IF;
196
197 -- update tl
198 UPDATE ibe_msite_resps_tl SET
199 object_version_number = object_version_number + 1,
200 display_name = decode(p_display_name, FND_API.G_MISS_CHAR,
201 display_name, p_display_name),
202 last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate,
203 NULL, sysdate, p_last_update_date),
204 last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
205 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
206 p_last_updated_by),
207 last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
208 FND_GLOBAL.login_id, NULL, FND_GLOBAL.login_id,
209 p_last_update_login),
210 source_lang = USERENV('LANG')
211 WHERE msite_resp_id = p_msite_resp_id
212 -- AND object_version_number = decode(p_object_version_number,
213 -- FND_API.G_MISS_NUM,
214 -- object_version_number,
215 -- p_object_version_number)
216 AND USERENV('LANG') IN (language, source_lang);
217
218 IF (sql%NOTFOUND) THEN
219 RAISE NO_DATA_FOUND;
220 END IF;
221 END update_row;
222
223 -- ****************************************************************************
224 -- delete row
225 -- ****************************************************************************
226 PROCEDURE delete_row
227 (
228 p_msite_resp_id IN NUMBER
229 )
230 IS
231 BEGIN
232 DELETE FROM ibe_msite_resps_tl
233 WHERE msite_resp_id = p_msite_resp_id;
234
235 IF (sql%NOTFOUND) THEN
236 RAISE NO_DATA_FOUND;
237 END IF;
238
239 DELETE FROM ibe_msite_resps_b
240 WHERE msite_resp_id = p_msite_resp_id;
241
242 IF (sql%NOTFOUND) THEN
243 RAISE NO_DATA_FOUND;
244 END IF;
245
246 END delete_row;
247
248 PROCEDURE add_language
249 IS
250 BEGIN
251 delete FROM ibe_msite_resps_tl T
252 WHERE NOT EXISTS
253 (SELECT NULL
254 FROM ibe_msite_resps_b B
255 WHERE B.MSITE_RESP_ID = T.MSITE_RESP_ID
256 );
257
258 UPDATE ibe_msite_resps_tl T SET
259 (
260 DISPLAY_NAME
261 ) = (SELECT
262 B.DISPLAY_NAME
263 FROM ibe_msite_resps_tl B
264 WHERE B.MSITE_RESP_ID = T.MSITE_RESP_ID
265 AND B.LANGUAGE = T.SOURCE_LANG)
266 WHERE
267 (
268 T.MSITE_RESP_ID,
269 T.LANGUAGE
270 ) IN (select
271 SUBT.MSITE_RESP_ID,
272 SUBT.LANGUAGE
273 FROM ibe_msite_resps_tl SUBB, ibe_msite_resps_tl SUBT
274 WHERE SUBB.MSITE_RESP_ID = SUBT.MSITE_RESP_ID
275 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
276 AND (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
277 ));
278
279 INSERT INTO ibe_msite_resps_tl
280 (
281 MSITE_RESP_ID,
282 OBJECT_VERSION_NUMBER,
283 CREATED_BY,
284 CREATION_DATE,
285 LAST_UPDATED_BY,
286 LAST_UPDATE_DATE,
287 LAST_UPDATE_LOGIN,
288 DISPLAY_NAME,
289 LANGUAGE,
290 SOURCE_LANG
291 ) select
292 b.msite_resp_id,
293 b.object_version_number,
294 b.created_by,
295 b.creation_date,
296 b.last_updated_by,
297 b.last_update_date,
298 b.last_update_login,
299 b.display_name,
300 l.language_code,
301 b.source_lang
302 from ibe_msite_resps_tl b, fnd_languages l
303 where l.installed_flag in ('I', 'B')
304 and b.language = userenv('LANG')
305 and not exists
306 (select null
307 from ibe_msite_resps_tl t
308 where t.msite_resp_id = b.msite_resp_id
309 and t.language = l.language_code);
310 END add_language;
311
312 END Ibe_Msite_Resp_Pkg;