[Home] [Help]
PACKAGE BODY: APPS.HZ_ADDRESS_USAGES_PKG
Source
1 PACKAGE BODY HZ_ADDRESS_USAGES_PKG AS
2 /*$Header: ARHGNRRB.pls 120.0 2005/07/28 02:17:34 baianand noship $ */
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_usage_id IN OUT NOCOPY NUMBER,
7 x_map_id IN NUMBER,
8 x_usage_code IN VARCHAR2,
9 x_status_flag IN VARCHAR2,
10 x_object_version_number IN NUMBER,
11 x_created_by_module IN VARCHAR2,
12 x_application_id IN NUMBER
13 ) IS
14
15 l_success VARCHAR2(1) := 'N';
16
17 BEGIN
18
19 WHILE l_success = 'N' LOOP
20 BEGIN
21 INSERT INTO HZ_ADDRESS_USAGES (
22 usage_id,
23 map_id,
24 usage_code,
25 status_flag,
26 created_by,
27 creation_date,
28 last_updated_by,
29 last_update_date,
30 last_update_login,
31 object_version_number,
32 created_by_module,
33 application_id
34 )
35 VALUES (
36 DECODE(x_usage_id,
37 FND_API.G_MISS_NUM, HZ_ADDRESS_USAGES_S.NEXTVAL,
38 NULL, HZ_ADDRESS_USAGES_S.NEXTVAL,
39 x_usage_id),
40 DECODE(x_map_id,
41 FND_API.G_MISS_NUM, NULL,
42 x_map_id),
43 DECODE(x_usage_code,
44 FND_API.G_MISS_CHAR, NULL,
45 x_usage_code),
46 DECODE(x_status_flag,
47 FND_API.G_MISS_CHAR, NULL,
48 x_status_flag),
49 hz_utility_v2pub.created_by,
50 hz_utility_v2pub.creation_date,
51 hz_utility_v2pub.last_updated_by,
52 hz_utility_v2pub.last_update_date,
53 hz_utility_v2pub.last_update_login,
54 DECODE(x_object_version_number,
55 FND_API.G_MISS_NUM, NULL,
56 x_object_version_number),
57 DECODE(x_created_by_module,
58 FND_API.G_MISS_CHAR, NULL,
59 x_created_by_module),
60 DECODE(x_application_id,
61 FND_API.G_MISS_NUM, NULL,
62 x_application_id)
63 ) RETURNING
64 rowid,
65 usage_id
66 INTO
67 x_rowid,
68 x_usage_id;
69
70 l_success := 'Y';
71
72 EXCEPTION
73 WHEN DUP_VAL_ON_INDEX THEN
74 IF INSTR(SQLERRM, 'HZ_ADDRESS_USAGES_U1') <> 0 THEN
75 DECLARE
76 l_count NUMBER;
77 l_dummy VARCHAR2(1);
78 BEGIN
79 l_count := 1;
80 WHILE l_count > 0 LOOP
81 SELECT HZ_ADDRESS_USAGES_S.NEXTVAL
82 INTO x_usage_id FROM dual;
83 BEGIN
84 SELECT 'Y' INTO l_dummy
85 FROM HZ_ADDRESS_USAGES
86 WHERE usage_id = x_usage_id;
87 l_count := 1;
88 EXCEPTION
89 WHEN NO_DATA_FOUND THEN
90 l_count := 0;
91 END;
92 END LOOP;
93 END;
94 ELSE
95 RAISE DUP_VAL_ON_INDEX;
96 END IF;
97
98 END;
99 END LOOP;
100
101 END Insert_Row;
102
103 PROCEDURE Update_Row (
104 x_rowid IN OUT NOCOPY VARCHAR2,
105 x_usage_id IN NUMBER,
106 x_map_id IN NUMBER,
107 x_usage_code IN VARCHAR2,
108 x_status_flag IN VARCHAR2,
109 x_object_version_number IN NUMBER,
110 x_created_by_module IN VARCHAR2,
111 x_application_id IN NUMBER
112 ) IS
113 BEGIN
114
115 UPDATE HZ_ADDRESS_USAGES
116 SET
117 usage_id =
118 DECODE(x_usage_id,
119 NULL, usage_id,
120 FND_API.G_MISS_NUM, NULL,
121 x_usage_id),
122 map_id =
123 DECODE(x_map_id,
124 NULL, map_id,
125 FND_API.G_MISS_NUM, NULL,
126 x_map_id),
127 usage_code =
128 DECODE(x_usage_code,
129 NULL, usage_code,
130 FND_API.G_MISS_CHAR, NULL,
131 x_usage_code),
132 status_flag =
133 DECODE(x_status_flag,
134 NULL, status_flag,
135 FND_API.G_MISS_CHAR, NULL,
136 x_status_flag),
137 created_by = created_by,
138 creation_date = creation_date,
139 last_updated_by = hz_utility_v2pub.last_updated_by,
140 last_update_date = hz_utility_v2pub.last_update_date,
141 last_update_login = hz_utility_v2pub.last_update_login,
142 object_version_number =
143 DECODE(x_object_version_number,
144 NULL, object_version_number,
145 FND_API.G_MISS_NUM, NULL,
146 x_object_version_number),
147 created_by_module =
148 DECODE(x_created_by_module,
149 NULL, created_by_module,
150 FND_API.G_MISS_CHAR, NULL,
151 x_created_by_module),
152 application_id =
153 DECODE(x_application_id,
154 NULL, application_id,
155 FND_API.G_MISS_NUM, NULL,
156 x_application_id)
157 WHERE rowid = x_rowid;
158
159 IF ( SQL%NOTFOUND ) THEN
160 RAISE NO_DATA_FOUND;
161 END IF;
162
163 END Update_Row;
164
165 PROCEDURE Lock_Row (
166 x_rowid IN OUT NOCOPY VARCHAR2,
167 x_usage_id IN NUMBER,
168 x_map_id IN NUMBER,
169 x_usage_code IN VARCHAR2,
170 x_status_flag IN VARCHAR2,
171 x_created_by IN NUMBER,
172 x_creation_date IN DATE,
173 x_last_updated_by IN NUMBER,
174 x_last_update_date IN DATE,
175 x_last_update_login IN NUMBER,
176 x_object_version_number IN NUMBER,
177 x_created_by_module IN VARCHAR2,
178 x_application_id IN NUMBER
179 ) IS
180
181 CURSOR c IS
182 SELECT * FROM hz_address_usages
183 WHERE rowid = x_rowid
184 FOR UPDATE NOWAIT;
185 Recinfo c%ROWTYPE;
186
187 BEGIN
188
189 OPEN c;
190 FETCH c INTO Recinfo;
191 IF ( c%NOTFOUND ) THEN
192 CLOSE c;
193 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
194 APP_EXCEPTION.RAISE_EXCEPTION;
195 END IF;
196 CLOSE C;
197
198 IF (
199 ( ( Recinfo.usage_id = x_usage_id )
200 OR ( ( Recinfo.usage_id IS NULL )
201 AND ( x_usage_id IS NULL ) ) )
202 AND ( ( Recinfo.map_id = x_map_id )
203 OR ( ( Recinfo.map_id IS NULL )
204 AND ( x_map_id IS NULL ) ) )
205 AND ( ( Recinfo.usage_code = x_usage_code )
206 OR ( ( Recinfo.usage_code IS NULL )
207 AND ( x_usage_code IS NULL ) ) )
208 AND ( ( Recinfo.status_flag = x_status_flag )
209 OR ( ( Recinfo.status_flag IS NULL )
210 AND ( x_status_flag IS NULL ) ) )
211 AND ( ( Recinfo.created_by = x_created_by )
212 OR ( ( Recinfo.created_by IS NULL )
213 AND ( x_created_by IS NULL ) ) )
214 AND ( ( Recinfo.creation_date = x_creation_date )
215 OR ( ( Recinfo.creation_date IS NULL )
216 AND ( x_creation_date IS NULL ) ) )
217 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
218 OR ( ( Recinfo.last_updated_by IS NULL )
219 AND ( x_last_updated_by IS NULL ) ) )
220 AND ( ( Recinfo.last_update_date = x_last_update_date )
221 OR ( ( Recinfo.last_update_date IS NULL )
222 AND ( x_last_update_date IS NULL ) ) )
223 AND ( ( Recinfo.last_update_login = x_last_update_login )
224 OR ( ( Recinfo.last_update_login IS NULL )
225 AND ( x_last_update_login IS NULL ) ) )
226 AND ( ( Recinfo.object_version_number = x_object_version_number )
227 OR ( ( Recinfo.object_version_number IS NULL )
228 AND ( x_object_version_number IS NULL ) ) )
229 AND ( ( Recinfo.created_by_module = x_created_by_module )
230 OR ( ( Recinfo.created_by_module IS NULL )
231 AND ( x_created_by_module IS NULL ) ) )
232 AND ( ( Recinfo.application_id = x_application_id )
233 OR ( ( Recinfo.application_id IS NULL )
234 AND ( x_application_id IS NULL ) ) )
235 ) THEN
236 RETURN;
237 ELSE
238 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
239 APP_EXCEPTION.RAISE_EXCEPTION;
240 END IF;
241
242 END Lock_Row;
243
244 PROCEDURE Select_Row (
245 x_usage_id IN OUT NOCOPY NUMBER,
246 x_map_id OUT NOCOPY NUMBER,
247 x_usage_code OUT NOCOPY VARCHAR2,
248 x_status_flag OUT NOCOPY VARCHAR2,
249 x_object_version_number OUT NOCOPY NUMBER,
250 x_created_by_module OUT NOCOPY VARCHAR2,
251 x_application_id OUT NOCOPY NUMBER
252 ) IS
253 BEGIN
254
255 SELECT
256 NVL(usage_id, FND_API.G_MISS_NUM),
257 NVL(map_id, FND_API.G_MISS_NUM),
258 NVL(usage_code, FND_API.G_MISS_CHAR),
259 NVL(status_flag, FND_API.G_MISS_CHAR),
260 NVL(created_by_module, FND_API.G_MISS_CHAR),
261 NVL(application_id, FND_API.G_MISS_NUM)
262 INTO
263 x_usage_id,
264 x_map_id,
265 x_usage_code,
266 x_status_flag,
267 x_created_by_module,
268 x_application_id
269 FROM HZ_ADDRESS_USAGES
270 WHERE usage_id = x_usage_id;
271
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
275 FND_MESSAGE.SET_TOKEN('RECORD', 'address_usages_rec');
276 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_usage_id));
277 FND_MSG_PUB.ADD;
278 RAISE FND_API.G_EXC_ERROR;
279
280 END Select_Row;
281
282 PROCEDURE Delete_Row (
283 x_usage_id IN NUMBER
284 ) IS
285 BEGIN
286
287 DELETE FROM HZ_ADDRESS_USAGES
288 WHERE usage_id = x_usage_id;
289
290 IF ( SQL%NOTFOUND ) THEN
291 RAISE NO_DATA_FOUND;
292 END IF;
293
294 END Delete_Row;
295
296 END HZ_ADDRESS_USAGES_PKG;