[Home] [Help]
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
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;
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
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;
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;
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
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
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
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
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:
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,
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
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
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
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
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
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
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
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
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
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
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
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:
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
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:
533: END IF;
534:
535: END Lock_Record;
536:
537: END cn_srp_quota_rules_pkg;