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