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