[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PAYGROUP_PUB
Source
1 PACKAGE BODY CN_Srp_PayGroup_PUB as
2 -- $Header: cnpspgpb.pls 120.14 2011/06/24 18:48:46 rnagired ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PayGroup_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpspgpb.pls';
6
7 procedure get_date_range_intersect(a_start_date in date, a_end_date in date,
8 b_start_date in date, b_end_date in date,
9 x_start_date out nocopy date, x_end_date out nocopy date)
10 IS
11 BEGIN
12 if ( a_start_date is null or b_start_date is null) then
13 x_start_date := null;
14 x_end_date := null;
15 elsif (a_end_date is not null and a_end_date < b_start_date)
16 or ( b_end_date is not null and a_start_date > b_end_date) then
17 x_start_date := null;
18 x_end_date := null;
19 else
20 x_start_date := greatest(a_start_date, b_start_date);
21 if a_end_date is null then
22 x_end_date := b_end_date;
23 elsif b_end_date is null then
24 x_end_date := a_end_date;
25 else
26 x_end_date := least(a_end_date, b_end_date);
27 end if;
28 end if;
29 END;
30
31 procedure get_masgn_date_intersect(
32 p_role_pay_group_id IN NUMBER,
33 p_srp_role_id IN NUMBER,
34 x_start_date OUT NOCOPY DATE,
35 x_end_date OUT NOCOPY DATE) IS
36
37 l_start_date cn_srp_pay_groups.start_date%TYPE;
38 l_end_date cn_srp_pay_groups.start_date%TYPE;
39
40
41 l_res_start_date cn_srp_pay_groups.start_date%TYPE;
42 l_res_end_date cn_srp_pay_groups.start_date%TYPE;
43
44 l_role_pg_start_date cn_srp_pay_groups.start_date%TYPE;
45 l_role_pg_end_date cn_srp_pay_groups.start_date%TYPE;
46
47 l_srp_role_start_date cn_srp_pay_groups.start_date%TYPE;
48 l_srp_role_end_date cn_srp_pay_groups.start_date%TYPE;
49
50 l_pg_start_date cn_srp_pay_groups.start_date%TYPE;
51 l_pg_end_date cn_srp_pay_groups.start_date%TYPE;
52
53 l_org_id NUMBER;
54 l_salesrep_id NUMBER;
55 l_pay_group_id NUMBER;
56 BEGIN
57 -- get start_date, end_date org_id and pay_group_id from role_pay_groups
58 select org_id, pay_group_id, start_date, end_date
59 into l_org_id, l_pay_group_id, l_role_pg_start_date, l_role_pg_end_date
60 from cn_role_pay_groups
61 where role_pay_group_id = p_role_pay_group_id;
62
63 -- get srp role assignment start and end dates
64 select start_date, end_date, salesrep_id
65 into l_srp_role_start_date, l_srp_role_end_date, l_salesrep_id
66 from cn_srp_roles
67 where srp_role_id = p_srp_role_id
68 and org_id = l_org_id;
69
70 -- get intersection between srp_role and role_pay_group dates
71 get_date_range_intersect(
72 a_start_date => l_srp_role_start_date,
73 a_end_date => l_srp_role_end_date,
74 b_start_date => l_role_pg_start_date,
75 b_end_date => l_role_pg_end_date,
76 x_start_date => x_start_date,
77 x_end_date => x_end_date);
78
79 l_start_date := x_start_date;
80 l_end_date := x_end_date;
81
82 -- get resource start and end dates
83 select start_date_active, end_date_active
84 into l_res_start_date, l_res_end_date
85 from cn_salesreps
86 where salesrep_id = l_salesrep_id
87 and org_id = l_org_id;
88
89 -- get intersection with resource start and end dates
90 get_date_range_intersect(
91 a_start_date => l_start_date,
92 a_end_date => l_end_date,
93 b_start_date => l_res_start_date,
94 b_end_date => l_res_end_date,
95 x_start_date => x_start_date,
96 x_end_date => x_end_date);
97
98 l_start_date := x_start_date;
99 l_end_date := x_end_date;
100
101 -- get pay groups start and end dates
102 select start_date, end_date
103 into l_pg_start_date, l_pg_end_date
104 from cn_pay_groups
105 where pay_group_id = l_pay_group_id;
106
107 -- get intersection with pay group start and end dates
108 get_date_range_intersect(
109 a_start_date => l_start_date,
110 a_end_date => l_end_date,
111 b_start_date => l_pg_start_date,
112 b_end_date => l_pg_end_date,
113 x_start_date => x_start_date,
114 x_end_date => x_end_date);
115
116 END;
117
118 --| -----------------------------------------------------------------------+
119 --| Function Name : chk_and get_salesrep_id
120 --| Desc : Based on the employee number and salesrep type passed in,
121 --| Check if only one rec retrieve, if yes get the salesrep_id
122 --| Created to fix the customer Bug which has same employee number
123 --| only happed for multiple contacts for the supplier
124 --| Added By Kumar Sivasankran Dated on 05/OCT/01
125 --|
126 --| ---------------------------------------------------------------------+
127 PROCEDURE chk_and_get_salesrep_id( p_emp_num IN VARCHAR2,
128 p_type IN VARCHAR2,
129 p_source_id IN NUMBER,
130 p_org_id IN NUMBER,
131 x_salesrep_id OUT NOCOPY cn_salesreps.salesrep_id%TYPE,
132 x_return_status OUT NOCOPY VARCHAR2,
133 x_loading_status OUT NOCOPY VARCHAR2) IS
134
135 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
136 l_emp_num cn_salesreps.employee_number%TYPE;
137 p_show_message VARCHAR2(1);
138 BEGIN
139 -- change for performance. Force to hit index on employee_number
140 -- Bug 1508614
141 -- Fixed on 25/0ct/2001
142 p_show_message := fnd_api.g_true;
143 l_emp_num := upper(p_emp_num);
144
145 IF p_emp_num IS NULL THEN
146 SELECT salesrep_id
147 INTO l_salesrep_id
148 FROM cn_salesreps
149 WHERE employee_number IS NULL
150 AND source_id = p_source_id
151 AND org_id = p_org_id
152 AND ((type = p_type) OR (type IS NULL AND p_type IS NULL));
153 ELSE
154 SELECT /*+ first_rows */ salesrep_id
155 INTO l_salesrep_id
156 FROM cn_salesreps
157 WHERE upper(employee_number) = l_emp_num
158 AND source_id = p_source_id
159 AND org_id = p_org_id
160 AND ((type = p_type) OR (type IS NULL AND p_type IS NULL));
161 END IF;
162
163 x_salesrep_id := l_salesrep_id;
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165 x_loading_status := 'CN_SALESREP_FOUND';
166
167 EXCEPTION
168 WHEN no_data_found THEN
169 x_salesrep_id := NULL;
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 x_loading_status := 'CN_SALESREP_NOT_FOUND';
172 IF (p_show_message = FND_API.G_TRUE) THEN
173 -- Error, check the msg level and add an error message to the
174 -- API message list
175 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
176 THEN
177 FND_MESSAGE.SET_NAME ('CN', 'CN_SALESREP_NOT_FOUND');
178 FND_MSG_PUB.Add;
179 END IF;
180 END IF;
181 WHEN too_many_rows THEN
182 x_salesrep_id := NULL;
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 x_loading_status := 'CN_SALESREP_TOO_MANY_ROWS';
185 IF (p_show_message = FND_API.G_TRUE) THEN
186 -- Error, check the msg level and add an error message to the
187 -- API message list
188 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
189 THEN
190 FND_MESSAGE.SET_NAME ('CN', 'CN_SALESREP_TOO_MANY_ROWS');
191 FND_MSG_PUB.Add;
192 END IF;
193 END IF;
194 END chk_and_get_salesrep_id;
195
196 -- -------------------------------------------------------------------------+
197 -- Procedure : ASSIGN_SALESREPS
198 -- Description : To assign pay groups to a salesperson
199 -- -------------------------------------------------------------------------+
200
201 PROCEDURE Assign_salesreps
202 ( p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2,
204 p_commit IN VARCHAR2,
205 p_validation_level IN NUMBER,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2,
209 p_paygroup_assign_rec IN PayGroup_assign_rec,
210 x_loading_status OUT NOCOPY VARCHAR2,
211 x_status OUT NOCOPY VARCHAR2
212 ) IS
213
214 l_api_name CONSTANT VARCHAR2(30) := 'assign_salesreps';
215 l_api_version CONSTANT NUMBER := 1.0;
216 l_pay_group_id NUMBER;
217 l_org_id NUMBER;
218 l_srp_pay_group_id NUMBER;
219 l_salesrep_id cn_srp_pay_groups.salesrep_id%TYPE;
220 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
221 l_status VARCHAR2(1);
222
223 l_pay_group_name cn_pay_groups.name%TYPE;
224 l_create_rec cn_srp_paygroup_pvt.paygroup_assign_rec;
225
226 CURSOR get_pay_group_id_cur IS
227 SELECT pay_group_id
228 FROM cn_pay_groups_all
229 WHERE name = p_paygroup_assign_rec.pay_group_name
230 AND org_id = l_org_id;
231 l_get_pay_group_id_rec get_pay_group_id_cur%ROWTYPE;
232
233 --
234 --Declaration for user hooks
235 --
236 l_paygroup_assign_rec paygroup_assign_rec;
237 l_OAI_array JTF_USR_HKS.oai_data_array_type;
238 l_bind_data_id NUMBER;
239
240 BEGIN
241
242 --
243 -- Standard Start of API savepoint
244 --
245 SAVEPOINT assign_salesreps;
246
247
248 --
249 -- Standard call to check for call compatibility.
250 --
251 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
252 p_api_version ,
253 l_api_name ,
254 G_PKG_NAME )
255 THEN
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END IF;
258
259
260 --
261 -- Initialize message list if p_init_msg_list is set to TRUE.
262 --
263 IF FND_API.to_Boolean( p_init_msg_list ) THEN
264 FND_MSG_PUB.initialize;
265 END IF;
266
267
268 --
269 -- Initialize API return status to success
270 --
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272 x_loading_status := 'CN_INSERTED';
273
274 --
275 -- Assign the parameter to a local variable
276 --
277 l_paygroup_assign_rec := p_paygroup_assign_rec;
278
279 --
280 -- API body
281 --
282
283 --
284 --Validate the input parameters
285 --
286
287 --
288 --Validate org id
289 --
290 l_org_id := l_paygroup_assign_rec.org_id;
291 mo_global.validate_orgid_pub_api
292 (org_id => l_org_id,
293 status => l_status);
294
295 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
296 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
297 'cn.plsql.cn_srp_paygroup_pub.assign_salesreps.org_validate',
298 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
299 end if;
300
301 --
302 --Validate pay group Name
303 --
304 IF ( (cn_api.chk_miss_null_char_para
305 (p_char_para => l_paygroup_assign_rec.pay_group_name,
306 p_obj_name => 'Pay Group Name',
307 p_loading_status => x_loading_status,
308 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
309 THEN
310 RAISE FND_API.G_EXC_ERROR ;
311 END IF;
312
313 -- Fetch pay_group_id
314 OPEN get_pay_group_id_cur;
315 FETCH get_pay_group_id_cur INTO l_get_pay_group_id_rec;
316
317 --
318 --Check to ensure that the specified pay group actually exists
319 --If it does not exist, raise an error
320 --
321 IF get_pay_group_id_cur%ROWCOUNT <> 1
322 THEN
323
324 --Error condition
325 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
326 THEN
327 fnd_message.set_name('CN', 'CN_INVALID_PAY_GROUP');
328 fnd_message.set_token('PAY_GROUP_NAME', l_paygroup_assign_rec.pay_group_name);
329 fnd_msg_pub.add;
330 END IF;
331
332 x_loading_status := 'CN_INVALID_PAY_GROUP';
333 CLOSE get_pay_group_id_cur;
334 RAISE FND_API.G_EXC_ERROR;
335
336 END IF;
337
338 --
339 --Pay group exists, fetch id
340 --
341 l_pay_group_id := l_get_pay_group_id_rec.pay_group_id;
342 CLOSE get_pay_group_id_cur;
343
344 --
345 --Process the salesrep that has to be assigned to the pay group
346 --
347
348 --
349 -- Check to ensure that the salesrep actually exists
350 -- Fetch salesrep ID
351 --
352
353 --**************************************************************
354 -- added Kumar Sivasankaran
355 -- Dated on 05/OCT/01
356 --
357 -- Added new procedure to handle multiple supplier contact
358 --
359 --**************************************************************
360 IF l_paygroup_assign_rec.employee_type = 'SUPPLIER_CONTACT' THEN
361
362 chk_and_get_salesrep_id( p_emp_num =>
363 l_paygroup_assign_rec.employee_number,
364 p_type =>
365 l_paygroup_assign_rec.employee_type,
366 p_source_id => l_paygroup_assign_rec.source_id,
367 p_org_id => l_org_id,
368 x_salesrep_id => l_salesrep_id,
369 x_return_status => x_return_status,
370 x_loading_status=> x_loading_status);
371
372 ELSE
373 cn_api.chk_and_get_salesrep_id( p_emp_num =>
374 l_paygroup_assign_rec.employee_number,
375 p_type =>
376 l_paygroup_assign_rec.employee_type,
377 p_org_id => l_org_id,
378 x_salesrep_id => l_salesrep_id,
379 x_return_status => x_return_status,
380 x_loading_status=> x_loading_status);
381 END IF;
382
383
384 IF x_loading_status = 'CN_SALESREP_FOUND'
385 THEN
386 --
387 --Reset the loading status
388 --
389 x_loading_status := 'CN_INSERTED';
390 ELSE
391 RAISE fnd_api.g_exc_error;
392 END IF;
393
394 --
395 -- User hooks
396 --
397
398 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PAYGROUP_PUB',
399 'ASSIGN_SALESREPS',
400 'B',
401 'C')
402 THEN
403 cn_srp_paygroup_pub_cuhk.assign_salesreps_pre
404 (p_api_version => p_api_version,
405 p_init_msg_list => fnd_api.g_false,
406 p_commit => fnd_api.g_false,
407 p_validation_level => p_validation_level,
408 x_return_status => x_return_status,
409 x_msg_count => x_msg_count,
410 x_msg_data => x_msg_data,
411 p_paygroup_assign_rec => l_payGroup_assign_rec,
412 x_loading_status => x_loading_status,
413 x_status => x_status
414 );
415
416 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
417 THEN
418 RAISE FND_API.G_EXC_ERROR;
419 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
420 THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423 END IF;
424
425 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PAYGROUP_PUB',
426 'ASSIGN_SALESREPS',
427 'B',
428 'V')
429 THEN
430 cn_srp_paygroup_pub_vuhk.assign_salesreps_pre
431 (p_api_version => p_api_version,
432 p_init_msg_list => fnd_api.g_false,
433 p_commit => fnd_api.g_false,
434 p_validation_level => p_validation_level,
435 x_return_status => x_return_status,
436 x_msg_count => x_msg_count,
437 x_msg_data => x_msg_data,
438 p_paygroup_assign_rec => l_payGroup_assign_rec,
439 x_loading_status => x_loading_status,
440 x_status => x_status
441 );
442
443 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
444 THEN
445 RAISE FND_API.G_EXC_ERROR;
446 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
447 THEN
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 END IF;
450 END IF;
451
452 -- call main API
453
454 -- build rec type
455 l_create_rec.salesrep_id := l_salesrep_id;
456 l_create_rec.pay_group_id := l_pay_group_id;
457 l_create_rec.assignment_start_date := l_paygroup_assign_rec.assignment_start_date;
458 l_create_rec.assignment_end_date := l_paygroup_assign_rec.assignment_end_date;
459 l_create_rec.lock_flag := l_paygroup_assign_rec.lock_flag;
460 l_create_rec.role_pay_group_id := l_paygroup_assign_rec.role_pay_group_id;
461 l_create_rec.org_id := l_org_id;
462 l_create_rec.attribute_category := l_paygroup_assign_rec.attribute_category;
463 l_create_rec.attribute1 := l_paygroup_assign_rec.attribute1;
464 l_create_rec.attribute2 := l_paygroup_assign_rec.attribute2;
465 l_create_rec.attribute3 := l_paygroup_assign_rec.attribute3;
466 l_create_rec.attribute4 := l_paygroup_assign_rec.attribute4;
467 l_create_rec.attribute5 := l_paygroup_assign_rec.attribute5;
468 l_create_rec.attribute6 := l_paygroup_assign_rec.attribute6;
469 l_create_rec.attribute7 := l_paygroup_assign_rec.attribute7;
470 l_create_rec.attribute8 := l_paygroup_assign_rec.attribute8;
471 l_create_rec.attribute9 := l_paygroup_assign_rec.attribute9;
472 l_create_rec.attribute10 := l_paygroup_assign_rec.attribute10;
473 l_create_rec.attribute11 := l_paygroup_assign_rec.attribute11;
474 l_create_rec.attribute12 := l_paygroup_assign_rec.attribute12;
475 l_create_rec.attribute13 := l_paygroup_assign_rec.attribute13;
476 l_create_rec.attribute14 := l_paygroup_assign_rec.attribute14;
477 l_create_rec.attribute15 := l_paygroup_assign_rec.attribute15;
478
479 cn_srp_paygroup_pvt.Create_Srp_Pay_Group
480 ( p_api_version => 1.0,
481 x_return_status => x_return_status,
482 x_loading_status => x_loading_status,
483 x_msg_count => x_msg_count,
484 x_msg_data => x_msg_data,
485 p_paygroup_assign_rec => l_create_rec);
486
487 IF x_return_status <> fnd_api.g_ret_sts_success THEN
488 RAISE fnd_api.g_exc_error;
489 END IF;
490
491 --
492 -- End of API body.
493 --
494
495 --
496 -- Post processing hooks
497 --
498
499
500 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PAYGROUP_PUB',
501 'ASSIGN_SALESREPS',
502 'A',
503 'V')
504 THEN
505 cn_srp_paygroup_pub_vuhk.assign_salesreps_post
506 (p_api_version => p_api_version,
507 p_init_msg_list => fnd_api.g_false,
508 p_commit => fnd_api.g_false,
509 p_validation_level => p_validation_level,
510 x_return_status => x_return_status,
511 x_msg_count => x_msg_count,
512 x_msg_data => x_msg_data,
513 p_paygroup_assign_rec => l_payGroup_assign_rec,
514 x_loading_status => x_loading_status,
515 x_status => x_status
516 );
517
518 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
519 THEN
520 RAISE FND_API.G_EXC_ERROR;
521 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
522 THEN
523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
524 END IF;
525 END IF;
526
527 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PAYGROUP_PUB',
528 'ASSIGN_SALESREPS',
529 'A',
530 'C')
531 THEN
532 cn_srp_paygroup_pub_cuhk.assign_salesreps_post
533 (p_api_version => p_api_version,
534 p_init_msg_list => fnd_api.g_false,
535 p_commit => fnd_api.g_false,
536 p_validation_level => p_validation_level,
537 x_return_status => x_return_status,
538 x_msg_count => x_msg_count,
539 x_msg_data => x_msg_data,
540 p_paygroup_assign_rec => l_payGroup_assign_rec,
541 x_loading_status => x_loading_status,
542 x_status => x_status
543 );
544
545 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
546 THEN
547 RAISE FND_API.G_EXC_ERROR;
548 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
549 THEN
550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551 END IF;
552 END IF;
553
554 IF JTF_USR_HKS.Ok_to_execute('CN_SRP_PAYGROUP_PUB',
555 'ASSIGN_SALESREPS',
556 'M',
557 'M')
558 THEN
559 IF cn_srp_paygroup_pub_cuhk.ok_to_generate_msg
560 (p_paygroup_assign_rec => l_paygroup_assign_rec)
561 THEN
562
563 -- Get a ID for workflow/ business object instance
564 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
565
566 -- Do this for all the bind variables in the Business Object
567 JTF_USR_HKS.load_bind_data
568 ( l_bind_data_id, 'SRP_PAY_GROUP_ID', l_srp_pay_group_id, 'S', 'S');
569
570 -- Message generation API
571 JTF_USR_HKS.generate_message
572 (p_prod_code => 'CN',
573 p_bus_obj_code => 'SRP_PGRP',
574 p_bus_obj_name => 'SRP_PAYGROUP',
575 p_action_code => 'I',
576 p_bind_data_id => l_srp_pay_group_id,
577 p_oai_param => null,
578 p_oai_array => l_oai_array,
579 x_return_code => x_return_status) ;
580
581 IF (x_return_status = FND_API.G_RET_STS_ERROR)
582 THEN
583 RAISE FND_API.G_EXC_ERROR;
584 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
585 THEN
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587 END IF;
588 END IF;
589 END IF;
590
591
592
593 -- Standard check of p_commit.
594 IF FND_API.To_Boolean( p_commit ) THEN
595 COMMIT WORK;
596 END IF;
597
598 --
599 -- Standard call to get message count and if count is 1, get message info.
600 --
601
602 FND_MSG_PUB.Count_And_Get
603 (
604 p_count => x_msg_count ,
605 p_data => x_msg_data ,
606 p_encoded => FND_API.G_FALSE
607 );
608
609 EXCEPTION
610 WHEN FND_API.G_EXC_ERROR THEN
611 ROLLBACK TO Assign_salesreps;
612 x_return_status := FND_API.G_RET_STS_ERROR ;
613 FND_MSG_PUB.Count_And_Get
614 (
615 p_count => x_msg_count ,
616 p_data => x_msg_data ,
617 p_encoded => FND_API.G_FALSE
618 );
619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
620 ROLLBACK TO Assign_salesreps;
621 x_loading_status := 'UNEXPECTED_ERR';
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
623 FND_MSG_PUB.Count_And_Get
624 (
625 p_count => x_msg_count ,
626 p_data => x_msg_data ,
627 p_encoded => FND_API.G_FALSE
628 );
629 WHEN OTHERS THEN
630 ROLLBACK TO Assign_salesreps;
631 x_loading_status := 'UNEXPECTED_ERR';
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
633 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
634 THEN
635 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
636 END IF;
637 FND_MSG_PUB.Count_And_Get
638 (
639 p_count => x_msg_count ,
640 p_data => x_msg_data ,
641 p_encoded => FND_API.G_FALSE
642 );
643
644 END Assign_salesreps;
645
646 -- -------------------------------------------------------------------------+
647 -- Procedure : Update_srp_assignment
648 -- Description : TO update the salesrep assignment to a paygroup
649 -- -------------------------------------------------------------------------+
650
651 PROCEDURE Update_srp_assignment
652 ( p_api_version IN NUMBER,
653 p_init_msg_list IN VARCHAR2,
654 p_commit IN VARCHAR2,
655 p_validation_level IN NUMBER,
656 x_return_status OUT NOCOPY VARCHAR2,
657 x_msg_count OUT NOCOPY NUMBER,
658 x_msg_data OUT NOCOPY VARCHAR2,
659 p_old_paygroup_assign_rec IN PayGroup_assign_rec,
660 p_paygroup_assign_rec IN PayGroup_assign_rec,
661 p_ovn IN NUMBER,
662 x_loading_status OUT NOCOPY VARCHAR2,
663 x_status OUT NOCOPY VARCHAR2
664 ) IS
665
666 l_api_name CONSTANT VARCHAR2(30) := 'Update_srp_assignment';
667 l_api_version CONSTANT NUMBER := 1.0;
668 l_pay_group_id NUMBER;
669 l_srp_pay_group_id NUMBER;
670 l_salesrep_id cn_srp_pay_groups.salesrep_id%TYPE;
671 l_old_salesrep_id cn_srp_pay_groups.salesrep_id%TYPE;
672 l_org_id cn_srp_pay_groups.org_id%TYPE;
673 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
674 l_ovn_old NUMBER;
675 l_count NUMBER;
676 l_role_pay_group_id cn_role_pay_groups.role_pay_group_id%TYPE;
677 l_status VARCHAR2(1);
678
679 --
680 --Declaration for user hooks
681 --
682 l_OAI_array JTF_USR_HKS.oai_data_array_type;
683 l_old_paygroup_assign_rec paygroup_assign_rec;
684 l_paygroup_assign_rec paygroup_assign_rec;
685 l_update_rec cn_srp_paygroup_pvt.paygroup_assign_rec;
686 l_bind_data_id NUMBER;
687
688 CURSOR get_pay_group_id_cur(p_name VARCHAR2, p_org_id NUMBER) IS
689 SELECT pay_group_id
690 FROM cn_pay_groups_all
691 WHERE name = p_name
692 AND org_id = p_org_id;
693 l_old_pay_group_id cn_pay_groups.pay_group_id%TYPE;
694
695 CURSOR get_srp_pay_group_id_cur (
696 c_salesrep_id cn_srp_pay_groups.salesrep_id%TYPE,
697 c_pay_group_id cn_srp_pay_groups.pay_group_id%TYPE,
698 c_start_date cn_srp_pay_groups.start_date%TYPE,
699 c_end_date cn_srp_pay_groups.end_date%TYPE) IS
700 SELECT srp_pay_group_id
701 FROM cn_srp_pay_groups_all
702 WHERE salesrep_id = c_salesrep_id
703 AND pay_group_id = c_pay_group_id
704 AND trunc(start_date) = trunc(c_start_date)
705 AND trunc(nvl(end_date, l_null_date)) =
706 trunc(nvl(c_end_date, l_null_date));
707
708 BEGIN
709
710 --
711 -- Standard Start of API savepoint
712 --
713 SAVEPOINT update_srp_assignment;
714
715
716 --
717 -- Standard call to check for call compatibility.
718 --
719 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
720 p_api_version ,
721 l_api_name ,
722 G_PKG_NAME )
723 THEN
724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
725 END IF;
726
727
728 --
729 -- Initialize message list if p_init_msg_list is set to TRUE.
730 --
731 IF FND_API.to_Boolean( p_init_msg_list ) THEN
732 FND_MSG_PUB.initialize;
733 END IF;
734
735
736 --
737 -- Initialize API return status to success
738 --
739 x_return_status := FND_API.G_RET_STS_SUCCESS;
740 x_loading_status := 'CN_UPDATED';
741
742 --
743 -- Assign the parameter to a local variable
744 --
745 l_paygroup_assign_rec := p_paygroup_assign_rec;
746 l_old_paygroup_assign_rec := p_old_paygroup_assign_rec;
747
748 -- API body
749 --
750
751 --
752 --Validate the input parameters
753 --
754
755 --
756 --Validate org ID
757 --
758
759 if nvl(l_paygroup_assign_rec.org_id, -99) <>
760 Nvl(l_old_paygroup_assign_rec.org_id, -99) then
761 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
762 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
763 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
764 'cn.plsql.cn_srp_paygroup_pub.update_srp_assignment.error',
765 true);
766 end if;
767
768 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
769 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
770 FND_MSG_PUB.Add;
771 END IF;
772
773 RAISE FND_API.G_EXC_ERROR ;
774 end if;
775
776 l_org_id := l_old_paygroup_assign_rec.org_id;
777 mo_global.validate_orgid_pub_api
778 (org_id => l_org_id,
779 status => l_status);
780
781 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
782 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
783 'cn.plsql.cn_srp_paygroup_pub.update_srp_assignment.org_validate',
784 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
785 end if;
786
787 --
788 --Validate pay group Name
789 --
790 IF ( (cn_api.chk_miss_null_char_para
791 (p_char_para => l_old_paygroup_assign_rec.pay_group_name,
792 p_obj_name =>
793 cn_api.get_lkup_meaning('PAY_GROUP_NAME', 'PAY_GROUP_VALIDATION_TYPE'),
794 p_loading_status => x_loading_status,
795 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
796 THEN
797 RAISE FND_API.G_EXC_ERROR ;
798 END IF;
799
800 -- Fetch pay_group_id
801 OPEN get_pay_group_id_cur(l_old_paygroup_assign_rec.pay_group_name, l_org_id);
802 FETCH get_pay_group_id_cur INTO l_old_pay_group_id;
803
804
805 --
806 --Check to ensure that the specified pay group actually exists
807 --If it does not exist, raise an error
808 --
809 IF get_pay_group_id_cur%ROWCOUNT <> 1
810 THEN
811
812 --Error condition
813 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
814 THEN
815 fnd_message.set_name('CN', 'CN_INVALID_PAY_GROUP');
816 fnd_message.set_token('PAY_GROUP_NAME',
817 l_old_paygroup_assign_rec.pay_group_name);
818 fnd_msg_pub.add;
819 END IF;
820
821 x_loading_status := 'CN_INVALID_PAY_GROUP';
822 CLOSE get_pay_group_id_cur;
823 RAISE FND_API.G_EXC_ERROR;
824
825 END IF;
826
827
828 --
829 --Pay group exists, close cursor
830 --
831 CLOSE get_pay_group_id_cur;
832
833
834 --
835 --Check for the current paygroup definition
836 --
837
838 --org cannot change - no need to validate new org ID since it is ignored
839
840 --
841 --Validate pay group Name
842 --
843 IF ( (cn_api.chk_miss_null_char_para
844 (p_char_para => l_paygroup_assign_rec.pay_group_name,
845 p_obj_name =>
846 cn_api.get_lkup_meaning('PAY_GROUP_NAME', 'PAY_GROUP_VALIDATION_TYPE'),
847 p_loading_status => x_loading_status,
848 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
849 THEN
850 RAISE FND_API.G_EXC_ERROR ;
851 END IF;
852
853 -- Fetch pay_group_id
854 OPEN get_pay_group_id_cur(l_paygroup_assign_rec.pay_group_name, l_org_id);
855 FETCH get_pay_group_id_cur INTO l_pay_group_id;
856
857 --
858 --Check to ensure that the specified pay group actually exists
859 --If it does not exist, raise an error
860 --
861 IF get_pay_group_id_cur%ROWCOUNT <> 1
862 THEN
863
864 --Error condition
865 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
866 THEN
867 fnd_message.set_name('CN', 'CN_INVALID_PAY_GROUP');
868 fnd_message.set_token('PAY_GROUP_NAME', l_paygroup_assign_rec.pay_group_name);
869 fnd_msg_pub.add;
870 END IF;
871
872 x_loading_status := 'CN_INVALID_PAY_GROUP';
873 CLOSE get_pay_group_id_cur;
874 RAISE FND_API.G_EXC_ERROR;
875
876 END IF;
877
878 --
879 --Pay group exists, close cursor
880 --
881 CLOSE get_pay_group_id_cur;
882
883
884 --
885 -- Process the salesrep that has to be assigned to the pay group
886 --
887 --
888 -- Check to ensure that the salesrep actually exists
889 -- Fetch salesrep ID
890 --
891 --**************************************************************
892 -- added Kumar Sivasankaran
893 -- Dated on 05/OCT/01
894 --
895 -- Added new procedure to handle multiple supplier contact
896 --
897 --**************************************************************
898 IF l_paygroup_assign_rec.employee_type = 'SUPPLIER_CONTACT' THEN
899
900 chk_and_get_salesrep_id( p_emp_num =>
901 l_paygroup_assign_rec.employee_number,
902 p_type =>
903 l_paygroup_assign_rec.employee_type,
904 p_source_id => l_paygroup_assign_rec.source_id,
905 p_org_id => l_org_id,
906 x_salesrep_id => l_salesrep_id,
907 x_return_status => x_return_status,
908 x_loading_status=> x_loading_status);
909
910 ELSE
911
912 cn_api.chk_and_get_salesrep_id( p_emp_num =>
913 l_paygroup_assign_rec.employee_number,
914 p_type =>
915 l_paygroup_assign_rec.employee_type,
916 p_org_id => l_org_id,
917 x_salesrep_id => l_salesrep_id,
918 x_return_status => x_return_status,
919 x_loading_status=> x_loading_status);
920 END IF;
921
922
923
924 IF x_loading_status = 'CN_SALESREP_FOUND'
925 THEN
926 --
927 --Reset the loading status
928 --
929 x_loading_status := 'CN_UPDATED';
930 ELSE
931 RAISE fnd_api.g_exc_error;
932 END IF;
933
934 --**************************************************************
935 -- added Kumar Sivasankaran
936 -- Dated on 05/OCT/01
937 --
938 -- Added new procedure to handle multiple supplier contact
939 --
940 --**************************************************************
941 IF l_old_paygroup_assign_rec.employee_type = 'SUPPLIER_CONTACT' THEN
942
943 chk_and_get_salesrep_id( p_emp_num =>
944 l_old_paygroup_assign_rec.employee_number,
945 p_type =>
946 l_old_paygroup_assign_rec.employee_type,
947 p_source_id => l_paygroup_assign_rec.source_id,
948 p_org_id => l_org_id,
949 x_salesrep_id => l_old_salesrep_id,
950 x_return_status => x_return_status,
951 x_loading_status=> x_loading_status);
952
953 ELSE
954
955 cn_api.chk_and_get_salesrep_id( p_emp_num =>
956 l_old_paygroup_assign_rec.employee_number,
957 p_type =>
958 l_old_paygroup_assign_rec.employee_type,
959 p_org_id => l_org_id,
960 x_salesrep_id => l_old_salesrep_id,
961 x_return_status => x_return_status,
962 x_loading_status=> x_loading_status);
963
964 END IF;
965
966
967 IF x_loading_status = 'CN_SALESREP_FOUND'
968 THEN
969 --
970 --Reset the loading status
971 --
972 x_loading_status := 'CN_UPDATED';
973 ELSE
974 RAISE fnd_api.g_exc_error;
975 END IF;
976
977 OPEN get_srp_pay_group_id_cur(l_old_salesrep_id,
978 l_old_pay_group_id,
979 l_old_paygroup_assign_rec.assignment_start_date,
980 l_old_paygroup_assign_rec.assignment_end_date);
981
982 FETCH get_srp_pay_group_id_cur INTO l_srp_pay_group_id;
983
984 IF get_srp_pay_group_id_cur%rowcount = 0
985 THEN
986 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
987 THEN
988 fnd_message.set_name('CN', 'CN_INVALID_SRP_PGRP_ASGN');
989 fnd_message.set_token('EMPLOYEE_TYPE',
990 l_old_paygroup_assign_rec.employee_type);
991 fnd_message.set_token('EMPLOYEE_NUMBER',
992 l_old_paygroup_assign_rec.employee_number);
993 fnd_msg_pub.add;
994 END IF;
995 x_loading_status := 'CN_INVALID_SRP_PGRP_ASGN';
996 CLOSE get_srp_pay_group_id_cur;
997 RAISE FND_API.G_EXC_ERROR;
998 END IF;
999
1000 CLOSE get_srp_pay_group_id_cur;
1001
1002 --
1003 -- User hooks
1004 --
1005
1006 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PAYGROUP_PUB',
1007 'UPDATE_SRP_ASSIGNMENT',
1008 'B',
1009 'C')
1010 THEN
1011 cn_srp_paygroup_pub_cuhk.update_srp_assignment_pre
1012 (p_api_version => p_api_version,
1013 p_init_msg_list => fnd_api.g_false,
1014 p_commit => fnd_api.g_false,
1015 p_validation_level => p_validation_level,
1016 x_return_status => x_return_status,
1017 x_msg_count => x_msg_count,
1018 x_msg_data => x_msg_data,
1019 p_paygroup_assign_rec => l_payGroup_assign_rec,
1020 p_old_paygroup_assign_rec => l_old_payGroup_assign_rec,
1021 x_loading_status => x_loading_status,
1022 x_status => x_status
1023 );
1024
1025 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1026 THEN
1027 RAISE FND_API.G_EXC_ERROR;
1028 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1029 THEN
1030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1031 END IF;
1032 END IF;
1033
1034 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PAYGROUP_PUB',
1035 'UPDATE_SRP_ASSIGNMENT',
1036 'B',
1037 'V')
1038 THEN
1039 cn_srp_paygroup_pub_vuhk.update_srp_assignment_pre
1040 (p_api_version => p_api_version,
1041 p_init_msg_list => fnd_api.g_false,
1042 p_commit => fnd_api.g_false,
1043 p_validation_level => p_validation_level,
1044 x_return_status => x_return_status,
1045 x_msg_count => x_msg_count,
1046 x_msg_data => x_msg_data,
1047 p_paygroup_assign_rec => l_payGroup_assign_rec,
1048 p_old_paygroup_assign_rec => l_old_payGroup_assign_rec,
1049 x_loading_status => x_loading_status,
1050 x_status => x_status
1051 );
1052
1053 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1054 THEN
1055 RAISE FND_API.G_EXC_ERROR;
1056 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1057 THEN
1058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1059 END IF;
1060 END IF;
1061
1062 -- main API
1063
1064 -- build update rec
1065 l_update_rec.srp_pay_group_id := l_srp_pay_group_id;
1066 l_update_rec.salesrep_id := l_salesrep_id;
1067 l_update_rec.pay_group_id := l_pay_group_id;
1068 l_update_rec.assignment_start_date := l_paygroup_assign_rec.assignment_start_date;
1069 l_update_rec.assignment_end_date := l_paygroup_assign_rec.assignment_end_date;
1070 l_update_rec.lock_flag := l_paygroup_assign_rec.lock_flag;
1071 l_update_rec.role_pay_group_id := l_paygroup_assign_rec.role_pay_group_id;
1072 l_update_rec.org_id := l_org_id;
1073 l_update_rec.attribute_category := l_paygroup_assign_rec.attribute_category;
1074 l_update_rec.attribute1 := l_paygroup_assign_rec.attribute1;
1075 l_update_rec.attribute2 := l_paygroup_assign_rec.attribute2;
1076 l_update_rec.attribute3 := l_paygroup_assign_rec.attribute3;
1077 l_update_rec.attribute4 := l_paygroup_assign_rec.attribute4;
1078 l_update_rec.attribute5 := l_paygroup_assign_rec.attribute5;
1079 l_update_rec.attribute6 := l_paygroup_assign_rec.attribute6;
1080 l_update_rec.attribute7 := l_paygroup_assign_rec.attribute7;
1081 l_update_rec.attribute8 := l_paygroup_assign_rec.attribute8;
1082 l_update_rec.attribute9 := l_paygroup_assign_rec.attribute9;
1083 l_update_rec.attribute10 := l_paygroup_assign_rec.attribute10;
1084 l_update_rec.attribute11 := l_paygroup_assign_rec.attribute11;
1085 l_update_rec.attribute12 := l_paygroup_assign_rec.attribute12;
1086 l_update_rec.attribute13 := l_paygroup_assign_rec.attribute13;
1087 l_update_rec.attribute14 := l_paygroup_assign_rec.attribute14;
1088 l_update_rec.attribute15 := l_paygroup_assign_rec.attribute15;
1089
1090
1091 -- call private API
1092 cn_srp_paygroup_pvt.Update_Srp_Pay_Group
1093 ( p_api_version => 1.0,
1094 x_return_status => x_return_status,
1095 x_loading_status => x_loading_status,
1096 x_msg_count => x_msg_count,
1097 x_msg_data => x_msg_data,
1098 p_paygroup_assign_rec => l_update_rec);
1099
1100 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1101 RAISE fnd_api.g_exc_error;
1102 END IF;
1103
1104 --
1105 -- End of API body.
1106 --
1107
1108
1109 --
1110 -- Post processing hooks
1111 --
1112
1113
1114 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PAYGROUP_PUB',
1115 'UPDATE_SRP_ASSIGNMENT',
1116 'A',
1117 'V')
1118 THEN
1119 cn_srp_paygroup_pub_vuhk.update_srp_assignment_post
1120 (p_api_version => p_api_version,
1121 p_init_msg_list => fnd_api.g_false,
1122 p_commit => fnd_api.g_false,
1123 p_validation_level => p_validation_level,
1124 x_return_status => x_return_status,
1125 x_msg_count => x_msg_count,
1126 x_msg_data => x_msg_data,
1127 p_old_paygroup_assign_rec => l_old_payGroup_assign_rec,
1128 p_paygroup_assign_rec => l_payGroup_assign_rec,
1129 x_loading_status => x_loading_status,
1130 x_status => x_status
1131 );
1132
1133 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1134 THEN
1135 RAISE FND_API.G_EXC_ERROR;
1136 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1137 THEN
1138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1139 END IF;
1140 END IF;
1141
1142 IF JTF_USR_HKS.Ok_to_Execute('CN_SRP_PAYGROUP_PUB',
1143 'UPDATE_SRP_ASSIGNMENT',
1144 'A',
1145 'C')
1146 THEN
1147 cn_srp_paygroup_pub_cuhk.update_srp_assignment_post
1148 (p_api_version => p_api_version,
1149 p_init_msg_list => fnd_api.g_false,
1150 p_commit => fnd_api.g_false,
1151 p_validation_level => p_validation_level,
1152 x_return_status => x_return_status,
1153 x_msg_count => x_msg_count,
1154 x_msg_data => x_msg_data,
1155 p_old_paygroup_assign_rec => l_old_payGroup_assign_rec,
1156 p_paygroup_assign_rec => l_payGroup_assign_rec,
1157 x_loading_status => x_loading_status,
1158 x_status => x_status
1159 );
1160
1161 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1162 THEN
1163 RAISE FND_API.G_EXC_ERROR;
1164 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1165 THEN
1166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1167 END IF;
1168 END IF;
1169
1170 IF JTF_USR_HKS.Ok_to_execute('CN_SRP_PAYGROUP_PUB',
1171 'UPDATE_SRP_ASSIGNMENT',
1172 'M',
1173 'M')
1174 THEN
1175 IF cn_srp_paygroup_pub_cuhk.ok_to_generate_msg
1176 (p_paygroup_assign_rec => l_paygroup_assign_rec)
1177 THEN
1178
1179 -- Get a ID for workflow/ business object instance
1180 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
1181
1182 -- Do this for all the bind variables in the Business Object
1183 JTF_USR_HKS.load_bind_data
1184 ( l_bind_data_id, 'SRP_PAY_GROUP_ID', l_srp_pay_group_id, 'S', 'S');
1185
1186 -- Message generation API
1187 JTF_USR_HKS.generate_message
1188 (p_prod_code => 'CN',
1189 p_bus_obj_code => 'SRP_PGRP',
1190 p_bus_obj_name => 'SRP_PAYGROUP',
1191 p_action_code => 'I',
1192 p_bind_data_id => l_bind_data_id,
1193 p_oai_param => null,
1194 p_oai_array => l_oai_array,
1195 x_return_code => x_return_status) ;
1196
1197 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1198 THEN
1199 RAISE FND_API.G_EXC_ERROR;
1200 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1201 THEN
1202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1203 END IF;
1204 END IF;
1205 END IF;
1206
1207 -- Standard check of p_commit.
1208 IF FND_API.To_Boolean( p_commit ) THEN
1209 COMMIT WORK;
1210 END IF;
1211
1212 --
1213 -- Standard call to get message count and if count is 1, get message info.
1214 --
1215
1216 FND_MSG_PUB.Count_And_Get
1217 (
1218 p_count => x_msg_count ,
1219 p_data => x_msg_data ,
1220 p_encoded => FND_API.G_FALSE
1221 );
1222
1223 EXCEPTION
1224 WHEN FND_API.G_EXC_ERROR THEN
1225 ROLLBACK TO update_srp_assignment;
1226 x_return_status := FND_API.G_RET_STS_ERROR ;
1227 FND_MSG_PUB.Count_And_Get
1228 (
1229 p_count => x_msg_count ,
1230 p_data => x_msg_data ,
1231 p_encoded => FND_API.G_FALSE
1232 );
1233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1234 ROLLBACK TO update_srp_assignment;
1235 x_loading_status := 'UNEXPECTED_ERR';
1236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1237 FND_MSG_PUB.Count_And_Get
1238 (
1239 p_count => x_msg_count ,
1240 p_data => x_msg_data ,
1241 p_encoded => FND_API.G_FALSE
1242 );
1243 WHEN OTHERS THEN
1244 ROLLBACK TO update_srp_assignment;
1245 x_loading_status := 'UNEXPECTED_ERR';
1246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1247 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1248 THEN
1249 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1250 END IF;
1251 FND_MSG_PUB.Count_And_Get
1252 (
1253 p_count => x_msg_count ,
1254 p_data => x_msg_data ,
1255 p_encoded => FND_API.G_FALSE
1256 );
1257
1258 END update_srp_assignment;
1259
1260
1261 -- --------------------------------------------------------------------------*
1262 -- Procedure: Create_Mass_Asgn_Srp_Pay_Groups
1263 -- --------------------------------------------------------------------------*
1264
1265 PROCEDURE Create_Mass_Asgn_Srp_Pay
1266 (
1267 p_api_version IN NUMBER,
1268 p_init_msg_list IN VARCHAR2,
1269 p_commit IN VARCHAR2,
1270 p_validation_level IN NUMBER,
1271 x_return_status OUT NOCOPY VARCHAR2,
1272 x_msg_count OUT NOCOPY NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2,
1274 p_srp_role_id IN NUMBER,
1275 p_role_pay_group_id IN NUMBER,
1276 x_srp_pay_group_id OUT NOCOPY NUMBER,
1277 x_loading_status OUT NOCOPY VARCHAR2
1278 ) IS
1279
1280 l_return_status VARCHAR2(2000);
1281 l_msg_count NUMBER;
1282 l_msg_data VARCHAR2(2000);
1283 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
1284 l_loading_status VARCHAR2(2000);
1285 l_status VARCHAR2(2000);
1286 l_count NUMBER;
1287 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mass_Asgn_Srp_Pay';
1288 l_api_version CONSTANT NUMBER := 1.0;
1289 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
1290 newrec CN_SRP_PAYGROUP_PUB.PayGroup_assign_rec;
1291 l_salesrep_type cn_salesreps.type%TYPE;
1292 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
1293 l_org_id cn_salesreps.org_id%TYPE;
1294 l_emp_num cn_salesreps.employee_number%TYPE;
1295 l_pay_group_name cn_pay_groups.name%TYPE;
1296 l_pay_group_id cn_pay_groups.pay_group_id%TYPE;
1297 l_pg_start_date cn_pay_groups.start_date%TYPE;
1298 l_pg_end_date cn_pay_groups.end_date%TYPE;
1299 l_srp_start_date cn_srp_roles.start_date%TYPE;
1300 l_srp_end_date cn_pay_groups.end_date%TYPE;
1301 l_start_date cn_srp_pay_groups.start_date%TYPE;
1302 l_end_date cn_srp_pay_groups.start_date%TYPE;
1303 l_source_id cn_salesreps.source_id%TYPE;
1304
1305 BEGIN
1306 -- Standard Start of API savepoint
1307 SAVEPOINT Create_Mass_Asgn_Srp_Pay;
1308 -- Standard call to check for call compatibility.
1309 IF NOT FND_API.compatible_api_call
1310 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
1311 THEN
1312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1313 END IF;
1314 -- Initialize message list if p_init_msg_list is set to TRUE.
1315 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1316 FND_MSG_PUB.initialize;
1317 END IF;
1318 -- Initialize API return status to success
1319 x_return_status := FND_API.G_RET_STS_SUCCESS;
1320 x_loading_status := 'CN_INSERTED';
1321
1322 -- begin API
1323 select pay_group_id, start_date, end_date, org_id
1324 into l_pay_group_id, l_pg_start_date, l_pg_end_date, l_org_id
1325 from cn_role_pay_groups_all
1326 where role_pay_group_id = p_role_pay_group_id;
1327
1328 -- validate org ID
1329 mo_global.validate_orgid_pub_api
1330 (org_id => l_org_id,
1331 status => l_status);
1332
1333 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1334 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1335 'cn.plsql.cn_srp_paygroup_pub.create_mass_asgn_srp_pay.org_validate',
1336 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
1337 end if;
1338
1339 select salesrep_id, start_date, end_date
1340 into l_salesrep_id, l_srp_start_date, l_srp_end_date
1341 from cn_srp_roles
1342 where srp_role_id = p_srp_role_id
1343 AND org_id = l_org_id;
1344
1345 select employee_number, type, source_id
1346 into l_emp_num, l_salesrep_type, l_source_id
1347 from cn_salesreps
1348 where salesrep_id = l_salesrep_id
1349 AND org_id = l_org_id;
1350
1351 select name
1352 into l_pay_group_name
1353 from cn_pay_groups_all
1354 where pay_group_id = l_pay_group_id;
1355
1356 l_start_date := NULL;
1357 l_end_date := NULL;
1358
1359 get_masgn_date_intersect( -- Bug fix 5458432. vensrini
1360 p_srp_role_id => p_srp_role_id,
1361 p_role_pay_group_id => p_role_pay_group_id,
1362 x_start_date => l_start_date,
1363 x_end_date => l_end_date);
1364
1365 IF l_start_date IS NOT NULL AND l_end_date IS NOT NULL THEN
1366
1367 select count(*)
1368 into l_count
1369 from cn_srp_pay_groups_all
1370 where salesrep_id = l_salesrep_id
1371 AND org_id = l_org_id
1372 and ((l_start_date between start_date and nvl(end_date,l_null_date))
1373 or (nvl(l_end_date,l_null_date) between
1374 start_date and nvl(end_date,l_null_date)));
1375
1376 IF l_count = 0
1377
1378 THEN
1379
1380 newrec.employee_type := l_salesrep_type;
1381 newrec.employee_number := l_emp_num;
1382 newrec.pay_group_name := l_pay_group_name;
1383 newrec.assignment_start_date := l_start_date;
1384 newrec.assignment_end_date := l_end_date;
1385 newrec.role_pay_group_id := p_role_pay_group_id;
1386 newrec.lock_flag := 'N';
1387 newrec.source_id := l_source_id;
1388
1389 Assign_salesreps
1390 (p_api_version => 1.0,
1391 x_return_status => l_return_status,
1392 x_msg_count => l_msg_count,
1393 x_msg_data => l_msg_data,
1394 p_paygroup_assign_rec => newrec,
1395 x_loading_status => l_loading_status,
1396 x_status => l_status );
1397
1398 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1399 RAISE fnd_api.g_exc_error;
1400 END IF;
1401
1402 l_return_status := FND_API.G_RET_STS_SUCCESS;
1403 x_return_status := l_return_status;
1404 x_loading_status := l_loading_status;
1405 ELSE
1406 null;
1407 END IF;
1408
1409 ELSE
1410 NULL;
1411 END IF;
1412
1413 -- Standard check of p_commit.
1414 IF FND_API.To_Boolean( p_commit ) THEN
1415 COMMIT WORK;
1416 END IF;
1417 -- Standard call to get message count and if count is 1, get message info.
1418 FND_MSG_PUB.Count_And_Get
1419 (
1420 p_count => x_msg_count ,
1421 p_data => x_msg_data ,
1422 p_encoded => FND_API.G_FALSE
1423 );
1424 EXCEPTION
1425 WHEN FND_API.G_EXC_ERROR THEN
1426 ROLLBACK TO Create_Mass_Asgn_Srp_Pay;
1427 x_return_status := FND_API.G_RET_STS_ERROR ;
1428 FND_MSG_PUB.Count_And_Get
1429 (
1430 p_count => x_msg_count ,
1431 p_data => x_msg_data ,
1432 p_encoded => FND_API.G_FALSE
1433 );
1434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435 ROLLBACK TO Create_Mass_Asgn_Srp_Pay;
1436 x_loading_status := 'UNEXPECTED_ERR';
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1438 FND_MSG_PUB.Count_And_Get
1439 (
1440 p_count => x_msg_count ,
1441 p_data => x_msg_data ,
1442 p_encoded => FND_API.G_FALSE
1443 );
1444 WHEN OTHERS THEN
1445 ROLLBACK TO Create_Mass_Asgn_Srp_Pay;
1446 x_loading_status := 'UNEXPECTED_ERR';
1447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1448 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1449 THEN
1450 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1451 END IF;
1452 FND_MSG_PUB.Count_And_Get
1453 (
1454 p_count => x_msg_count ,
1455 p_data => x_msg_data ,
1456 p_encoded => FND_API.G_FALSE
1457 );
1458
1459 END Create_Mass_Asgn_Srp_Pay;
1460
1461 -- --------------------------------------------------------------------------*
1462 -- Procedure: Update_Mass_Asgn_Srp_Pay
1463 -- --------------------------------------------------------------------------*
1464
1465 PROCEDURE Update_Mass_Asgn_Srp_Pay
1466 (
1467 p_api_version IN NUMBER,
1468 p_init_msg_list IN VARCHAR2,
1469 p_commit IN VARCHAR2,
1470 p_validation_level IN NUMBER,
1471 x_return_status OUT NOCOPY VARCHAR2,
1472 x_msg_count OUT NOCOPY NUMBER,
1473 x_msg_data OUT NOCOPY VARCHAR2,
1474 p_srp_role_id IN NUMBER,
1475 p_role_pay_group_id IN NUMBER,
1476 x_srp_pay_group_id OUT NOCOPY NUMBER,
1477 x_loading_status OUT NOCOPY VARCHAR2
1478 ) IS
1479
1480 l_return_status VARCHAR2(2000);
1481 l_msg_count NUMBER;
1482 l_msg_data VARCHAR2(2000);
1483 l_srp_pmt_plan_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
1484 l_loading_status VARCHAR2(2000);
1485 l_status VARCHAR2(2000);
1486 l_lock_flag cn_srp_pay_groups.lock_flag%TYPE;
1487 l_api_name CONSTANT VARCHAR2(30) := 'Update_Mass_Asgn_Srp_Pay';
1488 l_api_version CONSTANT NUMBER := 1.0;
1489 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
1490 l_count NUMBER;
1491 l_count_pay NUMBER;
1492 l_count_role NUMBER;
1493 l_count_srp_pay_group NUMBER;
1494 l_salesrep_type_old cn_salesreps.type%TYPE;
1495 l_salesrep_id_old cn_salesreps.salesrep_id%TYPE;
1496 l_emp_num_old cn_salesreps.employee_number%TYPE;
1497 l_pay_group_name_old cn_pay_groups.name%TYPE;
1498 l_pay_group_id_old cn_pay_groups.pay_group_id%TYPE;
1499 l_role_id_old cn_roles.role_id%TYPE;
1500 l_start_date_old cn_srp_pay_groups.start_date%TYPE;
1501 l_end_date_old cn_srp_pay_groups.start_date%TYPE;
1502 l_ovn_old cn_srp_pay_groups.object_version_number%TYPE;
1503 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
1504 l_org_id cn_srp_pay_groups.org_id%TYPE;
1505
1506 newrec CN_SRP_PAYGROUP_PUB.PayGroup_assign_rec;
1507 oldrec CN_SRP_PAYGROUP_PUB.PayGroup_assign_rec;
1508
1509 delrec CN_Srp_PayGroup_PVT.PayGroup_assign_rec;
1510 l_salesrep_type_new cn_salesreps.type%TYPE;
1511 l_salesrep_id_new cn_salesreps.salesrep_id%TYPE;
1512 l_emp_num_new cn_salesreps.employee_number%TYPE;
1513 l_pay_group_name_new cn_pay_groups.name%TYPE;
1514 l_pay_group_id_new cn_pay_groups.pay_group_id%TYPE;
1515 l_pp_start_date_new cn_pay_groups.start_date%TYPE;
1516 l_pp_end_date_new cn_pay_groups.end_date%TYPE;
1517 l_srp_start_date_new cn_srp_roles.start_date%TYPE;
1518 l_srp_end_date_new cn_srp_roles.end_date%TYPE;
1519 l_start_date_new cn_srp_pay_groups.start_date%TYPE;
1520 l_end_date_new cn_srp_pay_groups.start_date%TYPE;
1521 l_source_id_old cn_salesreps.source_id%TYPE;
1522 l_source_id_new cn_salesreps.source_id%TYPE;
1523
1524
1525 --changed the cursor to get proper srp-pay_group assignment to be updated--Hanaraya
1526 CURSOR get_pay_groups
1527 (l_salesrep_id_old NUMBER,
1528 p_role_pay_group_id NUMBER) IS
1529 select srp_pay_group_id,pay_group_id, start_date, end_date,object_version_number,lock_flag
1530 from cn_srp_pay_groups sp
1531 where salesrep_id = l_salesrep_id_old
1532 AND role_pay_group_id = p_role_pay_group_id
1533 AND NOT EXISTS
1534 ( Select 1 from cn_srp_roles sr, cn_role_pay_groups rp
1535 Where salesrep_id = l_salesrep_id_old
1536 AND role_pay_group_id = p_role_pay_group_id
1537 AND sr.role_id = rp.role_id
1538 AND sr.org_id = rp.org_id
1539 AND greatest(sr.start_date,rp.start_date) = sp.start_date
1540 AND least(nvl(sr.end_date,l_null_date),nvl(rp.end_date,l_null_date))
1541 = nvl(sp.end_date,l_null_date)
1542 );
1543
1544 BEGIN
1545 -- Standard Start of API savepoint
1546 SAVEPOINT Update_Mass_Asgn_Srp_Pay;
1547 -- Standard call to check for call compatibility.
1548 IF NOT FND_API.compatible_api_call
1549 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
1550 THEN
1551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1552 END IF;
1553 -- Initialize message list if p_init_msg_list is set to TRUE.
1554 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1555 FND_MSG_PUB.initialize;
1556 END IF;
1557 -- Initialize API return status to success
1558 x_return_status := FND_API.G_RET_STS_SUCCESS;
1559 x_loading_status := 'CN_UPDATED';
1560
1561 -- begin API
1562 SELECT org_id
1563 INTO l_org_id
1564 FROM cn_role_pay_groups_all
1565 WHERE role_pay_group_id = p_role_pay_group_id;
1566
1567 -- validate org ID
1568 mo_global.validate_orgid_pub_api
1569 (org_id => l_org_id,
1570 status => l_status);
1571
1572 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1573 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1574 'cn.plsql.cn_srp_paygroup_pub.update_mass_asgn_srp_pay.org_validate',
1575 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
1576 end if;
1577
1578 SELECT salesrep_id,role_id,start_date, end_date
1579 INTO l_salesrep_id_old,l_role_id_old,l_srp_start_date_new, l_srp_end_date_new
1580 FROM cn_srp_roles
1581 WHERE srp_role_id = p_srp_role_id
1582 AND org_id = l_org_id;
1583
1584 SELECT count(*)
1585 INTO l_count
1586 FROM cn_srp_pay_groups_all
1587 WHERE salesrep_id = l_salesrep_id_old
1588 AND role_pay_group_id = p_role_pay_group_id;
1589
1590 IF (l_count <> 0)
1591 THEN
1592 FOR paygroup IN get_pay_groups(l_salesrep_id_old, p_role_pay_group_id) LOOP
1593 l_pay_group_id_old := paygroup.pay_group_id;
1594 l_start_date_old := paygroup.start_date;
1595 l_end_date_old := paygroup.end_date;
1596 l_ovn_old := paygroup.object_version_number;
1597 l_lock_flag := paygroup.lock_flag;
1598
1599 /* commented out validation for bug 5018892 - it is performed later
1600 SELECT count(*) into l_count_pay
1601 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
1602 cn_payruns_all prun
1603 WHERE w.salesrep_id = l_salesrep_id_old
1604 AND w.org_id = l_org_id
1605 AND prun.pay_period_id = prd.period_id
1606 AND prun.payrun_id = w.payrun_id
1607 AND prun.pay_group_id = l_pay_group_id_old
1608 AND prd.org_id = l_org_id
1609 AND ((prd.start_date BETWEEN l_start_date_old AND nvl(l_end_date_old,l_null_date)) OR
1610 (prd.end_date between l_start_date_old AND nvl(l_end_date_old,l_null_date)) );
1611
1612
1613 IF l_count_pay > 0
1614 THEN
1615 RAISE FND_API.G_EXC_ERROR;
1616 END IF;
1617 */
1618
1619 SELECT employee_number, type,source_id
1620 INTO l_emp_num_old, l_salesrep_type_old,l_source_id_old
1621 FROM cn_salesreps
1622 WHERE salesrep_id = l_salesrep_id_old
1623 AND org_id = l_org_id;
1624
1625
1626
1627 SELECT name
1628 INTO l_pay_group_name_old
1629 FROM cn_pay_groups_all
1630 WHERE pay_group_id = l_pay_group_id_old;
1631
1632 oldrec.employee_type := l_salesrep_type_old;
1633 oldrec.employee_number := l_emp_num_old;
1634 oldrec.source_id := l_source_id_old;
1635 delrec.salesrep_id := l_salesrep_id_old;
1636 oldrec.pay_group_name := l_pay_group_name_old;
1637 delrec.pay_group_id := l_pay_group_id_old;
1638 oldrec.org_id := l_org_id;
1639 delrec.org_id := l_org_id;
1640 oldrec.assignment_start_date := l_start_date_old;
1641 delrec.assignment_start_date := l_start_date_old;
1642 oldrec.assignment_end_date := l_end_date_old;
1643 delrec.assignment_end_date := l_end_date_old;
1644 oldrec.role_pay_group_id := p_role_pay_group_id;
1645 delrec.role_pay_group_id := p_role_pay_group_id;
1646
1647 end loop;
1648 END IF;
1649
1650 SELECT salesrep_id, start_date, end_date
1651 INTO l_salesrep_id_new, l_srp_start_date_new, l_srp_end_date_new
1652 FROM cn_srp_roles
1653 WHERE srp_role_id = p_srp_role_id
1654 AND org_id = l_org_id;
1655
1656 SELECT employee_number, type,source_id
1657 INTO l_emp_num_new, l_salesrep_type_new,l_source_id_new
1658 FROM cn_salesreps
1659 WHERE salesrep_id = l_salesrep_id_new
1660 AND org_id = l_org_id;
1661
1662 SELECT pay_group_id, start_date, end_date
1663 INTO l_pay_group_id_new, l_pp_start_date_new, l_pp_end_date_new
1664 FROM cn_role_pay_groups_all
1665 WHERE role_pay_group_id = p_role_pay_group_id;
1666
1667 SELECT name
1668 INTO l_pay_group_name_new
1669 FROM cn_pay_groups_all
1670 WHERE pay_group_id = l_pay_group_id_new;
1671
1672 SELECT count(*)
1673 INTO l_count_srp_pay_group
1674 FROM cn_srp_pay_groups_all
1675 WHERE salesrep_id=l_salesrep_id_old
1676 AND org_id = l_org_id
1677 AND ((l_start_date_old between start_date and nvl(end_date,l_null_date))
1678 OR (l_end_date_old between start_date and nvl(end_date,l_null_date)));
1679
1680 l_start_date_new := NULL;
1681 l_end_date_new := NULL;
1682
1683 IF (l_lock_flag = 'N' or l_count=0)
1684 THEN
1685 get_masgn_date_intersect( -- Bug fix 5458432
1686 p_srp_role_id => p_srp_role_id,
1687 p_role_pay_group_id => p_role_pay_group_id,
1688 x_start_date => l_start_date_new,
1689 x_end_date => l_end_date_new);
1690
1691 IF l_start_date_new IS NOT NULL AND l_end_date_new IS NOT NULL THEN
1692 newrec.employee_type := l_salesrep_type_new;
1693 newrec.employee_number := l_emp_num_new;
1694 newrec.pay_group_name := l_pay_group_name_new;
1695 newrec.org_id := l_org_id;
1696 newrec.assignment_start_date := l_start_date_new;
1697 newrec.assignment_end_date := l_end_date_new;
1698 newrec.lock_flag :='N';
1699 newrec.role_pay_group_id := p_role_pay_group_id;
1700 newrec.source_id := l_source_id_new;
1701
1702 IF (l_count > 0 )
1703 THEN
1704 Update_srp_Assignment
1705 (p_api_version => 1.0,
1706 x_return_status => l_return_status,
1707 x_msg_count => l_msg_count,
1708 x_msg_data => l_msg_data,
1709 p_old_paygroup_assign_rec => oldrec,
1710 p_paygroup_assign_rec => newrec,
1711 p_ovn => l_ovn_old ,
1712 x_loading_status => l_loading_status,
1713 x_status => l_status );
1714
1715 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1716 RAISE fnd_api.g_exc_error;
1717 END IF;
1718
1719 l_return_status := FND_API.G_RET_STS_SUCCESS;
1720
1721 IF l_loading_status = 'CN_INVALID_SRP_PGRP_ASGN_DT' THEN
1722 cn_srp_paygroup_pvt.delete_srp_pay_group
1723 (
1724 p_api_version => 1.0,
1725 x_return_status => l_return_status,
1726 x_loading_status => l_loading_status,
1727 x_msg_count => l_msg_count,
1728 x_msg_data => l_msg_data,
1729 p_paygroup_assign_rec => delrec
1730 );
1731
1732 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1733 RAISE fnd_api.g_exc_error;
1734 END IF;
1735 END IF;
1736
1737 l_return_status := FND_API.G_RET_STS_SUCCESS;
1738 x_return_status := l_return_status;
1739 x_loading_status := l_loading_status;
1740 ELSIF (l_count_srp_pay_group = 0 )
1741 THEN
1742
1743 SELECT count(*)
1744 INTO l_count_srp_pay_group
1745 FROM cn_srp_pay_groups_all
1746 WHERE salesrep_id=l_salesrep_id_old
1747 AND org_id = l_org_id
1748 AND ((l_start_date_new between start_date and nvl(end_date,l_null_date))
1749 OR (l_end_date_new between start_date and nvl(end_date,l_null_date)));
1750
1751 IF (l_count_srp_pay_group = 0) THEN
1752
1753 Assign_salesreps
1754 (p_api_version => 1.0,
1755 x_return_status => l_return_status,
1756 x_msg_count => l_msg_count,
1757 x_msg_data => l_msg_data,
1758 p_paygroup_assign_rec=> newrec,
1759 x_loading_status => l_loading_status,
1760 x_status => l_status );
1761
1762 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1763 RAISE fnd_api.g_exc_error;
1764 END IF;
1765 END IF;
1766 l_return_status := FND_API.G_RET_STS_SUCCESS;
1767 x_return_status := l_return_status;
1768 x_loading_status := l_loading_status;
1769
1770 END IF;
1771
1772 ELSIF l_count <> 0 THEN
1773 cn_srp_paygroup_pvt.delete_srp_pay_group
1774 (
1775 p_api_version => 1.0,
1776 x_return_status => l_return_status,
1777 x_loading_status => l_loading_status,
1778 x_msg_count => l_msg_count,
1779 x_msg_data => l_msg_data,
1780 p_paygroup_assign_rec=> delrec
1781
1782 );
1783
1784 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1785 RAISE fnd_api.g_exc_error;
1786 END IF;
1787
1788 l_return_status:=FND_API.G_RET_STS_SUCCESS;
1789
1790 END IF;
1791 ELSE
1792 NULL;
1793
1794 END IF;
1795
1796 -- Standard check of p_commit.
1797 IF FND_API.To_Boolean( p_commit ) THEN
1798 COMMIT WORK;
1799 END IF;
1800 -- Standard call to get message count and if count is 1, get message info.
1801 FND_MSG_PUB.Count_And_Get
1802 (
1803 p_count => x_msg_count ,
1804 p_data => x_msg_data ,
1805 p_encoded => FND_API.G_FALSE
1806 );
1807
1808 EXCEPTION
1809 WHEN FND_API.G_EXC_ERROR THEN
1810 ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
1811 x_return_status := FND_API.G_RET_STS_ERROR ;
1812 FND_MSG_PUB.Count_And_Get
1813 (
1814 p_count => x_msg_count ,
1815 p_data => x_msg_data ,
1816 p_encoded => FND_API.G_FALSE
1817 );
1818 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1819 ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
1820 x_loading_status := 'UNEXPECTED_ERR';
1821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1822 FND_MSG_PUB.Count_And_Get
1823 (
1824 p_count => x_msg_count ,
1825 p_data => x_msg_data ,
1826 p_encoded => FND_API.G_FALSE
1827 );
1828 WHEN OTHERS THEN
1829 ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
1830 x_loading_status := 'UNEXPECTED_ERR';
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1832 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1833 THEN
1834 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1835 END IF;
1836 FND_MSG_PUB.Count_And_Get
1837 (
1838 p_count => x_msg_count ,
1839 p_data => x_msg_data ,
1840 p_encoded => FND_API.G_FALSE
1841 );
1842
1843 End Update_Mass_Asgn_Srp_Pay;
1844
1845 END CN_Srp_PayGroup_PUB ;