[Home] [Help]
PACKAGE BODY: APPS.CN_INTEL_CALC_PKG
Source
1 PACKAGE BODY CN_INTEL_CALC_PKG AS
2 /* $Header: cntcalcb.pls 120.1 2005/07/20 10:45:54 mblum noship $ */
3 --
4 -- Package Name
5 -- CN_INTEL_CALC_PKG
6 -- Purpose
7 -- Table handler for CN_SRP_INTEL_PERIODS
8 -- Form
9 -- N/A
10 -- Block
11 -- N/A
12 --
13 -- History
14 -- 16-SEP-99 Yonghong Mao Created
15
16 procedure insert_row (
17 x_srp_intel_period_id IN NUMBER,
18 x_salesrep_id IN NUMBER,
19 x_org_id IN NUMBER,
20 x_period_id IN NUMBER,
21 x_processing_status_code IN VARCHAR2,
22 x_process_all_flag IN VARCHAR2,
23 x_attribute_category IN VARCHAR2 := null,
24 x_attribute1 IN VARCHAR2 := null,
25 x_attribute2 IN VARCHAR2 := null,
26 x_attribute3 IN VARCHAR2 := null,
27 x_attribute4 IN VARCHAR2 := null,
28 x_attribute5 IN VARCHAR2 := null,
29 x_attribute6 IN VARCHAR2 := null,
30 x_attribute7 IN VARCHAR2 := null,
31 x_attribute8 IN VARCHAR2 := null,
32 x_attribute9 IN VARCHAR2 := null,
33 x_attribute10 IN VARCHAR2 := null,
34 x_attribute11 IN VARCHAR2 := null,
35 x_attribute12 IN VARCHAR2 := null,
36 x_attribute13 IN VARCHAR2 := null,
37 x_attribute14 IN VARCHAR2 := null,
38 x_attribute15 IN VARCHAR2 := null,
39 x_creation_date IN DATE := sysdate,
40 x_created_by IN NUMBER := fnd_global.user_id,
41 x_last_update_date IN DATE := sysdate,
42 x_last_updated_by IN NUMBER := fnd_global.user_id,
43 x_last_update_login IN NUMBER := fnd_global.login_id,
44 x_start_date IN DATE := null,
45 x_end_date IN DATE := null
46 ) IS
47 l_srp_intel_period_id NUMBER(15);
48 l_rowid ROWID;
49 CURSOR u_id IS
50 SELECT cn_srp_intel_periods_s.NEXTVAL
51 FROM dual;
52 CURSOR c IS SELECT ROWID FROM cn_srp_intel_periods
53 WHERE srp_intel_period_id = l_srp_intel_period_id;
54 CURSOR rec IS
55 SELECT srp_intel_period_id
56 FROM cn_srp_intel_periods
57 WHERE period_id = x_period_id
58 AND salesrep_id = x_salesrep_id
59 AND org_id = x_org_id;
60 BEGIN
61 OPEN rec;
62 FETCH rec INTO l_srp_intel_period_id;
63 IF (rec%found) THEN
64 CLOSE rec;
65 RETURN;
66 END IF;
67 CLOSE rec;
68
69 IF (x_srp_intel_period_id IS NULL) THEN
70 OPEN u_id;
71 FETCH u_id INTO l_srp_intel_period_id;
72 CLOSE u_id;
73 ELSE
74 l_srp_intel_period_id := x_srp_intel_period_id;
75 END IF;
76
77 INSERT INTO cn_srp_intel_periods (
78 srp_intel_period_id,
79 salesrep_id,
80 org_id,
81 period_id,
82 processing_status_code,
83 process_all_flag,
84 attribute_category,
85 attribute1,
86 attribute2,
87 attribute3,
88 attribute4,
89 attribute5,
90 attribute6,
91 attribute7,
92 attribute8,
93 attribute9,
94 attribute10,
95 attribute11,
96 attribute12,
97 attribute13,
98 attribute14,
99 attribute15,
100 creation_date,
101 created_by,
102 last_update_date,
103 last_updated_by,
104 last_update_login,
105 start_date,
106 end_date
107 ) VALUES (
108 l_srp_intel_period_id,
109 x_salesrep_id,
110 x_org_id,
111 x_period_id,
112 x_processing_status_code,
113 x_process_all_flag,
114 x_attribute_category,
115 x_attribute1,
116 x_attribute2,
117 x_attribute3,
118 x_attribute4,
119 x_attribute5,
120 x_attribute6,
121 x_attribute7,
122 x_attribute8,
123 x_attribute9,
124 x_attribute10,
125 x_attribute11,
126 x_attribute12,
127 x_attribute13,
128 x_attribute14,
129 x_attribute15,
130 x_creation_date,
131 x_created_by,
132 x_last_update_date,
133 x_last_updated_by,
134 x_last_update_login,
135 x_start_date,
136 x_end_date
137 );
138
139 OPEN c;
140 FETCH c INTO l_rowid;
141 IF (c%notfound) THEN
142 CLOSE c;
143 RAISE no_data_found;
144 END IF;
145 CLOSE c;
146
147 END insert_row;
148
149
150 PROCEDURE lock_row (
151 x_srp_intel_period_id IN NUMBER,
152 x_salesrep_id IN NUMBER,
153 x_period_id IN NUMBER,
154 x_processing_status_code IN VARCHAR2,
155 x_process_all_flag IN VARCHAR2,
156 x_start_date IN DATE,
157 x_end_date IN DATE,
158 x_attribute_category IN VARCHAR2,
159 x_attribute1 IN VARCHAR2,
160 x_attribute2 IN VARCHAR2,
161 x_attribute3 IN VARCHAR2,
162 x_attribute4 IN VARCHAR2,
163 x_attribute5 IN VARCHAR2,
164 x_attribute6 IN VARCHAR2,
165 x_attribute7 IN VARCHAR2,
166 x_attribute8 IN VARCHAR2,
167 x_attribute9 IN VARCHAR2,
168 x_attribute10 IN VARCHAR2,
169 x_attribute11 IN VARCHAR2,
170 x_attribute12 IN VARCHAR2,
171 x_attribute13 IN VARCHAR2,
172 x_attribute14 IN VARCHAR2,
173 x_attribute15 IN VARCHAR2)
174 IS
175 CURSOR c IS SELECT
176 salesrep_id,
177 period_id,
178 processing_status_code,
179 process_all_flag,
180 start_date,
181 end_date,
182 attribute_category,
183 attribute1,
184 attribute2,
185 attribute3,
186 attribute4,
187 attribute5,
188 attribute6,
189 attribute7,
190 attribute8,
191 attribute9,
192 attribute10,
193 attribute11,
194 attribute12,
195 attribute13,
196 attribute14,
197 attribute15
198 FROM cn_srp_intel_periods
199 WHERE srp_intel_period_id = x_srp_intel_period_id
200 FOR UPDATE OF srp_intel_period_id nowait;
201 recinfo c%ROWTYPE;
202 BEGIN
203 OPEN c;
204 FETCH c INTO recinfo;
205 IF (c%notfound) THEN
206 CLOSE c;
207 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
208 app_exception.raise_exception;
209 END IF;
210 CLOSE c;
211
212 IF ( recinfo.salesrep_id = x_salesrep_id AND
213 recinfo.period_id = x_period_id AND
214 recinfo.processing_status_code = x_processing_status_code AND
215 recinfo.process_all_flag = x_process_all_flag AND
216 ( (recinfo.start_date = x_start_date)
217 OR ((recinfo.start_date IS NULL) AND (x_start_date IS NULL))
218 )
219 AND
220 ( (recinfo.end_date = x_end_date)
221 OR ((recinfo.end_date IS NULL) AND (x_end_date IS NULL))
222 )
223 AND
224 ( (recinfo.attribute_category = x_attribute_category)
225 OR ((recinfo.attribute_category IS NULL) AND (x_attribute_category IS NULL))
226 )
227 AND
228 ( (recinfo.attribute1 = x_attribute1)
229 OR ((recinfo.attribute1 IS NULL) AND (x_attribute1 IS NULL))
230 )
231 AND
232 ( (recinfo.attribute2 = x_attribute2)
233 OR ((recinfo.attribute2 IS NULL) AND (x_attribute2 IS NULL))
234 )
235 AND
236 ( recinfo.attribute3 = x_attribute3
237 OR (recinfo.attribute3 IS NULL AND x_attribute3 IS NULL)
238 )
239 AND
240 ( recinfo.attribute4 = x_attribute4
241 OR (recinfo.attribute4 IS NULL AND x_attribute4 IS NULL)
242 )
243 AND
244 ( recinfo.attribute5 = x_attribute5
245 OR (recinfo.attribute5 IS NULL AND x_attribute5 IS NULL)
246 )
247 AND
248 ( recinfo.attribute6 = x_attribute6
249 OR (recinfo.attribute6 IS NULL AND x_attribute6 IS NULL)
250 )
251 AND
252 ( recinfo.attribute7 = x_attribute7
253 OR (recinfo.attribute7 IS NULL AND x_attribute7 IS NULL)
254 )
255 AND
256 ( recinfo.attribute8 = x_attribute8
257 OR (recinfo.attribute8 IS NULL AND x_attribute8 IS NULL)
258 )
259 AND
260 ( recinfo.attribute9 = x_attribute9
261 OR (recinfo.attribute9 IS NULL AND x_attribute9 IS NULL)
262 )
263 AND
264 ( recinfo.attribute10 = x_attribute10
265 OR (recinfo.attribute10 IS NULL AND x_attribute10 IS NULL)
266 )
267 AND
268 ( recinfo.attribute11 = x_attribute11
269 OR (recinfo.attribute11 IS NULL AND x_attribute11 IS NULL)
270 )
271 AND
272 ( recinfo.attribute12 = x_attribute12
273 OR (recinfo.attribute12 IS NULL AND x_attribute12 IS NULL)
274 )
275 AND
276 ( recinfo.attribute13 = x_attribute13
277 OR (recinfo.attribute13 IS NULL AND x_attribute13 IS NULL)
278 )
279 AND
280 ( recinfo.attribute14 = x_attribute14
281 OR (recinfo.attribute14 IS NULL AND x_attribute14 IS NULL)
282 )
283 AND
284 ( recinfo.attribute15 = x_attribute15
285 OR (recinfo.attribute15 IS NULL AND x_attribute15 IS NULL)
286 )
287 ) THEN
288 NULL;
289 ELSE
290 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
291 app_exception.raise_exception;
292 END IF;
293
294 RETURN;
295 END lock_row;
296
297 PROCEDURE update_row (
298 x_srp_intel_period_id IN NUMBER,
299 x_salesrep_id IN NUMBER,
300 x_period_id IN NUMBER,
301 x_start_date IN DATE,
302 x_end_date IN DATE,
303 x_processing_status_code IN VARCHAR2,
304 x_process_all_flag IN VARCHAR2,
305 x_attribute_category IN VARCHAR2,
306 x_attribute1 IN VARCHAR2,
307 x_attribute2 IN VARCHAR2,
308 x_attribute3 IN VARCHAR2,
309 x_attribute4 IN VARCHAR2,
310 x_attribute5 IN VARCHAR2,
311 x_attribute6 IN VARCHAR2,
312 x_attribute7 IN VARCHAR2,
313 x_attribute8 IN VARCHAR2,
314 x_attribute9 IN VARCHAR2,
315 x_attribute10 IN VARCHAR2,
316 x_attribute11 IN VARCHAR2,
317 x_attribute12 IN VARCHAR2,
318 x_attribute13 IN VARCHAR2,
319 x_attribute14 IN VARCHAR2,
320 x_attribute15 IN VARCHAR2,
321 x_last_update_date IN DATE,
322 x_last_updated_by IN NUMBER,
323 x_last_update_login IN NUMBER) IS
324 BEGIN
325 UPDATE cn_srp_intel_periods SET
326 salesrep_id = x_salesrep_id,
327 period_id = x_period_id,
328 start_date = x_start_date,
329 end_date = x_end_date,
330 processing_status_code = x_processing_status_code,
331 process_all_flag = x_process_all_flag,
332 attribute_category = x_attribute_category,
333 attribute1 = x_attribute1,
334 attribute2 = x_attribute2,
335 attribute3 = x_attribute3,
336 attribute4 = x_attribute4,
337 attribute5 = x_attribute5,
338 attribute6 = x_attribute6,
339 attribute7 = x_attribute7,
340 attribute8 = x_attribute8,
341 attribute9 = x_attribute9,
342 attribute10 = x_attribute10,
343 attribute11 = x_attribute11,
344 attribute12 = x_attribute12,
345 attribute13 = x_attribute13,
346 attribute14 = x_attribute14,
347 attribute15 = x_attribute15,
348 last_update_date = x_last_update_date,
349 last_updated_by = x_last_updated_by,
350 last_update_login = x_last_update_login
351 WHERE srp_intel_period_id = x_srp_intel_period_id;
352
353 IF (SQL%notfound) THEN
354 RAISE no_data_found;
355 END IF;
356
357 END update_row;
358
359 PROCEDURE delete_row (
360 x_srp_intel_period_id IN NUMBER
361 ) IS
362 BEGIN
363 DELETE FROM cn_srp_intel_periods
364 WHERE srp_intel_period_id = x_srp_intel_period_id;
365
366 IF (SQL%notfound) THEN
367 RAISE no_data_found;
368 END IF;
369
370 END delete_row;
371
372 end CN_INTEL_CALC_PKG;