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