[Home] [Help]
PACKAGE BODY: APPS.CSC_PROF_GROUPS_PKG
Source
1 PACKAGE BODY CSC_PROF_GROUPS_PKG as
2 /* $Header: csctpgrb.pls 120.3 2005/09/18 23:41:17 vshastry ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PROF_GROUPS_PKG
5 -- Purpose :
6 -- History :
7 -- 07 Nov 02 jamose Upgrade table handler changes
8 -- 29 Nov 02 jamose made changes for the NOCOPY and FND_API.G_MISS*
9 -- 19 july 2005 tpalaniv Modified the translate_row and load_row APIs to fetch last_updated_by using FND API
10 -- 19-09-2005 vshastry Bug 4596220. Added condition in insert row
11 -- NOTE :
12 -- End of Comments
13
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_PROF_GROUPS_PKG';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctugrb.pls';
17
18 G_MISS_CHAR VARCHAR2(1) := FND_API.G_MISS_CHAR;
19 G_MISS_NUM NUMBER := FND_API.G_MISS_NUM;
20 G_MISS_DATE DATE := FND_API.G_MISS_DATE;
21
22 PROCEDURE Insert_Row(
23 px_GROUP_ID IN OUT NOCOPY NUMBER,
24 p_CREATED_BY NUMBER,
25 p_CREATION_DATE DATE,
26 p_LAST_UPDATED_BY NUMBER,
27 p_LAST_UPDATE_DATE DATE,
28 p_LAST_UPDATE_LOGIN NUMBER,
29 p_GROUP_NAME VARCHAR2,
30 p_GROUP_NAME_CODE VARCHAR2,
31 p_DESCRIPTION VARCHAR2,
32 p_START_DATE_ACTIVE DATE,
33 p_END_DATE_ACTIVE DATE,
34 p_USE_IN_CUSTOMER_DASHBOARD VARCHAR2,
35 p_PARTY_TYPE VARCHAR2,
36 p_SEEDED_FLAG VARCHAR2,
37 x_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
38 p_APPLICATION_ID NUMBER )
39
40 IS
41 CURSOR C2 IS SELECT CSC_PROF_GROUPS_S.nextval FROM sys.dual;
42
43 l_object_version_number number := 1;
44 ps_SEEDED_FLAG Varchar2(3);
45
46 BEGIN
47
48 /* added the below 2 lines for bug 4596220 */
49 ps_seeded_flag := p_seeded_flag;
50 IF NVL(p_seeded_flag, 'N') <> 'Y' THEN
51
52 /* Added This If Condition for Bug 1944040*/
53 If p_Created_by=1 then
54 ps_seeded_flag:='Y';
55 Else
56 ps_seeded_flag:=p_seeded_flag;
57 End If;
58 END IF;
59
60 If (px_GROUP_ID IS NULL) OR (px_GROUP_ID = G_MISS_NUM) then
61 OPEN C2;
62 FETCH C2 INTO px_GROUP_ID;
63 CLOSE C2;
64 End If;
65 -- to_date(NULL) added to include timestamp during creation
66 INSERT INTO CSC_PROF_GROUPS_B(
67 GROUP_ID,
68 CREATED_BY,
69 CREATION_DATE,
70 LAST_UPDATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATE_LOGIN,
73 GROUP_NAME_CODE,
74 START_DATE_ACTIVE,
75 END_DATE_ACTIVE,
76 USE_IN_CUSTOMER_DASHBOARD,
77 PARTY_TYPE,
78 SEEDED_FLAG,
79 OBJECT_VERSION_NUMBER,
80 APPLICATION_ID
81 ) VALUES (
82 px_GROUP_ID,
83 decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
84 decode( p_CREATION_DATE, G_MISS_DATE, to_date(NULL), p_CREATION_DATE),
85 decode( p_LAST_UPDATED_BY, G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
86 decode( p_LAST_UPDATE_DATE, G_MISS_DATE, to_date(NULL), p_LAST_UPDATE_DATE),
87 decode( p_LAST_UPDATE_LOGIN,G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
88 decode( p_GROUP_NAME_CODE, G_MISS_CHAR, NULL, p_GROUP_NAME_CODE),
89 decode( p_START_DATE_ACTIVE,G_MISS_DATE, to_date(NULL), p_START_DATE_ACTIVE),
90 decode( p_END_DATE_ACTIVE, G_MISS_DATE,to_date(NULL), p_END_DATE_ACTIVE),
91 decode( p_USE_IN_CUSTOMER_DASHBOARD,G_MISS_CHAR, NULL, p_USE_IN_CUSTOMER_DASHBOARD),
92 decode( p_PARTY_TYPE, G_MISS_CHAR, NULL, p_PARTY_TYPE),
93 decode( p_SEEDED_FLAG,G_MISS_CHAR, NULL, ps_SEEDED_FLAG),
94 l_object_version_number,
95 decode( p_APPLICATION_ID,G_MISS_NUM, NULL, p_APPLICATION_ID));
96
97 -- assign the object version number to the out parameter
98 x_object_version_number := l_object_version_number;
99
100 INSERT INTO CSC_PROF_GROUPS_TL(
101 GROUP_ID,
102 GROUP_NAME,
103 DESCRIPTION,
104 CREATED_BY,
105 CREATION_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_DATE,
108 LAST_UPDATE_LOGIN,
109 LANGUAGE,
110 SOURCE_LANG
111 ) select
112 Px_GROUP_ID,
113 decode( p_GROUP_NAME, G_MISS_CHAR, NULL, p_GROUP_NAME),
114 decode( p_DESCRIPTION,G_MISS_CHAR, NULL, p_DESCRIPTION),
115 decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
116 decode( p_CREATION_DATE,G_MISS_DATE,to_date(NULL), p_CREATION_DATE),
117 decode( p_LAST_UPDATED_BY,G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
118 decode( p_LAST_UPDATE_DATE,G_MISS_DATE,to_date(NULL), p_LAST_UPDATE_DATE),
119 decode( p_LAST_UPDATE_LOGIN,G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
120 L.LANGUAGE_CODE,
121 userenv('LANG')
122 from FND_LANGUAGES L
123 where L.INSTALLED_FLAG in ('I', 'B')
124 and not exists
125 (select NULL
126 from CSC_PROF_GROUPS_TL T
127 where T.GROUP_ID = Px_GROUP_ID
128 and T.LANGUAGE = L.LANGUAGE_CODE);
129
130 End Insert_Row;
131
132 PROCEDURE Update_Row(
133 p_GROUP_ID NUMBER,
134 p_LAST_UPDATED_BY NUMBER,
135 p_LAST_UPDATE_DATE DATE,
136 p_LAST_UPDATE_LOGIN NUMBER,
137 p_GROUP_NAME VARCHAR2,
138 p_GROUP_NAME_CODE VARCHAR2,
139 p_DESCRIPTION VARCHAR2,
140 p_START_DATE_ACTIVE DATE,
141 p_END_DATE_ACTIVE DATE,
142 p_USE_IN_CUSTOMER_DASHBOARD VARCHAR2,
143 p_PARTY_TYPE VARCHAR2,
144 p_SEEDED_FLAG VARCHAR2,
145 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
146 p_APPLICATION_ID NUMBER )
147 IS
148 BEGIN
149 Update CSC_PROF_GROUPS_B
150 SET
151 LAST_UPDATED_BY =p_LAST_UPDATED_BY,
152 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
153 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
154 GROUP_NAME_CODE = p_GROUP_NAME_CODE,
155 START_DATE_ACTIVE = p_START_DATE_ACTIVE,
156 END_DATE_ACTIVE = p_END_DATE_ACTIVE,
157 USE_IN_CUSTOMER_DASHBOARD = p_USE_IN_CUSTOMER_DASHBOARD,
158 PARTY_TYPE = p_PARTY_TYPE,
159 SEEDED_FLAG = p_SEEDED_FLAG,
160 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
161 APPLICATION_ID = p_APPLICATION_ID
162 where GROUP_ID = p_GROUP_ID
163 RETURNING OBJECT_VERSION_NUMBER INTO px_OBJECT_VERSION_NUMBER;
164
165 Update CSC_PROF_GROUPS_TL
166 SET
167 GROUP_NAME = p_GROUP_NAME,
168 DESCRIPTION = p_DESCRIPTION,
169 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
170 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
171 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
172 SOURCE_LANG = userenv('LANG')
173 where GROUP_ID = p_GROUP_ID
174 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
175
176 If (SQL%NOTFOUND) then
177 RAISE NO_DATA_FOUND;
178 End If;
179 END Update_Row;
180
181 PROCEDURE Delete_Row(
182 p_GROUP_ID NUMBER,
183 p_OBJECT_VERSION_NUMBER NUMBER)
184 IS
185 BEGIN
186
187 DELETE FROM CSC_PROF_GROUPS_B
188 WHERE GROUP_ID = p_GROUP_ID
189 AND OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;
190 If (SQL%NOTFOUND) then
191 RAISE NO_DATA_FOUND;
192 End If;
193
194
195 DELETE FROM CSC_PROF_GROUPS_TL
196 WHERE GROUP_ID = p_GROUP_ID;
197 If (SQL%NOTFOUND) then
198 RAISE NO_DATA_FOUND;
199 End If;
200
201 END Delete_Row;
202
203
204 procedure LOCK_ROW (
205 P_GROUP_ID in NUMBER,
206 P_OBJECT_VERSION_NUMBER in NUMBER
207 ) is
208 cursor c is select
209 GROUP_NAME_CODE,
210 START_DATE_ACTIVE,
211 END_DATE_ACTIVE,
212 USE_IN_CUSTOMER_DASHBOARD,
213 PARTY_TYPE,
214 OBJECT_VERSION_NUMBER
215 from CSC_PROF_GROUPS_B
216 where GROUP_ID = P_GROUP_ID
217 and object_version_number = p_object_version_number
218 for update of GROUP_ID nowait;
219 recinfo c%rowtype;
220
221 cursor c1 is select
222 GROUP_NAME,
223 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
224 from CSC_PROF_GROUPS_TL
225 where GROUP_ID = P_GROUP_ID
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227 for update of GROUP_ID nowait;
228 begin
229 open c;
230 fetch c into recinfo;
231 if (c%notfound) then
232 close c;
233 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
234 app_exception.raise_exception;
235 end if;
236 close c;
237 return;
238 end LOCK_ROW;
239
240 procedure ADD_LANGUAGE
241 is
242 begin
243 delete from CSC_PROF_GROUPS_TL T
244 where not exists
245 (select NULL
246 from CSC_PROF_GROUPS_B B
247 where B.GROUP_ID = T.GROUP_ID
248 );
249
250 update CSC_PROF_GROUPS_TL T set (
251 GROUP_NAME,
252 DESCRIPTION
253 ) = (select
254 B.GROUP_NAME,
255 B.DESCRIPTION
256 from CSC_PROF_GROUPS_TL B
257 where B.GROUP_ID = T.GROUP_ID
258 and B.LANGUAGE = T.SOURCE_LANG)
259 where (
260 T.GROUP_ID,
261 T.LANGUAGE
262 ) in (select
263 SUBT.GROUP_ID,
264 SUBT.LANGUAGE
265 from CSC_PROF_GROUPS_TL SUBB, CSC_PROF_GROUPS_TL SUBT
266 where SUBB.GROUP_ID = SUBT.GROUP_ID
267 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
268 and (SUBB.GROUP_NAME <> SUBT.GROUP_NAME
269 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
270 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
271 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
272 ));
273
274 insert into CSC_PROF_GROUPS_TL (
275 GROUP_ID,
276 GROUP_NAME,
277 DESCRIPTION,
278 CREATED_BY,
279 CREATION_DATE,
280 LAST_UPDATED_BY,
281 LAST_UPDATE_DATE,
282 LAST_UPDATE_LOGIN,
283 LANGUAGE,
284 SOURCE_LANG
285 ) select
286 B.GROUP_ID,
287 B.GROUP_NAME,
288 B.DESCRIPTION,
289 B.CREATED_BY,
290 B.CREATION_DATE,
291 B.LAST_UPDATED_BY,
292 B.LAST_UPDATE_DATE,
293 B.LAST_UPDATE_LOGIN,
294 L.LANGUAGE_CODE,
295 B.SOURCE_LANG
296 from CSC_PROF_GROUPS_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 CSC_PROF_GROUPS_TL T
302 where T.GROUP_ID = B.GROUP_ID
303 and T.LANGUAGE = L.LANGUAGE_CODE);
304 end ADD_LANGUAGE;
305
306
307 PROCEDURE TRANSLATE_ROW (
308 p_group_id IN NUMBER,
309 p_group_name IN VARCHAR2,
310 p_description IN VARCHAR2,
311 p_owner IN VARCHAR2 )
312 IS
313 BEGIN
314 update csc_prof_groups_tl
315 set group_name = p_group_name,
316 description = nvl(p_description, description),
317 last_update_date = sysdate,
318 last_updated_by = fnd_load_util.owner_id(p_owner),
319 last_update_login = 0,
320 source_lang = userenv('LANG')
321 where group_id = p_group_id
322 and userenv('LANG') IN (language, source_lang);
323
324 END TRANSLATE_ROW;
325
326
327 PROCEDURE LOAD_ROW(
328 p_GROUP_ID IN NUMBER,
329 p_LAST_UPDATED_BY IN NUMBER,
330 p_LAST_UPDATE_DATE IN DATE,
331 p_LAST_UPDATE_LOGIN IN NUMBER,
332 p_GROUP_NAME IN VARCHAR2,
333 p_GROUP_NAME_CODE IN VARCHAR2,
334 p_DESCRIPTION IN VARCHAR2,
335 p_START_DATE_ACTIVE IN DATE,
336 p_END_DATE_ACTIVE IN DATE,
337 p_USE_IN_CUSTOMER_DASHBOARD IN VARCHAR2,
338 p_PARTY_TYPE IN VARCHAR2,
339 p_SEEDED_FLAG IN VARCHAR2,
340 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
341 p_APPLICATION_ID IN NUMBER,
342 p_OWNER IN VARCHAR2 )
343 IS
344 l_user_id number := 0;
345 l_object_version_number number := 0;
346 l_group_id number := p_group_id;
347
348 BEGIN
349 Csc_Prof_Groups_Pkg.Update_Row(
350 p_GROUP_ID => p_group_id,
351 p_LAST_UPDATED_BY => p_last_updated_by,
352 p_LAST_UPDATE_DATE => p_last_update_date,
353 p_LAST_UPDATE_LOGIN => 0,
354 p_GROUP_NAME => p_group_name,
355 p_GROUP_NAME_CODE => p_group_name_code,
356 p_DESCRIPTION => p_description,
357 p_START_DATE_ACTIVE => to_date(p_start_date_active,'YYYY/MM/DD'),
358 p_END_DATE_ACTIVE => to_date(p_end_date_active,'YYYY/MM/DD'),
359 p_USE_IN_CUSTOMER_DASHBOARD => p_use_in_customer_dashboard,
360 p_PARTY_TYPE => p_party_type,
361 p_SEEDED_FLAG => p_seeded_flag,
362 px_OBJECT_VERSION_NUMBER => l_object_version_number,
363 p_APPLICATION_ID => p_application_id);
364
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 Csc_Prof_Groups_Pkg.Insert_Row(
368 px_GROUP_ID => l_group_id,
369 p_CREATED_BY => p_last_updated_by,
370 p_CREATION_DATE => p_last_update_date,
371 p_LAST_UPDATED_BY => p_last_updated_by,
372 p_LAST_UPDATE_DATE => p_last_update_date,
373 p_LAST_UPDATE_LOGIN => 0,
374 p_GROUP_NAME => p_group_name,
375 p_GROUP_NAME_CODE => p_group_name_code,
376 p_DESCRIPTION => p_description,
377 p_START_DATE_ACTIVE => to_date(p_start_date_active,'YYYY/MM/DD'),
378 p_END_DATE_ACTIVE => to_date(p_end_date_active,'YYYY/MM/DD'),
379 p_USE_IN_CUSTOMER_DASHBOARD => p_use_in_customer_dashboard,
380 p_PARTY_TYPE => p_party_type,
381 p_SEEDED_FLAG => p_seeded_flag,
382 x_OBJECT_VERSION_NUMBER => px_object_version_number,
383 p_APPLICATION_ID => p_application_id );
384
385 END LOAD_ROW;
386
387 End CSC_PROF_GROUPS_PKG;