[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_SUB_QUOTAS_PKG
Source
1 PACKAGE BODY CN_CALC_SUB_QUOTAS_PKG as
2 /* $Header: cnsbpeb.pls 120.1 2005/08/02 17:53:27 ymao noship $ */
3 G_LAST_UPDATE_DATE DATE := sysdate;
4 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
5 G_CREATION_DATE DATE := sysdate;
6 G_CREATED_BY NUMBER := fnd_global.user_id;
7 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
8 --
9 --
10 --
11 -- This Procedure is called to
12 -- 1. Insert
13 -- 2. Update
14 -- 3. Delete
15 -- Records into Table cn_calc_sub_quotas
16 --
17 --
18
19
20 Procedure Insert_row ( p_calc_sub_quota_id NUMBER,
21 p_calc_sub_batch_id NUMBER,
22 p_quota_id NUMBER,
23 p_org_id NUMBER,
24 P_ATTRIBUTE_CATEGORY VARCHAR2,
25 P_ATTRIBUTE1 VARCHAR2,
26 P_ATTRIBUTE2 VARCHAR2,
27 P_ATTRIBUTE3 VARCHAR2,
28 P_ATTRIBUTE4 VARCHAR2,
29 P_ATTRIBUTE5 VARCHAR2,
30 P_ATTRIBUTE6 VARCHAR2,
31 P_ATTRIBUTE7 VARCHAR2,
32 P_ATTRIBUTE8 VARCHAR2,
33 P_ATTRIBUTE9 VARCHAR2,
34 P_ATTRIBUTE10 VARCHAR2,
35 P_ATTRIBUTE11 VARCHAR2,
36 P_ATTRIBUTE12 VARCHAR2,
37 P_ATTRIBUTE13 VARCHAR2,
38 P_ATTRIBUTE14 VARCHAR2,
39 P_ATTRIBUTE15 VARCHAR2,
40 P_CREATED_BY NUMBER ,
41 P_CREATION_DATE DATE ,
42 P_LAST_UPDATE_LOGIN NUMBER ,
43 P_LAST_UPDATE_DATE DATE ,
44 P_LAST_UPDATED_BY NUMBER
45 ) IS
46 l_calc_sub_quota_id NUMBER(15);
47 BEGIN
48
49 IF p_calc_sub_quota_id IS NOT NULL THEN
50 l_calc_sub_quota_id := p_calc_sub_quota_id;
51 ELSE
52 SELECT cn_calc_sub_quotas_s.NEXTVAL
53 INTO l_calc_sub_quota_id
54 FROM dual;
55 END IF;
56
57 INSERT INTO cn_calc_sub_quotas
58 ( calc_sub_quota_id,
59 calc_sub_batch_id,
60 quota_id,
61 org_id
62 ,attribute_category
63 ,attribute1
64 ,attribute2
65 ,attribute3
66 ,attribute4
67 ,attribute5
68 ,attribute6
69 ,attribute7
70 ,attribute8
71 ,attribute9
72 ,attribute10
73 ,attribute11
74 ,attribute12
75 ,attribute13
76 ,attribute14
77 ,attribute15
78 ,created_by
79 ,creation_date
80 ,last_update_login
81 ,last_update_date
82 ,last_updated_by
83 )
84 VALUES ( l_calc_sub_quota_id,
85 p_calc_sub_batch_id,
86 p_quota_id,
87 p_org_id
88 ,p_attribute_category
89 ,p_attribute1
90 ,p_attribute2
91 ,p_attribute3
92 ,p_attribute4
93 ,p_attribute5
94 ,p_attribute6
95 ,p_attribute7
96 ,p_attribute8
97 ,p_attribute9
98 ,p_attribute10
99 ,p_attribute11
100 ,p_attribute12
101 ,p_attribute13
102 ,p_attribute14
103 ,p_attribute15
104 ,Nvl( p_created_by,g_created_by)
105 ,Nvl( p_creation_date,g_creation_date )
106 ,Nvl( p_last_update_login, g_last_update_login )
107 ,Nvl( p_last_update_date, g_last_update_date )
108 ,Nvl( p_last_updated_by, g_last_updated_by )
109 ) ;
110
111
112 END insert_row;
113
114
115 Procedure Update_row ( p_calc_sub_quota_id NUMBER,
116 p_calc_sub_batch_id NUMBER,
117 p_quota_id NUMBER,
118 P_ATTRIBUTE_CATEGORY VARCHAR2,
119 P_ATTRIBUTE1 VARCHAR2,
120 P_ATTRIBUTE2 VARCHAR2,
121 P_ATTRIBUTE3 VARCHAR2,
122 P_ATTRIBUTE4 VARCHAR2,
123 P_ATTRIBUTE5 VARCHAR2,
124 P_ATTRIBUTE6 VARCHAR2,
125 P_ATTRIBUTE7 VARCHAR2,
126 P_ATTRIBUTE8 VARCHAR2,
127 P_ATTRIBUTE9 VARCHAR2,
128 P_ATTRIBUTE10 VARCHAR2,
129 P_ATTRIBUTE11 VARCHAR2,
130 P_ATTRIBUTE12 VARCHAR2,
131 P_ATTRIBUTE13 VARCHAR2,
132 P_ATTRIBUTE14 VARCHAR2,
133 P_ATTRIBUTE15 VARCHAR2,
134 P_CREATED_BY NUMBER ,
135 P_CREATION_DATE DATE ,
136 P_LAST_UPDATE_LOGIN NUMBER ,
137 P_LAST_UPDATE_DATE DATE ,
138 P_LAST_UPDATED_BY NUMBER
139 ) IS
140
141 BEGIN
142
143 UPDATE cn_calc_sub_quotas_all SET
144 calc_sub_quota_id = p_calc_sub_quota_id,
145 calc_sub_batch_id = p_calc_sub_batch_id,
146 quota_id = p_quota_id
147 ,attribute_category = p_attribute_category
148 ,attribute1 = p_attribute1
149 ,attribute2 = p_attribute2
150 ,attribute3 = p_attribute3
151 ,attribute4 = p_attribute4
152 ,attribute5 = p_attribute5
153 ,attribute6 = p_attribute6
154 ,attribute7 = p_attribute7
155 ,attribute8 = p_attribute8
156 ,attribute9 = p_attribute9
157 ,attribute10 = p_attribute10
158 ,attribute11 = p_attribute11
159 ,attribute12 = p_attribute12
160 ,attribute13 = p_attribute13
161 ,attribute14 = p_attribute14
162 ,attribute15 = p_attribute15
163 ,created_by = Nvl( p_created_by,g_created_by)
164 ,creation_date = Nvl( p_creation_date,g_creation_date )
165 ,last_update_login = Nvl( p_last_update_login, g_last_update_login )
166 ,last_update_date = Nvl( p_last_update_date, g_last_update_date )
167 ,last_updated_by = Nvl( p_last_updated_by, g_last_updated_by )
168 WHERE calc_sub_quota_id = p_calc_sub_quota_id;
169
170 END update_row;
171
172 Procedure delete_row ( p_calc_sub_quota_id NUMBER ) IS
173
174 BEGIN
175 DELETE cn_calc_sub_quotas_all
176 WHERE calc_sub_quota_id = p_calc_sub_quota_id;
177
178 IF (sql%notfound) THEN
179 raise no_data_found;
180 END IF;
181 END delete_row;
182
183 Procedure lock_row ( p_calc_sub_quota_id NUMBER,
184 p_calc_sub_batch_id NUMBER,
185 p_quota_id NUMBER,
186 P_ATTRIBUTE_CATEGORY VARCHAR2,
187 P_ATTRIBUTE1 VARCHAR2,
188 P_ATTRIBUTE2 VARCHAR2,
189 P_ATTRIBUTE3 VARCHAR2,
190 P_ATTRIBUTE4 VARCHAR2,
191 P_ATTRIBUTE5 VARCHAR2,
192 P_ATTRIBUTE6 VARCHAR2,
193 P_ATTRIBUTE7 VARCHAR2,
194 P_ATTRIBUTE8 VARCHAR2,
195 P_ATTRIBUTE9 VARCHAR2,
196 P_ATTRIBUTE10 VARCHAR2,
197 P_ATTRIBUTE11 VARCHAR2,
198 P_ATTRIBUTE12 VARCHAR2,
199 P_ATTRIBUTE13 VARCHAR2,
200 P_ATTRIBUTE14 VARCHAR2,
201 P_ATTRIBUTE15 VARCHAR2,
202 P_CREATED_BY NUMBER ,
203 P_CREATION_DATE DATE ,
204 P_LAST_UPDATE_LOGIN NUMBER ,
205 P_LAST_UPDATE_DATE DATE ,
206 P_LAST_UPDATED_BY NUMBER
207 ) IS
208 CURSOR C IS
209 SELECT * FROM cn_calc_sub_quotas_all
210 WHERE calc_sub_quota_id = p_calc_sub_quota_id
211 FOR UPDATE OF calc_sub_quota_id NOWAIT;
212
213 Recinfo C%ROWTYPE;
214
215 BEGIN
216 OPEN C;
217 FETCH C INTO Recinfo;
218
219 IF C%NOTFOUND THEN
220 CLOSE C;
221 fnd_message.set_name('FND','FORM_RECORD_DELETED');
222 app_exception.raise_exception;
223 END IF;
224 CLOSE C;
225
226 IF ( recinfo.calc_sub_batch_id = p_calc_sub_batch_id
227 OR ( recinfo.calc_sub_batch_id IS NULL AND p_calc_sub_batch_id IS NULL) )
228 AND ( recinfo.calc_sub_quota_id = p_calc_sub_quota_id
229 OR ( recinfo.calc_sub_quota_id IS NULL AND p_calc_sub_quota_id IS NULL) )
230 AND ( recinfo.quota_id = p_quota_id
231 OR ( recinfo.quota_id IS NULL AND p_quota_id IS NULL) )
232 AND ( recinfo.attribute_category = p_attribute_category
233 OR ( recinfo.attribute_category IS NULL AND
234 p_attribute_category IS NULL) )
235 AND ( recinfo.attribute1 = p_attribute1
236 OR ( recinfo.attribute1 IS NULL AND p_attribute1 IS NULL) )
237 AND ( recinfo.attribute2 = p_attribute2
238 OR ( recinfo.attribute2 IS NULL AND p_attribute2 IS NULL) )
239 AND ( recinfo.attribute3 = p_attribute3
240 OR ( recinfo.attribute3 IS NULL AND p_attribute3 IS NULL) )
241
242 AND ( recinfo.attribute4 = p_attribute4
243 OR ( recinfo.attribute4 IS NULL AND p_attribute4 IS NULL) )
244
245 AND ( recinfo.attribute5 = p_attribute5
246 OR ( recinfo.attribute5 IS NULL AND p_attribute5 IS NULL) )
247
248 AND ( recinfo.attribute6 = p_attribute6
249 OR ( recinfo.attribute6 IS NULL AND p_attribute6 IS NULL) )
250
251 AND ( recinfo.attribute7 = p_attribute7
252 OR ( recinfo.attribute7 IS NULL AND p_attribute7 IS NULL) )
253
254 AND ( recinfo.attribute8 = p_attribute8
255 OR ( recinfo.attribute8 IS NULL AND p_attribute8 IS NULL) )
256
257 AND ( recinfo.attribute9 = p_attribute9
258 OR ( recinfo.attribute9 IS NULL AND p_attribute9 IS NULL) )
259
260 AND ( recinfo.attribute10 = p_attribute10
261 OR ( recinfo.attribute10 IS NULL AND p_attribute10 IS NULL) )
262
263 AND ( recinfo.attribute11 = p_attribute11
264 OR ( recinfo.attribute11 IS NULL AND p_attribute11 IS NULL) )
265
266 AND ( recinfo.attribute12 = p_attribute12
267 OR ( recinfo.attribute12 IS NULL AND p_attribute12 IS NULL) )
268
269 AND ( recinfo.attribute13 = p_attribute13
270 OR ( recinfo.attribute13 IS NULL AND p_attribute13 IS NULL) )
271
272 AND ( recinfo.attribute14 = p_attribute14
273 OR ( recinfo.attribute14 IS NULL AND p_attribute14 IS NULL) )
274
275 AND ( recinfo.attribute15 = p_attribute15
276 OR ( recinfo.attribute15 IS NULL AND p_attribute15 IS NULL) )
277
278 THEN
279 RETURN;
280 ELSE
281 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
282 app_exception.raise_exception;
283 END IF;
284
285 END lock_row;
286
287 --
288 Procedure Begin_Record ( P_OPERATION VARCHAR2,
289 p_calc_sub_quota_id NUMBER := NULL ,
290 p_calc_sub_batch_id NUMBER := NULL,
291 p_quota_id NUMBER := NULL,
292 p_org_id NUMBER,
293 P_ATTRIBUTE_CATEGORY VARCHAR2 := NULL,
294 P_ATTRIBUTE1 VARCHAR2 := NULL,
295 P_ATTRIBUTE2 VARCHAR2 := NULL,
296 P_ATTRIBUTE3 VARCHAR2 := NULL,
297 P_ATTRIBUTE4 VARCHAR2 := NULL,
298 P_ATTRIBUTE5 VARCHAR2 := NULL,
299 P_ATTRIBUTE6 VARCHAR2 := NULL,
300 P_ATTRIBUTE7 VARCHAR2 := NULL,
301 P_ATTRIBUTE8 VARCHAR2 := NULL,
302 P_ATTRIBUTE9 VARCHAR2 := NULL,
303 P_ATTRIBUTE10 VARCHAR2 := NULL,
304 P_ATTRIBUTE11 VARCHAR2 := NULL,
305 P_ATTRIBUTE12 VARCHAR2 := NULL,
306 P_ATTRIBUTE13 VARCHAR2 := NULL,
307 P_ATTRIBUTE14 VARCHAR2 := NULL,
308 P_ATTRIBUTE15 VARCHAR2 := NULL,
309 P_CREATED_BY NUMBER := NULL,
310 P_CREATION_DATE DATE := NULL,
311 P_LAST_UPDATE_LOGIN NUMBER := NULL,
312 P_LAST_UPDATE_DATE DATE := NULL,
313 P_LAST_UPDATED_BY NUMBER := NULL ) IS
314 BEGIN
315 IF p_operation = 'INSERT' THEN
316 insert_row ( p_calc_sub_quota_id,
317 p_calc_sub_batch_id,
318 p_quota_id,
319 p_org_id
320 ,p_attribute_category
321 ,p_attribute1
322 ,p_attribute2
323 ,p_attribute3
324 ,p_attribute4
325 ,p_attribute5
326 ,p_attribute6
327 ,p_attribute7
328 ,p_attribute8
329 ,p_attribute9
330 ,p_attribute10
331 ,p_attribute11
332 ,p_attribute12
333 ,p_attribute13
334 ,p_attribute14
335 ,p_attribute15
336 ,p_created_by
337 ,p_creation_date
338 ,p_last_update_login
342 update_row ( p_calc_sub_quota_id,
339 ,p_last_update_date
340 ,p_last_updated_by ) ;
341 ELSIF p_operation = 'UPDATE' THEN
343 p_calc_sub_batch_id,
344 p_quota_id
345 ,p_attribute_category
346 ,p_attribute1
347 ,p_attribute2
348 ,p_attribute3
349 ,p_attribute4
350 ,p_attribute5
351 ,p_attribute6
352 ,p_attribute7
353 ,p_attribute8
354 ,p_attribute9
355 ,p_attribute10
356 ,p_attribute11
357 ,p_attribute12
358 ,p_attribute13
359 ,p_attribute14
360 ,p_attribute15
361 ,p_created_by
362 ,p_creation_date
363 ,p_last_update_login
364 ,p_last_update_date
365 ,p_last_updated_by ) ;
366 ELSIF p_operation = 'DELETE' THEN
367 delete_row ( p_calc_sub_quota_id );
368 ELSIF p_operation = 'LOCK' THEN
369 lock_row ( p_calc_sub_quota_id,
370 p_calc_sub_batch_id,
371 p_quota_id
372 ,p_attribute_category
373 ,p_attribute1
374 ,p_attribute2
375 ,p_attribute3
376 ,p_attribute4
377 ,p_attribute5
378 ,p_attribute6
379 ,p_attribute7
380 ,p_attribute8
381 ,p_attribute9
382 ,p_attribute10
383 ,p_attribute11
384 ,p_attribute12
385 ,p_attribute13
386 ,p_attribute14
387 ,p_attribute15
388 ,p_created_by
389 ,p_creation_date
390 ,p_last_update_login
391 ,p_last_update_date
392 ,p_last_updated_by );
393 END IF;
394
395 END begin_record;
396
397 END cn_calc_sub_quotas_pkg;