[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_RULE_UPLIFTS_PKG
Source
1 PACKAGE BODY cn_quota_rule_uplifts_pkg AS
2 /* $Header: cnpliqrub.pls 120.2 2005/08/05 00:32:02 fmburu ship $ */
3 /*
4 Date Name Description
5 ------------------------------------------------------------------------------+
6 29-APR-99 S Kumar Create/Update/Delete/Lock
7 APR-2005 Fred mburu refactor
8 */
9 ------------------------------------------------------------------------------+
10 -- Variables
11 ------------------------------------------------------------------------------+
12 g_program_type VARCHAR2 (30) := NULL;
13
14 ------------------------------------------------------------------------------+
15 -- Private Routines
16 ------------------------------------------------------------------------------+
17 -- Name
18 --
19 -- Purpose
20 --
21 -- Notes
22 --
23 --
24 PROCEDURE get_uid (
25 x_quota_rule_uplift_id IN OUT NOCOPY NUMBER
26 )
27 IS
28 BEGIN
29 SELECT cn_quota_rule_uplifts_s.NEXTVAL
30 INTO x_quota_rule_uplift_id
31 FROM SYS.DUAL;
32 END get_uid;
33
34 -- Name
35 --
36 -- Purpose
37 --
38 -- Notes
39 --
40 ------------------------------------------------------------------------------+
41 -- Insert_record
42 ------------------------------------------------------------------------------+
43 PROCEDURE INSERT_RECORD (
44 x_org_id NUMBER,
45 x_quota_rule_uplift_id IN OUT NOCOPY NUMBER,
46 x_quota_rule_id NUMBER,
47 x_last_update_date DATE,
48 x_last_updated_by NUMBER,
49 x_creation_date DATE,
50 x_created_by NUMBER,
51 x_last_update_login NUMBER,
52 x_start_date DATE,
53 x_end_date DATE,
54 x_payment_factor NUMBER,
55 x_quota_factor NUMBER,
56 x_start_date_old DATE,
57 x_end_date_old DATE,
58 x_object_version_number OUT NOCOPY NUMBER
59 )
60 IS
61 BEGIN
62 get_uid (x_quota_rule_uplift_id);
63 x_object_version_number := 1;
64
65 INSERT INTO cn_quota_rule_uplifts
66 (org_id,
67 quota_rule_uplift_id,
68 quota_rule_id,
69 payment_factor,
70 quota_factor,
71 start_date,
72 end_date,
73 last_update_date,
74 last_updated_by,
75 creation_date,
76 created_by,
77 last_update_login,
78 object_version_number
79 )
80 VALUES (x_org_id,
81 x_quota_rule_uplift_id,
82 x_quota_rule_id,
83 x_payment_factor,
84 x_quota_factor,
85 x_start_date,
86 x_end_date,
87 x_last_update_date,
88 x_last_updated_by,
89 x_creation_date,
90 x_created_by,
91 x_last_update_login,
92 x_object_version_number
93 );
94
95 -- create the srp quota rule uplifts
96 cn_srp_rule_uplifts_pkg.INSERT_RECORD (p_srp_plan_assign_id => NULL,
97 p_quota_id => NULL,
98 p_quota_rule_id => x_quota_rule_id,
99 p_quota_rule_uplift_id => x_quota_rule_uplift_id
100 );
101 END INSERT_RECORD;
102
103 -- Name
104 --
105 -- Purpose
106 --
107 -- Notes
108 --
109 --
110 ------------------------------------------------------------------------------+
111 -- Update_Record
112 ------------------------------------------------------------------------------+
113 PROCEDURE UPDATE_RECORD (
114 x_org_id NUMBER,
115 x_quota_rule_uplift_id NUMBER,
116 x_quota_rule_id NUMBER,
117 x_last_update_date DATE,
118 x_last_updated_by NUMBER,
119 x_last_update_login NUMBER,
120 x_start_date DATE,
121 x_start_date_old DATE,
122 x_payment_factor NUMBER,
123 x_payment_factor_old NUMBER,
124 x_quota_factor NUMBER,
125 x_quota_factor_old NUMBER,
126 x_end_date DATE,
127 x_end_date_old DATE,
128 x_object_version_number OUT NOCOPY NUMBER
129 )
130 IS
131 CURSOR c IS
132 SELECT *
133 FROM cn_quota_rule_uplifts
134 WHERE quota_rule_uplift_id = x_quota_rule_uplift_id;
135
136 recinfo c%ROWTYPE;
137 BEGIN
138
139 OPEN c;
140 FETCH c INTO recinfo;
141 CLOSE c;
142
143 x_object_version_number := NVL (recinfo.object_version_number, 1) + 1;
144
145 UPDATE cn_quota_rule_uplifts
146 SET payment_factor = x_payment_factor,
147 quota_factor = x_quota_factor,
148 start_date = x_start_date,
149 end_date = x_end_date,
150 last_update_date = x_last_update_date,
151 last_updated_by = x_last_updated_by,
152 last_update_login = x_last_update_login,
153 object_version_number = x_object_version_number
154 WHERE quota_rule_uplift_id = x_quota_rule_uplift_id;
155
156 IF (SQL%NOTFOUND)
157 THEN
158 RAISE NO_DATA_FOUND;
159 END IF;
160
161 cn_srp_rule_uplifts_pkg.UPDATE_RECORD (p_quota_rule_uplift_id => x_quota_rule_uplift_id,
162 p_quota_factor => x_quota_factor,
163 p_payment_factor => x_payment_factor
164 );
165 END UPDATE_RECORD;
166
167 ------------------------------------------------------------------------------+
168 -- Lock Record
169 ------------------------------------------------------------------------------+
170 PROCEDURE LOCK_RECORD (
171 x_org_id NUMBER,
172 x_quota_rule_uplift_id NUMBER,
173 x_quota_rule_id NUMBER,
174 x_start_date DATE,
175 x_end_date DATE,
176 x_payment_factor NUMBER,
177 x_quota_factor NUMBER
178 )
179 IS
180 BEGIN
181 NULL;
182 END LOCK_RECORD;
183
184 -- Name
185 --
186 -- Purpose
187 --
188 -- Notes
189 -- Delete the quota rule uplift can be done in three ways
190 -- 1. delete the given quota rule uplift only
191 -- 2. delete all uplift factor for given quota rule
192 -- 3. delete all uplift factor including all quota rules for a quota
193 ------------------------------------------------------------------------------+
194 -- Delete_record
195 ------------------------------------------------------------------------------+
196 PROCEDURE DELETE_RECORD (
197 x_quota_rule_uplift_id NUMBER,
198 x_quota_rule_id NUMBER,
199 x_quota_id NUMBER
200 )
201 IS
202 BEGIN
203 --clku, bug 2257692
204 IF x_quota_rule_uplift_id IS NOT NULL
205 THEN
206 -- delete with id
207 DELETE FROM cn_quota_rule_uplifts
208 WHERE quota_rule_uplift_id = x_quota_rule_uplift_id;
209
210 IF (SQL%NOTFOUND)
211 THEN
212 RAISE NO_DATA_FOUND;
213 END IF;
214 ELSIF x_quota_rule_id IS NOT NULL
215 THEN
216 -- Deleting an entire quota rule Uplifts
217 DELETE FROM cn_quota_rule_uplifts
218 WHERE quota_rule_id = x_quota_rule_id;
219 ELSIF x_quota_id IS NOT NULL
220 THEN
221 DELETE FROM cn_quota_rule_uplifts cqru
222 WHERE EXISTS (SELECT 1
223 FROM cn_quota_rules crq
224 WHERE crq.quota_rule_id = cqru.quota_rule_id AND crq.quota_id = x_quota_id);
225 END IF;
226
227 cn_srp_rule_uplifts_pkg.DELETE_RECORD (p_srp_plan_assign_id => NULL,
228 p_quota_id => x_quota_id,
229 p_quota_rule_id => x_quota_rule_id,
230 p_quota_rule_uplift_id => x_quota_rule_uplift_id
231 );
232 END DELETE_RECORD;
233
234 ------------------------------------------------------------------------------+
235 -- Public Routine Bodies
236 ------------------------------------------------------------------------------+
237
238 -- Procedure Name
239 --
240 -- Purpose
241 --
242 --
243 -- Notes
244 --
245 --
246 PROCEDURE begin_record (
247 x_operation VARCHAR2,
248 x_org_id NUMBER,
249 x_quota_rule_uplift_id IN OUT NOCOPY NUMBER,
250 x_quota_rule_id NUMBER,
251 x_quota_rule_id_old NUMBER,
252 x_start_date DATE,
253 x_start_date_old DATE,
254 x_end_date DATE,
255 x_end_date_old DATE,
256 x_payment_factor NUMBER,
257 x_payment_factor_old NUMBER,
258 x_quota_factor NUMBER,
259 x_quota_factor_old NUMBER,
260 x_last_updated_by NUMBER,
261 x_creation_date DATE,
262 x_created_by NUMBER,
263 x_last_update_login NUMBER,
264 x_last_update_date DATE,
265 x_program_type VARCHAR2,
266 x_status_code VARCHAR2,
267 x_object_version_number OUT NOCOPY NUMBER
268 )
269 IS
270 BEGIN
271 g_program_type := x_program_type;
272
273 IF x_operation = 'INSERT'
274 THEN
275 INSERT_RECORD (x_org_id,
276 x_quota_rule_uplift_id,
277 x_quota_rule_id,
278 x_last_update_date,
279 x_last_updated_by,
280 x_creation_date,
281 x_created_by,
282 x_last_update_login,
283 x_start_date,
284 x_end_date,
285 x_payment_factor,
286 x_quota_factor,
287 x_start_date_old,
288 x_end_date_old,
289 x_object_version_number
290 );
291 ELSIF x_operation = 'UPDATE'
292 THEN
293 UPDATE_RECORD (x_org_id,
294 x_quota_rule_uplift_id,
295 x_quota_rule_id,
296 x_last_update_date,
297 x_last_updated_by,
298 x_last_update_login,
299 x_start_date,
300 x_start_date_old,
301 x_payment_factor,
302 x_payment_factor_old,
303 x_quota_factor,
304 x_quota_factor_old,
305 x_end_date,
306 x_end_date_old,
307 x_object_version_number
308 );
309 ELSIF x_operation = 'LOCK'
310 THEN
311 LOCK_RECORD (x_org_id, x_quota_rule_uplift_id, x_quota_rule_id, x_start_date, x_end_date, x_payment_factor, x_quota_factor);
312 ELSIF x_operation = 'DELETE'
313 THEN
314 DELETE_RECORD (x_quota_rule_uplift_id => x_quota_rule_uplift_id, x_quota_rule_id => NULL, x_quota_id => NULL);
315 END IF;
316 END begin_record;
317
318 -- Name
319 --
320 -- Purpose
321 --
322 -- Notes
323 --
324 --
325 PROCEDURE end_record (
326 x_rowid VARCHAR2,
327 x_quota_rule_uplift_id NUMBER,
328 x_quota_rule_id NUMBER,
329 x_start_date DATE,
330 x_end_date DATE,
331 x_payment_factor NUMBER,
332 x_quota_factor NUMBER,
333 x_program_type VARCHAR2
334 )
335 IS
336 BEGIN
337 -- Saves passing it around
338 g_program_type := x_program_type;
339 -- no validation perfromed here. All validation aimed at changing the
340 -- status of the quota is performed in the quota package.
341 NULL;
342 END end_record;
343 END cn_quota_rule_uplifts_pkg;