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