[Home] [Help]
PACKAGE BODY: APPS.HZ_GEOGRAPHY_IDENTIFIERS_PKG
Source
1 PACKAGE BODY HZ_GEOGRAPHY_IDENTIFIERS_PKG AS
2 /*$Header: ARHGIDTB.pls 115.2 2003/02/19 00:26:53 smattegu noship $ */
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_geography_id IN NUMBER,
7 x_identifier_subtype IN VARCHAR2,
8 x_identifier_value IN VARCHAR2,
9 x_geo_data_provider IN VARCHAR2,
10 x_object_version_number IN NUMBER,
11 x_identifier_type IN VARCHAR2,
12 x_primary_flag IN VARCHAR2,
13 x_language_code IN VARCHAR2,
14 x_geography_use IN VARCHAR2,
15 x_geography_type IN VARCHAR2,
16 x_created_by_module IN VARCHAR2,
17 x_application_id IN NUMBER,
18 x_program_login_id IN NUMBER
19 ) IS
20
21
22 BEGIN
23 --dbms_output.put_line.PUT_LINE('before identifier insert in tblhandler');
24 --dbms_output.put_line.PUT_LINE('geography_id '||to_char(x_geography_id)||',subtype '||x_identifier_subtype||',value '||x_identifier_value ||',provider '||x_geo_data_provider||', version '||to_char(x_object_version_number));
25 --dbms_output.put_line.PUT_LINE('type '||x_identifier_type||',p_flag '||x_primary_flag||',use '||x_geography_use|| ',geo_type '||x_geography_type);
26
27 INSERT INTO HZ_GEOGRAPHY_IDENTIFIERS (
28 geography_id,
29 identifier_subtype,
30 identifier_value,
31 geo_data_provider,
32 object_version_number,
33 identifier_type,
34 primary_flag,
35 language_code,
36 geography_use,
37 geography_type,
38 created_by_module,
39 last_updated_by,
40 creation_date,
41 created_by,
42 last_update_date,
43 last_update_login,
44 application_id,
45 program_id,
46 program_login_id,
47 program_application_id,
48 request_id
49 )
50 VALUES (
51 DECODE(x_geography_id,
52 FND_API.G_MISS_NUM, NULL,
53 x_geography_id),
54 DECODE(x_identifier_subtype,
55 FND_API.G_MISS_CHAR, NULL,
56 x_identifier_subtype),
57 DECODE(x_identifier_value,
58 FND_API.G_MISS_CHAR, NULL,
59 x_identifier_value),
60 DECODE(x_geo_data_provider,
61 FND_API.G_MISS_CHAR, NULL,
62 x_geo_data_provider),
63 DECODE(x_object_version_number,
64 FND_API.G_MISS_NUM, NULL,
65 x_object_version_number),
66 DECODE(x_identifier_type,
67 FND_API.G_MISS_CHAR, NULL,
68 x_identifier_type),
69 DECODE(x_primary_flag,
70 FND_API.G_MISS_CHAR, NULL,
71 x_primary_flag),
72 DECODE(x_language_code,
73 FND_API.G_MISS_CHAR, NULL,
74 x_language_code),
75 DECODE(x_geography_use,
76 FND_API.G_MISS_CHAR, NULL,
77 x_geography_use),
78 DECODE(x_geography_type,
79 FND_API.G_MISS_CHAR, NULL,
80 x_geography_type),
81 DECODE(x_created_by_module,
82 FND_API.G_MISS_CHAR, NULL,
83 x_created_by_module),
84 hz_utility_v2pub.last_updated_by,
85 hz_utility_v2pub.creation_date,
86 hz_utility_v2pub.created_by,
87 hz_utility_v2pub.last_update_date,
88 hz_utility_v2pub.last_update_login,
89 DECODE(x_application_id,
90 FND_API.G_MISS_NUM, NULL,
91 x_application_id),
92 hz_utility_v2pub.program_id,
93 DECODE(x_program_login_id,
94 FND_API.G_MISS_NUM, NULL,
95 x_program_login_id),
96 hz_utility_v2pub.program_application_id,
97 hz_utility_v2pub.request_id
98 ) RETURNING
99 rowid
100 INTO
101 x_rowid;
102
103 --dbms_output.put_line.PUT_LINE('after identifier insert in tblhandler rowid is '||x_rowid);
104
105 END Insert_Row;
106
107 PROCEDURE Update_Row (
108 x_rowid IN OUT NOCOPY VARCHAR2,
109 x_geography_id IN NUMBER,
110 x_identifier_subtype IN VARCHAR2,
111 x_identifier_value IN VARCHAR2,
112 x_geo_data_provider IN VARCHAR2,
113 x_object_version_number IN NUMBER,
114 x_identifier_type IN VARCHAR2,
115 x_primary_flag IN VARCHAR2,
116 x_language_code IN VARCHAR2,
117 x_geography_use IN VARCHAR2,
118 x_geography_type IN VARCHAR2,
119 x_created_by_module IN VARCHAR2,
120 x_application_id IN NUMBER,
121 x_program_login_id IN NUMBER
122 ) IS
123 BEGIN
124
125 UPDATE HZ_GEOGRAPHY_IDENTIFIERS
126 SET
127 geography_id =
128 DECODE(x_geography_id,
129 NULL, geography_id,
130 FND_API.G_MISS_NUM, NULL,
131 x_geography_id),
132 identifier_subtype =
133 DECODE(x_identifier_subtype,
134 NULL, identifier_subtype,
135 FND_API.G_MISS_CHAR, NULL,
136 x_identifier_subtype),
137 identifier_value =
138 DECODE(x_identifier_value,
139 NULL, identifier_value,
140 FND_API.G_MISS_CHAR, NULL,
141 x_identifier_value),
142 geo_data_provider =
143 DECODE(x_geo_data_provider,
144 NULL, geo_data_provider,
145 FND_API.G_MISS_CHAR, NULL,
146 x_geo_data_provider),
147 object_version_number =
148 DECODE(x_object_version_number,
149 NULL, object_version_number,
150 FND_API.G_MISS_NUM, NULL,
151 x_object_version_number),
152 identifier_type =
153 DECODE(x_identifier_type,
154 NULL, identifier_type,
155 FND_API.G_MISS_CHAR, NULL,
156 x_identifier_type),
157 primary_flag =
158 DECODE(x_primary_flag,
159 NULL, primary_flag,
160 FND_API.G_MISS_CHAR, NULL,
161 x_primary_flag),
162 language_code =
163 DECODE(x_language_code,
164 NULL, language_code,
165 FND_API.G_MISS_CHAR, NULL,
166 x_language_code),
167 geography_use =
168 DECODE(x_geography_use,
169 NULL, geography_use,
170 FND_API.G_MISS_CHAR, NULL,
171 x_geography_use),
172 geography_type =
173 DECODE(x_geography_type,
174 NULL, geography_type,
175 FND_API.G_MISS_CHAR, NULL,
176 x_geography_type),
177 created_by_module =
178 DECODE(x_created_by_module,
179 NULL, created_by_module,
180 FND_API.G_MISS_CHAR, NULL,
181 x_created_by_module),
182 last_updated_by = hz_utility_v2pub.last_updated_by,
183 creation_date = creation_date,
184 created_by = created_by,
185 last_update_date = hz_utility_v2pub.last_update_date,
186 last_update_login = hz_utility_v2pub.last_update_login,
187 application_id =
188 DECODE(x_application_id,
189 NULL, application_id,
190 FND_API.G_MISS_NUM, NULL,
191 x_application_id),
192 program_id = hz_utility_v2pub.program_id,
193 program_login_id =
194 DECODE(x_program_login_id,
195 NULL, program_login_id,
196 FND_API.G_MISS_NUM, NULL,
197 x_program_login_id),
198 program_application_id = hz_utility_v2pub.program_application_id,
199 request_id = hz_utility_v2pub.request_id
200 WHERE rowid = x_rowid;
201
202 IF ( SQL%NOTFOUND ) THEN
203 RAISE NO_DATA_FOUND;
204 END IF;
205
206 END Update_Row;
207
208 PROCEDURE Lock_Row (
209 x_rowid IN OUT NOCOPY VARCHAR2,
210 x_geography_id IN NUMBER,
211 x_identifier_subtype IN VARCHAR2,
212 x_identifier_value IN VARCHAR2,
213 x_geo_data_provider IN VARCHAR2,
214 x_object_version_number IN NUMBER,
215 x_identifier_type IN VARCHAR2,
216 x_primary_flag IN VARCHAR2,
217 x_language_code IN VARCHAR2,
218 x_geography_use IN VARCHAR2,
219 x_geography_type IN VARCHAR2,
220 x_created_by_module IN VARCHAR2,
221 x_last_updated_by IN NUMBER,
222 x_creation_date IN DATE,
223 x_created_by IN NUMBER,
224 x_last_update_date IN DATE,
225 x_last_update_login IN NUMBER,
226 x_application_id IN NUMBER,
227 x_program_id IN NUMBER,
228 x_program_login_id IN NUMBER,
229 x_program_application_id IN NUMBER,
230 x_request_id IN NUMBER
231 ) IS
232
233 CURSOR c IS
234 SELECT * FROM hz_geography_identifiers
235 WHERE rowid = x_rowid
236 FOR UPDATE NOWAIT;
237 Recinfo c%ROWTYPE;
238
239 BEGIN
240
241 OPEN c;
242 FETCH c INTO Recinfo;
243 IF ( c%NOTFOUND ) THEN
244 CLOSE c;
245 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
246 APP_EXCEPTION.RAISE_EXCEPTION;
247 END IF;
248 CLOSE C;
249
250 IF (
251 ( ( Recinfo.geography_id = x_geography_id )
252 OR ( ( Recinfo.geography_id IS NULL )
253 AND ( x_geography_id IS NULL ) ) )
254 AND ( ( Recinfo.identifier_subtype = x_identifier_subtype )
255 OR ( ( Recinfo.identifier_subtype IS NULL )
256 AND ( x_identifier_subtype IS NULL ) ) )
257 AND ( ( Recinfo.identifier_value = x_identifier_value )
258 OR ( ( Recinfo.identifier_value IS NULL )
259 AND ( x_identifier_value IS NULL ) ) )
260 AND ( ( Recinfo.geo_data_provider = x_geo_data_provider )
261 OR ( ( Recinfo.geo_data_provider IS NULL )
262 AND ( x_geo_data_provider IS NULL ) ) )
263 AND ( ( Recinfo.object_version_number = x_object_version_number )
264 OR ( ( Recinfo.object_version_number IS NULL )
265 AND ( x_object_version_number IS NULL ) ) )
266 AND ( ( Recinfo.identifier_type = x_identifier_type )
267 OR ( ( Recinfo.identifier_type IS NULL )
268 AND ( x_identifier_type IS NULL ) ) )
269 AND ( ( Recinfo.primary_flag = x_primary_flag )
270 OR ( ( Recinfo.primary_flag IS NULL )
271 AND ( x_primary_flag IS NULL ) ) )
272 AND ( ( Recinfo.language_code = x_language_code )
273 OR ( ( Recinfo.language_code IS NULL )
274 AND ( x_language_code IS NULL ) ) )
275 AND ( ( Recinfo.geography_use = x_geography_use )
276 OR ( ( Recinfo.geography_use IS NULL )
277 AND ( x_geography_use IS NULL ) ) )
278 AND ( ( Recinfo.geography_type = x_geography_type )
282 OR ( ( Recinfo.created_by_module IS NULL )
279 OR ( ( Recinfo.geography_type IS NULL )
280 AND ( x_geography_type IS NULL ) ) )
281 AND ( ( Recinfo.created_by_module = x_created_by_module )
283 AND ( x_created_by_module IS NULL ) ) )
284 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
285 OR ( ( Recinfo.last_updated_by IS NULL )
286 AND ( x_last_updated_by IS NULL ) ) )
287 AND ( ( Recinfo.creation_date = x_creation_date )
288 OR ( ( Recinfo.creation_date IS NULL )
289 AND ( x_creation_date IS NULL ) ) )
290 AND ( ( Recinfo.created_by = x_created_by )
291 OR ( ( Recinfo.created_by IS NULL )
292 AND ( x_created_by IS NULL ) ) )
293 AND ( ( Recinfo.last_update_date = x_last_update_date )
294 OR ( ( Recinfo.last_update_date IS NULL )
295 AND ( x_last_update_date IS NULL ) ) )
296 AND ( ( Recinfo.last_update_login = x_last_update_login )
297 OR ( ( Recinfo.last_update_login IS NULL )
298 AND ( x_last_update_login IS NULL ) ) )
299 AND ( ( Recinfo.application_id = x_application_id )
300 OR ( ( Recinfo.application_id IS NULL )
301 AND ( x_application_id IS NULL ) ) )
302 AND ( ( Recinfo.program_id = x_program_id )
303 OR ( ( Recinfo.program_id IS NULL )
304 AND ( x_program_id IS NULL ) ) )
305 AND ( ( Recinfo.program_login_id = x_program_login_id )
306 OR ( ( Recinfo.program_login_id IS NULL )
307 AND ( x_program_login_id IS NULL ) ) )
308 AND ( ( Recinfo.program_application_id = x_program_application_id )
309 OR ( ( Recinfo.program_application_id IS NULL )
310 AND ( x_program_application_id IS NULL ) ) )
311 AND ( ( Recinfo.request_id = x_request_id )
312 OR ( ( Recinfo.request_id IS NULL )
313 AND ( x_request_id IS NULL ) ) )
314 ) THEN
315 RETURN;
316 ELSE
317 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
318 APP_EXCEPTION.RAISE_EXCEPTION;
319 END IF;
320
321 END Lock_Row;
322
323 PROCEDURE Select_Row (
324 x_geography_id IN OUT NOCOPY NUMBER,
325 x_identifier_subtype IN OUT NOCOPY VARCHAR2,
326 x_identifier_value IN OUT NOCOPY VARCHAR2,
327 x_geo_data_provider IN OUT NOCOPY VARCHAR2,
328 x_object_version_number OUT NOCOPY NUMBER,
329 x_identifier_type IN OUT NOCOPY VARCHAR2,
330 x_primary_flag OUT NOCOPY VARCHAR2,
331 x_language_code OUT NOCOPY VARCHAR2,
332 x_geography_use OUT NOCOPY VARCHAR2,
333 x_geography_type OUT NOCOPY VARCHAR2,
334 x_created_by_module OUT NOCOPY VARCHAR2,
335 x_application_id OUT NOCOPY NUMBER,
336 x_program_login_id OUT NOCOPY NUMBER
337 ) IS
338 BEGIN
339
340 SELECT
341 NVL(geography_id, FND_API.G_MISS_NUM),
342 NVL(identifier_subtype, FND_API.G_MISS_CHAR),
343 NVL(identifier_value, FND_API.G_MISS_CHAR),
344 NVL(geo_data_provider, FND_API.G_MISS_CHAR),
345 NVL(identifier_type, FND_API.G_MISS_CHAR),
346 NVL(primary_flag, FND_API.G_MISS_CHAR),
347 NVL(language_code, FND_API.G_MISS_CHAR),
348 NVL(geography_use, FND_API.G_MISS_CHAR),
349 NVL(geography_type, FND_API.G_MISS_CHAR),
350 NVL(created_by_module, FND_API.G_MISS_CHAR),
351 NVL(application_id, FND_API.G_MISS_NUM),
352 NVL(program_login_id, FND_API.G_MISS_NUM)
353 INTO
354 x_geography_id,
355 x_identifier_subtype,
356 x_identifier_value,
357 x_geo_data_provider,
358 x_identifier_type,
359 x_primary_flag,
360 x_language_code,
361 x_geography_use,
362 x_geography_type,
363 x_created_by_module,
364 x_application_id,
365 x_program_login_id
366 FROM HZ_GEOGRAPHY_IDENTIFIERS
367 WHERE geography_id = x_geography_id
368 AND identifier_subtype = x_identifier_subtype
369 AND identifier_value = x_identifier_value
370 AND language_code = x_language_code
371 AND identifier_type = x_identifier_type;
372
373
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
377 FND_MESSAGE.SET_TOKEN('RECORD', 'geography_identifiers_rec');
378 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_geography_id));
379 FND_MSG_PUB.ADD;
380 RAISE FND_API.G_EXC_ERROR;
381
382 END Select_Row;
383
384 PROCEDURE Delete_Row (
385 x_geography_id IN NUMBER,
386 x_identifier_subtype IN VARCHAR2,
387 x_identifier_value IN VARCHAR2,
388 x_language_code IN VARCHAR2,
389 x_identifier_type IN VARCHAR2
390 ) IS
391 BEGIN
392
393 DELETE FROM HZ_GEOGRAPHY_IDENTIFIERS
394 WHERE geography_id = x_geography_id
395 AND identifier_subtype = x_identifier_subtype
396 AND identifier_value = x_identifier_value
397 AND language_code = x_language_code
398 AND identifier_type = x_identifier_type;
399
400 IF ( SQL%NOTFOUND ) THEN
401 RAISE NO_DATA_FOUND;
402 END IF;
403
404 END Delete_Row;
405
406 END HZ_GEOGRAPHY_IDENTIFIERS_PKG;