[Home] [Help]
PACKAGE BODY: APPS.HZ_DSS_ENTITIES_PKG
Source
1 PACKAGE BODY HZ_DSS_ENTITIES_PKG AS
2 /* $Header: ARHPDSEB.pls 115.3 2003/01/07 19:25:24 jypandey noship $ */
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_entity_id IN OUT NOCOPY NUMBER,
7 x_status IN VARCHAR2,
8 x_object_id IN NUMBER,
9 x_instance_set_id IN NUMBER,
10 x_parent_entity_id IN NUMBER,
11 x_parent_fk_column1 IN VARCHAR2,
12 x_parent_fk_column2 IN VARCHAR2,
13 x_parent_fk_column3 IN VARCHAR2,
14 x_parent_fk_column4 IN VARCHAR2,
15 x_parent_fk_column5 IN VARCHAR2,
16 x_group_assignment_level IN VARCHAR2,
17 x_object_version_number IN NUMBER
18 ) IS
19
20 l_success VARCHAR2(1) := 'N';
21
22 BEGIN
23
24 WHILE l_success = 'N' LOOP
25 BEGIN
26 INSERT INTO HZ_DSS_ENTITIES (
27 entity_id,
28 status,
29 object_id,
30 instance_set_id,
31 parent_entity_id,
32 parent_fk_column1,
33 parent_fk_column2,
34 parent_fk_column3,
35 parent_fk_column4,
36 parent_fk_column5,
37 group_assignment_level,
38 last_update_date,
39 last_updated_by,
40 creation_date,
41 created_by,
42 last_update_login,
43 object_version_number
44 )
45 VALUES (
46 DECODE(x_entity_id,
47 FND_API.G_MISS_NUM, HZ_DSS_ENTITIES_S.NEXTVAL,
48 NULL, HZ_DSS_ENTITIES_S.NEXTVAL,
49 x_entity_id),
50 DECODE(x_status,
51 FND_API.G_MISS_CHAR, 'A',
52 NULL, 'A',
53 x_status),
54 DECODE(x_object_id,
55 FND_API.G_MISS_NUM, NULL,
56 x_object_id),
57 DECODE(x_instance_set_id,
58 FND_API.G_MISS_NUM, NULL,
59 x_instance_set_id),
60 DECODE(x_parent_entity_id,
61 FND_API.G_MISS_NUM, NULL,
62 x_parent_entity_id),
63 DECODE(x_parent_fk_column1,
64 FND_API.G_MISS_CHAR, NULL,
65 x_parent_fk_column1),
66 DECODE(x_parent_fk_column2,
67 FND_API.G_MISS_CHAR, NULL,
68 x_parent_fk_column2),
69 DECODE(x_parent_fk_column3,
70 FND_API.G_MISS_CHAR, NULL,
71 x_parent_fk_column3),
72 DECODE(x_parent_fk_column4,
73 FND_API.G_MISS_CHAR, NULL,
74 x_parent_fk_column4),
75 DECODE(x_parent_fk_column5,
76 FND_API.G_MISS_CHAR, NULL,
77 x_parent_fk_column5),
78 DECODE(x_group_assignment_level,
79 FND_API.G_MISS_CHAR, NULL,
80 x_group_assignment_level),
81 hz_utility_v2pub.last_update_date,
82 hz_utility_v2pub.last_updated_by,
83 hz_utility_v2pub.creation_date,
84 hz_utility_v2pub.created_by,
85 hz_utility_v2pub.last_update_login,
86 DECODE(x_object_version_number,
87 FND_API.G_MISS_NUM, NULL,
88 x_object_version_number)
89 ) RETURNING
90 rowid,
91 entity_id
92 INTO
93 x_rowid,
94 x_entity_id;
95
96 l_success := 'Y';
97
98 EXCEPTION
99 WHEN DUP_VAL_ON_INDEX THEN
100 IF INSTR(SQLERRM, 'HZ_DSS_ENTITIES_U1') <> 0 THEN
101 DECLARE
102 l_count NUMBER;
103 l_dummy VARCHAR2(1);
104 BEGIN
105 l_count := 1;
106 WHILE l_count > 0 LOOP
107 SELECT HZ_DSS_ENTITIES_S.NEXTVAL
108 INTO x_entity_id FROM dual;
109 BEGIN
110 SELECT 'Y' INTO l_dummy
111 FROM HZ_DSS_ENTITIES
112 WHERE entity_id = x_entity_id;
113 l_count := 1;
114 EXCEPTION
115 WHEN NO_DATA_FOUND THEN
116 l_count := 0;
117 END;
118 END LOOP;
119 END;
120 END IF;
121
122 END;
123 END LOOP;
124
125 END Insert_Row;
126
127 PROCEDURE Update_Row (
128 x_rowid IN OUT NOCOPY VARCHAR2,
129 x_status IN VARCHAR2,
130 x_object_id IN NUMBER,
131 x_instance_set_id IN NUMBER,
132 x_parent_entity_id IN NUMBER,
133 x_parent_fk_column1 IN VARCHAR2,
134 x_parent_fk_column2 IN VARCHAR2,
135 x_parent_fk_column3 IN VARCHAR2,
136 x_parent_fk_column4 IN VARCHAR2,
137 x_parent_fk_column5 IN VARCHAR2,
138 x_group_assignment_level IN VARCHAR2,
139 x_object_version_number IN NUMBER
140 ) IS
141 BEGIN
142
143 UPDATE HZ_DSS_ENTITIES
144 SET
145 status =
146 DECODE(x_status,
147 NULL, status,
148 FND_API.G_MISS_CHAR, NULL,
149 x_status),
150 --Bug:2620112 Allow updates to following columns
151 parent_entity_id =
152 DECODE(x_parent_entity_id,
153 NULL, parent_entity_id,
154 FND_API.G_MISS_NUM, NULL,
155 x_parent_entity_id),
156 parent_fk_column1 =
157 DECODE(x_parent_fk_column1,
158 NULL, parent_fk_column1,
159 FND_API.G_MISS_CHAR, NULL,
160 x_parent_fk_column1),
161 parent_fk_column2 =
162 DECODE(x_parent_fk_column2,
163 NULL, parent_fk_column2,
164 FND_API.G_MISS_CHAR, NULL,
165 x_parent_fk_column2),
166 parent_fk_column3 =
167 DECODE(x_parent_fk_column3,
168 NULL, parent_fk_column3,
169 FND_API.G_MISS_CHAR, NULL,
170 x_parent_fk_column3),
171 parent_fk_column4 =
172 DECODE(x_parent_fk_column4,
173 NULL, parent_fk_column4,
174 FND_API.G_MISS_CHAR, NULL,
175 x_parent_fk_column4),
176 parent_fk_column5 =
177 DECODE(x_parent_fk_column5,
178 NULL, parent_fk_column5,
179 FND_API.G_MISS_CHAR, NULL,
180 x_parent_fk_column5),
181 group_assignment_level =
182 DECODE(x_group_assignment_level,
183 NULL, group_assignment_level,
184 FND_API.G_MISS_CHAR, NULL,
185 x_group_assignment_level),
186 last_update_date = hz_utility_v2pub.last_update_date,
187 last_updated_by = hz_utility_v2pub.last_updated_by,
188 creation_date = creation_date,
189 created_by = created_by,
190 last_update_login = hz_utility_v2pub.last_update_login,
191 object_version_number =
192 DECODE(x_object_version_number,
193 NULL, object_version_number,
194 FND_API.G_MISS_NUM, NULL,
195 x_object_version_number)
196 WHERE rowid = x_rowid;
197
198 IF ( SQL%NOTFOUND ) THEN
199 RAISE NO_DATA_FOUND;
200 END IF;
201
202 END Update_Row;
203
204 PROCEDURE Lock_Row (
205 x_rowid IN OUT NOCOPY VARCHAR2,
206 x_entity_id IN NUMBER,
207 x_status IN VARCHAR2,
208 x_object_id IN NUMBER,
209 x_instance_set_id IN NUMBER,
210 x_parent_entity_id IN NUMBER,
211 x_parent_fk_column1 IN VARCHAR2,
212 x_parent_fk_column2 IN VARCHAR2,
213 x_parent_fk_column3 IN VARCHAR2,
214 x_parent_fk_column4 IN VARCHAR2,
215 x_parent_fk_column5 IN VARCHAR2,
216 x_group_assignment_level IN VARCHAR2,
217 x_last_update_date IN DATE,
218 x_last_updated_by IN NUMBER,
219 x_creation_date IN DATE,
220 x_created_by IN NUMBER,
221 x_last_update_login IN NUMBER,
222 x_object_version_number IN NUMBER
223 ) IS
224
225 CURSOR c IS
226 SELECT * FROM hz_dss_entities
227 WHERE rowid = x_rowid
228 FOR UPDATE NOWAIT;
229 Recinfo c%ROWTYPE;
230
231 BEGIN
232
233 OPEN c;
234 FETCH c INTO Recinfo;
235 IF ( c%NOTFOUND ) THEN
236 CLOSE c;
237 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
238 APP_EXCEPTION.RAISE_EXCEPTION;
239 END IF;
240 CLOSE C;
241
242 IF (
243 ( ( Recinfo.entity_id = x_entity_id )
244 OR ( ( Recinfo.entity_id IS NULL )
245 AND ( x_entity_id IS NULL ) ) )
246 AND ( ( Recinfo.status = x_status )
247 OR ( ( Recinfo.status IS NULL )
248 AND ( x_status IS NULL ) ) )
249 AND ( ( Recinfo.object_id = x_object_id )
250 OR ( ( Recinfo.object_id IS NULL )
251 AND ( x_object_id IS NULL ) ) )
252 AND ( ( Recinfo.instance_set_id = x_instance_set_id )
253 OR ( ( Recinfo.instance_set_id IS NULL )
254 AND ( x_instance_set_id IS NULL ) ) )
255 AND ( ( Recinfo.parent_entity_id = x_parent_entity_id )
256 OR ( ( Recinfo.parent_entity_id IS NULL )
257 AND ( x_parent_entity_id IS NULL ) ) )
258 AND ( ( Recinfo.parent_fk_column1 = x_parent_fk_column1 )
259 OR ( ( Recinfo.parent_fk_column1 IS NULL )
260 AND ( x_parent_fk_column1 IS NULL ) ) )
261 AND ( ( Recinfo.parent_fk_column2 = x_parent_fk_column2 )
262 OR ( ( Recinfo.parent_fk_column2 IS NULL )
263 AND ( x_parent_fk_column2 IS NULL ) ) )
264 AND ( ( Recinfo.parent_fk_column3 = x_parent_fk_column3 )
265 OR ( ( Recinfo.parent_fk_column3 IS NULL )
266 AND ( x_parent_fk_column3 IS NULL ) ) )
267 AND ( ( Recinfo.parent_fk_column4 = x_parent_fk_column4 )
268 OR ( ( Recinfo.parent_fk_column4 IS NULL )
269 AND ( x_parent_fk_column4 IS NULL ) ) )
270 AND ( ( Recinfo.parent_fk_column5 = x_parent_fk_column5 )
271 OR ( ( Recinfo.parent_fk_column5 IS NULL )
272 AND ( x_parent_fk_column5 IS NULL ) ) )
273 AND ( ( Recinfo.group_assignment_level = x_group_assignment_level )
274 OR ( ( Recinfo.group_assignment_level IS NULL )
275 AND ( x_group_assignment_level IS NULL ) ) )
276 AND ( ( Recinfo.last_update_date = x_last_update_date )
277 OR ( ( Recinfo.last_update_date IS NULL )
278 AND ( x_last_update_date IS NULL ) ) )
279 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
280 OR ( ( Recinfo.last_updated_by IS NULL )
281 AND ( x_last_updated_by IS NULL ) ) )
282 AND ( ( Recinfo.creation_date = x_creation_date )
283 OR ( ( Recinfo.creation_date IS NULL )
284 AND ( x_creation_date IS NULL ) ) )
285 AND ( ( Recinfo.created_by = x_created_by )
286 OR ( ( Recinfo.created_by IS NULL )
287 AND ( x_created_by IS NULL ) ) )
288 AND ( ( Recinfo.last_update_login = x_last_update_login )
289 OR ( ( Recinfo.last_update_login IS NULL )
290 AND ( x_last_update_login IS NULL ) ) )
291 AND ( ( Recinfo.object_version_number = x_object_version_number )
292 OR ( ( Recinfo.object_version_number IS NULL )
293 AND ( x_object_version_number IS NULL ) ) )
294 ) THEN
295 RETURN;
296 ELSE
297 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
298 APP_EXCEPTION.RAISE_EXCEPTION;
299 END IF;
300
301 END Lock_Row;
302
303 PROCEDURE Select_Row (
304 x_entity_id IN OUT NOCOPY NUMBER,
305 x_status OUT NOCOPY VARCHAR2,
306 x_object_id OUT NOCOPY NUMBER,
307 x_instance_set_id OUT NOCOPY NUMBER,
308 x_parent_entity_id OUT NOCOPY NUMBER,
309 x_parent_fk_column1 OUT NOCOPY VARCHAR2,
310 x_parent_fk_column2 OUT NOCOPY VARCHAR2,
311 x_parent_fk_column3 OUT NOCOPY VARCHAR2,
312 x_parent_fk_column4 OUT NOCOPY VARCHAR2,
313 x_parent_fk_column5 OUT NOCOPY VARCHAR2,
314 x_group_assignment_level OUT NOCOPY VARCHAR2,
315 x_object_version_number OUT NOCOPY NUMBER
316 ) IS
317 BEGIN
318
319 SELECT
320 NVL(entity_id, FND_API.G_MISS_NUM),
321 NVL(status, FND_API.G_MISS_CHAR),
322 NVL(object_id, FND_API.G_MISS_NUM),
323 NVL(instance_set_id, FND_API.G_MISS_NUM),
324 NVL(parent_entity_id, FND_API.G_MISS_NUM),
325 NVL(parent_fk_column1, FND_API.G_MISS_CHAR),
326 NVL(parent_fk_column2, FND_API.G_MISS_CHAR),
327 NVL(parent_fk_column3, FND_API.G_MISS_CHAR),
328 NVL(parent_fk_column4, FND_API.G_MISS_CHAR),
329 NVL(parent_fk_column5, FND_API.G_MISS_CHAR),
330 NVL(group_assignment_level, FND_API.G_MISS_CHAR),
331 NVL(object_version_number, FND_API.G_MISS_NUM)
332 INTO
333 x_entity_id,
334 x_status,
335 x_object_id,
336 x_instance_set_id,
337 x_parent_entity_id,
338 x_parent_fk_column1,
339 x_parent_fk_column2,
340 x_parent_fk_column3,
341 x_parent_fk_column4,
342 x_parent_fk_column5,
343 x_group_assignment_level,
344 x_object_version_number
345 FROM HZ_DSS_ENTITIES
346 WHERE entity_id = x_entity_id;
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
351 FND_MESSAGE.SET_TOKEN('RECORD', 'dss_entity_profile');
352 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_entity_id));
353 FND_MSG_PUB.ADD;
354 RAISE FND_API.G_EXC_ERROR;
355
356 END Select_Row;
357
358 PROCEDURE Delete_Row (
359 x_entity_id IN NUMBER
360 ) IS
361 BEGIN
362
363 DELETE FROM HZ_DSS_ENTITIES
364 WHERE entity_id = x_entity_id;
365
366 IF ( SQL%NOTFOUND ) THEN
367 RAISE NO_DATA_FOUND;
368 END IF;
369
370 END Delete_Row;
371
372 END HZ_DSS_ENTITIES_PKG;