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