1 package body CN_ROLE_QUOTA_RATES_PKG as
2 /* $Header: cntpqrb.pls 115.4 2002/11/21 21:10:11 hlchen ship $ */
3 procedure INSERT_ROW
4 (X_ROWID IN OUT NOCOPY VARCHAR2,
5 X_ROLE_QUOTA_RATE_ID IN OUT NOCOPY NUMBER,
6 X_ROLE_QUOTA_CATE_ID IN NUMBER,
7 X_RATE_TIER_ID IN NUMBER,
8 X_RATE_SCHEDULE_ID IN NUMBER,
9 X_MIN_RATE IN NUMBER := NULL,
10 X_MAX_RATE IN NUMBER := NULL,
11 X_COMM_RATE IN NUMBER := NULL,
12 X_ATTRIBUTE_CATEGORY IN VARCHAR2 := NULL,
13 X_ATTRIBUTE1 IN VARCHAR2 := NULL,
14 X_ATTRIBUTE2 IN VARCHAR2 := NULL,
15 X_ATTRIBUTE3 IN VARCHAR2 := NULL,
16 X_ATTRIBUTE4 IN VARCHAR2 := NULL,
17 X_ATTRIBUTE5 IN VARCHAR2 := NULL,
18 X_ATTRIBUTE6 IN VARCHAR2 := NULL,
19 X_ATTRIBUTE7 IN VARCHAR2 := NULL,
20 X_ATTRIBUTE8 IN VARCHAR2 := NULL,
21 X_ATTRIBUTE9 IN VARCHAR2 := NULL,
22 X_ATTRIBUTE10 IN VARCHAR2 := NULL,
23 X_ATTRIBUTE11 IN VARCHAR2 := NULL,
24 X_ATTRIBUTE12 IN VARCHAR2 := NULL,
25 X_ATTRIBUTE13 IN VARCHAR2 := NULL,
26 X_ATTRIBUTE14 IN VARCHAR2 := NULL,
27 X_ATTRIBUTE15 IN VARCHAR2 := NULL,
28 X_CREATION_DATE IN DATE,
29 X_CREATED_BY IN NUMBER,
30 X_LAST_UPDATE_DATE IN DATE,
31 X_LAST_UPDATED_BY IN NUMBER,
32 X_LAST_UPDATE_LOGIN IN NUMBER,
33 X_OBJECT_VERSION_NUMBER IN NUMBER
34 ) IS
35 cursor C is select ROWID from cn_role_quota_rates
36 where ROLE_QUOTA_RATE_ID = x_role_quota_rate_id;
37
38 CURSOR id IS SELECT cn_role_quota_rates_s.NEXTVAL FROM dual;
39 BEGIN
40 IF (x_role_quota_rate_id IS NULL) THEN
41 OPEN id;
42 FETCH id INTO x_role_quota_rate_id;
43 IF (id%notfound) THEN
44 CLOSE id;
45 RAISE no_data_found;
46 END IF;
47 CLOSE id;
48 END IF;
49
50 insert into CN_ROLE_QUOTA_RATES
51 (ROLE_QUOTA_RATE_ID,
52 ROLE_QUOTA_CATE_ID,
53 RATE_TIER_ID,
54 RATE_SCHEDULE_ID,
55 MIN_RATE,
56 MAX_RATE,
57 COMM_RATE,
58 ATTRIBUTE_CATEGORY,
59 ATTRIBUTE1,
60 ATTRIBUTE2,
61 ATTRIBUTE3,
62 ATTRIBUTE4,
63 ATTRIBUTE5,
64 ATTRIBUTE6,
65 ATTRIBUTE7,
66 ATTRIBUTE8,
67 ATTRIBUTE9,
68 ATTRIBUTE10,
69 ATTRIBUTE11,
70 ATTRIBUTE12,
71 ATTRIBUTE13,
72 ATTRIBUTE14,
73 ATTRIBUTE15,
74 LAST_UPDATE_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_LOGIN,
77 CREATION_DATE,
78 created_by,
79 OBJECT_VERSION_NUMBER
80 )
81 VALUES
82 (X_ROLE_QUOTA_RATE_ID,
83 X_ROLE_QUOTA_CATE_ID,
84 X_RATE_TIER_ID,
85 X_RATE_SCHEDULE_ID,
86 X_MIN_RATE,
87 X_MAX_RATE,
88 X_COMM_RATE,
89 X_ATTRIBUTE_CATEGORY,
90 X_ATTRIBUTE1,
91 X_ATTRIBUTE2,
92 X_ATTRIBUTE3,
93 X_ATTRIBUTE4,
94 X_ATTRIBUTE5,
95 X_ATTRIBUTE6,
96 X_ATTRIBUTE7,
97 X_ATTRIBUTE8,
98 X_ATTRIBUTE9,
99 X_ATTRIBUTE10,
100 X_ATTRIBUTE11,
101 X_ATTRIBUTE12,
102 X_ATTRIBUTE13,
103 X_ATTRIBUTE14,
104 X_ATTRIBUTE15,
105 X_LAST_UPDATE_DATE,
106 X_LAST_UPDATED_BY,
107 X_LAST_UPDATE_LOGIN,
108 X_CREATION_DATE,
109 x_created_by,
110 X_OBJECT_VERSION_NUMBER
111 );
112
113 open c;
114 fetch c into X_ROWID;
115 if (c%notfound) THEN
116 close c;
117 raise no_data_found;
118 end if;
119 close c;
120
121 end INSERT_ROW;
122
123 procedure LOCK_ROW
124 (X_ROLE_QUOTA_RATE_ID IN NUMBER,
125 X_ROLE_QUOTA_CATE_ID IN NUMBER,
126 X_RATE_TIER_ID IN NUMBER,
127 X_RATE_SCHEDULE_ID IN NUMBER,
128 X_MIN_RATE IN NUMBER,
129 X_MAX_RATE IN NUMBER,
130 X_COMM_RATE IN NUMBER,
131 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
132 X_ATTRIBUTE1 IN VARCHAR2,
133 X_ATTRIBUTE2 IN VARCHAR2,
134 X_ATTRIBUTE3 IN VARCHAR2,
135 X_ATTRIBUTE4 IN VARCHAR2,
136 X_ATTRIBUTE5 IN VARCHAR2,
137 X_ATTRIBUTE6 IN VARCHAR2,
138 X_ATTRIBUTE7 IN VARCHAR2,
139 X_ATTRIBUTE8 IN VARCHAR2,
140 X_ATTRIBUTE9 IN VARCHAR2,
141 X_ATTRIBUTE10 IN VARCHAR2,
142 X_ATTRIBUTE11 IN VARCHAR2,
143 X_ATTRIBUTE12 IN VARCHAR2,
144 X_ATTRIBUTE13 IN VARCHAR2,
145 X_ATTRIBUTE14 IN VARCHAR2,
146 X_ATTRIBUTE15 IN VARCHAR2,
147 X_OBJECT_VERSION_NUMBER IN NUMBER
148 ) IS
149 cursor c1 is SELECT
150 ROLE_QUOTA_CATE_ID,
151 RATE_TIER_ID,
152 RATE_SCHEDULE_ID,
153 MIN_RATE,
154 MAX_RATE,
155 COMM_RATE,
156 ATTRIBUTE_CATEGORY,
157 ATTRIBUTE1,
158 ATTRIBUTE2,
159 ATTRIBUTE3,
160 ATTRIBUTE4,
161 ATTRIBUTE5,
162 ATTRIBUTE6,
163 ATTRIBUTE7,
164 ATTRIBUTE8,
165 ATTRIBUTE9,
166 ATTRIBUTE10,
167 ATTRIBUTE11,
168 ATTRIBUTE12,
169 ATTRIBUTE13,
170 ATTRIBUTE14,
171 ATTRIBUTE15,
172 ROLE_QUOTA_RATE_ID,
173 OBJECT_VERSION_NUMBER
174 from cn_role_quota_rates
175 where ROLE_QUOTA_RATE_ID = x_role_quota_rate_id
176 for update of ROLE_QUOTA_RATE_ID nowait;
177
178 tlinfo c1%ROWTYPE;
179 record_changed EXCEPTION;
180 BEGIN
181 OPEN c1;
182 FETCH c1 INTO tlinfo;
183
184 IF (c1%notfound) THEN
185 CLOSE c1;
186 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
187 RAISE no_data_found;
188 END IF;
189 CLOSE c1;
190
191 if ( (tlinfo.ROLE_QUOTA_RATE_ID = X_ROLE_QUOTA_RATE_ID)
192 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
193 AND (tlinfo.ROLE_QUOTA_CATE_ID = X_ROLE_QUOTA_CATE_ID)
194 AND (tlinfo.RATE_TIER_ID = X_RATE_TIER_ID)
195 AND (tlinfo.RATE_SCHEDULE_ID = X_RATE_SCHEDULE_ID)
196 AND ((tlinfo.MIN_RATE = X_MIN_RATE)
197 OR ((tlinfo.MIN_RATE is null) AND (X_MIN_RATE is null)))
198 AND ((tlinfo.MAX_RATE = X_MAX_RATE)
199 OR ((tlinfo.MAX_RATE is null) AND (X_MAX_RATE is null)))
200 AND ((tlinfo.COMM_RATE = X_COMM_RATE)
201 OR ((tlinfo.COMM_RATE is null) AND (X_COMM_RATE is null)))
202 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
203 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
204 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
205 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
206 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
207 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
208 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
209 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
210 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
211 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
212 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
213 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
214 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
215 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
216 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
217 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
218 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
219 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
220 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
221 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
222 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
223 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
224 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
225 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
226 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
227 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
228 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
229 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
230 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
231 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
232 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
233 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
234 ) then
235 null;
236 else
237 fnd_message.set_name('CN', 'CN_RECORD_CHANGED');
238 RAISE record_changed;
239 end if;
240 return;
241 end LOCK_ROW;
242
243 procedure UPDATE_ROW
244 (X_ROLE_QUOTA_RATE_ID IN NUMBER,
245 X_ROLE_QUOTA_CATE_ID IN NUMBER,
246 X_RATE_TIER_ID IN NUMBER,
247 X_RATE_SCHEDULE_ID IN NUMBER,
248 X_MIN_RATE IN NUMBER,
249 X_MAX_RATE IN NUMBER,
250 X_COMM_RATE IN NUMBER,
251 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
252 X_ATTRIBUTE1 IN VARCHAR2,
253 X_ATTRIBUTE2 IN VARCHAR2,
254 X_ATTRIBUTE3 IN VARCHAR2,
255 X_ATTRIBUTE4 IN VARCHAR2,
256 X_ATTRIBUTE5 IN VARCHAR2,
257 X_ATTRIBUTE6 IN VARCHAR2,
258 X_ATTRIBUTE7 IN VARCHAR2,
259 X_ATTRIBUTE8 IN VARCHAR2,
260 X_ATTRIBUTE9 IN VARCHAR2,
261 X_ATTRIBUTE10 IN VARCHAR2,
262 X_ATTRIBUTE11 IN VARCHAR2,
263 X_ATTRIBUTE12 IN VARCHAR2,
264 X_ATTRIBUTE13 IN VARCHAR2,
265 X_ATTRIBUTE14 IN VARCHAR2,
266 X_ATTRIBUTE15 IN VARCHAR2,
267 X_LAST_UPDATE_DATE IN DATE,
268 X_LAST_UPDATED_BY IN NUMBER,
269 X_LAST_UPDATE_LOGIN IN NUMBER,
270 X_OBJECT_VERSION_NUMBER IN NUMBER
271 ) IS
272 BEGIN
273 update CN_ROLE_QUOTA_RATES SET
274 ROLE_QUOTA_CATE_ID = X_ROLE_QUOTA_CATE_ID,
275 RATE_TIER_ID = X_RATE_TIER_ID,
276 RATE_SCHEDULE_ID = X_RATE_SCHEDULE_ID,
277 MIN_RATE = X_MIN_RATE,
278 MAX_RATE = X_MAX_RATE,
279 COMM_RATE = X_COMM_RATE,
280 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
281 ATTRIBUTE1 = X_ATTRIBUTE1,
282 ATTRIBUTE2 = X_ATTRIBUTE2,
283 ATTRIBUTE3 = X_ATTRIBUTE3,
284 ATTRIBUTE4 = X_ATTRIBUTE4,
285 ATTRIBUTE5 = X_ATTRIBUTE5,
286 ATTRIBUTE6 = X_ATTRIBUTE6,
287 ATTRIBUTE7 = X_ATTRIBUTE7,
288 ATTRIBUTE8 = X_ATTRIBUTE8,
289 ATTRIBUTE9 = X_ATTRIBUTE9,
290 ATTRIBUTE10 = X_ATTRIBUTE10,
291 ATTRIBUTE11 = X_ATTRIBUTE11,
292 ATTRIBUTE12 = X_ATTRIBUTE12,
293 ATTRIBUTE13 = X_ATTRIBUTE13,
294 ATTRIBUTE14 = X_ATTRIBUTE14,
295 ATTRIBUTE15 = X_ATTRIBUTE15,
296 ROLE_QUOTA_RATE_ID = X_ROLE_QUOTA_RATE_ID,
297 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
298 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
299 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
300 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
301 where ROLE_QUOTA_RATE_ID = x_role_quota_rate_id;
302
303 if (sql%notfound) THEN
304 raise no_data_found;
305 end if;
306 end UPDATE_ROW;
307
308 procedure DELETE_ROW (
309 X_ROLE_QUOTA_RATE_ID in NUMBER
310 ) is
311 begin
312 delete from CN_ROLE_QUOTA_RATES
313 where ROLE_QUOTA_RATE_ID = X_ROLE_QUOTA_RATE_ID;
314
315 if (sql%notfound) then
316 raise no_data_found;
317 end if;
318
319 end DELETE_ROW;
320
321 end CN_ROLE_QUOTA_RATES_PKG;
[Home] [Help]