1 PACKAGE BODY cn_srp_rule_uplifts_pkg as
2 /* $Header: cnsrprub.pls 120.0 2005/06/06 17:56:22 appldev noship $ */
3
4 /*
5 Date Name Description
6 ----------------------------------------------------------------------------
7 02-JUN-99 S Kumar
8 23-Mar-01 Zack Added srp_quota_assign_id in the where clause for performance fix.
9
10 */
11 -- Procedure Name
12 -- Insert_record
13 -- Purpose
14 -- insert the Quota Rule Uplift from
15 -- two places
16 -- 1. -- Inserting a new cn_quota_assigns
17 -- 2. -- Inserting a new plan assignment
18 -- 3. -- Inserting a new Quota Rule Uplifts
19 -- Notes
20 --
21
22 PROCEDURE insert_record
23 (
24 p_srp_plan_assign_id NUMBER
25 ,p_quota_id NUMBER
26 ,p_quota_rule_id NUMBER
27 ,p_quota_rule_uplift_id NUMBER := NULL
28 ) IS
29
30 BEGIN
31
32 IF ( p_srp_plan_assign_id IS NOT NULL AND p_quota_id IS NOT NULL) THEN
33
34 -- Inserting a new cn_quota_assigns
35
36 INSERT INTO cn_srp_rule_uplifts_all
37 (
38 srp_rule_uplift_id
39 ,srp_quota_rule_id
40 ,quota_rule_uplift_id
41 ,payment_factor
42 ,quota_factor
43 ,creation_date
44 ,created_by
45 ,last_updated_by
46 ,last_update_date
47 ,last_update_login
48 ,org_id
49 )
50 SELECT
51 cn_srp_rule_uplifts_s.nextval
52 ,sqr.srp_quota_rule_id
53 ,qru.quota_rule_uplift_id
54 ,qru.payment_factor
55 ,qru.quota_factor
56 ,Sysdate
57 ,fnd_global.user_id
58 ,fnd_global.user_id
59 ,Sysdate
60 ,fnd_global.login_id
61 ,sqa.org_id
62 FROM cn_srp_quota_assigns_all sqa
63 ,cn_srp_quota_rules_all sqr
64 ,cn_quota_rule_uplifts_all qru
65 WHERE sqa.srp_plan_assign_id = p_srp_plan_assign_id
66 AND sqa.quota_id = p_quota_id
67 AND sqa.srp_quota_assign_id = sqr.srp_quota_assign_id
68 AND sqa.srp_plan_assign_id = sqr.srp_plan_assign_id
69 AND sqr.quota_rule_id = qru.quota_rule_id
70 ;
71
72 ELSIF ( p_srp_plan_assign_id IS NOT NULL AND p_quota_id IS NULL) THEN
73 -- Inserting a new plan assignment
74
75 INSERT INTO cn_srp_rule_uplifts_all
76 (
77 srp_rule_uplift_id
78 ,srp_quota_rule_id
79 ,quota_rule_uplift_id
80 ,payment_factor
81 ,quota_factor
82 ,creation_date
83 ,created_by
84 ,last_updated_by
85 ,last_update_date
86 ,last_update_login
87 ,org_id
88 )
89 SELECT
90 cn_srp_rule_uplifts_s.nextval
91 ,sqr.srp_quota_rule_id
92 ,qru.quota_rule_uplift_id
93 ,qru.payment_factor
94 ,qru.quota_factor
95 ,Sysdate
96 ,fnd_global.user_id
97 ,fnd_global.user_id
98 ,Sysdate
99 ,fnd_global.login_id
100 ,sqa.org_id
101 FROM cn_srp_quota_assigns_all sqa
102 ,cn_srp_quota_rules_all sqr
103 ,cn_quota_rule_uplifts_all qru
104 WHERE sqa.srp_plan_assign_id = p_srp_plan_assign_id
105 AND sqa.srp_quota_assign_id = sqr.srp_quota_assign_id
106 AND sqa.srp_plan_assign_id = sqr.srp_plan_assign_id
107 AND sqr.quota_rule_id = qru.quota_rule_id
108 ;
109
110 ELSIF ( p_quota_rule_id IS NOT NULL AND p_quota_rule_uplift_id IS NOT NULL) THEN
111
112 -- Inserting a new Quota Rule Uplifts
113
114 IF p_quota_rule_id IS NOT NULL THEN
115
116 INSERT INTO cn_srp_rule_uplifts_all
117 ( srp_rule_uplift_id
118 ,srp_quota_rule_id
119 ,quota_rule_uplift_id
120 ,payment_factor
121 ,quota_factor
122 ,creation_date
123 ,created_by
124 ,last_updated_by
125 ,last_update_date
126 ,last_update_login
127 ,org_id)
128 SELECT
129 cn_srp_rule_uplifts_s.nextval
130 ,sqr.srp_quota_rule_id
131 ,qru.quota_rule_uplift_id
132 ,qru.payment_factor
133 ,qru.quota_factor
134 ,Sysdate
135 ,fnd_global.user_id
136 ,fnd_global.user_id
137 ,Sysdate
138 ,fnd_global.login_id
139 ,sqr.org_id
140 FROM cn_srp_quota_rules_all sqr
141 ,cn_quota_rule_uplifts_all qru
142 WHERE sqr.quota_rule_id = p_quota_rule_id
143 AND qru.quota_rule_id = p_quota_rule_id
144 AND qru.quota_rule_id = sqr.quota_rule_id --bugfix 3633243
145 AND qru.quota_rule_uplift_id = p_quota_rule_uplift_id
146 ;
147 END IF;
148 -- clku, handle the case of inserting srp quota rules which already have
149 -- uplift factor at PE Level. Bug 2788644
150 ELSIF ( p_quota_rule_id IS NOT NULL AND p_quota_rule_uplift_id IS NULL) THEN
151
152 INSERT INTO cn_srp_rule_uplifts_all
153 ( srp_rule_uplift_id
154 ,srp_quota_rule_id
155 ,quota_rule_uplift_id
156 ,payment_factor
157 ,quota_factor
158 ,creation_date
159 ,created_by
160 ,last_updated_by
161 ,last_update_date
162 ,last_update_login
163 ,org_id)
164 SELECT
165 cn_srp_rule_uplifts_s.nextval
166 ,sqr.srp_quota_rule_id
167 ,qru.quota_rule_uplift_id
168 ,qru.payment_factor
169 ,qru.quota_factor
170 ,Sysdate
171 ,fnd_global.user_id
172 ,fnd_global.user_id
173 ,Sysdate
174 ,fnd_global.login_id
175 ,sqr.org_id
176 FROM cn_srp_quota_rules_all sqr
177 ,cn_quota_rule_uplifts_all qru
178 WHERE sqr.quota_rule_id = qru.quota_rule_id
179 AND qru.quota_rule_id = p_quota_rule_id;
180
181
182 END IF;
183
184 END insert_record;
185
186 -- Procedure Name
187 -- Update_record
188 -- Purpose
189 -- Upate the Quota Rule Uplift from from
190 -- Notes
191 --
192
193 PROCEDURE update_record(
194 p_srp_rule_uplift_id NUMBER
195 ,p_payment_factor NUMBER
196 ,p_quota_factor NUMBER
197 ,p_last_update_date DATE
198 ,p_last_updated_by NUMBER
199 ,p_last_update_login NUMBER) IS
200
201 BEGIN
202 IF p_srp_rule_uplift_id IS NOT NULL THEN
203
204 -- Called from srp rule Uplift block
205 UPDATE cn_srp_rule_uplifts_all
206 SET
207 -- Should be an optional column as it can be null for
208 -- quota types 'revenue' and 'draw'. But it is mandatory and
209 -- this nvl protects against a null value coming back from the
210 -- form
211 payment_factor = p_payment_factor
212 ,quota_factor = p_quota_factor
213 ,last_update_date = p_last_update_date
214 ,last_updated_by = p_last_updated_by
215 ,last_update_login = p_last_update_login
216 WHERE srp_rule_uplift_id = p_srp_rule_uplift_id
217 ;
218
219 IF (sql%notfound) THEN
220 raise no_data_found;
221 END IF;
222 END IF;
223
224 END update_record;
225
226 -- Procedure Name
227 -- Delete_record
228 -- Purpose
229 -- Delete will be called from different place
230 -- 1. Delete the cn_quota_assigns
231 -- 2. Delete the srp_plan_assigns
232 -- 3. delete the cn_quota_rules
233 -- 4. delete the quota_rule_uplifts
234 -- Notes
235 --
236
237 PROCEDURE Delete_record
238 (
239 p_srp_plan_assign_id NUMBER
240 ,p_quota_id NUMBER
241 ,p_quota_rule_id NUMBER
242 ,p_quota_rule_uplift_id NUMBER := NULL
243 ) IS
244
245 BEGIN
246
247 IF ( p_srp_plan_assign_id IS NOT NULL AND
248 p_quota_id IS NOT NULL AND
249 p_quota_rule_id IS NULL AND
250 p_quota_rule_uplift_id IS NULL ) THEN
251
252 -- cn_quota_assigns Record has been deleted
253 -- for each srp plan assign record
254 DELETE FROM cn_srp_rule_uplifts_all sru
255 WHERE sru.srp_quota_rule_id IN
256 ( SELECT sqr.srp_quota_rule_id
257 FROM cn_srp_quota_rules_all sqr
258 , cn_srp_quota_assigns sqa
259 WHERE sqa.srp_quota_assign_id = sqr.srp_quota_assign_id
260 AND sqa.srp_plan_assign_id = sqr.srp_plan_assign_id
261 AND sqa.srp_plan_assign_id = p_srp_plan_assign_id
262 AND sqa.quota_id = p_quota_id )
263 ;
264 ELSIF (p_srp_plan_assign_id IS NOT NULL
265 AND p_quota_id IS NULL
266 AND p_quota_rule_id IS NULL) THEN
267
268 -- cn_srp_plan_assigns record has been deleted
269 DELETE FROM cn_srp_rule_uplifts_all sru
270 WHERE sru.srp_quota_rule_id IN
271 ( SELECT sqr.srp_quota_rule_id
272 FROM cn_srp_quota_rules_all sqr
273 WHERE sqr.srp_plan_assign_id = p_srp_plan_assign_id )
274 ;
275
276 ELSIF ( p_srp_plan_assign_id IS NULL
277 AND p_quota_id IS NOT NULL
278 AND p_quota_rule_id IS NOT NULL) THEN
279
280 -- cn_quota_rules record deleted
281 DELETE FROM cn_srp_rule_uplifts_all sru
282 WHERE sru.srp_quota_rule_id IN
283 (SELECT sqr.srp_quota_rule_id
284 FROM cn_srp_quota_rules_all sqr, cn_quota_rules_all qr
285 WHERE sqr.quota_rule_id = p_quota_rule_id
286 AND sqr.quota_rule_id = qr.quota_rule_id
287 AND sqr.revenue_class_id = qr.revenue_class_id);
288
289 ELSIF ( p_quota_rule_uplift_id IS NOT NULL) THEN
290
291 -- cn_quota_rule_uplifts record deleted
292
293 DELETE FROM cn_srp_rule_uplifts_all
294 WHERE quota_rule_uplift_id = p_quota_rule_uplift_id
295 ;
296
297 ELSIF ( p_quota_id IS NOT NULL ) THEN
298 DELETE FROM cn_srp_rule_uplifts_all sru
299 WHERE sru.srp_quota_rule_id IN
300 (SELECT sqr.srp_quota_rule_id
301 FROM cn_quota_rules_all qr,
302 cn_srp_quota_rules_all sqr
303 WHERE sqr.quota_rule_id = qr.quota_rule_id
304 AND sqr.revenue_class_id = qr.revenue_class_id
305 AND quota_id = p_quota_id )
306 ;
307
308 END IF;
309
310 END delete_record;
311 ----------------------------------------------------------------------------
312 -- PROCEDURE UPDATE_RECORD
313 ----------------------------------------------------------------------------
314 PROCEDURE update_record ( p_quota_rule_uplift_id NUMBER
315 ,p_quota_factor NUMBER
316 ,p_payment_factor NUMBER) IS
317 BEGIN
318 UPDATE cn_srp_rule_uplifts_all u
319 set u.payment_factor = p_payment_factor
320 , u.quota_factor = p_quota_factor
321 WHERE u.quota_rule_uplift_id = p_quota_rule_uplift_id
322 AND EXISTS (SELECT 'quota rule uplift belongs to a uncustomized quota'
323 FROM cn_srp_quota_assigns_all q,
324 cn_srp_quota_rules_all r
325 WHERE q.srp_quota_assign_id = r.srp_quota_assign_id
326 AND r.srp_quota_rule_id = u.srp_quota_rule_id
327 AND q.customized_flag = 'N')
328 ;
329
330 END update_record;
331
332
333 END cn_srp_rule_uplifts_pkg;
[Home] [Help]