[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PAYEE_ASSIGNS_PVT
Source
1 PACKAGE BODY cn_srp_payee_assigns_pvt AS
2 /* $Header: cnvpspab.pls 120.7 2006/02/13 17:21:47 mblum noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PAYEE_ASSIGNS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvpspab.pls';
6 g_payee_role CONSTANT NUMBER := 54;
7
8 PROCEDURE delete_trigger
9 (old_salesrep_id NUMBER,
10 old_srp_quota_assign_id NUMBER,
11 old_start_date DATE,
12 old_end_date DATE,
13 old_org_id NUMBER) IS
14
15 x_salesrep_name cn_salesreps.name%TYPE;
16 BEGIN
17 IF fnd_profile.value('CN_MARK_EVENTS') = 'Y' THEN
18 SELECT name
19 INTO x_salesrep_name
20 FROM cn_salesreps
21 WHERE salesrep_id = old_salesrep_id
22 AND org_id = old_org_id;
23
24 cn_mark_events_pkg.mark_event_srp_payee_assign
25 ('CHANGE_SRP_QUOTA_POP',
26 x_salesrep_name,
27 old_srp_quota_assign_id,
28 null,
29 null,
30 old_start_date,
31 null,
32 old_end_date,
33 old_org_id);
34 END IF;
35 END delete_trigger;
36
37 PROCEDURE insert_trigger
38 (new_salesrep_id NUMBER,
39 new_srp_quota_assign_id NUMBER,
40 new_start_date DATE,
41 new_end_date DATE,
42 new_org_id NUMBER) IS
43
44 x_salesrep_name cn_salesreps.name%TYPE;
45 BEGIN
46 IF fnd_profile.value('CN_MARK_EVENTS') = 'Y' THEN
47 SELECT name
48 INTO x_salesrep_name
49 FROM cn_salesreps
50 WHERE salesrep_id = new_salesrep_id
51 AND org_id = new_org_id;
52
53 cn_mark_events_pkg.mark_event_srp_payee_assign
54 ('CHANGE_SRP_QUOTA_POP',
55 x_salesrep_name,
56 new_srp_quota_assign_id,
57 null,
58 null,
59 new_start_date,
60 null,
61 new_end_date,
62 new_org_id);
63 END IF;
64 END insert_trigger;
65
66 PROCEDURE update_trigger
67 (old_salesrep_id NUMBER,
68 old_payee_id NUMBER,
69 old_start_date DATE,
70 old_end_date DATE,
71 new_srp_quota_assign_id NUMBER,
72 new_salesrep_id NUMBER,
73 new_payee_id NUMBER,
74 new_start_date DATE,
75 new_end_date DATE,
76 new_org_id NUMBER) IS
77
78 x_salesrep_name cn_salesreps.name%TYPE;
79 BEGIN
80 IF fnd_profile.value('CN_MARK_EVENTS') = 'Y' THEN
81 SELECT name
82 INTO x_salesrep_name
83 FROM cn_salesreps
84 WHERE salesrep_id = new_salesrep_id
85 AND org_id = new_org_id;
86
87 IF (new_payee_id <> old_payee_id) THEN
88 cn_mark_events_pkg.mark_event_srp_payee_assign
89 ('CHANGE_SRP_QUOTA_POP',
90 x_salesrep_name,
91 new_srp_quota_assign_id,
92 null,
93 new_start_date,
94 old_start_date,
95 new_end_date,
96 old_end_date,
97 new_org_id);
98 END IF;
99
100 -- clku fix for bug 3234665
101
102 IF (new_start_date <> old_start_date) OR
103 Nvl(old_end_date,fnd_api.g_miss_date) <>
104 Nvl(new_end_date,fnd_api.g_miss_date)
105 THEN
106 cn_mark_events_pkg.mark_event_srp_payee_assign
107 ('CHANGE_SRP_QUOTA_PAYEE_DATE',
108 x_salesrep_name,
109 new_srp_quota_assign_id,
110 null,
111 new_start_date,
112 old_start_date,
113 new_end_date,
114 old_end_date,
115 new_org_id);
116 END IF;
117 END IF;
118 END update_trigger;
119
120
121 -- ---------------------------------------------------------------------------+
122 -- Procedure: Validate_Payee_Dates
123 -- Desc : Validating payee dates with plan elements date and with other
124 -- payees of the planelement
125 -- ---------------------------------------------------------------------------+
126 PROCEDURE Validate_Payee_Dates
127 (p_srp_payee_assign_id IN NUMBER, -- null means we're creating
128 p_srp_quota_assign_id IN NUMBER,
129 p_salesrep_id IN NUMBER,
130 p_org_id IN NUMBER,
131 p_start_date IN DATE,
132 p_end_date IN DATE,
133 p_quota_id IN NUMBER,
134 p_payee_id IN NUMBER,
135 x_loading_status IN OUT NOCOPY VARCHAR2
136 ) IS
137
138 l_count NUMBER;
139 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Payee_Dates';
140 l_end_of_time CONSTANT DATE := to_date('12/31/9999','MM/DD/YYYY');
141 BEGIN
142 -- null p_srp_payee_assign_id means we're creating new assignment
143
144 --check whether the payee start date and end date
145 --fall between the start and end date of plan element
146 SELECT count(1)
147 INTO l_count
148 FROM cn_quotas_all
149 WHERE quota_id = p_quota_id
150 AND p_start_date >= start_date
151 AND Nvl(p_end_date, l_end_of_time) <=
152 Nvl(end_date, l_end_of_time);
153
154 IF l_count = 0 THEN
155 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
156 THEN
157 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAYEE_DATE_INVALID');
158 FND_MSG_PUB.Add;
159 END IF;
160 x_loading_status := 'CN_PAYEE_DATE_INVALID';
161 RAISE FND_API.G_EXC_ERROR ;
162 END IF;
163
164 -- make sure payee assignment falls within a valid plan assignment
165 -- fix for bug 4507995
166 SELECT COUNT(1)
167 INTO l_count
168 FROM cn_srp_quota_assigns sqa, cn_srp_plan_assigns spa
169 WHERE sqa.srp_quota_assign_id = p_srp_quota_assign_id
170 AND sqa.srp_plan_assign_id = spa.srp_plan_assign_id
171 AND p_start_date >= start_date
172 AND Nvl(p_end_date, l_end_of_time) <=
173 Nvl(end_date, l_end_of_time);
174
175 IF l_count = 0 THEN
176 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
177 THEN
178 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAYEE_DATE_INVALID');
179 FND_MSG_PUB.Add;
180 END IF;
181 x_loading_status := 'CN_PAYEE_DATE_INVALID';
182 RAISE FND_API.G_EXC_ERROR ;
183 END IF;
184
185 --check whether the payee's date overlap with the dates of
186 --other payee who is already assigned
187 SELECT count(1)
188 INTO l_count
189 FROM cn_srp_payee_assigns_all
190 WHERE srp_quota_assign_id = p_srp_quota_assign_id
191 AND delete_flag = 'N'
192 AND srp_payee_assign_id <> Nvl(p_srp_payee_assign_id, -1)
193 AND Greatest(start_date, p_start_date) <=
194 Least(Nvl(end_date, l_end_of_time),
195 Nvl(p_end_date, l_end_of_time));
196
197 IF l_count > 0 then
198 --payee dates overlap
199 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
200 THEN
201 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAYEE_DATE_OVERLAP');
202 FND_MSG_PUB.Add;
203 END IF;
204 x_loading_status := 'CN_PAYEE_DATE_OVERLAP';
205 RAISE FND_API.G_EXC_ERROR ;
206 end if;
207
208 END Validate_Payee_Dates;
209
210 -- ---------------------------------------------------------------------------+
211 -- Procedure: Valid_Srp_Payee_Assigns
212 -- Desc : Calling the check required procedure to check the mandatory
213 -- values, get the respective ids for update/insert
214 -- return loading status should be same as passed loading status
215 -- others means failure
216 -- ---------------------------------------------------------------------------+
217 PROCEDURE Valid_Srp_Payee_Assigns
218 (
219 p_srp_payee_assign_id IN NUMBER,
220 p_srp_quota_assign_id IN NUMBER,
221 p_salesrep_id IN NUMBER,
222 p_org_id IN NUMBER,
223 p_payee_id IN NUMBER,
224 p_start_date IN DATE,
225 p_end_date IN DATE,
226 x_loading_status IN OUT NOCOPY VARCHAR2
227 ) IS
228
229 l_api_name CONSTANT VARCHAR2(30) := 'Valid_Srp_Payee_Assigns';
230 l_payee_assign_flag cn_quotas.payee_assign_flag%TYPE;
231 l_count NUMBER;
232 l_daycount NUMBER;
233 l_sd DATE;
234 l_ed DATE;
235 l_end_of_time CONSTANT DATE := to_date('12/31/9999','MM/DD/YYYY');
236 l_end_date DATE := nvl(p_end_date, l_end_of_time);
237 l_srp_name cn_salesreps.name%TYPE;
238 l_payee_name cn_salesreps.name%TYPE;
239 l_emp_num cn_salesreps.employee_number%TYPE;
240 l_pe_name cn_quotas.name%TYPE;
241 l_quota_id NUMBER;
242
243 CURSOR get_pgs is
244 SELECT start_date, nvl(end_date,l_end_of_time) end_date
245 FROM cn_srp_pay_groups_all
246 WHERE salesrep_id = p_payee_id
247 AND org_id = p_org_id;
248
249 CURSOR get_roles is
250 SELECT start_date, nvl(end_date,l_end_of_time) end_date
251 FROM cn_srp_roles
252 WHERE salesrep_id = p_payee_id
253 AND org_id = p_org_id
254 AND role_id = g_payee_role;
255
256 BEGIN
257 -- API body
258
259 --+
260 -- Check active Payee
261 --+
262 -- get name and number
263 SELECT name, employee_number
264 INTO l_payee_name, l_emp_num
265 FROM cn_salesreps
266 WHERE salesrep_id = p_payee_id
267 AND org_id = p_org_id;
268
269 SELECT COUNT(1)
270 INTO l_count
271 FROM cn_salesreps
272 WHERE salesrep_id = p_payee_id
273 AND org_id = p_org_id
274 AND start_date_active <= p_start_date
275 AND ((end_date_active IS NULL AND p_end_date IS NULL ) OR
276 (end_date_active IS NULL AND p_end_date IS NOT NULL ) OR
277 (end_date_active >= p_end_date));
278
279 IF l_count = 0 THEN
280 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
281 THEN
282 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAYEE_NOT_ACTIVE' );
283 FND_MESSAGE.SET_TOKEN('PAYEE_NAME', l_payee_name);
284 FND_MESSAGE.SET_TOKEN('PAYEE_NUMBER',l_emp_num);
285 FND_MSG_PUB.Add;
286 END IF;
287 x_loading_status := 'CN_PAYEE_NOT_ACTIVE';
288 RAISE FND_API.G_EXC_ERROR ;
289 END IF;
290
291 --+
292 -- Check salesrep and Payee are different, if not error
293 --+
294 IF p_payee_id = p_salesrep_id THEN
295
296 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
297 THEN
298 FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PAYEE_CANNOT_BE_SAME' );
299 FND_MSG_PUB.Add;
300 END IF;
301 x_loading_status := 'CN_SRP_PAYEE_CANNOT_BE_SAME';
302 RAISE FND_API.G_EXC_ERROR ;
303 END IF;
304
305 --+
306 -- Validate End Date must be greater than Start Date
307 --+
308 IF p_end_date IS NOT NULL AND p_end_date < p_start_date THEN
309 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
310 THEN
311 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATE_RANGE');
312 FND_MSG_PUB.Add;
313 END IF;
314 x_loading_status := 'CN_INVALID_DATE_RANGE';
315 RAISE FND_API.G_EXC_ERROR ;
316 END IF;
317
318 -- Check wheather the payee can be assigned to this plan Element
319 SELECT payee_assign_flag, q.name, q.quota_id
320 INTO l_payee_assign_flag, l_pe_name, l_quota_id
321 FROM cn_quotas_all q, cn_srp_quota_assigns_all sqa
322 WHERE sqa.srp_quota_assign_id = p_srp_quota_assign_id
323 AND q.quota_id = sqa.quota_id;
324
325 IF Nvl(l_payee_assign_flag,'N') <> 'Y' THEN
326 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
327 THEN
328 -- get salesrep name
329 SELECT name
330 INTO l_srp_name
331 FROM cn_salesreps
332 WHERE salesrep_id = p_salesrep_id
333 AND org_id = p_org_id;
334 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_HAVE_PAYEE');
335 FND_MESSAGE.SET_TOKEN('PLAN_NAME', l_pe_name);
336 FND_MESSAGE.SET_TOKEN('SALESREP_NAME', l_srp_name);
337 FND_MESSAGE.SET_TOKEN('PAYEE_NAME', l_payee_name);
338 FND_MSG_PUB.Add;
339 END IF;
340 x_loading_status := 'CN_CANNOT_HAVE_PAYEE';
341 RAISE FND_API.G_EXC_ERROR ;
342 END IF;
343
344 -- ** bug 3143462
345 -- check to make sure pay group is assigned over whole interval
346 l_daycount := 0;
347 for pg in get_pgs loop
348 l_sd := greatest(p_start_date, pg.start_date);
349 l_ed := least(l_end_date, pg.end_date);
350 if l_ed >= l_sd then
351 l_daycount := l_daycount + (l_ed - l_sd) + 1;
352 end if;
353 end loop;
354
355 if l_daycount <> (l_end_date - p_start_date + 1) then
356 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
357 THEN
358 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAYEE_PG_NOT_FOUND');
359 FND_MSG_PUB.Add;
360 END IF;
361 x_loading_status := 'CN_PAYEE_PG_NOT_FOUND';
362 RAISE FND_API.G_EXC_ERROR ;
363 end if;
364
365
366 -- make sure payee isn't assigned longer than the payee has payee role
367 l_daycount := 0;
368 for role in get_roles loop
369 l_sd := greatest(p_start_date, role.start_date);
370 l_ed := least(l_end_date, role.end_date);
371 if l_ed >= l_sd then
372 l_daycount := l_daycount + (l_ed - l_sd) + 1;
373 end if;
374 end loop;
375
376 if l_daycount <> (l_end_date - p_start_date + 1) then
377 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
378 THEN
379 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAYEE_ROLE_NOT_FOUND');
380 FND_MSG_PUB.Add;
381 END IF;
382 x_loading_status := 'CN_PAYEE_ROLE_NOT_FOUND';
383 RAISE FND_API.G_EXC_ERROR ;
384 end if;
385
386 --+
387 -- Start of Payee Assigns Check
388 -- CHK validity of dates assigned to payee
389 --+
390 Validate_Payee_Dates(p_srp_payee_assign_id => p_srp_payee_assign_id,
391 p_srp_quota_assign_id => p_srp_quota_assign_id,
392 p_salesrep_id => p_salesrep_id,
393 p_org_id => p_org_id,
394 p_start_date => p_start_date,
395 p_end_date => p_end_date,
396 p_payee_id => p_payee_id,
397 p_quota_id => l_quota_id,
398 x_loading_status => x_loading_status
399 );
400
401 --+
402 -- End of API body.
403 --+
404 END Valid_Srp_Payee_Assigns;
405
406 -- --------------------------------------------------------------------------+
407 -- PROCEDURE: CREATE_UPD_NOTE
408 -- --------------------------------------------------------------------------+
409 PROCEDURE get_note
410 (p_field IN VARCHAR2,
411 p_old_value IN VARCHAR2,
412 p_new_value IN VARCHAR2,
413 x_msg IN OUT nocopy VARCHAR2) IS
414
415 l_note_msg VARCHAR2(240);
416 BEGIN
417 fnd_message.set_name('CN', 'CN_PAYEE_UPD_NOTE');
418 fnd_message.set_token('FIELD', cn_api.get_lkup_meaning(p_field, 'CN_NOTE_FIELDS'));
419 fnd_message.set_token('OLD', p_old_value);
420 fnd_message.set_token('NEW', p_new_value);
421 l_note_msg := fnd_message.get;
422
423 IF x_msg IS NOT NULL THEN
424 x_msg := x_msg || fnd_global.local_chr(10);
425 END IF;
426 x_msg := x_msg || l_note_msg;
427
428 END get_note;
429
430 PROCEDURE raise_note
431 (p_srp_payee_assign_id IN NUMBER,
432 p_msg IN VARCHAR2) IS
433
434 x_note_id NUMBER;
435 x_msg_count NUMBER;
436 x_msg_data VARCHAR2(240);
437 x_return_status VARCHAR2(1);
438
439 BEGIN
440 jtf_notes_pub.create_note
441 ( p_api_version => 1.0,
442 x_return_status => x_return_status,
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data,
445 p_source_object_id => p_srp_payee_assign_id,
446 p_source_object_code => 'CN_SRP_PAYEE_ASSIGNS',
447 p_notes => p_msg,
448 p_notes_detail => p_msg,
449 p_note_type => 'CN_SYSGEN', -- for system generated
450 x_jtf_note_id => x_note_id -- returned
451 );
452 END raise_note;
453
454 -- --------------------------------------------------------------------------+
455 -- PROCEDURE: CREATE_SRP_PAYEE_ASSIGNS
456 -- --------------------------------------------------------------------------+
457 PROCEDURE Create_Srp_Payee_Assigns
458 ( p_api_version IN NUMBER,
459 p_init_msg_list IN VARCHAR2,
460 p_commit IN VARCHAR2,
461 p_validation_level IN NUMBER,
462 x_return_status OUT NOCOPY VARCHAR2,
463 x_msg_count OUT NOCOPY NUMBER,
464 x_msg_data OUT NOCOPY VARCHAR2,
465 p_srp_quota_assign_id IN NUMBER,
466 p_payee_id IN NUMBER,
467 p_start_date IN DATE,
468 p_end_date IN DATE,
469 x_srp_payee_assign_id OUT NOCOPY NUMBER,
470 x_object_version_number OUT NOCOPY NUMBER,
471 x_loading_status OUT NOCOPY VARCHAR2
472 ) IS
473
474 l_api_name CONSTANT VARCHAR2(30)
475 := 'Create_Srp_Payee_Assgins';
476 l_api_version CONSTANT NUMBER := 1.0;
477
478 l_quota_id NUMBER;
479 l_comp_plan_id NUMBER;
480 l_salesrep_id NUMBER;
481 l_org_id NUMBER;
482 l_count NUMBER;
483 l_payee_name cn_salesreps.name%TYPE;
484 l_note_msg VARCHAR2(240);
485 l_note_id NUMBER;
486
487 BEGIN
488 --+
489 -- Standard Start of API savepoint
490 --+
491 SAVEPOINT Create_Srp_Payee_Assigns;
492 --+
493 -- Standard call to check for call compatibility.
494 --+
495 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
496 p_api_version ,
497 l_api_name ,
498 G_PKG_NAME )
499 THEN
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501 END IF;
502 --+
503 -- Initialize message list if p_init_msg_list is set to TRUE.
504 IF FND_API.to_Boolean( p_init_msg_list ) THEN
505 FND_MSG_PUB.initialize;
506 END IF;
507 --+
508 -- Initialize API return status to success
509 --+
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511 x_loading_status := 'CN_INSERTED';
512
513 --+
514 -- Start API body
515 --+
516
517 -- get properties from given srp_quota_assign_id
518 SELECT spa.salesrep_id, sqa.org_id, sqa.quota_id, spa.comp_plan_id
519 INTO l_salesrep_id, l_org_id, l_quota_id, l_comp_plan_id
520 FROM cn_srp_quota_assigns_all sqa, cn_srp_plan_assigns_all spa
521 WHERE srp_quota_assign_id = p_srp_quota_assign_id
522 AND sqa.srp_plan_assign_id = spa.srp_plan_assign_id;
523
524 Valid_Srp_Payee_Assigns
525 (p_srp_payee_assign_id => NULL,
526 p_srp_quota_assign_id => p_srp_quota_assign_id,
527 p_salesrep_id => l_salesrep_id,
528 p_org_id => l_org_id,
529 p_payee_id => p_payee_id,
530 p_start_date => p_start_date,
531 p_end_date => p_end_date,
532 x_loading_status => x_loading_status
533 );
534
535 --+
536 -- Call the Table Handler
537 --+
538 cn_srp_payee_assigns_pkg.insert_record
539 ( x_srp_payee_assign_id => x_srp_payee_assign_id
540 ,p_srp_quota_assign_id => p_srp_quota_assign_id
541 ,p_org_id => l_org_id
542 ,p_payee_id => p_payee_id
543 ,p_quota_id => l_quota_id
544 ,p_salesrep_id => l_salesrep_id
545 ,p_start_date => p_start_date
546 ,p_end_date => p_end_date
547 ,p_last_update_date => sysdate
548 ,p_last_updated_by => fnd_global.user_id
549 ,p_creation_date => sysdate
550 ,p_created_by => fnd_global.user_id
551 ,p_last_update_login => fnd_global.login_id);
552
553 insert_trigger
554 (new_salesrep_id => l_salesrep_id,
555 new_srp_quota_assign_id => p_srp_quota_assign_id,
556 new_start_date => p_start_date,
557 new_end_date => p_end_date,
558 new_org_id => l_org_id);
559
560 cn_srp_periods_pvt.create_srp_periods_per_quota
561 (p_api_version => 1.0,
562 x_return_status => x_return_status,
563 p_salesrep_id => p_payee_id,
564 p_role_id => g_payee_role,
565 p_quota_id => l_quota_id,
566 p_comp_plan_id => l_comp_plan_id,
567 p_start_date => p_start_date,
568 p_end_date => p_end_date,
569 x_msg_count => x_msg_count,
570 x_msg_data => x_msg_data,
571 x_loading_status => x_loading_status
572 );
573
574 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
575 RAISE FND_API.G_EXC_ERROR;
576 end if;
577
578 -- add note for srp_quota_assigns
579 SELECT name INTO l_payee_name
580 FROM cn_salesreps
581 WHERE salesrep_id = p_payee_id
582 AND org_id = l_org_id;
583
584 fnd_message.set_name('CN', 'CN_PAYEE_CRE_NOTE');
585 fnd_message.set_token('PAYEE', l_payee_name);
586 fnd_message.set_token('START_DATE', p_start_date);
587 fnd_message.set_token('END_DATE', p_end_date);
588 l_note_msg := fnd_message.get;
589
590 jtf_notes_pub.create_note
591 ( p_api_version => 1.0,
592 x_return_status => x_return_status,
593 x_msg_count => x_msg_count,
594 x_msg_data => x_msg_data,
595 p_source_object_id => p_srp_quota_assign_id,
596 p_source_object_code => 'CN_SRP_QUOTA_ASSIGNS',
597 p_notes => l_note_msg,
598 p_notes_detail => l_note_msg,
599 p_note_type => 'CN_SYSGEN', -- for system generated
600 x_jtf_note_id => l_note_id -- returned
601 );
602
603 -- get new version number
604 SELECT object_version_number
605 INTO x_object_version_number
606 FROM cn_srp_payee_assigns_all
607 WHERE srp_payee_assign_id = x_srp_payee_assign_id;
608
609 --+
610 -- Issue the Commit and recreate the Save Point.
611 --+
612 IF FND_API.To_Boolean( p_commit ) THEN
613 COMMIT WORK;
614 END IF;
615 --+
616 -- Standard call to get message count and if count is 1, get message info.
617 --+
618 FND_MSG_PUB.Count_And_Get
619 (
620 p_count => x_msg_count ,
621 p_data => x_msg_data ,
622 p_encoded => FND_API.G_FALSE
623 );
624 EXCEPTION
625
626 WHEN FND_API.G_EXC_ERROR THEN
627 ROLLBACK TO create_srp_payee_assigns;
628 x_return_status := FND_API.G_RET_STS_ERROR ;
629 FND_MSG_PUB.Count_And_Get
630 (
631 p_count => x_msg_count ,
632 p_data => x_msg_data ,
633 p_encoded => FND_API.G_FALSE
634 );
635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
636 ROLLBACK TO create_srp_payee_assigns;
637 x_loading_status := 'UNEXPECTED_ERR';
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
639 FND_MSG_PUB.Count_And_Get
640 (
641 p_count => x_msg_count ,
642 p_data => x_msg_data ,
643 p_encoded => FND_API.G_FALSE
644 );
645 WHEN OTHERS THEN
646 ROLLBACK TO Create_srp_payee_assigns;
647 x_loading_status := 'UNEXPECTED_ERR';
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
649 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
650 THEN
651 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
652 END IF;
653 FND_MSG_PUB.Count_And_Get
654 (
655 p_count => x_msg_count ,
656 p_data => x_msg_data ,
657 p_encoded => FND_API.G_FALSE
658 );
659 END create_srp_payee_assigns;
660
661
662 -- --------------------------------------------------------------------------+
663 -- PROCEDURE: UPDATE_SRP_PAYEE_ASSIGNS
664 -- Update is allowed in only start date, end date
665 -- --------------------------------------------------------------------------+
666 PROCEDURE Update_Srp_Payee_Assigns
667 (
668 p_api_version IN NUMBER,
669 p_init_msg_list IN VARCHAR2,
670 p_commit IN VARCHAR2,
671 p_validation_level IN NUMBER,
672 x_return_status OUT NOCOPY VARCHAR2,
673 x_msg_count OUT NOCOPY NUMBER,
674 x_msg_data OUT NOCOPY VARCHAR2,
675 p_srp_payee_assign_id IN NUMBER,
676 p_payee_id IN NUMBER,
677 p_start_date IN DATE,
678 p_end_date IN DATE,
679 p_object_version_number IN OUT NOCOPY NUMBER,
680 x_loading_status OUT NOCOPY VARCHAR2
681 ) IS
682
683 l_api_name CONSTANT VARCHAR2(30)
684 := 'Update_Srp_Payee_Assigns';
685 l_api_version CONSTANT NUMBER := 1.0;
686 l_comp_plan_id NUMBER;
687 l_end_of_time date := to_date('12/31/9999','MM/DD/YYYY');
688 l_payee_name cn_salesreps.name%TYPE;
689 l_old_payee_name cn_salesreps.name%TYPE;
690
691 CURSOR get_old_payee_rec(l_srp_payee_asgn_id number) IS
692 SELECT srp_quota_assign_id, payee_id, start_date, end_date,
693 quota_id, salesrep_id, org_id, object_version_number
694 FROM cn_srp_payee_assigns_all
695 WHERE srp_payee_assign_id = l_srp_payee_asgn_id;
696
697 l_old_rec get_old_payee_rec%ROWTYPE;
698
699 cursor get_worksheets(l_srp_payee_assign_id number) IS
700 SELECT ps.start_date, ps.end_date
701 FROM cn_payment_worksheets_all w,
702 cn_srp_payee_assigns_all spa,
703 cn_payruns_all p,
704 cn_period_statuses_all ps
705 WHERE (w.salesrep_id = spa.payee_id or
706 w.salesrep_id = spa.salesrep_id)
707 AND w.quota_id is NULL
708 AND w.org_id = spa.org_id
709 AND p.payrun_id = w.payrun_id
710 AND p.org_id = w.org_id
711 AND p.pay_period_id = ps.period_id
712 AND p.org_id = ps.org_id
713 AND spa.srp_payee_assign_id = l_srp_payee_assign_id
714 AND spa.org_id = l_old_rec.org_id;
715
716 l_date_range_action_tbl cn_api.date_range_action_tbl_type;
717 l_count number;
718
719 l_key VARCHAR2(80);
720 l_list wf_parameter_list_t;
721 l_event_name VARCHAR2(80);
722 l_notemsg VARCHAR2(2000);
723
724 BEGIN
725 -- Standard Start of API savepoint
726 SAVEPOINT update_srp_payee_assigns;
727
728 -- Standard call to check for call compatibility.
729 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
730 p_api_version ,
731 l_api_name ,
732 G_PKG_NAME )
733 THEN
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735 END IF;
736
737 -- Initialize message list if p_init_msg_list is set to TRUE.
738 IF FND_API.to_Boolean( p_init_msg_list ) THEN
739 FND_MSG_PUB.initialize;
740 END IF;
741
742 -- Initialize API return status to success
743
744 x_return_status := FND_API.G_RET_STS_SUCCESS;
745 x_loading_status := 'CN_UPDATED';
746
747 -- API body
748 OPEN get_old_payee_rec(p_srp_payee_assign_id);
749 FETCH get_old_payee_rec INTO l_old_rec;
750 CLOSE get_old_payee_rec;
751
752 IF l_old_rec.object_version_number <> p_object_version_number THEN
753 --
754 --Raise an error if the object_version numbers don't match
755 --
756 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
757 THEN
758 fnd_message.set_name('CN', 'CN_RECORD_UPDATED');
759 fnd_msg_pub.add;
760 END IF;
761 x_loading_status := 'CN_RECORD_UPDATED';
762 RAISE FND_API.G_EXC_ERROR;
763 END IF;
764
765 -- get properties from given srp_quota_assign_id
766 SELECT spa.comp_plan_id
767 INTO l_comp_plan_id
768 FROM cn_srp_quota_assigns_all sqa, cn_srp_plan_assigns_all spa
769 WHERE sqa.srp_quota_assign_id = l_old_rec.srp_quota_assign_id
770 AND sqa.srp_plan_assign_id = spa.srp_plan_assign_id;
771
772 Valid_Srp_Payee_Assigns
773 (p_srp_payee_assign_id => p_srp_payee_assign_id,
774 p_srp_quota_assign_id => l_old_rec.srp_quota_assign_id,
775 p_salesrep_id => l_old_rec.salesrep_id,
776 p_org_id => l_old_rec.org_id,
777 p_payee_id => p_payee_id,
778 p_start_date => p_start_date,
779 p_end_date => p_end_date,
780 x_loading_status => x_loading_status
781 );
782
783 x_loading_status := 'CN_UPDATED';
784
785 -- see if date range is shrinking in any way. if so, delete and
786 -- recreate the payee
787 IF (p_start_date > l_old_rec.start_date OR
788 Nvl(p_end_date, l_end_of_time) <
789 Nvl(l_old_rec.end_date, l_end_of_time))
790 THEN
791 -- make sure no worksheets in any part of the shrunk range
792 -- this is for bug fix 3390199
793 cn_api.get_date_range_diff_action
794 (start_date_new => p_start_date
795 ,end_date_new => Nvl(p_end_date, l_end_of_time)
796 ,start_date_old => l_old_rec.start_date
797 ,end_date_old => Nvl(l_old_rec.end_date, l_end_of_time)
798 ,x_date_range_action_tbl => l_date_range_action_tbl );
799 FOR d IN 1..l_date_range_action_tbl.COUNT LOOP
800 if l_date_range_action_tbl(d).action_flag = 'D' THEN
801 for w in get_worksheets(p_srp_payee_assign_id) loop
802 IF CN_API.date_range_overlap
803 (l_date_range_action_tbl(d).start_date,
804 l_date_range_action_tbl(d).end_date,
805 w.start_date,
806 w.end_date) = true then
807 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
808 THEN
809 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAYEE_HAS_WKSHT');
810 FND_MSG_PUB.Add;
811 END IF;
812 x_loading_status := 'CN_PAYEE_HAS_WKSHT';
813 RAISE FND_API.G_EXC_ERROR ;
814 END IF; -- ck date range overlap
815 end loop; -- get_worksheets
816 end if; -- if action = D
817 end loop; -- date range loop
818 END IF;
819
820 -- update
821 cn_srp_payee_assigns_pkg.update_record
822 (p_srp_payee_assign_id => p_srp_payee_assign_id,
823 p_payee_id => p_payee_id,
824 p_start_date => p_start_date,
825 p_end_date => p_end_date,
826 p_last_update_date => Sysdate,
827 p_last_updated_by => fnd_global.user_id,
828 p_last_update_login => fnd_global.login_id);
829
830 -- call triggers
831 update_trigger
832 (old_salesrep_id => l_old_rec.salesrep_id,
833 old_payee_id => l_old_rec.payee_id,
834 old_start_date => l_old_rec.start_date,
835 old_end_date => l_old_rec.end_date,
836 new_srp_quota_assign_id => l_old_rec.srp_quota_assign_id,
837 new_salesrep_id => l_old_rec.salesrep_id,
838 new_payee_id => p_payee_id,
839 new_start_date => p_start_date,
840 new_end_date => p_end_date,
841 new_org_id => l_old_rec.org_id);
842
843 -- raise business event
844 l_event_name := 'oracle.apps.cn.resource.PlanAssign.UpdatePayee';
845 l_key := l_event_name || '-' || p_srp_payee_assign_id || '-' ||
846 p_object_version_number;
847
848 wf_event.AddParameterToList('SRP_PAYEE_ASSIGN_ID',
849 p_srp_payee_assign_id,l_list);
850 wf_event.AddParameterToList('PAYEE_ID',p_payee_id,l_list);
851 wf_event.AddParameterToList('START_DATE',p_start_date,l_list);
852 wf_event.AddParameterToList('END_DATE',p_end_date,l_list);
853
854 -- Raise Event
855 wf_event.raise
856 (p_event_name => l_event_name,
857 p_event_key => l_key,
858 p_parameters => l_list);
859
860 l_list.DELETE;
861
862 -- create srp periods as necessary
863 cn_srp_periods_pvt.create_srp_periods_per_quota
864 (
865 p_api_version => 1.0,
866 x_return_status => x_return_status,
867 p_salesrep_id => p_payee_id,
868 p_role_id => g_payee_role,
869 p_quota_id => l_old_rec.quota_id,
870 p_comp_plan_id => l_comp_plan_id,
871 p_start_date => p_start_date,
872 p_end_date => p_end_date,
873 x_msg_count => x_msg_count,
874 x_msg_data => x_msg_data,
875 x_loading_status => x_loading_status
876 );
877 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
878 RAISE FND_API.G_EXC_ERROR;
879 end if;
880
881 -- add note for srp_payee_assigns
882 l_notemsg := NULL;
883 IF (l_old_rec.payee_id <> p_payee_id) THEN
884 SELECT name INTO l_old_payee_name
885 FROM cn_salesreps
886 WHERE salesrep_id = l_old_rec.payee_id
887 AND org_id = l_old_rec.org_id;
888 SELECT name INTO l_payee_name
889 FROM cn_salesreps
890 WHERE salesrep_id = p_payee_id
891 AND org_id = l_old_rec.org_id;
892
893 get_note('PAYEE', l_old_payee_name, l_payee_name, l_notemsg);
894 END IF;
895 IF (l_old_rec.start_date <> p_start_date) THEN
896 get_note('START_DATE', l_old_rec.start_date, p_start_date, l_notemsg);
897 END IF;
898 IF (Nvl(l_old_rec.end_date,fnd_api.g_miss_date) <>
899 Nvl(p_end_date, fnd_api.g_miss_date)) THEN
900 get_note('END_DATE', l_old_rec.end_date, p_end_date, l_notemsg);
901 END IF;
902
903 IF (l_notemsg IS NOT NULL) THEN
904 raise_note(p_srp_payee_assign_id, l_notemsg);
905 END IF;
906
907 -- get new version number
908 SELECT object_version_number
909 INTO p_object_version_number
910 FROM cn_srp_payee_assigns_all
911 WHERE srp_payee_assign_id = p_srp_payee_assign_id;
912
913 --+
914 -- Issue the Commit and recreate the Save Point.
915 --+
916 IF FND_API.To_Boolean( p_commit ) THEN
917 COMMIT WORK;
918 END IF;
919 --+
920 -- Standard call to get message count and if count is 1, get message info.
921 --+
922 FND_MSG_PUB.Count_And_Get
923 (
924 p_count => x_msg_count ,
925 p_data => x_msg_data ,
926 p_encoded => FND_API.G_FALSE
927 );
928 EXCEPTION
929 WHEN FND_API.G_EXC_ERROR THEN
930 ROLLBACK TO update_srp_payee_assigns;
931 x_return_status := FND_API.G_RET_STS_ERROR ;
932 FND_MSG_PUB.Count_And_Get
933 (
934 p_count => x_msg_count ,
935 p_data => x_msg_data ,
936 p_encoded => FND_API.G_FALSE
937 );
938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
939 ROLLBACK TO update_srp_payee_assigns;
940 x_loading_status := 'UNEXPECTED_ERR';
941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
942 FND_MSG_PUB.Count_And_Get
943 (
944 p_count => x_msg_count ,
945 p_data => x_msg_data ,
946 p_encoded => FND_API.G_FALSE
947 );
948 WHEN OTHERS THEN
949 ROLLBACK TO Update_srp_payee_assigns;
950 x_loading_status := 'UNEXPECTED_ERR';
951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
952 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
953 THEN
954 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
955 END IF;
956 FND_MSG_PUB.Count_And_Get
957 (
958 p_count => x_msg_count ,
959 p_data => x_msg_data ,
960 p_encoded => FND_API.G_FALSE
961 );
962
963 END Update_Srp_Payee_Assigns ;
964
965 -- --------------------------------------------------------------------------+
966 -- Procedure: Valid_Delete_Srp_Payee_Assigns
967 -- Descr: validate Delete srp Payee Assigns
968 -- --------------------------------------------------------------------------+
969 PROCEDURE Valid_Delete_Srp_Payee_Assigns
970 ( p_init_msg_list IN VARCHAR2,
971 x_return_status OUT NOCOPY VARCHAR2,
972 x_msg_count OUT NOCOPY NUMBER,
973 x_msg_data OUT NOCOPY VARCHAR2,
974 p_srp_payee_assign_id IN NUMBER,
975 x_loading_status OUT NOCOPY VARCHAR2) IS
976
977 l_api_name CONSTANT VARCHAR2(30) := 'Valid_Delete_Srp_Payee_Assigns';
978 l_start_date DATE;
979 l_end_date DATE;
980 l_org_id NUMBER;
981
982 CURSOR get_worksheets IS
983 select ps.start_date, ps.end_date
984 from cn_payment_worksheets_all w,
985 cn_srp_payee_assigns_all spa,
986 cn_payruns_all p,
987 cn_period_statuses_all ps
988 where (w.salesrep_id = spa.payee_id or
989 w.salesrep_id = spa.salesrep_id)
990 AND w.org_id = spa.org_id
991 AND w.quota_id is null
992 AND p.payrun_id = w.payrun_id
993 AND p.pay_period_id = ps.period_id
994 AND p.org_id = ps.org_id
995 AND spa.srp_payee_assign_id = p_srp_payee_assign_id
996 AND spa.org_id = l_org_id;
997
998 BEGIN
999 -- Initialize message list if p_init_msg_list is set to TRUE.
1000 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1001 FND_MSG_PUB.initialize;
1002 END IF;
1003
1004 -- Initialize API return status to success
1005 x_return_status := FND_API.G_RET_STS_SUCCESS;
1006 x_loading_status := 'CN_DELETED';
1007
1008 SELECT start_date, end_date, org_id
1009 INTO l_start_date, l_end_date, l_org_id
1010 FROM cn_srp_payee_assigns_all
1011 WHERE srp_payee_assign_id = p_srp_payee_assign_id;
1012
1013 -- check payee has no worksheet for bug 3390199
1014 FOR w IN get_worksheets loop
1015 IF CN_API.date_range_overlap
1016 (l_start_date,
1017 l_end_date,
1018 w.start_date,
1019 w.end_date) = true THEN
1020 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1021 THEN
1022 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAYEE_HAS_WKSHT');
1023 FND_MSG_PUB.Add;
1024 END IF;
1025 x_loading_status := 'CN_PAYEE_HAS_WKSHT';
1026 RAISE FND_API.G_EXC_ERROR ;
1027 END IF;
1028 END LOOP;
1029
1030 EXCEPTION
1031 WHEN FND_API.G_EXC_ERROR THEN
1032 x_return_status := FND_API.G_RET_STS_ERROR ;
1033 FND_MSG_PUB.Count_And_Get
1034 (
1035 p_count => x_msg_count ,
1036 p_data => x_msg_data ,
1037 p_encoded => FND_API.G_FALSE
1038 );
1039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1040 x_loading_status := 'UNEXPECTED_ERR';
1041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1042 FND_MSG_PUB.Count_And_Get
1043 (
1044 p_count => x_msg_count ,
1045 p_data => x_msg_data ,
1046 p_encoded => FND_API.G_FALSE
1047 );
1048 WHEN OTHERS THEN
1049 x_loading_status := 'UNEXPECTED_ERR';
1050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1051 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1052 THEN
1053 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1054 END IF;
1055 FND_MSG_PUB.Count_And_Get
1056 (
1057 p_count => x_msg_count ,
1058 p_data => x_msg_data ,
1059 p_encoded => FND_API.G_FALSE
1060 );
1061
1062 END valid_delete_srp_payee_assigns;
1063
1064
1065 -- --------------------------------------------------------------------------+
1066 -- Procedure: Delete_srp_payee_assigns
1067 -- Descr: Delete srp Payee Assigns
1068 -- --------------------------------------------------------------------------+
1069 PROCEDURE Delete_Srp_Payee_Assigns
1070 ( p_api_version IN NUMBER,
1071 p_init_msg_list IN VARCHAR2,
1072 p_commit IN VARCHAR2,
1073 p_validation_level IN NUMBER,
1074 x_return_status OUT NOCOPY VARCHAR2,
1075 x_msg_count OUT NOCOPY NUMBER,
1076 x_msg_data OUT NOCOPY VARCHAR2,
1077 p_srp_payee_assign_id IN NUMBER,
1078 x_loading_status OUT NOCOPY VARCHAR2
1079 ) IS
1080
1081
1082 l_api_name CONSTANT VARCHAR2(30)
1083 := 'Delete_Srp_Payee_Assigns';
1084 l_api_version CONSTANT NUMBER := 1.0;
1085
1086 l_start_date DATE;
1087 l_end_date DATE;
1088 l_salesrep_id NUMBER;
1089 l_org_id NUMBER;
1090 l_srp_quota_assign_id NUMBER;
1091 l_note_id NUMBER;
1092 l_note_msg VARCHAR2(240);
1093 l_payee_id NUMBER;
1094 l_payee_name cn_salesreps.name%TYPE;
1095
1096 BEGIN
1097 -- Standard Start of API savepoint
1098 SAVEPOINT delete_srp_payee_assigns;
1099
1100 -- Standard call to check for call compatibility.
1101 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1102 p_api_version ,
1103 l_api_name ,
1104 G_PKG_NAME )
1105 THEN
1106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1107 END IF;
1108
1109 -- Initialize message list if p_init_msg_list is set to TRUE.
1110 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1111 FND_MSG_PUB.initialize;
1112 END IF;
1113
1114 -- Initialize API return status to success
1115 x_return_status := FND_API.G_RET_STS_SUCCESS;
1116 x_loading_status := 'CN_DELETED';
1117
1118 --+
1119 -- API body
1120 -- +
1121
1122 -- validate delete
1123 valid_delete_srp_payee_assigns
1124 (p_init_msg_list => p_init_msg_list,
1125 x_return_status => x_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data,
1128 p_srp_payee_assign_id => p_srp_payee_assign_id,
1129 x_loading_status => x_loading_status);
1130
1131 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1132 RAISE fnd_api.g_exc_error;
1133 END IF;
1134
1135 -- get info for trigger
1136 SELECT salesrep_id, srp_quota_assign_id, start_date, end_date, org_id, payee_id
1137 INTO l_salesrep_id, l_srp_quota_assign_id, l_start_date, l_end_date, l_org_id, l_payee_id
1138 FROM cn_srp_payee_assigns_all
1139 WHERE srp_payee_assign_id = p_srp_payee_assign_id;
1140
1141 cn_srp_payee_assigns_pkg.delete_record
1142 (p_srp_payee_assign_id => p_srp_payee_assign_id);
1143
1144 delete_trigger
1145 (old_salesrep_id => l_salesrep_id,
1146 old_srp_quota_assign_id => l_srp_quota_assign_id,
1147 old_start_date => l_start_date,
1148 old_end_date => l_end_date,
1149 old_org_id => l_org_id);
1150
1151 -- add note for srp_quota_assigns
1152 SELECT name INTO l_payee_name
1153 FROM cn_salesreps
1154 WHERE salesrep_id = l_payee_id
1155 AND org_id = l_org_id;
1156
1157 fnd_message.set_name('CN', 'CN_PAYEE_DEL_NOTE');
1158 fnd_message.set_token('PAYEE', l_payee_name);
1159 fnd_message.set_token('START_DATE', l_start_date);
1160 fnd_message.set_token('END_DATE', l_end_date);
1161 l_note_msg := fnd_message.get;
1162
1163 jtf_notes_pub.create_note
1164 ( p_api_version => 1.0,
1165 x_return_status => x_return_status,
1166 x_msg_count => x_msg_count,
1167 x_msg_data => x_msg_data,
1168 p_source_object_id => l_srp_quota_assign_id,
1169 p_source_object_code => 'CN_SRP_QUOTA_ASSIGNS',
1170 p_notes => l_note_msg,
1171 p_notes_detail => l_note_msg,
1172 p_note_type => 'CN_SYSGEN', -- for system generated
1173 x_jtf_note_id => l_note_id -- returned
1174 );
1175
1176
1177 --+
1178 -- Issue the Commit and recreate the Save Point.
1179 --+
1180 IF FND_API.To_Boolean( p_commit ) THEN
1181 COMMIT WORK;
1182 END IF;
1183 --+
1184 -- Standard call to get message count and if count is 1, get message info.
1185 --+
1186 FND_MSG_PUB.Count_And_Get
1187 (
1188 p_count => x_msg_count ,
1189 p_data => x_msg_data ,
1190 p_encoded => FND_API.G_FALSE
1191 );
1192 EXCEPTION
1193 WHEN FND_API.G_EXC_ERROR THEN
1194 ROLLBACK TO Delete_srp_payee_assigns;
1195 x_return_status := FND_API.G_RET_STS_ERROR ;
1196 FND_MSG_PUB.Count_And_Get
1197 (
1198 p_count => x_msg_count ,
1199 p_data => x_msg_data ,
1200 p_encoded => FND_API.G_FALSE
1201 );
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203 ROLLBACK TO Delete_srp_payee_assigns;
1204 x_loading_status := 'UNEXPECTED_ERR';
1205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1206 FND_MSG_PUB.Count_And_Get
1207 (
1208 p_count => x_msg_count ,
1209 p_data => x_msg_data ,
1210 p_encoded => FND_API.G_FALSE
1211 );
1212 WHEN OTHERS THEN
1213 ROLLBACK TO delete_srp_payee_assigns;
1214 x_loading_status := 'UNEXPECTED_ERR';
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1216 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1217 THEN
1218 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1219 END IF;
1220 FND_MSG_PUB.Count_And_Get
1221 (
1222 p_count => x_msg_count ,
1223 p_data => x_msg_data ,
1224 p_encoded => FND_API.G_FALSE
1225 );
1226 END Delete_Srp_Payee_Assigns;
1227
1228 END CN_SRP_PAYEE_ASSIGNS_PVT;