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