1 Package Body ASO_SUP_INSTANCE_VALUE_PKG AS
2 /* $Header: asospivb.pls 120.1 2005/06/29 15:58:50 appldev ship $*/
3
4 /* procedure to insert INSERT_ROW */
5
6 PROCEDURE INSERT_ROW
7 (
8 PX_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
9 PX_INSTANCE_VALUE_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
10 P_SECT_COMP_MAP_ID IN NUMBER,
11 P_Template_Instance_ID IN NUMBER,
12 P_created_by IN NUMBER ,
13 P_last_updated_by IN NUMBER,
14 P_last_update_login IN NUMBER,
15 P_creation_date IN DATE := SYSDATE,
16 P_last_update_date IN DATE := SYSDATE,
17 P_Value IN VARCHAR2 := NULL,
18 P_Value_Type_Qualifier IN VARCHAR2 := NULL,
19 P_Response_id IN NUMBER := NULL,
20 P_CONTEXT IN VARCHAR2 := NULL,
21 P_ATTRIBUTE1 IN VARCHAR2 := NULL,
22 P_ATTRIBUTE2 IN VARCHAR2 := NULL,
23 P_ATTRIBUTE3 IN VARCHAR2 := NULL,
24 P_ATTRIBUTE4 IN VARCHAR2 := NULL,
25 P_ATTRIBUTE5 IN VARCHAR2 := NULL,
26 P_ATTRIBUTE6 IN VARCHAR2 := NULL,
27 P_ATTRIBUTE7 IN VARCHAR2 := NULL,
28 P_ATTRIBUTE8 IN VARCHAR2 := NULL,
29 P_ATTRIBUTE9 IN VARCHAR2 := NULL,
30 P_ATTRIBUTE10 IN VARCHAR2 := NULL,
31 P_ATTRIBUTE11 IN VARCHAR2 := NULL,
32 P_ATTRIBUTE12 IN VARCHAR2 := NULL,
33 P_ATTRIBUTE13 IN VARCHAR2 := NULL,
34 P_ATTRIBUTE14 IN VARCHAR2 := NULL,
35 P_ATTRIBUTE15 IN VARCHAR2 := NULL,
36 P_ATTRIBUTE16 IN VARCHAR2 := NULL,
37 P_ATTRIBUTE17 IN VARCHAR2 := NULL,
38 P_ATTRIBUTE18 IN VARCHAR2 := NULL,
39 P_ATTRIBUTE19 IN VARCHAR2 := NULL,
40 P_ATTRIBUTE20 IN VARCHAR2 := NULL,
41 p_OBJECT_VERSION_NUMBER IN NUMBER
42 )
43
44 IS
45
46 cursor c is
47 select ROWID
48 from ASO_SUP_INSTANCE_VALUE
49 where INSTANCE_VALUE_ID = PX_INSTANCE_VALUE_ID ;
50
51 cursor CU_VALUE_ID IS
52 select ASO_SUP_INST_VALUE_S.NEXTVAL from sys.dual;
53
54 Begin
55
56 IF (PX_INSTANCE_VALUE_ID IS NULL) OR (PX_INSTANCE_VALUE_ID = FND_API.G_MISS_NUM) THEN
57 OPEN CU_VALUE_ID;
58 FETCH CU_VALUE_ID INTO PX_INSTANCE_VALUE_ID;
59 CLOSE CU_VALUE_ID;
60
61 END IF;
62
63 insert into ASO_SUP_INSTANCE_VALUE (
64 INSTANCE_VALUE_ID,
65 SECT_COMP_MAP_ID,
66 Template_Instance_ID,
67 Value,
68 Value_Type_Qualifier,
69 Response_id,
70 created_by ,
71 creation_date ,
72 last_updated_by ,
73 last_update_date ,
74 last_update_login ,
75 CONTEXT,
76 ATTRIBUTE1 ,
77 ATTRIBUTE2 ,
78 ATTRIBUTE3 ,
79 ATTRIBUTE4 ,
80 ATTRIBUTE5 ,
81 ATTRIBUTE6 ,
82 ATTRIBUTE7 ,
83 ATTRIBUTE8 ,
84 ATTRIBUTE9 ,
85 ATTRIBUTE10 ,
86 ATTRIBUTE11 ,
87 ATTRIBUTE12 ,
88 ATTRIBUTE13 ,
89 ATTRIBUTE14 ,
90 ATTRIBUTE15,
91 ATTRIBUTE16,
92 ATTRIBUTE17,
93 ATTRIBUTE18,
94 ATTRIBUTE19,
95 ATTRIBUTE20,
96 OBJECT_VERSION_NUMBER
97 )
98 values
99 (
100 PX_INSTANCE_VALUE_ID,
101 P_SECT_COMP_MAP_ID,
102 P_Template_Instance_ID,
103 P_Value,
104 P_Value_Type_Qualifier,
105 P_Response_id,
106 P_created_by ,
107 P_creation_date ,
108 P_last_updated_by ,
109 P_last_update_date ,
110 P_last_update_login,
111 P_CONTEXT,
112 P_ATTRIBUTE1 ,
113 P_ATTRIBUTE2 ,
114 P_ATTRIBUTE3 ,
115 P_ATTRIBUTE4 ,
116 P_ATTRIBUTE5 ,
117 P_ATTRIBUTE6 ,
118 P_ATTRIBUTE7 ,
119 P_ATTRIBUTE8 ,
120 P_ATTRIBUTE9 ,
121 P_ATTRIBUTE10 ,
122 P_ATTRIBUTE11 ,
123 P_ATTRIBUTE12 ,
124 P_ATTRIBUTE13 ,
125 P_ATTRIBUTE14 ,
126 P_ATTRIBUTE15,
127 P_ATTRIBUTE16,
128 P_ATTRIBUTE17,
129 P_ATTRIBUTE18,
130 P_ATTRIBUTE19,
131 P_ATTRIBUTE20,
132 decode ( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,1,NULL,1, p_OBJECT_VERSION_NUMBER)
133 );
134
135
136 open c;
137 fetch c into PX_ROWID;
138 if (c%notfound) then
139 close c;
140 raise no_data_found;
141 end if;
142 close c;
143
144 end INSERT_ROW;
145
146
147 /* procedure to Update Row */
148
149 PROCEDURE UPDATE_ROW
150 (
151 P_INSTANCE_VALUE_ID IN NUMBER,
152 P_SECT_COMP_MAP_ID IN NUMBER,
153 P_Template_Instance_ID IN NUMBER,
154 P_last_updated_by IN NUMBER,
155 P_last_update_date IN DATE := SYSDATE,
156 P_last_update_login IN NUMBER,
157 P_Value IN VARCHAR2,
158 P_Value_Type_Qualifier IN VARCHAR2,
159 P_Response_id IN NUMBER,
160 P_CONTEXT IN VARCHAR2,
161 P_ATTRIBUTE1 IN VARCHAR2,
162 P_ATTRIBUTE2 IN VARCHAR2,
163 P_ATTRIBUTE3 IN VARCHAR2,
164 P_ATTRIBUTE4 IN VARCHAR2,
165 P_ATTRIBUTE5 IN VARCHAR2,
166 P_ATTRIBUTE6 IN VARCHAR2,
167 P_ATTRIBUTE7 IN VARCHAR2,
168 P_ATTRIBUTE8 IN VARCHAR2,
169 P_ATTRIBUTE9 IN VARCHAR2,
170 P_ATTRIBUTE10 IN VARCHAR2,
171 P_ATTRIBUTE11 IN VARCHAR2,
172 P_ATTRIBUTE12 IN VARCHAR2,
173 P_ATTRIBUTE13 IN VARCHAR2,
174 P_ATTRIBUTE14 IN VARCHAR2,
175 P_ATTRIBUTE15 IN VARCHAR2,
176 P_ATTRIBUTE16 IN VARCHAR2,
177 P_ATTRIBUTE17 IN VARCHAR2,
178 P_ATTRIBUTE18 IN VARCHAR2,
179 P_ATTRIBUTE19 IN VARCHAR2,
180 P_ATTRIBUTE20 IN VARCHAR2,
181 p_OBJECT_VERSION_NUMBER IN NUMBER
182 )
183
184 IS
185
186 Begin
187
188 update ASO_SUP_INSTANCE_VALUE
189 set
190 SECT_COMP_MAP_ID = P_SECT_COMP_MAP_ID,
191 Template_Instance_ID = P_Template_Instance_ID,
192 Value = P_Value ,
193 Value_Type_Qualifier = P_Value_Type_Qualifier,
194 Response_id = P_Response_id,
195 last_updated_by = P_last_updated_by,
196 last_update_date = P_last_update_date,
197 last_update_login = P_last_update_login,
198 context = P_context,
199 ATTRIBUTE1 = P_ATTRIBUTE1,
200 ATTRIBUTE2 = P_ATTRIBUTE2,
201 ATTRIBUTE3 = P_ATTRIBUTE3,
202 ATTRIBUTE4 = P_ATTRIBUTE4,
203 ATTRIBUTE5 = P_ATTRIBUTE5,
204 ATTRIBUTE6 = P_ATTRIBUTE6,
205 ATTRIBUTE7 = P_ATTRIBUTE7,
206 ATTRIBUTE8 = P_ATTRIBUTE8,
207 ATTRIBUTE9 = P_ATTRIBUTE9,
208 ATTRIBUTE10 = P_ATTRIBUTE10,
209 ATTRIBUTE11 = P_ATTRIBUTE11,
210 ATTRIBUTE12 = P_ATTRIBUTE12,
211 ATTRIBUTE13 = P_ATTRIBUTE13,
212 ATTRIBUTE14 = P_ATTRIBUTE14,
213 ATTRIBUTE15 = P_ATTRIBUTE15,
214 ATTRIBUTE16 = P_ATTRIBUTE16,
215 ATTRIBUTE17 = P_ATTRIBUTE17,
216 ATTRIBUTE18 = P_ATTRIBUTE18,
217 ATTRIBUTE19 = P_ATTRIBUTE19,
218 ATTRIBUTE20 = P_ATTRIBUTE20,
219 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, nvl(OBJECT_VERSION_NUMBER,0)+1, nvl(p_OBJECT_VERSION_NUMBER, nvl(OBJECT_VERSION_NUMBER,0))+1)
220 where INSTANCE_VALUE_ID = P_INSTANCE_VALUE_ID;
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225
226
227 End UPDATE_ROW;
228
229 /* procedure to Delete Row */
230
231
232 procedure DELETE_ROW (
233 P_INSTANCE_VALUE_ID IN NUMBER
234
235 )
236
237 IS
238
239 Begin
240
241 delete from ASO_SUP_INSTANCE_VALUE
242 where INSTANCE_VALUE_ID = P_INSTANCE_VALUE_ID;
243
244 if (sql%notfound) then
245 raise no_data_found;
246 end if;
247
248
249 End Delete_row;
250
251
252 /* procedure to Lock Row */
253
254 PROCEDURE LOCK_ROW
255 (
256 P_INSTANCE_VALUE_ID IN NUMBER,
257 --p_OBJECT_VERSION_NUMBER IN NUMBER,
258 P_SECT_COMP_MAP_ID IN NUMBER,
259 P_Template_Instance_ID IN NUMBER,
260 P_created_by IN NUMBER ,
261 P_creation_date IN DATE ,
262 P_last_updated_by IN NUMBER,
263 P_last_update_date IN DATE,
264 P_last_update_login IN NUMBER,
265 P_Value IN VARCHAR2,
266 P_Value_Type_Qualifier IN VARCHAR2,
267 P_Response_id IN NUMBER,
268 P_CONTEXT IN VARCHAR2,
269 P_ATTRIBUTE1 IN VARCHAR2,
270 P_ATTRIBUTE2 IN VARCHAR2,
271 P_ATTRIBUTE3 IN VARCHAR2,
272 P_ATTRIBUTE4 IN VARCHAR2,
273 P_ATTRIBUTE5 IN VARCHAR2,
274 P_ATTRIBUTE6 IN VARCHAR2,
275 P_ATTRIBUTE7 IN VARCHAR2,
276 P_ATTRIBUTE8 IN VARCHAR2,
277 P_ATTRIBUTE9 IN VARCHAR2,
278 P_ATTRIBUTE10 IN VARCHAR2,
279 P_ATTRIBUTE11 IN VARCHAR2,
280 P_ATTRIBUTE12 IN VARCHAR2,
281 P_ATTRIBUTE13 IN VARCHAR2,
282 P_ATTRIBUTE14 IN VARCHAR2,
283 P_ATTRIBUTE15 IN VARCHAR2
284 )
285
286 IS
287
288 CURSOR i_csr is
289 SELECT
290 a.INSTANCE_VALUE_ID ,
291 --OBJECT_VERSION_NUMBER,
292 a.SECT_COMP_MAP_ID,
293 a.Template_Instance_ID,
294 a.Value,
295 a.Value_Type_Qualifier,
296 a.Response_id,
297 created_by ,
298 creation_date ,
299 last_updated_by ,
300 last_update_date ,
301 last_update_login ,
302 context,
303 ATTRIBUTE1 ,
304 ATTRIBUTE2 ,
305 ATTRIBUTE3 ,
306 ATTRIBUTE4 ,
307 ATTRIBUTE5 ,
308 ATTRIBUTE6 ,
309 ATTRIBUTE7 ,
310 ATTRIBUTE8 ,
311 ATTRIBUTE9 ,
312 ATTRIBUTE10 ,
313 ATTRIBUTE11 ,
314 ATTRIBUTE12 ,
315 ATTRIBUTE13 ,
316 ATTRIBUTE14 ,
317 ATTRIBUTE15
318
319 from ASO_SUP_INSTANCE_VALUE a
320 where a.INSTANCE_VALUE_ID = P_INSTANCE_VALUE_ID
321 for update of a.INSTANCE_VALUE_ID nowait;
322
323 recinfo i_csr%rowtype;
324
325
326 l_Item_ID NUMBER ;
327 l_Org_ID NUMBER ;
328
329 l_return_status VARCHAR2(1) ;
330
331 BEGIN
332
333
334 l_Item_ID := P_INSTANCE_VALUE_ID ;
335
336 open i_csr;
337
338 fetch i_csr into recinfo;
339
340 if (i_csr%notfound) then
341 close i_csr;
342 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
343 app_exception.raise_exception;
344 end if;
345
346 close i_csr;
347
348 -- Do not compare to the B table column;
349 -- only compare to TL column (c1 cursor below).
350
351 if (
352 ((recinfo.INSTANCE_VALUE_ID = P_INSTANCE_VALUE_ID)
353 OR ((recinfo.INSTANCE_VALUE_ID is null) AND (P_INSTANCE_VALUE_ID is null)))
354 AND ((recinfo.SECT_COMP_MAP_ID = P_SECT_COMP_MAP_ID)
355 OR ((recinfo.SECT_COMP_MAP_ID is null) AND (P_SECT_COMP_MAP_ID is null)))
356 AND ((recinfo.Template_Instance_ID = P_Template_Instance_ID)
357 OR ((recinfo.Template_Instance_ID is null) AND (P_Template_Instance_ID is null)))
358 AND ((recinfo.Value = P_Value)
359 OR ((recinfo.Value is null) AND (P_Value is null)))
360 AND ((recinfo.Value_Type_Qualifier = P_Value_Type_Qualifier)
361 OR ((recinfo.Value_Type_Qualifier is null) AND (P_Value_Type_Qualifier is null)))
362 AND ((recinfo.Response_id = P_Response_id)
363 OR ((recinfo.Response_id is null) AND (P_Response_id is null)))
364 AND ((recinfo.CREATED_BY = P_CREATED_BY)
365 OR ((recinfo.CREATED_BY is null) AND (P_CREATED_BY is null)))
366 AND ((recinfo.CREATION_DATE = P_CREATION_DATE)
367 OR ((recinfo.CREATION_DATE is null) AND (P_CREATION_DATE is null)))
368 AND ((recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
369 OR ((recinfo.LAST_UPDATED_BY is null) AND (P_LAST_UPDATED_BY is null)))
370 AND ((recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
371 OR ((recinfo.LAST_UPDATE_DATE is null) AND (P_LAST_UPDATE_DATE is null)))
372 /* AND
373 ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
374 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
375 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))*/
376 AND ((recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
377 OR ((recinfo.LAST_UPDATE_LOGIN is null) AND (P_LAST_UPDATE_LOGIN is null)))
378 AND ((recinfo.CONTEXT = P_CONTEXT)
379 OR ((recinfo.CONTEXT is null) AND (P_CONTEXT is null)))
380 AND ((recinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
381 OR ((recinfo.ATTRIBUTE1 is null) AND (P_ATTRIBUTE1 is null)))
382 AND ((recinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
383 OR ((recinfo.ATTRIBUTE2 is null) AND (P_ATTRIBUTE2 is null)))
384 AND ((recinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
385 OR ((recinfo.ATTRIBUTE3 is null) AND (P_ATTRIBUTE3 is null)))
386 AND ((recinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
387 OR ((recinfo.ATTRIBUTE4 is null) AND (P_ATTRIBUTE4 is null)))
388 AND ((recinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
389 OR ((recinfo.ATTRIBUTE5 is null) AND (P_ATTRIBUTE5 is null)))
390 AND ((recinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
391 OR ((recinfo.ATTRIBUTE6 is null) AND (P_ATTRIBUTE6 is null)))
392 AND ((recinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
393 OR ((recinfo.ATTRIBUTE7 is null) AND (P_ATTRIBUTE7 is null)))
394 AND ((recinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
395 OR ((recinfo.ATTRIBUTE8 is null) AND (P_ATTRIBUTE8 is null)))
396 AND ((recinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
397 OR ((recinfo.ATTRIBUTE9 is null) AND (P_ATTRIBUTE9 is null)))
398 AND ((recinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
399 OR ((recinfo.ATTRIBUTE10 is null) AND (P_ATTRIBUTE10 is null)))
400 AND ((recinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
401 OR ((recinfo.ATTRIBUTE11 is null) AND (P_ATTRIBUTE11 is null)))
402 AND ((recinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
403 OR ((recinfo.ATTRIBUTE12 is null) AND (P_ATTRIBUTE12 is null)))
404 AND ((recinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
405 OR ((recinfo.ATTRIBUTE13 is null) AND (P_ATTRIBUTE13 is null)))
406 AND ((recinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
407 OR ((recinfo.ATTRIBUTE14 is null) AND (P_ATTRIBUTE14 is null)))
408 AND ((recinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
409 OR ((recinfo.ATTRIBUTE15 is null) AND (P_ATTRIBUTE15 is null)))
410 ) then
411 null;
412 else
413 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
414 app_exception.raise_exception;
415 end if;
416
417 return;
418
419 End Lock_Row;
420
421
422
423
424 END; -- Package Body ASO_SUP_INSTANCE_VALUE_PKG
[Home] [Help]