[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PAYGROUP_PUB
Source
1 PACKAGE BODY CN_Srp_PayGroup_PUB as
2 -- $Header: cnpspgpb.pls 120.12 2006/10/05 09:59:16 chanthon noship $
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')
432 p_init_msg_list => fnd_api.g_false,
429 THEN
430 cn_srp_paygroup_pub_vuhk.assign_salesreps_pre
431 (p_api_version => p_api_version,
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
562
559 IF cn_srp_paygroup_pub_cuhk.ok_to_generate_msg
560 (p_paygroup_assign_rec => l_paygroup_assign_rec)
561 THEN
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,
662 x_loading_status 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,
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
810 THEN
807 --If it does not exist, raise an error
808 --
809 IF get_pay_group_id_cur%ROWCOUNT <> 1
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);
966
963
964 END IF;
965
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
1096 x_msg_count => x_msg_count,
1093 ( p_api_version => 1.0,
1094 x_return_status => x_return_status,
1095 x_loading_status => x_loading_status,
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 ;
1250 END IF;
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 );
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
1304 BEGIN
1305 -- Standard Start of API savepoint
1306 SAVEPOINT Create_Mass_Asgn_Srp_Pay;
1307 -- Standard call to check for call compatibility.
1308 IF NOT FND_API.compatible_api_call
1309 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
1310 THEN
1311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1312 END IF;
1313 -- Initialize message list if p_init_msg_list is set to TRUE.
1314 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1315 FND_MSG_PUB.initialize;
1316 END IF;
1317 -- Initialize API return status to success
1318 x_return_status := FND_API.G_RET_STS_SUCCESS;
1319 x_loading_status := 'CN_INSERTED';
1320
1321 -- begin API
1322 select pay_group_id, start_date, end_date, org_id
1323 into l_pay_group_id, l_pg_start_date, l_pg_end_date, l_org_id
1324 from cn_role_pay_groups_all
1325 where role_pay_group_id = p_role_pay_group_id;
1326
1327 -- validate org ID
1328 mo_global.validate_orgid_pub_api
1329 (org_id => l_org_id,
1330 status => l_status);
1331
1332 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1333 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1334 'cn.plsql.cn_srp_paygroup_pub.create_mass_asgn_srp_pay.org_validate',
1335 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
1336 end if;
1337
1338 select salesrep_id, start_date, end_date
1339 into l_salesrep_id, l_srp_start_date, l_srp_end_date
1340 from cn_srp_roles
1341 where srp_role_id = p_srp_role_id
1342 AND org_id = l_org_id;
1343
1344 select employee_number, type
1345 into l_emp_num, l_salesrep_type
1346 from cn_salesreps
1347 where salesrep_id = l_salesrep_id
1348 AND org_id = l_org_id;
1349
1350 select name
1351 into l_pay_group_name
1352 from cn_pay_groups_all
1353 where pay_group_id = l_pay_group_id;
1354
1355 l_start_date := NULL;
1356 l_end_date := NULL;
1357
1358 get_masgn_date_intersect( -- Bug fix 5458432. vensrini
1359 p_srp_role_id => p_srp_role_id,
1360 p_role_pay_group_id => p_role_pay_group_id,
1361 x_start_date => l_start_date,
1362 x_end_date => l_end_date);
1363
1364 IF l_start_date IS NOT NULL AND l_end_date IS NOT NULL THEN
1365
1366 select count(*)
1367 into l_count
1368 from cn_srp_pay_groups_all
1369 where salesrep_id = l_salesrep_id
1370 AND org_id = l_org_id
1371 and ((l_start_date between start_date and nvl(end_date,l_null_date))
1372 or (nvl(l_end_date,l_null_date) between
1373 start_date and nvl(end_date,l_null_date)));
1374
1375 IF l_count = 0
1376
1380 newrec.employee_number := l_emp_num;
1377 THEN
1378
1379 newrec.employee_type := l_salesrep_type;
1381 newrec.pay_group_name := l_pay_group_name;
1382 newrec.assignment_start_date := l_start_date;
1383 newrec.assignment_end_date := l_end_date;
1384 newrec.role_pay_group_id := p_role_pay_group_id;
1385 newrec.lock_flag := 'N';
1386
1387 Assign_salesreps
1388 (p_api_version => 1.0,
1389 x_return_status => l_return_status,
1390 x_msg_count => l_msg_count,
1391 x_msg_data => l_msg_data,
1392 p_paygroup_assign_rec => newrec,
1393 x_loading_status => l_loading_status,
1394 x_status => l_status );
1395
1396 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1397 RAISE fnd_api.g_exc_error;
1398 END IF;
1399
1400 l_return_status := FND_API.G_RET_STS_SUCCESS;
1401 x_return_status := l_return_status;
1402 x_loading_status := l_loading_status;
1403 ELSE
1404 null;
1405 END IF;
1406
1407 ELSE
1408 NULL;
1409 END IF;
1410
1411 -- Standard check of p_commit.
1412 IF FND_API.To_Boolean( p_commit ) THEN
1413 COMMIT WORK;
1414 END IF;
1415 -- Standard call to get message count and if count is 1, get message info.
1416 FND_MSG_PUB.Count_And_Get
1417 (
1418 p_count => x_msg_count ,
1419 p_data => x_msg_data ,
1420 p_encoded => FND_API.G_FALSE
1421 );
1422 EXCEPTION
1423 WHEN FND_API.G_EXC_ERROR THEN
1424 ROLLBACK TO Create_Mass_Asgn_Srp_Pay;
1425 x_return_status := FND_API.G_RET_STS_ERROR ;
1426 FND_MSG_PUB.Count_And_Get
1427 (
1428 p_count => x_msg_count ,
1429 p_data => x_msg_data ,
1430 p_encoded => FND_API.G_FALSE
1431 );
1432 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1433 ROLLBACK TO Create_Mass_Asgn_Srp_Pay;
1434 x_loading_status := 'UNEXPECTED_ERR';
1435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1436 FND_MSG_PUB.Count_And_Get
1437 (
1438 p_count => x_msg_count ,
1439 p_data => x_msg_data ,
1440 p_encoded => FND_API.G_FALSE
1441 );
1442 WHEN OTHERS THEN
1443 ROLLBACK TO Create_Mass_Asgn_Srp_Pay;
1444 x_loading_status := 'UNEXPECTED_ERR';
1445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1446 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1447 THEN
1448 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1449 END IF;
1450 FND_MSG_PUB.Count_And_Get
1451 (
1452 p_count => x_msg_count ,
1453 p_data => x_msg_data ,
1454 p_encoded => FND_API.G_FALSE
1455 );
1456
1457 END Create_Mass_Asgn_Srp_Pay;
1458
1459 -- --------------------------------------------------------------------------*
1460 -- Procedure: Update_Mass_Asgn_Srp_Pay
1461 -- --------------------------------------------------------------------------*
1462
1463 PROCEDURE Update_Mass_Asgn_Srp_Pay
1464 (
1465 p_api_version IN NUMBER,
1466 p_init_msg_list IN VARCHAR2,
1467 p_commit IN VARCHAR2,
1468 p_validation_level IN NUMBER,
1469 x_return_status OUT NOCOPY VARCHAR2,
1470 x_msg_count OUT NOCOPY NUMBER,
1471 x_msg_data OUT NOCOPY VARCHAR2,
1472 p_srp_role_id IN NUMBER,
1473 p_role_pay_group_id IN NUMBER,
1474 x_srp_pay_group_id OUT NOCOPY NUMBER,
1475 x_loading_status OUT NOCOPY VARCHAR2
1476 ) IS
1477
1478 l_return_status VARCHAR2(2000);
1479 l_msg_count NUMBER;
1480 l_msg_data VARCHAR2(2000);
1481 l_srp_pmt_plan_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
1482 l_loading_status VARCHAR2(2000);
1483 l_status VARCHAR2(2000);
1484 l_lock_flag cn_srp_pay_groups.lock_flag%TYPE;
1485 l_api_name CONSTANT VARCHAR2(30) := 'Update_Mass_Asgn_Srp_Pay';
1486 l_api_version CONSTANT NUMBER := 1.0;
1487 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
1488 l_count NUMBER;
1489 l_count_pay NUMBER;
1490 l_count_role NUMBER;
1491 l_count_srp_pay_group NUMBER;
1492 l_salesrep_type_old cn_salesreps.type%TYPE;
1493 l_salesrep_id_old cn_salesreps.salesrep_id%TYPE;
1494 l_emp_num_old cn_salesreps.employee_number%TYPE;
1495 l_pay_group_name_old cn_pay_groups.name%TYPE;
1496 l_pay_group_id_old cn_pay_groups.pay_group_id%TYPE;
1497 l_role_id_old cn_roles.role_id%TYPE;
1498 l_start_date_old cn_srp_pay_groups.start_date%TYPE;
1499 l_end_date_old cn_srp_pay_groups.start_date%TYPE;
1500 l_ovn_old cn_srp_pay_groups.object_version_number%TYPE;
1501 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
1502 l_org_id cn_srp_pay_groups.org_id%TYPE;
1503
1504 newrec CN_SRP_PAYGROUP_PUB.PayGroup_assign_rec;
1505 oldrec CN_SRP_PAYGROUP_PUB.PayGroup_assign_rec;
1506
1510 l_emp_num_new cn_salesreps.employee_number%TYPE;
1507 delrec CN_Srp_PayGroup_PVT.PayGroup_assign_rec;
1508 l_salesrep_type_new cn_salesreps.type%TYPE;
1509 l_salesrep_id_new cn_salesreps.salesrep_id%TYPE;
1511 l_pay_group_name_new cn_pay_groups.name%TYPE;
1512 l_pay_group_id_new cn_pay_groups.pay_group_id%TYPE;
1513 l_pp_start_date_new cn_pay_groups.start_date%TYPE;
1514 l_pp_end_date_new cn_pay_groups.end_date%TYPE;
1515 l_srp_start_date_new cn_srp_roles.start_date%TYPE;
1516 l_srp_end_date_new cn_srp_roles.end_date%TYPE;
1517 l_start_date_new cn_srp_pay_groups.start_date%TYPE;
1518 l_end_date_new cn_srp_pay_groups.start_date%TYPE;
1519
1520
1521 --changed the cursor to get proper srp-pay_group assignment to be updated--Hanaraya
1522 CURSOR get_pay_groups
1523 (l_salesrep_id_old NUMBER,
1524 p_role_pay_group_id NUMBER) IS
1525 select srp_pay_group_id,pay_group_id, start_date, end_date,object_version_number,lock_flag
1526 from cn_srp_pay_groups sp
1527 where salesrep_id = l_salesrep_id_old
1528 AND role_pay_group_id = p_role_pay_group_id
1529 AND NOT EXISTS
1530 ( Select 1 from cn_srp_roles sr, cn_role_pay_groups rp
1531 Where salesrep_id = l_salesrep_id_old
1532 AND role_pay_group_id = p_role_pay_group_id
1533 AND sr.role_id = rp.role_id
1534 AND sr.org_id = rp.org_id
1535 AND greatest(sr.start_date,rp.start_date) = sp.start_date
1536 AND least(nvl(sr.end_date,l_null_date),nvl(rp.end_date,l_null_date))
1537 = nvl(sp.end_date,l_null_date)
1538 );
1539
1540 BEGIN
1541 -- Standard Start of API savepoint
1542 SAVEPOINT Update_Mass_Asgn_Srp_Pay;
1543 -- Standard call to check for call compatibility.
1544 IF NOT FND_API.compatible_api_call
1545 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
1546 THEN
1547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1548 END IF;
1549 -- Initialize message list if p_init_msg_list is set to TRUE.
1550 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1551 FND_MSG_PUB.initialize;
1552 END IF;
1553 -- Initialize API return status to success
1554 x_return_status := FND_API.G_RET_STS_SUCCESS;
1555 x_loading_status := 'CN_UPDATED';
1556
1557 -- begin API
1558 SELECT org_id
1559 INTO l_org_id
1560 FROM cn_role_pay_groups_all
1561 WHERE role_pay_group_id = p_role_pay_group_id;
1562
1563 -- validate org ID
1564 mo_global.validate_orgid_pub_api
1565 (org_id => l_org_id,
1566 status => l_status);
1567
1568 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1569 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1570 'cn.plsql.cn_srp_paygroup_pub.update_mass_asgn_srp_pay.org_validate',
1571 'Validated org_id = ' || l_org_id || ' status = ' || l_status);
1572 end if;
1573
1574 SELECT salesrep_id,role_id,start_date, end_date
1575 INTO l_salesrep_id_old,l_role_id_old,l_srp_start_date_new, l_srp_end_date_new
1576 FROM cn_srp_roles
1577 WHERE srp_role_id = p_srp_role_id
1578 AND org_id = l_org_id;
1579
1580 SELECT count(*)
1581 INTO l_count
1582 FROM cn_srp_pay_groups_all
1583 WHERE salesrep_id = l_salesrep_id_old
1584 AND role_pay_group_id = p_role_pay_group_id;
1585
1586 IF (l_count <> 0)
1587 THEN
1588 FOR paygroup IN get_pay_groups(l_salesrep_id_old, p_role_pay_group_id) LOOP
1589 l_pay_group_id_old := paygroup.pay_group_id;
1590 l_start_date_old := paygroup.start_date;
1591 l_end_date_old := paygroup.end_date;
1592 l_ovn_old := paygroup.object_version_number;
1593 l_lock_flag := paygroup.lock_flag;
1594
1595 /* commented out validation for bug 5018892 - it is performed later
1596 SELECT count(*) into l_count_pay
1597 FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
1598 cn_payruns_all prun
1599 WHERE w.salesrep_id = l_salesrep_id_old
1600 AND w.org_id = l_org_id
1601 AND prun.pay_period_id = prd.period_id
1602 AND prun.payrun_id = w.payrun_id
1603 AND prun.pay_group_id = l_pay_group_id_old
1604 AND prd.org_id = l_org_id
1605 AND ((prd.start_date BETWEEN l_start_date_old AND nvl(l_end_date_old,l_null_date)) OR
1606 (prd.end_date between l_start_date_old AND nvl(l_end_date_old,l_null_date)) );
1607
1608
1609 IF l_count_pay > 0
1610 THEN
1611 RAISE FND_API.G_EXC_ERROR;
1612 END IF;
1613 */
1614
1615 SELECT employee_number, type
1616 INTO l_emp_num_old, l_salesrep_type_old
1617 FROM cn_salesreps
1618 WHERE salesrep_id = l_salesrep_id_old
1619 AND org_id = l_org_id;
1620
1621 SELECT name
1622 INTO l_pay_group_name_old
1623 FROM cn_pay_groups_all
1624 WHERE pay_group_id = l_pay_group_id_old;
1625
1626 oldrec.employee_type := l_salesrep_type_old;
1627 oldrec.employee_number := l_emp_num_old;
1628 delrec.salesrep_id := l_salesrep_id_old;
1632 delrec.org_id := l_org_id;
1629 oldrec.pay_group_name := l_pay_group_name_old;
1630 delrec.pay_group_id := l_pay_group_id_old;
1631 oldrec.org_id := l_org_id;
1633 oldrec.assignment_start_date := l_start_date_old;
1634 delrec.assignment_start_date := l_start_date_old;
1635 oldrec.assignment_end_date := l_end_date_old;
1636 delrec.assignment_end_date := l_end_date_old;
1637 oldrec.role_pay_group_id := p_role_pay_group_id;
1638 delrec.role_pay_group_id := p_role_pay_group_id;
1639
1640 end loop;
1641 END IF;
1642
1643 SELECT salesrep_id, start_date, end_date
1644 INTO l_salesrep_id_new, l_srp_start_date_new, l_srp_end_date_new
1645 FROM cn_srp_roles
1646 WHERE srp_role_id = p_srp_role_id
1647 AND org_id = l_org_id;
1648
1649 SELECT employee_number, type
1650 INTO l_emp_num_new, l_salesrep_type_new
1651 FROM cn_salesreps
1652 WHERE salesrep_id = l_salesrep_id_new
1653 AND org_id = l_org_id;
1654
1655 SELECT pay_group_id, start_date, end_date
1656 INTO l_pay_group_id_new, l_pp_start_date_new, l_pp_end_date_new
1657 FROM cn_role_pay_groups_all
1658 WHERE role_pay_group_id = p_role_pay_group_id;
1659
1660 SELECT name
1661 INTO l_pay_group_name_new
1662 FROM cn_pay_groups_all
1663 WHERE pay_group_id = l_pay_group_id_new;
1664
1665 SELECT count(*)
1666 INTO l_count_srp_pay_group
1667 FROM cn_srp_pay_groups_all
1668 WHERE salesrep_id=l_salesrep_id_old
1669 AND org_id = l_org_id
1670 AND ((l_start_date_old between start_date and nvl(end_date,l_null_date))
1671 OR (l_end_date_old between start_date and nvl(end_date,l_null_date)));
1672
1673 l_start_date_new := NULL;
1674 l_end_date_new := NULL;
1675
1676 IF (l_lock_flag = 'N' or l_count=0)
1677 THEN
1678 get_masgn_date_intersect( -- Bug fix 5458432
1679 p_srp_role_id => p_srp_role_id,
1680 p_role_pay_group_id => p_role_pay_group_id,
1681 x_start_date => l_start_date_new,
1682 x_end_date => l_end_date_new);
1683
1684 IF l_start_date_new IS NOT NULL AND l_end_date_new IS NOT NULL THEN
1685 newrec.employee_type := l_salesrep_type_new;
1686 newrec.employee_number := l_emp_num_new;
1687 newrec.pay_group_name := l_pay_group_name_new;
1688 newrec.org_id := l_org_id;
1689 newrec.assignment_start_date := l_start_date_new;
1690 newrec.assignment_end_date := l_end_date_new;
1691 newrec.lock_flag :='N';
1692 newrec.role_pay_group_id := p_role_pay_group_id;
1693
1694 IF (l_count > 0 )
1695 THEN
1696 Update_srp_Assignment
1697 (p_api_version => 1.0,
1698 x_return_status => l_return_status,
1699 x_msg_count => l_msg_count,
1700 x_msg_data => l_msg_data,
1701 p_old_paygroup_assign_rec => oldrec,
1702 p_paygroup_assign_rec => newrec,
1703 p_ovn => l_ovn_old ,
1704 x_loading_status => l_loading_status,
1705 x_status => l_status );
1706
1707 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1708 RAISE fnd_api.g_exc_error;
1709 END IF;
1710
1711 l_return_status := FND_API.G_RET_STS_SUCCESS;
1712
1713 IF l_loading_status = 'CN_INVALID_SRP_PGRP_ASGN_DT' THEN
1714 cn_srp_paygroup_pvt.delete_srp_pay_group
1715 (
1716 p_api_version => 1.0,
1717 x_return_status => l_return_status,
1718 x_loading_status => l_loading_status,
1719 x_msg_count => l_msg_count,
1720 x_msg_data => l_msg_data,
1721 p_paygroup_assign_rec => delrec
1722 );
1723
1724 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1725 RAISE fnd_api.g_exc_error;
1726 END IF;
1727 END IF;
1728
1729 l_return_status := FND_API.G_RET_STS_SUCCESS;
1730 x_return_status := l_return_status;
1731 x_loading_status := l_loading_status;
1732 ELSIF (l_count_srp_pay_group = 0 )
1733 THEN
1734
1735 SELECT count(*)
1736 INTO l_count_srp_pay_group
1737 FROM cn_srp_pay_groups_all
1738 WHERE salesrep_id=l_salesrep_id_old
1739 AND org_id = l_org_id
1740 AND ((l_start_date_new between start_date and nvl(end_date,l_null_date))
1741 OR (l_end_date_new between start_date and nvl(end_date,l_null_date)));
1742
1743 IF (l_count_srp_pay_group = 0) THEN
1744
1745 Assign_salesreps
1746 (p_api_version => 1.0,
1747 x_return_status => l_return_status,
1748 x_msg_count => l_msg_count,
1749 x_msg_data => l_msg_data,
1750 p_paygroup_assign_rec=> newrec,
1751 x_loading_status => l_loading_status,
1752 x_status => l_status );
1753
1754 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1755 RAISE fnd_api.g_exc_error;
1756 END IF;
1757 END IF;
1758 l_return_status := FND_API.G_RET_STS_SUCCESS;
1759 x_return_status := l_return_status;
1760 x_loading_status := l_loading_status;
1761
1762 END IF;
1763
1764 ELSIF l_count <> 0 THEN
1765 cn_srp_paygroup_pvt.delete_srp_pay_group
1766 (
1767 p_api_version => 1.0,
1768 x_return_status => l_return_status,
1769 x_loading_status => l_loading_status,
1770 x_msg_count => l_msg_count,
1771 x_msg_data => l_msg_data,
1772 p_paygroup_assign_rec=> delrec
1773
1774 );
1775
1776 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1777 RAISE fnd_api.g_exc_error;
1778 END IF;
1779
1780 l_return_status:=FND_API.G_RET_STS_SUCCESS;
1781
1782 END IF;
1783 ELSE
1784 NULL;
1785
1786 END IF;
1787
1788 -- Standard check of p_commit.
1789 IF FND_API.To_Boolean( p_commit ) THEN
1790 COMMIT WORK;
1791 END IF;
1792 -- Standard call to get message count and if count is 1, get message info.
1793 FND_MSG_PUB.Count_And_Get
1794 (
1795 p_count => x_msg_count ,
1796 p_data => x_msg_data ,
1797 p_encoded => FND_API.G_FALSE
1798 );
1799
1800 EXCEPTION
1801 WHEN FND_API.G_EXC_ERROR THEN
1802 ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
1803 x_return_status := FND_API.G_RET_STS_ERROR ;
1804 FND_MSG_PUB.Count_And_Get
1805 (
1806 p_count => x_msg_count ,
1807 p_data => x_msg_data ,
1808 p_encoded => FND_API.G_FALSE
1809 );
1810 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1811 ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
1812 x_loading_status := 'UNEXPECTED_ERR';
1813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1814 FND_MSG_PUB.Count_And_Get
1815 (
1816 p_count => x_msg_count ,
1817 p_data => x_msg_data ,
1818 p_encoded => FND_API.G_FALSE
1819 );
1820 WHEN OTHERS THEN
1821 ROLLBACK TO Update_Mass_Asgn_Srp_Pay;
1822 x_loading_status := 'UNEXPECTED_ERR';
1823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1824 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1825 THEN
1826 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1827 END IF;
1828 FND_MSG_PUB.Count_And_Get
1829 (
1830 p_count => x_msg_count ,
1831 p_data => x_msg_data ,
1832 p_encoded => FND_API.G_FALSE
1833 );
1834
1835 End Update_Mass_Asgn_Srp_Pay;
1836
1837 END CN_Srp_PayGroup_PUB ;