[Home] [Help]
PACKAGE BODY: APPS.PSB_POSITION_PAY_DISTR_PVT
Source
1 PACKAGE BODY PSB_POSITION_PAY_DISTR_PVT AS
2 /* $Header: PSBVPYDB.pls 120.7 2005/10/17 07:48:25 matthoma ship $ */
3 --
4 -- Global Variables
5 --
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_POSITION_PAY_DISTR_PVT';
8
9 TYPE g_paydist_rec_type IS RECORD
10 ( distribution_id NUMBER,
11 position_id NUMBER,
12 data_extract_id NUMBER,
13 worksheet_id NUMBER,
14 effective_start_date DATE,
15 effective_end_date DATE,
16 chart_of_accounts_id NUMBER,
17 code_combination_id NUMBER,
18 distribution_percent NUMBER,
19 global_default_flag VARCHAR2(1),
20 dist_default_rule_id NUMBER,
21 proper_subset VARCHAR2(1),
22 project_id NUMBER,
23 task_id NUMBER,
24 award_id NUMBER,
25 expenditure_type VARCHAR2(30),
26 expenditure_organization_id NUMBER,
27 --UTF8 changes for Bug No : 2615261
28 description psb_position_pay_distributions.description%TYPE,
29 delete_flag VARCHAR2(1));
30
31 TYPE g_paydist_tbl_type IS TABLE OF g_paydist_rec_type
32 INDEX BY BINARY_INTEGER;
33
34 g_pay_dist g_paydist_tbl_type;
35 g_num_pay_dist NUMBER;
36
37 TYPE TokNameArray IS TABLE OF VARCHAR2(100)
38 INDEX BY BINARY_INTEGER;
39
40 TYPE TokValArray IS TABLE OF VARCHAR2(1000)
41 INDEX BY BINARY_INTEGER;
42
43 -- Number of Message Tokens
44
45 no_msg_tokens NUMBER := 0;
46
47 -- Message Token Name
48
49 msg_tok_names TokNameArray;
50
51 -- Message Token Value
52
53 msg_tok_val TokValArray;
54
55 G_DBUG VARCHAR2(2000);
56
57 /* ----------------------------------------------------------------------- */
58
59 PROCEDURE message_token
60 ( tokname IN VARCHAR2,
61 tokval IN VARCHAR2
62 );
63
64 PROCEDURE add_message
65 ( appname IN VARCHAR2,
66 msgname IN VARCHAR2
67 );
68
69 --
70 -- Private Procedure Declarations
71 --
72 --
73
74 PROCEDURE Modify_WS_Distribution
75 ( p_return_status OUT NOCOPY VARCHAR2,
76 p_distribution_id IN OUT NOCOPY NUMBER,
77 p_position_id IN NUMBER,
78 p_data_extract_id IN NUMBER,
79 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
80 p_effective_start_date IN DATE,
81 p_effective_end_date IN DATE,
82 p_chart_of_accounts_id IN NUMBER,
83 p_code_combination_id IN NUMBER,
84 p_distribution_percent IN NUMBER,
85 p_global_default_flag IN VARCHAR2,
86 p_distribution_default_rule_id IN NUMBER,
87 p_rowid IN OUT NOCOPY VARCHAR2,
88 p_project_id IN NUMBER,
89 p_task_id IN NUMBER,
90 p_award_id IN NUMBER,
91 p_expenditure_type IN VARCHAR2,
92 p_expenditure_organization_id IN NUMBER,
93 p_description IN VARCHAR2 ,
94 p_mode IN VARCHAR2
95 );
96 -- Begin Table Handler Procedures
97 --
98
99 --
100 PROCEDURE INSERT_ROW
101 ( p_api_version IN NUMBER,
102 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
103 p_commit IN VARCHAR2 := fnd_api.g_false,
104 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
105 p_return_status OUT NOCOPY VARCHAR2,
106 p_msg_count OUT NOCOPY NUMBER,
107 p_msg_data OUT NOCOPY VARCHAR2,
108 p_rowid IN OUT NOCOPY VARCHAR2,
109 p_distribution_id IN NUMBER,
110 p_position_id IN NUMBER,
111 p_data_extract_id IN NUMBER,
112 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
113 p_effective_start_date IN DATE,
114 p_effective_end_date IN DATE,
115 p_chart_of_accounts_id IN NUMBER,
116 p_code_combination_id IN NUMBER,
117 p_distribution_percent IN NUMBER,
118 p_global_default_flag IN VARCHAR2,
119 p_distribution_default_rule_id IN NUMBER,
120 p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
121 p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
122 p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
123 p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
124 p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
125 p_description IN VARCHAR2:= FND_API.G_MISS_CHAR,
126 p_mode in varchar2 := 'R'
127 ) is
128 cursor C is select ROWID from PSB_POSITION_PAY_DISTRIBUTIONS
129 where distribution_id = P_distribution_id;
130 P_LAST_UPDATE_DATE DATE;
131 P_LAST_UPDATED_BY NUMBER;
132 P_LAST_UPDATE_LOGIN NUMBER;
133 --
134 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row' ;
135 l_api_version CONSTANT NUMBER := 1.0 ;
136 l_return_status VARCHAR2(1);
137 --
138 BEGIN
139 --
140 SAVEPOINT Insert_Row ;
141 --
142 if FND_API.to_Boolean (p_init_msg_list) then
143 FND_MSG_PUB.initialize;
144 end if;
145 --
146 --
147 P_LAST_UPDATE_DATE := SYSDATE;
148 if(P_MODE = 'I') then
149 P_LAST_UPDATED_BY := 1;
150 P_LAST_UPDATE_LOGIN := 0;
151 elsif (P_MODE = 'R') then
152 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
153 if P_LAST_UPDATED_BY is NULL then
154 P_LAST_UPDATED_BY := -1;
155 end if;
156 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
157 if P_LAST_UPDATE_LOGIN is NULL then
158 P_LAST_UPDATE_LOGIN := -1;
159 end if;
160 else
161 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
162 FND_MSG_PUB.Add ;
163 raise FND_API.G_EXC_ERROR;
164 end if;
165 --
166 insert into PSB_POSITION_PAY_DISTRIBUTIONS(
167 distribution_id ,
168 position_id ,
169 data_extract_id ,
170 worksheet_id ,
171 effective_start_date ,
172 effective_end_date ,
173 chart_of_accounts_id ,
174 code_combination_id ,
175 distribution_percent ,
176 global_default_flag ,
177 distribution_default_rule_id ,
178 project_id,
179 task_id,
180 award_id,
181 expenditure_type,
182 expenditure_organization_id,
183 description,
184 creation_date,
185 created_by,
186 last_update_date,
187 last_updated_by,
188 last_update_login
189 ) values (
190 p_distribution_id ,
191 p_position_id ,
192 p_data_extract_id ,
193 decode(p_worksheet_id,FND_API.G_MISS_NUM,null,p_worksheet_id),
194 p_effective_start_date ,
195 p_effective_end_date ,
196 p_chart_of_accounts_id ,
197 p_code_combination_id ,
198 p_distribution_percent ,
199 p_global_default_flag ,
200 p_distribution_default_rule_id ,
201 decode(p_project_id, FND_API.G_MISS_NUM, null, p_project_id),
202 decode(p_task_id, FND_API.G_MISS_NUM, null, p_task_id),
203 decode(p_award_id, FND_API.G_MISS_NUM, null, p_award_id),
204 decode(p_expenditure_type, FND_API.G_MISS_CHAR, null, p_expenditure_type),
205 decode(p_expenditure_organization_id, FND_API.G_MISS_NUM, null, p_expenditure_organization_id),
206 decode(p_description, FND_API.G_MISS_CHAR, null, p_description),
207 p_last_update_date,
208 p_last_updated_by,
209 p_last_update_date,
210 p_last_updated_by,
211 p_last_update_login
212 );
213 --
214 open c;
215 fetch c into P_ROWID;
216 if (c%notfound) then
217 close c;
218 raise FND_API.G_EXC_ERROR ;
219 --raise no_data_found;
220 end if;
221 close c;
222 --
223 --
224 p_return_status := FND_API.G_RET_STS_SUCCESS ;
225
226 -- Standard check of p_commit.
227 if FND_API.to_Boolean (p_commit) then
228 commit work;
229 end if;
230 -- Standard call to get message count and if count is 1, get message info.
231 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
232 p_data => p_msg_data);
233 --
234 EXCEPTION
235 --
236 when FND_API.G_EXC_ERROR then
237 --
238 rollback to INSERT_ROW ;
239 p_return_status := FND_API.G_RET_STS_ERROR;
240 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
241 p_data => p_msg_data);
242 --
243 when FND_API.G_EXC_UNEXPECTED_ERROR then
244 --
245 rollback to INSERT_ROW ;
246 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
248 p_data => p_msg_data);
249 --
250 when OTHERS then
251 --
252 rollback to INSERT_ROW ;
253 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
255 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
256 l_api_name);
257 END if;
258 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
259 p_data => p_msg_data);
260 --
261 END INSERT_ROW;
262 --
263
264 PROCEDURE LOCK_ROW
265 ( p_api_version IN NUMBER,
266 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
267 p_commit IN VARCHAR2 := fnd_api.g_false,
268 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
269 p_return_status OUT NOCOPY VARCHAR2,
270 p_msg_count OUT NOCOPY NUMBER,
271 p_msg_data OUT NOCOPY VARCHAR2,
272 p_row_locked OUT NOCOPY VARCHAR2,
273 p_distribution_id IN NUMBER,
274 p_position_id IN NUMBER,
275 p_data_extract_id IN NUMBER,
276 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
277 p_effective_start_date IN DATE,
278 p_effective_end_date IN DATE,
279 p_chart_of_accounts_id IN NUMBER,
280 p_code_combination_id IN NUMBER,
281 p_distribution_percent IN NUMBER,
282 p_global_default_flag IN VARCHAR2,
283 p_distribution_default_rule_id IN NUMBER,
284 p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
285 p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
286 p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
287 p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
288 p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
289 p_description IN VARCHAR2:= FND_API.G_MISS_CHAR
290
291 ) is
292 cursor c1 is select
293 distribution_id,
294 position_id,
295 data_extract_id,
296 worksheet_id,
297 effective_start_date,
298 effective_end_date,
299 chart_of_accounts_id,
300 code_combination_id,
301 distribution_percent,
302 distribution_default_rule_id,
303 global_default_flag,
304 project_id,
305 task_id,
306 award_id,
307 expenditure_type,
308 expenditure_organization_id,
309 description
310 from PSB_POSITION_PAY_DISTRIBUTIONS
311 where distribution_id = P_distribution_id
312 for update of distribution_id nowait;
313 tlinfo c1%rowtype;
314 --
315 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row' ;
316 l_api_version CONSTANT NUMBER := 1.0 ;
317 l_return_status VARCHAR2(1);
318 --
319 BEGIN
320 --
321 SAVEPOINT Lock_Row ;
322 --
323 if FND_API.to_Boolean (p_init_msg_list) then
324 FND_MSG_PUB.initialize;
325 end if;
326 --
327 p_row_locked := FND_API.G_TRUE ;
328 --
329 open c1;
330 fetch c1 into tlinfo;
331 if (c1%notfound) then
332 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
333 fnd_msg_pub.add ;
334 close c1;
335 raise fnd_api.g_exc_error ;
336 end if;
337 close c1;
338 --
339 if ( (tlinfo.position_id = p_position_id)
340 AND (tlinfo.distribution_id = p_distribution_id)
341 AND (tlinfo.data_extract_id = p_data_extract_id)
342 AND (tlinfo.effective_start_date = p_effective_start_date)
343 AND (tlinfo.chart_of_accounts_id = p_chart_of_accounts_id)
344 AND (tlinfo.code_combination_id = p_code_combination_id)
345
346 AND ((tlinfo.effective_end_date = p_effective_end_date)
347 OR ((tlinfo.effective_end_date is null)
348 AND (p_effective_end_date is null)))
349
350 -- AND ((tlinfo.worksheet_id = p_worksheet_id)
351 -- OR ((tlinfo.worksheet_id is null)
352 -- AND (p_worksheet_id is null))
353
354 -- do not test this due to ws specific
355
356 AND (tlinfo.distribution_percent = p_distribution_percent)
357
358 AND ((tlinfo.global_default_flag = p_global_default_flag)
359 OR ((tlinfo.global_default_flag is null)
360 AND (p_global_default_flag is null)))
361
362 AND ((tlinfo.distribution_default_rule_id = p_distribution_default_rule_id)
363 OR ((tlinfo.distribution_default_rule_id is null)
364 AND (p_distribution_default_rule_id is null)))
365
366 AND ((tlinfo.project_id = p_project_id)
367 OR ((tlinfo.project_id is null)
368 AND (p_project_id is null))
369 OR ( (p_project_id = FND_API.G_MISS_NUM )))
370
371 AND ((tlinfo.task_id = p_task_id)
372 OR ((tlinfo.task_id is null)
373 AND (p_task_id is null))
374 OR ( (p_task_id = FND_API.G_MISS_NUM )))
375
376 AND ((tlinfo.award_id = p_award_id)
377 OR ((tlinfo.award_id is null)
378 AND (p_award_id is null))
379 OR ( (p_award_id = FND_API.G_MISS_NUM)))
380
381 AND ((tlinfo.expenditure_type = p_expenditure_type)
382 OR ((tlinfo.expenditure_type is null)
383 AND (p_expenditure_type is null))
384 OR ((p_expenditure_type = FND_API.G_MISS_CHAR)))
385
386 AND ((tlinfo.expenditure_organization_id = p_expenditure_organization_id)
387 OR ((tlinfo.expenditure_organization_id is null)
388 AND (p_expenditure_organization_id is null))
389 OR ((p_expenditure_organization_id = FND_API.G_MISS_NUM)))
390
391 AND ((tlinfo.description = p_description)
392 OR ((tlinfo.description is null)
393 AND (p_description is null))
394 OR ( (p_description = FND_API.G_MISS_CHAR)))
395
396
397 ) then
398 null;
399 else
400 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
401 fnd_msg_pub.add ;
402 raise fnd_api.g_exc_error ;
403 end if;
404
405 p_return_status := FND_API.G_RET_STS_SUCCESS ;
406
407 EXCEPTION
408 when app_exception.record_lock_exception then
409 --
410 rollback to LOCK_ROW ;
411 p_row_locked := FND_API.G_FALSE ;
412 p_return_status := FND_API.G_RET_STS_ERROR;
413 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
414 p_data => p_msg_data);
415 --
416 when FND_API.G_EXC_ERROR then
417 --
418 rollback to LOCK_ROW ;
419 p_return_status := FND_API.G_RET_STS_ERROR;
420 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
421 p_data => p_msg_data);
422 --
423 when FND_API.G_EXC_UNEXPECTED_ERROR then
424 --
425 rollback to LOCK_ROW ;
426 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
428 p_data => p_msg_data);
429 --
430 when OTHERS then
431 --
432 rollback to LOCK_ROW ;
433 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
435 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
436 l_api_name);
437 END if;
438 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
439 p_data => p_msg_data);
440 --
441 END LOCK_ROW;
442
443 --
444 PROCEDURE UPDATE_ROW (
445 p_api_version IN NUMBER,
446 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
447 p_commit IN VARCHAR2 := fnd_api.g_false,
448 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
449 p_return_status OUT NOCOPY VARCHAR2,
450 p_msg_count OUT NOCOPY NUMBER,
451 p_msg_data OUT NOCOPY VARCHAR2,
452 p_distribution_id IN NUMBER,
453 p_code_combination_id IN NUMBER,
454 p_effective_start_date IN DATE := FND_API.G_MISS_DATE,
455 p_effective_end_date IN DATE := FND_API.G_MISS_DATE,
456 p_distribution_percent IN NUMBER,
457 p_global_default_flag IN VARCHAR2,
458 p_distribution_default_rule_id IN NUMBER,
459 p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
460 p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
461 p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
462 p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
463 p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
464 p_description IN VARCHAR2:= FND_API.G_MISS_CHAR,
465 p_mode in varchar2 := 'R'
466
467 ) is
468 P_LAST_UPDATE_DATE DATE;
469 P_LAST_UPDATED_BY NUMBER;
470 P_LAST_UPDATE_LOGIN NUMBER;
471 --
472 l_api_name CONSTANT VARCHAR2(30) := 'Update Row';
473 l_api_version CONSTANT NUMBER := 1.0 ;
474 l_return_status VARCHAR2(1);
475 --
476 BEGIN
477 --
478 SAVEPOINT Update_Row ;
479 --
480 if FND_API.to_Boolean (p_init_msg_list) then
481 FND_MSG_PUB.initialize;
482 end if;
483
484 --
485 P_LAST_UPDATE_DATE := SYSDATE;
486 if(P_MODE = 'I') then
487 P_LAST_UPDATED_BY := 1;
488 P_LAST_UPDATE_LOGIN := 0;
489 elsif (P_MODE = 'R') then
490 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
491 if P_LAST_UPDATED_BY is NULL then
492 P_LAST_UPDATED_BY := -1;
493 end if;
494 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
495 if P_LAST_UPDATE_LOGIN is NULL then
496 P_LAST_UPDATE_LOGIN := -1;
497 end if;
498 else
499 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
500 FND_MSG_PUB.Add ;
501 raise FND_API.G_EXC_ERROR ;
502 end if;
503
504 -- do the update of the record
505 --
506 update PSB_POSITION_PAY_DISTRIBUTIONS set
507 code_combination_id = p_code_combination_id,
508 effective_start_date = decode(p_effective_start_date, FND_API.G_MISS_DATE, effective_start_date, p_effective_start_date),
509 effective_end_date = decode(p_effective_end_date, FND_API.G_MISS_DATE, effective_end_date, p_effective_end_date),
510 distribution_percent = p_distribution_percent,
511 global_default_flag = p_global_default_flag,
512 distribution_default_rule_id = p_distribution_default_rule_id,
513 project_id = decode(p_project_id,FND_API.G_MISS_NUM,
514 project_id, p_project_id),
515 task_id = decode(p_task_id,FND_API.G_MISS_NUM,
516 task_id, p_task_id),
517 award_id = decode(p_award_id,FND_API.G_MISS_NUM,
518 award_id, p_award_id),
519 expenditure_type = decode(p_expenditure_type,
520 FND_API.G_MISS_CHAR, expenditure_type,
521 p_expenditure_type),
522 expenditure_organization_id = decode(p_expenditure_organization_id,
523 FND_API.G_MISS_NUM, expenditure_organization_id,
524 p_expenditure_organization_id),
525 description = decode(p_description,
526 FND_API.G_MISS_CHAR, description,
527 p_description),
528 last_update_date = p_last_update_date,
529 last_updated_by = p_last_updated_by,
530 last_update_login = p_last_update_login
531 where distribution_id = p_distribution_id;
532
533 if (sql%notfound) then
534 -- raise no_data_found;
535 raise FND_API.G_EXC_ERROR ;
536 end if;
537
538
539 -- Initialize API return status to success
540
541 p_return_status := FND_API.G_RET_STS_SUCCESS ;
542
543 --
544 --
545 -- Standard check of p_commit.
546
547 if FND_API.to_Boolean (p_commit) then
548 commit work;
549 end if;
550
551 -- Standard call to get message count and if count is 1, get message info.
552
553 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
554 p_data => p_msg_data);
555 --
556 EXCEPTION
557
558 when FND_API.G_EXC_ERROR then
559 --
560 rollback to Update_Row ;
561 p_return_status := FND_API.G_RET_STS_ERROR;
562 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
563 p_data => p_msg_data);
564 --
565 when FND_API.G_EXC_UNEXPECTED_ERROR then
566 --
567 rollback to Update_Row ;
568 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
569 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
570 p_data => p_msg_data);
571 --
572 when OTHERS then
573 --
574 rollback to Update_Row ;
575 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
576 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
577 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
578 l_api_name);
579 end if;
580 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
581 p_data => p_msg_data);
582 --
583
584 END UPDATE_ROW;
585 --
586 PROCEDURE ADD_ROW (
587 p_api_version IN NUMBER,
588 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
589 p_commit IN VARCHAR2 := fnd_api.g_false,
590 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
591 p_return_status OUT NOCOPY VARCHAR2,
592 p_msg_count OUT NOCOPY NUMBER,
593 p_msg_data OUT NOCOPY VARCHAR2,
594 p_rowid IN OUT NOCOPY VARCHAR2,
595 p_distribution_id IN NUMBER,
596 p_position_id IN NUMBER,
597 p_data_extract_id IN NUMBER,
598 p_worksheet_id IN NUMBER,
599 p_effective_start_date IN DATE,
600 p_effective_end_date IN DATE,
601 p_chart_of_accounts_id IN NUMBER,
602 p_code_combination_id IN NUMBER,
603 p_distribution_percent IN NUMBER,
604 p_global_default_flag IN VARCHAR2,
605 p_distribution_default_rule_id IN NUMBER,
606 p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
607 p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
608 p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
609 p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
610 p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
611 p_description IN VARCHAR2:= FND_API.G_MISS_CHAR,
612 p_mode in varchar2 := 'R'
613
614
615 ) is
616 cursor c1 is select rowid from PSB_POSITION_PAY_DISTRIBUTIONS
617 where position_id = p_position_id
618 ;
619 dummy c1%rowtype;
620 --
621 l_api_name CONSTANT VARCHAR2(30) := 'Add Row' ;
622 l_api_version CONSTANT NUMBER := 1.0 ;
623 --
624 BEGIN
625 --
626 SAVEPOINT Add_Row ;
627 --
628 -- Initialize message list if p_init_msg_list is set to TRUE.
629 --
630 if FND_API.to_Boolean (p_init_msg_list) then
631 FND_MSG_PUB.initialize;
632 end if;
633 --
634 p_return_status := FND_API.G_RET_STS_SUCCESS ;
635 --
636 open c1;
637 fetch c1 into dummy;
638 if (c1%notfound) then
639 close c1;
640 INSERT_ROW (
641 p_api_version => p_api_version,
642 p_init_msg_list => p_init_msg_list,
643 p_commit => p_commit,
644 p_validation_level => p_validation_level,
645 p_return_status => p_return_status,
646 p_msg_count => p_msg_count,
647 p_msg_data => p_msg_data,
648 p_rowid => p_rowid,
649 p_distribution_id => p_distribution_id,
650 p_position_id => p_position_id,
651 p_data_extract_id => p_data_extract_id,
652 p_worksheet_id => p_worksheet_id,
653 p_effective_start_date => p_effective_start_date,
654 p_effective_end_date => p_effective_end_date,
655 p_chart_of_accounts_id => p_chart_of_accounts_id,
656 p_code_combination_id => p_code_combination_id,
657 p_distribution_percent => p_distribution_percent,
658 p_global_default_flag => p_global_default_flag,
659 p_distribution_default_rule_id => p_distribution_default_rule_id,
660 p_project_id => p_project_id,
661 p_task_id => p_task_id,
662 p_award_id => p_award_id,
663 p_expenditure_type => p_expenditure_type,
664 p_expenditure_organization_id => p_expenditure_organization_id,
665 p_description => p_description,
666 p_mode => p_mode
667 );
668 --
669 if FND_API.to_Boolean (p_commit) then
670 commit work;
671 end if;
672 -- Standard call to get message count and if count is 1, get message info.
673 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
674 p_data => p_msg_data);
675
676 return;
677 END if;
678 close c1;
679 UPDATE_ROW (
680 p_api_version => p_api_version,
681 p_init_msg_list => p_init_msg_list,
682 p_commit => p_commit,
683 p_validation_level => p_validation_level,
684 p_return_status => p_return_status,
685 p_msg_count => p_msg_count,
686 p_msg_data => p_msg_data,
687 p_distribution_id => p_distribution_id,
688 p_code_combination_id => p_code_combination_id,
689 p_effective_start_date => p_effective_start_date,
690 p_effective_end_date => p_effective_end_date,
691 p_distribution_percent => p_distribution_percent,
692 p_global_default_flag => p_global_default_flag,
693 p_distribution_default_rule_id => p_distribution_default_rule_id,
694 p_project_id => p_project_id,
695 p_task_id => p_task_id,
696 p_award_id => p_award_id,
697 p_expenditure_type => p_expenditure_type,
698 p_expenditure_organization_id => p_expenditure_organization_id,
699 p_description => p_description,
700 p_mode => p_mode
701 );
702 -- Standard check of p_commit.
703
704 if FND_API.to_Boolean (p_commit) then
705 commit work;
706 end if;
707
708 -- Standard call to get message count and if count is 1, get message info.
709
710 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
711 p_data => p_msg_data);
712
713 END ADD_ROW;
714 --
715 PROCEDURE DELETE_ROW (
716 p_api_version in number,
717 p_init_msg_list in varchar2 := fnd_api.g_false,
718 p_commit in varchar2 := fnd_api.g_false,
719 p_validation_level in number := fnd_api.g_valid_level_full,
720 p_return_status OUT NOCOPY varchar2,
721 p_msg_count OUT NOCOPY number,
722 p_msg_data OUT NOCOPY varchar2,
723 p_distribution_id in number
724 ) is
725 --
726 l_api_name CONSTANT VARCHAR2(30) := 'Delete Row' ;
727 l_api_version CONSTANT NUMBER := 1.0 ;
728
729 l_return_status VARCHAR2(1);
730 --
731 BEGIN
732 --
733 SAVEPOINT Delete_Row ;
734 --
735 -- Initialize message list if p_init_msg_list is set to TRUE.
736 --
737 if FND_API.to_Boolean (p_init_msg_list) then
738 FND_MSG_PUB.initialize;
739 end if;
740 --
741 p_return_status := FND_API.G_RET_STS_SUCCESS ;
742
743 --
744 delete from PSB_POSITION_PAY_DISTRIBUTIONS
745 where distribution_id = p_distribution_id;
746 if (sql%notfound) THEN
747 null;
748 end if;
749
750 -- Standard check of p_commit.
751 --
752 if FND_API.to_Boolean (p_commit) then
753 commit work;
754 end if;
755
756 -- Standard call to get message count and if count is 1, get message info.
757
758 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
759 p_data => p_msg_data);
760 --
761 EXCEPTION
762 when FND_API.G_EXC_ERROR then
763 --
764 rollback to Delete_Row;
765 p_return_status := FND_API.G_RET_STS_ERROR;
766 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
767 p_data => p_msg_data);
768 --
769 when FND_API.G_EXC_UNEXPECTED_ERROR then
770 --
771 rollback to Delete_Row;
772 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
774 p_data => p_msg_data);
775 --
776 when OTHERS then
777 --
778 rollback to Delete_Row ;
779 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
780 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
781 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
782 l_api_name);
783 end if;
784 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
785 p_data => p_msg_data);
786 --
787 END DELETE_ROW;
788
789 /* ----------------------------------------------------------------------- */
790
791 PROCEDURE Delete_Distributions
792 ( p_api_version IN NUMBER,
793 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
794 p_commit IN VARCHAR2 := FND_API.G_FALSE,
795 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
796 p_return_status OUT NOCOPY VARCHAR2,
797 p_msg_count OUT NOCOPY NUMBER,
798 p_msg_data OUT NOCOPY VARCHAR2,
799 p_data_extract_id IN NUMBER
800 ) IS
801
802 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Distributions';
803 l_api_version CONSTANT NUMBER := 1.0;
804
805 BEGIN
806
807 -- Standard Start of API savepoint
808
809 SAVEPOINT Delete_Distributions_Pvt;
810
811
812 -- Standard call to check for call compatibility
813
814 if not FND_API.Compatible_API_Call (l_api_version,
815 p_api_version,
816 l_api_name,
817 G_PKG_NAME)
818 then
819 raise FND_API.G_EXC_UNEXPECTED_ERROR;
820 end if;
821
822
823 -- Initialize message list if p_init_msg_list is set to TRUE
824
825 if FND_API.to_Boolean (p_init_msg_list) then
826 FND_MSG_PUB.initialize;
827 end if;
828
829 delete from PSB_POSITION_PAY_DISTRIBUTIONS
830 where data_extract_id = p_data_extract_id;
831
832
833 -- Standard check of p_commit
834
835 if FND_API.to_Boolean (p_commit) then
836 commit work;
837 end if;
838
839
840 -- Initialize API return status to success
841
842 p_return_status := FND_API.G_RET_STS_SUCCESS;
843
844
845 -- Standard call to get message count and if count is 1, get message info
846
847 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
848 p_data => p_msg_data);
849
850 EXCEPTION
851
852 when FND_API.G_EXC_ERROR then
853 rollback to Delete_Distributions_Pvt;
854 p_return_status := FND_API.G_RET_STS_ERROR;
855
856 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
857 p_data => p_msg_data);
858
859
860 when FND_API.G_EXC_UNEXPECTED_ERROR then
861 rollback to Delete_Distributions_Pvt;
862 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863
864 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
865 p_data => p_msg_data);
866
867
868 when OTHERS then
869 rollback to Delete_Distributions_Pvt;
870 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
871
872 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
873
874 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
875 l_api_name);
876 end if;
877
878 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
879 p_data => p_msg_data);
880
881 END Delete_Distributions;
882
883 /* ----------------------------------------------------------------------- */
884
885 PROCEDURE Delete_Distributions_Position
886 ( p_api_version IN NUMBER,
887 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
888 p_commit IN VARCHAR2 := FND_API.G_FALSE,
889 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
890 p_return_status OUT NOCOPY VARCHAR2,
891 p_msg_count OUT NOCOPY NUMBER,
892 p_msg_data OUT NOCOPY VARCHAR2,
893 p_position_id IN NUMBER,
894 p_worksheet_id IN NUMBER
895 ) IS
896
897 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Distributions_Position';
898 l_api_version CONSTANT NUMBER := 1.0;
899
900 BEGIN
901
902 -- Standard Start of API savepoint
903
904 SAVEPOINT Delete_Dist_Position_Pvt;
905
906
907 -- Standard call to check for call compatibility
908
909 if not FND_API.Compatible_API_Call (l_api_version,
910 p_api_version,
911 l_api_name,
912 G_PKG_NAME)
913 then
914 raise FND_API.G_EXC_UNEXPECTED_ERROR;
915 end if;
916
917
918 -- Initialize message list if p_init_msg_list is set to TRUE
919
920 if FND_API.to_Boolean (p_init_msg_list) then
921 FND_MSG_PUB.initialize;
922 end if;
923
924 DELETE from PSB_POSITION_PAY_DISTRIBUTIONS
925 WHERE position_id = p_position_id
926 /* Bug 4545909 Start */
927 AND ((worksheet_id IS NULL AND p_worksheet_id IS NULL)
928 OR worksheet_id = p_worksheet_id);
929 /* Bug 4545909 End */
930
931
932 -- Standard check of p_commit
933
934 if FND_API.to_Boolean (p_commit) then
935 commit work;
936 end if;
937
938
939 -- Initialize API return status to success
940
941 p_return_status := FND_API.G_RET_STS_SUCCESS;
942
943
944 -- Standard call to get message count and if count is 1, get message info
945
946 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
947 p_data => p_msg_data);
948
949 EXCEPTION
950
951 when FND_API.G_EXC_ERROR then
952 rollback to Delete_Dist_Position_Pvt;
953 p_return_status := FND_API.G_RET_STS_ERROR;
954
955 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
956 p_data => p_msg_data);
957
958
959 when FND_API.G_EXC_UNEXPECTED_ERROR then
960 rollback to Delete_Dist_Position_Pvt;
961 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
962
963 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
964 p_data => p_msg_data);
965
966
967 when OTHERS then
968 rollback to Delete_Dist_Position_Pvt;
969 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
970
971 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
972
973 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
974 l_api_name);
975 end if;
976
977 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
978 p_data => p_msg_data);
979
980 END Delete_Distributions_Position;
981
982 /* ----------------------------------------------------------------------- */
983
984 PROCEDURE Modify_Distribution_WS
985 ( p_api_version IN NUMBER,
986 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
987 p_commit IN VARCHAR2 := FND_API.G_FALSE,
988 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
989 p_return_status OUT NOCOPY VARCHAR2,
990 p_msg_count OUT NOCOPY NUMBER,
991 p_msg_data OUT NOCOPY VARCHAR2,
992 p_distribution_id IN OUT NOCOPY NUMBER,
993 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
994 p_position_id IN NUMBER,
995 p_data_extract_id IN NUMBER,
996 p_effective_start_date IN DATE,
997 p_effective_end_date IN DATE,
998 p_modify_flag IN VARCHAR2,
999 p_chart_of_accounts_id IN NUMBER,
1000 p_code_combination_id IN NUMBER,
1001 p_distribution_percent IN NUMBER,
1002 p_global_default_flag IN VARCHAR2,
1003 p_distribution_default_rule_id IN NUMBER,
1004 p_rowid IN OUT NOCOPY VARCHAR2,
1005 p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
1006 p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
1007 p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
1008 p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
1009 p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
1010 p_description IN VARCHAR2:= FND_API.G_MISS_CHAR,
1011 p_budget_revision_pos_line_id IN NUMBER:= FND_API.G_MISS_NUM,
1012 p_mode IN VARCHAR2 := 'R',
1013 p_ruleset_id IN NUMBER -- 1308558
1014 ) IS
1015
1016 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Distribution_WS';
1017 l_api_version CONSTANT NUMBER := 1.0;
1018
1019 l_budget_calendar_id NUMBER;
1020 l_budget_group_id NUMBER;
1021
1022 l_name VARCHAR2(80);
1023 l_set_of_books_id NUMBER;
1024 l_flex_code NUMBER;
1025
1026 l_concat_segments VARCHAR2(2000);
1027 l_ccid_valid VARCHAR2(1) := FND_API.G_FALSE;
1028
1029 l_msg_count NUMBER;
1030 l_msg_data VARCHAR2(2000);
1031
1032 l_out_ccid NUMBER;
1033 l_out_budget_group_id NUMBER;
1034 l_rv_start_date DATE;
1035 l_rv_end_date DATE;
1036 l_return_status VARCHAR2(1);
1037 l_rev_budget_group_id NUMBER;
1038 l_data_extract_id NUMBER;
1039
1040 cursor c_WS is
1041 select budget_calendar_id,
1042 budget_group_id
1043 from PSB_WORKSHEETS_V
1044 where worksheet_id = p_worksheet_id;
1045
1046 cursor c_BG is
1047 select name,
1048 nvl(set_of_books_id, root_set_of_books_id) set_of_books_id,
1049 nvl(chart_of_accounts_id, root_chart_of_accounts_id) flex_code
1050 from PSB_BUDGET_GROUPS_V
1051 where budget_group_id = l_budget_group_id;
1052
1053 /* -- Commented out for Bug: 3325171
1054 -- since we are going to use the Start Date from the
1055 -- revision-level and not from the position-level
1056
1057 cursor c_RV_pos is
1058 select effective_start_date
1059 from psb_positions
1060 where position_id = p_position_id;
1061 */
1062
1063 cursor c_RV_rev is
1064 select effective_end_date,
1065 effective_start_date
1066 from psb_budget_revision_positions
1067 where budget_revision_pos_line_id = p_budget_revision_pos_line_id;
1068
1069 cursor c_rev IS
1070 SELECT budget_group_id
1071 FROM psb_budget_revisions
1072 WHERE budget_revision_id = p_worksheet_id;
1073
1074 CURSOR c_data_extract is
1075 SELECT set_of_books_id ,
1076 position_id_flex_num
1077 FROM psb_data_extracts
1078 WHERE data_extract_id = l_data_extract_id ;
1079 BEGIN
1080
1081 -- Standard Start of API savepoint
1082
1083 SAVEPOINT Modify_Distribution_WS_Pvt;
1084
1085
1086 -- Standard call to check for call compatibility
1087
1088 if not FND_API.Compatible_API_Call (l_api_version,
1089 p_api_version,
1090 l_api_name,
1091 G_PKG_NAME)
1092 then
1093 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1094 end if;
1095
1096
1097 -- Initialize message list if p_init_msg_list is set to TRUE
1098
1099 if FND_API.to_Boolean (p_init_msg_list) then
1100 FND_MSG_PUB.initialize;
1101 end if;
1102
1103 if nvl(p_worksheet_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
1104 l_ccid_valid := FND_API.G_TRUE;
1105 else
1106
1107
1108 if p_budget_revision_pos_line_id <> FND_API.G_MISS_NUM then
1109 -- budget revision
1110
1111 begin
1112
1113 /* -- Commented out for Bug: 3325171
1114 -- since we are going to use the Start Date from the
1115 -- revision-level and not from the position-level
1116
1117 for c_RV_pos_rec in c_RV_pos loop
1118 l_rv_start_date := c_RV_pos_rec.effective_start_date;
1119 end loop;
1120
1121 */
1122
1123 for c_RV_rev_rec in c_RV_rev loop
1124 l_rv_start_date := c_RV_rev_rec.effective_start_date;
1125 l_rv_end_date := c_RV_rev_rec.effective_end_date;
1126 end loop;
1127
1128 FOR c_rev_rec in c_rev loop
1129 l_rev_budget_group_id := c_rev_rec.budget_group_id; -- get rev's bg
1130 END LOOP;
1131
1132
1133 -- then find the data extract id; api will get the top level bg
1134 l_data_extract_id := PSB_BUDGET_REVISIONS_PVT.Find_System_Data_Extract
1135 (p_budget_group_id => l_rev_budget_group_id);
1136
1137 for c_data_extract_rec in c_data_extract loop
1138
1139 l_set_of_books_id := c_data_extract_rec.set_of_books_id;
1140
1141 -- Fix for Bug: 3325171 - start ...
1142 -- l_flex_code := c_data_extract_rec.position_id_flex_num;
1143
1144 select chart_of_accounts_id
1145 into l_flex_code
1146 from GL_SETS_OF_BOOKS
1147 where set_of_books_id = l_set_of_books_id;
1148
1149 select name
1150 into l_name
1151 from PSB_BUDGET_GROUPS_V
1152 where budget_group_id = l_rev_budget_group_id;
1153
1154 -- Fix for Bug: 3325171 - ... end
1155
1156 end loop;
1157
1158
1159 PSB_VALIDATE_ACCT_PVT.Validate_Account
1160 (p_api_version => 1.0,
1161 p_return_status => l_return_status,
1162 p_msg_count => l_msg_count,
1163 p_msg_data => l_msg_data,
1164 p_parent_budget_group_id => l_rev_budget_group_id,
1165 p_startdate_pp => l_rv_start_date,
1166 p_enddate_cy => l_rv_end_date,
1167 p_create_budget_account => FND_API.G_TRUE,
1168 p_set_of_books_id => l_set_of_books_id,
1169 p_flex_code => l_flex_code,
1170 p_in_ccid => p_code_combination_id,
1171 p_out_ccid => l_out_ccid,
1172 p_budget_group_id => l_out_budget_group_id );
1173
1174 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1175
1176 l_concat_segments := FND_FLEX_EXT.Get_Segs
1177 (application_short_name => 'SQLGL',
1178 key_flex_code => 'GL#',
1179 structure_number => l_flex_code,
1180 combination_id => p_code_combination_id);
1181
1182 message_token('CCID', l_concat_segments);
1183 message_token('BUDGET_GROUP', l_name);
1184 add_message('PSB', 'PSB_CCID_NOTIN_BUDGET_GROUP');
1185
1186 l_ccid_valid := FND_API.G_FALSE;
1187
1188 else
1189 l_ccid_valid := FND_API.G_TRUE;
1190 end if;
1191
1192 end; -- of rev
1193
1194 else
1195
1196 begin
1197 -- ws
1198
1199 for c_WS_Rec in c_WS loop
1200 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
1201 l_budget_group_id := c_WS_Rec.budget_group_id;
1202 end loop;
1203
1204 for c_BG_Rec in c_BG loop
1205 l_name := c_BG_Rec.name;
1206 l_set_of_books_id := c_BG_Rec.set_of_books_id;
1207 l_flex_code := c_BG_Rec.flex_code;
1208 end loop;
1209
1210 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
1211 begin
1212
1213 PSB_WS_ACCT1.Cache_Budget_Calendar
1214 (p_return_status => l_return_status,
1215 p_budget_calendar_id => l_budget_calendar_id);
1216
1217 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1218 raise FND_API.G_EXC_ERROR;
1219 end if;
1220
1221 end;
1222 end if;
1223
1224 PSB_VALIDATE_ACCT_PVT.Validate_Account
1225 (p_api_version => 1.0,
1226 p_return_status => l_return_status,
1227 p_msg_count => l_msg_count,
1228 p_msg_data => l_msg_data,
1229 p_parent_budget_group_id => l_budget_group_id,
1230 p_startdate_pp => PSB_WS_ACCT1.g_startdate_pp,
1231 p_enddate_cy => PSB_WS_ACCT1.g_enddate_cy,
1232 p_create_budget_account => FND_API.G_TRUE,
1233 p_set_of_books_id => l_set_of_books_id,
1234 p_flex_code => l_flex_code,
1235 p_in_ccid => p_code_combination_id,
1236 p_out_ccid => l_out_ccid,
1237 p_budget_group_id => l_out_budget_group_id);
1238
1239 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1240 begin
1241
1242 l_concat_segments := FND_FLEX_EXT.Get_Segs
1243 (application_short_name => 'SQLGL',
1244 key_flex_code => 'GL#',
1245 structure_number => l_flex_code,
1246 combination_id => p_code_combination_id);
1247
1248 message_token('CCID', l_concat_segments);
1249 message_token('BUDGET_GROUP', l_name);
1250 add_message('PSB', 'PSB_CCID_NOTIN_BUDGET_GROUP');
1251
1252 l_ccid_valid := FND_API.G_FALSE;
1253
1254 end;
1255 else
1256 l_ccid_valid := FND_API.G_TRUE;
1257 end if;
1258
1259 end;
1260 end if;
1261 end if;
1262
1263 if FND_API.to_Boolean(l_ccid_valid) then
1264 begin
1265
1266 -- 1308558. Mass Position Assignment Rules
1267 IF p_ruleset_id IS NULL THEN
1268 Modify_Distribution
1269 (p_api_version => 1.0,
1270 p_return_status => l_return_status,
1271 p_msg_count => p_msg_count,
1272 p_msg_data => p_msg_data,
1273 p_distribution_id => p_distribution_id,
1274 p_position_id => p_position_id,
1275 p_data_extract_id => p_data_extract_id,
1276 p_worksheet_id => p_worksheet_id,
1277 p_effective_start_date => p_effective_start_date,
1278 p_effective_end_date => p_effective_end_date,
1279 p_chart_of_accounts_id => p_chart_of_accounts_id,
1280 p_code_combination_id => p_code_combination_id,
1281 p_distribution_percent => p_distribution_percent,
1282 p_global_default_flag => p_global_default_flag,
1283 p_distribution_default_rule_id => p_distribution_default_rule_id,
1284 p_project_id => p_project_id,
1285 p_task_id => p_task_id,
1286 p_award_id => p_award_id,
1287 p_expenditure_type => p_expenditure_type,
1288 p_expenditure_organization_id => p_expenditure_organization_id,
1289 p_description => p_description,
1290 p_rowid => p_rowid,
1291 p_mode => p_mode);
1292
1293 ELSE
1294
1295 Apply_Position_Pay_Distr
1296 (p_api_version => 1.0,
1297 x_return_status => l_return_status,
1298 x_msg_count => p_msg_count,
1299 x_msg_data => p_msg_data,
1300 p_distribution_id => p_distribution_id,
1301 p_position_id => p_position_id,
1302 p_data_extract_id => p_data_extract_id,
1303 p_worksheet_id => p_worksheet_id,
1304 p_effective_start_date => p_effective_start_date,
1305 p_effective_end_date => p_effective_end_date,
1306 p_modify_flag => p_modify_flag,
1307 p_chart_of_accounts_id => p_chart_of_accounts_id,
1308 p_code_combination_id => p_code_combination_id,
1309 p_distribution_percent => p_distribution_percent,
1310 p_global_default_flag => p_global_default_flag,
1311 p_distribution_default_rule_id => p_distribution_default_rule_id,
1312 p_project_id => p_project_id,
1313 p_task_id => p_task_id,
1314 p_award_id => p_award_id,
1315 p_expenditure_type => p_expenditure_type,
1316 p_expenditure_organization_id => p_expenditure_organization_id,
1317 p_description => p_description,
1318 p_rowid => p_rowid,
1319 p_mode => p_mode);
1320 END IF;
1321 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1322 raise FND_API.G_EXC_ERROR;
1323 end if;
1324
1325 end;
1326 -- Added for Bug: 3325171
1327 else
1328 raise FND_API.G_EXC_ERROR;
1329 end if;
1330
1331
1332 -- Standard check of p_commit
1333
1334 if FND_API.to_Boolean (p_commit) then
1335 commit work;
1336 end if;
1337
1338
1339 -- Initialize API return status to success
1340
1341 p_return_status := FND_API.G_RET_STS_SUCCESS;
1342
1343
1344 -- Standard call to get message count and if count is 1, get message info
1345
1346 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1347 p_data => p_msg_data);
1348
1349 EXCEPTION
1350
1351 when FND_API.G_EXC_ERROR then
1352 rollback to Modify_Distribution_WS_Pvt;
1353 p_return_status := FND_API.G_RET_STS_ERROR;
1354
1355 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1356 p_data => p_msg_data);
1357
1358
1359 when FND_API.G_EXC_UNEXPECTED_ERROR then
1360 rollback to Modify_Distribution_WS_Pvt;
1361 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1362
1363 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1364 p_data => p_msg_data);
1365
1366
1367 when OTHERS then
1368 rollback to Modify_Distribution_WS_Pvt;
1369 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1370
1371 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1372
1373 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1374 l_api_name);
1375 end if;
1376
1377 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1378 p_data => p_msg_data);
1379
1380 END Modify_Distribution_WS;
1381
1382 /* ----------------------------------------------------------------------- */
1383
1384 PROCEDURE Modify_Distribution
1385 ( p_api_version IN NUMBER,
1386 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1387 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1388 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1389 p_return_status OUT NOCOPY VARCHAR2,
1390 p_msg_count OUT NOCOPY NUMBER,
1391 p_msg_data OUT NOCOPY VARCHAR2,
1392 p_distribution_id IN OUT NOCOPY NUMBER,
1393 p_position_id IN NUMBER,
1394 p_data_extract_id IN NUMBER,
1395 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
1396 p_effective_start_date IN DATE,
1397 p_effective_end_date IN DATE,
1398 p_chart_of_accounts_id IN NUMBER,
1399 p_code_combination_id IN NUMBER,
1400 p_distribution_percent IN NUMBER,
1401 p_global_default_flag IN VARCHAR2,
1402 p_distribution_default_rule_id IN NUMBER,
1403 p_rowid IN OUT NOCOPY VARCHAR2,
1404 p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
1405 p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
1406 p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
1407 p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
1408 p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
1409 p_description IN VARCHAR2:= FND_API.G_MISS_CHAR,
1410 p_mode IN VARCHAR2 := 'R'
1411 ) IS
1412
1413 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Distribution';
1414 l_api_version CONSTANT NUMBER := 1.0;
1415
1416 l_userid NUMBER;
1417 l_loginid NUMBER;
1418
1419 l_init_index BINARY_INTEGER;
1420 l_dist_index BINARY_INTEGER;
1421
1422 l_distribution_id NUMBER;
1423
1424 l_created_record VARCHAR2(1) := FND_API.G_FALSE;
1425 l_updated_record VARCHAR2(1);
1426
1427 l_rowid VARCHAR2(100);
1428
1429 l_return_status VARCHAR2(1);
1430 l_dis_overlap VARCHAR2(1):= FND_API.G_FALSE;
1431
1432 cursor c_Seq is
1433 select psb_position_pay_distr_s.nextval DistID
1434 from dual;
1435
1436 cursor c_Dist is
1437 select distribution_id,
1438 position_id,
1439 data_extract_id,
1440 worksheet_id,
1441 effective_start_date,
1442 effective_end_date,
1443 chart_of_accounts_id,
1444 code_combination_id,
1445 distribution_percent,
1446 global_default_flag,
1447 distribution_default_rule_id,
1448 project_id,
1449 task_id,
1450 award_id,
1451 expenditure_type,
1452 expenditure_organization_id,
1453 description
1454 from PSB_POSITION_PAY_DISTRIBUTIONS
1455 where (worksheet_id is null or worksheet_id = p_worksheet_id)
1456 and chart_of_accounts_id = p_chart_of_accounts_id
1457 and code_combination_id = p_code_combination_id
1458 and (((p_effective_end_date is not null)
1459 and (((effective_start_date <= p_effective_end_date)
1460 and (effective_end_date is null))
1461 or ((effective_start_date between p_effective_start_date and p_effective_end_date)
1462 or (effective_end_date between p_effective_start_date and p_effective_end_date)
1463 or ((effective_start_date < p_effective_start_date)
1464 and (effective_end_date > p_effective_end_date)))))
1465 or ((p_effective_end_date is null)
1466 and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
1467 and position_id = p_position_id;
1468
1469 BEGIN
1470
1471 -- Standard Start of API savepoint
1472
1473 SAVEPOINT Modify_Distribution_Pvt;
1474
1475
1476 -- Standard call to check for call compatibility
1477
1478 if not FND_API.Compatible_API_Call (l_api_version,
1479 p_api_version,
1480 l_api_name,
1481 G_PKG_NAME)
1482 then
1483 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1484 end if;
1485
1486 -- Initialize message list if p_init_msg_list is set to TRUE
1487
1488 if FND_API.to_Boolean (p_init_msg_list) then
1489 FND_MSG_PUB.initialize;
1490 end if;
1491
1492 l_userid := FND_GLOBAL.USER_ID;
1493 l_loginid := FND_GLOBAL.LOGIN_ID;
1494
1495 update PSB_POSITION_PAY_DISTRIBUTIONS
1496 set distribution_percent = decode(p_distribution_percent, null, distribution_percent, p_distribution_percent),
1497 global_default_flag = decode(p_global_default_flag, null, global_default_flag, p_global_default_flag),
1498 distribution_default_rule_id = decode(p_distribution_default_rule_id, null, distribution_default_rule_id, p_distribution_default_rule_id),
1499 project_id = decode(p_project_id, null, project_id, FND_API.G_MISS_NUM, project_id, p_project_id),
1500 task_id = decode(p_task_id, null, project_id, FND_API.G_MISS_NUM, task_id, p_task_id),
1501 award_id = decode(p_award_id, null, award_id, FND_API.G_MISS_NUM, award_id, p_award_id),
1502 expenditure_type = decode(p_expenditure_type, null, expenditure_type, FND_API.G_MISS_CHAR, expenditure_type, p_expenditure_type),
1503 expenditure_organization_id = decode(p_expenditure_organization_id, null, expenditure_organization_id, FND_API.G_MISS_NUM, expenditure_organization_id, p_expenditure_organization_id),
1504 description = decode(p_description, null, description, FND_API.G_MISS_CHAR, description, p_description),
1505 -- Added for Bug: 3325171
1506 effective_end_date = decode(p_effective_end_date, null, effective_end_date, FND_API.G_MISS_DATE, effective_end_date, p_effective_end_date),
1507 last_update_date = sysdate,
1508 last_updated_by = l_userid,
1509 last_update_login = l_loginid
1510 where position_id = p_position_id
1511 and effective_start_date = p_effective_start_date
1512 and nvl(effective_end_date, FND_API.G_MISS_DATE) = nvl(p_effective_end_date, FND_API.G_MISS_DATE)
1513 and nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
1514 and chart_of_accounts_id = p_chart_of_accounts_id
1515 and code_combination_id = p_code_combination_id;
1516
1517 if SQL%NOTFOUND then
1518 begin
1519
1520 for l_init_index in 1..g_pay_dist.Count loop
1521 g_pay_dist(l_init_index).distribution_id := null;
1522 g_pay_dist(l_init_index).position_id := null;
1523 g_pay_dist(l_init_index).data_extract_id := null;
1524 g_pay_dist(l_init_index).worksheet_id := null;
1525 g_pay_dist(l_init_index).effective_start_date := null;
1526 g_pay_dist(l_init_index).effective_end_date := null;
1527 g_pay_dist(l_init_index).chart_of_accounts_id := null;
1528 g_pay_dist(l_init_index).code_combination_id := null;
1529 g_pay_dist(l_init_index).distribution_percent := null;
1530 g_pay_dist(l_init_index).global_default_flag := null;
1531 g_pay_dist(l_init_index).dist_default_rule_id := null;
1532 g_pay_dist(l_init_index).project_id := null;
1533 g_pay_dist(l_init_index).task_id:= null;
1534 g_pay_dist(l_init_index).award_id:= null;
1535 g_pay_dist(l_init_index).expenditure_type:= null;
1536 g_pay_dist(l_init_index).expenditure_organization_id:= null;
1537 g_pay_dist(l_init_index).description:= null;
1538 g_pay_dist(l_init_index).delete_flag := null;
1539 end loop;
1540
1541 g_num_pay_dist := 0;
1542
1543 for c_Dist_Rec in c_Dist loop
1544
1545 g_num_pay_dist := g_num_pay_dist + 1;
1546
1547 g_pay_dist(g_num_pay_dist).distribution_id := c_Dist_Rec.distribution_id;
1548 g_pay_dist(g_num_pay_dist).position_id := c_Dist_Rec.position_id;
1549 g_pay_dist(g_num_pay_dist).data_extract_id := c_Dist_Rec.data_extract_id;
1550 g_pay_dist(g_num_pay_dist).worksheet_id := c_Dist_Rec.worksheet_id;
1551 g_pay_dist(g_num_pay_dist).effective_start_date := c_Dist_Rec.effective_start_date;
1552 g_pay_dist(g_num_pay_dist).effective_end_date := c_Dist_Rec.effective_end_date;
1553 g_pay_dist(g_num_pay_dist).chart_of_accounts_id := c_Dist_Rec.chart_of_accounts_id;
1554 g_pay_dist(g_num_pay_dist).code_combination_id := c_Dist_Rec.code_combination_id;
1555 g_pay_dist(g_num_pay_dist).distribution_percent := c_Dist_Rec.distribution_percent;
1556 g_pay_dist(g_num_pay_dist).global_default_flag := c_Dist_Rec.global_default_flag;
1557 g_pay_dist(g_num_pay_dist).dist_default_rule_id := c_Dist_Rec.distribution_default_rule_id;
1558 g_pay_dist(g_num_pay_dist).project_id := c_Dist_Rec.project_id;
1559 g_pay_dist(g_num_pay_dist).task_id:= c_Dist_Rec.task_id;
1560 g_pay_dist(g_num_pay_dist).award_id:= c_Dist_Rec.award_id;
1561 g_pay_dist(g_num_pay_dist).expenditure_type:= c_Dist_Rec.expenditure_type;
1562 g_pay_dist(g_num_pay_dist).expenditure_organization_id:= c_Dist_Rec.expenditure_organization_id;
1563 g_pay_dist(g_num_pay_dist).description:= c_Dist_Rec.description;
1564 g_pay_dist(g_num_pay_dist).delete_flag := FND_API.G_TRUE;
1565
1566 if g_pay_dist(g_num_pay_dist).worksheet_id = p_worksheet_id then
1567 begin
1568
1569 if not FND_API.to_Boolean(l_dis_overlap) then
1570 l_dis_overlap := FND_API.G_TRUE;
1571 end if;
1572
1573 end;
1574 end if;
1575
1576 end loop;
1577
1578 if g_num_pay_dist = 0 then -- No matching records hence direct insert
1579 begin
1580
1581 for c_Seq_Rec in c_Seq loop
1582 l_distribution_id := c_Seq_Rec.DistID;
1583 end loop;
1584
1585 Insert_Row
1586 (p_api_version => 1.0,
1587 p_return_status => l_return_status,
1588 p_msg_count => p_msg_count,
1589 p_msg_data => p_msg_data,
1590 p_rowid => l_rowid,
1591 p_distribution_id => l_distribution_id,
1592 p_position_id => p_position_id,
1593 p_data_extract_id => p_data_extract_id,
1594 p_worksheet_id => p_worksheet_id,
1595 p_effective_start_date => p_effective_start_date,
1596 p_effective_end_date => p_effective_end_date,
1597 p_chart_of_accounts_id => p_chart_of_accounts_id,
1598 p_code_combination_id => p_code_combination_id,
1599 p_distribution_percent => p_distribution_percent,
1600 p_global_default_flag => p_global_default_flag,
1601 p_distribution_default_rule_id => p_distribution_default_rule_id,
1602 p_project_id => p_project_id,
1603 p_task_id => p_task_id,
1604 p_award_id => p_award_id,
1605 p_expenditure_type => p_expenditure_type,
1606 p_expenditure_organization_id => p_expenditure_organization_id,
1607 p_description => p_description,
1608 p_mode => p_mode);
1609
1610 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1611 raise FND_API.G_EXC_ERROR;
1612 end if;
1613
1614 p_rowid := l_rowid;
1615 p_distribution_id := l_distribution_id;
1616
1617 end; -- No Matching Records hence Direct Insert
1618 else
1619 begin -- Matching Records Check for different overlaps
1620
1621 for l_dist_index in 1..g_num_pay_dist loop
1622
1623 l_updated_record := FND_API.G_FALSE;
1624
1625 if (g_pay_dist(l_dist_index).effective_start_date = p_effective_start_date) then
1626 begin
1627
1628 if nvl(g_pay_dist(l_dist_index).worksheet_id,FND_API.G_MISS_NUM) = nvl(p_worksheet_id,FND_API.G_MISS_NUM) then
1629 begin
1630
1631 Update_Row
1632 (p_api_version => 1.0,
1633 p_return_status => l_return_status,
1634 p_msg_count => p_msg_count,
1635 p_msg_data => p_msg_data,
1636 p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
1637 p_code_combination_id => p_code_combination_id,
1638 p_distribution_percent => p_distribution_percent,
1639 p_effective_end_date => p_effective_end_date,
1640 p_global_default_flag => p_global_default_flag,
1641 p_distribution_default_rule_id => p_distribution_default_rule_id,
1642 p_project_id => p_project_id,
1643 p_task_id => p_task_id,
1644 p_award_id => p_award_id,
1645 p_expenditure_type => p_expenditure_type,
1646 p_expenditure_organization_id => p_expenditure_organization_id,
1647 p_description => p_description,
1648 p_mode => p_mode);
1649
1650 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1651 raise FND_API.G_EXC_ERROR;
1652 end if;
1653
1654 g_pay_dist(l_dist_index).delete_flag := FND_API.G_FALSE;
1655
1656 end;
1657 elsif ((g_pay_dist(l_dist_index).worksheet_id is null) and (p_worksheet_id is not null) and
1658 (not FND_API.to_Boolean(l_dis_overlap))) then
1659 begin
1660
1661 for c_Seq_Rec in c_Seq loop
1662 l_distribution_id := c_Seq_Rec.DistID;
1663 end loop;
1664
1665 Insert_Row
1666 (p_api_version => 1.0,
1667 p_return_status => l_return_status,
1668 p_msg_count => p_msg_count,
1669 p_msg_data => p_msg_data,
1670 p_rowid => l_rowid,
1671 p_distribution_id => l_distribution_id,
1672 p_position_id => p_position_id,
1673 p_data_extract_id => p_data_extract_id,
1674 p_worksheet_id => p_worksheet_id,
1675 p_effective_start_date => p_effective_start_date,
1676 p_effective_end_date => p_effective_end_date,
1677 p_chart_of_accounts_id => p_chart_of_accounts_id,
1678 p_code_combination_id => p_code_combination_id,
1679 p_distribution_percent => p_distribution_percent,
1680 p_global_default_flag => p_global_default_flag,
1681 p_distribution_default_rule_id => p_distribution_default_rule_id,
1682 p_project_id => p_project_id,
1683 p_task_id => p_task_id,
1684 p_award_id => p_award_id,
1685 p_expenditure_type => p_expenditure_type,
1686 p_expenditure_organization_id => p_expenditure_organization_id,
1687 p_description => p_description,
1688 p_mode => p_mode);
1689
1690 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1691 raise FND_API.G_EXC_ERROR;
1692 end if;
1693
1694 p_rowid := l_rowid;
1695 p_distribution_id := l_distribution_id;
1696
1697 end;
1698 end if;
1699
1700 end;-- end of effective start date matches
1701 --effective dates overlap
1702 elsif (((g_pay_dist(l_dist_index).effective_start_date <= (p_effective_start_date - 1)) and
1703 ((g_pay_dist(l_dist_index).effective_end_date is null) or
1704 (g_pay_dist(l_dist_index).effective_end_date > (p_effective_start_date - 1)))) or
1705 ((g_pay_dist(l_dist_index).effective_start_date > p_effective_start_date) and
1706 ((g_pay_dist(l_dist_index).effective_end_date is null) or
1707 (g_pay_dist(l_dist_index).effective_end_date > (p_effective_end_date + 1))))) then
1708 begin
1709
1710 if ((nvl(g_pay_dist(l_dist_index).worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM))) then
1711 begin
1712 --++ both either base or ws specific rec
1713
1714 if ((g_pay_dist(l_dist_index).effective_start_date < (p_effective_start_date - 1)) and
1715 ((g_pay_dist(l_dist_index).effective_end_date is null) or
1716 (g_pay_dist(l_dist_index).effective_end_date > (p_effective_start_date - 1)))) then
1717 begin
1718
1719 Update_Row
1720 (p_api_version => 1.0,
1721 p_return_status => l_return_status,
1722 p_msg_count => p_msg_count,
1723 p_msg_data => p_msg_data,
1724 p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
1725 p_code_combination_id => g_pay_dist(l_dist_index).code_combination_id,
1726 p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
1727 p_effective_end_date => p_effective_start_date - 1,
1728 p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
1729 p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
1730 p_project_id => g_pay_dist(l_dist_index).project_id,
1731 p_task_id => g_pay_dist(l_dist_index).task_id,
1732 p_award_id => g_pay_dist(l_dist_index).award_id,
1733 p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
1734 p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
1735 p_description => g_pay_dist(l_dist_index).description,
1736 p_mode => p_mode);
1737
1738 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1739 raise FND_API.G_EXC_ERROR;
1740 else
1741 l_updated_record := FND_API.G_TRUE;
1742 end if;
1743
1744 g_pay_dist(l_dist_index).delete_flag := FND_API.G_FALSE;
1745
1746 end; --
1747 elsif ((g_pay_dist(l_dist_index).effective_start_date > p_effective_start_date) and
1748 ((p_effective_end_date is not null) and
1749 ((g_pay_dist(l_dist_index).effective_end_date is null) or
1750 (g_pay_dist(l_dist_index).effective_end_date > (p_effective_end_date + 1))))) then
1751 begin
1752
1753 Update_Row
1754 (p_api_version => 1.0,
1755 p_return_status => l_return_status,
1756 p_msg_count => p_msg_count,
1757 p_msg_data => p_msg_data,
1758 p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
1759 p_code_combination_id => g_pay_dist(l_dist_index).code_combination_id,
1760 p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
1761 p_effective_start_date => p_effective_end_date + 1,
1762 p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
1763 p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
1764 p_project_id => g_pay_dist(l_dist_index).project_id,
1765 p_task_id => g_pay_dist(l_dist_index).task_id,
1766 p_award_id => g_pay_dist(l_dist_index).award_id,
1767 p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
1768 p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
1769 p_description => g_pay_dist(l_dist_index).description,
1770 p_mode => p_mode);
1771
1772 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1773 raise FND_API.G_EXC_ERROR;
1774 else
1775 l_updated_record := FND_API.G_FALSE;
1776 end if;
1777
1778 g_pay_dist(l_dist_index).delete_flag := FND_API.G_FALSE;
1779
1780 end;
1781 end if; -- end start date test
1782
1783 if not FND_API.to_Boolean(l_created_record) then
1784 begin
1785
1786 for c_Seq_Rec in c_Seq loop
1787 l_distribution_id := c_Seq_Rec.DistID;
1788 end loop;
1789
1790 Insert_Row
1791 (p_api_version => 1.0,
1792 p_return_status => l_return_status,
1793 p_msg_count => p_msg_count,
1794 p_msg_data => p_msg_data,
1795 p_rowid => l_rowid,
1796 p_distribution_id => l_distribution_id,
1797 p_position_id => p_position_id,
1798 p_data_extract_id => p_data_extract_id,
1799 p_worksheet_id => p_worksheet_id,
1800 p_effective_start_date => p_effective_start_date,
1801 p_effective_end_date => p_effective_end_date,
1802 p_chart_of_accounts_id => p_chart_of_accounts_id,
1803 p_code_combination_id => p_code_combination_id,
1804 p_distribution_percent => p_distribution_percent,
1805 p_global_default_flag => p_global_default_flag,
1806 p_distribution_default_rule_id => p_distribution_default_rule_id,
1807 p_project_id => p_project_id,
1808 p_task_id => p_task_id,
1809 p_award_id => p_award_id,
1810 p_expenditure_type => p_expenditure_type,
1811 p_expenditure_organization_id => p_expenditure_organization_id,
1812 p_description => p_description,
1813 p_mode => p_mode);
1814
1815 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1816 raise FND_API.G_EXC_ERROR;
1817 else
1818 l_created_record := FND_API.G_TRUE;
1819 end if;
1820
1821 p_rowid := l_rowid;
1822 p_distribution_id := l_distribution_id;
1823
1824 end;
1825 end if;
1826
1827 if p_effective_end_date is not null then
1828 begin
1829
1830 if nvl(g_pay_dist(l_dist_index).effective_end_date, (p_effective_end_date + 1)) > (p_effective_end_date + 1) then
1831 begin
1832
1833 if FND_API.to_Boolean(l_updated_record) then
1834 begin
1835
1836 for c_Seq_Rec in c_Seq loop
1837 l_distribution_id := c_Seq_Rec.DistID;
1838 end loop;
1839
1840 Insert_Row
1841 (p_api_version => 1.0,
1842 p_return_status => l_return_status,
1843 p_msg_count => p_msg_count,
1844 p_msg_data => p_msg_data,
1845 p_rowid => l_rowid,
1846 p_distribution_id => l_distribution_id,
1847 p_position_id => g_pay_dist(l_dist_index).position_id,
1848 p_data_extract_id => g_pay_dist(l_dist_index).data_extract_id,
1849 p_worksheet_id => g_pay_dist(l_dist_index).worksheet_id,
1850 p_effective_start_date => p_effective_end_date + 1,
1851 p_effective_end_date => g_pay_dist(l_dist_index).effective_end_date,
1852 p_chart_of_accounts_id => g_pay_dist(l_dist_index).chart_of_accounts_id,
1853 p_code_combination_id => g_pay_dist(l_dist_index).code_combination_id,
1854 p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
1855 p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
1856 p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
1857 p_project_id => g_pay_dist(l_dist_index).project_id,
1858 p_task_id => g_pay_dist(l_dist_index).task_id,
1859 p_award_id => g_pay_dist(l_dist_index).award_id,
1860 p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
1861 p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
1862 p_description => g_pay_dist(l_dist_index).description,
1863 p_mode => p_mode);
1864
1865 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1866 raise FND_API.G_EXC_ERROR;
1867 end if;
1868
1869 p_rowid := l_rowid;
1870 p_distribution_id := l_distribution_id;
1871
1872 end;
1873 else
1874 begin
1875
1876 Update_Row
1877 (p_api_version => 1.0,
1878 p_return_status => l_return_status,
1879 p_msg_count => p_msg_count,
1880 p_msg_data => p_msg_data,
1881 p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
1882 p_code_combination_id => g_pay_dist(l_dist_index).code_combination_id,
1883 p_effective_start_date => p_effective_end_date + 1,
1884 p_effective_end_date => g_pay_dist(l_dist_index).effective_end_date,
1885 p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
1886 p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
1887 p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
1888 p_project_id => g_pay_dist(l_dist_index).project_id,
1889 p_task_id => g_pay_dist(l_dist_index).task_id,
1890 p_award_id => g_pay_dist(l_dist_index).award_id,
1891 p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
1892 p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
1893 p_description => g_pay_dist(l_dist_index).description,
1894 p_mode => p_mode);
1895
1896 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1897 raise FND_API.G_EXC_ERROR;
1898 end if;
1899
1900 g_pay_dist(l_dist_index).delete_flag := FND_API.G_FALSE;
1901
1902 end;
1903 end if;
1904
1905 end;
1906 end if;
1907
1908 end;
1909 end if;
1910
1911 end;
1912 elsif ((g_pay_dist(l_dist_index).worksheet_id is null) and (p_worksheet_id is not null) and
1913 (not FND_API.to_Boolean(l_dis_overlap))) then
1914 begin
1915
1916 if ((g_pay_dist(l_dist_index).effective_start_date <= (p_effective_start_date - 1)) and
1917 ((g_pay_dist(l_dist_index).effective_end_date is null) or
1918 (g_pay_dist(l_dist_index).effective_end_date > (p_effective_start_date - 1)))) then
1919 begin
1920
1921 Modify_WS_Distribution
1922 (p_return_status => l_return_status,
1923 p_rowid => p_rowid,
1924 p_distribution_id => l_distribution_id,
1925 p_position_id => p_position_id,
1926 p_data_extract_id => p_data_extract_id,
1927 p_worksheet_id => p_worksheet_id,
1928 p_effective_start_date => g_pay_dist(l_dist_index).effective_start_date,
1929 p_effective_end_date => p_effective_start_date -1,
1930 p_chart_of_accounts_id => p_chart_of_accounts_id,
1931 p_code_combination_id => p_code_combination_id,
1932 p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
1933 p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
1934 p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
1935 p_project_id => g_pay_dist(l_dist_index).project_id,
1936 p_task_id => g_pay_dist(l_dist_index).task_id,
1937 p_award_id => g_pay_dist(l_dist_index).award_id,
1938 p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
1939 p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
1940 p_description => g_pay_dist(l_dist_index).description,
1941 p_mode => p_mode);
1942
1943 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1944 raise FND_API.G_EXC_ERROR;
1945 end if;
1946
1947 p_rowid := l_rowid;
1948 p_distribution_id := l_distribution_id;
1949
1950 end;
1951 elsif ((g_pay_dist(l_dist_index).effective_start_date > p_effective_start_date) and
1952 ((p_effective_end_date is not null) and
1953 ((g_pay_dist(l_dist_index).effective_end_date is null) or
1954 (g_pay_dist(l_dist_index).effective_end_date > (p_effective_end_date + 1))))) then
1955 begin
1956
1957 Modify_WS_Distribution
1958 (p_return_status => l_return_status,
1959 p_rowid => p_rowid,
1960 p_distribution_id => l_distribution_id,
1961 p_position_id => p_position_id,
1962 p_data_extract_id => p_data_extract_id,
1963 p_worksheet_id => p_worksheet_id,
1964 p_effective_start_date => p_effective_end_date + 1,
1965 p_effective_end_date => g_pay_dist(l_dist_index).effective_end_date,
1966 p_chart_of_accounts_id => p_chart_of_accounts_id,
1967 p_code_combination_id => p_code_combination_id,
1968 p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
1969 p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
1970 p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
1971 p_project_id => g_pay_dist(l_dist_index).project_id,
1972 p_task_id => g_pay_dist(l_dist_index).task_id,
1973 p_award_id => g_pay_dist(l_dist_index).award_id,
1974 p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
1975 p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
1976 p_description => g_pay_dist(l_dist_index).description,
1977 p_mode => p_mode);
1978
1979 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1980 raise FND_API.G_EXC_ERROR;
1981 end if;
1982
1983 p_rowid := l_rowid;
1984 p_distribution_id := l_distribution_id;
1985
1986 end;
1987 end if;
1988
1989 if not FND_API.to_Boolean(l_created_record) then
1990 begin
1991
1992 for c_Seq_Rec in c_Seq loop
1993 l_distribution_id := c_Seq_Rec.DistID;
1994 end loop;
1995
1996 Insert_Row
1997 (p_api_version => 1.0,
1998 p_return_status => l_return_status,
1999 p_msg_count => p_msg_count,
2000 p_msg_data => p_msg_data,
2001 p_rowid => l_rowid,
2002 p_distribution_id => l_distribution_id,
2003 p_position_id => p_position_id,
2004 p_data_extract_id => p_data_extract_id,
2005 p_worksheet_id => p_worksheet_id,
2006 p_effective_start_date => p_effective_start_date,
2007 p_effective_end_date => p_effective_end_date,
2008 p_chart_of_accounts_id => p_chart_of_accounts_id,
2009 p_code_combination_id => p_code_combination_id,
2010 p_distribution_percent => p_distribution_percent,
2011 p_global_default_flag => p_global_default_flag,
2012 p_distribution_default_rule_id => p_distribution_default_rule_id,
2013 p_project_id => p_project_id,
2014 p_task_id => p_task_id,
2015 p_award_id => p_award_id,
2016 p_expenditure_type => p_expenditure_type,
2017 p_expenditure_organization_id => p_expenditure_organization_id,
2018 p_description => p_description,
2019 p_mode => p_mode);
2020
2021 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2022 raise FND_API.G_EXC_ERROR;
2023 else
2024 l_created_record := FND_API.G_TRUE;
2025 end if;
2026
2027 p_rowid := l_rowid;
2028 p_distribution_id := l_distribution_id;
2029
2030 end;
2031 end if; -- end l_created_rec
2032
2033 if p_effective_end_date is not null then
2034 begin
2035
2036 if nvl(g_pay_dist(l_dist_index).effective_end_date, (p_effective_end_date + 1)) > (p_effective_end_date + 1) then
2037 begin
2038
2039 Modify_WS_Distribution
2040 (p_return_status => l_return_status,
2041 p_rowid => p_rowid,
2042 p_distribution_id => l_distribution_id,
2043 p_position_id => p_position_id,
2044 p_data_extract_id => p_data_extract_id,
2045 p_worksheet_id => p_worksheet_id,
2046 p_effective_start_date => p_effective_end_date + 1,
2047 p_effective_end_date => g_pay_dist(l_dist_index).effective_end_date,
2048 p_chart_of_accounts_id => p_chart_of_accounts_id,
2049 p_code_combination_id => p_code_combination_id,
2050 p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
2051 p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
2052 p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
2053 p_project_id => g_pay_dist(l_dist_index).project_id,
2054 p_task_id => g_pay_dist(l_dist_index).task_id,
2055 p_award_id => g_pay_dist(l_dist_index).award_id,
2056 p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
2057 p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
2058 p_description => g_pay_dist(l_dist_index).description,
2059 p_mode => p_mode);
2060
2061 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2062 raise FND_API.G_EXC_ERROR;
2063 end if;
2064
2065 p_rowid := l_rowid;
2066 p_distribution_id := l_distribution_id;
2067
2068 end;
2069 end if;
2070
2071 end;
2072 end if;
2073
2074 end; -- end effective date test
2075 end if;
2076
2077 end;
2078 end if;
2079
2080 end loop;
2081
2082 end;
2083 end if;
2084
2085 for l_dist_index in 1..g_num_pay_dist loop
2086
2087 if ((FND_API.to_Boolean(g_pay_dist(l_dist_index).delete_flag)) and (g_pay_dist(l_dist_index).worksheet_id is not null)) then
2088 begin
2089
2090 Delete_Row
2091 (p_api_version => 1.0,
2092 p_return_status => l_return_status,
2093 p_msg_count => p_msg_count,
2094 p_msg_data => p_msg_data,
2095 p_distribution_id => g_pay_dist(l_dist_index).distribution_id);
2096
2097 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2098 raise FND_API.G_EXC_ERROR;
2099 end if;
2100
2101 end;
2102 end if;
2103
2104 end loop;
2105
2106 end; -- SQL%NOTFOUND
2107 end if;
2108
2109 -- Standard check of p_commit
2110
2111 if FND_API.to_Boolean (p_commit) then
2112 commit work;
2113 end if;
2114
2115
2116 -- Initialize API return status to success
2117
2118 p_return_status := FND_API.G_RET_STS_SUCCESS;
2119
2120
2121 -- Standard call to get message count and if count is 1, get message info
2122
2123 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2124 p_data => p_msg_data);
2125
2126 EXCEPTION
2127
2128 when FND_API.G_EXC_ERROR then
2129 rollback to Modify_Distribution_Pvt;
2130 p_return_status := FND_API.G_RET_STS_ERROR;
2131
2132 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2133 p_data => p_msg_data);
2134
2135 when FND_API.G_EXC_UNEXPECTED_ERROR then
2136 rollback to Modify_Distribution_Pvt;
2137 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2138
2139 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2140 p_data => p_msg_data);
2141
2142 when OTHERS then
2143 rollback to Modify_Distribution_Pvt;
2144 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2145
2146 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2147
2148 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2149 l_api_name);
2150 end if;
2151 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2152 p_data => p_msg_data);
2153
2154 END Modify_Distribution;
2155
2156 -- +++
2157
2158 PROCEDURE Modify_WS_Distribution
2159 ( p_return_status OUT NOCOPY VARCHAR2,
2160 p_distribution_id IN OUT NOCOPY NUMBER,
2161 p_position_id IN NUMBER,
2162 p_data_extract_id IN NUMBER,
2163 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
2164 p_effective_start_date IN DATE,
2165 p_effective_end_date IN DATE,
2166 p_chart_of_accounts_id IN NUMBER,
2167 p_code_combination_id IN NUMBER,
2168 p_distribution_percent IN NUMBER,
2169 p_global_default_flag IN VARCHAR2,
2170 p_distribution_default_rule_id IN NUMBER,
2171 p_rowid IN OUT NOCOPY VARCHAR2,
2172 p_project_id IN NUMBER,
2173 p_task_id IN NUMBER,
2174 p_award_id IN NUMBER,
2175 p_expenditure_type IN VARCHAR2,
2176 p_expenditure_organization_id IN NUMBER,
2177 p_description IN VARCHAR2 ,
2178 p_mode IN VARCHAR2
2179 ) IS
2180
2181 l_return_status VARCHAR2(1);
2182 l_msg_count NUMBER;
2183 l_msg_data VARCHAR2(2000);
2184
2185 l_distribution_id NUMBER;
2186 l_rowid VARCHAR2(100);
2187 l_distr_found VARCHAR2(1) := FND_API.G_FALSE;
2188
2189 cursor c_Seq is
2190 select psb_position_pay_distr_s.nextval DistID
2191 from dual;
2192
2193 cursor c_overlap is
2194 select distribution_id
2195 from PSB_POSITION_PAY_DISTRIBUTIONS
2196 where chart_of_accounts_id = p_chart_of_accounts_id
2197 and worksheet_id = p_worksheet_id
2198 and code_combination_id = p_code_combination_id
2199 and (((p_effective_end_date is not null)
2200 and (((effective_start_date <= p_effective_end_date)
2201 and (effective_end_date is null))
2202 or ((effective_start_date between p_effective_start_date and p_effective_end_date)
2203 or (effective_end_date between p_effective_start_date and p_effective_end_date)
2204 or ((effective_start_date < p_effective_start_date)
2205 and (effective_end_date > p_effective_end_date)))))
2206 or ((p_effective_end_date is null)
2207 and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
2208 and position_id = p_position_id;
2209
2210 BEGIN
2211
2212 for c_Overlap_Rec in c_Overlap loop
2213 l_distr_found := FND_API.G_TRUE;
2214 end loop;
2215
2216 if not FND_API.to_Boolean(l_distr_found) then
2217 begin
2218
2219 for c_Seq_Rec in c_Seq loop
2220 l_distribution_id := c_Seq_Rec.DistID;
2221 end loop;
2222
2223 Insert_Row
2224 (p_api_version => 1.0,
2225 p_return_status => l_return_status,
2226 p_msg_count => l_msg_count,
2227 p_msg_data => l_msg_data,
2228 p_rowid => l_rowid,
2229 p_distribution_id => l_distribution_id,
2230 p_position_id => p_position_id,
2231 p_data_extract_id => p_data_extract_id,
2232 p_worksheet_id => p_worksheet_id,
2233 p_effective_start_date => p_effective_start_date,
2234 p_effective_end_date => p_effective_end_date,
2235 p_chart_of_accounts_id => p_chart_of_accounts_id,
2236 p_code_combination_id => p_code_combination_id,
2237 p_distribution_percent => p_distribution_percent,
2238 p_global_default_flag => p_global_default_flag,
2239 p_distribution_default_rule_id => p_distribution_default_rule_id,
2240 p_project_id => p_project_id,
2241 p_task_id => p_task_id,
2242 p_award_id => p_award_id,
2243 p_expenditure_type => p_expenditure_type,
2244 p_expenditure_organization_id => p_expenditure_organization_id,
2245 p_description => p_description,
2246 p_mode => p_mode );
2247
2248 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2249 raise FND_API.G_EXC_ERROR;
2250 end if;
2251
2252 p_rowid := l_rowid;
2253 p_distribution_id := l_distribution_id;
2254
2255 end;
2256 end if;
2257
2258 p_return_status := FND_API.G_RET_STS_SUCCESS;
2259
2260
2261 EXCEPTION
2262
2263 when FND_API.G_EXC_ERROR then
2264 p_return_status := FND_API.G_RET_STS_ERROR;
2265
2266 when FND_API.G_EXC_UNEXPECTED_ERROR then
2267 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2268
2269 when OTHERS then
2270 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2271
2272 END Modify_WS_Distribution;
2273
2274 --+++
2275
2276 PROCEDURE Modify_Extract_Distribution
2277 ( p_api_version IN NUMBER,
2278 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2279 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2280 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2281 p_return_status OUT NOCOPY VARCHAR2,
2282 p_msg_count OUT NOCOPY NUMBER,
2283 p_msg_data OUT NOCOPY VARCHAR2,
2284 p_position_id IN NUMBER,
2285 p_data_extract_id IN NUMBER,
2286 p_chart_of_accounts_id IN NUMBER,
2287 p_distribution IN OUT NOCOPY PSB_HR_POPULATE_DATA_PVT.gl_distribution_tbl_type
2288 ) IS
2289
2290 Cursor C_Distributions is
2291 Select distribution_id,
2292 code_combination_id,
2293 project_id,
2294 task_id,
2295 award_id,
2296 expenditure_type,
2297 expenditure_organization_id,
2298 distribution_percent,
2299 effective_start_date,
2300 effective_end_date,
2301 chart_of_accounts_id,
2302 global_default_flag,
2303 distribution_default_rule_id,
2304 rowid
2305 from psb_position_pay_distributions
2306 where position_id = p_position_id
2307 and worksheet_id is null;
2308
2309 cursor c_Seq is
2310 select psb_position_pay_distr_s.nextval DistID
2311 from dual;
2312
2313 l_distribution_id NUMBER;
2314 del_flag VARCHAR2(1);
2315 l_return_status VARCHAR2(1);
2316 l_msg_count NUMBER;
2317 l_msg_data VARCHAR2(2000);
2318 l_rowid VARCHAR2(100);
2319
2320 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Extract_Distribution';
2321 l_api_version CONSTANT NUMBER := 1.0;
2322
2323
2324 BEGIN
2325
2326 -- Standard Start of API savepoint
2327
2328 SAVEPOINT Modify_Extract_Dist_Pvt;
2329
2330
2331 -- Standard call to check for call compatibility
2332
2333 if not FND_API.Compatible_API_Call (l_api_version,
2334 p_api_version,
2335 l_api_name,
2336 G_PKG_NAME)
2337 then
2338 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2339 end if;
2340
2341
2342 -- Initialize message list if p_init_msg_list is set to TRUE
2343
2344 if FND_API.to_Boolean (p_init_msg_list) then
2345 FND_MSG_PUB.initialize;
2346 end if;
2347
2348 for C_Distribution_Rec in C_Distributions
2349 Loop
2350 del_flag := 'Y';
2351 for j in 1..p_distribution.count
2352 Loop
2353 if (C_Distribution_Rec.code_combination_id is null) then
2354 if ((C_Distribution_Rec.project_id = p_distribution(j).project_id) and
2355 (C_Distribution_Rec.award_id = p_distribution(j).award_id) and
2356 (C_Distribution_Rec.task_id = p_distribution(j).task_id) and
2357 (C_Distribution_Rec.expenditure_type = p_distribution(j).expenditure_type) and
2358 (C_Distribution_Rec.expenditure_organization_id = p_distribution(j).expenditure_org_id) and
2359 (C_Distribution_Rec.effective_start_date = p_distribution(j).effective_start_date) ) then
2360 del_flag := 'N';
2361 p_distribution(j).exist_flag := 'Y';
2362 end if;
2363 else
2364 if ((C_Distribution_Rec.code_combination_id = p_distribution(j).ccid) and
2365 (C_Distribution_Rec.effective_start_date = p_distribution(j).effective_start_date) ) then
2366 p_distribution(j).exist_flag := 'Y';
2367 del_flag := 'N';
2368 end if;
2369 end if;
2370
2371 if ((del_flag = 'N') and (p_distribution(j).exist_flag = 'Y')) then
2372 Update_Row
2373 (p_api_version => 1.0,
2374 p_return_status => l_return_status,
2375 p_msg_count => p_msg_count,
2376 p_msg_data => p_msg_data,
2377 p_distribution_id => C_Distribution_Rec.distribution_id,
2378 /* Bug#2869982 Start */
2379 p_code_combination_id => p_distribution(j).ccid,
2380 --p_code_combination_id => C_Distribution_Rec.code_combination_id,
2381 /* Bug#2869982 End */
2382 p_distribution_percent => p_distribution(j).distr_percent,
2383 p_effective_end_date => p_distribution(j).effective_end_date,
2384 p_global_default_flag => C_Distribution_Rec.global_default_flag,
2385 p_distribution_default_rule_id => C_Distribution_Rec.distribution_default_rule_id,
2386 p_project_id => p_distribution(j).project_id,
2387 p_task_id => p_distribution(j).task_id,
2388 p_award_id => p_distribution(j).award_id,
2389 p_expenditure_type => p_distribution(j).expenditure_type,
2390 p_expenditure_organization_id => p_distribution(j).expenditure_org_id,
2391 p_description => p_distribution(j).description,
2392 p_mode => 'R');
2393
2394 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2395 raise FND_API.G_EXC_ERROR;
2396 end if;
2397 end if;
2398 End Loop;
2399
2400 if (del_flag = 'Y') then
2401 DELETE_ROW (
2402 p_api_version => 1.0,
2403 p_init_msg_list => fnd_api.g_false,
2404 p_commit => p_commit,
2405 p_validation_level => fnd_api.g_valid_level_full,
2406 p_return_status => l_return_status,
2407 p_msg_count => l_msg_count,
2408 p_msg_data => l_msg_data,
2409 p_distribution_id => C_Distribution_Rec.distribution_id);
2410
2411 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2412 raise FND_API.G_EXC_ERROR;
2413 end if;
2414 end if;
2415 End Loop;
2416
2417 for j in 1..p_distribution.count
2418 Loop
2419 if (p_distribution(j).exist_flag <> 'Y') then
2420 /* Insert new distribution */
2421 for c_Seq_Rec in c_Seq loop
2422 l_distribution_id := c_Seq_Rec.DistID;
2423 end loop;
2424
2425 Insert_Row
2426 (p_api_version => 1.0,
2427 p_return_status => l_return_status,
2428 p_msg_count => p_msg_count,
2429 p_msg_data => p_msg_data,
2430 p_rowid => l_rowid,
2431 p_distribution_id => l_distribution_id,
2432 p_position_id => p_position_id,
2433 p_data_extract_id => p_data_extract_id,
2434 p_effective_start_date => p_distribution(j).effective_start_date,
2435 p_effective_end_date => p_distribution(j).effective_end_date,
2436 p_chart_of_accounts_id => p_chart_of_accounts_id,
2437 p_code_combination_id => p_distribution(j).ccid,
2438 p_distribution_percent => p_distribution(j).distr_percent,
2439 p_global_default_flag => null,
2440 p_distribution_default_rule_id => null,
2441 p_project_id => p_distribution(j).project_id,
2442 p_task_id => p_distribution(j).task_id,
2443 p_award_id => p_distribution(j).award_id,
2444 p_expenditure_type => p_distribution(j).expenditure_type,
2445 p_expenditure_organization_id => p_distribution(j).expenditure_org_id,
2446 p_description => p_distribution(j).description,
2447 p_mode => 'R');
2448
2449 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2450 raise FND_API.G_EXC_ERROR;
2451 end if;
2452
2453 end if;
2454 End Loop;
2455
2456 -- Standard check of p_commit
2457
2458 if FND_API.to_Boolean (p_commit) then
2459 commit work;
2460 end if;
2461
2462
2463 -- Initialize API return status to success
2464
2465 p_return_status := FND_API.G_RET_STS_SUCCESS;
2466
2467
2468 -- Standard call to get message count and if count is 1, get message info
2469
2470 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2471 p_data => p_msg_data);
2472
2473 EXCEPTION
2474
2475 when FND_API.G_EXC_ERROR then
2476 rollback to Modify_Extract_Dist_Pvt;
2477 p_return_status := FND_API.G_RET_STS_ERROR;
2478
2479 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2480 p_data => p_msg_data);
2481
2482
2483 when FND_API.G_EXC_UNEXPECTED_ERROR then
2484 rollback to Modify_Extract_Dist_Pvt;
2485 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2486
2487 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2488 p_data => p_msg_data);
2489
2490
2491 when OTHERS then
2492 rollback to Modify_Extract_Dist_Pvt;
2493 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2494
2495 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2496
2497 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2498 l_api_name);
2499 end if;
2500
2501 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2502 p_data => p_msg_data);
2503
2504 END Modify_Extract_Distribution;
2505
2506 /* ------------------------------------------------------------------------- */
2507 -- Add Token and Value to the Message Token array
2508
2509 PROCEDURE message_token(tokname IN VARCHAR2,
2510 tokval IN VARCHAR2) IS
2511
2512 BEGIN
2513
2514 if no_msg_tokens is null then
2515 no_msg_tokens := 1;
2516 else
2517 no_msg_tokens := no_msg_tokens + 1;
2518 end if;
2519
2520 msg_tok_names(no_msg_tokens) := tokname;
2521 msg_tok_val(no_msg_tokens) := tokval;
2522
2523 END message_token;
2524
2525 /* ----------------------------------------------------------------------- */
2526
2527 -- Define a Message Token with a Value and set the Message Name
2528
2529 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
2530 -- retrieved by the calling program.
2531
2532 PROCEDURE add_message(appname IN VARCHAR2,
2533 msgname IN VARCHAR2) IS
2534
2535 i BINARY_INTEGER;
2536
2537 BEGIN
2538
2539 if ((appname is not null) and
2540 (msgname is not null)) then
2541
2542 FND_MESSAGE.SET_NAME(appname, msgname);
2543
2544 if no_msg_tokens is not null then
2545 for i in 1..no_msg_tokens loop
2546 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
2547 end loop;
2548 end if;
2549
2550 FND_MSG_PUB.Add;
2551
2552 end if;
2553
2554 -- Clear Message Token stack
2555
2556 no_msg_tokens := 0;
2557
2558 END add_message;
2559
2560 /* ----------------------------------------------------------------------- */
2561
2562 -- Get Debug Information
2563
2564 -- This Module is used to retrieve Debug Information for Funds Checker. It
2565 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
2566 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
2567 -- 'Serveroutput' should be set to 'ON'
2568
2569 FUNCTION get_debug RETURN VARCHAR2 IS
2570
2571 BEGIN
2572
2573 return(g_dbug);
2574
2575 END get_debug;
2576
2577 /* ----------------------------------------------------------------------- */
2578
2579 /* ----------------------------------------------------------------------- */
2580 /* Bug 1308558 Start */
2581 -- Mass position assignment rules enhancement
2582 -- This api is used for applying default rule account distributions to various
2583 -- positions
2584
2585 PROCEDURE Apply_Position_Pay_Distr
2586 ( p_api_version IN NUMBER,
2587 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2588 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2589 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2590 x_return_status OUT NOCOPY VARCHAR2,
2591 x_msg_count OUT NOCOPY NUMBER,
2592 x_msg_data OUT NOCOPY VARCHAR2,
2593 p_distribution_id IN OUT NOCOPY NUMBER,
2594 p_position_id IN NUMBER,
2595 p_data_extract_id IN NUMBER,
2596 p_worksheet_id IN NUMBER,
2597 p_effective_start_date IN DATE,
2598 p_effective_end_date IN DATE,
2599 p_modify_flag IN VARCHAR2,
2600 p_chart_of_accounts_id IN NUMBER,
2601 p_code_combination_id IN NUMBER,
2602 p_distribution_percent IN NUMBER,
2603 p_global_default_flag IN VARCHAR2,
2604 p_distribution_default_rule_id IN NUMBER,
2605 p_rowid IN OUT NOCOPY VARCHAR2,
2606 p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
2607 p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
2608 p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
2609 p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
2610 p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
2611 p_description IN VARCHAR2:= FND_API.G_MISS_CHAR,
2612 p_mode IN VARCHAR2 := 'R'
2613 ) IS
2614
2615 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Position_Pay_Distr';
2616 l_api_version CONSTANT NUMBER := 1.0;
2617
2618 l_userid NUMBER;
2619 l_loginid NUMBER;
2620
2621 l_init_index BINARY_INTEGER;
2622 l_dist_index BINARY_INTEGER;
2623
2624 l_distribution_id NUMBER;
2625
2626 l_created_record VARCHAR2(1) := FND_API.G_FALSE;
2627 l_updated_record VARCHAR2(1);
2628
2629 l_rowid VARCHAR2(100);
2630
2631 l_return_status VARCHAR2(1);
2632 l_dis_overlap VARCHAR2(1):= FND_API.G_FALSE;
2633 l_dist_percent_sum NUMBER;
2634 l_distribution_percent NUMBER;
2635 l_ccid_exists BOOLEAN:=FALSE;
2636
2637 CURSOR c_Seq IS
2638 SELECT psb_position_pay_distr_s.NEXTVAL DistID
2639 FROM dual;
2640
2641 CURSOR c_Dist IS
2642 SELECT distribution_id,
2643 position_id,
2644 data_extract_id,
2645 worksheet_id,
2646 effective_start_date,
2647 effective_end_date,
2648 chart_of_accounts_id,
2649 code_combination_id,
2650 distribution_percent,
2651 global_default_flag,
2652 distribution_default_rule_id,
2653 project_id,
2654 task_id,
2655 award_id,
2656 expenditure_type,
2657 expenditure_organization_id,
2658 description
2659 FROM PSB_POSITION_PAY_DISTRIBUTIONS
2660 WHERE
2661 /* Bug 4545909 Start */
2662 ((worksheet_id IS NULL AND NOT EXISTS (
2663 SELECT 1 FROM psb_position_pay_distributions
2664 WHERE worksheet_id = p_worksheet_id
2665 AND position_id = p_position_id))
2666 OR worksheet_id = p_worksheet_id
2667 OR (worksheet_id IS NULL AND p_worksheet_id IS NULL))
2668 /* Bug 4545909 End */
2669 AND chart_of_accounts_id = p_chart_of_accounts_id
2670 AND code_combination_id = p_code_combination_id
2671 AND (((p_effective_end_date IS NOT NULL)
2672 AND (((effective_start_date <= p_effective_end_date)
2673 AND (effective_end_date IS NULL))
2674 OR ((effective_start_date BETWEEN p_effective_start_date AND p_effective_end_date)
2675 OR (effective_end_date BETWEEN p_effective_start_date AND p_effective_end_date)
2676 OR ((effective_start_date < p_effective_start_date)
2677 AND (effective_end_date > p_effective_end_date)))))
2678 OR ((p_effective_end_date IS NULL)
2679 AND (NVL(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
2680 AND position_id = p_position_id;
2681
2682 /* Bug 4545909 Start */
2683 l_de_exists BOOLEAN := FALSE;
2684 CURSOR l_exists IS
2685 SELECT 1
2686 FROM PSB_POSITION_PAY_DISTRIBUTIONS
2687 WHERE data_extract_id = p_data_extract_id
2688 AND position_id = p_position_id and worksheet_id IS NULL;
2689 /* Bug 4545909 End */
2690
2691 BEGIN
2692
2693 -- Standard Start of API savepoint
2694
2695 SAVEPOINT Apply_Position_Pay_Distr;
2696
2697
2698 -- Standard call to check for call compatibility
2699
2700 IF NOT FND_API.Compatible_API_Call (l_api_version,
2701 p_api_version,
2702 l_api_name,
2703 G_PKG_NAME)
2704 THEN
2705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2706 END IF;
2707
2708 -- Initialize message list if p_init_msg_list is set to TRUE
2709
2710 IF FND_API.to_Boolean (p_init_msg_list) THEN
2711 FND_MSG_PUB.initialize;
2712 END IF;
2713
2714 l_userid := FND_GLOBAL.USER_ID;
2715 l_loginid := FND_GLOBAL.LOGIN_ID;
2716
2717
2718 FOR l_init_index IN 1..g_pay_dist.COUNT LOOP
2719 g_pay_dist(l_init_index).distribution_id := NULL;
2720 g_pay_dist(l_init_index).position_id := NULL;
2721 g_pay_dist(l_init_index).data_extract_id := NULL;
2722 g_pay_dist(l_init_index).worksheet_id := NULL;
2723 g_pay_dist(l_init_index).effective_start_date := NULL;
2724 g_pay_dist(l_init_index).effective_end_date := NULL;
2725 g_pay_dist(l_init_index).chart_of_accounts_id := NULL;
2726 g_pay_dist(l_init_index).code_combination_id := NULL;
2727 g_pay_dist(l_init_index).distribution_percent := NULL;
2728 g_pay_dist(l_init_index).global_default_flag := NULL;
2729 g_pay_dist(l_init_index).dist_default_rule_id := NULL;
2730 g_pay_dist(l_init_index).project_id := NULL;
2731 g_pay_dist(l_init_index).task_id := NULL;
2732 g_pay_dist(l_init_index).award_id := NULL;
2733 g_pay_dist(l_init_index).expenditure_type := NULL;
2734 g_pay_dist(l_init_index).expenditure_organization_id:= NULL;
2735 g_pay_dist(l_init_index).description := NULL;
2736 g_pay_dist(l_init_index).delete_flag := NULL;
2737 END LOOP;
2738
2739 g_num_pay_dist := 0;
2740
2741 FOR c_Dist_Rec IN c_Dist LOOP
2742
2743 g_num_pay_dist := g_num_pay_dist + 1;
2744
2745 g_pay_dist(g_num_pay_dist).distribution_id := c_Dist_Rec.distribution_id;
2746 g_pay_dist(g_num_pay_dist).position_id := c_Dist_Rec.position_id;
2747 g_pay_dist(g_num_pay_dist).data_extract_id := c_Dist_Rec.data_extract_id;
2748 g_pay_dist(g_num_pay_dist).worksheet_id := c_Dist_Rec.worksheet_id;
2749 g_pay_dist(g_num_pay_dist).effective_start_date := c_Dist_Rec.effective_start_date;
2750 g_pay_dist(g_num_pay_dist).effective_end_date := c_Dist_Rec.effective_end_date;
2751 g_pay_dist(g_num_pay_dist).chart_of_accounts_id := c_Dist_Rec.chart_of_accounts_id;
2752 g_pay_dist(g_num_pay_dist).code_combination_id := c_Dist_Rec.code_combination_id;
2753 g_pay_dist(g_num_pay_dist).distribution_percent := c_Dist_Rec.distribution_percent;
2754 g_pay_dist(g_num_pay_dist).global_default_flag := c_Dist_Rec.global_default_flag;
2755 g_pay_dist(g_num_pay_dist).dist_default_rule_id := c_Dist_Rec.distribution_default_rule_id;
2756 g_pay_dist(g_num_pay_dist).project_id := c_Dist_Rec.project_id;
2757 g_pay_dist(g_num_pay_dist).task_id:= c_Dist_Rec.task_id;
2758 g_pay_dist(g_num_pay_dist).award_id:= c_Dist_Rec.award_id;
2759 g_pay_dist(g_num_pay_dist).expenditure_type:= c_Dist_Rec.expenditure_type;
2760 g_pay_dist(g_num_pay_dist).expenditure_organization_id:= c_Dist_Rec.expenditure_organization_id;
2761 g_pay_dist(g_num_pay_dist).description:= c_Dist_Rec.description;
2762 g_pay_dist(g_num_pay_dist).delete_flag := FND_API.G_TRUE;
2763
2764 END LOOP;
2765
2766 FOR l_exists_rec in l_exists
2767 LOOP
2768 l_de_exists := TRUE;
2769 END LOOP;
2770
2771 -- the following code processes overwrite default rule
2772 IF p_modify_flag = 'Y' THEN
2773
2774 FOR c_Seq_Rec IN c_Seq LOOP
2775 l_distribution_id := c_Seq_Rec.DistID;
2776 END LOOP;
2777
2778 -- Bug 4545909. The following IF clause is added
2779 -- first insert_row call create worksheet level record
2780 -- second insert_row call create extract level record
2781 IF l_de_exists THEN
2782 Insert_Row
2783 (p_api_version => 1.0,
2784 p_return_status => l_return_status,
2785 p_msg_count => x_msg_count,
2786 p_msg_data => x_msg_data,
2787 p_rowid => l_rowid,
2788 p_distribution_id => l_distribution_id,
2789 p_position_id => p_position_id,
2790 p_data_extract_id => p_data_extract_id,
2791 p_worksheet_id => p_worksheet_id,
2792 p_effective_start_date => p_effective_start_date,
2793 p_effective_end_date => p_effective_end_date,
2794 p_chart_of_accounts_id => p_chart_of_accounts_id,
2795 p_code_combination_id => p_code_combination_id,
2796 p_distribution_percent => p_distribution_percent,
2797 p_global_default_flag => p_global_default_flag,
2798 p_distribution_default_rule_id => p_distribution_default_rule_id,
2799 p_project_id => p_project_id,
2800 p_task_id => p_task_id,
2801 p_award_id => p_award_id,
2802 p_expenditure_type => p_expenditure_type,
2803 p_expenditure_organization_id => p_expenditure_organization_id,
2804 p_description => p_description,
2805 p_mode => p_mode);
2806 ELSE
2807 Insert_Row
2808 (p_api_version => 1.0,
2809 p_return_status => l_return_status,
2810 p_msg_count => x_msg_count,
2811 p_msg_data => x_msg_data,
2812 p_rowid => l_rowid,
2813 p_distribution_id => l_distribution_id,
2814 p_position_id => p_position_id,
2815 p_data_extract_id => p_data_extract_id,
2816 p_worksheet_id => NULL,
2817 p_effective_start_date => p_effective_start_date,
2818 p_effective_end_date => p_effective_end_date,
2819 p_chart_of_accounts_id => p_chart_of_accounts_id,
2820 p_code_combination_id => p_code_combination_id,
2821 p_distribution_percent => p_distribution_percent,
2822 p_global_default_flag => p_global_default_flag,
2823 p_distribution_default_rule_id => p_distribution_default_rule_id,
2824 p_project_id => p_project_id,
2825 p_task_id => p_task_id,
2826 p_award_id => p_award_id,
2827 p_expenditure_type => p_expenditure_type,
2828 p_expenditure_organization_id => p_expenditure_organization_id,
2829 p_description => p_description,
2830 p_mode => p_mode);
2831
2832 END IF;
2833
2834 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2835 RAISE FND_API.G_EXC_ERROR;
2836 END IF;
2837
2838 p_rowid := l_rowid;
2839 p_distribution_id := l_distribution_id;
2840
2841
2842 ELSE
2843 -- the following code processes non-overwrite default rule
2844
2845 l_dist_percent_sum := PSB_POSITIONS_PVT.g_distr_percent_total;
2846
2847
2848 IF l_dist_percent_sum < 100 THEN
2849
2850 l_distribution_percent
2851 := (100 - l_dist_percent_sum) * p_distribution_percent/100 ;
2852
2853 FOR l_dist_index IN 1..g_num_pay_dist LOOP
2854
2855 IF g_pay_dist(l_dist_index).code_combination_id = p_code_combination_id THEN
2856
2857 Update_Row
2858 (p_api_version => 1.0,
2859 p_return_status => l_return_status,
2860 p_msg_count => x_msg_count,
2861 p_msg_data => x_msg_data,
2862 p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
2863 p_code_combination_id => p_code_combination_id,
2864 p_distribution_percent =>
2865 g_pay_dist(l_dist_index).distribution_percent + l_distribution_percent,
2866 p_effective_end_date => p_effective_end_date,
2867 p_global_default_flag => p_global_default_flag,
2868 p_distribution_default_rule_id => p_distribution_default_rule_id,
2869 p_project_id => p_project_id,
2870 p_task_id => p_task_id,
2871 p_award_id => p_award_id,
2872 p_expenditure_type => p_expenditure_type,
2873 p_expenditure_organization_id => p_expenditure_organization_id,
2874 p_description => p_description,
2875 p_mode => p_mode);
2876
2877 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2878 RAISE FND_API.G_EXC_ERROR;
2879 END IF;
2880 l_ccid_exists := TRUE;
2881
2882 END IF;
2883 END LOOP;
2884
2885 IF l_ccid_exists = FALSE THEN
2886
2887 FOR c_Seq_Rec IN c_Seq LOOP
2888 l_distribution_id := c_Seq_Rec.DistID;
2889 END LOOP;
2890
2891 Insert_Row
2892 (p_api_version => 1.0,
2893 p_return_status => l_return_status,
2894 p_msg_count => x_msg_count,
2895 p_msg_data => x_msg_data,
2896 p_rowid => l_rowid,
2897 p_distribution_id => l_distribution_id,
2898 p_position_id => p_position_id,
2899 p_data_extract_id => p_data_extract_id,
2900 p_worksheet_id => p_worksheet_id,
2901 p_effective_start_date => p_effective_start_date,
2902 p_effective_end_date => p_effective_end_date,
2903 p_chart_of_accounts_id => p_chart_of_accounts_id,
2904 p_code_combination_id => p_code_combination_id,
2905 p_distribution_percent => l_distribution_percent,
2906 p_global_default_flag => p_global_default_flag,
2907 p_distribution_default_rule_id => p_distribution_default_rule_id,
2908 p_project_id => p_project_id,
2909 p_task_id => p_task_id,
2910 p_award_id => p_award_id,
2911 p_expenditure_type => p_expenditure_type,
2912 p_expenditure_organization_id => p_expenditure_organization_id,
2913 p_description => p_description,
2914 p_mode => p_mode);
2915 END IF;
2916 END IF;
2917 END IF;
2918
2919 -- Standard check of p_commit
2920
2921 IF FND_API.to_Boolean (p_commit) THEN
2922 COMMIT WORK;
2923 END IF;
2924
2925
2926 -- Initialize API return status to success
2927
2928 x_return_status := FND_API.G_RET_STS_SUCCESS;
2929
2930
2931 -- Standard call to get message count and if count is 1, get message info
2932
2933 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2934 p_data => x_msg_data);
2935
2936 EXCEPTION
2937
2938 WHEN FND_API.G_EXC_ERROR THEN
2939 ROLLBACK TO Apply_Position_Pay_Distr;
2940 x_return_status := FND_API.G_RET_STS_ERROR;
2941
2942 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2943 p_data => x_msg_data);
2944
2945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2946 ROLLBACK TO Apply_Position_Pay_Distr;
2947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2948
2949 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2950 p_data => x_msg_data);
2951
2952 WHEN OTHERS THEN
2953 ROLLBACK TO Apply_Position_Pay_Distr;
2954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2955
2956 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2957
2958 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2959 l_api_name);
2960 END IF;
2961 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2962 p_data => x_msg_data);
2963
2964 END Apply_Position_Pay_Distr;
2965
2966 /* Bug 1308558 End */
2967
2968 /* ----------------------------------------------------------------------- */
2969
2970
2971 END PSB_POSITION_PAY_DISTR_PVT ;