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