[Home] [Help]
PACKAGE BODY: APPS.PA_WORK_TYPES_PKG
Source
1 package body PA_WORK_TYPES_PKG as
2 /* $Header: PAWKTYPB.pls 120.1 2005/08/11 10:08:12 eyefimov noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_WORK_TYPE_ID in NUMBER,
6 X_ATTRIBUTE3 in VARCHAR2,
7 X_ATTRIBUTE4 in VARCHAR2,
8 X_ATTRIBUTE5 in VARCHAR2,
9 X_ATTRIBUTE6 in VARCHAR2,
10 X_ATTRIBUTE7 in VARCHAR2,
11 X_ATTRIBUTE8 in VARCHAR2,
12 X_ATTRIBUTE9 in VARCHAR2,
13 X_ATTRIBUTE10 in VARCHAR2,
14 X_ATTRIBUTE11 in VARCHAR2,
15 X_ATTRIBUTE12 in VARCHAR2,
16 X_ATTRIBUTE13 in VARCHAR2,
17 X_ATTRIBUTE14 in VARCHAR2,
18 X_ATTRIBUTE15 in VARCHAR2,
19 X_BILLABLE_CAPITALIZABLE_FLAG in VARCHAR2,
20 X_REDUCE_CAPACITY_FLAG in VARCHAR2,
21 X_RES_UTILIZATION_PERCENTAGE in NUMBER,
22 X_ORG_UTILIZATION_PERCENTAGE in NUMBER,
23 X_START_DATE_ACTIVE in DATE,
24 X_END_DATE_ACTIVE in DATE,
25 X_RES_UTIL_CATEGORY_ID in NUMBER,
26 X_ORG_UTIL_CATEGORY_ID in NUMBER,
27 X_ATTRIBUTE_CATEGORY in VARCHAR2,
28 X_ATTRIBUTE1 in VARCHAR2,
29 X_ATTRIBUTE2 in VARCHAR2,
30 X_NAME in VARCHAR2,
31 X_DESCRIPTION in VARCHAR2,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER,
37 X_TRAINING_FLAG in VARCHAR2,
38 X_TP_AMT_TYPE_CODE in VARCHAR2,
39 X_UNASSIGNED_FLAG in VARCHAR2
40 ) is
41
42 cursor C is select ROWID from PA_WORK_TYPES_B
43 where WORK_TYPE_ID = X_WORK_TYPE_ID
44 ;
45 /** Added thses variables for PJI changes **/
46 l_pji_rowid VARCHAR2(1000);
47 l_pji_event_id NUMBER;
48
49 begin
50
51 insert into PA_WORK_TYPES_B (
52 ATTRIBUTE3,
53 ATTRIBUTE4,
54 ATTRIBUTE5,
55 ATTRIBUTE6,
56 ATTRIBUTE7,
57 ATTRIBUTE8,
58 ATTRIBUTE9,
59 ATTRIBUTE10,
60 ATTRIBUTE11,
61 ATTRIBUTE12,
62 ATTRIBUTE13,
63 ATTRIBUTE14,
64 ATTRIBUTE15,
65 WORK_TYPE_ID,
66 BILLABLE_CAPITALIZABLE_FLAG,
67 REDUCE_CAPACITY_FLAG,
68 RES_UTILIZATION_PERCENTAGE,
69 ORG_UTILIZATION_PERCENTAGE,
70 START_DATE_ACTIVE,
71 END_DATE_ACTIVE,
72 RES_UTIL_CATEGORY_ID,
73 ORG_UTIL_CATEGORY_ID,
74 ATTRIBUTE_CATEGORY,
75 ATTRIBUTE1,
76 ATTRIBUTE2,
77 CREATION_DATE,
78 CREATED_BY,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 LAST_UPDATE_LOGIN,
82 TRAINING_FLAG,
83 TP_AMT_TYPE_CODE ,
84 UNASSIGNED_FLAG
85 ) values (
86 X_ATTRIBUTE3,
87 X_ATTRIBUTE4,
88 X_ATTRIBUTE5,
89 X_ATTRIBUTE6,
90 X_ATTRIBUTE7,
91 X_ATTRIBUTE8,
92 X_ATTRIBUTE9,
93 X_ATTRIBUTE10,
94 X_ATTRIBUTE11,
95 X_ATTRIBUTE12,
96 X_ATTRIBUTE13,
97 X_ATTRIBUTE14,
98 X_ATTRIBUTE15,
99 X_WORK_TYPE_ID,
100 X_BILLABLE_CAPITALIZABLE_FLAG,
101 X_REDUCE_CAPACITY_FLAG,
102 X_RES_UTILIZATION_PERCENTAGE,
103 X_ORG_UTILIZATION_PERCENTAGE,
104 X_START_DATE_ACTIVE,
105 X_END_DATE_ACTIVE,
106 X_RES_UTIL_CATEGORY_ID,
107 X_ORG_UTIL_CATEGORY_ID,
108 X_ATTRIBUTE_CATEGORY,
109 X_ATTRIBUTE1,
110 X_ATTRIBUTE2,
111 X_CREATION_DATE,
112 X_CREATED_BY,
113 X_LAST_UPDATE_DATE,
114 X_LAST_UPDATED_BY,
115 X_LAST_UPDATE_LOGIN,
116 X_TRAINING_FLAG,
117 X_TP_AMT_TYPE_CODE ,
118 X_UNASSIGNED_FLAG
119 );
120
121 /** If PJI is installed then we need to insert record into PA_PJI_PROJ_EVENTS_LOG table
122 ** for every insert/update/delete in pa_work_types_b **/
123 IF ((sql%rowcount > 0) AND (NVL(PA_INSTALL.is_pji_licensed(),'N')='Y')) THEN
124 l_pji_rowid := null;
125 l_pji_event_id := null;
126
127 PA_PJI_PROJ_EVENTS_LOG_PKG.Insert_Row(
128 X_ROW_ID => l_pji_rowid
129 ,X_EVENT_ID => l_pji_event_id
130 ,X_EVENT_TYPE => 'Work Types'
131 ,X_EVENT_OBJECT => X_WORK_TYPE_ID
132 ,X_OPERATION_TYPE => 'I' -- insert mode
133 ,X_STATUS => 'X' --NULL
134 ,X_ATTRIBUTE_CATEGORY => NULL
135 ,X_ATTRIBUTE1 => X_RES_UTILIZATION_PERCENTAGE
136 ,X_ATTRIBUTE2 => X_ORG_UTILIZATION_PERCENTAGE
137 ,X_ATTRIBUTE3 => X_BILLABLE_CAPITALIZABLE_FLAG
138 ,X_ATTRIBUTE4 => X_REDUCE_CAPACITY_FLAG
139 ,X_ATTRIBUTE5 => X_TRAINING_FLAG
140 ,X_ATTRIBUTE6 => X_UNASSIGNED_FLAG
141 ,X_ATTRIBUTE7 => X_TP_AMT_TYPE_CODE
142 ,X_ATTRIBUTE8 => to_char(X_START_DATE_ACTIVE,'YYYY/MM/DD')/* Bug fix:2428599 */
143 ,X_ATTRIBUTE9 => to_char(X_END_DATE_ACTIVE ,'YYYY/MM/DD')/* Bug fix:2428599 */
144 ,X_ATTRIBUTE10 => X_RES_UTIL_CATEGORY_ID
145 ,X_ATTRIBUTE11 => X_ORG_UTIL_CATEGORY_ID
146 ,X_ATTRIBUTE12 => NULL
147 ,X_ATTRIBUTE13 => NULL
148 ,X_ATTRIBUTE14 => NULL
149 ,X_ATTRIBUTE15 => NULL
150 ,X_ATTRIBUTE16 => NULL
151 ,X_ATTRIBUTE17 => NULL
152 ,X_ATTRIBUTE18 => NULL
153 ,X_ATTRIBUTE19 => NULL
154 ,X_ATTRIBUTE20 => NULL
155 );
156 End If;
157 /** End of PJI changes **/
158
159 insert into PA_WORK_TYPES_TL (
160 WORK_TYPE_ID,
161 NAME,
162 DESCRIPTION,
163 CREATION_DATE,
164 CREATED_BY,
165 LAST_UPDATE_DATE,
166 LAST_UPDATED_BY,
167 LAST_UPDATE_LOGIN,
168 LANGUAGE,
169 SOURCE_LANG
170 ) select
171 X_WORK_TYPE_ID,
172 X_NAME,
173 X_DESCRIPTION,
174 X_CREATION_DATE,
175 X_CREATED_BY,
176 X_LAST_UPDATE_DATE,
177 X_LAST_UPDATED_BY,
178 X_LAST_UPDATE_LOGIN,
179 L.LANGUAGE_CODE,
180 userenv('LANG')
181 from FND_LANGUAGES L
182 where L.INSTALLED_FLAG in ('I', 'B')
183 and not exists
184 (select NULL
185 from PA_WORK_TYPES_TL T
186 where T.WORK_TYPE_ID = X_WORK_TYPE_ID
187 and T.LANGUAGE = L.LANGUAGE_CODE);
188
189 open c;
190 fetch c into X_ROWID;
191 if (c%notfound) then
192 close c;
193 raise no_data_found;
194 end if;
195 close c;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 X_ROWID := Null;
200 RAISE;
201
202 end INSERT_ROW;
203
204 procedure LOCK_ROW (
205 X_WORK_TYPE_ID in NUMBER,
206 X_ATTRIBUTE3 in VARCHAR2,
207 X_ATTRIBUTE4 in VARCHAR2,
208 X_ATTRIBUTE5 in VARCHAR2,
209 X_ATTRIBUTE6 in VARCHAR2,
210 X_ATTRIBUTE7 in VARCHAR2,
211 X_ATTRIBUTE8 in VARCHAR2,
212 X_ATTRIBUTE9 in VARCHAR2,
213 X_ATTRIBUTE10 in VARCHAR2,
214 X_ATTRIBUTE11 in VARCHAR2,
215 X_ATTRIBUTE12 in VARCHAR2,
216 X_ATTRIBUTE13 in VARCHAR2,
217 X_ATTRIBUTE14 in VARCHAR2,
218 X_ATTRIBUTE15 in VARCHAR2,
219 X_BILLABLE_CAPITALIZABLE_FLAG in VARCHAR2,
220 X_REDUCE_CAPACITY_FLAG in VARCHAR2,
221 X_RES_UTILIZATION_PERCENTAGE in NUMBER,
222 X_ORG_UTILIZATION_PERCENTAGE in NUMBER,
223 X_START_DATE_ACTIVE in DATE,
224 X_END_DATE_ACTIVE in DATE,
225 X_RES_UTIL_CATEGORY_ID in NUMBER,
226 X_ORG_UTIL_CATEGORY_ID in NUMBER,
227 X_ATTRIBUTE_CATEGORY in VARCHAR2,
228 X_ATTRIBUTE1 in VARCHAR2,
229 X_ATTRIBUTE2 in VARCHAR2,
230 X_NAME in VARCHAR2,
231 X_DESCRIPTION in VARCHAR2,
232 X_TRAINING_FLAG in VARCHAR2,
233 X_TP_AMT_TYPE_CODE in VARCHAR2,
234 X_UNASSIGNED_FLAG in VARCHAR2
235 ) is
236
237 cursor c is select
238 ATTRIBUTE3,
239 ATTRIBUTE4,
240 ATTRIBUTE5,
241 ATTRIBUTE6,
242 ATTRIBUTE7,
243 ATTRIBUTE8,
244 ATTRIBUTE9,
245 ATTRIBUTE10,
246 ATTRIBUTE11,
247 ATTRIBUTE12,
248 ATTRIBUTE13,
249 ATTRIBUTE14,
250 ATTRIBUTE15,
251 BILLABLE_CAPITALIZABLE_FLAG,
252 REDUCE_CAPACITY_FLAG,
253 RES_UTILIZATION_PERCENTAGE,
254 ORG_UTILIZATION_PERCENTAGE,
255 START_DATE_ACTIVE,
256 END_DATE_ACTIVE,
257 RES_UTIL_CATEGORY_ID,
258 ORG_UTIL_CATEGORY_ID,
259 ATTRIBUTE_CATEGORY,
260 ATTRIBUTE1,
261 ATTRIBUTE2,
262 TRAINING_FLAG,
263 TP_AMT_TYPE_CODE,
264 UNASSIGNED_FLAG
265 from PA_WORK_TYPES_B
266 where WORK_TYPE_ID = X_WORK_TYPE_ID
267 for update of WORK_TYPE_ID nowait;
268 recinfo c%rowtype;
269
270 cursor c1 is select
271 NAME,
272 DESCRIPTION,
273 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
274 from PA_WORK_TYPES_TL
275 where WORK_TYPE_ID = X_WORK_TYPE_ID
276 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
277 for update of WORK_TYPE_ID nowait;
278
279 begin
280
281 open c;
282 fetch c into recinfo;
283 if (c%notfound) then
284 close c;
285 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
286 app_exception.raise_exception;
287 end if;
288 close c;
289 if ( ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
290 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
291 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
292 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
293 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
294 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
295 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
296 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
297 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
298 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
299 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
300 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
301 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
302 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
303 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
304 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
305 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
306 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
307 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
308 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
309 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
310 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
311 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
312 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
313 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
314 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
315 AND (recinfo.BILLABLE_CAPITALIZABLE_FLAG = X_BILLABLE_CAPITALIZABLE_FLAG)
316 AND (recinfo.REDUCE_CAPACITY_FLAG = X_REDUCE_CAPACITY_FLAG)
317 AND (recinfo.RES_UTILIZATION_PERCENTAGE = X_RES_UTILIZATION_PERCENTAGE)
318 AND (recinfo.ORG_UTILIZATION_PERCENTAGE = X_ORG_UTILIZATION_PERCENTAGE)
319 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
320 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
321 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
322 AND ((recinfo.RES_UTIL_CATEGORY_ID = X_RES_UTIL_CATEGORY_ID)
323 OR ((recinfo.RES_UTIL_CATEGORY_ID is null) AND (X_RES_UTIL_CATEGORY_ID is null)))
324 AND ((recinfo.ORG_UTIL_CATEGORY_ID = X_ORG_UTIL_CATEGORY_ID)
325 OR ((recinfo.ORG_UTIL_CATEGORY_ID is null) AND (X_ORG_UTIL_CATEGORY_ID is null)))
326 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
327 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
328 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
329 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
330 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
331 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
332 AND ((recinfo.TRAINING_FLAG = X_TRAINING_FLAG)
333 OR ((recinfo.TRAINING_FLAG is null) AND (X_TRAINING_FLAG is null)))
334 AND ((recinfo.TP_AMT_TYPE_CODE = X_TP_AMT_TYPE_CODE)
335 OR ((recinfo.TP_AMT_TYPE_CODE is null) AND (X_TP_AMT_TYPE_CODE is null)))
336 AND ((recinfo.UNASSIGNED_FLAG = X_UNASSIGNED_FLAG )
337 OR ((recinfo.UNASSIGNED_FLAG is null) AND (X_UNASSIGNED_FLAG is null)))
338 ) then
339
340 null;
341
342 else
343
344 null;
345 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
346 app_exception.raise_exception;
347
348 end if;
349
350 for tlinfo in c1 loop
351 if (tlinfo.BASELANG = 'Y') then
352 if ( (tlinfo.NAME = X_NAME)
353 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
354 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
355 ) then
356 null;
357 else
358 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
359 app_exception.raise_exception;
360 end if;
361 end if;
362 end loop;
363 return;
364
365 end LOCK_ROW;
366
367 procedure UPDATE_ROW (
368 X_WORK_TYPE_ID in NUMBER,
369 X_ATTRIBUTE3 in VARCHAR2,
370 X_ATTRIBUTE4 in VARCHAR2,
371 X_ATTRIBUTE5 in VARCHAR2,
372 X_ATTRIBUTE6 in VARCHAR2,
373 X_ATTRIBUTE7 in VARCHAR2,
374 X_ATTRIBUTE8 in VARCHAR2,
375 X_ATTRIBUTE9 in VARCHAR2,
376 X_ATTRIBUTE10 in VARCHAR2,
377 X_ATTRIBUTE11 in VARCHAR2,
378 X_ATTRIBUTE12 in VARCHAR2,
379 X_ATTRIBUTE13 in VARCHAR2,
380 X_ATTRIBUTE14 in VARCHAR2,
381 X_ATTRIBUTE15 in VARCHAR2,
382 X_BILLABLE_CAPITALIZABLE_FLAG in VARCHAR2,
383 X_REDUCE_CAPACITY_FLAG in VARCHAR2,
384 X_RES_UTILIZATION_PERCENTAGE in NUMBER,
385 X_ORG_UTILIZATION_PERCENTAGE in NUMBER,
386 X_START_DATE_ACTIVE in DATE,
387 X_END_DATE_ACTIVE in DATE,
388 X_RES_UTIL_CATEGORY_ID in NUMBER,
389 X_ORG_UTIL_CATEGORY_ID in NUMBER,
390 X_ATTRIBUTE_CATEGORY in VARCHAR2,
391 X_ATTRIBUTE1 in VARCHAR2,
392 X_ATTRIBUTE2 in VARCHAR2,
393 X_NAME in VARCHAR2,
394 X_DESCRIPTION in VARCHAR2,
395 X_LAST_UPDATE_DATE in DATE,
396 X_LAST_UPDATED_BY in NUMBER,
397 X_LAST_UPDATE_LOGIN in NUMBER,
398 X_TRAINING_FLAG in VARCHAR2,
399 X_TP_AMT_TYPE_CODE in VARCHAR2,
400 X_UNASSIGNED_FLAG in VARCHAR2
401 ) is
402
403 /** Added thses variables for PJI changes **/
404 l_pji_rowid VARCHAR2(1000);
405 l_pji_event_id NUMBER;
406
407 cursor old_worktype_values IS
408 SELECT
409 RES_UTILIZATION_PERCENTAGE
410 ,ORG_UTILIZATION_PERCENTAGE
411 ,BILLABLE_CAPITALIZABLE_FLAG
412 ,REDUCE_CAPACITY_FLAG
413 ,TRAINING_FLAG
414 ,UNASSIGNED_FLAG
415 ,TP_AMT_TYPE_CODE
416 ,START_DATE_ACTIVE
417 ,END_DATE_ACTIVE
418 ,RES_UTIL_CATEGORY_ID
419 ,ORG_UTIL_CATEGORY_ID
420 FROM pa_work_types_b
421 WHERE work_type_id = X_WORK_TYPE_ID;
422
423 l_wt_old old_worktype_values%ROWTYPE;
424 l_sql_rowcount number;
425
426 BEGIN
427
428 OPEN old_worktype_values;
429 FETCH old_worktype_values INTO l_wt_old;
430 IF old_worktype_values%FOUND then
431
432 update PA_WORK_TYPES_B set
433 ATTRIBUTE3 = X_ATTRIBUTE3,
434 ATTRIBUTE4 = X_ATTRIBUTE4,
435 ATTRIBUTE5 = X_ATTRIBUTE5,
436 ATTRIBUTE6 = X_ATTRIBUTE6,
437 ATTRIBUTE7 = X_ATTRIBUTE7,
438 ATTRIBUTE8 = X_ATTRIBUTE8,
439 ATTRIBUTE9 = X_ATTRIBUTE9,
440 ATTRIBUTE10 = X_ATTRIBUTE10,
441 ATTRIBUTE11 = X_ATTRIBUTE11,
442 ATTRIBUTE12 = X_ATTRIBUTE12,
443 ATTRIBUTE13 = X_ATTRIBUTE13,
444 ATTRIBUTE14 = X_ATTRIBUTE14,
445 ATTRIBUTE15 = X_ATTRIBUTE15,
446 BILLABLE_CAPITALIZABLE_FLAG = X_BILLABLE_CAPITALIZABLE_FLAG,
447 REDUCE_CAPACITY_FLAG = X_REDUCE_CAPACITY_FLAG,
448 RES_UTILIZATION_PERCENTAGE = X_RES_UTILIZATION_PERCENTAGE,
449 ORG_UTILIZATION_PERCENTAGE = X_ORG_UTILIZATION_PERCENTAGE,
450 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
451 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
452 RES_UTIL_CATEGORY_ID = X_RES_UTIL_CATEGORY_ID,
453 ORG_UTIL_CATEGORY_ID = X_ORG_UTIL_CATEGORY_ID,
454 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
455 ATTRIBUTE1 = X_ATTRIBUTE1,
456 ATTRIBUTE2 = X_ATTRIBUTE2,
457 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
458 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
459 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
460 TRAINING_FLAG = X_TRAINING_FLAG,
461 TP_AMT_TYPE_CODE = X_TP_AMT_TYPE_CODE,
462 UNASSIGNED_FLAG = X_UNASSIGNED_FLAG
463 where WORK_TYPE_ID = X_WORK_TYPE_ID;
464
465 l_sql_rowcount := sql%rowcount;
466
467 /** If PJI is installed then we need to insert record into PA_PJI_PROJ_EVENTS_LOG table
468 ** for every insert/update/delete in pa_work_types_b **/
469 IF ((l_sql_rowcount > 0) AND (NVL(PA_INSTALL.is_pji_licensed(),'N')='Y')) THEN
470 l_pji_rowid := null;
471 l_pji_event_id := null;
472
473 PA_PJI_PROJ_EVENTS_LOG_PKG.Insert_Row(
474 X_ROW_ID => l_pji_rowid
475 ,X_EVENT_ID => l_pji_event_id
476 ,X_EVENT_TYPE => 'Work Types'
477 ,X_EVENT_OBJECT => X_WORK_TYPE_ID
478 ,X_OPERATION_TYPE => 'U' -- update mode
479 ,X_STATUS => 'X' --NULL
480 ,X_ATTRIBUTE_CATEGORY => NULL
481 ,X_ATTRIBUTE1 => l_wt_old.RES_UTILIZATION_PERCENTAGE
482 ,X_ATTRIBUTE2 => l_wt_old.ORG_UTILIZATION_PERCENTAGE
483 ,X_ATTRIBUTE3 => l_wt_old.BILLABLE_CAPITALIZABLE_FLAG
484 ,X_ATTRIBUTE4 => l_wt_old.REDUCE_CAPACITY_FLAG
485 ,X_ATTRIBUTE5 => l_wt_old.TRAINING_FLAG
486 ,X_ATTRIBUTE6 => l_wt_old.UNASSIGNED_FLAG
487 ,X_ATTRIBUTE7 => l_wt_old.TP_AMT_TYPE_CODE
488 ,X_ATTRIBUTE8 => to_char(l_wt_old.START_DATE_ACTIVE,'YYYY/MM/DD') /* Bug fix:2428599 */
489 ,X_ATTRIBUTE9 => to_char(l_wt_old.END_DATE_ACTIVE,'YYYY/MM/DD') /* Bug fix:2428599 */
490 ,X_ATTRIBUTE10 => l_wt_old.RES_UTIL_CATEGORY_ID
491 ,X_ATTRIBUTE11 => l_wt_old.ORG_UTIL_CATEGORY_ID
492 ,X_ATTRIBUTE12 => NULL
493 ,X_ATTRIBUTE13 => NULL
494 ,X_ATTRIBUTE14 => NULL
495 ,X_ATTRIBUTE15 => NULL
496 ,X_ATTRIBUTE16 => NULL
497 ,X_ATTRIBUTE17 => NULL
498 ,X_ATTRIBUTE18 => NULL
499 ,X_ATTRIBUTE19 => NULL
500 ,X_ATTRIBUTE20 => NULL
501 );
502 End If;
503
504 END IF; -- end of fetch
505 CLOSE old_worktype_values;
506 /** End of PJI changes **/
507
508 if (l_sql_rowcount <= 0 ) then
509 raise no_data_found;
510 end if;
511
512
513
514 update PA_WORK_TYPES_TL set
515 NAME = X_NAME,
516 DESCRIPTION = X_DESCRIPTION,
517 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
518 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
519 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
520 SOURCE_LANG = userenv('LANG')
521 where WORK_TYPE_ID = X_WORK_TYPE_ID
522 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
523
524 if (sql%notfound) then
525 raise no_data_found;
526 end if;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 If old_worktype_values%isopen then
531 close old_worktype_values;
532 End if;
533 RAISE;
534
535 end UPDATE_ROW;
536
537 procedure DELETE_ROW (
538 X_WORK_TYPE_ID in NUMBER
539 ) is
540
541 /** Added thses variables for PJI changes **/
542 l_pji_rowid VARCHAR2(1000);
543 l_pji_event_id NUMBER;
544
545 cursor old_worktype_values IS
546 SELECT
547 RES_UTILIZATION_PERCENTAGE
548 ,ORG_UTILIZATION_PERCENTAGE
549 ,BILLABLE_CAPITALIZABLE_FLAG
550 ,REDUCE_CAPACITY_FLAG
551 ,TRAINING_FLAG
552 ,UNASSIGNED_FLAG
553 ,TP_AMT_TYPE_CODE
554 ,START_DATE_ACTIVE
555 ,END_DATE_ACTIVE
556 ,RES_UTIL_CATEGORY_ID
557 ,ORG_UTIL_CATEGORY_ID
558 FROM pa_work_types_b
559 WHERE work_type_id = X_WORK_TYPE_ID;
560
561 l_wt_old old_worktype_values%ROWTYPE;
562 l_sql_rowcount Number ;
563
564 BEGIN
565
566 OPEN old_worktype_values;
567 FETCH old_worktype_values INTO l_wt_old;
568 IF old_worktype_values%FOUND then
569
570 delete from PA_WORK_TYPES_TL
571 where WORK_TYPE_ID = X_WORK_TYPE_ID;
572
573 l_sql_rowcount := sql%rowcount ;
574
575 /** If PJI is installed then we need to insert record into PA_PJI_PROJ_EVENTS_LOG table
576 ** for every insert/update/delete in pa_work_types_b **/
577 IF ((l_sql_rowcount > 0) AND (NVL(PA_INSTALL.is_pji_licensed(),'N')='Y')) THEN
578 l_pji_rowid := null;
579 l_pji_event_id := null;
580
581 PA_PJI_PROJ_EVENTS_LOG_PKG.Insert_Row(
582 X_ROW_ID => l_pji_rowid
583 ,X_EVENT_ID => l_pji_event_id
584 ,X_EVENT_TYPE => 'Work Types'
585 ,X_EVENT_OBJECT => X_WORK_TYPE_ID
586 ,X_OPERATION_TYPE => 'D' -- delete mode
587 ,X_STATUS => 'X' --NULL
588 ,X_ATTRIBUTE_CATEGORY => NULL
589 ,X_ATTRIBUTE1 => l_wt_old.RES_UTILIZATION_PERCENTAGE
590 ,X_ATTRIBUTE2 => l_wt_old.ORG_UTILIZATION_PERCENTAGE
591 ,X_ATTRIBUTE3 => l_wt_old.BILLABLE_CAPITALIZABLE_FLAG
592 ,X_ATTRIBUTE4 => l_wt_old.REDUCE_CAPACITY_FLAG
593 ,X_ATTRIBUTE5 => l_wt_old.TRAINING_FLAG
594 ,X_ATTRIBUTE6 => l_wt_old.UNASSIGNED_FLAG
595 ,X_ATTRIBUTE7 => l_wt_old.TP_AMT_TYPE_CODE
596 ,X_ATTRIBUTE8 => to_char(l_wt_old.START_DATE_ACTIVE,'YYYY/MM/DD')/* Bug fix:2428599 */
597 ,X_ATTRIBUTE9 => to_char(l_wt_old.END_DATE_ACTIVE,'YYYY/MM/DD') /* Bug fix:2428599 */
598 ,X_ATTRIBUTE10 => l_wt_old.RES_UTIL_CATEGORY_ID
599 ,X_ATTRIBUTE11 => l_wt_old.ORG_UTIL_CATEGORY_ID
600 ,X_ATTRIBUTE12 => NULL
601 ,X_ATTRIBUTE13 => NULL
602 ,X_ATTRIBUTE14 => NULL
603 ,X_ATTRIBUTE15 => NULL
604 ,X_ATTRIBUTE16 => NULL
605 ,X_ATTRIBUTE17 => NULL
606 ,X_ATTRIBUTE18 => NULL
607 ,X_ATTRIBUTE19 => NULL
608 ,X_ATTRIBUTE20 => NULL
609 );
610 End If;
611 End IF; -- end of curosr fetch
612 CLOSE old_worktype_values;
613 /** End of PJI changes **/
614
615 if (l_sql_rowcount <= 0 ) then
616 raise no_data_found;
617 end if;
618
619 delete from PA_WORK_TYPES_B
620 where WORK_TYPE_ID = X_WORK_TYPE_ID;
621
622 if (sql%notfound) then
623 raise no_data_found;
624 end if;
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 If old_worktype_values%isopen then
629 close old_worktype_values;
630 End if;
631 RAISE;
632
633 end DELETE_ROW;
634
635 procedure ADD_LANGUAGE
636 is
637
638 begin
639
640 delete from PA_WORK_TYPES_TL T
641 where not exists
642 (select NULL
643 from PA_WORK_TYPES_B B
644 where B.WORK_TYPE_ID = T.WORK_TYPE_ID
645 );
646
647 update PA_WORK_TYPES_TL T set (
648 NAME,
649 DESCRIPTION
650 ) = (select
651 B.NAME,
652 B.DESCRIPTION
653 from PA_WORK_TYPES_TL B
654 where B.WORK_TYPE_ID = T.WORK_TYPE_ID
655 and B.LANGUAGE = T.SOURCE_LANG)
656 where (
657 T.WORK_TYPE_ID,
658 T.LANGUAGE
659 ) in (select
660 SUBT.WORK_TYPE_ID,
661 SUBT.LANGUAGE
662 from PA_WORK_TYPES_TL SUBB, PA_WORK_TYPES_TL SUBT
663 where SUBB.WORK_TYPE_ID = SUBT.WORK_TYPE_ID
664 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
665 and (SUBB.NAME <> SUBT.NAME
666 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
667 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
668 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
669 ));
670
671 insert into PA_WORK_TYPES_TL (
672 WORK_TYPE_ID,
673 NAME,
674 DESCRIPTION,
675 CREATION_DATE,
676 CREATED_BY,
677 LAST_UPDATE_DATE,
678 LAST_UPDATED_BY,
679 LAST_UPDATE_LOGIN,
680 LANGUAGE,
681 SOURCE_LANG
682 ) select
683 B.WORK_TYPE_ID,
684 B.NAME,
685 B.DESCRIPTION,
686 B.CREATION_DATE,
687 B.CREATED_BY,
688 B.LAST_UPDATE_DATE,
689 B.LAST_UPDATED_BY,
690 B.LAST_UPDATE_LOGIN,
691 L.LANGUAGE_CODE,
692 B.SOURCE_LANG
693 from PA_WORK_TYPES_TL B, FND_LANGUAGES L
694 where L.INSTALLED_FLAG in ('I', 'B')
695 and B.LANGUAGE = userenv('LANG')
696 and not exists
697 (select NULL
698 from PA_WORK_TYPES_TL T
699 where T.WORK_TYPE_ID = B.WORK_TYPE_ID
700 and T.LANGUAGE = L.LANGUAGE_CODE);
701 end ADD_LANGUAGE;
702
703 end PA_WORK_TYPES_PKG;