1 package body CN_ROLE_QUOTA_FORMULAS_PKG as
2 /* $Header: cntrlqfb.pls 115.4 2002/11/21 21:10:48 hlchen ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ROLE_QUOTA_FORMULA_ID in NUMBER,
6 X_ROLE_QUOTA_CATE_ID in NUMBER,
7 X_BASE_ROLE_QUOTA_ID in NUMBER,
8 X_PERCENT in NUMBER,
9 X_ATTRIBUTE_CATEGORY in VARCHAR2,
10 X_ATTRIBUTE1 in VARCHAR2,
11 X_ATTRIBUTE2 in VARCHAR2,
12 X_ATTRIBUTE3 in VARCHAR2,
13 X_ATTRIBUTE4 in VARCHAR2,
14 X_ATTRIBUTE5 in VARCHAR2,
15 X_ATTRIBUTE6 in VARCHAR2,
16 X_ATTRIBUTE7 in VARCHAR2,
17 X_ATTRIBUTE8 in VARCHAR2,
18 X_ATTRIBUTE9 in VARCHAR2,
19 X_ATTRIBUTE10 in VARCHAR2,
20 X_ATTRIBUTE11 in VARCHAR2,
21 X_ATTRIBUTE12 in VARCHAR2,
22 X_ATTRIBUTE13 in VARCHAR2,
23 X_ATTRIBUTE14 in VARCHAR2,
24 X_ATTRIBUTE15 in VARCHAR2,
25 X_OBJECT_VERSION_NUMBER in NUMBER,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_LOGIN in NUMBER
31 ) is
32 cursor C is select ROWID from CN_ROLE_QUOTA_FORMULAS
33 where ROLE_QUOTA_FORMULA_ID = X_ROLE_QUOTA_FORMULA_ID
34 ;
35 begin
36 insert into CN_ROLE_QUOTA_FORMULAS (
37 ROLE_QUOTA_FORMULA_ID,
38 ROLE_QUOTA_CATE_ID,
39 BASE_ROLE_QUOTA_ID,
40 PERCENT,
41 ATTRIBUTE_CATEGORY,
42 ATTRIBUTE1,
43 ATTRIBUTE2,
44 ATTRIBUTE3,
45 ATTRIBUTE4,
46 ATTRIBUTE5,
47 ATTRIBUTE6,
48 ATTRIBUTE7,
49 ATTRIBUTE8,
50 ATTRIBUTE9,
51 ATTRIBUTE10,
52 ATTRIBUTE11,
53 ATTRIBUTE12,
54 ATTRIBUTE13,
55 ATTRIBUTE14,
56 ATTRIBUTE15,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN,
60 CREATION_DATE,
61 CREATED_BY,
62 OBJECT_VERSION_NUMBER
63 ) values
64 (
65 X_ROLE_QUOTA_FORMULA_ID,
66 X_ROLE_QUOTA_CATE_ID,
67 X_BASE_ROLE_QUOTA_ID,
68 X_PERCENT,
69 X_ATTRIBUTE_CATEGORY,
70 X_ATTRIBUTE1,
71 X_ATTRIBUTE2,
72 X_ATTRIBUTE3,
73 X_ATTRIBUTE4,
74 X_ATTRIBUTE5,
75 X_ATTRIBUTE6,
76 X_ATTRIBUTE7,
77 X_ATTRIBUTE8,
78 X_ATTRIBUTE9,
79 X_ATTRIBUTE10,
80 X_ATTRIBUTE11,
81 X_ATTRIBUTE12,
82 X_ATTRIBUTE13,
83 X_ATTRIBUTE14,
84 X_ATTRIBUTE15,
85 X_LAST_UPDATE_DATE,
86 X_LAST_UPDATED_BY,
87 X_LAST_UPDATE_LOGIN,
88 X_CREATION_DATE,
89 X_CREATED_BY,
90 X_OBJECT_VERSION_NUMBER
91 );
92
93 open c;
94 fetch c into X_ROWID;
95 if (c%notfound) then
96 close c;
97 raise no_data_found;
98 end if;
99 close c;
100
101 end INSERT_ROW;
102
103 procedure LOCK_ROW (
104 X_ROLE_QUOTA_FORMULA_ID in NUMBER,
105 X_ROLE_QUOTA_CATE_ID in NUMBER,
106 X_BASE_ROLE_QUOTA_ID in NUMBER,
107 X_PERCENT in NUMBER,
108 X_ATTRIBUTE_CATEGORY in VARCHAR2,
109 X_ATTRIBUTE1 in VARCHAR2,
110 X_ATTRIBUTE2 in VARCHAR2,
111 X_ATTRIBUTE3 in VARCHAR2,
112 X_ATTRIBUTE4 in VARCHAR2,
113 X_ATTRIBUTE5 in VARCHAR2,
114 X_ATTRIBUTE6 in VARCHAR2,
115 X_ATTRIBUTE7 in VARCHAR2,
116 X_ATTRIBUTE8 in VARCHAR2,
117 X_ATTRIBUTE9 in VARCHAR2,
118 X_ATTRIBUTE10 in VARCHAR2,
119 X_ATTRIBUTE11 in VARCHAR2,
120 X_ATTRIBUTE12 in VARCHAR2,
121 X_ATTRIBUTE13 in VARCHAR2,
122 X_ATTRIBUTE14 in VARCHAR2,
123 X_ATTRIBUTE15 in VARCHAR2,
124 X_OBJECT_VERSION_NUMBER in NUMBER
125 ) is
126 cursor c1 is select
127 ROLE_QUOTA_CATE_ID,
128 BASE_ROLE_QUOTA_ID,
129 PERCENT,
130 ATTRIBUTE_CATEGORY,
131 ATTRIBUTE1,
132 ATTRIBUTE2,
133 ATTRIBUTE3,
134 ATTRIBUTE4,
135 ATTRIBUTE5,
136 ATTRIBUTE6,
137 ATTRIBUTE7,
138 ATTRIBUTE8,
139 ATTRIBUTE9,
140 ATTRIBUTE10,
141 ATTRIBUTE11,
142 ATTRIBUTE12,
143 ATTRIBUTE13,
144 ATTRIBUTE14,
145 ATTRIBUTE15,
146 OBJECT_VERSION_NUMBER
147 from CN_ROLE_QUOTA_FORMULAS
148 where ROLE_QUOTA_FORMULA_ID = X_ROLE_QUOTA_FORMULA_ID
149 for update of ROLE_QUOTA_FORMULA_ID nowait;
150 begin
151 for tlinfo in c1 loop
152 if ( (tlinfo.ROLE_QUOTA_CATE_ID = X_ROLE_QUOTA_CATE_ID)
153 AND (tlinfo.BASE_ROLE_QUOTA_ID = X_BASE_ROLE_QUOTA_ID)
154 AND ((tlinfo.PERCENT = X_PERCENT)
155 OR ((tlinfo.PERCENT is null) AND (X_PERCENT is null)))
156 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
157 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
158 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
159 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
160 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
161 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
162 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
163 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
164 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
165 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
166 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
167 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
168 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
169 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
170 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
171 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
172 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
173 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
174 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
175 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
176 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
177 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
178 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
179 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
180 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
181 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
182 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
183 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
184 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
185 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
186 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
187 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
188 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
189 ) then
190 null;
191 else
192 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
193 app_exception.raise_exception;
194 end if;
195 end loop;
196 return;
197 end LOCK_ROW;
198
199 procedure UPDATE_ROW (
200 X_ROLE_QUOTA_FORMULA_ID in NUMBER,
201 X_ROLE_QUOTA_CATE_ID in NUMBER,
202 X_BASE_ROLE_QUOTA_ID in NUMBER,
203 X_PERCENT in NUMBER,
204 X_ATTRIBUTE_CATEGORY in VARCHAR2,
205 X_ATTRIBUTE1 in VARCHAR2,
206 X_ATTRIBUTE2 in VARCHAR2,
207 X_ATTRIBUTE3 in VARCHAR2,
208 X_ATTRIBUTE4 in VARCHAR2,
209 X_ATTRIBUTE5 in VARCHAR2,
210 X_ATTRIBUTE6 in VARCHAR2,
211 X_ATTRIBUTE7 in VARCHAR2,
212 X_ATTRIBUTE8 in VARCHAR2,
213 X_ATTRIBUTE9 in VARCHAR2,
214 X_ATTRIBUTE10 in VARCHAR2,
215 X_ATTRIBUTE11 in VARCHAR2,
216 X_ATTRIBUTE12 in VARCHAR2,
217 X_ATTRIBUTE13 in VARCHAR2,
218 X_ATTRIBUTE14 in VARCHAR2,
219 X_ATTRIBUTE15 in VARCHAR2,
220 X_OBJECT_VERSION_NUMBER in NUMBER,
221 X_LAST_UPDATE_DATE in DATE,
222 X_LAST_UPDATED_BY in NUMBER,
223 X_LAST_UPDATE_LOGIN in NUMBER
224 ) is
225 begin
226 update CN_ROLE_QUOTA_FORMULAS set
227 ROLE_QUOTA_CATE_ID = X_ROLE_QUOTA_CATE_ID,
228 BASE_ROLE_QUOTA_ID = X_BASE_ROLE_QUOTA_ID,
229 PERCENT = X_PERCENT,
230 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
231 ATTRIBUTE1 = X_ATTRIBUTE1,
232 ATTRIBUTE2 = X_ATTRIBUTE2,
233 ATTRIBUTE3 = X_ATTRIBUTE3,
234 ATTRIBUTE4 = X_ATTRIBUTE4,
235 ATTRIBUTE5 = X_ATTRIBUTE5,
236 ATTRIBUTE6 = X_ATTRIBUTE6,
237 ATTRIBUTE7 = X_ATTRIBUTE7,
238 ATTRIBUTE8 = X_ATTRIBUTE8,
239 ATTRIBUTE9 = X_ATTRIBUTE9,
240 ATTRIBUTE10 = X_ATTRIBUTE10,
241 ATTRIBUTE11 = X_ATTRIBUTE11,
242 ATTRIBUTE12 = X_ATTRIBUTE12,
243 ATTRIBUTE13 = X_ATTRIBUTE13,
244 ATTRIBUTE14 = X_ATTRIBUTE14,
245 ATTRIBUTE15 = X_ATTRIBUTE15,
246 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
247 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
248 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
249 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
250 where ROLE_QUOTA_FORMULA_ID = X_ROLE_QUOTA_FORMULA_ID;
251
252 if (sql%notfound) then
253 raise no_data_found;
254 end if;
255 end UPDATE_ROW;
256
257 procedure DELETE_ROW (
258 X_ROLE_QUOTA_FORMULA_ID in NUMBER
259 ) is
260 begin
261 delete from CN_ROLE_QUOTA_FORMULAS
262 where ROLE_QUOTA_FORMULA_ID = X_ROLE_QUOTA_FORMULA_ID;
263
264 if (sql%notfound) then
265 raise no_data_found;
266 end if;
267
268 end DELETE_ROW;
269
270 end CN_ROLE_QUOTA_FORMULAS_PKG;
[Home] [Help]