[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_ASSIGNS_PKG
Source
1 PACKAGE BODY CN_QUOTA_ASSIGNS_PKG as
2 /* $Header: cnpliqab.pls 120.2 2005/07/05 09:25:19 appldev ship $ */
3
4 /*
5
6 Date Name Description
7 ---------------------------------------------------------------------------+
8 15-FEB-95 P Cook Unit tested
9 13-JUL-95 P Cook Added lock_record procedure
10 17-JUL-95 P Cook Do no raise exception if no srp records found when
11 updating a quota
12 28-JUL-95 P Cook Split up delete_record to use quota_assign index.
13 Only try to delete srp records if a quota assignment
14 record was deleted.
15
16 */
17
18 /* -------------------------------------------------------------------------
19 | Variables |
20 --------------------------------------------------------------------------*/
21
22 -- All srp plan assigns using this comp plan id
23 CURSOR reps (x_comp_plan_id NUMBER) IS
24 SELECT srp_plan_assign_id, salesrep_id, role_id, start_date, end_date
25 FROM cn_srp_plan_assigns
26 WHERE comp_plan_id = x_comp_plan_id;
27
28 rep_rec reps%ROWTYPE;
29
30 /* -------------------------------------------------------------------------
31 | Private Routines |
32 --------------------------------------------------------------------------*/
33
34 -- Name
35 --
36 -- Purpose
37 --
38 -- Notes
39 --
40 --
41 PROCEDURE get_uid (X_Quota_Assign_Id IN OUT NOCOPY NUMBER ) IS
42 BEGIN
43
44 SELECT cn_quota_assigns_s.nextval
45 INTO X_Quota_Assign_Id
46 FROM sys.dual;
47
48 END get_uid;
49
50 -- Name
51 --
52 -- Purpose
53 --
54 -- Notes
55 --
56 --
57 PROCEDURE Insert_Record( X_Quota_Id NUMBER
58 ,X_Comp_Plan_Id NUMBER
59 ,X_Quota_Assign_Id IN OUT NOCOPY NUMBER
60 ,X_Quota_Sequence NUMBER
61 ,X_ORG_ID NUMBER) IS
62
63 l_name cn_comp_plans.name%TYPE;
64 l_start_date DATE;
65 l_end_date DATE;
66 l_null_date CONSTANT DATE := to_date('31-12-3000','DD-MM-YYYY');
67 l_loading_status varchar2(30);
68 l_msg_count number;
69 l_msg_data varchar2(240);
70 l_return_status varchar2(1);
71
72 CURSOR pg_cur(srp_id number)
73 IS
74 select start_date, end_date
75 from cn_srp_pay_groups
76 where salesrep_id = srp_id;
77
78 pg_cur_rec pg_cur%ROWTYPE;
79
80 BEGIN
81
82 Get_Uid(X_Quota_Assign_Id);
83
84 -- If we change the assignments in any way we must immediately make
85 -- the plan 'incomplete'. If we rely on a db hit the form plan record
86 -- does not get updated since the status and complete_flag
87 -- fields are not used as OUT parameters. while its underlying db record
88 -- has changed.
89 -- must be called aftere the unique checks to ensure the plan
90 -- status is not updated even though the quota in/upd cannot be made.
91
92 cn_quota_assigns_pkg.check_exists(x_quota_id);
93
94 cn_comp_plans_pkg.set_status( x_comp_plan_id => x_comp_plan_id
95 ,x_quota_id => null
96 ,x_rate_schedule_id => null
97 ,x_status_code => 'INCOMPLETE'
98 ,x_event => 'CHANGE_COMP_PLAN');
99
100
101 INSERT INTO cn_quota_assigns
102 (
103 Quota_Id
104 ,Comp_Plan_Id
105 ,Quota_Assign_Id
106 ,Quota_Sequence
107 ,created_by
108 ,creation_date
109 ,last_updated_by
110 ,last_update_date
111 ,last_update_login
112 ,object_version_number
113 ,org_id)
114 VALUES
115 (
116 X_Quota_Id
117 ,X_Comp_Plan_Id
118 ,X_Quota_Assign_Id
119 ,X_Quota_Sequence
120 ,fnd_global.user_id
121 ,sysdate
122 ,fnd_global.user_id
123 ,sysdate
124 ,fnd_global.login_id
125 ,0
126 ,X_ORG_ID );
127
128 FOR rep_rec IN reps(x_comp_plan_id) LOOP
129 cn_srp_quota_assigns_pkg.insert_record
130 ( x_srp_plan_assign_id => rep_rec.srp_plan_assign_id
131 ,x_quota_id => x_quota_id);
132
133 -- create srp periods as necessary
134 FOR pg_cur_rec IN pg_cur(rep_rec.salesrep_id) LOOP
135 IF(pg_cur_rec.start_date <= rep_rec.start_date) THEN
136 l_start_date := rep_rec.start_date;
137 ELSE
138 l_start_date := pg_cur_rec.start_date;
139 END IF;
140
141 IF(nvl(pg_cur_rec.end_date,l_null_date) >=
142 nvl(rep_rec.end_date,l_null_date)) THEN
143 l_end_date := rep_rec.end_date;
144 ELSE
145 l_end_date := pg_cur_rec.end_date;
146 END IF;
147
148 IF l_start_date <= nvl(l_end_date, l_null_date) THEN
149 -- Create entry in cn_srp_periods
150 CN_SRP_PERIODS_PVT.Create_Srp_Periods_Per_Quota
151 (p_api_version => 1.0,
152 x_return_status => l_return_status,
153 x_msg_count => l_msg_count,
154 x_msg_data => l_msg_data,
155 p_role_id => rep_rec.role_id,
156 p_comp_plan_id => x_comp_plan_id,
157 p_quota_id => x_quota_id,
158 p_salesrep_id => rep_rec.salesrep_id,
159 p_start_date => l_start_date,
160 p_end_date => l_end_date,
161 p_sync_flag => fnd_api.g_false,
162 x_loading_status => l_loading_status
163 );
164 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
165 RAISE FND_API.G_EXC_ERROR ;
166 END IF;
167 END IF;
168 END LOOP;
169 END LOOP;
170
171 BEGIN
172
173 SELECT name, start_date, end_date
174 INTO l_name, l_start_date, l_end_date
175 FROM cn_comp_plans
176 WHERE comp_plan_id = x_comp_plan_id;
177 EXCEPTION
178 WHEN no_data_found THEN
179 l_name := NULL;
180 END ;
181 cn_mark_events_pkg.mark_event_comp_plan
182 ( p_event_name => 'CHANGE_COMP_PLAN'
183 ,p_object_name => l_name
184 ,p_object_id => x_comp_plan_id
185 ,p_start_date => NULL
186 ,p_end_date => NULL
187 ,p_start_date_old => l_start_date
188 ,p_end_date_old => l_end_date
189 ,p_org_id => X_ORG_ID);
190
191
192 END Insert_Record;
193
194 -- Name
195 --
196 -- Purpose
197 --
198 -- Notes
199 --
200 --
201
202 PROCEDURE Update_Record( X_Quota_Id NUMBER
203 ,X_Comp_Plan_Id NUMBER
204 ,X_Quota_Assign_Id NUMBER
205 ,X_Quota_Sequence NUMBER
206 ,x_quota_id_old VARCHAR2
207 ,X_ORG_ID NUMBER) IS
208
209 l_start_date DATE;
210 l_end_date DATE;
211 l_null_date CONSTANT DATE := to_date('31-12-3000','DD-MM-YYYY');
212 l_loading_status varchar2(30);
213 l_msg_count number;
214 l_msg_data varchar2(240);
215 l_return_status varchar2(1);
216
217 CURSOR pg_cur(srp_id number)
218 IS
219 select start_date, end_date
220 from cn_srp_pay_groups
221 where salesrep_id = srp_id;
222
223 pg_cur_rec pg_cur%ROWTYPE;
224
225 BEGIN
226
227 IF (x_quota_id <> x_quota_id_old ) THEN
228
229 cn_quota_assigns_pkg.check_exists(x_quota_id);
230
231 cn_comp_plans_pkg.set_status(
232 x_comp_plan_id => x_comp_plan_id
233 ,x_quota_id => null
234 ,x_rate_schedule_id => null
235 ,x_status_code => 'INCOMPLETE'
236 ,x_event => 'CHANGE_COMP_PLAN' );
237
238 END IF;
239
240 BEGIN
241 UPDATE cn_quota_assigns
242 SET quota_id = x_quota_id
243 ,comp_plan_id = x_comp_plan_id
244 ,quota_sequence = X_Quota_Sequence
245 ,last_updated_by = fnd_global.user_id
246 ,last_update_date = sysdate
247 ,last_update_login = fnd_global.login_id
248 ,object_version_number = object_version_number + 1
249 WHERE quota_assign_id = x_quota_assign_id;
250
251 IF (SQL%NOTFOUND) THEN
252 raise no_data_found;
253 END IF;
254
255 END;
256
257 IF x_quota_id <> x_quota_id_old THEN
258 FOR rep_rec IN reps(x_comp_plan_id) LOOP
259 cn_srp_quota_assigns_pkg.delete_record(
260 x_srp_plan_assign_id => rep_rec.srp_plan_assign_id
261 ,x_quota_id => x_quota_id_old);
262
263 cn_srp_quota_assigns_pkg.insert_record(
264 x_srp_plan_assign_id => rep_rec.srp_plan_assign_id
265 ,x_quota_id => x_quota_id);
266
267 -- create srp periods as necessary
268 FOR pg_cur_rec IN pg_cur(rep_rec.salesrep_id) LOOP
269 IF(pg_cur_rec.start_date <= rep_rec.start_date) THEN
270 l_start_date := rep_rec.start_date;
271 ELSE
272 l_start_date := pg_cur_rec.start_date;
273 END IF;
274
275 IF(nvl(pg_cur_rec.end_date,l_null_date) >=
276 nvl(rep_rec.end_date,l_null_date)) THEN
277 l_end_date := rep_rec.end_date;
278 ELSE
279 l_end_date := pg_cur_rec.end_date;
280 END IF;
281
282 IF l_start_date <= nvl(l_end_date, l_null_date) THEN
283 -- Create entry in cn_srp_periods
284 CN_SRP_PERIODS_PVT.Create_Srp_Periods_Per_Quota
285 (p_api_version => 1.0,
286 x_return_status => l_return_status,
287 x_msg_count => l_msg_count,
288 x_msg_data => l_msg_data,
289 p_role_id => rep_rec.role_id,
290 p_comp_plan_id => x_comp_plan_id,
291 p_quota_id => x_quota_id,
292 p_salesrep_id => rep_rec.salesrep_id,
293 p_start_date => l_start_date,
294 p_end_date => l_end_date,
295 p_sync_flag => fnd_api.g_false,
296 x_loading_status => l_loading_status
297 );
298 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
299 RAISE FND_API.G_EXC_ERROR ;
300 END IF;
301 END IF;
302 END LOOP;
303 END LOOP;
304 END IF;
305
306 END Update_Record;
307
308 -- Name
309 --
310 -- Purpose
311 --
312 -- Notes
313 --
314 --
315
316 PROCEDURE Delete_Record( X_Quota_Assign_Id NUMBER
317 ,X_Comp_Plan_Id NUMBER
318 ,x_quota_id NUMBER) IS
319
320 l_name cn_comp_plans.name%TYPE;
321 l_start_date DATE;
322 l_end_date DATE;
323 l_quota_id NUMBER;
324 l_org_id NUMBER;
325
326
327 CURSOR get_quota_id_for_mark IS
328 Select quota_id
329 from cn_quota_assigns
330 where comp_plan_id = nvl(x_comp_plan_id, comp_plan_id )
331 and quota_assign_id = nvl(x_quota_assign_id, quota_assign_id) ;
332
333 BEGIN
334
335 if x_quota_id is NULL then
336
337 open get_quota_id_for_mark;
338 fetch get_quota_id_for_mark into l_quota_id ;
339 close get_quota_id_for_mark;
340
341 end if;
342
343 cn_comp_plans_pkg.set_status( x_comp_plan_id => x_comp_plan_id
344 ,x_quota_id => null
345 ,x_rate_schedule_id => null
346 ,x_status_code => 'INCOMPLETE'
347 ,x_event => 'CHANGE_COMP_PLAN');
348
349 BEGIN
350 IF x_quota_assign_id IS NULL THEN
351
352 DELETE FROM cn_quota_assigns
353 WHERE comp_plan_id = x_comp_plan_id;
354
355 ELSE
356 DELETE FROM cn_quota_assigns
357 WHERE quota_assign_id = x_quota_assign_id
358 AND comp_plan_id = x_comp_plan_id;
359
360 END IF;
361
362 IF SQL%FOUND THEN
363
364 BEGIN
365
366 SELECT name, start_date, end_date
367 INTO l_name, l_start_date, l_end_date
368 FROM cn_comp_plans
369 WHERE comp_plan_id = x_comp_plan_id;
370 EXCEPTION
371 WHEN no_data_found THEN
372 l_name := NULL;
373 END ;
374
375 select org_id into l_org_id from cn_comp_plans
376 where comp_plan_id = x_comp_plan_id;
377
378 cn_mark_events_pkg.mark_event_comp_plan
379 ( p_event_name => 'CHANGE_COMP_PLAN'
380 ,p_object_name => l_name
381 ,p_object_id => x_comp_plan_id
382 ,p_start_date => NULL
383 ,p_end_date => NULL
384 ,p_start_date_old => l_start_date
385 ,p_end_date_old => l_end_date,
386 p_org_id => l_org_id);
387
388 FOR rep_rec IN reps(x_comp_plan_id) LOOP
389 cn_srp_quota_assigns_pkg.delete_record
390 ( x_srp_plan_assign_id => rep_rec.srp_plan_assign_id
391 ,x_quota_id => x_quota_id);
392 END LOOP;
393
394 END IF;
395 END;
396
397 END Delete_Record;
398
399 -- Name
400 --
401 -- Purpose
402 --
403 -- Notes
404 --
405 --
406
407 PROCEDURE lock_record( x_quota_assign_Id NUMBER
408 ,x_quota_id NUMBER) IS
409 CURSOR c IS
410 SELECT *
411 FROM cn_quota_assigns
412 WHERE quota_assign_id = x_quota_assign_id
413 FOR UPDATE OF quota_assign_id NOWAIT;
414 recinfo c%ROWTYPE;
415
416
417 BEGIN
418 OPEN C;
419 FETCH C INTO Recinfo;
420 if (C%NOTFOUND) then
421 CLOSE C;
422 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
423 APP_EXCEPTION.Raise_Exception;
424 end if;
425 CLOSE C;
426
427 if ( (Recinfo.quota_id = X_quota_id) ) THEN
428 return;
429 else
430 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
431 APP_EXCEPTION.Raise_Exception;
432 end if;
433 END lock_record;
434 /* ------------------------------------------------------------------------
435 | Public Routine Bodies |
436 --------------------------------------------------------------------------*/
437
438 -- Name
439 --
440 -- Purpose
441 --
442 -- Notes
443 --
444 --
445 PROCEDURE Begin_Record
446 ( X_Operation VARCHAR2
447 ,X_Quota_Id NUMBER
448 ,X_Comp_Plan_Id NUMBER
449 ,X_Quota_Assign_Id IN OUT NOCOPY NUMBER
450 ,X_Quota_Sequence NUMBER
451 ,x_quota_id_old NUMBER
452 ,X_ORG_ID NUMBER) IS
453 BEGIN
454 IF X_Operation = 'INSERT' THEN
455 Insert_record ( X_Quota_Id
456 ,X_Comp_Plan_Id
457 ,X_Quota_Assign_Id
458 ,X_Quota_Sequence
459 ,X_ORG_ID );
460
461 ELSIF X_Operation = 'UPDATE' THEN
462 Update_record ( X_Quota_Id
463 ,X_Comp_Plan_Id
464 ,X_Quota_Assign_Id
465 ,X_Quota_Sequence
466 ,x_quota_id_old
467 ,X_ORG_ID );
468
469 ELSIF X_Operation = 'DELETE' THEN
470 Delete_Record ( X_Quota_Assign_Id
471 ,X_Comp_Plan_Id
472 ,x_quota_id);
473
474 ELSIF X_Operation = 'LOCK' THEN
475 lock_Record ( x_quota_assign_Id
476 ,x_quota_id);
477
478 END IF;
479
480 END Begin_Record;
481
482 --
483 -- Procedure Name
484 -- get_quota_info
485 -- Purpose
486 --
487 --
488
489 PROCEDURE get_quota_info( X_quota_id IN NUMBER
490 ,X_name IN OUT NOCOPY VARCHAR2
491 ,x_quota_type_code IN OUT NOCOPY VARCHAR2) IS
492 BEGIN
493
494 IF X_quota_id IS NOT NULL THEN
495
496 SELECT name
497 ,quota_type_code
498 INTO x_name
499 ,x_quota_type_code
500 FROM cn_quotas
501 WHERE quota_id = X_quota_id
502 ;
503
504 END IF;
505
506 EXCEPTION
507 WHEN no_data_found THEN
508 RAISE no_data_found;
509
510 END get_quota_info;
511
512 -- Name
513 --
514 -- Purpose
515 -- check that the quota exists before you commit the assignment
516 -- Notes
517 --
518 --
519
520 PROCEDURE Check_exists( X_Quota_Id NUMBER) IS
521 X_Dummy NUMBER;
522 BEGIN
523 SELECT 1 INTO X_dummy FROM sys.dual
524 WHERE EXISTS ( SELECT 1
525 FROM cn_quotas
526 WHERE quota_id = X_quota_id)
527 ;
528
529 EXCEPTION
530 WHEN no_data_found THEN
531 fnd_message.Set_Name('CN', 'PLN_QUOTA_DELETED');
532 app_exception.Raise_Exception;
533
534 END Check_exists;
535
536 -- Name
537 --
538 -- Purpose
539 --
540 -- Notes
541 --
542 --
543
544 PROCEDURE Check_duplicate( x_quota_id NUMBER
545 ,x_quota_assign_id NUMBER
546 ,x_comp_plan_id NUMBER) IS
547 X_Dummy NUMBER;
548
549 BEGIN
550 SELECT 1
551 INTO x_dummy
552 FROM sys.dual
553 WHERE NOT EXISTS (
554 SELECT 1
555 FROM cn_quota_assigns
556 WHERE quota_id = x_quota_id
557 AND comp_plan_id = x_comp_plan_id
558 AND ( x_quota_assign_id IS NULL
559 OR quota_assign_id <> x_quota_assign_id))
560 ;
561
562 EXCEPTION
563 WHEN no_data_found THEN
564 fnd_message.Set_Name('CN', 'PLN_QUOTA_ASSIGNED');
565 app_exception.Raise_Exception;
566
567 END Check_duplicate;
568
569 END CN_QUOTA_ASSIGNS_PKG;