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