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