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