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