1 PACKAGE BODY cn_srp_quota_rules_pkg as
2 /* $Header: cnsrpqrb.pls 120.1 2005/06/09 11:52:49 appldev $ */
3
4 /*
5 Date Name Description
6 ---------------------------------------------------------------------------+
7 24-JUL-95 P Cook Prevent insert of quota rules for 'manual' and 'draw'
8 quota types
9 28-AUG-95 P Cook Bug: 304207. Default the srp quota rule targets from
10 the source quota rule.
11 10-JUN-99 S Kumar Modified the where condition if all the Quota types
12 noew we have only formula, external quota types.
13
14 25-AUG-99 S Kumar Added more procedure to handle the locks and
15 modified the update_record with more parameters
16 like srp_quota_rule_id, using this you can update
17 record from forms.
18
19 */
20 ---------------------------------------------------------------------------+
21 -- PROCEDURE SYNCH_TARGET
22 ---------------------------------------------------------------------------+
23 PROCEDURE synch_target (x_srp_quota_assign_id NUMBER) IS
24
25 l_target NUMBER;
26 l_payment_amount NUMBER;
27 l_performance_goal NUMBER;
28
29 CURSOR sqr_curs IS
30 SELECT qr.target,
31 qr.payment_amount,
32 qr.performance_goal,
33 sqr.srp_quota_rule_id
34 FROM cn_quota_rules_all qr,
35 cn_srp_quota_rules_all sqr
36 WHERE qr.quota_rule_id = sqr.quota_rule_id
37 AND sqr.srp_quota_assign_id = x_srp_quota_assign_id
38 ;
39 l_recinfo sqr_curs%ROWTYPE;
40
41 BEGIN
42 IF x_srp_quota_assign_id IS NOT NULL THEN
43
44 OPEN sqr_curs;
45 LOOP
46 FETCH sqr_curs INTO l_recinfo;
47 EXIT WHEN sqr_curs%notfound;
48
49 UPDATE cn_srp_quota_rules_all
50 SET target = l_recinfo.target,
51 payment_amount = l_recinfo.payment_amount,
52 performance_goal = l_recinfo.performance_goal
53 WHERE srp_quota_rule_id = l_recinfo. srp_quota_rule_id
54 ;
55 END LOOP;
56 CLOSE sqr_curs;
57 END IF;
58
59 END synch_target;
60
61 ---------------------------------------------------------------------------+
62 -- PROCEDURE UPDATE_RECORD
63 -- Descr: This program will be called from cn_quota_rules, when the user
64 -- modifiy the target, payment_amount, performance goal
65 -- Case1: x_quota_rule_id is NOT NULL when called from cn_quota_rules
66 -- Case2: x_srp_quota_rule_id IS NOT NULL FROM forms Only.
67 ---------------------------------------------------------------------------+
68 PROCEDURE update_record ( x_quota_rule_id NUMBER
69 ,x_srp_quota_rule_id NUMBER := NULL
70 ,x_target NUMBER
71 ,x_payment_amount NUMBER
72 ,x_performance_goal NUMBER
73 )
74 IS
75 l_target cn_srp_quota_rules.target%TYPE;
76 l_payment_amount cn_srp_quota_rules.payment_amount%TYPE;
77 l_performance_goal cn_srp_quota_rules.performance_goal%TYPE;
78
79 l_srp_quota_assign_id cn_srp_quota_assigns.srp_quota_assign_id%TYPE;
80 l_addup_flag cn_quotas.ADDUP_FROM_REV_CLASS_FLAG%TYPE;
81 BEGIN
82
83 IF x_quota_rule_id IS NOT NULL AND x_srp_quota_rule_id IS NOT NULL THEN
84
85 UPDATE cn_srp_quota_rules_all r
86 SET r.target = x_target,
87 r.payment_amount = x_payment_amount,
88 r.performance_goal = x_performance_goal
89 WHERE r.quota_rule_id = x_quota_rule_id
90 AND r.srp_quota_rule_id = x_srp_quota_rule_id
91 AND EXISTS (SELECT 'quota rule belongs to a customized quota'
92 FROM cn_srp_quota_assigns_all q
93 WHERE q.srp_quota_assign_id = r.srp_quota_assign_id
94 AND q.customized_flag = 'Y')
95 ;
96
97 ELSIF x_srp_quota_rule_id IS NOT NULL THEN
98
99
100 UPDATE cn_srp_quota_rules_all r
101 SET r.target = x_target,
102 r.payment_amount = x_payment_amount,
103 r.performance_goal = x_performance_goal
104 WHERE r.srp_quota_rule_id = x_srp_quota_rule_id
105 AND EXISTS (SELECT 'quota rule belongs to a customized quota'
106 FROM cn_srp_quota_assigns_all q
107 WHERE q.srp_quota_assign_id = r.srp_quota_assign_id
108 AND q.customized_flag = 'Y')
109 ;
110
111 ELSIF x_quota_rule_id IS NOT NULL THEN
112
113 UPDATE cn_srp_quota_rules_all r
114 SET r.target = x_target,
115 r.payment_amount = x_payment_amount,
116 r.performance_goal = x_performance_goal
117 WHERE r.quota_rule_id = x_quota_rule_id
118 AND EXISTS (SELECT 'quota rule belongs to a uncustomized quota'
119 FROM cn_srp_quota_assigns_all q
120 WHERE q.srp_quota_assign_id = r.srp_quota_assign_id
121 AND q.customized_flag = 'N')
122 ;
123 END IF;
124
125 IF x_srp_quota_rule_id IS NOT NULL THEN
126
127 SELECT q.srp_quota_assign_id, q.ADDUP_REV_CLASS_FLAG
128 INTO l_srp_quota_assign_id, l_addup_flag
129 FROM cn_srp_quota_assigns_v q,
130 cn_srp_quota_rules_all r
131 WHERE q.srp_quota_assign_id = r.srp_quota_assign_id
132 AND r.srp_quota_rule_id = x_srp_quota_rule_id
133 ;
134
135 IF l_addup_flag = 'Y' THEN
136
137 SELECT SUM(nvl(target,0)),
138 SUM(nvl(payment_amount,0)),
139 SUM(nvl(performance_goal,0))
140 INTO l_target,
141 l_payment_amount,
142 l_performance_goal
143 FROM cn_srp_quota_rules_all
144 WHERE srp_quota_assign_id = l_srp_quota_assign_id;
145
146 UPDATE cn_srp_quota_assigns_all
147 SET target = l_target,
148 payment_amount = l_payment_amount,
149 performance_goal = l_performance_goal
150 WHERE srp_quota_assign_id = l_srp_quota_assign_id
151 ;
152
153 END IF;
154
155 END IF;
156
157 END update_record;
158
159 -- Procedure Name
160 --
161 -- Purpose
162 --
163 -- Notes
164 -- Manual and draw quota types do not have revenue classes
165 ---------------------------------------------------------------------------+
166 -- PROCEDURE INSERT_RECORD
167 ---------------------------------------------------------------------------+
168 PROCEDURE insert_record
169 (
170 x_srp_plan_assign_id NUMBER
171 ,x_quota_id NUMBER
172 ,x_quota_rule_id NUMBER
173 ,x_revenue_class_id NUMBER ) IS
174
175 BEGIN
176
177 IF ( x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NOT NULL) THEN
178
179 -- Inserting a new cn_quota_assign
180 -- Bug 2507490
181
182 INSERT INTO cn_srp_quota_rules_all
183 ( srp_quota_rule_id
184 ,srp_plan_assign_id
185 ,srp_quota_assign_id
186 ,quota_rule_id
187 ,revenue_class_id
188 ,target
189 ,payment_amount
190 ,performance_goal
191 ,creation_date
192 ,created_by
193 ,last_updated_by
194 ,last_update_date
195 ,last_update_login
196 ,org_id)
197 SELECT cn_srp_quota_rules_s1.nextval
198 ,sqa.srp_plan_assign_id
199 ,sqa.srp_quota_assign_id
200 ,qr.quota_rule_id
201 ,qr.revenue_class_id
202 ,qr.target
203 ,qr.payment_amount
204 ,qr.performance_goal
205 ,Sysdate
206 ,fnd_global.user_id
207 ,fnd_global.user_id
208 ,Sysdate
209 ,fnd_global.login_id
210 ,sqa.org_id
211 FROM cn_srp_quota_assigns_all sqa
212 ,cn_quota_rules_all qr
213 WHERE sqa.srp_plan_assign_id = x_srp_plan_assign_id
214 AND sqa.quota_id = x_quota_id
215 AND qr.quota_id = sqa.quota_id
216 AND sqa.quota_type_code IN ('FORMULA','EXTERNAL')
217 ;
218
219 ELSIF ( x_srp_plan_assign_id IS NOT NULL AND x_quota_id IS NULL) THEN
220
221 -- Inserting a new plan assignment
222
223 INSERT INTO cn_srp_quota_rules_all
224 ( srp_quota_rule_id
225 ,srp_plan_assign_id
226 ,srp_quota_assign_id
227 ,quota_rule_id
228 ,revenue_class_id
229 ,target
230 ,payment_amount
231 ,performance_goal
232 ,creation_date
233 ,created_by
234 ,last_updated_by
235 ,last_update_date
236 ,last_update_login
237 ,org_id)
238 SELECT cn_srp_quota_rules_s1.nextval
239 ,sqa.srp_plan_assign_id
240 ,sqa.srp_quota_assign_id
241 ,qr.quota_rule_id
242 ,qr.revenue_class_id
243 ,qr.target
244 ,qr.payment_amount
245 ,qr.performance_goal
246 ,Sysdate
247 ,fnd_global.user_id
248 ,fnd_global.user_id
249 ,Sysdate
250 ,fnd_global.login_id
251 ,sqa.org_id
252 FROM cn_srp_quota_assigns_all sqa
253 ,cn_quota_rules_all qr
254 WHERE sqa.srp_plan_assign_id = x_srp_plan_assign_id
255 AND sqa.quota_id = qr.quota_id
256 AND sqa.quota_type_code IN ('FORMULA','EXTERNAL')
257 ;
258
259 ELSIF (x_srp_plan_assign_id IS NULL AND x_quota_id IS NOT NULL) THEN
260
261 -- Inserting a new cn_quota_rules record
262 IF x_quota_rule_id IS NOT NULL THEN
263
264 -- Bug 2507490
265 INSERT INTO cn_srp_quota_rules_all
266 ( srp_quota_rule_id
267 ,srp_plan_assign_id
268 ,srp_quota_assign_id
269 ,quota_rule_id
270 ,revenue_class_id
271 ,target
272 ,payment_amount
273 ,performance_goal
274 ,creation_date
275 ,created_by
276 ,last_updated_by
277 ,last_update_date
278 ,last_update_login
279 ,org_id)
280 SELECT
281 cn_srp_quota_rules_s1.nextval
282 ,sqa.srp_plan_assign_id
283 ,sqa.srp_quota_assign_id
284 ,qr.quota_rule_id
285 ,qr.revenue_class_id
286 ,qr.target
287 ,qr.payment_amount
288 ,qr.performance_goal
289 ,Sysdate
290 ,fnd_global.user_id
291 ,fnd_global.user_id
292 ,Sysdate
293 ,fnd_global.login_id
294 ,sqa.org_id
295 FROM cn_srp_quota_assigns_all sqa
296 ,cn_quota_rules_all qr
297 WHERE sqa.quota_id = x_quota_id
298 AND qr.quota_id = sqa.quota_id
299 AND qr.quota_rule_id = x_quota_rule_id
300 AND sqa.quota_type_code IN ('FORMULA','EXTERNAL')
301 ;
302
303 ELSIF x_quota_rule_id IS NULL THEN
304
305 -- Inserting after quota type was changed to 'target' or 'revenue'
306 -- Bug 2507490
307 INSERT INTO cn_srp_quota_rules_all
308 ( srp_quota_rule_id
309 ,srp_plan_assign_id
310 ,srp_quota_assign_id
311 ,quota_rule_id
312 ,revenue_class_id
313 ,target
314 ,payment_amount
315 ,performance_goal
316 ,creation_date
317 ,created_by
318 ,last_updated_by
319 ,last_update_date
320 ,last_update_login
321 ,org_id)
322 SELECT
323 cn_srp_quota_rules_s1.nextval
324 ,sqa.srp_plan_assign_id
325 ,sqa.srp_quota_assign_id
326 ,qr.quota_rule_id
327 ,qr.revenue_class_id
328 ,qr.target
329 ,qr.payment_amount
330 ,qr.performance_goal
331 ,Sysdate
332 ,fnd_global.user_id
333 ,fnd_global.user_id
334 ,Sysdate
335 ,fnd_global.login_id
336 ,sqa.org_id
337 FROM cn_srp_quota_assigns_all sqa
338 ,cn_quota_rules_all qr
339 WHERE sqa.quota_id = x_quota_id
340 AND qr.quota_id = sqa.quota_id
341 AND sqa.quota_type_code IN ('FORMULA', 'EXTERNAL')
342 ;
343
344 END IF;
345
346 END IF;
347
348 cn_srp_per_quota_rc_pkg.insert_record
349 (
350 x_srp_plan_assign_id => x_srp_plan_assign_id
351 ,x_quota_id => x_quota_id
352 ,x_revenue_class_id => x_revenue_class_id
353 ,x_start_period_id => null
354 ,x_end_period_id => null);
355
356 -- Srp Quota Rule uplifts
357 cn_srp_rule_uplifts_pkg.insert_record
358 ( p_srp_plan_assign_id => x_srp_plan_assign_id
359 ,p_quota_id => x_quota_id
360 ,p_quota_rule_id => x_quota_rule_id
361 ,p_quota_rule_uplift_id=> null
362 );
363
364 END insert_record;
365 ---------------------------------------------------------------------------+
366 -- PROCEDURE DELETE_RECORD
367 ---------------------------------------------------------------------------+
368 PROCEDURE delete_record
369 ( x_srp_plan_assign_id NUMBER
370 ,x_srp_quota_assign_id NUMBER
371 ,x_quota_id NUMBER
372 ,x_quota_rule_id NUMBER
373 ,x_revenue_class_id NUMBER ) IS
374 BEGIN
375
376 IF ( x_srp_plan_assign_id IS NOT NULL
377 AND x_quota_id IS NOT NULL
378 AND x_quota_rule_id IS NULL ) THEN
379
380 -- cn_quota_assigns record has been deleted.
381 -- This procedure is called once for each srp_plan_assign record
382 -- the plan belongs to
383 -- We really needed another foreign key to avoid the subquery
384
385 -- before delete the srp rules, delete the uplifts.
386
387 cn_srp_rule_uplifts_pkg.Delete_record
388 (
389 p_srp_plan_assign_id => x_srp_plan_assign_id
390 ,p_quota_id => x_quota_id
391 ,p_quota_rule_id => NULL
392 ,p_quota_rule_uplift_id => NULL);
393
394 DELETE FROM cn_srp_quota_rules_all qr
395 WHERE qr.srp_plan_assign_id = x_srp_plan_assign_id
396 AND qr.srp_quota_assign_id IN
397 (SELECT sqa.srp_quota_assign_id
398 FROM cn_srp_quota_assigns sqa
399 WHERE sqa.quota_id = x_quota_id
400 AND sqa.srp_plan_assign_id = x_srp_plan_assign_id)
401 ;
402
403 ELSIF ( x_srp_plan_assign_id IS NOT NULL
404 AND x_quota_id IS NULL
405 AND x_quota_rule_id IS NULL ) THEN
406
407 -- cn_srp_plan_assigns record has been deleted
408 -- delete srp rule uplifs before delete the srp rules.
409
410 cn_srp_rule_uplifts_pkg.Delete_record
411 (
412 p_srp_plan_assign_id => x_srp_plan_assign_id
413 ,p_quota_id => NULL
414 ,p_quota_rule_id => NULL
415 ,p_quota_rule_uplift_id => NULL);
416
417 DELETE FROM cn_srp_quota_rules_all qr
418 WHERE qr.srp_plan_assign_id = x_srp_plan_assign_id;
419
420 ELSIF ( x_srp_plan_assign_id IS NULL
421 AND x_quota_id IS NOT NULL
422 AND x_quota_rule_id IS NOT NULL) THEN
423
424 -- cn_quota_rules record deleted
425 -- The revenue_class_id is also passed to ensure we can delete the
426 -- per_quota_rc records.
427
428 -- ** Delete SRp Quota rule uplifs before delete the srp quota rules
429 cn_srp_rule_uplifts_pkg.Delete_record
430 (
431 p_srp_plan_assign_id => NULL
432 ,p_quota_id => x_quota_id
433 ,p_quota_rule_id => x_quota_rule_id
434 ,p_quota_rule_uplift_id => NULL);
435
436 DELETE FROM cn_srp_quota_rules_all
437 WHERE quota_rule_id = x_quota_rule_id;
438
439 ELSIF ( x_srp_plan_assign_id IS NULL
440 AND x_quota_id IS NOT NULL
441 AND x_quota_rule_id IS NULL ) THEN
442
443 -- Quota's type has changed to 'manual' or 'draw' which do not support
444 -- revenue classes
445 -- delete srp quota rule uplifts
446
447 cn_srp_rule_uplifts_pkg.Delete_record
448 (
449 p_srp_plan_assign_id => NULL
450 ,p_quota_id => x_quota_id
451 ,p_quota_rule_id => NULL
452 ,p_quota_rule_uplift_id => NULL);
453
454 DELETE FROM cn_srp_quota_rules_all
455 WHERE quota_rule_id IN (SELECT quota_rule_id
456 FROM cn_quota_rules
457 WHERE quota_id = x_quota_id);
458
459 END IF;
460
461 cn_srp_per_quota_rc_pkg.delete_record
462 (
463 x_srp_plan_assign_id => x_srp_plan_assign_id
464 ,x_quota_id => x_quota_id
465 ,x_revenue_class_id => x_revenue_class_id
466 ,x_start_period_id => null
467 ,x_end_period_id => null);
468
469 END delete_record;
470
471 ---------------------------------------------------------------------------+
472 -- PROCEDURE SELECT_SUMMARY
473 ---------------------------------------------------------------------------+
474 PROCEDURE select_summary( x_srp_quota_assign_id NUMBER
475 ,x_total IN OUT nocopy NUMBER) IS
476 BEGIN
477 SELECT nvl(sum(target_amount),0)
478 INTO x_total
479 FROM cn_srp_period_quotas_all
480 WHERE srp_quota_assign_id = x_srp_quota_assign_id
481 ;
482
483 EXCEPTION
484 WHEN no_data_found THEN null;
485 END select_summary;
486
487
488 ---------------------------------------------------------------------------+
489 -- PROCEDURE LOCK_RECORD
490 -- Descr: New procedure you can call it from cn_srp_quota_rules form
491 ---------------------------------------------------------------------------+
492 PROCEDURE lock_record
493 ( x_srp_quota_rule_id NUMBER
494 ,x_target NUMBER
495 ,x_payment_amount NUMBER
496 ,x_performance_goal NUMBER ) IS
497
498 CURSOR C IS
499 SELECT *
500 FROM cn_srp_quota_rules_all
501 WHERE srp_quota_rule_id = x_srp_quota_rule_id
502 FOR UPDATE OF srp_quota_rule_id NOWAIT;
503 Recinfo C%ROWTYPE;
504
505 BEGIN
506
507 OPEN C;
508 FETCH C INTO Recinfo;
509
510 IF (C%NOTFOUND) THEN
511 CLOSE C;
512 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
513 app_exception.raise_exception;
514 END IF;
515 CLOSE C;
516 IF ( ( recinfo.target = x_target
517 OR (recinfo.target IS NULL AND
518 x_target IS NULL )
519 )
520 AND ( recinfo.payment_amount = x_payment_amount
521 OR (recinfo.payment_amount IS NULL AND
522 x_payment_amount IS NULL )
523 )
524 AND ( recinfo.performance_goal = x_performance_goal
525 OR (recinfo.performance_goal IS NULL AND
526 x_performance_goal IS NULL )
527 )
528 ) THEN
529 RETURN;
530 ELSE
531 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
532 app_exception.raise_exception;
533 END IF;
534
535 END Lock_Record;
536
537 END cn_srp_quota_rules_pkg;
[Home] [Help]