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