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