[Home] [Help]
PACKAGE BODY: APPS.PSB_RULE_WITHIN_SEGMENT_PVT
Source
1 PACKAGE BODY PSB_RULE_WITHIN_SEGMENT_PVT AS
2 /* $Header: PSBVWSPB.pls 120.2 2005/07/13 11:31:26 shtripat noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_RULE_WITHIN_SEGMENT_PVT';
6
7 procedure INSERT_ROW (
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 p_commit IN VARCHAR2 := FND_API.G_FALSE,
11 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
12 p_return_status OUT NOCOPY VARCHAR2,
13 p_msg_count OUT NOCOPY NUMBER,
14 p_msg_data OUT NOCOPY VARCHAR2,
15 --
16 P_ROWID IN OUT NOCOPY VARCHAR2,
17 P_RULE_ID IN NUMBER,
18 P_SEGMENT_NAME IN VARCHAR2,
19 P_APPLICATION_COLUMN_NAME IN VARCHAR2,
20 p_Last_Update_Date DATE,
21 p_Last_Updated_By NUMBER,
22 p_Last_Update_Login NUMBER,
23 p_Created_By NUMBER,
24 p_Creation_Date DATE
25 ) is
26 --
27 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
28 l_api_version CONSTANT NUMBER := 1.0;
29 --
30 cursor C is
31 select ROWID
32 from PSB_RULE_WITHIN_SEGMENT
33 where RULE_ID = P_RULE_ID;
34
35 BEGIN
36 --
37 SAVEPOINT Insert_Row_Pvt ;
38 --
39 IF NOT FND_API.Compatible_API_Call ( l_api_version,
40 p_api_version,
41 l_api_name,
42 G_PKG_NAME )
43 THEN
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
45 END IF;
46 --
47
48 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
49 FND_MSG_PUB.initialize ;
50 END IF;
51 --
52 p_return_status := FND_API.G_RET_STS_SUCCESS ;
53 --
54
55 insert into PSB_RULE_WITHIN_SEGMENT (
56 RULE_ID,
57 SEGMENT_NAME,
58 APPLICATION_COLUMN_NAME,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN
64 ) values (
65 P_RULE_ID,
66 P_SEGMENT_NAME,
67 P_APPLICATION_COLUMN_NAME,
68 P_LAST_UPDATE_DATE,
69 P_LAST_UPDATED_BY,
70 P_LAST_UPDATE_DATE,
71 P_LAST_UPDATED_BY,
72 P_LAST_UPDATE_LOGIN
73 );
74
75 open c;
76 fetch c into P_ROWID;
77 if (c%notfound) then
78 close c;
79 raise FND_API.G_EXC_ERROR;
80 end if;
81 close c;
82
83 --
84 IF FND_API.To_Boolean ( p_commit ) THEN
85 COMMIT WORK;
86 END iF;
87 --
88 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
89 p_data => p_msg_data );
90 --
91 EXCEPTION
92 --
93 WHEN FND_API.G_EXC_ERROR THEN
94 --
95 ROLLBACK TO Insert_Row_Pvt ;
96 p_return_status := FND_API.G_RET_STS_ERROR;
97 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
98 p_data => p_msg_data );
99 --
100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
101 --
102 ROLLBACK TO Insert_Row_Pvt ;
103 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
104 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
105 p_data => p_msg_data );
106 --
107 WHEN OTHERS THEN
108 --
109 ROLLBACK TO Insert_Row_Pvt ;
110 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
111 --
112 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
113 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
114 l_api_name);
115 END if;
116 --
117 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
118 p_data => p_msg_data );
119 --
120 END Insert_Row;
121
122 procedure LOCK_ROW (
123 p_api_version IN NUMBER,
124 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
125 p_commit IN VARCHAR2 := FND_API.G_FALSE,
126 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
127 p_return_status OUT NOCOPY VARCHAR2,
128 p_msg_count OUT NOCOPY NUMBER,
129 p_msg_data OUT NOCOPY VARCHAR2,
130 p_lock_row OUT NOCOPY VARCHAR2,
131 --
132 P_RULE_ID IN NUMBER,
133 P_SEGMENT_NAME IN VARCHAR2,
134 P_APPLICATION_COLUMN_NAME IN VARCHAR2
135 ) is
136 --
137 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
138 l_api_version CONSTANT NUMBER := 1.0;
139 --
140 Counter NUMBER;
141 cursor c1 is select
142 SEGMENT_NAME, APPLICATION_COLUMN_NAME
143 from PSB_RULE_WITHIN_SEGMENT
144 where RULE_ID = P_RULE_ID
145 for update of RULE_ID nowait;
146 tlinfo c1%rowtype;
147
148 BEGIN
149 --
150 SAVEPOINT Lock_Row_Pvt ;
151 --
152 IF NOT FND_API.Compatible_API_Call ( l_api_version,
153 p_api_version,
154 l_api_name,
155 G_PKG_NAME )
156 THEN
157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
158 END IF;
159 --
160
161 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
162 FND_MSG_PUB.initialize ;
163 END IF;
164 --
165 p_return_status := FND_API.G_RET_STS_SUCCESS ;
166 --
167 open c1;
168 fetch c1 into tlinfo;
169 if (c1%notfound) then
170 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
171 app_exception.raise_exception;
172 close c1;
173 return;
174 end if;
175 close c1;
176
177 if ( (tlinfo.SEGMENT_NAME = P_SEGMENT_NAME)
178 AND (tlinfo.APPLICATION_COLUMN_NAME = P_APPLICATION_COLUMN_NAME)
179 ) then
180 p_lock_row := FND_API.G_TRUE;
181 ELSE
182 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
183 FND_MSG_PUB.Add;
184 RAISE FND_API.G_EXC_ERROR ;
185 END IF;
186
187 --
188 IF FND_API.To_Boolean ( p_commit ) THEN
189 COMMIT WORK;
190 END iF;
191 --
192 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
193 p_data => p_msg_data );
194 --
195 EXCEPTION
196 --
197 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
198 --
199 ROLLBACK TO Lock_Row_Pvt ;
200 p_lock_row := FND_API.G_FALSE;
201 p_return_status := FND_API.G_RET_STS_ERROR;
202 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
203 p_data => p_msg_data );
204 --
205 WHEN FND_API.G_EXC_ERROR THEN
206 --
207 ROLLBACK TO Lock_Row_Pvt ;
208 p_lock_row := FND_API.G_FALSE;
209 p_return_status := FND_API.G_RET_STS_ERROR;
210 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
211 p_data => p_msg_data );
212 --
213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
214 --
215 ROLLBACK TO Lock_Row_Pvt ;
216 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
218 p_data => p_msg_data );
219 --
220 WHEN OTHERS THEN
221 --
222 ROLLBACK TO Lock_Row_Pvt ;
223 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 --
225 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
226 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
227 l_api_name);
228 END if;
229 --
230 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
231 p_data => p_msg_data );
232 --
233 END Lock_Row;
234
235 procedure UPDATE_ROW (
236 p_api_version IN NUMBER,
237 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
238 p_commit IN VARCHAR2 := FND_API.G_FALSE,
239 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
240 p_return_status OUT NOCOPY VARCHAR2,
241 p_msg_count OUT NOCOPY NUMBER,
242 p_msg_data OUT NOCOPY VARCHAR2,
243 --
244 P_RULE_ID IN NUMBER,
245 P_SEGMENT_NAME IN VARCHAR2,
246 P_APPLICATION_COLUMN_NAME IN VARCHAR2,
247 p_Last_Update_Date DATE,
248 p_Last_Updated_By NUMBER,
249 p_Last_Update_Login NUMBER
250 ) is
251 --
252 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
253 l_api_version CONSTANT NUMBER := 1.0;
254 --
255 BEGIN
256 --
257 SAVEPOINT Update_Row_Pvt ;
258 --
259 IF NOT FND_API.Compatible_API_Call ( l_api_version,
260 p_api_version,
261 l_api_name,
262 G_PKG_NAME )
263 THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
265 END IF;
266 --
267
268 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
269 FND_MSG_PUB.initialize ;
270 END IF;
271 --
272 p_return_status := FND_API.G_RET_STS_SUCCESS ;
273 --
274 update PSB_RULE_WITHIN_SEGMENT set
275 SEGMENT_NAME = P_SEGMENT_NAME,
276 APPLICATION_COLUMN_NAME = P_APPLICATION_COLUMN_NAME,
277 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
278 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
279 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
280 where RULE_ID = P_RULE_ID
281 ;
282 if (sql%notfound) then
283 raise FND_API.G_EXC_ERROR;
284 end if;
285 --
286 IF FND_API.To_Boolean ( p_commit ) THEN
287 COMMIT WORK;
288 END iF;
289 --
290 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
291 p_data => p_msg_data );
292 --
293 EXCEPTION
294 --
295 WHEN FND_API.G_EXC_ERROR THEN
296 --
297 ROLLBACK TO Update_Row_Pvt ;
298 p_return_status := FND_API.G_RET_STS_ERROR;
299 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
300 p_data => p_msg_data );
301 --
302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
303 --
304 ROLLBACK TO Update_Row_Pvt ;
305 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
307 p_data => p_msg_data );
308 --
309 WHEN OTHERS THEN
310 --
311 ROLLBACK TO Update_Row_Pvt ;
312 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 --
314 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
315 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
316 l_api_name);
317 END if;
318 --
319 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
320 p_data => p_msg_data );
321 --
322 END Update_Row;
323
324
325 procedure DELETE_ROW (
326 p_api_version IN NUMBER,
327 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
328 p_commit IN VARCHAR2 := FND_API.G_FALSE,
329 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
330 p_return_status OUT NOCOPY VARCHAR2,
331 p_msg_count OUT NOCOPY NUMBER,
332 p_msg_data OUT NOCOPY VARCHAR2,
333 --
334 P_RULE_ID IN NUMBER,
335 P_APPLICATION_COLUMN_NAME IN VARCHAR2
336 ) is
337 --
338 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
339 l_api_version CONSTANT NUMBER := 1.0;
340 --
341 BEGIN
342 --
343 SAVEPOINT Delete_Row_Pvt ;
344 --
345 IF NOT FND_API.Compatible_API_Call ( l_api_version,
346 p_api_version,
347 l_api_name,
348 G_PKG_NAME )
349 THEN
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
351 END IF;
352 --
353
354 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
355 FND_MSG_PUB.initialize ;
356 END IF;
357 --
358 p_return_status := FND_API.G_RET_STS_SUCCESS ;
359 --
360 delete from PSB_RULE_WITHIN_SEGMENT
361 where RULE_ID = P_RULE_ID
362 and APPLICATION_COLUMN_NAME = P_APPLICATION_COLUMN_NAME;
363 if (sql%notfound) then
364 raise no_data_found;
365 end if;
366 --
367 IF FND_API.To_Boolean ( p_commit ) THEN
368 COMMIT WORK;
369 END iF;
370 --
371 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
372 p_data => p_msg_data );
373
374 EXCEPTION
375 --
376 WHEN FND_API.G_EXC_ERROR THEN
377 --
378 ROLLBACK TO Delete_Row_Pvt ;
379 p_return_status := FND_API.G_RET_STS_ERROR;
380 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
381 p_data => p_msg_data );
382 --
383 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
384 --
385 ROLLBACK TO Delete_Row_Pvt ;
386 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
388 p_data => p_msg_data );
389 --
390 WHEN OTHERS THEN
391 --
392 ROLLBACK TO Delete_Row_Pvt ;
393 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 --
395 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
396 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
397 l_api_name);
398 END if;
399 --
400 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
401 p_data => p_msg_data );
402 --
403 END Delete_Row;
404
405
406 FUNCTION VALIDATE_ACCOUNT_SEGMENT (
407 p_str IN VARCHAR2,
408 p_sets IN VARCHAR2,
409 p_chart_of_accounts_id IN VARCHAR2,
410 p_app_column_name IN VARCHAR2
411 ) RETURN BOOLEAN
412 IS
413 --
414 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Account_Segment';
415 l_api_version CONSTANT NUMBER := 1.0;
416
417 TYPE SegmentCurTyp IS REF CURSOR;
418 cur SegmentCurTyp;
419 segmentno VARCHAR2(30);
420
421 l_sql_validate VARCHAR2(4000);
422 l_sql_str VARCHAR2(3000);
423 l_sql_sets VARCHAR2(3000);
424
425 /* Bug No 2131859 Start */
426 ctr1 NUMBER := 0;
427 ctr2 NUMBER := 0;
428 /* Bug No 2131859 End */
429
430 --
431 BEGIN
432 --
433 -- Building query for finding segment numbers for a particular segment
434 --
435
436 l_sql_str := 'SELECT distinct glcc.'||p_app_column_name||
437 ' FROM gl_code_combinations glcc,'||
438 ' psb_account_position_set_lines apsl'||
439 ' where glcc.chart_of_accounts_id = '||p_chart_of_accounts_id||
440 ' AND apsl.account_position_set_id in '||p_str||
441 ' AND glcc.'||p_app_column_name||
442 ' BETWEEN apsl.'||p_app_column_name||
443 '_low AND apsl.'||p_app_column_name||
444 '_high';
445
446 l_sql_sets := 'SELECT distinct glcc.'||p_app_column_name||
447 ' FROM gl_code_combinations glcc,'||
448 ' psb_account_position_set_lines apsl'||
449 ' where glcc.chart_of_accounts_id = '||p_chart_of_accounts_id||
450 ' AND apsl.account_position_set_id in '||p_sets||
451 ' AND glcc.'||p_app_column_name||
452 ' BETWEEN apsl.'||p_app_column_name||
453 '_low AND apsl.'||p_app_column_name||
454 '_high';
455
456 /* Bug No 2131859 Start */
457 OPEN cur FOR l_sql_str;
458 LOOP
459 FETCH cur INTO segmentno;
460 if (cur%notfound) then
461 EXIT;
462 else
463 ctr1 := ctr1 + 1;
464 end if;
465 END LOOP;
466
467 OPEN cur FOR l_sql_sets;
468 LOOP
469 FETCH cur INTO segmentno;
470 if (cur%notfound) then
471 EXIT;
472 else
473 ctr2 := ctr2 + 1;
474 end if;
475 END LOOP;
476
477 IF ctr1 > ctr2 then
478 l_sql_validate := l_sql_str||' minus '||l_sql_sets;
479 ELSE
480 l_sql_validate := l_sql_sets||' minus '||l_sql_str;
481 END IF;
482 /* Bug No 2131859 End */
483
484 OPEN cur FOR l_sql_validate;
485 -- USING P_STR, P_SETS, P_CHART_OF_ACCOUNTS_ID;
486
487 LOOP
488 FETCH cur INTO segmentno;
489
490 if (cur%notfound) then
491 RETURN(TRUE);
492 else
493 RETURN(FALSE);
494 end if;
495 EXIT;
496 END LOOP;
497 CLOSE cur;
498 --
499 EXCEPTION
500 --
501 WHEN OTHERS THEN
502 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
503 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
504 l_api_name);
505 END if;
506 RETURN (FALSE);
507
508 END Validate_Account_Segment;
509
510
511 end PSB_RULE_WITHIN_SEGMENT_PVT;