[Home] [Help]
PACKAGE BODY: APPS.PJI_MT_MEASURES_PKG
Source
1 package body PJI_MT_MEASURES_PKG as
2 /* $Header: PJIMTMDB.pls 120.1 2005/05/31 07:58:41 appldev $ */
3
4
5 -- -----------------------------------------------------------------------
6 -- -----------------------------------------------------------------------
7
8 g_module_name VARCHAR2(100) := 'pa.plsql.pji_mt_measures_pkg';
9 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10
11 -- -----------------------------------------------------------------------
12 -- This api locks the row in Pji_Mt_Measures_B before updating
13 -- -----------------------------------------------------------------------
14
15 procedure LOCK_ROW (
16 p_measure_id IN pji_mt_measures_b.measure_id%TYPE,
17 p_OBJECT_VERSION_NUMBER IN pji_mt_measures_b.OBJECT_VERSION_NUMBER%TYPE
18 ) is
19
20 cursor c is
21 select OBJECT_VERSION_NUMBER
22 from PJI_MT_MEASURES_B
23 where MEASURE_ID = p_measure_id
24 for update of measure_id nowait;
25
26 recinfo c%rowtype;
27
28 begin
29
30 open c;
31 fetch c into recinfo;
32 if (c%notfound) then
33 close c;
34 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
35 app_exception.raise_exception;
36 end if;
37 close c;
38
39 if recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER then
40 null;
41 else
42 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
43 app_exception.raise_exception;
44 end if;
45
46 return;
47
48 end LOCK_ROW;
49
50
51 -- -----------------------------------------------------------------------
52
53 procedure DELETE_ROW (
54 p_measure_id IN pji_mt_measures_b.measure_id%TYPE
55 ) is
56
57
58 begin
59 delete from PJI_MT_MEASURES_TL
60 where measure_id = p_measure_id;
61
62 if (sql%notfound) then
63 raise no_data_found;
64 end if;
65
66 delete from PJI_MT_MEASURES_B
67 where measure_id = p_measure_id;
68
69 if (sql%notfound) then
70 raise no_data_found;
71 end if;
72
73 end DELETE_ROW;
74
75
76 -- -----------------------------------------------------------------------
77
78 procedure INSERT_ROW(
79
80 X_rowid IN OUT NOCOPY rowid,
81
82 X_measure_id IN pji_mt_measures_b.measure_id%type,
83
84 X_measure_set_code IN pji_mt_measures_b.measure_set_code%type,
85 X_measure_code IN pji_mt_measures_b.measure_code%type,
86 X_xtd_type IN pji_mt_measures_b.xtd_type%type,
87 X_pl_sql_api IN pji_mt_measures_b.pl_sql_api%type,
88 X_object_version_number IN pji_mt_measures_b.object_version_number%type,
89
90 X_name IN pji_mt_measures_tl.name%type,
91 X_description IN pji_mt_measures_tl.description%type,
92
93 X_last_update_date IN pji_mt_measures_b.last_update_date%Type,
94 X_last_updated_by IN pji_mt_measures_b.last_updated_by%Type,
95 X_creation_date IN pji_mt_measures_b.creation_date%Type,
96 X_created_by IN pji_mt_measures_b.created_by%Type,
97 X_last_update_Login IN pji_mt_measures_b.last_update_Login%Type,
98
99 X_return_status OUT NOCOPY VARCHAR2,
100 X_msg_data OUT NOCOPY VARCHAR2,
101 X_msg_count OUT NOCOPY NUMBER
102
103 ) is
104
105 l_measure_id pji_mt_measures_b.MEASURE_ID%type;
106
107 cursor C is select ROWID from pji_mt_measures_b
108 where MEASURE_ID = l_measure_id;
109
110 l_return_status VARCHAR2(1) := NULL;
111 l_msg_count NUMBER := 0;
112 l_data VARCHAR2(2000) := NULL;
113 l_msg_data VARCHAR2(2000) := NULL;
114 l_msg_index_out NUMBER;
115
116
117 begin
118
119 x_msg_count := 0;
120 x_return_status := FND_API.G_RET_STS_SUCCESS;
121
122 IF g_debug_mode = 'Y' THEN
123 pa_debug.set_curr_function( p_function => 'validate',
124 p_debug_mode => g_debug_mode );
125 END IF;
126
127 select nvl(X_MEASURE_ID,PJI_MT_MEASURES_S.nextval)
128 into l_measure_id
129 from dual;
130
131 IF g_debug_mode = 'Y' THEN
132 pa_debug.g_err_stage:= 'Inserting record in pji_mt_measures_b'||to_char(l_measure_id);
133 pa_debug.write(g_module_name,pa_debug.g_err_stage,
134 pa_fp_constants_pkg.g_debug_level3);
135 END IF;
136
137 INSERT INTO Pji_Mt_Measures_B
138 (
139 measure_id,
140 measure_set_code,
141 measure_code,
142 xtd_type,
143 pl_sql_api,
144 object_version_number,
145
146 last_update_date,
147 last_updated_by,
148 creation_date,
149 created_by,
150 last_update_login
151 )
152 VALUES
153 (
154 l_measure_id,
155 X_measure_set_code,
156 X_measure_code,
157 X_xtd_type,
158 X_pl_sql_api,
159 X_object_version_number,
160
161 X_last_update_date,
162 X_last_updated_by,
163 X_creation_date,
164 X_created_by,
165 X_last_update_login
166 );
167
168
169 IF g_debug_mode = 'Y' THEN
170 pa_debug.g_err_stage:= 'Inserting record in pji_mt_Measures_tl'||to_char(l_measure_id);
171 pa_debug.write(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level3);
172
173 END IF;
174
175 INSERT INTO pji_mt_measures_tl
176 (
177 measure_id,
178
179 name,
180 description,
181
182 last_update_date,
183 last_updated_by,
184 creation_date,
185 created_by,
186 last_update_login,
187
188 language,
189 source_lang
190 )
191 SELECT
192 l_measure_id,
193
194 X_name,
195 X_description,
196
197 X_last_update_date,
198 X_last_updated_by,
199 X_creation_date,
200 X_created_by,
201 X_last_update_login,
202
203 L.Language_Code,
204 Userenv('Lang')
205
206 FROM Fnd_Languages L
207 WHERE L.Installed_Flag In ('I', 'B')
208 AND NOT EXISTS
209 (SELECT NULL FROM Pji_Mt_Measures_Tl T
210 WHERE T.Measure_Id = L_Measure_Id
211 AND T.Language = L.Language_Code);
212
213 OPEN C;
214 FETCH C INTO X_ROWID;
215 IF (C%NOTFOUND) THEN
216
217 CLOSE C;
218 IF g_debug_mode = 'Y' THEN
219 pa_debug.g_err_stage:= 'Rowid could not be fetched after Inserting for'||to_char(l_measure_id);
220 pa_debug.write(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level5);
221 END IF;
222 RAISE NO_DATA_FOUND;
223
224 END IF;
225 CLOSE C;
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 l_msg_count := FND_MSG_PUB.count_msg;
232
233 IF l_msg_count = 1 and x_msg_data IS NULL THEN
234 PA_INTERFACE_UTILS_PUB.get_messages
235 (p_encoded => FND_API.G_TRUE
236 ,p_msg_index => 1
237 ,p_msg_count => l_msg_count
238 ,p_msg_data => l_msg_data
239 ,p_data => l_data
240 ,p_msg_index_out => l_msg_index_out);
241 x_msg_data := l_data;
242 x_msg_count := l_msg_count;
243 ELSE
244 x_msg_count := l_msg_count;
245 END IF;
246
247 IF g_debug_mode = 'Y' THEN
248 pa_debug.reset_curr_function;
249 END IF;
250
251 RETURN;
252
253 WHEN others THEN
254
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256 x_msg_count := 1;
257 x_msg_data := SQLERRM;
258
259 FND_MSG_PUB.add_exc_msg
260 ( p_pkg_name => 'PJI_MT_MEASURES_PKG'
261 ,p_procedure_name => 'Insert Row'
262 ,p_error_text => x_msg_data);
263
264 IF g_debug_mode = 'Y' THEN
265 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
266 pa_debug.write(g_module_name,pa_debug.g_err_stage,
267 pa_fp_constants_pkg.g_debug_level5);
268 pa_debug.reset_curr_function;
269 END IF;
270
271 RAISE;
272
273 END INSERT_ROW;
274
275
276 -- -----------------------------------------------------------------------
277
278 procedure UPDATE_ROW (
279
280 X_measure_id IN pji_mt_measures_b.measure_id%type,
281 X_measure_set_code IN pji_mt_measures_b.measure_set_code%type,
282 X_measure_code IN pji_mt_measures_b.measure_code%type,
283 X_xtd_type IN pji_mt_measures_b.xtd_type%type,
284 X_pl_sql_api IN pji_mt_measures_b.pl_sql_api%type,
285 X_object_version_number IN pji_mt_measures_b.object_version_number%type,
286
287 X_name IN pji_mt_measures_tl.name%type,
288 X_description IN pji_mt_measures_tl.description%type,
289
290 X_last_update_date IN pji_mt_measures_b.last_update_date%Type,
291 X_last_updated_by IN pji_mt_measures_b.last_updated_by%Type,
292 X_last_update_login IN pji_mt_measures_b.last_update_login%Type,
293
294 X_return_status OUT NOCOPY VARCHAR2,
295 X_msg_data OUT NOCOPY VARCHAR2,
296 X_msg_count OUT NOCOPY NUMBER
297
298 ) IS
299
300
301 l_return_status VARCHAR2(1) := NULL;
302 l_msg_count NUMBER := 0;
303 l_data VARCHAR2(2000) := NULL;
304 l_msg_data VARCHAR2(2000) := NULL;
305 l_msg_index_out NUMBER;
306
307
308 begin
309
310 x_msg_count := 0;
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312
313 IF g_debug_mode = 'Y' THEN
314 pa_debug.set_curr_function( p_function => 'validate',
315 p_debug_mode => g_debug_mode );
316 END IF;
317
318 IF g_debug_mode = 'Y' THEN
319 pa_debug.g_err_stage:= 'Updating Pji_Mt_Measures_B for'||to_char(X_measure_id);
320 pa_debug.write(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level3);
321 END IF;
322
323 UPDATE Pji_Mt_Measures_B
324 SET
325
326 measure_set_code = X_measure_set_code,
327 measure_code = X_measure_code,
328 xtd_type = X_xtd_type,
329 pl_sql_api = X_pl_sql_api,
330 object_version_number = X_object_version_number
331
332 where Measure_Id = X_Measure_Id;
333
334 IF (SQL%NOTFOUND) THEN
335 IF g_debug_mode = 'Y' THEN
336 pa_debug.g_err_stage:= 'NDF while updating Pji_Mt_Measures_B'||to_char(X_measure_id);
337 pa_debug.write(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level5);
338 END IF;
339 RAISE NO_DATA_FOUND;
340 END IF;
341
342 IF g_debug_mode = 'Y' THEN
343 pa_debug.g_err_stage:= 'Updating Pji_Mt_Measures_Tl for'||to_char(X_measure_id);
344 pa_debug.write(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level3);
345 END IF;
346
347 UPDATE Pji_Mt_Measures_Tl
348 SET
349 Name = X_Name,
350 Description = X_Description,
351 Last_Update_Date = X_Last_Update_Date,
352 Last_Updated_By = X_Last_Updated_By,
353 Last_Update_Login = X_Last_Update_Login,
354 Source_Lang = Userenv('Lang')
355 WHERE Measure_Id = X_Measure_Id
356 AND Userenv('Lang') In (Language, Source_Lang);
357
358 IF (SQL%NOTFOUND) THEN
359 IF g_debug_mode = 'Y' THEN
360 pa_debug.g_err_stage:= 'NDF while updating Pa_Spread_Curves_T'||to_char(X_measure_id);
361 pa_debug.write(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level5);
362 END IF;
363 RAISE NO_DATA_FOUND;
364 END IF;
365
366 EXCEPTION
367 WHEN NO_DATA_FOUND THEN
368
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 l_msg_count := FND_MSG_PUB.count_msg;
371
372 IF l_msg_count = 1 and x_msg_data IS NULL THEN
373 PA_INTERFACE_UTILS_PUB.get_messages
374 (p_encoded => FND_API.G_TRUE
375 ,p_msg_index => 1
376 ,p_msg_count => l_msg_count
377 ,p_msg_data => l_msg_data
378 ,p_data => l_data
379 ,p_msg_index_out => l_msg_index_out);
380 x_msg_data := l_data;
381 x_msg_count := l_msg_count;
382 ELSE
383 x_msg_count := l_msg_count;
384 END IF;
385
386 IF g_debug_mode = 'Y' THEN
387 pa_debug.reset_curr_function;
388 END IF;
389
390 RAISE NO_DATA_FOUND;
391
392 WHEN others THEN
393
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 x_msg_count := 1;
396 x_msg_data := SQLERRM;
397
398 FND_MSG_PUB.add_exc_msg
399 ( p_pkg_name => 'PJI_MT_MEASURES_PKG'
400 ,p_procedure_name => 'UPDATE_ROW'
401 ,p_error_text => x_msg_data);
402
403 IF g_debug_mode = 'Y' THEN
404 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
405 pa_debug.write(g_module_name,pa_debug.g_err_stage,
406 pa_fp_constants_pkg.g_debug_level5);
407 pa_debug.reset_curr_function;
408 END IF;
409
410 RAISE;
411
412 END UPDATE_ROW;
413
414 -- -----------------------------------------------------------------------
415
416 procedure LOAD_ROW (
417
418 X_measure_id IN pji_mt_measures_b.measure_id%type,
419
420 X_measure_set_code IN pji_mt_measures_b.measure_set_code%type,
421 X_measure_code IN pji_mt_measures_b.measure_code%type,
422 X_xtd_type IN pji_mt_measures_b.xtd_type%type,
423 X_pl_sql_api IN pji_mt_measures_b.pl_sql_api%type,
424 X_object_version_number IN pji_mt_measures_b.object_version_number%type,
425
426 X_name IN pji_mt_measures_tl.name%type,
427 X_description IN pji_mt_measures_tl.description%type,
428
429 X_owner IN VARCHAR2
430 ) IS
431
432
433 User_Id NUMBER := Null;
434 X_ROWID VARCHAR2(64);
435 l_return_status VARCHAR2(1) := NULL;
436 l_msg_count NUMBER := 0;
437 l_data VARCHAR2(2000) := NULL;
438 l_msg_data VARCHAR2(2000) := NULL;
439 l_msg_index_out NUMBER;
440
441
442 begin
443
444 g_debug_mode := 'N';
445
446 IF (X_Owner = 'SEED')THEN
447 User_Id := 1;
448 ELSE
449 User_Id := 0;
450 END IF;
451
452 Pji_Mt_Measures_Pkg.Update_Row (
453
454 X_Measure_Id => X_Measure_Id,
455
456 X_measure_set_code => X_measure_set_code,
457 X_measure_code => X_measure_code,
458 X_xtd_type => X_xtd_type,
459 X_pl_sql_api => X_pl_sql_api,
460 X_object_version_number => X_object_version_number,
461
462 X_Name => X_Name,
463 X_Description => X_Description,
464
465 X_Last_Update_Date => Sysdate,
466 X_Last_Updated_By => User_Id,
467 X_Last_Update_Login => 0,
468
469 X_Return_Status => l_Return_Status,
470 X_Msg_Data => l_Msg_Data,
471 X_Msg_Count => l_Msg_Count
472 );
473
474
475 EXCEPTION
476 WHEN no_data_found then
477
478 Pji_Mt_Measures_Pkg.Insert_Row (
479
480 X_Rowid => X_Rowid,
481
482 X_Measure_Id => X_Measure_Id,
483
484 X_measure_set_code => X_measure_set_code,
485 X_measure_code => X_measure_code,
486 X_xtd_type => X_xtd_type,
487 X_pl_sql_api => X_pl_sql_api,
488 X_object_version_number => X_object_version_number,
489
490 X_Name => X_Name,
491 X_Description => X_Description,
492
493 X_Creation_Date => Sysdate,
494 X_Created_By => User_Id,
495 X_Last_Update_Date => Sysdate,
496 X_Last_Updated_By => User_Id,
497 X_Last_Update_Login => 0,
498
499 X_Return_Status => l_Return_Status,
500 X_Msg_Data => l_Msg_Data,
501 X_Msg_Count => l_Msg_Count
502
503
504 );
505
506 WHEN others THEN
507
508 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 l_msg_count := 1;
510 l_msg_data := SQLERRM;
511
512 FND_MSG_PUB.add_exc_msg
513 ( p_pkg_name => 'PJI_MT_MEASURES_PKG'
514 ,p_procedure_name => 'UPDATE_ROW'
515 ,p_error_text => l_msg_data);
516
517 IF g_debug_mode = 'Y' THEN
518 pa_debug.g_err_stage:= 'Unexpected Error'||l_msg_data;
519 pa_debug.write(g_module_name,pa_debug.g_err_stage,
520 pa_fp_constants_pkg.g_debug_level5);
521 pa_debug.reset_curr_function;
522 END IF;
523
524 RAISE;
525
526 END LOAD_ROW;
527
528
529 -- -----------------------------------------------------------------------
530
531 procedure ADD_LANGUAGE
532
533 IS
534
535 begin
536
537 delete from PJI_MT_MEASURES_TL T
538 where not exists
539 (select NULL
540 from PJI_MT_MEASURES_B B
541 where B.MEASURE_ID = T.MEASURE_ID
542 );
543
544 update PJI_MT_MEASURES_TL T set (
545 NAME,
546 DESCRIPTION
547 ) = (select
548 B.NAME,
549 B.DESCRIPTION
550 from PJI_MT_MEASURES_TL B
551 where B.MEASURE_ID = T.MEASURE_ID
552 and B.LANGUAGE = T.SOURCE_LANG)
553 where (
554 T.MEASURE_ID,
555 T.LANGUAGE
556 ) in (select
557 SUBT.MEASURE_ID,
558 SUBT.LANGUAGE
559 from PJI_MT_MEASURES_TL SUBB, PJI_MT_MEASURES_TL SUBT
560 where SUBB.MEASURE_ID = SUBT.MEASURE_ID
561 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
562 and (SUBB.NAME <> SUBT.NAME
563 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
564 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
565 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
566 ));
567
568 insert into PJI_MT_MEASURES_TL (
569
570 MEASURE_ID,
571
572 NAME,
573 DESCRIPTION,
574
575 LANGUAGE,
576 SOURCE_LANG,
577
578 LAST_UPDATE_LOGIN,
579 CREATION_DATE,
580 CREATED_BY,
581 LAST_UPDATE_DATE,
582 LAST_UPDATED_BY
583
584 )select
585 B.MEASURE_ID,
586
587 B.NAME,
588 B.DESCRIPTION,
589
590 L.LANGUAGE_CODE,
591 B.SOURCE_LANG,
592
593 B.LAST_UPDATE_LOGIN,
594 B.CREATION_DATE,
595 B.CREATED_BY,
596 B.LAST_UPDATE_DATE,
597 B.LAST_UPDATED_BY
598
599 from PJI_MT_MEASURES_TL B, FND_LANGUAGES L
600 where L.INSTALLED_FLAG in ('I', 'B')
601 and B.LANGUAGE = userenv('LANG')
602 and not exists
603 (select NULL
604 from PJI_MT_MEASURES_TL T
605 where T.MEASURE_ID = B.MEASURE_ID
606 and T.LANGUAGE = L.LANGUAGE_CODE);
607
608 end ADD_LANGUAGE;
609
610
611 -- -----------------------------------------------------------------------
612
613 procedure TRANSLATE_ROW (
614
615 X_MEASURE_ID in PJI_MT_MEASURES_B.MEASURE_ID%TYPE,
616
617 X_NAME in PJI_MT_MEASURES_TL.NAME%TYPE,
618 X_DESCRIPTION in PJI_MT_MEASURES_TL.DESCRIPTION%TYPE,
619
620 X_OWNER in VARCHAR2
621
622 ) is
623
624
625 begin
626
627 g_debug_mode := 'N';
628
629
630 update PJI_MT_MEASURES_TL set
631 NAME = X_NAME,
632 DESCRIPTION = X_DESCRIPTION,
633 LAST_UPDATE_DATE = sysdate,
634 LAST_UPDATED_BY = decode(X_OWNER, 'SEED', 1, 0),
635 LAST_UPDATE_LOGIN = 0,
636 SOURCE_LANG = USERENV('LANG')
637 where MEASURE_ID = X_MEASURE_ID
638 and USERENV('LANG') IN (LANGUAGE, SOURCE_LANG) ;
639
640 if (sql%notfound) then
641 raise no_data_found;
642 end if;
643
644 end TRANSLATE_ROW;
645
646 -- -----------------------------------------------------------------------
647
648 end PJI_MT_MEASURES_PKG;