[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_ROLE_TYPES_PKG
Source
1 PACKAGE BODY pa_project_role_types_pkg AS
2 /* $Header: PAXPRRTB.pls 115.12 2003/06/13 18:06:16 ramurthy ship $ */
3 -- INSERT ROW -----------------------------------------
4
5 PROCEDURE INSERT_ROW (
6 X_ROWID IN OUT NOCOPY VARCHAR2,
7 X_PROJECT_ROLE_ID IN NUMBER,
8 X_PROJECT_ROLE_TYPE IN VARCHAR2,
9 X_MEANING IN VARCHAR2,
10 X_QUERY_LABOR_COST_FLAG IN VARCHAR2,
11 X_START_DATE_ACTIVE IN DATE,
12 X_LAST_UPDATE_DATE IN DATE,
13 X_LAST_UPDATED_BY IN NUMBER,
14 X_CREATION_DATE IN DATE,
15 X_CREATED_BY IN NUMBER,
16 X_LAST_UPDATE_LOGIN IN NUMBER,
17 X_END_DATE_ACTIVE IN DATE,
18 X_DESCRIPTION IN VARCHAR2,
19 X_DEFAULT_MIN_JOB_LEVEL IN NUMBER,
20 X_DEFAULT_MAX_JOB_LEVEL IN NUMBER,
21 X_MENU_ID IN NUMBER,
22 X_DEFAULT_JOB_ID IN NUMBER,
23 X_FREEZE_RULES_FLAG IN VARCHAR2,
24 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
25 X_ATTRIBUTE1 IN VARCHAR2,
26 X_ATTRIBUTE2 IN VARCHAR2,
27 X_ATTRIBUTE3 IN VARCHAR2,
28 X_ATTRIBUTE4 IN VARCHAR2,
29 X_ATTRIBUTE5 IN VARCHAR2,
30 X_ATTRIBUTE6 IN VARCHAR2,
31 X_ATTRIBUTE7 IN VARCHAR2,
32 X_ATTRIBUTE8 IN VARCHAR2,
33 X_ATTRIBUTE9 IN VARCHAR2,
34 X_ATTRIBUTE10 IN VARCHAR2,
35 X_ATTRIBUTE11 IN VARCHAR2,
36 X_ATTRIBUTE12 IN VARCHAR2,
37 X_ATTRIBUTE13 IN VARCHAR2,
38 X_ATTRIBUTE14 IN VARCHAR2,
39 X_ATTRIBUTE15 IN VARCHAR2,
40 X_DEFAULT_ACCESS_LEVEL IN VARCHAR2,
41 X_ROLE_PARTY_CLASS IN VARCHAR2,
42 X_STATUS_LEVEL IN VARCHAR2
43 ) IS
44
45
46 cursor C is select ROWID from PA_PROJECT_ROLE_TYPES_B
47 where PROJECT_ROLE_ID = X_PROJECT_ROLE_ID
48 ;
49
50
51
52 BEGIN
53
54 insert into PA_PROJECT_ROLE_TYPES_B (
55 PROJECT_ROLE_TYPE,
56 QUERY_LABOR_COST_FLAG,
57 START_DATE_ACTIVE,
58 END_DATE_ACTIVE,
59 ATTRIBUTE_CATEGORY,
60 ATTRIBUTE1,
61 ATTRIBUTE2,
62 ATTRIBUTE3,
63 ATTRIBUTE4,
64 ATTRIBUTE5,
65 ATTRIBUTE6,
66 ATTRIBUTE7,
67 ATTRIBUTE8,
68 ATTRIBUTE9,
69 ATTRIBUTE10,
70 ATTRIBUTE11,
71 ATTRIBUTE12,
72 ATTRIBUTE13,
73 ATTRIBUTE14,
74 ATTRIBUTE15,
75 PROJECT_ROLE_ID,
76 MENU_ID,
77 DEFAULT_JOB_ID,
78 DEFAULT_MIN_JOB_LEVEL,
79 DEFAULT_MAX_JOB_LEVEL,
80 RECORD_VERSION_NUMBER,
81 FREEZE_RULES_FLAG,
82 DEFAULT_ACCESS_LEVEL,
83 ROLE_PARTY_CLASS,
84 STATUS_LEVEL,
85 CREATION_DATE,
86 CREATED_BY,
87 LAST_UPDATE_DATE,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_LOGIN
90 ) values (
91 X_PROJECT_ROLE_TYPE,
92 X_QUERY_LABOR_COST_FLAG,
93 X_START_DATE_ACTIVE,
94 X_END_DATE_ACTIVE,
95 X_ATTRIBUTE_CATEGORY,
96 X_ATTRIBUTE1,
97 X_ATTRIBUTE2,
98 X_ATTRIBUTE3,
99 X_ATTRIBUTE4,
100 X_ATTRIBUTE5,
101 X_ATTRIBUTE6,
102 X_ATTRIBUTE7,
103 X_ATTRIBUTE8,
104 X_ATTRIBUTE9,
105 X_ATTRIBUTE10,
106 X_ATTRIBUTE11,
107 X_ATTRIBUTE12,
108 X_ATTRIBUTE13,
109 X_ATTRIBUTE14,
110 X_ATTRIBUTE15,
111 X_PROJECT_ROLE_ID,
112 X_MENU_ID,
113 X_DEFAULT_JOB_ID,
114 X_DEFAULT_MIN_JOB_LEVEL,
115 X_DEFAULT_MAX_JOB_LEVEL,
116 1,
117 X_FREEZE_RULES_FLAG,
118 X_DEFAULT_ACCESS_LEVEL,
119 X_ROLE_PARTY_CLASS,
120 nvl(X_STATUS_LEVEL, 'SYSTEM'),
121 X_CREATION_DATE,
122 X_CREATED_BY,
123 X_LAST_UPDATE_DATE,
124 X_LAST_UPDATED_BY,
125 X_LAST_UPDATE_LOGIN
126 );
127
128 insert into PA_PROJECT_ROLE_TYPES_TL (
129 PROJECT_ROLE_ID,
130 MEANING,
131 DESCRIPTION,
132 CREATION_DATE,
133 CREATED_BY,
134 LAST_UPDATE_DATE,
135 LAST_UPDATED_BY,
136 LAST_UPDATE_LOGIN,
137 LANGUAGE,
138 SOURCE_LANG
139 ) select
140 X_PROJECT_ROLE_ID,
141 X_MEANING,
142 nvl(X_DESCRIPTION, x_meaning),
143 X_CREATION_DATE,
144 X_CREATED_BY,
145 X_LAST_UPDATE_DATE,
146 X_LAST_UPDATED_BY,
147 X_LAST_UPDATE_LOGIN,
148 L.LANGUAGE_CODE,
149 userenv('LANG')
150 from FND_LANGUAGES L
151 where L.INSTALLED_FLAG in ('I', 'B')
152 and not exists
153 (select NULL
154 from PA_PROJECT_ROLE_TYPES_TL T
155 where T.PROJECT_ROLE_ID = X_PROJECT_ROLE_ID
156 and T.LANGUAGE = L.LANGUAGE_CODE);
157
158
159 OPEN c;
160 FETCH c INTO x_rowid;
161 IF (c%NOTFOUND)
162 THEN
163 CLOSE c;
164 RAISE NO_DATA_FOUND;
165 END IF;
166 CLOSE c;
167
168 END INSERT_ROW;
169
170
171 -- LOCK ROW ------------------------------------------
172 PROCEDURE LOCK_ROW (
173 X_ROWID IN OUT NOCOPY VARCHAR2,
174 X_RECORD_VERSION_NUMBER IN NUMBER
175 ) IS
176
177 CURSOR c
178 IS
179 SELECT *
180 FROM pa_project_role_types_b
181 WHERE rowid = X_Rowid
182 FOR UPDATE OF project_role_id NOWAIT;
183
184 Recinfo c%ROWTYPE;
185
186 BEGIN
187
188 OPEN c;
189 FETCH c INTO Recinfo;
190 IF (c%NOTFOUND)
191 THEN
192 CLOSE c;
193 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
194 APP_EXCEPTION.Raise_Exception;
195 END IF;
196 CLOSE c;
197
198
199 IF ( ( (Recinfo.RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER)
200 OR ( (Recinfo.RECORD_VERSION_NUMBER IS NULL)
201 AND (X_RECORD_VERSION_NUMBER IS NULL)))
202 )
203 THEN
204 RETURN;
205 ELSE
206 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
207 APP_EXCEPTION.Raise_Exception;
208 END IF;
209
210 END LOCK_ROW;
211
212 -- record version number
213 -- rowid
214
215 -- UPDATE ROW -----------------------------------------
216 PROCEDURE UPDATE_ROW (
217 X_ROWID IN OUT NOCOPY VARCHAR2,
218 X_PROJECT_ROLE_ID IN NUMBER,
219 X_PROJECT_ROLE_TYPE IN VARCHAR2,
220 X_MEANING IN VARCHAR2,
221 X_QUERY_LABOR_COST_FLAG IN VARCHAR2,
222 X_START_DATE_ACTIVE IN DATE,
223 X_LAST_UPDATE_DATE IN DATE,
224 X_LAST_UPDATED_BY IN NUMBER,
225 X_CREATION_DATE IN DATE,
226 X_CREATED_BY IN NUMBER,
227 X_LAST_UPDATE_LOGIN IN NUMBER,
228 X_END_DATE_ACTIVE IN DATE,
229 X_DESCRIPTION IN VARCHAR2,
230 X_DEFAULT_MIN_JOB_LEVEL IN NUMBER,
231 X_DEFAULT_MAX_JOB_LEVEL IN NUMBER,
232 X_MENU_ID IN NUMBER,
233 X_DEFAULT_JOB_ID IN NUMBER,
234 X_FREEZE_RULES_FLAG IN VARCHAR2,
235 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
236 X_ATTRIBUTE1 IN VARCHAR2,
237 X_ATTRIBUTE2 IN VARCHAR2,
238 X_ATTRIBUTE3 IN VARCHAR2,
239 X_ATTRIBUTE4 IN VARCHAR2,
240 X_ATTRIBUTE5 IN VARCHAR2,
241 X_ATTRIBUTE6 IN VARCHAR2,
242 X_ATTRIBUTE7 IN VARCHAR2,
243 X_ATTRIBUTE8 IN VARCHAR2,
244 X_ATTRIBUTE9 IN VARCHAR2,
245 X_ATTRIBUTE10 IN VARCHAR2,
246 X_ATTRIBUTE11 IN VARCHAR2,
247 X_ATTRIBUTE12 IN VARCHAR2,
248 X_ATTRIBUTE13 IN VARCHAR2,
249 X_ATTRIBUTE14 IN VARCHAR2,
250 X_ATTRIBUTE15 IN VARCHAR2,
251 X_DEFAULT_ACCESS_LEVEL IN VARCHAR2,
252 X_ROLE_PARTY_CLASS IN VARCHAR2,
253 X_STATUS_LEVEL IN VARCHAR2
254 ) IS
255
256
257 BEGIN
258 --dbms_output.put_line('check 100');
259 update PA_PROJECT_ROLE_TYPES_B set
260 PROJECT_ROLE_TYPE = X_PROJECT_ROLE_TYPE,
261 QUERY_LABOR_COST_FLAG = X_QUERY_LABOR_COST_FLAG,
262 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
263 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
264 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
265 ATTRIBUTE1 = X_ATTRIBUTE1,
266 ATTRIBUTE2 = X_ATTRIBUTE2,
267 ATTRIBUTE3 = X_ATTRIBUTE3,
268 ATTRIBUTE4 = X_ATTRIBUTE4,
269 ATTRIBUTE5 = X_ATTRIBUTE5,
270 ATTRIBUTE6 = X_ATTRIBUTE6,
271 ATTRIBUTE7 = X_ATTRIBUTE7,
272 ATTRIBUTE8 = X_ATTRIBUTE8,
273 ATTRIBUTE9 = X_ATTRIBUTE9,
274 ATTRIBUTE10 = X_ATTRIBUTE10,
275 ATTRIBUTE11 = X_ATTRIBUTE11,
276 ATTRIBUTE12 = X_ATTRIBUTE12,
277 ATTRIBUTE13 = X_ATTRIBUTE13,
278 ATTRIBUTE14 = X_ATTRIBUTE14,
279 ATTRIBUTE15 = X_ATTRIBUTE15,
280 MENU_ID = X_MENU_ID,
281 DEFAULT_JOB_ID = X_DEFAULT_JOB_ID,
282 DEFAULT_MIN_JOB_LEVEL = X_DEFAULT_MIN_JOB_LEVEL,
283 DEFAULT_MAX_JOB_LEVEL = X_DEFAULT_MAX_JOB_LEVEL,
284 RECORD_VERSION_NUMBER = (RECORD_VERSION_NUMBER + 1),
285 FREEZE_RULES_FLAG = X_FREEZE_RULES_FLAG,
286 DEFAULT_ACCESS_LEVEL = X_DEFAULT_ACCESS_LEVEL,
287 ROLE_PARTY_CLASS = X_ROLE_PARTY_CLASS,
288 STATUS_LEVEL = nvl(X_STATUS_LEVEL, 'SYSTEM'),
289 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
290 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
291 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
292 WHERE project_role_id = X_project_role_id;
293
294 --dbms_output.put_line('check 101');
295 IF (SQL%NOTFOUND)
296 THEN
297 RAISE NO_DATA_FOUND;
298 END IF;
299 --dbms_output.put_line('check 102');
300 update PA_PROJECT_ROLE_TYPES_TL set
301 MEANING = X_MEANING,
302 DESCRIPTION = X_DESCRIPTION,
303 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
304 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
305 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
306 SOURCE_LANG = userenv('LANG')
307 where PROJECT_ROLE_ID = X_PROJECT_ROLE_ID
308 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
309 --dbms_output.put_line('check 103');
310 if (sql%notfound) then
311 raise no_data_found;
312 end if;
313 --dbms_output.put_line('check 104');
314
315 END UPDATE_ROW;
316
317
318 -- DELETE ROW -----------------------------------------
319 PROCEDURE DELETE_ROW (X_Rowid VARCHAR2)
320 IS
321 x_project_role_id NUMBER;
322
323 BEGIN
324
325 SELECT project_role_id INTO x_project_role_id
326 FROM pa_project_role_types_b
327 WHERE ROWID = x_rowid;
328
329
330 delete from PA_PROJECT_ROLE_TYPES_TL
331 where PROJECT_ROLE_ID = X_PROJECT_ROLE_ID;
332
333 if (sql%notfound) then
334 raise no_data_found;
335 end if;
336
337 delete from PA_PROJECT_ROLE_TYPES_B
338 where PROJECT_ROLE_ID = X_PROJECT_ROLE_ID;
339
340 if (sql%notfound) then
341 raise no_data_found;
342 end if;
343
344
345 END Delete_Row;
346
347
348 procedure ADD_LANGUAGE
349 is
350 begin
351 delete from PA_PROJECT_ROLE_TYPES_TL T
352 where not exists
353 (select NULL
354 from PA_PROJECT_ROLE_TYPES_B B
355 where B.PROJECT_ROLE_ID = T.PROJECT_ROLE_ID
356 );
357
358 update PA_PROJECT_ROLE_TYPES_TL T set (
359 MEANING,
360 DESCRIPTION
361 ) = (select
362 B.MEANING,
363 B.DESCRIPTION
364 from PA_PROJECT_ROLE_TYPES_TL B
365 where B.PROJECT_ROLE_ID = T.PROJECT_ROLE_ID
366 and B.LANGUAGE = T.SOURCE_LANG)
367 where (
368 T.PROJECT_ROLE_ID,
369 T.LANGUAGE
370 ) in (select
371 SUBT.PROJECT_ROLE_ID,
372 SUBT.LANGUAGE
373 from PA_PROJECT_ROLE_TYPES_TL SUBB, PA_PROJECT_ROLE_TYPES_TL SUBT
374 where SUBB.PROJECT_ROLE_ID = SUBT.PROJECT_ROLE_ID
375 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
376 and (SUBB.MEANING <> SUBT.MEANING
377 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
378 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
379 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
380 ));
381
382 insert into PA_PROJECT_ROLE_TYPES_TL (
383 PROJECT_ROLE_ID,
384 MEANING,
385 DESCRIPTION,
386 CREATION_DATE,
387 CREATED_BY,
388 LAST_UPDATE_DATE,
389 LAST_UPDATED_BY,
390 LAST_UPDATE_LOGIN,
391 LANGUAGE,
392 SOURCE_LANG
393 ) select
394 B.PROJECT_ROLE_ID,
395 B.MEANING,
396 B.DESCRIPTION,
397 B.CREATION_DATE,
398 B.CREATED_BY,
399 B.LAST_UPDATE_DATE,
400 B.LAST_UPDATED_BY,
401 B.LAST_UPDATE_LOGIN,
402 L.LANGUAGE_CODE,
403 B.SOURCE_LANG
404 from PA_PROJECT_ROLE_TYPES_TL B, FND_LANGUAGES L
405 where L.INSTALLED_FLAG in ('I', 'B')
406 and B.LANGUAGE = userenv('LANG')
407 and not exists
408 (select NULL
409 from PA_PROJECT_ROLE_TYPES_TL T
410 where T.PROJECT_ROLE_ID = B.PROJECT_ROLE_ID
411 and T.LANGUAGE = L.LANGUAGE_CODE);
412 end ADD_LANGUAGE;
413
414
415 END pa_project_role_types_pkg;