[Home] [Help]
PACKAGE BODY: APPS.PSB_ENTITY_PVT
Source
1 PACKAGE BODY PSB_ENTITY_PVT AS
2 /* $Header: PSBVENPB.pls 120.2 2005/07/13 11:24:48 shtripat ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_ENTITY_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_ENTITY_ID in NUMBER,
18 P_ENTITY_TYPE in VARCHAR2,
19 P_ENTITY_SUBTYPE in VARCHAR2,
20 P_NAME in VARCHAR2,
21 P_DESCRIPTION in VARCHAR2,
22 P_DATA_EXTRACT_ID in NUMBER,
23 P_SET_OF_BOOKS_ID in NUMBER,
24 P_BUDGET_GROUP_ID in NUMBER := FND_API.G_MISS_NUM,
25 P_ALLOCATION_TYPE in VARCHAR2,
26 P_BUDGET_YEAR_TYPE_ID in NUMBER,
27 P_BALANCE_TYPE in VARCHAR2,
28 P_PARAMETER_AUTOINC_RULE in VARCHAR2,
29 P_PARAMETER_COMPOUND_ANNUALLY in VARCHAR2,
30 P_CURRENCY_CODE in VARCHAR2,
31 P_FTE_CONSTRAINT in VARCHAR2,
32 P_CONSTRAINT_DETAILED_FLAG in VARCHAR2,
33 /* Budget Revision Rules Enhancement Start */
34 P_APPLY_ACCOUNT_SET_FLAG in VARCHAR2,
35 P_BALANCE_ACCOUNT_SET_FLAG in VARCHAR2,
36 /* Budget Revision Rules Enhancement End */
37 P_ATTRIBUTE1 in VARCHAR2,
38 P_ATTRIBUTE2 in VARCHAR2,
39 P_ATTRIBUTE3 in VARCHAR2,
40 P_ATTRIBUTE4 in VARCHAR2,
41 P_ATTRIBUTE5 in VARCHAR2,
42 P_ATTRIBUTE6 in VARCHAR2,
43 P_ATTRIBUTE7 in VARCHAR2,
44 P_ATTRIBUTE8 in VARCHAR2,
45 P_ATTRIBUTE9 in VARCHAR2,
46 P_ATTRIBUTE10 in VARCHAR2,
47 P_CONTEXT in VARCHAR2,
48 P_EFFECTIVE_START_DATE in DATE := FND_API.G_MISS_DATE,
49 P_EFFECTIVE_END_DATE in DATE := FND_API.G_MISS_DATE,
50 p_Last_Update_Date DATE,
51 p_Last_Updated_By NUMBER,
52 p_Last_Update_Login NUMBER,
53 p_Created_By NUMBER,
54 p_Creation_Date DATE
55 ) is
56 --
57 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
58 l_api_version CONSTANT NUMBER := 1.0;
59 --
60 cursor C is
61 select ROWID
62 from PSB_ENTITY
63 where ENTITY_ID = P_ENTITY_ID;
64
65 BEGIN
66 --
67 SAVEPOINT Insert_Row_Pvt ;
68 --
69 IF NOT FND_API.Compatible_API_Call ( l_api_version,
70 p_api_version,
71 l_api_name,
72 G_PKG_NAME )
73 THEN
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
75 END IF;
76 --
77
78 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
79 FND_MSG_PUB.initialize ;
80 END IF;
81 --
82 p_return_status := FND_API.G_RET_STS_SUCCESS ;
83 --
84
85 insert into PSB_ENTITY (
86 ENTITY_ID,
87 ENTITY_TYPE,
88 ENTITY_SUBTYPE,
89 NAME,
90 DESCRIPTION,
91 DATA_EXTRACT_ID,
92 SET_OF_BOOKS_ID,
93 BUDGET_GROUP_ID,
94 ALLOCATION_TYPE,
95 BUDGET_YEAR_TYPE_ID,
96 BALANCE_TYPE,
97 PARAMETER_AUTOINC_RULE,
98 PARAMETER_COMPOUND_ANNUALLY,
99 CURRENCY_CODE,
100 FTE_CONSTRAINT,
101 CONSTRAINT_DETAILED_FLAG,
102 /* Budget Revision Rules Enhancement Start */
103 APPLY_ACCOUNT_SET_FLAG,
104 BALANCE_ACCOUNT_SET_FLAG,
105 /* Budget Revision Rules Enhancement End */
106 ATTRIBUTE1,
107 ATTRIBUTE2,
108 ATTRIBUTE3,
109 ATTRIBUTE4,
110 ATTRIBUTE5,
111 ATTRIBUTE6,
112 ATTRIBUTE7,
113 ATTRIBUTE8,
114 ATTRIBUTE9,
115 ATTRIBUTE10,
116 CONTEXT,
117 EFFECTIVE_START_DATE,
118 EFFECTIVE_END_DATE,
119 CREATION_DATE,
120 CREATED_BY,
121 LAST_UPDATE_DATE,
122 LAST_UPDATED_BY,
123 LAST_UPDATE_LOGIN
124 ) values (
125 P_ENTITY_ID,
126 P_ENTITY_TYPE,
127 P_ENTITY_SUBTYPE,
128 P_NAME,
129 P_DESCRIPTION,
130 P_DATA_EXTRACT_ID,
131 P_SET_OF_BOOKS_ID,
132 decode(P_BUDGET_GROUP_ID, FND_API.G_MISS_NUM, null, P_BUDGET_GROUP_ID),
133 P_ALLOCATION_TYPE,
134 P_BUDGET_YEAR_TYPE_ID,
135 P_BALANCE_TYPE,
136 P_PARAMETER_AUTOINC_RULE,
137 P_PARAMETER_COMPOUND_ANNUALLY,
138 P_CURRENCY_CODE,
139 P_FTE_CONSTRAINT,
140 P_CONSTRAINT_DETAILED_FLAG,
141 /* Budget Revision Rules Enhancement Start */
142 P_APPLY_ACCOUNT_SET_FLAG,
143 P_BALANCE_ACCOUNT_SET_FLAG,
144 /* Budget Revision Rules Enhancement End */
145 P_ATTRIBUTE1,
146 P_ATTRIBUTE2,
147 P_ATTRIBUTE3,
148 P_ATTRIBUTE4,
149 P_ATTRIBUTE5,
150 P_ATTRIBUTE6,
151 P_ATTRIBUTE7,
152 P_ATTRIBUTE8,
153 P_ATTRIBUTE9,
154 P_ATTRIBUTE10,
155 P_CONTEXT,
156 decode(P_EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, null,
157 P_EFFECTIVE_START_DATE),
158 decode(P_EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, null,
159 P_EFFECTIVE_END_DATE),
160 P_LAST_UPDATE_DATE,
161 P_LAST_UPDATED_BY,
162 P_LAST_UPDATE_DATE,
163 P_LAST_UPDATED_BY,
164 P_LAST_UPDATE_LOGIN
165 );
166
167 open c;
168 fetch c into P_ROWID;
169 if (c%notfound) then
170 close c;
171 raise FND_API.G_EXC_ERROR;
172 end if;
173 close c;
174
175 --
176 IF FND_API.To_Boolean ( p_commit ) THEN
177 COMMIT WORK;
178 END iF;
179 --
180 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
181 p_data => p_msg_data );
182 --
183 EXCEPTION
184 --
185 WHEN FND_API.G_EXC_ERROR THEN
186 --
187 ROLLBACK TO Insert_Row_Pvt ;
188 p_return_status := FND_API.G_RET_STS_ERROR;
189 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
190 p_data => p_msg_data );
191 --
192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
193 --
194 ROLLBACK TO Insert_Row_Pvt ;
195 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
197 p_data => p_msg_data );
198 --
199 WHEN OTHERS THEN
200 --
201 ROLLBACK TO Insert_Row_Pvt ;
202 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 --
204 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
205 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
206 l_api_name);
207 END if;
208 --
209 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
210 p_data => p_msg_data );
211 --
212 END Insert_Row;
213
214 procedure LOCK_ROW (
215 p_api_version IN NUMBER,
216 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
217 p_commit IN VARCHAR2 := FND_API.G_FALSE,
218 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
219 p_return_status OUT NOCOPY VARCHAR2,
220 p_msg_count OUT NOCOPY NUMBER,
221 p_msg_data OUT NOCOPY VARCHAR2,
222 p_lock_row OUT NOCOPY VARCHAR2,
223 --
224 P_ENTITY_ID in NUMBER,
225 P_ENTITY_TYPE in VARCHAR2,
226 P_ENTITY_SUBTYPE in VARCHAR2,
227 P_NAME in VARCHAR2,
228 P_DESCRIPTION in VARCHAR2,
229 P_DATA_EXTRACT_ID in NUMBER,
230 P_SET_OF_BOOKS_ID in NUMBER,
231 P_BUDGET_GROUP_ID in NUMBER := FND_API.G_MISS_NUM,
232 P_ALLOCATION_TYPE in VARCHAR2,
233 P_BUDGET_YEAR_TYPE_ID in NUMBER,
234 P_BALANCE_TYPE in VARCHAR2,
235 P_PARAMETER_AUTOINC_RULE in VARCHAR2,
236 P_PARAMETER_COMPOUND_ANNUALLY in VARCHAR2,
237 P_CURRENCY_CODE in VARCHAR2,
238 P_FTE_CONSTRAINT in VARCHAR2,
239 P_CONSTRAINT_DETAILED_FLAG in VARCHAR2,
240 /* Budget Revision Rules Enhancement Start */
241 P_APPLY_ACCOUNT_SET_FLAG in VARCHAR2,
242 P_BALANCE_ACCOUNT_SET_FLAG in VARCHAR2,
243 /* Budget Revision Rules Enhancement End */
244 P_ATTRIBUTE1 in VARCHAR2,
245 P_ATTRIBUTE2 in VARCHAR2,
246 P_ATTRIBUTE3 in VARCHAR2,
247 P_ATTRIBUTE4 in VARCHAR2,
248 P_ATTRIBUTE5 in VARCHAR2,
249 P_ATTRIBUTE6 in VARCHAR2,
250 P_ATTRIBUTE7 in VARCHAR2,
251 P_ATTRIBUTE8 in VARCHAR2,
252 P_ATTRIBUTE9 in VARCHAR2,
253 P_ATTRIBUTE10 in VARCHAR2,
254 P_CONTEXT in VARCHAR2,
255 P_EFFECTIVE_START_DATE in DATE := FND_API.G_MISS_DATE,
256 P_EFFECTIVE_END_DATE in DATE := FND_API.G_MISS_DATE
257 ) is
258 --
259 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
260 l_api_version CONSTANT NUMBER := 1.0;
261 --
262 Counter NUMBER;
263 cursor c1 is select
264 ENTITY_TYPE,
265 ENTITY_SUBTYPE,
266 NAME,
267 DESCRIPTION,
268 DATA_EXTRACT_ID,
269 SET_OF_BOOKS_ID,
270 BUDGET_GROUP_ID,
271 ALLOCATION_TYPE,
272 BUDGET_YEAR_TYPE_ID,
273 BALANCE_TYPE,
274 PARAMETER_AUTOINC_RULE,
275 PARAMETER_COMPOUND_ANNUALLY,
276 CURRENCY_CODE,
277 FTE_CONSTRAINT,
278 CONSTRAINT_DETAILED_FLAG,
279 /* Budget Revision Rules Enhancement Start */
280 APPLY_ACCOUNT_SET_FLAG,
281 BALANCE_ACCOUNT_SET_FLAG,
282 /* Budget Revision Rules Enhancement End */
283 ATTRIBUTE1,
284 ATTRIBUTE2,
285 ATTRIBUTE3,
286 ATTRIBUTE4,
287 ATTRIBUTE5,
288 ATTRIBUTE6,
289 ATTRIBUTE7,
290 ATTRIBUTE8,
291 ATTRIBUTE9,
292 ATTRIBUTE10,
293 CONTEXT,
294 EFFECTIVE_START_DATE,
295 EFFECTIVE_END_DATE
296 from PSB_ENTITY
297 where ENTITY_ID = P_ENTITY_ID
298 for update of ENTITY_ID nowait;
299 tlinfo c1%rowtype;
300
301 BEGIN
302 --
303 SAVEPOINT Lock_Row_Pvt ;
304 --
305 IF NOT FND_API.Compatible_API_Call ( l_api_version,
306 p_api_version,
307 l_api_name,
308 G_PKG_NAME )
309 THEN
310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
311 END IF;
312 --
313
314 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
315 FND_MSG_PUB.initialize ;
316 END IF;
317 --
318 p_return_status := FND_API.G_RET_STS_SUCCESS ;
319 --
320 open c1;
321 fetch c1 into tlinfo;
322 if (c1%notfound) then
323 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
324 app_exception.raise_exception;
325 close c1;
326 return;
327 end if;
328 close c1;
329
330 if ( (tlinfo.ENTITY_TYPE = P_ENTITY_TYPE)
331 AND (tlinfo.ENTITY_SUBTYPE = P_ENTITY_SUBTYPE)
332 AND (tlinfo.NAME = P_NAME)
333 AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
334 OR ((tlinfo.DESCRIPTION is null)
335 AND (P_DESCRIPTION is null)))
336 AND ((tlinfo.DATA_EXTRACT_ID = P_DATA_EXTRACT_ID)
337 OR ((tlinfo.DATA_EXTRACT_ID is null)
338 AND (P_DATA_EXTRACT_ID is null)))
339 AND ((tlinfo.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID)
340 OR ((tlinfo.SET_OF_BOOKS_ID is null)
341 AND (P_SET_OF_BOOKS_ID is null)))
342 AND ((tlinfo.BUDGET_GROUP_ID = P_BUDGET_GROUP_ID)
343 OR ((tlinfo.BUDGET_GROUP_ID is null)
344 AND (P_BUDGET_GROUP_ID is null))
345 OR ((tlinfo.BUDGET_GROUP_ID is null)
346 AND (P_BUDGET_GROUP_ID = FND_API.G_MISS_NUM)))
347 AND ((tlinfo.ALLOCATION_TYPE = P_ALLOCATION_TYPE)
348 OR ((tlinfo.ALLOCATION_TYPE is null)
349 AND (P_ALLOCATION_TYPE is null)))
350 AND ((tlinfo.BUDGET_YEAR_TYPE_ID = P_BUDGET_YEAR_TYPE_ID)
351 OR ((tlinfo.BUDGET_YEAR_TYPE_ID is null)
352 AND (P_BUDGET_YEAR_TYPE_ID is null)))
353 AND ((tlinfo.BALANCE_TYPE = P_BALANCE_TYPE)
354 OR ((tlinfo.BALANCE_TYPE is null)
355 AND (P_BALANCE_TYPE is null)))
356 AND ((tlinfo.PARAMETER_AUTOINC_RULE = P_PARAMETER_AUTOINC_RULE)
357 OR ((tlinfo.PARAMETER_AUTOINC_RULE is null)
358 AND (P_PARAMETER_AUTOINC_RULE is null)))
359 AND ((tlinfo.PARAMETER_COMPOUND_ANNUALLY = P_PARAMETER_COMPOUND_ANNUALLY)
360 OR ((tlinfo.PARAMETER_COMPOUND_ANNUALLY is null)
361 AND (P_PARAMETER_COMPOUND_ANNUALLY is null)))
362 AND ((tlinfo.CURRENCY_CODE = P_CURRENCY_CODE)
363 OR ((tlinfo.CURRENCY_CODE is null)
364 AND (P_CURRENCY_CODE is null)))
365 AND ((tlinfo.FTE_CONSTRAINT = P_FTE_CONSTRAINT)
366 OR ((tlinfo.FTE_CONSTRAINT is null)
367 AND (P_FTE_CONSTRAINT is null)))
368 AND ((tlinfo.CONSTRAINT_DETAILED_FLAG = P_CONSTRAINT_DETAILED_FLAG)
369 OR ((tlinfo.CONSTRAINT_DETAILED_FLAG is null)
370 AND (P_CONSTRAINT_DETAILED_FLAG is null)))
371 /* Budget Revision Rules Enhancement Start */
372 AND ((tlinfo.APPLY_ACCOUNT_SET_FLAG = P_APPLY_ACCOUNT_SET_FLAG)
373 OR ((tlinfo.APPLY_ACCOUNT_SET_FLAG is null)
374 AND (P_APPLY_ACCOUNT_SET_FLAG is null)))
375
376 AND ((tlinfo.BALANCE_ACCOUNT_SET_FLAG = P_BALANCE_ACCOUNT_SET_FLAG)
377 OR ((tlinfo.BALANCE_ACCOUNT_SET_FLAG is null)
378 AND (P_BALANCE_ACCOUNT_SET_FLAG is null)))
379 /* Budget Revision Rules Enhancement End */
380 AND ((tlinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
381 OR ((tlinfo.ATTRIBUTE1 is null)
382 AND (P_ATTRIBUTE1 is null)))
383 AND ((tlinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
384 OR ((tlinfo.ATTRIBUTE2 is null)
385 AND (P_ATTRIBUTE2 is null)))
386 AND ((tlinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
387 OR ((tlinfo.ATTRIBUTE3 is null)
388 AND (P_ATTRIBUTE3 is null)))
389 AND ((tlinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
390 OR ((tlinfo.ATTRIBUTE4 is null)
394 AND (P_ATTRIBUTE5 is null)))
391 AND (P_ATTRIBUTE4 is null)))
392 AND ((tlinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
393 OR ((tlinfo.ATTRIBUTE5 is null)
395 AND ((tlinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
396 OR ((tlinfo.ATTRIBUTE6 is null)
397 AND (P_ATTRIBUTE6 is null)))
398 AND ((tlinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
399 OR ((tlinfo.ATTRIBUTE7 is null)
400 AND (P_ATTRIBUTE7 is null)))
401 AND ((tlinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
402 OR ((tlinfo.ATTRIBUTE8 is null)
403 AND (P_ATTRIBUTE8 is null)))
404 AND ((tlinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
405 OR ((tlinfo.ATTRIBUTE9 is null)
406 AND (P_ATTRIBUTE9 is null)))
407 AND ((tlinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
408 OR ((tlinfo.ATTRIBUTE10 is null)
409 AND (P_ATTRIBUTE10 is null)))
410 AND ((tlinfo.CONTEXT = P_CONTEXT)
411 OR ((tlinfo.CONTEXT is null)
412 AND (P_CONTEXT is null)))
413 AND ((tlinfo.EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE)
414 OR ((tlinfo.EFFECTIVE_START_DATE is null)
415 AND (P_EFFECTIVE_START_DATE is null))
416 OR ((tlinfo.EFFECTIVE_START_DATE is null)
417 AND (P_EFFECTIVE_START_DATE = FND_API.G_MISS_DATE)))
418 AND ((tlinfo.EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE)
419 OR ((tlinfo.EFFECTIVE_END_DATE is null)
420 AND (P_EFFECTIVE_END_DATE is null))
421 OR ((tlinfo.EFFECTIVE_END_DATE is null)
422 AND (P_EFFECTIVE_END_DATE = FND_API.G_MISS_DATE)))
423 ) then
424 p_lock_row := FND_API.G_TRUE;
425 ELSE
426 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
427 FND_MSG_PUB.Add;
428 RAISE FND_API.G_EXC_ERROR ;
429 END IF;
430
431 --
432 IF FND_API.To_Boolean ( p_commit ) THEN
433 COMMIT WORK;
434 END iF;
435 --
436 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
437 p_data => p_msg_data );
438 --
439 EXCEPTION
440 --
441 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
442 --
443 ROLLBACK TO Lock_Row_Pvt ;
444 p_lock_row := FND_API.G_FALSE;
445 p_return_status := FND_API.G_RET_STS_ERROR;
446 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
447 p_data => p_msg_data );
448 --
449 WHEN FND_API.G_EXC_ERROR THEN
450 --
451 ROLLBACK TO Lock_Row_Pvt ;
452 p_lock_row := FND_API.G_FALSE;
453 p_return_status := FND_API.G_RET_STS_ERROR;
454 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
455 p_data => p_msg_data );
456 --
457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
458 --
459 ROLLBACK TO Lock_Row_Pvt ;
460 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
461 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
462 p_data => p_msg_data );
463 --
464 WHEN OTHERS THEN
465 --
466 ROLLBACK TO Lock_Row_Pvt ;
467 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
468 --
469 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
470 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
471 l_api_name);
472 END if;
473 --
474 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
475 p_data => p_msg_data );
476 --
477 END Lock_Row;
478
479 procedure UPDATE_ROW (
480 p_api_version IN NUMBER,
481 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
482 p_commit IN VARCHAR2 := FND_API.G_FALSE,
483 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
484 p_return_status OUT NOCOPY VARCHAR2,
485 p_msg_count OUT NOCOPY NUMBER,
486 p_msg_data OUT NOCOPY VARCHAR2,
487 --
488 P_ENTITY_ID in NUMBER,
489 P_ENTITY_TYPE in VARCHAR2,
490 P_ENTITY_SUBTYPE in VARCHAR2,
491 P_NAME in VARCHAR2,
492 P_DESCRIPTION in VARCHAR2,
493 P_DATA_EXTRACT_ID in NUMBER,
494 P_SET_OF_BOOKS_ID in NUMBER,
495 P_BUDGET_GROUP_ID in NUMBER := FND_API.G_MISS_NUM,
496 P_ALLOCATION_TYPE in VARCHAR2,
497 P_BUDGET_YEAR_TYPE_ID in NUMBER,
498 P_BALANCE_TYPE in VARCHAR2,
499 P_PARAMETER_AUTOINC_RULE in VARCHAR2,
500 P_PARAMETER_COMPOUND_ANNUALLY in VARCHAR2,
501 P_CURRENCY_CODE in VARCHAR2,
502 P_FTE_CONSTRAINT in VARCHAR2,
503 P_CONSTRAINT_DETAILED_FLAG in VARCHAR2,
504 /* Budget Revision Rules Enhancement Start */
505 P_APPLY_ACCOUNT_SET_FLAG in VARCHAR2,
506 P_BALANCE_ACCOUNT_SET_FLAG in VARCHAR2,
507 /* Budget Revision Rules Enhancement End */
508 P_ATTRIBUTE1 in VARCHAR2,
509 P_ATTRIBUTE2 in VARCHAR2,
510 P_ATTRIBUTE3 in VARCHAR2,
511 P_ATTRIBUTE4 in VARCHAR2,
512 P_ATTRIBUTE5 in VARCHAR2,
513 P_ATTRIBUTE6 in VARCHAR2,
514 P_ATTRIBUTE7 in VARCHAR2,
515 P_ATTRIBUTE8 in VARCHAR2,
516 P_ATTRIBUTE9 in VARCHAR2,
517 P_ATTRIBUTE10 in VARCHAR2,
518 P_CONTEXT in VARCHAR2,
519 P_EFFECTIVE_START_DATE in DATE := FND_API.G_MISS_DATE,
520 P_EFFECTIVE_END_DATE in DATE := FND_API.G_MISS_DATE,
521 p_Last_Update_Date DATE,
522 p_Last_Updated_By NUMBER,
523 p_Last_Update_Login NUMBER
524 ) is
525 --
526 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
530 --
527 l_api_version CONSTANT NUMBER := 1.0;
528 --
529 BEGIN
531 SAVEPOINT Update_Row_Pvt ;
532 --
533 IF NOT FND_API.Compatible_API_Call ( l_api_version,
534 p_api_version,
535 l_api_name,
536 G_PKG_NAME )
537 THEN
538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
539 END IF;
540 --
541
542 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
543 FND_MSG_PUB.initialize ;
544 END IF;
545 --
546 p_return_status := FND_API.G_RET_STS_SUCCESS ;
547 --
548 update PSB_ENTITY set
549 ENTITY_TYPE = P_ENTITY_TYPE,
550 ENTITY_SUBTYPE = P_ENTITY_SUBTYPE,
551 NAME = P_NAME,
552 DESCRIPTION = P_DESCRIPTION,
553 DATA_EXTRACT_ID = P_DATA_EXTRACT_ID,
554 SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID,
555 BUDGET_GROUP_ID = decode(P_BUDGET_GROUP_ID, FND_API.G_MISS_NUM,
556 null, P_BUDGET_GROUP_ID),
557 ALLOCATION_TYPE = P_ALLOCATION_TYPE,
558 BUDGET_YEAR_TYPE_ID = P_BUDGET_YEAR_TYPE_ID,
559 BALANCE_TYPE = P_BALANCE_TYPE,
560 PARAMETER_AUTOINC_RULE = P_PARAMETER_AUTOINC_RULE,
561 PARAMETER_COMPOUND_ANNUALLY = P_PARAMETER_COMPOUND_ANNUALLY,
562 CURRENCY_CODE = P_CURRENCY_CODE,
563 FTE_CONSTRAINT = P_FTE_CONSTRAINT,
564 CONSTRAINT_DETAILED_FLAG = P_CONSTRAINT_DETAILED_FLAG,
565 /* Budget Revision Rules Enhancement Start */
566 APPLY_ACCOUNT_SET_FLAG = P_APPLY_ACCOUNT_SET_FLAG,
567 BALANCE_ACCOUNT_SET_FLAG = P_BALANCE_ACCOUNT_SET_FLAG,
568 /* Budget Revision Rules Enhancement End */
569 ATTRIBUTE1 = P_ATTRIBUTE1,
570 ATTRIBUTE2 = P_ATTRIBUTE2,
571 ATTRIBUTE3 = P_ATTRIBUTE3,
572 ATTRIBUTE4 = P_ATTRIBUTE4,
573 ATTRIBUTE5 = P_ATTRIBUTE5,
574 ATTRIBUTE6 = P_ATTRIBUTE6,
575 ATTRIBUTE7 = P_ATTRIBUTE7,
576 ATTRIBUTE8 = P_ATTRIBUTE8,
577 ATTRIBUTE9 = P_ATTRIBUTE9,
578 ATTRIBUTE10 = P_ATTRIBUTE10,
579 CONTEXT = P_CONTEXT,
580 EFFECTIVE_START_DATE = decode(P_EFFECTIVE_START_DATE, FND_API.G_MISS_DATE,
581 null, P_EFFECTIVE_START_DATE),
582 EFFECTIVE_END_DATE = decode(P_EFFECTIVE_END_DATE, FND_API.G_MISS_DATE,
583 null, P_EFFECTIVE_END_DATE),
584 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
585 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
586 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
587 where ENTITY_ID = P_ENTITY_ID
588 ;
589 if (sql%notfound) then
590 raise FND_API.G_EXC_ERROR;
591 end if;
592 --
593 IF FND_API.To_Boolean ( p_commit ) THEN
594 COMMIT WORK;
595 END iF;
596 --
597 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
598 p_data => p_msg_data );
599 --
600 EXCEPTION
601 --
602 WHEN FND_API.G_EXC_ERROR THEN
603 --
604 ROLLBACK TO Update_Row_Pvt ;
605 p_return_status := FND_API.G_RET_STS_ERROR;
606 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
607 p_data => p_msg_data );
608 --
609 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
610 --
611 ROLLBACK TO Update_Row_Pvt ;
612 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
613 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
614 p_data => p_msg_data );
615 --
616 WHEN OTHERS THEN
617 --
618 ROLLBACK TO Update_Row_Pvt ;
619 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 --
621 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
622 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
623 l_api_name);
624 END if;
625 --
626 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
627 p_data => p_msg_data );
628 --
629 END Update_Row;
630
631 procedure ADD_ROW(
632 p_api_version IN NUMBER,
633 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
634 p_commit IN VARCHAR2 := FND_API.G_FALSE,
635 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
636 p_return_status OUT NOCOPY VARCHAR2,
637 p_msg_count OUT NOCOPY NUMBER,
638 p_msg_data OUT NOCOPY VARCHAR2,
639 --
640 P_ROWID in OUT NOCOPY VARCHAR2,
641 P_ENTITY_ID in NUMBER,
642 P_ENTITY_TYPE in VARCHAR2,
643 P_ENTITY_SUBTYPE in VARCHAR2,
644 P_NAME in VARCHAR2,
645 P_DESCRIPTION in VARCHAR2,
646 P_DATA_EXTRACT_ID in NUMBER,
647 P_SET_OF_BOOKS_ID in NUMBER,
648 P_BUDGET_GROUP_ID in NUMBER :=FND_API.G_MISS_NUM,
649 P_ALLOCATION_TYPE in VARCHAR2,
650 P_BUDGET_YEAR_TYPE_ID in NUMBER,
651 P_BALANCE_TYPE in VARCHAR2,
652 P_PARAMETER_AUTOINC_RULE in VARCHAR2,
653 P_PARAMETER_COMPOUND_ANNUALLY in VARCHAR2,
654 P_CURRENCY_CODE in VARCHAR2,
655 P_FTE_CONSTRAINT in VARCHAR2,
656 P_CONSTRAINT_DETAILED_FLAG in VARCHAR2,
657 /* Budget Revision Rules Enhancement Start */
658 P_APPLY_ACCOUNT_SET_FLAG in VARCHAR2,
659 P_BALANCE_ACCOUNT_SET_FLAG in VARCHAR2,
660 /* Budget Revision Rules Enhancement End */
661 P_ATTRIBUTE1 in VARCHAR2,
662 P_ATTRIBUTE2 in VARCHAR2,
666 P_ATTRIBUTE6 in VARCHAR2,
663 P_ATTRIBUTE3 in VARCHAR2,
664 P_ATTRIBUTE4 in VARCHAR2,
665 P_ATTRIBUTE5 in VARCHAR2,
667 P_ATTRIBUTE7 in VARCHAR2,
668 P_ATTRIBUTE8 in VARCHAR2,
669 P_ATTRIBUTE9 in VARCHAR2,
670 P_ATTRIBUTE10 in VARCHAR2,
671 P_CONTEXT in VARCHAR2,
672 P_EFFECTIVE_START_DATE in DATE := FND_API.G_MISS_DATE,
673 P_EFFECTIVE_END_DATE in DATE := FND_API.G_MISS_DATE,
674 p_Last_Update_Date DATE,
675 p_Last_Updated_By NUMBER,
676 p_Last_Update_Login NUMBER,
677 p_Created_By NUMBER,
678 p_Creation_Date DATE
679 ) IS
680
681 cursor c is
682 select rowid
683 from psb_entity
684 where entity_id = p_entity_id;
685 dummy c%rowtype;
686
687 l_api_name CONSTANT varchar2(30) := 'Add Row';
688 l_api_version CONSTANT number := 1.0;
689
690 BEGIN
691
692 SAVEPOINT Add_Row;
693 --
694 -- Initialize message list if p_init_msg_list is set to TRUE.
695 --
696 if FND_API.to_Boolean (p_init_msg_list) then
697 FND_MSG_PUB.initialize;
698 end if;
699 --
700 p_return_status := FND_API.G_RET_STS_SUCCESS ;
701 --
702 open c;
703 fetch c into dummy;
704 if (c%notfound) then
705 close c;
706
707 INSERT_ROW (
708 p_api_version,
709 p_init_msg_list,
710 p_commit,
711 p_validation_level,
712 p_return_status,
713 p_msg_count,
714 p_msg_data,
715
716 p_rowid ,
717 p_entity_id ,
718 p_entity_type ,
719 p_entity_subtype ,
720 p_name ,
721 p_description ,
722 p_data_extract_id ,
723 p_set_of_books_id ,
724 p_budget_group_id,
725 p_allocation_type ,
726 p_budget_year_type_id ,
727 p_balance_type ,
728 p_parameter_autoinc_rule ,
729 p_parameter_compound_annually ,
730 p_currency_code ,
731 p_fte_constraint ,
732 p_constraint_detailed_flag ,
733 /* Budget Revision Rules Enhancement Start */
734 p_apply_account_set_flag ,
735 p_balance_account_set_flag ,
736 /* Budget Revision Rules Enhancement End */
737 p_attribute1 ,
738 p_attribute2 ,
739 p_attribute3 ,
740 p_attribute4 ,
741 p_attribute5 ,
742 p_attribute6 ,
743 p_attribute7 ,
744 p_attribute8 ,
745 p_attribute9 ,
746 p_attribute10 ,
747 p_context ,
748 p_effective_start_date,
749 p_effective_end_date,
750 p_last_update_date ,
751 p_last_updated_by ,
752 p_last_update_login ,
753 p_created_by ,
754 p_creation_date);
755 return;
756 end if;
757 close c;
758
759 UPDATE_ROW(
760 p_api_version,
761 p_init_msg_list,
762 p_commit,
763 p_validation_level,
764 p_return_status,
765 p_msg_count,
766 p_msg_data,
767
768 p_entity_id ,
769 p_entity_type ,
770 p_entity_subtype ,
771 p_name ,
772 p_description ,
773 p_data_extract_id ,
774 p_set_of_books_id ,
775 p_budget_group_id,
776 p_allocation_type ,
777 p_budget_year_type_id ,
778 p_balance_type ,
779 p_parameter_autoinc_rule ,
780 p_parameter_compound_annually ,
781 p_currency_code ,
782 p_fte_constraint ,
783 p_constraint_detailed_flag ,
784 /* Budget Revision Rules Enhancement Start */
785 p_apply_account_set_flag ,
786 p_balance_account_set_flag ,
787 /* Budget Revision Rules Enhancement End */
788 p_attribute1 ,
789 p_attribute2 ,
790 p_attribute3 ,
791 p_attribute4 ,
792 p_attribute5 ,
793 p_attribute6 ,
794 p_attribute7 ,
795 p_attribute8 ,
796 p_attribute9 ,
797 p_attribute10 ,
798 p_context ,
799 p_effective_start_date,
800 p_effective_end_date,
801 p_last_update_date ,
802 p_last_updated_by ,
803 p_last_update_login );
804
805 --
806 -- Standard check of p_commit.
807 if FND_API.to_Boolean (p_commit) then
808 commit work;
809 end if;
810 -- Standard call to get message count and if count is 1, get message info.
811 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
812 p_data => p_msg_data);
813 --
814
815 EXCEPTION
816 --
817 when FND_API.G_EXC_ERROR then
818 --
819 rollback to ADD_ROW ;
820 p_return_status := FND_API.G_RET_STS_ERROR;
821 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
822 p_data => p_msg_data);
823 --
824 when FND_API.G_EXC_UNEXPECTED_ERROR then
825 --
826 rollback to ADD_ROW ;
827 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
828 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
832 --
829 p_data => p_msg_data);
830 --
831 when OTHERS then
833 rollback to ADD_ROW ;
834 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
835 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
836 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
837 l_api_name);
838 END if;
839 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
840 p_data => p_msg_data);
841 --
842 END ADD_ROW;
843
844
845 procedure DELETE_ROW (
846 p_api_version IN NUMBER,
847 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
848 p_commit IN VARCHAR2 := FND_API.G_FALSE,
849 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
850 p_return_status OUT NOCOPY VARCHAR2,
851 p_msg_count OUT NOCOPY NUMBER,
852 p_msg_data OUT NOCOPY VARCHAR2,
853 --
854 P_ENTITY_ID in NUMBER
855 ) is
856 --
857 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
858 l_api_version CONSTANT NUMBER := 1.0;
859 --
860 BEGIN
861 --
862 SAVEPOINT Delete_Row_Pvt ;
863 --
864 IF NOT FND_API.Compatible_API_Call ( l_api_version,
865 p_api_version,
866 l_api_name,
867 G_PKG_NAME )
868 THEN
869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
870 END IF;
871 --
872
873 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
874 FND_MSG_PUB.initialize ;
875 END IF;
876 --
877 p_return_status := FND_API.G_RET_STS_SUCCESS ;
878 --
879 delete from PSB_ENTITY
880 where ENTITY_ID = P_ENTITY_ID;
881 if (sql%notfound) then
882 raise no_data_found;
883 end if;
884 --
885 IF FND_API.To_Boolean ( p_commit ) THEN
886 COMMIT WORK;
887 END iF;
888 --
889 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
890 p_data => p_msg_data );
891
892 EXCEPTION
893 --
894 WHEN FND_API.G_EXC_ERROR THEN
895 --
896 ROLLBACK TO Delete_Row_Pvt ;
897 p_return_status := FND_API.G_RET_STS_ERROR;
898 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
899 p_data => p_msg_data );
900 --
901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
902 --
903 ROLLBACK TO Delete_Row_Pvt ;
904 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
905 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
906 p_data => p_msg_data );
907 --
908 WHEN OTHERS THEN
909 --
910 ROLLBACK TO Delete_Row_Pvt ;
911 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 --
913 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
914 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
915 l_api_name);
916 END if;
917 --
918 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
919 p_data => p_msg_data );
920 --
921 END Delete_Row;
922
923
924 end PSB_ENTITY_PVT;