[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_DISTRIBUTION_RULES_PVT
Source
1 PACKAGE BODY PSB_WS_Distribution_Rules_PVT AS
2 /* $Header: PSBVWDRB.pls 120.2 2005/07/13 11:30:49 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WS_Distribution_Rules_PVT';
5
6 g_chr10 CONSTANT VARCHAR2(1) := FND_GLOBAL.Newline;
7
8 G_DBUG VARCHAR2(2000) := 'start';
9
10 PROCEDURE Pass_Rule_ID ( p_rule_id IN NUMBER) AS
11 BEGIN
12 g_rule_id := p_rule_id;
13 END Pass_Rule_ID;
14
15 /*=======================================================================+
16 | PROCEDURE Insert_Row |
17 +=======================================================================*/
18
19 PROCEDURE Insert_Row
20 (
21 p_api_version IN NUMBER,
22 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
23 p_commit IN VARCHAR2 := FND_API.G_FALSE,
24 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
25 p_return_status OUT NOCOPY VARCHAR2,
26 p_msg_count OUT NOCOPY NUMBER,
27 p_msg_data OUT NOCOPY VARCHAR2,
28 p_Row_Id IN OUT NOCOPY VARCHAR2,
29 --
30 p_Distribution_Rule_Line_Id IN NUMBER,
31 p_Distribution_Rule_Id IN NUMBER,
32 p_Budget_Group_Id IN NUMBER,
33 p_distribute_flag IN VARCHAR2,
34 p_distribute_all_level_flag IN VARCHAR2,
35 p_download_flag IN VARCHAR2,
36 p_download_all_level_flag IN VARCHAR2,
37 p_year_category_type IN VARCHAR2,
38 p_attribute1 in varchar2,
39 p_attribute2 in varchar2,
40 p_attribute3 in varchar2,
41 p_attribute4 in varchar2,
42 p_attribute5 in varchar2,
43 p_attribute6 in varchar2,
44 p_attribute7 in varchar2,
45 p_attribute8 in varchar2,
46 p_attribute9 in varchar2,
47 p_attribute10 in varchar2,
48 p_context in varchar2,
49 p_mode in varchar2
50
51 )
52 IS
53
54 CURSOR C IS
55 SELECT rowid
56 FROM psb_ws_distribution_rule_lines
57 WHERE distribution_rule_line_id = p_distribution_rule_line_id ;
58
59 --
60 P_LAST_UPDATE_DATE DATE;
61 P_LAST_UPDATED_BY NUMBER;
62 P_LAST_UPDATE_LOGIN NUMBER;
63 -- variables --
64 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
65 l_api_version CONSTANT NUMBER := 1.0;
66 l_return_status VARCHAR2(1);
67 --
68 BEGIN
69 --
70 SAVEPOINT Insert_Row_Pvt ;
71 --
72 IF NOT FND_API.Compatible_API_Call ( l_api_version,
73 p_api_version,
74 l_api_name,
75 G_PKG_NAME )
76 THEN
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
78 END IF;
79 --
80
81 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
82 FND_MSG_PUB.initialize ;
83 END IF;
84 --
85 p_return_status := FND_API.G_RET_STS_SUCCESS ;
86 --
87 P_LAST_UPDATE_DATE := SYSDATE;
88 if(P_MODE = 'I') then
89 P_LAST_UPDATED_BY := 1;
90 P_LAST_UPDATE_LOGIN := 0;
91 elsif (P_MODE = 'R') then
92 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
93 if P_LAST_UPDATED_BY is NULL then
94 P_LAST_UPDATED_BY := -1;
95 end if;
96 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
97 if P_LAST_UPDATE_LOGIN is NULL then
98 P_LAST_UPDATE_LOGIN := -1;
99 end if;
100 else
101 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
102 FND_MSG_PUB.Add ;
103 raise FND_API.G_EXC_ERROR;
104 end if;
105 --
106
107 INSERT INTO psb_ws_distribution_rule_lines
108 ( distribution_rule_line_id,
109 distribution_rule_id,
110 budget_group_id,
111 distribute_flag,
112 distribute_all_level_flag,
113 download_flag ,
114 download_all_level_flag ,
115 year_category_type,
116 attribute1,
117 attribute2,
118 attribute3,
119 attribute4,
120 attribute5,
121 attribute6,
122 attribute7,
123 attribute8,
124 attribute9,
125 attribute10,
126 context,
127 creation_date,
128 created_by,
129 last_update_date,
130 last_updated_by,
131 last_update_login
132 )
133 VALUES
134 ( p_distribution_rule_line_id,
135 p_Distribution_rule_id,
136 p_budget_group_id,
137 p_distribute_flag,
138 p_distribute_all_level_flag,
139 p_download_flag ,
140 p_download_all_level_flag ,
141 p_year_category_type,
142 p_attribute1,
143 p_attribute2,
144 p_attribute3,
145 p_attribute4,
146 p_attribute5,
147 p_attribute6,
148 p_attribute7,
149 p_attribute8,
150 p_attribute9,
151 p_attribute10,
152 p_context,
153 p_last_update_date,
154 p_last_updated_by,
155 p_last_update_date,
156 p_last_updated_by,
157 p_last_update_login
158
159
160 );
161 OPEN C;
162 FETCH C INTO p_Row_Id;
163 IF (C%NOTFOUND) THEN
164 CLOSE C;
165 RAISE FND_API.G_EXC_ERROR ;
166 END IF;
167 CLOSE C;
168 --
169
170 --
171 IF FND_API.To_Boolean ( p_commit ) THEN
172 COMMIT WORK;
173 END iF;
174 --
175 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
176 p_data => p_msg_data );
177 --
178 EXCEPTION
179 --
180 WHEN FND_API.G_EXC_ERROR THEN
181 --
182 ROLLBACK TO Insert_Row_Pvt ;
183 p_return_status := FND_API.G_RET_STS_ERROR;
184 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
185 p_data => p_msg_data );
186 --
187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
188 --
189 ROLLBACK TO Insert_Row_Pvt ;
190 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
191 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
192 p_data => p_msg_data );
193 --
194 WHEN OTHERS THEN
195 --
196 ROLLBACK TO Insert_Row_Pvt ;
197 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 --
199 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
200 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
201 l_api_name);
202 END if;
203 --
204 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
205 p_data => p_msg_data );
206 --
207 END Insert_Row;
208 /*-------------------------------------------------------------------------*/
209
210
211
212 /*==========================================================================+
213 | PROCEDURE Lock_Row |
214 +==========================================================================*/
215
216 PROCEDURE Lock_Row
217 (
218 p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
220 p_commit IN VARCHAR2 := FND_API.G_FALSE,
221 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
222 p_return_status OUT NOCOPY VARCHAR2,
223 p_msg_count OUT NOCOPY NUMBER,
224 p_msg_data OUT NOCOPY VARCHAR2,
225 --
226 p_Distribution_Rule_Line_Id IN NUMBER,
227 p_Distribution_Rule_Id IN NUMBER,
228 p_Budget_Group_Id IN NUMBER,
229 p_distribute_flag IN VARCHAR2,
230 p_distribute_all_level_flag IN VARCHAR2,
231 p_download_flag IN VARCHAR2,
232 p_download_all_level_flag IN VARCHAR2,
233 p_year_category_type IN VARCHAR2,
234 p_attribute1 IN varchar2,
235 p_attribute2 IN varchar2,
236 p_attribute3 IN varchar2,
237 p_attribute4 IN varchar2,
238 p_attribute5 IN varchar2,
239 p_attribute6 IN varchar2,
240 p_attribute7 IN varchar2,
241 p_attribute8 IN varchar2,
242 p_attribute9 IN varchar2,
243 p_attribute10 IN varchar2,
244 p_context IN varchar2,
245 --
246 p_row_locked OUT NOCOPY VARCHAR2
247 )
248 IS
249 --
250 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
251 l_api_version CONSTANT NUMBER := 1.0;
252 l_return_status VARCHAR2(1);
253
254 --
255 Counter NUMBER;
256 CURSOR C IS
257 SELECT distribution_rule_line_id,
258 distribution_rule_id,
259 budget_group_id,
260 distribute_flag,
261 distribute_all_level_flag,
262 download_flag,
263 download_all_level_flag ,
264 year_category_type,
265 attribute1,
266 attribute2,
267 attribute3,
268 attribute4,
269 attribute5,
270 attribute6,
271 attribute7,
272 attribute8,
273 attribute9,
274 attribute10,
275 context
276 FROM psb_ws_distribution_rule_lines
277 WHERE distribution_rule_line_id = p_distribution_rule_line_id
278 FOR UPDATE of distribution_rule_id NOWAIT;
279 Recinfo C%ROWTYPE;
280
281 BEGIN
282 --
283 SAVEPOINT Lock_Row_Pvt ;
284 --
285 IF NOT FND_API.Compatible_API_Call ( l_api_version,
286 p_api_version,
287 l_api_name,
288 G_PKG_NAME )
289 THEN
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
291 END IF;
292 --
293
294 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
295 FND_MSG_PUB.initialize ;
296 END IF;
297 --
298 p_return_status := FND_API.G_RET_STS_SUCCESS ;
299 p_row_locked := FND_API.G_TRUE ;
300 --
301 OPEN C;
302 --
303 FETCH C INTO Recinfo;
304 IF (C%NOTFOUND) then
305 CLOSE C;
306 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
307 FND_MSG_PUB.Add;
308 -- For bug # 2396565 : Following statement comented since Cursor is already closed
309 -- CLOSE c;
310 RAISE FND_API.G_EXC_ERROR ;
311 END IF;
312 CLOSE C;
313 IF
314 (
315 ( Recinfo.distribution_rule_line_id = p_distribution_rule_line_id )
316 AND ( Recinfo.distribution_rule_id = p_distribution_rule_id )
317 AND ( Recinfo.budget_group_id = p_budget_group_id)
318 AND ( Recinfo.distribute_flag = p_distribute_flag )
319 --
320 AND ((recinfo.distribute_all_level_flag = P_distribute_all_level_flag)
321 OR ((recinfo.distribute_all_level_flag is null)
322 AND (P_distribute_all_level_flag is null)))
323 AND ((recinfo.download_flag = P_download_flag)
324 OR ((recinfo.download_flag is null)
325 AND (P_download_flag is null)))
326 AND ((recinfo.download_all_level_flag = P_download_all_level_flag)
327 OR ((recinfo.download_all_level_flag is null)
328 AND (P_download_all_level_flag is null)))
329 AND ((recinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
330 OR ((recinfo.ATTRIBUTE1 is null)
331 AND (P_ATTRIBUTE1 is null)))
332 AND ((recinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
333 OR ((recinfo.ATTRIBUTE2 is null)
334 AND (P_ATTRIBUTE2 is null)))
335 AND ((recinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
336 OR ((recinfo.ATTRIBUTE3 is null)
337 AND (P_ATTRIBUTE3 is null)))
338 AND ((recinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
339 OR ((recinfo.ATTRIBUTE4 is null)
340 AND (P_ATTRIBUTE4 is null)))
341 AND ((recinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
342 OR ((recinfo.ATTRIBUTE5 is null)
343 AND (P_ATTRIBUTE5 is null)))
344 AND ((recinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
345 OR ((recinfo.ATTRIBUTE6 is null)
346 AND (P_ATTRIBUTE6 is null)))
347 AND ((recinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
348 OR ((recinfo.ATTRIBUTE7 is null)
349 AND (P_ATTRIBUTE7 is null)))
350 AND ((recinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
351 OR ((recinfo.ATTRIBUTE8 is null)
352 AND (P_ATTRIBUTE8 is null)))
353 AND ((recinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
354 OR ((recinfo.ATTRIBUTE9 is null)
355 AND (P_ATTRIBUTE9 is null)))
356 AND ((recinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
357 OR ((recinfo.ATTRIBUTE10 is null)
358 AND (P_ATTRIBUTE10 is null)))
359 AND ((recinfo.CONTEXT = P_CONTEXT)
360 OR ((recinfo.CONTEXT is null)
361 AND (P_CONTEXT is null)))
362 --
363 )
364 THEN
365 NULL ;
366 ELSE
367 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED') ;
368 FND_MSG_PUB.Add ;
369 RAISE FND_API.G_EXC_ERROR ;
370 END IF;
371
372 --
373 /*--
374 IF FND_API.To_Boolean ( p_commit ) THEN
375 COMMIT WORK;
376 END iF;
377 --
378 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
379 p_data => p_msg_data );
380 */
381 --
382 EXCEPTION
383 --
384 WHEN App_Exception.Record_Lock_Exception THEN
385 --
386 ROLLBACK TO Lock_Row_Pvt ;
387 p_row_locked := FND_API.G_FALSE ;
388 p_return_status := FND_API.G_RET_STS_ERROR ;
389 --
390 WHEN FND_API.G_EXC_ERROR THEN
391 --
392 ROLLBACK TO Lock_Row_Pvt ;
393 p_return_status := FND_API.G_RET_STS_ERROR;
394 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
395 p_data => p_msg_data );
396 --
397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
398 --
399 ROLLBACK TO Lock_Row_Pvt ;
400 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
402 p_data => p_msg_data );
403 --
404 WHEN OTHERS THEN
405 --
406 ROLLBACK TO Lock_Row_Pvt ;
407 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 --
409 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
410 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
411 l_api_name);
412 END if;
413 --
414 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
415 p_data => p_msg_data );
416 --
417 END Lock_Row;
418 /* ----------------------------------------------------------------------- */
419
420
421
422
423 /*==========================================================================+
424 | PROCEDURE Update_Row |
425 +==========================================================================*/
426
427 PROCEDURE Update_Row
428 (
429 p_api_version IN NUMBER,
430 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
431 p_commit IN VARCHAR2 := FND_API.G_FALSE,
432 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
433 p_return_status OUT NOCOPY VARCHAR2,
434 p_msg_count OUT NOCOPY NUMBER,
435 p_msg_data OUT NOCOPY VARCHAR2,
436 --
437 p_Distribution_Rule_Line_Id IN NUMBER,
438 p_Distribution_Rule_Id IN NUMBER,
439 p_Budget_Group_Id IN NUMBER,
440 p_distribute_flag IN VARCHAR2,
441 p_distribute_all_level_flag IN VARCHAR2,
442 p_download_flag IN VARCHAR2,
443 p_download_all_level_flag IN VARCHAR2,
444 p_year_category_type IN VARCHAR2,
448 p_attribute4 in varchar2,
445 p_attribute1 in varchar2,
446 p_attribute2 in varchar2,
447 p_attribute3 in varchar2,
449 p_attribute5 in varchar2,
450 p_attribute6 in varchar2,
451 p_attribute7 in varchar2,
452 p_attribute8 in varchar2,
453 p_attribute9 in varchar2,
454 p_attribute10 in varchar2,
455 p_context in varchar2,
456 p_mode in varchar2
457
458 )
459 IS
460 P_LAST_UPDATE_DATE DATE;
461 P_LAST_UPDATED_BY NUMBER;
462 P_LAST_UPDATE_LOGIN NUMBER;
463 --
464 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
465 l_api_version CONSTANT NUMBER := 1.0;
466 l_return_status VARCHAR2(1);
467 --
468 BEGIN
469 --
470 SAVEPOINT Update_Row_Pvt ;
471 --
472 IF NOT FND_API.Compatible_API_Call ( l_api_version,
473 p_api_version,
474 l_api_name,
475 G_PKG_NAME )
476 THEN
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
478 END IF;
479 --
480
481 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
482 FND_MSG_PUB.initialize ;
483 END IF;
484 --
485 p_return_status := FND_API.G_RET_STS_SUCCESS ;
486 --
487
488 P_LAST_UPDATE_DATE := SYSDATE;
489 if(P_MODE = 'I') then
490 P_LAST_UPDATED_BY := 1;
491 P_LAST_UPDATE_LOGIN := 0;
492 elsif (P_MODE = 'R') then
493 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
494 if P_LAST_UPDATED_BY is NULL then
495 P_LAST_UPDATED_BY := -1;
496 end if;
497 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
498 if P_LAST_UPDATE_LOGIN is NULL then
499 P_LAST_UPDATE_LOGIN := -1;
500 end if;
501 else
502 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
503 FND_MSG_PUB.Add ;
504 raise FND_API.G_EXC_ERROR ;
505 end if;
506 --
507 UPDATE psb_ws_distribution_rule_lines
508 SET
509 distribution_rule_id = p_distribution_rule_id,
510 Budget_Group_Id = p_Budget_Group_Id ,
511 distribute_flag = p_distribute_flag ,
512 distribute_all_level_flag = p_distribute_all_level_flag ,
513 download_flag = p_download_flag ,
514 download_all_level_flag = p_download_all_level_flag ,
515 year_category_type = p_year_category_type ,
516 attribute1 = p_attribute1,
517 attribute2 = p_attribute2,
518 attribute3 = p_attribute3,
519 attribute4 = p_attribute4,
520 attribute5 = p_attribute5,
521 attribute6 = p_attribute6,
522 attribute7 = p_attribute7,
523 attribute8 = p_attribute8,
524 attribute9 = p_attribute9,
525 attribute10 = p_attribute10,
526 context = p_context,
527 last_update_date = p_last_update_date,
528 last_updated_by = p_last_updated_by,
529 last_update_login = p_last_update_login
530 WHERE distribution_rule_line_id = p_distribution_rule_line_id;
531
532 IF (SQL%NOTFOUND) THEN
533 RAISE FND_API.G_EXC_ERROR ;
534 END IF;
535
536 --
537 --
538 -- Standard check of p_commit.
539
540 IF FND_API.To_Boolean ( p_commit ) THEN
541 COMMIT WORK;
542 END iF;
543 --
544 -- Standard call to get message count and if count is 1, get message info.
545 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
546 p_data => p_msg_data );
547 --
548 EXCEPTION
549
550 WHEN FND_API.G_EXC_ERROR THEN
551 --
552 ROLLBACK TO Update_Row_Pvt ;
553 p_return_status := FND_API.G_RET_STS_ERROR;
554 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
555 p_data => p_msg_data );
556 --
557 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
558 --
559 ROLLBACK TO Update_Row_Pvt ;
560 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
562 p_data => p_msg_data );
563 --
564 WHEN OTHERS THEN
565 --
566 ROLLBACK TO Update_Row_Pvt ;
567 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568 --
569 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
570 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
571 l_api_name);
572 END if;
573 --
574 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
575 p_data => p_msg_data );
576 --
577 END Update_Row;
578 /* ----------------------------------------------------------------------- */
579
580
581
582
583 /*==========================================================================+
584 | PROCEDURE Delete_Row |
585 +==========================================================================*/
586
587 PROCEDURE Delete_Row
588 (
589 p_api_version IN NUMBER,
590 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
591 p_commit IN VARCHAR2 := FND_API.G_FALSE,
592 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
593 p_return_status OUT NOCOPY VARCHAR2,
594 p_msg_count OUT NOCOPY NUMBER,
598 )
595 p_msg_data OUT NOCOPY VARCHAR2,
596 --
597 p_Distribution_Rule_Line_Id IN NUMBER
599 IS
600 --
601 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
602 l_api_version CONSTANT NUMBER := 1.0;
603 --
604 BEGIN
605 --
606 SAVEPOINT Delete_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 p_return_status := FND_API.G_RET_STS_SUCCESS ;
622 --
623
624 --
625 -- Deleting the record in psb_ws_distribution_rule_lines.
626 --
627 DELETE psb_ws_distribution_rule_lines
628 WHERE distribution_rule_line_id = p_distribution_rule_line_id;
629
630 IF (SQL%NOTFOUND) THEN
631 RAISE NO_DATA_FOUND ;
632 END IF;
633
634 --
635 IF FND_API.To_Boolean ( p_commit ) THEN
636 COMMIT WORK;
637 END iF;
638 --
639 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
640 p_data => p_msg_data );
641
642 EXCEPTION
643 --
644 WHEN FND_API.G_EXC_ERROR THEN
645 --
646 ROLLBACK TO Delete_Row_Pvt ;
647 p_return_status := FND_API.G_RET_STS_ERROR;
648 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
649 p_data => p_msg_data );
650 --
651 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
652 --
653 ROLLBACK TO Delete_Row_Pvt ;
654 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
656 p_data => p_msg_data );
657 --
658 WHEN OTHERS THEN
659 --
660 ROLLBACK TO Delete_Row_Pvt ;
661 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
662 --
663 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
664 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
665 l_api_name);
666 END if;
667 --
668 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
669 p_data => p_msg_data );
670 --
671 END Delete_Row;
672 /* ----------------------------------------------------------------------- */
673
674
675
676
677
678 /*==========================================================================+
679 | PROCEDURE Check_Unique |
680 +==========================================================================*/
681
682 PROCEDURE Check_Unique
683 (
684 p_api_version IN NUMBER,
685 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
686 p_commit IN VARCHAR2 := FND_API.G_FALSE,
687 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
688 p_return_status OUT NOCOPY VARCHAR2,
689 p_msg_count OUT NOCOPY NUMBER,
690 p_msg_data OUT NOCOPY VARCHAR2,
691 --
692 p_Row_Id IN VARCHAR2,
693 p_Name IN VARCHAR2,
694 p_Return_Value OUT NOCOPY VARCHAR2
695 )
696 IS
697 --
698 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
699 l_api_version CONSTANT NUMBER := 1.0;
700 --
701 l_tmp VARCHAR2(1);
702
703 CURSOR c IS
704 SELECT '1'
705 FROM psb_ws_distribution_rules
706 WHERE name = p_name
707 AND ( (p_Row_Id IS NULL)
708 OR (RowId <> p_Row_Id) );
709 BEGIN
710 --
711 SAVEPOINT Check_Unique_Pvt ;
712 --
713 IF NOT FND_API.Compatible_API_Call ( l_api_version,
714 p_api_version,
715 l_api_name,
716 G_PKG_NAME )
717 THEN
718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
719 END IF;
720 --
721
722 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
723 FND_MSG_PUB.initialize ;
724 END IF;
725 --
726 p_return_status := FND_API.G_RET_STS_SUCCESS ;
727 --
728
729 -- Checking the Psb_set_relations table for references.
730 OPEN c;
731 FETCH c INTO l_tmp;
732 --
733 -- p_Return_Value tells whether references exist or not.
734 --
735 IF l_tmp IS NULL THEN
736 p_Return_Value := 'FALSE';
737 ELSE
738 p_Return_Value := 'TRUE';
739 END IF;
740
741 CLOSE c;
742 --
743 IF FND_API.To_Boolean ( p_commit ) THEN
744 COMMIT WORK;
745 END iF;
746 --
747 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
748 p_data => p_msg_data );
749 --
750 EXCEPTION
751 --
752 WHEN FND_API.G_EXC_ERROR THEN
753 --
754 ROLLBACK TO Check_Unique_Pvt ;
755 p_return_status := FND_API.G_RET_STS_ERROR;
756 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
757 p_data => p_msg_data );
761 ROLLBACK TO Check_Unique_Pvt ;
758 --
759 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
760 --
762 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
763 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
764 p_data => p_msg_data );
765 --
766 WHEN OTHERS THEN
767 --
768 ROLLBACK TO Check_Unique_Pvt ;
769 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
770 --
771 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
772 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
773 l_api_name);
774 END if;
775 --
776 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
777 p_data => p_msg_data );
778 --
779 END Check_Unique;
780 /* ----------------------------------------------------------------------- */
781
782
783
784 PROCEDURE Distribution_Insert_Row
785 (
786 p_api_version IN NUMBER,
787 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
788 p_commit IN VARCHAR2 := FND_API.G_FALSE,
789 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
790 p_return_status OUT NOCOPY VARCHAR2,
791 p_msg_count OUT NOCOPY NUMBER,
792 p_msg_data OUT NOCOPY VARCHAR2,
793 p_Row_Id IN OUT NOCOPY VARCHAR2,
794 --
795 p_Distribution_Id IN NUMBER,
796 p_Distribution_Rule_Id IN NUMBER,
797 p_Worksheet_Id IN NUMBER,
798 p_distribution_date IN DATE,
799 p_distributed_flag IN VARCHAR2,
800 p_distribution_instructions IN VARCHAR2,
801 p_distribution_option_flag IN VARCHAR2,
802 p_revision_option_flag IN VARCHAR2,
803 p_mode IN VARCHAR2
804 )
805 IS
806
807 CURSOR C IS
808 SELECT rowid
809 FROM psb_ws_distributions
810 WHERE distribution_id = p_distribution_id ;
811
812 --
813 P_LAST_UPDATE_DATE DATE;
814 P_LAST_UPDATED_BY NUMBER;
815 P_LAST_UPDATE_LOGIN NUMBER;
816 -- variables --
817 l_api_name CONSTANT VARCHAR2(30) := 'Distribution_Insert_Row';
818 l_api_version CONSTANT NUMBER := 1.0;
819 l_return_status VARCHAR2(1);
820 --
821 BEGIN
822 --
823 SAVEPOINT Distribution_Insert_Row_Pvt ;
824 --
825 IF NOT FND_API.Compatible_API_Call ( l_api_version,
826 p_api_version,
827 l_api_name,
828 G_PKG_NAME )
829 THEN
830 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
831 END IF;
832 --
833
834 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
835 FND_MSG_PUB.initialize ;
836 END IF;
837 --
838 p_return_status := FND_API.G_RET_STS_SUCCESS ;
839 --
840 P_LAST_UPDATE_DATE := SYSDATE;
841 if(P_MODE = 'I') then
842 P_LAST_UPDATED_BY := 1;
843 P_LAST_UPDATE_LOGIN := 0;
844 elsif (P_MODE = 'R') then
845 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
846 if P_LAST_UPDATED_BY is NULL then
847 P_LAST_UPDATED_BY := -1;
848 end if;
849 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
850 if P_LAST_UPDATE_LOGIN is NULL then
851 P_LAST_UPDATE_LOGIN := -1;
852 end if;
853 else
854 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
855 FND_MSG_PUB.Add ;
856 raise FND_API.G_EXC_ERROR;
857 end if;
858 --
859
860 INSERT INTO psb_ws_distributions
861 ( distribution_id,
862 distribution_rule_id,
863 worksheet_id,
864 distribution_date,
865 distributed_flag,
866 distribution_instructions,
867 distribution_option_flag,
868 revision_option_flag,
869 creation_date,
870 created_by,
871 last_update_date,
872 last_updated_by,
873 last_update_login
874 )
875 VALUES
876 ( p_distribution_id,
877 p_Distribution_rule_id,
878 p_worksheet_id,
879 p_distribution_date,
880 p_distributed_flag,
881 p_distribution_instructions,
882 p_distribution_option_flag,
883 p_revision_option_flag,
884 p_last_update_date,
885 p_last_updated_by,
886 p_last_update_date,
887 p_last_updated_by,
888 p_last_update_login
889
890 );
891 OPEN C;
892 FETCH C INTO p_Row_Id;
893 IF (C%NOTFOUND) THEN
894 CLOSE C;
895 RAISE FND_API.G_EXC_ERROR ;
896 END IF;
897 CLOSE C;
898 --
899
900 --
901 IF FND_API.To_Boolean ( p_commit ) THEN
902 COMMIT WORK;
903 END iF;
904 --
905 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
906 p_data => p_msg_data );
907 --
908 EXCEPTION
909 --
910 WHEN FND_API.G_EXC_ERROR THEN
911 --
912 ROLLBACK TO Distribution_Insert_Row_Pvt ;
913 p_return_status := FND_API.G_RET_STS_ERROR;
914 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
915 p_data => p_msg_data );
916 --
920 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
918 --
919 ROLLBACK TO Distribution_Insert_Row_Pvt ;
921 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
922 p_data => p_msg_data );
923 --
924 WHEN OTHERS THEN
925 --
926 ROLLBACK TO Distribution_Insert_Row_Pvt ;
927 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
928 --
929 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
930 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
931 l_api_name);
932 END if;
933 --
934 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
935 p_data => p_msg_data );
936 --
937 END Distribution_Insert_Row;
938 /*-------------------------------------------------------------------------*/
939
940 PROCEDURE Rules_Insert_Row
941 (
942 p_api_version IN NUMBER,
943 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
944 p_commit IN VARCHAR2 := FND_API.G_FALSE,
945 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
946 p_return_status OUT NOCOPY VARCHAR2,
947 p_msg_count OUT NOCOPY NUMBER,
948 p_msg_data OUT NOCOPY VARCHAR2,
949 p_Row_Id IN OUT NOCOPY VARCHAR2,
950 --
951 p_Distribution_Rule_Id IN NUMBER,
952 p_Budget_Group_Id IN NUMBER,
953 p_Name IN VARCHAR2,
954 p_mode in varchar2
955
956 )
957 IS
958
959 CURSOR C IS
960 SELECT rowid
961 FROM psb_ws_distribution_rules
962 WHERE distribution_rule_id = p_distribution_rule_id ;
963
964 --
965 P_LAST_UPDATE_DATE DATE;
966 P_LAST_UPDATED_BY NUMBER;
967 P_LAST_UPDATE_LOGIN NUMBER;
968 -- variables --
969 l_api_name CONSTANT VARCHAR2(30) := 'Rules_Insert_Row';
970 l_api_version CONSTANT NUMBER := 1.0;
971 l_return_status VARCHAR2(1);
972 --
973 BEGIN
974 --
975 SAVEPOINT Rules_Insert_Row_Pvt ;
976 --
977 IF NOT FND_API.Compatible_API_Call ( l_api_version,
978 p_api_version,
979 l_api_name,
980 G_PKG_NAME )
981 THEN
982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
983 END IF;
984 --
985
986 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
987 FND_MSG_PUB.initialize ;
988 END IF;
989 --
990 p_return_status := FND_API.G_RET_STS_SUCCESS ;
991 --
992 P_LAST_UPDATE_DATE := SYSDATE;
993 if(P_MODE = 'I') then
994 P_LAST_UPDATED_BY := 1;
995 P_LAST_UPDATE_LOGIN := 0;
996 elsif (P_MODE = 'R') then
997 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
998 if P_LAST_UPDATED_BY is NULL then
999 P_LAST_UPDATED_BY := -1;
1000 end if;
1001 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1002 if P_LAST_UPDATE_LOGIN is NULL then
1003 P_LAST_UPDATE_LOGIN := -1;
1004 end if;
1005 else
1006 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1007 FND_MSG_PUB.Add ;
1008 raise FND_API.G_EXC_ERROR;
1009 end if;
1010 --
1011
1012 INSERT INTO psb_ws_distribution_rules
1013 ( distribution_rule_id,
1014 name,
1015 budget_group_id,
1016 creation_date,
1017 created_by,
1018 last_update_date,
1019 last_updated_by,
1020 last_update_login
1021 )
1022 VALUES
1023 ( p_distribution_rule_id,
1024 p_Name,
1025 p_budget_group_id,
1026 p_last_update_date,
1027 p_last_updated_by,
1028 p_last_update_date,
1029 p_last_updated_by,
1030 p_last_update_login
1031
1032 );
1033 OPEN C;
1034 FETCH C INTO p_Row_Id;
1035 IF (C%NOTFOUND) THEN
1036 CLOSE C;
1037 RAISE FND_API.G_EXC_ERROR ;
1038 END IF;
1039 CLOSE C;
1040 --
1041
1042 --
1043 IF FND_API.To_Boolean ( p_commit ) THEN
1044 COMMIT WORK;
1045 END iF;
1046 --
1047 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1048 p_data => p_msg_data );
1049 --
1050 EXCEPTION
1051 --
1052 WHEN FND_API.G_EXC_ERROR THEN
1053 --
1054 ROLLBACK TO Rules_Insert_Row_Pvt ;
1055 p_return_status := FND_API.G_RET_STS_ERROR;
1056 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1057 p_data => p_msg_data );
1058 --
1059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1060 --
1061 ROLLBACK TO Rules_Insert_Row_Pvt ;
1062 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1063 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1064 p_data => p_msg_data );
1065 --
1066 WHEN OTHERS THEN
1067 --
1068 ROLLBACK TO Rules_Insert_Row_Pvt ;
1069 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1070 --
1071 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1072 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1073 l_api_name);
1074 END if;
1075 --
1076 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1080 /*==========================================================================+
1077 p_data => p_msg_data );
1078 --
1079 END Rules_Insert_Row;
1081 | PROCEDURE Delete_Row |
1082 +==========================================================================*/
1083
1084 PROCEDURE Rules_Delete_Row
1085 (
1086 p_api_version IN NUMBER,
1087 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1088 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1089 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1090 p_return_status OUT NOCOPY VARCHAR2,
1091 p_msg_count OUT NOCOPY NUMBER,
1092 p_msg_data OUT NOCOPY VARCHAR2,
1093 --
1094 p_Distribution_Rule_Id IN NUMBER
1095 )
1096 IS
1097 --
1098 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
1099 l_api_version CONSTANT NUMBER := 1.0;
1100 --
1101 l_ws_count NUMBER;
1102 BEGIN
1103 --
1104 SAVEPOINT Rules_Delete_Row_Pvt ;
1105 --
1106 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1107 p_api_version,
1108 l_api_name,
1109 G_PKG_NAME )
1110 THEN
1111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1112 END IF;
1113 --
1114
1115 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1116 FND_MSG_PUB.initialize ;
1117 END IF;
1118 --
1119 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1120 --
1121
1122 SELECT count(*) INTO l_ws_count
1123 FROM psb_ws_distributions
1124 WHERE distribution_rule_id = p_distribution_rule_id;
1125
1126 IF (l_ws_count <> 0) THEN
1127 FND_MESSAGE.SET_NAME('PSB', 'PSB_RULE_IS_DISTRIBUTED');
1128 FND_MSG_PUB.Add;
1129 RAISE FND_API.G_EXC_ERROR ;
1130 END IF;
1131 --
1132 -- Deleting the record in psb_ws_distribution_rule_lines and rules.
1133 --
1134 DELETE psb_ws_distribution_rules
1135 WHERE distribution_rule_id = p_distribution_rule_id;
1136
1137 DELETE psb_ws_distribution_rule_lines
1138 WHERE distribution_rule_id = p_distribution_rule_id;
1139
1140 IF (SQL%NOTFOUND) THEN
1141 RAISE NO_DATA_FOUND ;
1142 END IF;
1143
1144 --
1145 IF FND_API.To_Boolean ( p_commit ) THEN
1146 COMMIT WORK;
1147 END iF;
1148 --
1149 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1150 p_data => p_msg_data );
1151
1152 EXCEPTION
1153 --
1154 WHEN FND_API.G_EXC_ERROR THEN
1155 --
1156 ROLLBACK TO Rules_Delete_Row_Pvt ;
1157 p_return_status := FND_API.G_RET_STS_ERROR;
1158 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1159 p_data => p_msg_data );
1160 --
1161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1162 --
1163 ROLLBACK TO Rules_Delete_Row_Pvt ;
1164 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1166 p_data => p_msg_data );
1167 --
1168 WHEN OTHERS THEN
1169 --
1170 ROLLBACK TO Rules_Delete_Row_Pvt ;
1171 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1172 --
1173 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1174 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1175 l_api_name);
1176 END if;
1177 --
1178 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1179 p_data => p_msg_data );
1180 --
1181 END Rules_Delete_Row;
1182 /*-------------------------------------------------------------------------*/
1183
1184 /*-------------------------------------------------------------------------*/
1185
1186
1187
1188 PROCEDURE Copy_Rule
1189 (
1190 p_api_version IN NUMBER,
1191 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1192 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1193 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1194 p_return_status OUT NOCOPY VARCHAR2,
1195 p_msg_count OUT NOCOPY NUMBER,
1196 p_msg_data OUT NOCOPY VARCHAR2,
1197 --
1198 p_Source_Distribution_Rule_Id IN NUMBER,
1199 p_Source_Budget_Group IN NUMBER,
1200 p_Target_Rule_Name IN VARCHAR2,
1201 p_Target_Rule_ID OUT NOCOPY NUMBER,
1202 p_mode in varchar2
1203
1204 )
1205 IS
1206
1207 CURSOR l_from_distr_lines_csr IS
1208 SELECT budget_group_id ,
1209 distribute_flag ,
1210 distribute_all_level_flag ,
1211 download_flag ,
1212 download_all_level_flag ,
1213 year_category_type ,
1214 attribute1 ,
1215 attribute2 ,
1216 attribute3 ,
1217 attribute4 ,
1218 attribute5 ,
1219 attribute6 ,
1220 attribute7 ,
1221 attribute8 ,
1222 attribute9 ,
1223 attribute10 ,
1224 context
1225 FROM psb_ws_distribution_rule_lines
1226 WHERE distribution_rule_id = p_Source_Distribution_Rule_Id ;
1227
1228 --
1232 l_return_status VARCHAR2(1) ;
1229 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Rule';
1230 l_api_version CONSTANT NUMBER := 1.0;
1231 l_dist_rule_id NUMBER ;
1233 l_rowid VARCHAR2(100) ;
1234 l_dist_rule_line_id NUMBER ;
1235 --
1236 BEGIN
1237 -- Standard Start of API savepoint
1238 g_dbug := g_dbug || ' copy rule';
1239
1240 SAVEPOINT Copy_Rule;
1241
1242 -- Initialize message list if p_init_msg_list is set to TRUE.
1243
1244 if FND_API.to_Boolean (p_init_msg_list) then
1245 FND_MSG_PUB.initialize;
1246 end if;
1247
1248 -- Initialize API return status to success
1249
1250 l_return_status := FND_API.G_RET_STS_SUCCESS;
1251 --
1252 -- ... insert distribution rules
1253
1254 SELECT psb_ws_distribution_rules_s.NEXTVAL
1255 INTO l_dist_rule_id FROM dual;
1256
1257
1258 RULES_INSERT_ROW (
1259 p_api_version => 1.0,
1260 p_init_msg_list => fnd_api.g_false,
1261 p_commit => fnd_api.g_false,
1262 p_validation_level => fnd_api.g_valid_level_full,
1263 p_return_status => l_return_status,
1264 p_msg_count => p_msg_count,
1265 p_msg_data => p_msg_data,
1266 p_row_id => l_rowid,
1267 p_distribution_rule_id => l_dist_rule_id,
1268 p_name => p_target_rule_name,
1269 p_budget_group_id => p_Source_Budget_Group ,
1270 p_mode => 'R'
1271 );
1272
1273 -- ... insert distribution rule lines
1274 g_dbug := g_dbug || g_chr10 || 'rule id is: ' || to_char(l_dist_rule_id);
1275
1276 FOR lines_rec IN l_from_distr_lines_csr LOOP
1277
1278
1279 SELECT psb_ws_distribute_rule_lines_s.NEXTVAL
1280 INTO l_dist_rule_line_id FROM dual;
1281
1282 INSERT_ROW (
1283 p_api_version => 1.0,
1284 p_init_msg_list => fnd_api.g_false,
1285 p_commit => fnd_api.g_false,
1286 p_validation_level => fnd_api.g_valid_level_full,
1287 p_return_status => l_return_status,
1288 p_msg_count => p_msg_count,
1289 p_msg_data => p_msg_data,
1290 p_row_id => l_rowid,
1291 p_distribution_rule_line_id => l_dist_rule_line_id,
1292 p_distribution_rule_id => l_dist_rule_id,
1293 p_budget_group_id => lines_rec.budget_group_id,
1294 p_distribute_flag => lines_rec.distribute_flag,
1295 p_distribute_all_level_flag => lines_rec.distribute_all_level_flag,
1296 p_download_flag => lines_rec.download_flag ,
1297 p_download_all_level_flag => lines_rec.download_all_level_flag ,
1298 p_year_category_type => lines_rec.year_category_type ,
1299 p_attribute1 => lines_rec.attribute1,
1300 p_attribute2 => lines_rec.attribute2,
1301 p_attribute3 => lines_rec.attribute3,
1302 p_attribute4 => lines_rec.attribute4,
1303 p_attribute5 => lines_rec.attribute5,
1304 p_attribute6 => lines_rec.attribute6,
1305 p_attribute7 => lines_rec.attribute7,
1306 p_attribute8 => lines_rec.attribute8,
1307 p_attribute9 => lines_rec.attribute9,
1308 p_attribute10 => lines_rec.attribute10,
1309 p_context => lines_rec.context,
1310 p_mode => 'R'
1311 );
1312
1313 --
1314 g_dbug := g_dbug || g_chr10 || 'rule line id is: ' || to_char(l_dist_rule_line_id);
1315 END LOOP;
1316 --
1317 p_Target_Rule_ID := l_dist_rule_id;
1318 g_dbug := g_dbug || g_chr10 || 'rule id is: ' || to_char(l_dist_rule_id);
1319 --
1320 EXCEPTION
1321
1322 when FND_API.G_EXC_ERROR then
1323 --
1324 rollback to Copy_Rule;
1325 p_return_status := FND_API.G_RET_STS_ERROR;
1326 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1327 p_data => p_msg_data);
1328 --
1329 when FND_API.G_EXC_UNEXPECTED_ERROR then
1330 --
1331 rollback to Copy_Rule;
1332 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1333 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1334 p_data => p_msg_data);
1335 --
1336 when OTHERS then
1337 --
1338 rollback to Copy_Rule ;
1339 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1340 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1341 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1342 l_api_name);
1343 end if;
1344 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1345 p_data => p_msg_data);
1346 --
1347 END Copy_Rule;
1348
1349 --
1350 -- FUNCTIONS
1351 --
1352
1353 FUNCTION Get_Rule_Id RETURN NUMBER IS
1354 BEGIN
1355 Return g_rule_id;
1356 END Get_Rule_Id;
1357
1358
1359
1360
1361
1362 -- Get Debug Information
1363
1364 -- This Module is used to retrieve Debug Information for Funds Checker. It
1365 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
1366 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
1367 -- 'Serveroutput' should be set to 'ON'
1368
1369 FUNCTION get_debug RETURN VARCHAR2 IS
1370
1371 BEGIN
1372
1373 return(g_dbug);
1374
1375 END get_debug;
1376
1377 /* ----------------------------------------------------------------------- */
1378
1379 END PSB_WS_Distribution_Rules_PVT;