[Home] [Help]
PACKAGE BODY: APPS.PJI_MT_ROWSET_DET_PKG
Source
1 PACKAGE BODY PJI_MT_ROWSET_DET_PKG AS
2 /* $Header: PJIMTRDB.pls 120.1 2005/05/31 08:01:34 appldev $ */
3
4 g_module_name VARCHAR2(100) := 'pa.plsql.pji_mt_rowset_det_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_det before updating
9 ==================================================================*/
10
11 PROCEDURE LOCK_ROW (
12 p_measure_set_code IN pji_mt_rowset_det.measure_set_code%TYPE,
13 p_rowset_code IN pji_mt_rowset_det.rowset_code%TYPE,
14 p_object_version_number IN pji_mt_rowset_det.object_version_number%TYPE
15 ) IS
16
17 CURSOR c IS
18 SELECT object_version_number
19 FROM pji_mt_rowset_det
20 WHERE measure_set_code = p_measure_set_code
21 AND rowset_code = p_rowset_code
22 FOR UPDATE OF measure_set_code, rowset_code NOWAIT;
23
24 recinfo c%ROWTYPE;
25
26 BEGIN
27
28 OPEN c;
29 FETCH c INTO recinfo;
30 IF (c%NOTFOUND) THEN
31 CLOSE c;
32 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
33 app_exception.raise_exception;
34 END IF;
35 CLOSE c;
36
37 IF recinfo.object_version_number = p_object_version_number THEN
38 NULL;
39 ELSE
40 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
41 app_exception.raise_exception;
42 END IF;
43
44 RETURN;
45
46 END LOCK_ROW;
47
48
49
50 PROCEDURE DELETE_ROW (
51 p_measure_set_code IN pji_mt_rowset_det.measure_set_code%TYPE,
52 p_rowset_code IN pji_mt_rowset_det.rowset_code%TYPE
53 ) IS
54 BEGIN
55 DELETE FROM pji_mt_rowset_det
56 WHERE measure_set_code = p_measure_set_code
57 AND rowset_code = p_rowset_code;
58
59 IF (SQL%NOTFOUND) THEN
60 RAISE NO_DATA_FOUND;
61 END IF;
62
63 END DELETE_ROW;
64
65
66 PROCEDURE Insert_Row (
67 X_Rowid IN OUT NOCOPY ROWID,
68 X_measure_set_code IN pji_mt_rowset_det.measure_set_code%TYPE,
69 X_rowset_code IN pji_mt_rowset_det.rowset_code%TYPE,
70 X_Object_Version_Number IN pji_mt_rowset_det.Object_Version_Number%TYPE,
71 X_display_order IN pji_mt_rowset_det.display_order%TYPE, --Bug 3798976
72 X_Last_Update_Date IN pji_mt_rowset_det.Last_Update_Date%TYPE,
73 X_Last_Updated_by IN pji_mt_rowset_det.Last_Updated_by%TYPE,
74 X_Creation_Date IN pji_mt_rowset_det.Creation_Date%TYPE,
75 X_Created_By IN pji_mt_rowset_det.Created_By%TYPE,
76 X_Last_Update_Login IN pji_mt_rowset_det.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_det
83 WHERE measure_set_code = x_measure_set_code
84 AND rowset_code = x_rowset_code;
85
86 l_return_status VARCHAR2(1) := NULL;
87 l_msg_count NUMBER := 0;
88 l_data VARCHAR2(2000) := NULL;
89 l_msg_data VARCHAR2(2000) := NULL;
90 l_msg_index_out NUMBER;
91
92 BEGIN
93
94 x_msg_count := 0;
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
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_det, measure_code= '||X_measure_set_code||', rowset_code= '||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_det
109 (
110 measure_set_code
111 , rowset_code
112 , Object_Version_Number
113 , display_order --Bug 3798976
114 , Creation_Date
115 , Last_Update_Date
116 , Last_Updated_By
117 , Created_By
118 , Last_Update_Login )
119 VALUES
120 (
121 X_measure_set_code
122 , X_rowset_code
123 , X_Object_Version_Number
124 , X_display_order --Bug 3798976
125 , X_Creation_Date
126 , X_Last_Update_Date
127 , X_Last_Updated_By
128 , X_Created_By
129 , X_Last_Update_Login
130 );
131
132 IF g_debug_mode = 'Y' THEN
133 pa_debug.g_err_stage:= 'Inserting record in pji_mt_rowset_det '||X_measure_set_code;
134 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level3);
135 END IF;
136
137 OPEN C;
138 FETCH C INTO X_ROWID;
139 IF (C%NOTFOUND) THEN
140
141 CLOSE C;
142 IF g_debug_mode = 'Y' THEN
143 pa_debug.g_err_stage:= 'Rowid could not be fetched after Inserting for '||X_measure_set_code;
144 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level5);
145 END IF;
146 RAISE NO_DATA_FOUND;
147
148 END IF;
149 CLOSE C;
150
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 l_msg_count := FND_MSG_PUB.count_msg;
156
157 IF l_msg_count = 1 AND x_msg_data IS NULL THEN
158 PA_INTERFACE_UTILS_PUB.get_messages
159 (p_encoded => FND_API.G_TRUE
160 ,p_msg_index => 1
161 ,p_msg_count => l_msg_count
162 ,p_msg_data => l_msg_data
163 ,p_data => l_data
164 ,p_msg_index_out => l_msg_index_out);
165 x_msg_data := l_data;
166 x_msg_count := l_msg_count;
167 ELSE
168 x_msg_count := l_msg_count;
169 END IF;
170
171 IF g_debug_mode = 'Y' THEN
172 pa_debug.reset_curr_function;
173 END IF;
174
175 RETURN;
176
177 WHEN OTHERS THEN
178
179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
180 x_msg_count := 1;
181 x_msg_data := SQLERRM;
182
183 FND_MSG_PUB.add_exc_msg
184 ( p_pkg_name => 'PJI_MT_ROWSET_DET_PKG'
185 ,p_procedure_name => 'Insert Row'
186 ,p_error_text => x_msg_data);
187
188 IF g_debug_mode = 'Y' THEN
189 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
190 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,
191 pa_fp_constants_pkg.g_debug_level5);
192 pa_debug.reset_curr_function;
193 END IF;
194
195 RAISE;
196
197 END Insert_Row;
198
199
200 PROCEDURE Update_Row (
201 X_measure_set_code IN pji_mt_rowset_det.measure_set_code%TYPE,
202 X_rowset_code IN pji_mt_rowset_det.rowset_code%TYPE,
203 X_Object_Version_Number IN pji_mt_rowset_det.Object_Version_Number%TYPE,
204 X_display_order IN pji_mt_rowset_det.display_order%TYPE, --Bug 3798976
205 X_Last_Update_Date IN pji_mt_rowset_det.Last_Update_Date%TYPE,
206 X_Last_Updated_by IN pji_mt_rowset_det.Last_Updated_by%TYPE,
207 X_Last_Update_Login IN pji_mt_rowset_det.Last_Update_Login%TYPE,
208 X_Return_Status OUT NOCOPY VARCHAR2,
209 X_Msg_Data OUT NOCOPY VARCHAR2,
210 X_Msg_Count OUT NOCOPY NUMBER
211 )
212 IS
213
214 l_return_status VARCHAR2(1) := NULL;
215 l_msg_count NUMBER := 0;
216 l_data VARCHAR2(2000) := NULL;
217 l_msg_data VARCHAR2(2000) := NULL;
218 l_msg_index_out NUMBER;
219
220 BEGIN
221 NULL;
222
223 x_msg_count := 0;
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 IF g_debug_mode = 'Y' THEN
227 pa_debug.set_curr_function( p_function => 'validate',
228 p_debug_mode => g_debug_mode );
229 END IF;
230
231 IF g_debug_mode = 'Y' THEN
232 pa_debug.g_err_stage:= 'Updating pji_mt_rowset_det for'||X_measure_set_code;
233 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level3);
234 END IF;
235
236 UPDATE pji_mt_rowset_det
237 SET
238 Object_Version_Number = X_Object_Version_Number
239 , display_order = X_display_order --Bug 3798976
240 , Last_Update_Date = X_Last_Update_Date
241 , Last_Updated_By = X_Last_Updated_By
242 , Last_Update_Login = X_Last_Update_Login
243 WHERE measure_set_code = X_measure_set_code
244 AND rowset_code = X_rowset_code;
245
246 IF (SQL%NOTFOUND) THEN
247 IF g_debug_mode = 'Y' THEN
248 pa_debug.g_err_stage:= 'NDF while updating pji_mt_rowset_det, measure_code= '||X_measure_set_code||', rowset_code= '||x_rowset_code;
249 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage, pa_fp_constants_pkg.g_debug_level5);
250 END IF;
251 RAISE NO_DATA_FOUND;
252 END IF;
253
254 IF g_debug_mode = 'Y' THEN
255 pa_debug.g_err_stage:= 'Updating pji_mt_rowset_det for, measure_code= '||X_measure_set_code||', rowset_code= '||x_rowset_code;
256 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,pa_fp_constants_pkg.g_debug_level3);
257 END IF;
258
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 l_msg_count := FND_MSG_PUB.count_msg;
264
265 IF l_msg_count = 1 AND x_msg_data IS NULL THEN
266 PA_INTERFACE_UTILS_PUB.get_messages
267 (p_encoded => FND_API.G_TRUE
268 ,p_msg_index => 1
269 ,p_msg_count => l_msg_count
270 ,p_msg_data => l_msg_data
271 ,p_data => l_data
272 ,p_msg_index_out => l_msg_index_out);
273 x_msg_data := l_data;
274 x_msg_count := l_msg_count;
275 ELSE
276 x_msg_count := l_msg_count;
277 END IF;
278
279 IF g_debug_mode = 'Y' THEN
280 pa_debug.reset_curr_function;
281 END IF;
282
283 RAISE NO_DATA_FOUND;
284
285 WHEN OTHERS THEN
286
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 x_msg_count := 1;
289 x_msg_data := SQLERRM;
290
291 FND_MSG_PUB.add_exc_msg
292 ( p_pkg_name => 'PJI_MT_ROWSET_DET_PKG'
293 ,p_procedure_name => 'UPDATE_ROW'
294 ,p_error_text => x_msg_data);
295
296 IF g_debug_mode = 'Y' THEN
297 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
298 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,
299 pa_fp_constants_pkg.g_debug_level5);
300 pa_debug.reset_curr_function;
301 END IF;
302
303 RAISE;
304 END Update_Row;
305
306
307
308 PROCEDURE Load_Row (
309 X_measure_set_code IN pji_mt_rowset_det.measure_set_code%TYPE,
310 X_rowset_code IN pji_mt_rowset_det.rowset_code%TYPE,
311 X_Object_Version_Number IN pji_mt_rowset_det.Object_Version_Number%TYPE,
312 X_display_order IN pji_mt_rowset_det.display_order%TYPE, --Bug 3798976
313 X_Owner IN VARCHAR2
314 )
315 IS
316 User_Id NUMBER := NULL;
317 X_ROWID VARCHAR2(64);
318 l_return_status VARCHAR2(1) := NULL;
319 l_msg_count NUMBER := 0;
320 l_data VARCHAR2(2000) := NULL;
321 l_msg_data VARCHAR2(2000) := NULL;
322 l_msg_index_out NUMBER;
323
324 BEGIN
325
326 g_debug_mode := 'N';
327
328 IF (X_Owner = 'SEED')THEN
329 User_Id := 1;
330 ELSE
331 User_Id := 0;
332 END IF;
333 PJI_MT_ROWSET_DET_PKG.Update_Row (
334 X_measure_set_code => X_measure_set_code ,
335 X_rowset_code => X_rowset_code ,
336 X_Object_Version_Number => X_Object_Version_Number ,
337 X_display_order => X_display_order , --Bug 3798976
338 X_Last_Update_Date => SYSDATE ,
339 X_Last_Updated_By => User_Id ,
340 X_Last_Update_Login => 0 ,
341 X_Return_Status => l_Return_Status ,
342 X_Msg_Data => l_Msg_Data ,
343 X_Msg_Count => l_Msg_Count );
344
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348
349 PJI_MT_ROWSET_DET_PKG.Insert_Row (
350 X_Rowid => X_Rowid ,
351 X_measure_set_code => X_measure_set_code ,
352 X_rowset_code => X_rowset_code ,
353 X_Object_Version_Number => X_Object_Version_Number ,
354 X_display_order => X_display_order , --Bug 3798976
355 X_Creation_Date => SYSDATE ,
356 X_Created_By => User_Id ,
357 X_Last_Update_Date => SYSDATE ,
358 X_Last_Updated_By => User_Id ,
359 X_Last_Update_Login => 0 ,
360 X_Return_Status => l_Return_Status ,
361 X_Msg_Data => l_Msg_Data ,
362 X_Msg_Count => l_Msg_Count );
363
364 WHEN OTHERS THEN
365
366 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 l_msg_count := 1;
368 l_msg_data := SQLERRM;
369
370 FND_MSG_PUB.add_exc_msg
371 ( p_pkg_name => 'PJI_MT_ROWSET_DET_PKG'
372 ,p_procedure_name => 'UPDATE_ROW'
373 ,p_error_text => l_msg_data);
374
375 IF g_debug_mode = 'Y' THEN
376 pa_debug.g_err_stage:= 'Unexpected Error'||l_msg_data;
377 pa_debug.WRITE(g_module_name,pa_debug.g_err_stage,
378 pa_fp_constants_pkg.g_debug_level5);
379 pa_debug.reset_curr_function;
380 END IF;
381
382 RAISE;
383 END Load_Row;
384
385
386
387 END PJI_MT_ROWSET_DET_PKG;