[Home] [Help]
PACKAGE BODY: APPS.PJI_MT_ROWSET_PKG
Source
1 PACKAGE BODY PJI_MT_ROWSET_PKG AS
2 /* $Header: PJIMTRSB.pls 120.1 2005/05/31 08:01:44 appldev $ */
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pji_mt_rowset_pkg';
5 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
6
7 /*==================================================================
8 This api locks the row in pji_mt_rowset_b before updating
9 ==================================================================*/
10
11 PROCEDURE LOCK_ROW (
12 p_rowset_code IN pji_mt_rowset_b.rowset_code%TYPE,
13 p_object_version_number IN pji_mt_rowset_b.object_version_number%TYPE
14 ) IS
15
16 CURSOR c IS
17 SELECT object_version_number
18 FROM pji_mt_rowset_b
19 WHERE rowset_code = p_rowset_code
20 FOR UPDATE OF rowset_code NOWAIT;
21
22 recinfo c%ROWTYPE;
23
24 BEGIN
25
26 OPEN c;
27 FETCH c INTO recinfo;
28 IF (c%NOTFOUND) THEN
29 CLOSE c;
30 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
31 app_exception.raise_exception;
32 END IF;
33 CLOSE c;
34
35 IF recinfo.object_version_number = p_object_version_number THEN
36 NULL;
37 ELSE
38 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
39 app_exception.raise_exception;
40 END IF;
41
42 RETURN;
43
44 END LOCK_ROW;
45
46 PROCEDURE DELETE_ROW (
47 p_rowset_code IN pji_mt_rowset_b.rowset_code%TYPE
48 ) IS
49 BEGIN
50 DELETE FROM pji_mt_rowset_TL
51 WHERE rowset_code = p_rowset_code;
52
53 IF (SQL%NOTFOUND) THEN
54 RAISE NO_DATA_FOUND;
55 END IF;
56
57 DELETE FROM pji_mt_rowset_B
58 WHERE rowset_code = p_rowset_code;
59
60 IF (SQL%NOTFOUND) THEN
61 RAISE NO_DATA_FOUND;
62 END IF;
63 END DELETE_ROW;
64
65
66 PROCEDURE Insert_Row (
67 X_Rowid IN OUT NOCOPY ROWID,
68 X_rowset_Code IN pji_mt_rowset_b.Rowset_Code%TYPE,
69 X_Object_Version_Number IN pji_mt_rowset_b.Object_Version_Number%TYPE,
70 X_Name IN pji_mt_rowset_Tl.Name%TYPE,
71 X_Description IN pji_mt_rowset_Tl.Description%TYPE,
72 X_Last_Update_Date IN pji_mt_rowset_b.Last_Update_Date%TYPE,
73 X_Last_Updated_by IN pji_mt_rowset_b.Last_Updated_by%TYPE,
74 X_Creation_Date IN pji_mt_rowset_b.Creation_Date%TYPE,
75 X_Created_By IN pji_mt_rowset_b.Created_By%TYPE,
76 X_Last_Update_Login IN pji_mt_rowset_b.Last_Update_Login%TYPE,
77 X_Return_Status OUT NOCOPY VARCHAR2,
78 X_Msg_Data OUT NOCOPY VARCHAR2,
79 X_Msg_Count OUT NOCOPY NUMBER
80 ) IS
81
82 CURSOR C IS SELECT ROWID FROM pji_mt_rowset_b
83 WHERE rowset_code = x_rowset_code;
84
85 l_return_status VARCHAR2(1) := NULL;
86 l_msg_count NUMBER := 0;
87 l_data VARCHAR2(2000) := NULL;
88 l_msg_data VARCHAR2(2000) := NULL;
89 l_msg_index_out NUMBER;
90
91 BEGIN
92
93 x_msg_count := 0;
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96
97 IF g_debug_mode = 'Y' THEN
98 pa_debug.set_curr_function( p_function => 'validate',
99 p_debug_mode => g_debug_mode );
100 END IF;
101
102 IF g_debug_mode = 'Y' THEN
103 pa_debug.g_err_stage:= 'Inserting record in pji_mt_rowset_B '||X_Rowset_Code;
104 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,
105 pa_fp_constants_pkg.g_debug_level3);
106 END IF;
107
108 INSERT INTO pji_mt_rowset_B
109 (
110 Rowset_Code
111 , Object_Version_Number
112 , Creation_Date
113 , Last_Update_Date
114 , Last_Updated_By
115 , Created_By
116 , Last_Update_Login )
117 VALUES
118 (
119 X_Rowset_Code
120 , X_Object_Version_Number
121 , X_Creation_Date
122 , X_Last_Update_Date
123 , X_Last_Updated_By
124 , X_Created_By
125 , X_Last_Update_Login
126 );
127
128 IF g_debug_mode = 'Y' THEN
129 pa_debug.g_err_stage:= 'Inserting record in pji_mt_rowset_tl '||X_Rowset_Code;
130 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level3);
131 END IF;
132
133 INSERT INTO pji_mt_rowset_tl
134 (
135 Rowset_Code,
136 Name,
137 Description,
138 Last_Update_Date,
139 Last_Updated_By,
140 Creation_Date,
141 Created_By,
142 Last_Update_Login,
143 LANGUAGE,
144 Source_Lang
145 )
146 SELECT
147 X_Rowset_Code,
148 X_Name,
149 X_Description,
150 X_Last_Update_Date,
151 X_Last_Updated_By,
152 X_Creation_Date,
153 X_Created_By,
154 X_Last_Update_Login,
155 L.Language_Code,
156 USERENV('Lang')
157 FROM Fnd_Languages L
158 WHERE L.Installed_Flag IN ('I', 'B')
159 AND NOT EXISTS
160 (SELECT NULL FROM pji_mt_rowset_tl T
161 WHERE T.rowset_code = X_Rowset_Code
162 AND T.LANGUAGE = L.Language_Code);
163
164 OPEN C;
165 FETCH C INTO X_ROWID;
166 IF (C%NOTFOUND) THEN
167
168 CLOSE C;
169 IF g_debug_mode = 'Y' THEN
170 pa_debug.g_err_stage:= 'Rowid could not be fetched after Inserting for '||X_Rowset_Code;
171 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level5);
172 END IF;
173 RAISE NO_DATA_FOUND;
174
175 END IF;
176 CLOSE C;
177
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 l_msg_count := FND_MSG_PUB.count_msg;
183
184 IF l_msg_count = 1 AND x_msg_data IS NULL THEN
185 PA_INTERFACE_UTILS_PUB.get_messages
186 (p_encoded => FND_API.G_TRUE
187 ,p_msg_index => 1
188 ,p_msg_count => l_msg_count
189 ,p_msg_data => l_msg_data
190 ,p_data => l_data
191 ,p_msg_index_out => l_msg_index_out);
192 x_msg_data := l_data;
193 x_msg_count := l_msg_count;
194 ELSE
195 x_msg_count := l_msg_count;
196 END IF;
197
198 IF g_debug_mode = 'Y' THEN
199 pa_debug.reset_curr_function;
200 END IF;
201
202 RETURN;
203
204 WHEN OTHERS THEN
205
206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207 x_msg_count := 1;
208 x_msg_data := SQLERRM;
209
210 FND_MSG_PUB.add_exc_msg
211 ( p_pkg_name => 'PJI_MT_ROWSET_PKG'
212 ,p_procedure_name => 'Insert Row'
213 ,p_error_text => x_msg_data);
214
215 IF g_debug_mode = 'Y' THEN
216 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
217 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,
218 pa_fp_constants_pkg.g_debug_level5);
219 pa_debug.reset_curr_function;
220 END IF;
221
222 RAISE;
223
224 END Insert_Row;
225
226
227 PROCEDURE Update_Row (
228 X_Rowset_Code IN pji_mt_rowset_b.Rowset_Code%TYPE,
229 X_Object_Version_Number IN pji_mt_rowset_b.Object_Version_Number%TYPE,
230 X_Name IN pji_mt_rowset_Tl.Name%TYPE,
231 X_Description IN pji_mt_rowset_Tl.Description%TYPE,
232 X_Last_Update_Date IN pji_mt_rowset_b.Last_Update_Date%TYPE,
233 X_Last_Updated_by IN pji_mt_rowset_b.Last_Updated_by%TYPE,
234 X_Last_Update_Login IN pji_mt_rowset_b.Last_Update_Login%TYPE,
235 X_Lock_Flag IN VARCHAR2 DEFAULT 'true',
236 X_Return_Status OUT NOCOPY VARCHAR2,
237 X_Msg_Data OUT NOCOPY VARCHAR2,
238 X_Msg_Count OUT NOCOPY NUMBER
239 )
240 IS
241
242 l_return_status VARCHAR2(1) := NULL;
243 l_msg_count NUMBER := 0;
244 l_data VARCHAR2(2000) := NULL;
245 l_msg_data VARCHAR2(2000) := NULL;
246 l_msg_index_out NUMBER;
247 l_object_version_number NUMBER := x_object_version_number;
248
249 BEGIN
250 NULL;
251
252 x_msg_count := 0;
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 IF g_debug_mode = 'Y' THEN
256 pa_debug.set_curr_function( p_function => 'validate',
257 p_debug_mode => g_debug_mode );
258 END IF;
259
260 IF g_debug_mode = 'Y' THEN
261 pa_debug.g_err_stage:= 'Updating pji_mt_rowset_b for'||X_Rowset_Code;
262 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level3);
263 END IF;
264
265 -- The lock row procedure need not be called when the update_row is called
266 -- from the lct. It should be called when update row is called from the
267 -- page
268
269 IF X_Lock_Flag = 'true' then
270 lock_row(X_Rowset_Code, l_object_version_number);
271 END IF;
272
273 l_object_version_number := l_object_version_number + 1;
274
275 UPDATE pji_mt_rowset_B
276 SET
277 Rowset_Code = X_Rowset_Code
278 , Object_Version_Number = l_object_version_number
279 , Last_Update_Date = X_Last_Update_Date
280 , Last_Updated_By = X_Last_Updated_By
281 , Last_Update_Login = X_Last_Update_Login
282 WHERE rowset_code = X_rowset_code;
283
284 IF (SQL%NOTFOUND) THEN
285 IF g_debug_mode = 'Y' THEN
286 pa_debug.g_err_stage:= 'NDF while updating pji_mt_rowset_B '||X_Rowset_Code;
287 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level5);
288 END IF;
289 RAISE NO_DATA_FOUND;
290 END IF;
291
292 IF g_debug_mode = 'Y' THEN
293 pa_debug.g_err_stage:= 'Updating pji_mt_rowset_tl for '||X_Rowset_Code;
294 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level3);
295 END IF;
296
297 UPDATE pji_mt_rowset_tl
298 SET Name = X_Name,
299 Description = X_Description,
300 Last_Update_Date = X_Last_Update_Date,
301 Last_Updated_By = X_Last_Updated_By,
302 Last_Update_Login = X_Last_Update_Login,
303 Source_Lang = USERENV('Lang')
304 WHERE Rowset_Code = X_Rowset_Code
305 AND USERENV('Lang') IN (LANGUAGE, Source_Lang);
306
307 IF (SQL%NOTFOUND) THEN
308 IF g_debug_mode = 'Y' THEN
309 pa_debug.g_err_stage:= 'NDF while updating pji_mt_rowset_T '||X_Rowset_Code;
310 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level5);
311 END IF;
312 RAISE NO_DATA_FOUND;
313 END IF;
314
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317
318 x_return_status := FND_API.G_RET_STS_ERROR;
319 l_msg_count := FND_MSG_PUB.count_msg;
320
321 IF l_msg_count = 1 AND x_msg_data IS NULL THEN
322 PA_INTERFACE_UTILS_PUB.get_messages
323 (p_encoded => FND_API.G_TRUE
324 ,p_msg_index => 1
325 ,p_msg_count => l_msg_count
326 ,p_msg_data => l_msg_data
327 ,p_data => l_data
328 ,p_msg_index_out => l_msg_index_out);
329 x_msg_data := l_data;
330 x_msg_count := l_msg_count;
331 ELSE
332 x_msg_count := l_msg_count;
333 END IF;
334
335 IF g_debug_mode = 'Y' THEN
336 pa_debug.reset_curr_function;
337 END IF;
338
339 RAISE NO_DATA_FOUND;
340
341 WHEN OTHERS THEN
342
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344 x_msg_count := 1;
345 x_msg_data := SQLERRM;
346
347 FND_MSG_PUB.add_exc_msg
348 ( p_pkg_name => 'PJI_MT_ROWSET_PKG'
349 ,p_procedure_name => 'UPDATE_ROW'
350 ,p_error_text => x_msg_data);
351
352 IF g_debug_mode = 'Y' THEN
353 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
354 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,
355 pa_fp_constants_pkg.g_debug_level5);
356 pa_debug.reset_curr_function;
357 END IF;
358
359 RAISE;
360 END Update_Row;
361
362
363
364
365
366 PROCEDURE Load_Row (
367 X_Rowset_Code IN pji_mt_rowset_b.Rowset_Code%TYPE,
368 X_Object_Version_Number IN pji_mt_rowset_b.Object_Version_Number%TYPE,
369 X_Name IN pji_mt_rowset_Tl.Name%TYPE,
370 X_Description IN pji_mt_rowset_Tl.Description%TYPE,
371 X_Owner IN VARCHAR2
372 )
373 IS
374 User_Id NUMBER := NULL;
375 X_ROWID VARCHAR2(64);
376 l_return_status VARCHAR2(1) := NULL;
377 l_msg_count NUMBER := 0;
378 l_data VARCHAR2(2000) := NULL;
379 l_msg_data VARCHAR2(2000) := NULL;
380 l_msg_index_out NUMBER;
381
382 BEGIN
383
384 g_debug_mode := 'N';
385
386 IF (X_Owner = 'SEED')THEN
387 User_Id := 1;
388 ELSE
389 User_Id := 0;
390 END IF;
391 PJI_MT_ROWSET_Pkg.Update_Row (
392 X_Rowset_Code => X_Rowset_Code ,
393 X_Object_Version_Number => X_Object_Version_Number ,
394 X_Name => X_Name ,
395 X_Description => X_Description ,
396 X_Last_Update_Date => SYSDATE ,
397 X_Last_Updated_By => User_Id ,
398 X_Last_Update_Login => 0 ,
399 X_Lock_Flag => 'false' ,
400 X_Return_Status => l_Return_Status ,
401 X_Msg_Data => l_Msg_Data ,
402 X_Msg_Count => l_Msg_Count );
403
404
405 EXCEPTION
406 WHEN NO_DATA_FOUND THEN
407
408 PJI_MT_ROWSET_Pkg.Insert_Row (
409 X_Rowid => X_Rowid ,
410 X_Rowset_Code => X_Rowset_Code ,
411 X_Object_Version_Number => X_Object_Version_Number ,
412 X_Name => X_Name ,
413 X_Description => X_Description ,
414 X_Creation_Date => SYSDATE ,
415 X_Created_By => User_Id ,
416 X_Last_Update_Date => SYSDATE ,
417 X_Last_Updated_By => User_Id ,
418 X_Last_Update_Login => 0 ,
419 X_Return_Status => l_Return_Status ,
420 X_Msg_Data => l_Msg_Data ,
421 X_Msg_Count => l_Msg_Count );
422
423 WHEN OTHERS THEN
424
425 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
426 l_msg_count := 1;
427 l_msg_data := SQLERRM;
428
429 FND_MSG_PUB.add_exc_msg
430 ( p_pkg_name => 'PJI_MT_ROWSET_PKG'
431 ,p_procedure_name => 'UPDATE_ROW'
432 ,p_error_text => l_msg_data);
433
434 IF g_debug_mode = 'Y' THEN
435 pa_debug.g_err_stage:= 'Unexpected Error'||l_msg_data;
436 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,
437 pa_fp_constants_pkg.g_debug_level5);
438 pa_debug.reset_curr_function;
439 END IF;
440
441 RAISE;
442 END Load_Row;
443
444
445 PROCEDURE Add_Language
446 IS
447 BEGIN
448
449 DELETE FROM pji_mt_rowset_tl T
450 WHERE NOT EXISTS
451 (SELECT NULL
452 FROM pji_mt_rowset_B B
453 WHERE B.rowset_code = T.rowset_code
454 );
455
456 UPDATE pji_mt_rowset_tl T SET (
457 NAME,
458 DESCRIPTION
459 ) = (SELECT
460 B.NAME,
461 B.DESCRIPTION
462 FROM pji_mt_rowset_tl B
463 WHERE B.rowset_code = T.rowset_code
464 AND B.LANGUAGE = T.SOURCE_LANG)
465 WHERE (
466 T.rowset_code,
467 T.LANGUAGE
468 ) IN (SELECT
469 SUBT.rowset_code,
470 SUBT.LANGUAGE
471 FROM pji_mt_rowset_tl SUBB, pji_mt_rowset_tl SUBT
472 WHERE SUBB.rowset_code = SUBT.rowset_code
473 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
474 AND (SUBB.NAME <> SUBT.NAME
475 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
476 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
477 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
478 ));
479
480 INSERT INTO pji_mt_rowset_tl(
481 LAST_UPDATE_LOGIN,
482 CREATION_DATE,
483 CREATED_BY,
484 LAST_UPDATE_DATE,
485 LAST_UPDATED_BY,
486 rowset_code,
487 NAME,
488 DESCRIPTION,
489 LANGUAGE,
490 SOURCE_LANG
491 )SELECT
492 B.LAST_UPDATE_LOGIN,
493 B.CREATION_DATE,
494 B.CREATED_BY,
495 B.LAST_UPDATE_DATE,
496 B.LAST_UPDATED_BY,
497 B.rowset_code,
498 B.NAME,
499 B.DESCRIPTION,
500 L.LANGUAGE_CODE,
501 B.SOURCE_LANG
502 FROM pji_mt_rowset_tl B, FND_LANGUAGES L
503 WHERE L.INSTALLED_FLAG IN ('I', 'B')
504 AND B.LANGUAGE = USERENV('LANG')
505 AND NOT EXISTS
506 (SELECT NULL
507 FROM pji_mt_rowset_tl T
508 WHERE T.rowset_code = B.rowset_code
509 AND T.LANGUAGE = L.LANGUAGE_CODE);
510 END ADD_LANGUAGE;
511
512
513 PROCEDURE TRANSLATE_ROW (
514 X_rowset_code IN pji_mt_rowset_b.rowset_code%TYPE,
515 X_OWNER IN VARCHAR2 ,
516 X_NAME IN pji_mt_rowset_TL.NAME%TYPE,
517 X_DESCRIPTION IN pji_mt_rowset_TL.DESCRIPTION%TYPE
518 )IS
519 BEGIN
520
521 UPDATE pji_mt_rowset_tl SET
522 NAME = X_NAME,
523 DESCRIPTION = X_DESCRIPTION,
524 LAST_UPDATE_DATE = SYSDATE,
525 LAST_UPDATED_BY = DECODE(X_OWNER, 'SEED', 1, 0),
526 LAST_UPDATE_LOGIN = 0,
527 SOURCE_LANG = USERENV('LANG')
528 WHERE rowset_code = x_rowset_code
529 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG) ;
530
531 IF (SQL%NOTFOUND) THEN
532 RAISE NO_DATA_FOUND;
533 END IF;
534
535 END TRANSLATE_ROW;
536
537
538 END PJI_MT_ROWSET_PKG;