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