[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_PLAN_ASSIGNS_PKG
Source
1 PACKAGE BODY CN_SRP_PLAN_ASSIGNS_PKG AS
2 -- $Header: cnsrplab.pls 120.0 2005/06/06 17:57:37 appldev noship $
3 --
4 -- Package Name
5 -- CN_SRP_PLAN_ASSIGNS_PKG
6 -- Purpose
7 -- Table Handler for CN_SRP_PLAN_ASSIGNS
8 -- FORM CNSRMT
9 -- BLOCK SRP_PLAN_ASSIGNS
10 --
11 -- History
12 -- 06-Jun-99 Angela Chung Created
13 -- /*-------------------------------------------------------------------------*
14 -- |
15 -- | PRIVATE VARIABLES
16 -- |
17 -- *-------------------------------------------------------------------------*/
18
19 -- -------------------------------------------------------------------------+
20 -- Procedure Name
21 -- Get_UID
22 -- Purpose
23 -- Get the Sequence Number to Create a new Srp Plan Assign.
24 -- -------------------------------------------------------------------------+
25 PROCEDURE Get_UID( X_srp_plan_assign_id IN OUT NOCOPY NUMBER) IS
26 BEGIN
27 SELECT cn_srp_plan_assigns_s.nextval
28 INTO x_srp_plan_assign_id
29 FROM dual;
30 END Get_UID;
31
32 -- -------------------------------------------------------------------------+
33 -- Procedure Name
34 -- Get_UID
35 -- Purpose
36 -- Get the Next period
37 -- -------------------------------------------------------------------------+
38 FUNCTION next_period (p_end_date DATE, p_org_id NUMBER)
39 RETURN cn_period_statuses.end_date%TYPE IS
40
41 l_next_end_date cn_period_statuses.end_date%TYPE;
42
43 BEGIN
44
45 SELECT MAX(end_date)
46 INTO l_next_end_date
47 FROM cn_period_statuses_all s, cn_repositories_all r
48 WHERE s.period_type_id = r.period_type_id
49 AND s.period_set_id = r.period_set_id
50 AND s.org_id = p_org_id
51 AND r.org_id = p_org_id;
52
53 IF trunc(l_next_end_date) > trunc(p_end_date) THEN
54
55 SELECT MIN(end_date)
56 INTO l_next_end_date
57 FROM cn_period_statuses_all s, cn_repositories_all r
58 WHERE trunc(end_date) >= trunc(p_end_date)
59 AND s.period_type_id = r.period_type_id
60 AND s.period_set_id = r.period_set_id
61 AND s.org_id = p_org_id
62 AND r.org_id = p_org_id;
63
64 END IF;
65
66 RETURN l_next_end_date;
67
68 EXCEPTION
69 WHEN no_data_found THEN
70 RETURN NULL;
71 END next_period;
72
73 -- -------------------------------------------------------------------------+
74 -- Procedure Name
75 -- INSERT_ROW
76 -- Purpose
77 --
78 -- History
79 --
80 -- -------------------------------------------------------------------------+
81 PROCEDURE INSERT_ROW
82 (X_SRP_PLAN_ASSIGN_ID IN OUT NOCOPY NUMBER,
83 X_SRP_ROLE_ID IN NUMBER,
84 X_ROLE_PLAN_ID IN NUMBER,
85 X_SALESREP_ID IN NUMBER,
86 X_ROLE_ID IN NUMBER,
87 X_COMP_PLAN_ID IN NUMBER,
88 X_START_DATE IN DATE,
89 X_END_DATE IN DATE,
90 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
91 X_ATTRIBUTE1 IN VARCHAR2,
92 X_ATTRIBUTE2 IN VARCHAR2,
93 X_ATTRIBUTE3 IN VARCHAR2,
94 X_ATTRIBUTE4 IN VARCHAR2,
95 X_ATTRIBUTE5 IN VARCHAR2,
96 X_ATTRIBUTE6 IN VARCHAR2,
97 X_ATTRIBUTE7 IN VARCHAR2,
98 X_ATTRIBUTE8 IN VARCHAR2,
99 X_ATTRIBUTE9 IN VARCHAR2,
100 X_ATTRIBUTE10 IN VARCHAR2,
101 X_ATTRIBUTE11 IN VARCHAR2,
102 X_ATTRIBUTE12 IN VARCHAR2,
103 X_ATTRIBUTE13 IN VARCHAR2,
104 X_ATTRIBUTE14 IN VARCHAR2,
105 X_ATTRIBUTE15 IN VARCHAR2,
106 X_CREATED_BY IN NUMBER,
107 X_CREATION_DATE IN DATE,
108 X_LAST_UPDATE_DATE IN DATE,
109 X_LAST_UPDATED_BY IN NUMBER,
110 X_LAST_UPDATE_LOGIN IN NUMBER
111 ) IS
112 l_dummy NUMBER;
113 l_start_period_id cn_srp_periods.period_id%TYPE;
114 l_end_period_id cn_srp_periods.period_id%TYPE;
115 l_org_id NUMBER;
116
117 BEGIN
118
119 Get_UID(X_SRP_PLAN_ASSIGN_ID);
120
121 -- get org ID
122 SELECT org_id INTO l_org_id
123 FROM cn_comp_plans_all
124 WHERE comp_plan_id = x_comp_plan_id;
125
126 INSERT INTO CN_SRP_PLAN_ASSIGNS
127 (SRP_PLAN_ASSIGN_ID,
128 SRP_ROLE_ID,
129 ROLE_PLAN_ID,
130 SALESREP_ID,
131 ORG_ID,
132 ROLE_ID,
133 COMP_PLAN_ID,
134 START_DATE,
135 END_DATE,
136 ATTRIBUTE_CATEGORY,
137 ATTRIBUTE1,
138 ATTRIBUTE2,
139 ATTRIBUTE3,
140 ATTRIBUTE4,
141 ATTRIBUTE5,
142 ATTRIBUTE6,
143 ATTRIBUTE7,
144 ATTRIBUTE8,
145 ATTRIBUTE9,
146 ATTRIBUTE10,
147 ATTRIBUTE11,
148 ATTRIBUTE12,
149 ATTRIBUTE13,
150 ATTRIBUTE14,
151 ATTRIBUTE15,
152 CREATION_DATE,
153 CREATED_BY,
154 LAST_UPDATE_DATE,
155 LAST_UPDATED_BY,
156 LAST_UPDATE_LOGIN
157 ) VALUES
158 (X_SRP_PLAN_ASSIGN_ID,
159 X_SRP_ROLE_ID,
160 X_ROLE_PLAN_ID,
161 X_SALESREP_ID,
162 l_ORG_ID,
163 X_ROLE_ID,
164 X_COMP_PLAN_ID,
165 X_START_DATE,
166 X_END_DATE,
167 X_ATTRIBUTE_CATEGORY,
168 X_ATTRIBUTE1,
169 X_ATTRIBUTE2,
170 X_ATTRIBUTE3,
171 X_ATTRIBUTE4,
172 X_ATTRIBUTE5,
173 X_ATTRIBUTE6,
174 X_ATTRIBUTE7,
175 X_ATTRIBUTE8,
176 X_ATTRIBUTE9,
177 X_ATTRIBUTE10,
178 X_ATTRIBUTE11,
179 X_ATTRIBUTE12,
180 X_ATTRIBUTE13,
181 X_ATTRIBUTE14,
182 X_ATTRIBUTE15,
183 X_CREATION_DATE,
184 X_CREATED_BY,
185 X_LAST_UPDATE_DATE,
186 X_LAST_UPDATED_BY,
187 X_LAST_UPDATE_LOGIN
188 );
189 SELECT 1 INTO l_dummy FROM CN_SRP_PLAN_ASSIGNS_ALL
190 WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID;
191
192 -- insert all child records -- called in API:CN_SRP_PLAN_ASSIGNS_PVT
193 -- cn_srp_quota_assigns_pkg.insert_record
194
195 END INSERT_ROW;
196
197 -- -------------------------------------------------------------------------+
198 -- Procedure Name
199 -- LOCK_ROW
200 -- Purpose
201 --
202 -- History
203 --
204 -- -------------------------------------------------------------------------+
205 PROCEDURE LOCK_ROW
206 (X_SRP_PLAN_ASSIGN_ID IN NUMBER,
207 X_SRP_ROLE_ID IN NUMBER,
208 X_ROLE_PLAN_ID IN NUMBER,
209 X_SALESREP_ID IN NUMBER,
210 X_ROLE_ID IN NUMBER,
211 X_COMP_PLAN_ID IN NUMBER,
212 X_START_DATE IN DATE,
213 X_END_DATE IN DATE,
214 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
215 X_ATTRIBUTE1 IN VARCHAR2,
216 X_ATTRIBUTE2 IN VARCHAR2,
217 X_ATTRIBUTE3 IN VARCHAR2,
218 X_ATTRIBUTE4 IN VARCHAR2,
219 X_ATTRIBUTE5 IN VARCHAR2,
220 X_ATTRIBUTE6 IN VARCHAR2,
221 X_ATTRIBUTE7 IN VARCHAR2,
222 X_ATTRIBUTE8 IN VARCHAR2,
223 X_ATTRIBUTE9 IN VARCHAR2,
224 X_ATTRIBUTE10 IN VARCHAR2,
225 X_ATTRIBUTE11 IN VARCHAR2,
226 X_ATTRIBUTE12 IN VARCHAR2,
227 X_ATTRIBUTE13 IN VARCHAR2,
228 X_ATTRIBUTE14 IN VARCHAR2,
229 X_ATTRIBUTE15 IN VARCHAR2
230 ) IS
231 CURSOR c IS
232 SELECT * FROM CN_SRP_PLAN_ASSIGNS_ALL
233 WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID
234 FOR UPDATE OF SRP_PLAN_ASSIGN_ID nowait;
235 tlinfo C%ROWTYPE;
236 BEGIN
237 OPEN C;
238 FETCH C INTO tlinfo;
239 IF (C%NOTFOUND) THEN
240 CLOSE C;
241 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
242 app_exception.raise_exception;
243 END IF;
244 CLOSE C;
245
246 IF ((tlinfo.SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID)
247 AND (tlinfo.ROLE_PLAN_ID = X_ROLE_PLAN_ID)
248 AND (tlinfo.SRP_ROLE_ID = X_SRP_ROLE_ID)
249 AND (tlinfo.SALESREP_ID = X_SALESREP_ID)
250 AND (tlinfo.ROLE_ID = X_ROLE_ID)
251 AND (tlinfo.COMP_PLAN_ID = X_COMP_PLAN_ID)
252 AND (tlinfo.START_DATE = X_START_DATE)
253 AND ((tlinfo.END_DATE = X_END_DATE)
254 OR ((tlinfo.END_DATE is null) AND (X_END_DATE is null)))
255 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
256 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
257 AND (X_ATTRIBUTE_CATEGORY is null)))
258 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
259 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
260 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
261 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
262 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
263 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
264 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
265 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
266 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
267 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
268 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
269 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
270 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
271 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
272 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
273 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
274 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
275 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
276 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
277 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
278 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
279 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
280 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
281 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
282 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
283 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
284 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
285 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
286 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
287 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
288 ) THEN
289 RETURN;
290 ELSE
291 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
292 app_exception.raise_exception;
293 END IF ;
294
295 END LOCK_ROW;
296
297 -- -------------------------------------------------------------------------+
298 -- Procedure Name
299 -- UPDATE_ROW
300 -- Purpose
301 --
302 -- History
303 --
304 -- -------------------------------------------------------------------------+
305 PROCEDURE UPDATE_ROW
306 (X_SRP_PLAN_ASSIGN_ID IN NUMBER,
307 X_SRP_ROLE_ID IN NUMBER,
308 X_ROLE_PLAN_ID IN NUMBER,
309 X_SALESREP_ID IN NUMBER,
310 X_ROLE_ID IN NUMBER,
311 X_COMP_PLAN_ID IN NUMBER,
312 X_START_DATE IN DATE,
313 X_END_DATE IN DATE,
314 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
315 X_ATTRIBUTE1 IN VARCHAR2,
316 X_ATTRIBUTE2 IN VARCHAR2,
317 X_ATTRIBUTE3 IN VARCHAR2,
318 X_ATTRIBUTE4 IN VARCHAR2,
319 X_ATTRIBUTE5 IN VARCHAR2,
320 X_ATTRIBUTE6 IN VARCHAR2,
321 X_ATTRIBUTE7 IN VARCHAR2,
322 X_ATTRIBUTE8 IN VARCHAR2,
323 X_ATTRIBUTE9 IN VARCHAR2,
324 X_ATTRIBUTE10 IN VARCHAR2,
325 X_ATTRIBUTE11 IN VARCHAR2,
326 X_ATTRIBUTE12 IN VARCHAR2,
327 X_ATTRIBUTE13 IN VARCHAR2,
328 X_ATTRIBUTE14 IN VARCHAR2,
329 X_ATTRIBUTE15 IN VARCHAR2,
330 X_LAST_UPDATE_DATE IN DATE,
331 X_LAST_UPDATED_BY IN NUMBER,
332 X_LAST_UPDATE_LOGIN IN NUMBER
333 ) IS
334
335 l_srp_role_id CN_SRP_PLAN_ASSIGNS.srp_role_id%TYPE;
336 l_role_plan_id CN_SRP_PLAN_ASSIGNS.role_plan_id%TYPE;
337 l_salesrep_id CN_SRP_PLAN_ASSIGNS.salesrep_id%TYPE;
338 l_role_id CN_SRP_PLAN_ASSIGNS.role_id%TYPE;
339 l_comp_plan_id CN_SRP_PLAN_ASSIGNS.comp_plan_id%TYPE;
340 l_start_date CN_SRP_PLAN_ASSIGNS.start_date%TYPE;
341 l_end_date CN_SRP_PLAN_ASSIGNS.end_date%TYPE;
342 l_attribute_category CN_SRP_PLAN_ASSIGNS.attribute_category%TYPE;
343 l_attribute1 CN_SRP_PLAN_ASSIGNS.attribute1%TYPE;
344 l_attribute2 CN_SRP_PLAN_ASSIGNS.attribute2%TYPE;
345 l_attribute3 CN_SRP_PLAN_ASSIGNS.attribute3%TYPE;
346 l_attribute4 CN_SRP_PLAN_ASSIGNS.attribute4%TYPE;
347 l_attribute5 CN_SRP_PLAN_ASSIGNS.attribute5%TYPE;
348 l_attribute6 CN_SRP_PLAN_ASSIGNS.attribute6%TYPE;
349 l_attribute7 CN_SRP_PLAN_ASSIGNS.attribute7%TYPE;
350 l_attribute8 CN_SRP_PLAN_ASSIGNS.attribute8%TYPE;
351 l_attribute9 CN_SRP_PLAN_ASSIGNS.attribute9%TYPE;
352 l_attribute10 CN_SRP_PLAN_ASSIGNS.attribute10%TYPE;
353 l_attribute11 CN_SRP_PLAN_ASSIGNS.attribute11%TYPE;
354 l_attribute12 CN_SRP_PLAN_ASSIGNS.attribute12%TYPE;
355 l_attribute13 CN_SRP_PLAN_ASSIGNS.attribute13%TYPE;
356 l_attribute14 CN_SRP_PLAN_ASSIGNS.attribute14%TYPE;
357 l_attribute15 CN_SRP_PLAN_ASSIGNS.attribute15%TYPE;
358
359 l_next_start_date cn_acc_period_statuses_v.end_date%TYPE;
360
361 CURSOR c IS
362 SELECT * FROM CN_SRP_PLAN_ASSIGNS_ALL
363 WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID
364 FOR UPDATE OF SRP_PLAN_ASSIGN_ID nowait;
365 oldrow C%ROWTYPE;
366
367 BEGIN
368 OPEN C;
369 FETCH C INTO oldrow;
370 IF (C%NOTFOUND) THEN
371 CLOSE C;
372 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
373 APP_EXCEPTION.RAISE_EXCEPTION;
374 END IF;
375 CLOSE C;
376 SELECT
377 decode(x_srp_role_id,
378 fnd_api.g_miss_num, oldrow.srp_role_id,
379 x_srp_role_id),
380 decode(x_role_plan_id,
381 fnd_api.g_miss_num, oldrow.role_plan_id,
382 x_role_plan_id),
383 decode(x_salesrep_id,
384 fnd_api.g_miss_num, oldrow.salesrep_id,
385 x_salesrep_id),
386 decode(x_role_id,
387 fnd_api.g_miss_num, oldrow.role_id,
388 x_role_id),
389 decode(x_comp_plan_id,
390 fnd_api.g_miss_num, oldrow.comp_plan_id,
391 x_comp_plan_id),
392 decode(x_start_date,
393 fnd_api.g_miss_date, oldrow.start_date,
394 x_start_date),
395 decode(x_end_date,
396 fnd_api.g_miss_date, oldrow.end_date,
397 x_end_date),
398 decode(x_attribute_category,
399 fnd_api.g_miss_char, oldrow.attribute_category,
400 x_attribute_category),
401 decode(x_attribute1,
402 fnd_api.g_miss_char, oldrow.attribute1,
403 x_attribute1),
404 decode(x_attribute2,
405 fnd_api.g_miss_char, oldrow.attribute2,
406 x_attribute2),
407 decode(x_attribute3,
408 fnd_api.g_miss_char, oldrow.attribute3,
409 x_attribute3),
410 decode(x_attribute4,
411 fnd_api.g_miss_char, oldrow.attribute4,
412 x_attribute4),
413 decode(x_attribute5,
414 fnd_api.g_miss_char, oldrow.attribute5,
415 x_attribute5),
416 decode(x_attribute6,
417 fnd_api.g_miss_char, oldrow.attribute6,
418 x_attribute6),
419 decode(x_attribute7,
420 fnd_api.g_miss_char, oldrow.attribute7,
421 x_attribute7),
422 decode(x_attribute8,
423 fnd_api.g_miss_char, oldrow.attribute8,
424 x_attribute8),
425 decode(x_attribute9,
426 fnd_api.g_miss_char, oldrow.attribute9,
427 x_attribute9),
428 decode(x_attribute10,
429 fnd_api.g_miss_char, oldrow.attribute10,
430 x_attribute10),
431 decode(x_attribute11,
432 fnd_api.g_miss_char, oldrow.attribute11,
433 x_attribute11),
434 decode(x_attribute12,
435 fnd_api.g_miss_char, oldrow.attribute12,
436 x_attribute12),
437 decode(x_attribute13,
438 fnd_api.g_miss_char, oldrow.attribute13,
439 x_attribute13),
440 decode(x_attribute14,
441 fnd_api.g_miss_char, oldrow.attribute14,
442 x_attribute14),
443 decode(x_attribute15,
444 fnd_api.g_miss_char, oldrow.attribute15,
445 x_attribute15)
446 INTO
447 l_srp_role_id,
448 l_role_plan_id,
449 l_salesrep_id,
450 l_role_id,
451 l_comp_plan_id,
452 l_start_date,
453 l_end_date,
454 l_attribute_category,
455 l_attribute1,
456 l_attribute2,
457 l_attribute3,
458 l_attribute4,
459 l_attribute5,
460 l_attribute6,
461 l_attribute7,
462 l_attribute8,
463 l_attribute9,
464 l_attribute10,
465 l_attribute11,
466 l_attribute12,
467 l_attribute13,
468 l_attribute14,
469 l_attribute15
470 FROM dual;
471
472 UPDATE CN_SRP_PLAN_ASSIGNS_ALL SET
473 SRP_PLAN_ASSIGN_ID = x_srp_plan_assign_id ,
474 SRP_ROLE_ID = l_srp_role_id ,
475 ROLE_PLAN_ID = l_role_plan_id ,
476 SALESREP_ID = l_salesrep_id ,
477 ROLE_ID = l_role_id ,
478 COMP_PLAN_ID = l_comp_plan_id ,
479 START_DATE = l_start_date ,
480 END_DATE = l_end_date ,
481 ATTRIBUTE_CATEGORY = l_attribute_category ,
482 ATTRIBUTE1 = l_attribute1 ,
483 ATTRIBUTE2 = l_attribute2 ,
484 ATTRIBUTE3 = l_attribute3 ,
485 ATTRIBUTE4 = l_attribute4 ,
486 ATTRIBUTE5 = l_attribute5 ,
487 ATTRIBUTE6 = l_attribute6 ,
488 ATTRIBUTE7 = l_attribute7 ,
489 ATTRIBUTE8 = l_attribute8 ,
490 ATTRIBUTE9 = l_attribute9 ,
491 ATTRIBUTE10 = l_attribute10 ,
492 ATTRIBUTE11 = l_attribute11 ,
493 ATTRIBUTE12 = l_attribute12 ,
494 ATTRIBUTE13 = l_attribute13 ,
495 ATTRIBUTE14 = l_attribute14 ,
496 ATTRIBUTE15 = l_attribute15 ,
497 LAST_UPDATE_DATE = x_last_update_date ,
498 LAST_UPDATED_BY = x_last_updated_by ,
499 LAST_UPDATE_LOGIN = x_last_update_login
500 WHERE SRP_PLAN_ASSIGN_ID = x_srp_plan_assign_id;
501
502 IF (SQL%NOTFOUND) THEN
503 RAISE NO_DATA_FOUND;
504 END IF;
505
506 -- The periods have changed we need maintain the period dependent
507 -- information. do not need to do srp_quotas and srp_rate_assigns
508 IF (x_start_date <> oldrow.start_date) OR
509 (Nvl(x_end_date,fnd_api.g_miss_date) <>
510 Nvl(oldrow.end_date,fnd_api.g_miss_date)) THEN
511 -- start_date remanin unchanged
512 IF x_start_date = oldrow.start_date THEN
513 IF x_end_date IS NULL THEN
514 -- oldrow.end_date is not null,extend end_date
515 cn_srp_period_quotas_pkg.insert_record
516 (x_srp_plan_assign_id => x_srp_plan_assign_id
517 ,x_quota_id => NULL
518 ,x_start_period_id => NULL -- obsolete
519 ,x_end_period_id => NULL -- obsolete
520 ,x_start_date => next_period(oldrow.end_date,
521 oldrow.org_id)
522 ,x_end_date => x_end_date );
523 cn_srp_per_quota_rc_pkg.insert_record
524 (x_srp_plan_assign_id => x_srp_plan_assign_id
525 ,x_quota_id => NULL
526 ,x_revenue_class_id => NULL
527 ,x_start_period_id => NULL
528 ,x_end_period_id => NULL
529 ,x_start_date => next_period(oldrow.end_date,
530 oldrow.org_id)
531 ,x_end_date => x_end_date);
532 ELSIF oldrow.end_date IS NULL THEN
533 -- x_end_date is not null,shorten end_date
534 cn_srp_period_quotas_pkg.delete_record
535 ( x_srp_plan_assign_id => x_srp_plan_assign_id
536 ,x_quota_id => NULL
537 ,x_start_period_id => NULL -- obsolete
538 ,x_end_period_id => NULL -- obsolete
539 ,x_start_date => next_period(x_end_date,
540 oldrow.org_id)
541 ,x_end_date => oldrow.end_date );
542 cn_srp_per_quota_rc_pkg.delete_record
543 (x_srp_plan_assign_id => x_srp_plan_assign_id
544 ,x_quota_id => NULL
545 ,x_revenue_class_id => NULL
546 ,x_start_period_id => NULL
547 ,x_end_period_id => NULL
548 ,x_start_date => next_period(x_end_date,
549 oldrow.org_id)
550 ,x_end_date => oldrow.end_date );
551 ELSIF x_end_date > oldrow.end_date THEN
552 -- extend end_date
553 SELECT MIN(start_date)
554 INTO l_next_start_date
555 FROM cn_acc_period_statuses_v
556 WHERE period_status IN ('F', 'O')
557 AND org_id = oldrow.org_id;
558
559 IF x_end_date > l_next_start_date THEN
560
561 cn_srp_period_quotas_pkg.insert_record
562 (x_srp_plan_assign_id => x_srp_plan_assign_id
563 ,x_quota_id => NULL
564 ,x_start_period_id => NULL -- obsolete
565 ,x_end_period_id => NULL -- obsolete
566 ,x_start_date => next_period(oldrow.end_date,
567 oldrow.org_id)
568 ,x_end_date => x_end_date );
569 cn_srp_per_quota_rc_pkg.insert_record
570 (x_srp_plan_assign_id => x_srp_plan_assign_id
571 ,x_quota_id => NULL
572 ,x_revenue_class_id => NULL
573 ,x_start_period_id => NULL
574 ,x_end_period_id => NULL
575 ,x_start_date => next_period(oldrow.end_date,
576 oldrow.org_id)
577 ,x_end_date => x_end_date);
578 END IF;
579 ELSE
580 -- shorten end_date
581 cn_srp_period_quotas_pkg.delete_record
582 ( x_srp_plan_assign_id => x_srp_plan_assign_id
583 ,x_quota_id => NULL
584 ,x_start_period_id => NULL -- obsolete
585 ,x_end_period_id => NULL -- obsolete
586 ,x_start_date => next_period(x_end_date,
587 oldrow.org_id)
588 ,x_end_date => oldrow.end_date );
589 cn_srp_per_quota_rc_pkg.delete_record
590 (x_srp_plan_assign_id => x_srp_plan_assign_id
591 ,x_quota_id => NULL
592 ,x_revenue_class_id => NULL
593 ,x_start_period_id => NULL
594 ,x_end_period_id => NULL
595 ,x_start_date => next_period(x_end_date,
596 oldrow.org_id)
597 ,x_end_date => oldrow.end_date );
598 END IF;
599 ELSE
600 -- start_date changed, delete/add the whole set
601 -- Remove all assignments for this plan/salesrep
602
603 -- cascades to per_quota_rc
604 cn_srp_per_quota_rc_pkg.delete_record
605 (x_srp_plan_assign_id => x_srp_plan_assign_id
606 ,x_quota_id => NULL
607 ,x_revenue_class_id => NULL
608 ,x_start_period_id => NULL
609 ,x_end_period_id => NULL
610 ,x_start_date => oldrow.start_date
611 ,x_end_date => oldrow.end_date );
612 cn_srp_period_quotas_pkg.delete_record
613 ( x_srp_plan_assign_id => x_srp_plan_assign_id
614 ,x_quota_id => NULL
615 ,x_start_period_id => NULL -- obsolete
616 ,x_end_period_id => NULL -- obsolete
617 ,x_start_date => oldrow.start_date
618 ,x_end_date => oldrow.end_date );
619
620 cn_srp_period_quotas_pkg.insert_record
621 (x_srp_plan_assign_id => x_srp_plan_assign_id
622 ,x_quota_id => NULL
623 ,x_start_period_id => NULL -- obsolete
624 ,x_end_period_id => NULL -- obsolete
625 ,x_start_date => x_start_date
626 ,x_end_date => x_end_date );
627 cn_srp_per_quota_rc_pkg.insert_record
628 (x_srp_plan_assign_id => x_srp_plan_assign_id
629 ,x_quota_id => NULL
630 ,x_revenue_class_id => NULL
631 ,x_start_period_id => NULL
632 ,x_end_period_id => NULL
633 ,x_start_date => x_start_date
634 ,x_end_date => x_end_date);
635 END IF;
636 END IF ;
637
638 END UPDATE_ROW;
639
640 -- -------------------------------------------------------------------------+
641 -- Procedure Name
642 -- DELETE_ROW
643 -- Purpose
644 --
645 -- History
646 --
647 -- -------------------------------------------------------------------------+
648 PROCEDURE DELETE_ROW (X_SRP_PLAN_ASSIGN_ID IN NUMBER) IS
649 BEGIN
650
651 -- delete child rec is called in API:CN_SRP_PLAN_ASSIGNS_PVT
652 -- cn_srp_quota_assigns_pkg.delete_record
653
654 DELETE FROM CN_SRP_PLAN_ASSIGNS_ALL
655 WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID;
656
657 IF (SQL%NOTFOUND) THEN
658 RAISE NO_DATA_FOUND;
659 END IF;
660
661 END DELETE_ROW;
662
663 END CN_SRP_PLAN_ASSIGNS_PKG;