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