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