1 PACKAGE BODY cn_srp_rate_assigns_pkg as
2 -- $Header: cnsrplcb.pls 120.0 2005/06/06 17:33:27 appldev noship $
3 /*
4
5 Package Body Name
6
7 Purpose
8 Form - cnsrmt cnpldf
9 Block - rate_assign
10
11 History
12 -------------- ------------ -------------------------------------------------+
13 26-JAN-94 Tony Lower Created
14 10-FEB-95 P Cook Modified for table handlers
15 03-AUG-95 P Cook Bug 272779. Ignore quota s customixed_flag when
16 propogating cn_rate_tier changes to the srp tiers
17 20-NOV-95 P Cook Added who columns.
18
19 Notes
20 Tiers cannot be manually inserted or deleted in the srp/plan assignment
21 block. The insert and delete procedures are called when a plan assignment
22 is inserted or deleted OR when tiers are inserted/deleted from the comp
23 plans form.
24 If the design is changed to allow manual insert and deletion these table
25 handlers must be modified to deal with individual records.
26 -- Modified
27 Delete_record procedure has two more paramenters like calc_formula_id
28 and rt_quota_asgn_id
29
30 Calc_formula_id tied with the rate_schedule.
31
32 Two more if condition is added in the delete_record procedure
33
34 */
35
36 --+
37 -- Procedure Name
38 -- Delete_Record
39 -- Purpose
40 -- Delete srp rate tier assignments.
41 -- +
42 -- Notes Passed Parameters
43 -- o Called once for each deleted srp plan assignment. x_srp_plan_assign_id
44
45 -- o Called when a quota assignment is deleted from the x_srp_plan_assign_id
46 -- source comp plans. Called once for each srp plan x_quota_id
47 -- assignment referencing the comp plan on the deleted
48 -- assignment
49
50 -- o Called once for each quota whose rate schedule has x_quota_id
51 -- been updated to null or another schedule x_rate_schedule_id
52 -- Deletes all tiers attached to the quota
53
54 -- o Called after a rate tier is deleted from x_rate_tier_id
55 -- cn_rate_tiers x_rate_schedule_id
56 -- +
57 -- o Called once for each quota whose calc_formula_id has x_quota_id
58 -- been updated to null or another calc_formula_id x_calc_formula_id
59 -- Deletes all tiers attached to the quota
60
61 -- o Called once for each rt_quota_asgns_deleted x_quota_id
62 -- x_rt_quota_asgn_id
63
64 ----------------------------------------------------------------------------+
65 -- PROCEDURE DELETE_RECORD
66 ---------------------------------------------------------------------------- +
67 PROCEDURE delete_record
68 ( x_srp_plan_assign_id NUMBER
69 ,x_srp_rate_assign_id NUMBER
70 ,x_quota_id NUMBER
71 ,x_rate_schedule_id NUMBER
72 ,x_rt_quota_asgn_id NUMBER := Null
73 ,x_rate_tier_id NUMBER) IS
74 BEGIN
75
76 IF x_rate_tier_id IS NOT NULL AND x_rate_schedule_id IS NOT NULL THEN
77
78 -- cn_rate_tiers record deleted
79 DELETE FROM cn_srp_rate_assigns_all
80 WHERE rate_tier_id = x_rate_tier_id
81 AND rate_schedule_id = x_rate_schedule_id;
82
83 ELSE
84
85 IF x_quota_id IS NOT NULL THEN
86
87 IF x_rt_quota_asgn_id IS NOT NULL THEN
88 -- delete the specific rt_quota_asgns_record.
89
90 DELETE FROM cn_srp_rate_assigns_all
91 WHERE rt_quota_asgn_id = x_rt_quota_asgn_id
92 AND quota_id = x_quota_id;
93
94 ELSIF x_srp_plan_assign_id IS NOT NULL THEN
95
96 -- deleted cn_quota_assign record
97 DELETE FROM cn_srp_rate_assigns_all
98 WHERE srp_plan_assign_id = x_srp_plan_assign_id
99 AND quota_id = x_quota_id;
100
101 ELSIF x_rate_schedule_id IS NOT NULL THEN
102
103 -- quota has been assigned a different schedule
104 DELETE FROM cn_srp_rate_assigns_all
105 WHERE rate_schedule_id = x_rate_schedule_id
106 AND quota_id = x_quota_id;
107
108 END IF;
109
110 ELSE
111 -- deleting a srp plan assign
112 DELETE FROM cn_srp_rate_assigns_all
113 WHERE srp_plan_assign_id = x_srp_plan_assign_id;
114
115 END IF;
116
117 END IF;
118
119 END delete_record;
120
121 --+
122 -- Procedure Name
123 -- Insert_Record
124 -- Purpose
125 -- Insert srp rate tier assignments.
126 -- Notes
127 -- o called once for each new srp plan assignment
128 -- o called once for each srp plan assignment that references the comp
129 -- plan on a newly created comp plan quota assignment
130 -- +
131 -- Calling event Passed Parameters
132 -- 1. after insert of srp plan assignment. x_srp_plan_assign_id
133
134 -- 2. after insert of comp plan quota assignment x_srp_plan_assign_id
135 -- x_quota_id
136
137 -- 3. after update of rate schedule assigned to quota x_quota_id
138 -- x_rate_schedule_id
139
140 -- 4. after insert of new rate tier x_rate_schedule_id
141 -- x_rate_tier_id
142 --+
143 ----------------------------------------------------------------------------+
144 -- PROCEDURE INSERT_RECORD
145 ---------------------------------------------------------------------------- +
146 PROCEDURE Insert_Record
147 (
148 x_srp_plan_assign_id NUMBER
149 ,x_srp_quota_assign_id NUMBER
150 ,x_srp_rate_assign_id NUMBER
151 ,x_quota_id NUMBER
152 ,x_rate_schedule_id NUMBER
153 ,x_rt_quota_asgn_id NUMBER := NULL
154 ,x_rate_tier_id NUMBER
155 ,x_commission_rate NUMBER
156 ,x_commission_amount NUMBER
157 ,x_disc_rate_table_flag VARCHAR2
158 ,x_rate_sequence NUMBER := NULL
159 ) IS
160
161 l_user_id NUMBER(15);
162 l_resp_id NUMBER(15);
163 l_login_id NUMBER(15);
164 BEGIN
165
166 l_user_id := fnd_global.user_id;
167 l_resp_id := fnd_global.resp_id;
168 l_login_id := fnd_global.login_id;
169
170 -- in all cases, only insert if customized_flag = 'Y' - bugfix 3204833
171 -- +
172 IF x_srp_plan_assign_id IS NOT NULL THEN
173
174 IF x_quota_id IS NOT NULL THEN
175
176 INSERT INTO cn_srp_rate_assigns_all
177 (
178 srp_plan_assign_id
179 ,srp_quota_assign_id
180 ,srp_rate_assign_id
181 ,rate_tier_id
182 ,rate_sequence
183 ,commission_amount
184 ,quota_id
185 ,rate_schedule_id
186 ,rt_quota_asgn_id
187 ,creation_date
188 ,created_by
189 ,last_update_date
190 ,last_updated_by
191 ,last_update_login
192 ,org_id)
193 SELECT
194 qa.srp_plan_assign_id
195 ,qa.srp_quota_assign_id
196 ,cn_srp_rate_assigns_s.nextval
197 ,t.rate_tier_id
198 ,t.rate_sequence
199 ,t.commission_amount
200 ,qa.quota_id
201 ,t.rate_schedule_id
202 ,rqa.rt_quota_asgn_id
203 ,sysdate
204 ,l_user_id
205 ,sysdate
206 ,l_user_id
207 ,l_login_id
208 ,qa.org_id
209 FROM cn_rate_tiers_all t
210 ,cn_srp_quota_assigns_all qa
211 ,cn_rt_quota_asgns_all rqa
212 WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
213 AND qa.quota_id = x_quota_id
214 AND rqa.quota_id = x_quota_id
215 AND rqa.quota_id = qa.quota_id
216 AND rqa.rate_schedule_id = t.rate_schedule_id
217 AND qa.customized_flag = 'Y'
218 AND qa.quota_type_code IN ('EXTERNAL','FORMULA')
219 AND t.commission_amount <> 0
220 ;
221
222 --- Insert the Discount Rate SChedule Table when a New Plan element
223 --- is being assigned to a COmp Plan.
224
225 ELSE
226 -- New plan assignment
227 INSERT INTO cn_srp_rate_assigns_all
228 (srp_plan_assign_id
229 ,srp_quota_assign_id
230 ,srp_rate_assign_id
231 ,rate_tier_id
232 ,rate_sequence
233 ,commission_amount
234 ,quota_id
235 ,rate_schedule_id
236 ,rt_quota_asgn_id
237 ,creation_date
238 ,created_by
239 ,last_update_date
240 ,last_updated_by
241 ,last_update_login
242 ,org_id)
243 SELECT qa.srp_plan_assign_id
244 ,qa.srp_quota_assign_id
245 ,cn_srp_rate_assigns_s.nextval
246 ,t.rate_tier_id
247 ,t.rate_sequence
248 ,t.commission_amount
249 ,qa.quota_id
250 ,t.rate_schedule_id
251 ,rqa.rt_quota_asgn_id
252 ,sysdate
253 ,l_user_id
254 ,sysdate
255 ,l_user_id
256 ,l_login_id
257 ,qa.org_id
258 FROM cn_rate_tiers_all t
259 ,cn_srp_quota_assigns_all qa
260 ,cn_rt_quota_asgns_all rqa
261 WHERE qa.srp_plan_assign_id = x_srp_plan_assign_id
262 AND qa.quota_id = rqa.quota_id
263 AND rqa.rate_schedule_id = t.rate_schedule_id
264 AND qa.customized_flag = 'Y'
265 AND qa.quota_type_code IN ('EXTERNAL','FORMULA')
266 AND t.commission_amount <> 0
267 ;
268 END IF; -- x_quota_id is not null
269
270 ELSIF x_quota_id IS NOT NULL AND x_rate_schedule_id IS NOT NULL THEN
271 -- create a new rt_quota_assigns
272 -- 1 called from cn_rt_quota_assigns insert_record
273 IF x_rt_quota_asgn_id IS NOT NULL THEN
274
275 INSERT INTO cn_srp_rate_assigns_all
276 (srp_plan_assign_id
277 ,srp_quota_assign_id
278 ,srp_rate_assign_id
279 ,rate_tier_id
280 ,rate_sequence
281 ,commission_amount
282 ,quota_id
283 ,rate_schedule_id
284 ,rt_quota_asgn_id
285 ,creation_date
286 ,created_by
287 ,last_update_date
288 ,last_updated_by
289 ,last_update_login
290 ,org_id)
291 SELECT qa.srp_plan_assign_id
292 ,qa.srp_quota_assign_id
293 ,cn_srp_rate_assigns_s.nextval
294 ,t.rate_tier_id
295 ,t.rate_sequence
296 ,t.commission_amount
297 ,qa.quota_id
298 ,t.rate_schedule_id
299 ,rqa.rt_quota_asgn_id
300 ,sysdate
301 ,l_user_id
302 ,sysdate
303 ,l_user_id
304 ,l_login_id
305 ,qa.org_id
306 FROM cn_rate_tiers_all t
307 ,cn_srp_quota_assigns_all qa
308 ,cn_rt_quota_asgns_all rqa
309 WHERE qa.quota_id = x_quota_id
310 AND rqa.quota_id = x_quota_Id
311 AND rqa.quota_id = qa.quota_id
312 AND rqa.rate_schedule_id = x_rate_schedule_id
313 AND t.rate_schedule_id = x_rate_schedule_id
314 AND rqa.rate_schedule_id = t.rate_schedule_id
315 AND rqa.rt_quota_asgn_id = x_rt_quota_asgn_id
316 AND qa.customized_flag = 'Y'
317 AND qa.quota_type_code IN ('EXTERNAL', 'FORMULA')
318 AND t.commission_amount <> 0
319 ;
320
321 ELSE
322
323 INSERT INTO cn_srp_rate_assigns_all
324 (srp_plan_assign_id
325 ,srp_quota_assign_id
326 ,srp_rate_assign_id
327 ,rate_tier_id
328 ,rate_sequence
329 ,commission_amount
330 ,quota_id
331 ,rate_schedule_id
332 ,rt_quota_asgn_id
333 ,creation_date
334 ,created_by
335 ,last_update_date
336 ,last_updated_by
337 ,last_update_login
338 ,org_id)
339 SELECT qa.srp_plan_assign_id
340 ,qa.srp_quota_assign_id
341 ,cn_srp_rate_assigns_s.nextval
342 ,t.rate_tier_id
343 ,t.rate_sequence
344 ,t.commission_amount
345 ,qa.quota_id
346 ,t.rate_schedule_id
347 ,rqa.rt_quota_asgn_id
348 ,sysdate
349 ,l_user_id
350 ,sysdate
351 ,l_user_id
352 ,l_login_id
353 ,qa.org_id
354 FROM cn_rate_tiers_all t
355 ,cn_srp_quota_assigns_all qa
356 ,cn_rt_quota_asgns_all rqa
357 WHERE qa.quota_id = x_quota_id
358 AND rqa.quota_id = x_quota_Id
359 AND rqa.quota_id = qa.quota_id
360 AND rqa.rate_schedule_id = x_rate_schedule_id
361 AND t.rate_schedule_id = x_rate_schedule_id
362 AND rqa.rate_schedule_id = t.rate_schedule_id
363 AND qa.customized_flag = 'Y'
364 AND qa.quota_type_code IN ('EXTERNAL', 'FORMULA')
365 AND t.commission_amount <> 0
366 ;
367
368 END IF;
369
370 ELSIF ( x_quota_id IS NULL AND x_rate_schedule_id IS NOT NULL
371 AND x_rate_tier_id IS NOT NULL) THEN
372
373 INSERT INTO cn_srp_rate_assigns_all
374 ( srp_plan_assign_id
375 ,srp_quota_assign_id
376 ,srp_rate_assign_id
377 ,rate_tier_id
378 ,rate_sequence
379 ,commission_amount
380 ,quota_id
381 ,rate_schedule_id
382 ,rt_quota_asgn_id
383 ,creation_date
384 ,created_by
385 ,last_update_date
386 ,last_updated_by
387 ,last_update_login
388 ,org_id
389 )
390 SELECT qa.srp_plan_assign_id
391 ,qa.srp_quota_assign_id
392 ,cn_srp_rate_assigns_s.nextval
393 ,t.rate_tier_id
394 ,t.rate_sequence
395 ,t.commission_amount
396 ,qa.quota_id
397 ,t.rate_schedule_id
398 ,rqa.rt_quota_asgn_id
399 ,sysdate
400 ,l_user_id
401 ,sysdate
402 ,l_user_id
403 ,l_login_id
404 ,qa.org_id
405 FROM cn_rate_tiers_all t
406 ,cn_srp_quota_assigns_all qa
407 ,cn_rt_quota_asgns_all rqa
408 WHERE rqa.rate_schedule_id = t.rate_schedule_id
409 AND rqa.quota_id = qa.quota_id
410 AND t.rate_tier_id = x_rate_tier_id
411 AND t.rate_schedule_id = x_rate_schedule_id
412 AND qa.customized_flag = 'Y'
413 AND qa.quota_type_code IN ('EXTERNAL', 'FORMULA')
414 AND t.commission_amount <> 0
415 ;
416 END IF;
417
418 END insert_record;
419 ----------------------------------------------------------------------------+
420 -- PROCEDURE LOCK_RECORD
421 ---------------------------------------------------------------------------- +
422 PROCEDURE Lock_Record
423 (
424 X_Srp_Plan_Assign_Id NUMBER,
425 X_Srp_Quota_Assign_Id NUMBER,
426 X_Srp_Rate_Assign_Id NUMBER,
427 X_Rate_Tier_Id NUMBER,
428 X_Commission_Rate NUMBER,
429 x_commission_amount NUMBER ) IS
430 CURSOR C IS
431 SELECT *
432 FROM cn_srp_rate_assigns_all
433 WHERE srp_rate_assign_id = x_srp_rate_assign_id
434 FOR UPDATE OF srp_rate_assign_id NOWAIT;
435 Recinfo C%ROWTYPE;
436
437
438 BEGIN
439 OPEN C;
440 FETCH C INTO Recinfo;
441
442 IF (C%NOTFOUND) THEN
443 close C;
444 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
445 app_exception.raise_exception;
446 END IF;
447 CLOSE C;
448
449 IF ( ( recinfo.commission_amount = x_commission_amount
450 OR ( recinfo.commission_amount IS NULL
451 AND x_commission_amount IS NULL) )
452 AND (recinfo.commission_rate = x_commission_rate
453 OR ( recinfo.commission_rate IS NULL
454 AND x_commission_rate IS NULL) )
455 ) THEN
456 RETURN;
457
458 ELSE
459
460 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
461 app_exception.raise_exception;
462 END IF;
463
464 END Lock_Record;
465 ----------------------------------------------------------------------------+
466 -- PROCEDURE UPDATE_RECORD
467 ---------------------------------------------------------------------------- +
468 PROCEDURE Update_Record(
469 x_srp_plan_assign_id NUMBER
470 ,x_srp_quota_assign_Id NUMBER
471 ,x_srp_rate_assign_id NUMBER
472 ,x_rate_tier_id NUMBER
473 ,x_commission_rate NUMBER
474 ,x_commission_rate_old NUMBER
475 ,x_start_period_id NUMBER
476 ,x_salesrep_id NUMBER
477 ,x_commission_amount NUMBER
478 ,x_commission_amount_old NUMBER
479 ,x_last_update_date DATE
480 ,x_last_updated_by NUMBER
481 ,x_last_update_login NUMBER) IS
482 BEGIN
483
487 ,last_updated_by = x_last_updated_by
484 UPDATE cn_srp_rate_assigns_all
485 SET commission_amount = x_commission_amount
486 ,last_update_date = x_last_update_date
488 ,last_update_login = x_last_update_login
489 WHERE srp_rate_assign_id = x_srp_rate_assign_id
490 ;
491
492 IF SQL%NOTFOUND THEN
493 raise no_data_found;
494 END IF;
495
496 END Update_Record;
497
498 -- Procedure Name
499 -- Synch_rate
500 -- Purpose
501 -- Ensure that all rep/plan assignments get the correct commission rates
502 -- Notes
503 -- Not called unless quota type is target or revenue
504 -- +
505 -- When the procedure is called Passed Parameters
506 -- 1. After update of cn_rate_tiers.commission_rate x_rate_schedule_id
507 -- x_rate_tier_id
508
509 -- 2. After user chooses to not to have custom quotas x_srp_plan_assign_id
510 -- and rates at srp level x_srp_quota_assign_id
511 -- x_rate_schedule_id
512 ----------------------------------------------------------------------------+
513 -- PROCEDURE SYNCH_RATE
514 ---------------------------------------------------------------------------- +
515 PROCEDURE synch_rate(
516 x_srp_plan_assign_id NUMBER
517 ,x_srp_quota_assign_id NUMBER
518 ,x_rate_schedule_id NUMBER
519 ,x_rate_tier_id NUMBER
520 ,x_commission_rate NUMBER
521 ,x_salesrep_id NUMBER
522 ,x_start_period_id NUMBER
523 ,x_commission_amount NUMBER ) IS
524 BEGIN
525 -- obsoleted as part of bug fix 3204833
526 NULL;
527 END synch_rate;
528
529 END cn_srp_rate_assigns_pkg;
[Home] [Help]