1 PACKAGE BODY CN_SRP_PER_QUOTA_RC_PKG as
2 /* $Header: cnsrprcb.pls 120.1 2005/12/19 13:37:22 mblum noship $ */
3
4 --Date Name Description
5 ----------------------------------------------------------------------------+
6 --12-FEB-95 P Cook
7 --24-JUL-95 P Cook Split up the insert a little more to maintain perf.
8 --28-JUL-95 P Cook Removed statment to insert/delete records when
9 -- the quota type was changed from/to revenue
10 --04-AUG-95 P Cook Added insert of mandatory QUARTER_TO_DATE column
11 --
12 --08-SEP-99 S Kumar Modified this package with the date effectivity
13 -- Modified the cn_periods to cn_period_statuses
14 -- Added start date, end_date parameters.
15 -- Procedure Name
16 --
17 -- Purpose
18 -- Remove revenue class records
19 --
20 -- When the procedure is called Passed Parameters
21
22 -- 1. Once for each deleted srp plan assignment. x_srp_plan_assign_id
23
24 -- 2. Once for each srp_plan_assign referencing the x_srp_plan_assign_id
25 -- comp plan of a deleted cn_quota_assign. x_quota_id
26
27 -- 3. Once for each deleted cn_quota_rule. x_quota_id
28 -- x_revenue_class_id
29
30 -- 4. Once for each quota whose type has been x_quota_id
31 -- changed to manual or draw.
32
33 -- 5. The quota's date range changed x_quota_id
34 ----------------------------------------------------------------------------+
35 -- Delete_Record
36 ----------------------------------------------------------------------------+
37 PROCEDURE Delete_Record( x_srp_plan_assign_id NUMBER
38 ,x_quota_id NUMBER
39 ,x_revenue_class_id NUMBER
40 ,x_start_period_id NUMBER
41 ,x_end_period_id NUMBER
42 ,x_start_date DATE := NULL
43 ,x_end_date DATE := NULL ) IS
44 BEGIN
45
46 IF x_srp_plan_assign_id IS NOT NULL THEN
47 IF x_quota_id IS NOT NULL THEN
48 -- deleting a plan's quota assignment or quota rule
49 DELETE FROM cn_srp_per_quota_rc_all
50 WHERE quota_id = x_quota_id
51 AND srp_plan_assign_id = x_srp_plan_assign_id
52 ;
53 ELSE
54 IF x_start_date IS NULL THEN
55 -- deleting an entire srp_plan_assign or changing the date range
56 DELETE FROM cn_srp_per_quota_rc_all
57 WHERE srp_plan_assign_id = x_srp_plan_assign_id
58 ;
59 ELSE
60 -- Delete the specific periods
61 DELETE FROM cn_srp_per_quota_rc_all
62 WHERE srp_plan_assign_id = x_srp_plan_assign_id
63 AND EXISTS ( select 1 from cn_period_statuses p
64 WHERE p.start_date >= Nvl(x_start_date,p.start_date)
65 AND p.end_date <= Nvl(x_end_date ,p.end_date)
66 AND cn_srp_per_quota_rc_all.org_id = p.org_id
67 AND cn_srp_per_quota_rc_all.period_id = p.period_id);
68 END IF; -- start_date is null
69 END IF; -- quota_id is not null
70 ELSE
71
72 IF x_quota_id IS NOT NULL THEN
73
74 IF x_revenue_class_id IS NOT NULL THEN
75
76 -- Deleting a quota rule
77 -- OR the quota type changed to one that doesn not support
78 -- revenue classes
79
80 DELETE FROM cn_srp_per_quota_rc_all
81 WHERE quota_id = x_quota_id
82 AND revenue_class_id = x_revenue_class_id;
83
84 ELSE
85 -- The quota's date range changed and we've deleted all period
86 -- quotas in preparation for insert of the new period quota range
87 -- OR the quota type was changed to one that does not support
88 -- revenue classes
89
90 -- Modified from cn_periods to cn_period_statuses
91 -- Modified the the start_period_id, end_period_id to
92 -- start date and end date
93 DELETE FROM cn_srp_per_quota_rc_all
94 WHERE quota_id = x_quota_id
95 AND EXISTS ( select 1 from cn_period_statuses p
96 WHERE p.start_date >= Nvl(x_start_date,p.start_date)
97 AND p.end_date <= Nvl(x_end_date ,p.end_date)
98 AND cn_srp_per_quota_rc_all.period_id = p.period_id
99 AND cn_srp_per_quota_rc_all.org_id = p.org_id);
100
101 END IF; -- revenue_class_id is not null
102 END IF; -- quota_id is not null
103 END IF; -- srp_plan_assign_id is not null
104
105 END Delete_Record;
106
107 --
108 -- Procedure Name
109 --
110 -- Purpose
111 -- Insert quota rule for each rep using the quota in a period,
112 -- The period restrictions have already been applied when creating
113 -- cn_srp_period_quotas.
114
115 -- Period quotas are created for manual and draw qupta types purely for
116 -- internal use, the user does not have access to them.
117 -- We cannot create period quota rev class records for them because
118 -- thee two quota types do not have any revenue classes
119
120 -- We do not attempt to explode any of the rolled up rev classes.
121 -- The table does not currently support rev class explosion.
122 --
123 -- Notes Parameters
124
125 -- 1 Called once for each new srp plan assignment. x_srp_plan_assign_id
126
127 -- 2 Called once for each srp plan assignment that x_srp_plan_assign_id
128 -- references the comp plan id on a new comp x_quota_id
129 -- plan quota assignment
130 -- The quota_id restriction ensures only the newly
131 -- assigned quota is inserted.
132
133 -- 3 Called once for each new quota rule x_quota_id
134 -- x_revenue_class_id
135
136 -- 4. Called once when the quota date range is changed x_quota_id
137
138 -- Notes
139 -- Using revenue_class_id instead of quota_rule_id as
140 -- revenue class/quota_id is unique.
141 ----------------------------------------------------------------------------+
142 -- Insert Record
143 ----------------------------------------------------------------------------+
144 PROCEDURE insert_record( x_srp_plan_assign_id NUMBER
145 ,x_quota_id NUMBER
146 ,x_revenue_class_id NUMBER
147 ,x_start_period_id NUMBER
148 ,x_end_period_id NUMBER
149 ,x_start_date DATE
150 ,x_end_date DATE ) IS
151
152 BEGIN
153
154 IF ( x_srp_plan_assign_id IS NULL
155 AND x_quota_id IS NOT NULL
156 AND x_revenue_class_id IS NOT NULL ) THEN
157
158 -- New quota rule inserted
159 -- Insert one record for each srp_period_quota record that references
160 -- the quota that has been assigned the new quota rule
161 -- Note the new revenue_class in the select statement.
162
163 -- clku, fixed for performance bug 2321076
164
165 INSERT INTO cn_srp_per_quota_rc_all
166 ( srp_per_quota_rc_id
167 ,srp_period_quota_id
168 ,srp_plan_assign_id
169 ,salesrep_id
170 ,period_id
171 ,quota_id
172 ,revenue_class_id
173 ,target_amount
174 ,year_to_date
175 ,period_to_date
176 ,quarter_to_date
177 ,creation_date
178 ,created_by
179 ,last_updated_by
180 ,last_update_date
181 ,last_update_login
182 ,org_id)
183 SELECT
184 cn_srp_per_quota_rc_s.nextval
185 ,pq.srp_period_quota_id
186 ,pq.srp_plan_assign_id
187 ,pq.salesrep_id
188 ,pq.period_id
189 ,pq.quota_id
190 ,x_revenue_class_id
191 ,0 -- target amount
192 ,0 -- ytd
193 ,0 -- ptd
194 ,0 -- qtd
195 ,Sysdate
196 ,fnd_global.user_id
197 ,fnd_global.user_id
198 ,Sysdate
199 ,fnd_global.login_id
200 ,pq.org_id
201 FROM cn_srp_period_quotas_all pq -- periods that rep/plan uses quota
202 ,cn_quotas_all q
203 WHERE pq.quota_id = x_quota_id
204 AND q.quota_id = pq.quota_id
205 AND q.quota_type_code IN ('FORMULA','EXTERNAL')
206
207 AND NOT EXISTS (SELECT 'srp_period_quota_rc already exists'
208 FROM cn_srp_per_quota_rc_all spqr
209 WHERE spqr.srp_period_quota_id = pq.srp_period_quota_id
210 AND spqr.srp_plan_assign_id = pq.srp_plan_assign_id
211 AND spqr.revenue_class_id = x_revenue_class_id)
212 ;
213
214 ELSIF ( x_srp_plan_assign_id IS NULL
215 AND x_quota_id IS NOT NULL
216 AND x_revenue_class_id IS NULL ) THEN
217
218 -- Quota's period range changed and having just deleted all the
219 -- period quotas and their rev class records we will now insert the
220 -- records for the new range
221
222 INSERT INTO cn_srp_per_quota_rc_all
223 ( srp_per_quota_rc_id
224 ,srp_period_quota_id
225 ,srp_plan_assign_id
226 ,salesrep_id
227 ,period_id
228 ,quota_id
229 ,revenue_class_id
230 ,target_amount
231 ,year_to_date
232 ,period_to_date
233 ,quarter_to_date
234 ,creation_date
235 ,created_by
236 ,last_updated_by
237 ,last_update_date
238 ,last_update_login
239 ,org_id)
240 SELECT
241 cn_srp_per_quota_rc_s.nextval
242 ,pq.srp_period_quota_id
243 ,pq.srp_plan_assign_id
244 ,pq.salesrep_id
245 ,pq.period_id
246 ,pq.quota_id
247 ,qr.revenue_class_id
248 ,0 -- target amount
249 ,0 -- ytd
250 ,0 -- ptd
251 ,0 -- qtd
252 ,Sysdate
253 ,fnd_global.user_id
254 ,fnd_global.user_id
255 ,Sysdate
256 ,fnd_global.login_id
257 ,pq.org_id
258 FROM cn_srp_period_quotas_all pq -- periods that rep/plan uses quota
259 ,cn_quota_rules_all qr
260 ,cn_quotas_all q
261 WHERE pq.quota_id = q.quota_id
262 AND qr.quota_id = x_quota_id
263 AND q.quota_id = qr.quota_id
264 AND q.quota_type_code IN ('EXTERNAL','FORMULA')
265
266 AND exists (select 'x' from cn_period_statuses_all p
267 where pq.period_id = p.period_id
268 AND pq.org_id = p.org_id
269 AND p.period_status in ('O','F')
270 AND p.start_date >= nvl(x_start_date, p.start_date)
271 AND p.end_date <= nvl(x_end_date, p.end_date))
272
273 AND NOT EXISTS (SELECT 'srp_period_quota_rc already exists'
274 FROM cn_srp_per_quota_rc_all spqr
275 WHERE spqr.srp_period_quota_id = pq.srp_period_quota_id
276 AND spqr.srp_plan_assign_id = pq.srp_plan_assign_id
277 AND spqr.revenue_class_id = qr.revenue_class_id)
278 ;
279
280 ELSIF ( x_srp_plan_assign_id IS NOT NULL
281 AND x_quota_id IS NOT NULL
282 AND x_revenue_class_id IS NULL ) THEN
283
284 -- A new cn_quota_assign has been created
285
286 INSERT INTO cn_srp_per_quota_rc
287 ( srp_per_quota_rc_id
288 ,srp_period_quota_id
289 ,srp_plan_assign_id
290 ,salesrep_id
291 ,period_id
292 ,quota_id
293 ,revenue_class_id
294 ,target_amount
295 ,year_to_date
296 ,period_to_date
297 ,quarter_to_date
298 ,creation_date
299 ,created_by
300 ,last_updated_by
301 ,last_update_date
302 ,last_update_login
303 ,org_id)
304 SELECT
305 cn_srp_per_quota_rc_s.nextval
306 ,pq.srp_period_quota_id
307 ,pq.srp_plan_assign_id
308 ,pq.salesrep_id
309 ,pq.period_id
310 ,pq.quota_id
311 ,qr.revenue_class_id
312 ,0 -- target amount
313 ,0 -- ytd
314 ,0 -- ptd
315 ,0 -- qtd
316 ,Sysdate
317 ,fnd_global.user_id
318 ,fnd_global.user_id
319 ,Sysdate
320 ,fnd_global.login_id
321 ,pq.org_id
322 FROM cn_srp_period_quotas_all pq -- periods that rep/plan uses quota
323 ,cn_quota_rules_all qr
324 ,cn_quotas_all q
325 WHERE pq.srp_plan_assign_id = x_srp_plan_assign_id
326 AND pq.quota_id = qr.quota_id
327 AND qr.quota_id = q.quota_id
328 AND q.quota_id = x_quota_id
329 AND q.quota_type_code IN ('EXTERNAL','FORMULA')
330
331 AND NOT EXISTS (SELECT 'srp_period_quota_rc already exists'
332 FROM cn_srp_per_quota_rc_all spqr
333 WHERE spqr.srp_period_quota_id = pq.srp_period_quota_id
334 AND spqr.srp_plan_assign_id = pq.srp_plan_assign_id
335 AND spqr.revenue_class_id = qr.revenue_class_id)
336 ;
337
338 ELSIF ( x_srp_plan_assign_id IS NOT NULL
339 AND x_quota_id IS NULL
340 AND x_revenue_class_id IS NULL ) THEN
341
342 -- New plan assignment or change in plan assigns date range
343 -- only consider difference of the range
344
345 -- modified the cn_periods to cn_period_statuses
346 -- modified the start_period_id, end_period_id to
347 -- start date end date
348
349 INSERT INTO cn_srp_per_quota_rc
350 ( srp_per_quota_rc_id
351 ,srp_period_quota_id
352 ,srp_plan_assign_id
353 ,salesrep_id
354 ,period_id
355 ,quota_id
356 ,revenue_class_id
357 ,target_amount
358 ,year_to_date
359 ,period_to_date
360 ,quarter_to_date
361 ,creation_date
362 ,created_by
363 ,last_updated_by
364 ,last_update_date
365 ,last_update_login
366 ,org_id)
367 SELECT
368 cn_srp_per_quota_rc_s.nextval
369 ,pq.srp_period_quota_id
370 ,pq.srp_plan_assign_id
371 ,pq.salesrep_id
372 ,pq.period_id
373 ,pq.quota_id
374 ,qr.revenue_class_id
375 ,0 -- target amount
376 ,0 -- ytd
377 ,0 -- ptd
378 ,0 -- qtd
379 ,Sysdate
380 ,fnd_global.user_id
381 ,fnd_global.user_id
382 ,Sysdate
383 ,fnd_global.login_id
384 ,pq.org_id
385 FROM cn_srp_period_quotas_all pq -- periods that rep/plan uses quota
386 ,cn_quota_rules_all qr
387 ,cn_quotas_all q
388 WHERE pq.srp_plan_assign_id = x_srp_plan_assign_id
389 AND pq.quota_id = qr.quota_id
390 AND qr.quota_id = q.quota_id
391 AND q.quota_type_code IN ('EXTERNAL','FORMULA')
392
393 AND exists (select 'x' from cn_period_statuses_all p
394 where pq.period_id = p.period_id
395 AND pq.org_id = p.org_id
396 AND p.period_status in ('O','F')
397 AND p.start_date >= nvl(x_start_date, p.start_date)
398 AND p.end_date <= nvl(x_end_date, p.end_date))
399
400 AND NOT EXISTS (SELECT 'srp_period_quota_rc already exists'
401 FROM cn_srp_per_quota_rc_all spqr
402 WHERE spqr.srp_period_quota_id = pq.srp_period_quota_id
403 AND spqr.srp_plan_assign_id = pq.srp_plan_assign_id
404 AND spqr.revenue_class_id = qr.revenue_class_id)
405 ;
406 END IF;
407
408 END insert_record;
409
410 END CN_SRP_PER_QUOTA_RC_PKG;
[Home] [Help]