[Home] [Help]
PACKAGE BODY: APPS.CSI_II_RELATIONSHIPS_PKG
Source
1 PACKAGE BODY csi_ii_relationships_pkg AS
2 /* $Header: csitiirb.pls 115.13 2003/09/04 00:18:58 sguthiva ship $ */
3 -- start of comments
4 -- package name : csi_ii_relationships_pkg
5 -- purpose :
6 -- history :
7 -- note :
8 -- end of comments
9
10
11 g_pkg_name CONSTANT VARCHAR2(30):= 'csi_ii_relationships_pkg';
12 g_file_name CONSTANT VARCHAR2(12) := 'csitiirb.pls';
13
14 PROCEDURE insert_row(
15 px_relationship_id IN OUT NOCOPY NUMBER,
16 p_relationship_type_code VARCHAR2,
17 p_object_id NUMBER,
18 p_subject_id NUMBER,
19 p_position_reference VARCHAR2,
20 p_active_start_date DATE,
21 p_active_end_date DATE,
22 p_display_order NUMBER,
23 p_mandatory_flag VARCHAR2,
24 p_context VARCHAR2,
25 p_attribute1 VARCHAR2,
26 p_attribute2 VARCHAR2,
27 p_attribute3 VARCHAR2,
28 p_attribute4 VARCHAR2,
29 p_attribute5 VARCHAR2,
30 p_attribute6 VARCHAR2,
31 p_attribute7 VARCHAR2,
32 p_attribute8 VARCHAR2,
33 p_attribute9 VARCHAR2,
34 p_attribute10 VARCHAR2,
35 p_attribute11 VARCHAR2,
36 p_attribute12 VARCHAR2,
37 p_attribute13 VARCHAR2,
38 p_attribute14 VARCHAR2,
39 p_attribute15 VARCHAR2,
40 p_created_by NUMBER,
41 p_creation_date DATE,
42 p_last_updated_by NUMBER,
43 p_last_update_date DATE,
44 p_last_update_login NUMBER,
45 p_object_version_number NUMBER)
46
47 IS
48 CURSOR c2 IS SELECT csi_ii_relationships_s.NEXTVAL FROM sys.dual;
49 BEGIN
50 IF (px_relationship_id IS NULL) OR (px_relationship_id = fnd_api.g_miss_num) THEN
51 OPEN c2;
52 FETCH c2 INTO px_relationship_id;
53 CLOSE c2;
54 END IF;
55 --dbms_output.put_line('relationship_id'||px_relationship_id);
56 INSERT INTO csi_ii_relationships(
57 relationship_id,
58 relationship_type_code,
59 object_id,
60 subject_id,
61 position_reference,
62 active_start_date,
63 active_end_date,
64 display_order,
65 mandatory_flag,
66 context,
67 attribute1,
68 attribute2,
69 attribute3,
70 attribute4,
71 attribute5,
72 attribute6,
73 attribute7,
74 attribute8,
75 attribute9,
76 attribute10,
77 attribute11,
78 attribute12,
79 attribute13,
80 attribute14,
81 attribute15,
82 created_by,
83 creation_date,
84 last_updated_by,
85 last_update_date,
86 last_update_login,
87 object_version_number
88 ) VALUES (
89 px_relationship_id,
90 decode( p_relationship_type_code, fnd_api.g_miss_char, NULL, p_relationship_type_code),
91 decode( p_object_id, fnd_api.g_miss_num, NULL, p_object_id),
92 decode( p_subject_id, fnd_api.g_miss_num, NULL, p_subject_id),
93 decode( p_position_reference, fnd_api.g_miss_char, NULL, p_position_reference),
94 decode( p_active_start_date, fnd_api.g_miss_date, to_date(NULL), p_active_start_date),
95 decode( p_active_end_date, fnd_api.g_miss_date, to_date(NULL), p_active_end_date),
96 decode( p_display_order, fnd_api.g_miss_num, NULL, p_display_order),
97 decode( p_mandatory_flag, fnd_api.g_miss_char, NULL, p_mandatory_flag),
98 decode( p_context, fnd_api.g_miss_char, NULL, p_context),
99 decode( p_attribute1, fnd_api.g_miss_char, NULL, p_attribute1),
100 decode( p_attribute2, fnd_api.g_miss_char, NULL, p_attribute2),
101 decode( p_attribute3, fnd_api.g_miss_char, NULL, p_attribute3),
102 decode( p_attribute4, fnd_api.g_miss_char, NULL, p_attribute4),
103 decode( p_attribute5, fnd_api.g_miss_char, NULL, p_attribute5),
104 decode( p_attribute6, fnd_api.g_miss_char, NULL, p_attribute6),
105 decode( p_attribute7, fnd_api.g_miss_char, NULL, p_attribute7),
106 decode( p_attribute8, fnd_api.g_miss_char, NULL, p_attribute8),
107 decode( p_attribute9, fnd_api.g_miss_char, NULL, p_attribute9),
108 decode( p_attribute10, fnd_api.g_miss_char, NULL, p_attribute10),
109 decode( p_attribute11, fnd_api.g_miss_char, NULL, p_attribute11),
110 decode( p_attribute12, fnd_api.g_miss_char, NULL, p_attribute12),
111 decode( p_attribute13, fnd_api.g_miss_char, NULL, p_attribute13),
112 decode( p_attribute14, fnd_api.g_miss_char, NULL, p_attribute14),
113 decode( p_attribute15, fnd_api.g_miss_char, NULL, p_attribute15),
114 decode( p_created_by, fnd_api.g_miss_num, NULL, p_created_by),
115 decode( p_creation_date, fnd_api.g_miss_date, to_date(NULL), p_creation_date),
116 decode( p_last_updated_by, fnd_api.g_miss_num, NULL, p_last_updated_by),
117 decode( p_last_update_date, fnd_api.g_miss_date, to_date(NULL), p_last_update_date),
118 decode( p_last_update_login, fnd_api.g_miss_num, NULL, p_last_update_login),
119 decode( p_object_version_number, fnd_api.g_miss_num, NULL, p_object_version_number));
120 --px_relationship_id:=NULL;
121 --dbms_output.put_line('after insert');
122 --commit;
123 END insert_row;
124
125 PROCEDURE update_row(
126 p_relationship_id NUMBER,
127 p_relationship_type_code VARCHAR2,
128 p_object_id NUMBER,
129 p_subject_id NUMBER,
130 p_position_reference VARCHAR2,
131 p_active_start_date DATE,
132 p_active_end_date DATE,
133 p_display_order NUMBER,
134 p_mandatory_flag VARCHAR2,
135 p_context VARCHAR2,
136 p_attribute1 VARCHAR2,
137 p_attribute2 VARCHAR2,
138 p_attribute3 VARCHAR2,
139 p_attribute4 VARCHAR2,
140 p_attribute5 VARCHAR2,
141 p_attribute6 VARCHAR2,
142 p_attribute7 VARCHAR2,
143 p_attribute8 VARCHAR2,
144 p_attribute9 VARCHAR2,
145 p_attribute10 VARCHAR2,
146 p_attribute11 VARCHAR2,
147 p_attribute12 VARCHAR2,
148 p_attribute13 VARCHAR2,
149 p_attribute14 VARCHAR2,
150 p_attribute15 VARCHAR2,
151 p_created_by NUMBER,
152 p_creation_date DATE,
153 p_last_updated_by NUMBER,
154 p_last_update_date DATE,
155 p_last_update_login NUMBER,
156 p_object_version_number NUMBER)
157
158 IS
159 BEGIN
160 update csi_ii_relationships
161 set
162 relationship_type_code = decode( p_relationship_type_code, fnd_api.g_miss_char, relationship_type_code, p_relationship_type_code),
163 object_id = decode( p_object_id, fnd_api.g_miss_num, object_id, p_object_id),
164 subject_id = decode( p_subject_id, fnd_api.g_miss_num, subject_id, p_subject_id),
165 position_reference = decode( p_position_reference, fnd_api.g_miss_char, position_reference, p_position_reference),
166 active_start_date = decode( p_active_start_date, fnd_api.g_miss_date, active_start_date, p_active_start_date),
167 active_end_date = decode( p_active_end_date, fnd_api.g_miss_date, active_end_date, p_active_end_date),
168 display_order = decode( p_display_order, fnd_api.g_miss_num, display_order, p_display_order),
169 mandatory_flag = decode( p_mandatory_flag, fnd_api.g_miss_char, mandatory_flag, p_mandatory_flag),
170 context = decode( p_context, fnd_api.g_miss_char, context, p_context),
171 attribute1 = decode( p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1),
172 attribute2 = decode( p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2),
173 attribute3 = decode( p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3),
174 attribute4 = decode( p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4),
175 attribute5 = decode( p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5),
176 attribute6 = decode( p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6),
177 attribute7 = decode( p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7),
178 attribute8 = decode( p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8),
179 attribute9 = decode( p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9),
180 attribute10 = decode( p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10),
181 attribute11 = decode( p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11),
182 attribute12 = decode( p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12),
183 attribute13 = decode( p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13),
184 attribute14 = decode( p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14),
185 attribute15 = decode( p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15),
186 created_by = decode( p_created_by, fnd_api.g_miss_num, created_by, p_created_by),
187 creation_date = decode( p_creation_date, fnd_api.g_miss_date, creation_date, p_creation_date),
188 last_updated_by = decode( p_last_updated_by, fnd_api.g_miss_num, last_updated_by, p_last_updated_by),
189 last_update_date = decode( p_last_update_date, fnd_api.g_miss_date, last_update_date, p_last_update_date),
190 last_update_login = decode( p_last_update_login, fnd_api.g_miss_num, last_update_login, p_last_update_login),
191 object_version_number = object_version_number+1
192 --object_version_number = decode( p_object_version_number, fnd_api.g_miss_num, object_version_number, p_object_version_number)
193 WHERE relationship_id = p_relationship_id;
194
195 IF (SQL%NOTFOUND) THEN
196 RAISE NO_DATA_FOUND;
197 END IF;
198 END update_row;
199
200 PROCEDURE delete_row(
201 p_relationship_id NUMBER)
202 IS
203 BEGIN
204 DELETE FROM csi_ii_relationships
205 WHERE relationship_id = p_relationship_id;
206 IF (SQL%NOTFOUND) THEN
207 RAISE NO_DATA_FOUND;
208 END IF;
209 END delete_row;
210
211 PROCEDURE lock_row(
212 p_relationship_id NUMBER,
213 p_relationship_type_code VARCHAR2,
214 p_object_id NUMBER,
215 p_subject_id NUMBER,
216 p_position_reference VARCHAR2,
217 p_active_start_date DATE,
218 p_active_end_date DATE,
219 p_display_order NUMBER,
220 p_mandatory_flag VARCHAR2,
221 p_context VARCHAR2,
222 p_attribute1 VARCHAR2,
223 p_attribute2 VARCHAR2,
224 p_attribute3 VARCHAR2,
225 p_attribute4 VARCHAR2,
226 p_attribute5 VARCHAR2,
227 p_attribute6 VARCHAR2,
228 p_attribute7 VARCHAR2,
229 p_attribute8 VARCHAR2,
230 p_attribute9 VARCHAR2,
231 p_attribute10 VARCHAR2,
232 p_attribute11 VARCHAR2,
233 p_attribute12 VARCHAR2,
234 p_attribute13 VARCHAR2,
235 p_attribute14 VARCHAR2,
236 p_attribute15 VARCHAR2,
237 p_created_by NUMBER,
238 p_creation_date DATE,
239 p_last_updated_by NUMBER,
240 p_last_update_date DATE,
241 p_last_update_login NUMBER,
242 p_object_version_number NUMBER)
243
244 IS
245 CURSOR c IS
246 SELECT *
247 FROM csi_ii_relationships
248 WHERE relationship_id = p_relationship_id
249 FOR UPDATE OF relationship_id NOWAIT;
253 FETCH c INTO recinfo;
250 recinfo c%ROWTYPE;
251 BEGIN
252 OPEN c;
254 IF (c%NOTFOUND) THEN
255 CLOSE c;
256 fnd_message.set_name('fnd', 'form_record_deleted');
257 app_exception.raise_exception;
258 END IF;
259 CLOSE c;
260 IF (
261 ( recinfo.relationship_id = p_relationship_id)
262 AND ( ( recinfo.relationship_type_code = p_relationship_type_code)
263 OR ( ( recinfo.relationship_type_code IS NULL )
264 AND ( p_relationship_type_code IS NULL )))
265 AND ( ( recinfo.object_id = p_object_id)
266 OR ( ( recinfo.object_id IS NULL )
267 AND ( p_object_id IS NULL )))
268 AND ( ( recinfo.subject_id = p_subject_id)
269 OR ( ( recinfo.subject_id IS NULL )
270 AND ( p_subject_id IS NULL )))
271 AND ( ( recinfo.position_reference = p_position_reference)
272 OR ( ( recinfo.position_reference IS NULL )
273 AND ( p_position_reference IS NULL )))
274 AND ( ( recinfo.active_start_date = p_active_start_date)
275 OR ( ( recinfo.active_start_date IS NULL )
276 AND ( p_active_start_date IS NULL )))
277 AND ( ( recinfo.active_end_date = p_active_end_date)
278 OR ( ( recinfo.active_end_date IS NULL )
279 AND ( p_active_end_date IS NULL )))
280 AND ( ( recinfo.display_order = p_display_order)
281 OR ( ( recinfo.display_order IS NULL )
282 AND ( p_display_order IS NULL )))
283 AND ( ( recinfo.mandatory_flag = p_mandatory_flag)
284 OR ( ( recinfo.mandatory_flag IS NULL )
285 AND ( p_mandatory_flag IS NULL )))
286 AND ( ( recinfo.context = p_context)
287 OR ( ( recinfo.context IS NULL )
288 AND ( p_context IS NULL )))
289 AND ( ( recinfo.attribute1 = p_attribute1)
290 OR ( ( recinfo.attribute1 IS NULL )
291 AND ( p_attribute1 IS NULL )))
292 AND ( ( recinfo.attribute2 = p_attribute2)
293 OR ( ( recinfo.attribute2 IS NULL )
294 AND ( p_attribute2 IS NULL )))
295 AND ( ( recinfo.attribute3 = p_attribute3)
296 OR ( ( recinfo.attribute3 IS NULL )
297 AND ( p_attribute3 IS NULL )))
298 AND ( ( recinfo.attribute4 = p_attribute4)
299 OR ( ( recinfo.attribute4 IS NULL )
300 AND ( p_attribute4 IS NULL )))
301 AND ( ( recinfo.attribute5 = p_attribute5)
302 OR ( ( recinfo.attribute5 IS NULL )
303 AND ( p_attribute5 IS NULL )))
304 AND ( ( recinfo.attribute6 = p_attribute6)
305 OR ( ( recinfo.attribute6 IS NULL )
306 AND ( p_attribute6 IS NULL )))
307 AND ( ( recinfo.attribute7 = p_attribute7)
308 OR ( ( recinfo.attribute7 IS NULL )
309 AND ( p_attribute7 IS NULL )))
310 AND ( ( recinfo.attribute8 = p_attribute8)
311 OR ( ( recinfo.attribute8 IS NULL )
312 AND ( p_attribute8 IS NULL )))
313 AND ( ( recinfo.attribute9 = p_attribute9)
314 OR ( ( recinfo.attribute9 IS NULL )
315 AND ( p_attribute9 IS NULL )))
316 AND ( ( recinfo.attribute10 = p_attribute10)
317 OR ( ( recinfo.attribute10 IS NULL )
318 AND ( p_attribute10 IS NULL )))
319 AND ( ( recinfo.attribute11 = p_attribute11)
320 OR ( ( recinfo.attribute11 IS NULL )
321 AND ( p_attribute11 IS NULL )))
322 AND ( ( recinfo.attribute12 = p_attribute12)
323 OR ( ( recinfo.attribute12 IS NULL )
324 AND ( p_attribute12 IS NULL )))
325 AND ( ( recinfo.attribute13 = p_attribute13)
326 OR ( ( recinfo.attribute13 IS NULL )
327 AND ( p_attribute13 IS NULL )))
328 AND ( ( recinfo.attribute14 = p_attribute14)
329 OR ( ( recinfo.attribute14 IS NULL )
330 AND ( p_attribute14 IS NULL )))
331 AND ( ( recinfo.attribute15 = p_attribute15)
332 OR ( ( recinfo.attribute15 IS NULL )
333 AND ( p_attribute15 IS NULL )))
334 AND ( ( recinfo.created_by = p_created_by)
335 OR ( ( recinfo.created_by IS NULL )
336 AND ( p_created_by IS NULL )))
337 AND ( ( recinfo.creation_date = p_creation_date)
338 OR ( ( recinfo.creation_date IS NULL )
339 AND ( p_creation_date IS NULL )))
340 AND ( ( recinfo.last_updated_by = p_last_updated_by)
341 OR ( ( recinfo.last_updated_by IS NULL )
342 AND ( p_last_updated_by IS NULL )))
343 AND ( ( recinfo.last_update_date = p_last_update_date)
344 OR ( ( recinfo.last_update_date IS NULL )
345 AND ( p_last_update_date IS NULL )))
346 AND ( ( recinfo.last_update_login = p_last_update_login)
347 OR ( ( recinfo.last_update_login IS NULL )
348 AND ( p_last_update_login IS NULL )))
349 AND ( ( recinfo.object_version_number = p_object_version_number)
350 OR ( ( recinfo.object_version_number IS NULL )
351 AND ( p_object_version_number IS NULL )))
352 ) THEN
353 return;
354 ELSE
355 fnd_message.set_name('fnd', 'form_record_changed');
356 app_exception.raise_exception;
357 END IF;
358 END lock_row;
359
360 END csi_ii_relationships_pkg;