[Home] [Help]
PACKAGE BODY: APPS.CS_TIMEZONE_PVT
Source
1 PACKAGE BODY CS_TIMEZONE_PVT AS
2 /* $Header: cscttzob.pls 115.1 99/07/16 08:55:09 porting ship $ */
3 FUNCTION get_seq_id RETURN NUMBER IS
4 CURSOR get_seq_id_csr IS
5 SELECT CS_TIME_ZONES_S.nextval FROM SYS.DUAL;
6 l_seq_id NUMBER := 0;
7 BEGIN
8 OPEN get_seq_id_csr;
9 FETCH get_seq_id_csr INTO l_seq_id;
10 CLOSE get_seq_id_csr;
11 RETURN(l_seq_id);
12 END get_seq_id;
13
14
15 -- Validation
16 FUNCTION Validate_Item_Attributes
17 (
18 p_timezone_rec IN TimeZone_Rec_Type
19 )
20 RETURN VARCHAR2
21 IS
22 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
23 BEGIN
24 IF p_timezone_rec.name = TAPI_DEV_KIT.G_MISS_CHAR OR
25 p_timezone_rec.name IS NULL
26 THEN
27 TAPI_DEV_KIT.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'name');
28 l_return_status := FND_API.G_RET_STS_ERROR;
29 ELSIF p_timezone_rec.offset_time = TAPI_DEV_KIT.G_MISS_DATE OR
30 p_timezone_rec.offset_time IS NULL
31 THEN
32 TAPI_DEV_KIT.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'offset_time');
33 l_return_status := FND_API.G_RET_STS_ERROR;
34 ELSIF p_timezone_rec.last_update_date = TAPI_DEV_KIT.G_MISS_DATE OR
35 p_timezone_rec.last_update_date IS NULL
36 THEN
37 TAPI_DEV_KIT.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'last_update_date');
38 l_return_status := FND_API.G_RET_STS_ERROR;
39 ELSIF p_timezone_rec.last_updated_by = TAPI_DEV_KIT.G_MISS_NUM OR
40 p_timezone_rec.last_updated_by IS NULL
41 THEN
42 TAPI_DEV_KIT.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'last_updated_by');
43 l_return_status := FND_API.G_RET_STS_ERROR;
44 ELSIF p_timezone_rec.creation_date = TAPI_DEV_KIT.G_MISS_DATE OR
45 p_timezone_rec.creation_date IS NULL
46 THEN
47 TAPI_DEV_KIT.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'creation_date');
48 l_return_status := FND_API.G_RET_STS_ERROR;
49 ELSIF p_timezone_rec.created_by = TAPI_DEV_KIT.G_MISS_NUM OR
50 p_timezone_rec.created_by IS NULL
51 THEN
52 TAPI_DEV_KIT.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'created_by');
53 l_return_status := FND_API.G_RET_STS_ERROR;
54 END IF;
55 RETURN(l_return_status);
56 END Validate_Item_Attributes;
57
58
59 ----- Default
60 FUNCTION Default_Item_Attributes
61 (
62 p_timezone_rec IN TimeZone_Rec_Type,
63 l_def_timezone_rec OUT TimeZone_Rec_Type
64 )
65 RETURN VARCHAR2
66 IS
67 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
68 BEGIN
69 l_def_timezone_rec := p_timezone_rec;
70 l_def_timezone_rec.OBJECT_VERSION_NUMBER := NVL(l_def_timezone_rec.OBJECT_VERSION_NUMBER, 0) + 1;
71 RETURN(l_return_status);
72 End Default_Item_attributes;
73
74
75 FUNCTION Validate_Item_Record (
76 p_timezone_rec IN TimeZone_Rec_Type
77 )
78 RETURN VARCHAR2 IS
79 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
80 BEGIN
81 RETURN (l_return_status);
82 END Validate_Item_Record;
83
84
85 PROCEDURE migrate (
86 p_from IN TimeZone_Val_Rec_Type,
87 p_to OUT TimeZone_Rec_Type
88 ) IS
89 BEGIN
90 p_to.time_zone_id := p_from.time_zone_id;
91 p_to.name := p_from.name;
92 p_to.description := p_from.description;
93 p_to.offset_indicator := p_from.offset_indicator;
94 p_to.offset_time := p_from.offset_time;
95 p_to.last_update_date := p_from.last_update_date;
96 p_to.last_updated_by := p_from.last_updated_by;
97 p_to.creation_date := p_from.creation_date;
98 p_to.created_by := p_from.created_by;
99 p_to.last_update_login := p_from.last_update_login;
100 p_to.start_date_active := p_from.start_date_active;
101 p_to.end_date_active := p_from.end_date_active;
102 p_to.attribute1 := p_from.attribute1;
103 p_to.attribute2 := p_from.attribute2;
104 p_to.attribute3 := p_from.attribute3;
105 p_to.attribute4 := p_from.attribute4;
106 p_to.attribute5 := p_from.attribute5;
107 p_to.attribute6 := p_from.attribute6;
108 p_to.attribute7 := p_from.attribute7;
109 p_to.attribute8 := p_from.attribute8;
110 p_to.attribute9 := p_from.attribute9;
111 p_to.attribute10 := p_from.attribute10;
112 p_to.attribute11 := p_from.attribute11;
113 p_to.attribute12 := p_from.attribute12;
114 p_to.attribute13 := p_from.attribute13;
115 p_to.attribute14 := p_from.attribute14;
116 p_to.attribute15 := p_from.attribute15;
117 p_to.context := p_from.context;
118 p_to.object_version_number := p_from.object_version_number;
119 END migrate;
120 PROCEDURE migrate (
121 p_from IN TimeZone_Rec_Type,
122 p_to OUT TimeZone_Val_Rec_Type
123 ) IS
124 BEGIN
125 p_to.time_zone_id := p_from.time_zone_id;
126 p_to.name := p_from.name;
127 p_to.description := p_from.description;
128 p_to.offset_indicator := p_from.offset_indicator;
129 p_to.offset_time := p_from.offset_time;
130 p_to.last_update_date := p_from.last_update_date;
131 p_to.last_updated_by := p_from.last_updated_by;
132 p_to.creation_date := p_from.creation_date;
133 p_to.created_by := p_from.created_by;
134 p_to.last_update_login := p_from.last_update_login;
135 p_to.start_date_active := p_from.start_date_active;
136 p_to.end_date_active := p_from.end_date_active;
137 p_to.attribute1 := p_from.attribute1;
138 p_to.attribute2 := p_from.attribute2;
139 p_to.attribute3 := p_from.attribute3;
140 p_to.attribute4 := p_from.attribute4;
141 p_to.attribute5 := p_from.attribute5;
142 p_to.attribute6 := p_from.attribute6;
143 p_to.attribute7 := p_from.attribute7;
144 p_to.attribute8 := p_from.attribute8;
145 p_to.attribute9 := p_from.attribute9;
146 p_to.attribute10 := p_from.attribute10;
147 p_to.attribute11 := p_from.attribute11;
148 p_to.attribute12 := p_from.attribute12;
149 p_to.attribute13 := p_from.attribute13;
150 p_to.attribute14 := p_from.attribute14;
151 p_to.attribute15 := p_from.attribute15;
152 p_to.context := p_from.context;
153 p_to.object_version_number := p_from.object_version_number;
154 END migrate;
155 PROCEDURE insert_row
156 (
157 p_api_version IN NUMBER,
158 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
159 p_validation_level IN NUMBER,
160 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
161 x_return_status OUT VARCHAR2,
162 x_msg_count OUT NUMBER,
163 x_msg_data OUT VARCHAR2,
164 p_timezone_rec IN TimeZone_Rec_Type := G_MISS_TIMEZONE_REC,
165 x_time_zone_id OUT NUMBER,
166 x_object_version_number OUT NUMBER) IS
167 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
168 l_api_version CONSTANT NUMBER := 1;
169 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
170 l_timezone_rec TimeZone_Rec_Type;
171 l_def_timezone_rec TimeZone_Rec_Type;
172 BEGIN
173 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
174 G_PKG_NAME,
175 l_api_version,
176 p_api_version,
177 p_init_msg_list,
178 '_Pvt',
179 x_return_status);
180 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
182 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185 l_timezone_rec := p_timezone_rec;
186 --- Validate all non-missing attributes (Item Level Validation)
187 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
188 l_return_status := Validate_Item_Attributes
189 (
190 l_timezone_rec ---- IN
191 );
192 --- If any errors happen abort API
193 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
195 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198 END IF;
199 --- Defaulting item attributes
200 l_return_status := Default_Item_Attributes
201 (
202 l_timezone_rec, ---- IN
203 l_def_timezone_rec
204 );
205 --- If any errors happen abort API
206 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
209 RAISE FND_API.G_EXC_ERROR;
210 END IF;
211 IF (p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
212 l_return_status := Validate_Item_Record(l_def_timezone_rec);
213 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
216 RAISE FND_API.G_EXC_ERROR;
217 END IF;
218 END IF;
219 -- Set primary key value
220 l_def_timezone_rec.time_zone_id := get_seq_id;
221 INSERT INTO CS_TIME_ZONES(
222 time_zone_id,
223 name,
224 description,
225 offset_indicator,
226 offset_time,
227 last_update_date,
228 last_updated_by,
229 creation_date,
230 created_by,
231 last_update_login,
232 start_date_active,
233 end_date_active,
234 attribute1,
235 attribute2,
236 attribute3,
237 attribute4,
238 attribute5,
239 attribute6,
240 attribute7,
241 attribute8,
242 attribute9,
243 attribute10,
244 attribute11,
245 attribute12,
246 attribute13,
247 attribute14,
248 attribute15,
249 context,
250 object_version_number)
251 VALUES (
252 l_def_timezone_rec.time_zone_id,
253 l_def_timezone_rec.name,
254 l_def_timezone_rec.description,
255 l_def_timezone_rec.offset_indicator,
256 l_def_timezone_rec.offset_time,
257 l_def_timezone_rec.last_update_date,
258 l_def_timezone_rec.last_updated_by,
259 l_def_timezone_rec.creation_date,
260 l_def_timezone_rec.created_by,
261 l_def_timezone_rec.last_update_login,
262 l_def_timezone_rec.start_date_active,
263 l_def_timezone_rec.end_date_active,
264 l_def_timezone_rec.attribute1,
265 l_def_timezone_rec.attribute2,
266 l_def_timezone_rec.attribute3,
267 l_def_timezone_rec.attribute4,
268 l_def_timezone_rec.attribute5,
269 l_def_timezone_rec.attribute6,
270 l_def_timezone_rec.attribute7,
271 l_def_timezone_rec.attribute8,
272 l_def_timezone_rec.attribute9,
273 l_def_timezone_rec.attribute10,
274 l_def_timezone_rec.attribute11,
275 l_def_timezone_rec.attribute12,
276 l_def_timezone_rec.attribute13,
277 l_def_timezone_rec.attribute14,
278 l_def_timezone_rec.attribute15,
279 l_def_timezone_rec.context,
280 l_def_timezone_rec.object_version_number);
281 -- Set OUT values
282 x_time_zone_id := l_def_timezone_rec.time_zone_id;
283 x_object_version_number := l_def_timezone_rec.OBJECT_VERSION_NUMBER;
284 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
285 EXCEPTION
286 WHEN FND_API.G_EXC_ERROR THEN
287 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
288 (
289 l_api_name,
290 G_PKG_NAME,
291 'FND_API.G_RET_STS_ERROR',
292 x_msg_count,
293 x_msg_data,
294 '_Pvt'
295 );
296 APP_EXCEPTION.RAISE_EXCEPTION;
297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
298 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
299 (
300 l_api_name,
301 G_PKG_NAME,
302 'FND_API.G_RET_STS_UNEXP_ERROR',
303 x_msg_count,
304 x_msg_data,
305 '_Pvt'
306 );
307 APP_EXCEPTION.RAISE_EXCEPTION;
308 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
309 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
310 (
311 l_api_name,
312 G_PKG_NAME,
313 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
314 x_msg_count,
315 x_msg_data,
316 '_Pvt'
317 );
318 APP_EXCEPTION.RAISE_EXCEPTION;
319 END insert_row;
320 PROCEDURE insert_row
321 (
322 p_api_version IN NUMBER,
323 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
324 p_validation_level IN NUMBER,
325 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
326 x_return_status OUT VARCHAR2,
327 x_msg_count OUT NUMBER,
328 x_msg_data OUT VARCHAR2,
329 p_name IN CS_TIME_ZONES.NAME%TYPE := NULL,
330 p_description IN CS_TIME_ZONES.DESCRIPTION%TYPE := NULL,
331 p_offset_indicator IN CS_TIME_ZONES.OFFSET_INDICATOR%TYPE := NULL,
332 p_offset_time IN CS_TIME_ZONES.OFFSET_TIME%TYPE := NULL,
333 p_last_update_date IN CS_TIME_ZONES.LAST_UPDATE_DATE%TYPE := NULL,
334 p_last_updated_by IN NUMBER := NULL,
335 p_creation_date IN CS_TIME_ZONES.CREATION_DATE%TYPE := NULL,
336 p_created_by IN NUMBER := NULL,
337 p_last_update_login IN NUMBER := NULL,
338 p_start_date_active IN CS_TIME_ZONES.START_DATE_ACTIVE%TYPE := NULL,
339 p_end_date_active IN CS_TIME_ZONES.END_DATE_ACTIVE%TYPE := NULL,
340 p_attribute1 IN CS_TIME_ZONES.ATTRIBUTE1%TYPE := NULL,
341 p_attribute2 IN CS_TIME_ZONES.ATTRIBUTE2%TYPE := NULL,
342 p_attribute3 IN CS_TIME_ZONES.ATTRIBUTE3%TYPE := NULL,
343 p_attribute4 IN CS_TIME_ZONES.ATTRIBUTE4%TYPE := NULL,
344 p_attribute5 IN CS_TIME_ZONES.ATTRIBUTE5%TYPE := NULL,
345 p_attribute6 IN CS_TIME_ZONES.ATTRIBUTE6%TYPE := NULL,
346 p_attribute7 IN CS_TIME_ZONES.ATTRIBUTE7%TYPE := NULL,
347 p_attribute8 IN CS_TIME_ZONES.ATTRIBUTE8%TYPE := NULL,
348 p_attribute9 IN CS_TIME_ZONES.ATTRIBUTE9%TYPE := NULL,
349 p_attribute10 IN CS_TIME_ZONES.ATTRIBUTE10%TYPE := NULL,
350 p_attribute11 IN CS_TIME_ZONES.ATTRIBUTE11%TYPE := NULL,
351 p_attribute12 IN CS_TIME_ZONES.ATTRIBUTE12%TYPE := NULL,
355 p_context IN CS_TIME_ZONES.CONTEXT%TYPE := NULL,
352 p_attribute13 IN CS_TIME_ZONES.ATTRIBUTE13%TYPE := NULL,
353 p_attribute14 IN CS_TIME_ZONES.ATTRIBUTE14%TYPE := NULL,
354 p_attribute15 IN CS_TIME_ZONES.ATTRIBUTE15%TYPE := NULL,
356 p_object_version_number IN NUMBER := NULL,
357 x_time_zone_id OUT NUMBER,
358 x_object_version_number OUT NUMBER) IS
359 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
360 l_api_version CONSTANT NUMBER := 1;
361 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
362 l_timezone_rec TimeZone_Rec_Type;
363 BEGIN
364 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
365 G_PKG_NAME,
366 l_api_version,
367 p_api_version,
368 p_init_msg_list,
369 '_Pvt',
370 x_return_status);
371 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
374 RAISE FND_API.G_EXC_ERROR;
375 END IF;
376 l_timezone_rec.NAME := p_name;
377 l_timezone_rec.DESCRIPTION := p_description;
378 l_timezone_rec.OFFSET_INDICATOR := p_offset_indicator;
379 l_timezone_rec.OFFSET_TIME := p_offset_time;
380 l_timezone_rec.LAST_UPDATE_DATE := p_last_update_date;
381 l_timezone_rec.LAST_UPDATED_BY := p_last_updated_by;
382 l_timezone_rec.CREATION_DATE := p_creation_date;
383 l_timezone_rec.CREATED_BY := p_created_by;
384 l_timezone_rec.LAST_UPDATE_LOGIN := p_last_update_login;
385 l_timezone_rec.START_DATE_ACTIVE := p_start_date_active;
386 l_timezone_rec.END_DATE_ACTIVE := p_end_date_active;
387 l_timezone_rec.ATTRIBUTE1 := p_attribute1;
388 l_timezone_rec.ATTRIBUTE2 := p_attribute2;
389 l_timezone_rec.ATTRIBUTE3 := p_attribute3;
390 l_timezone_rec.ATTRIBUTE4 := p_attribute4;
391 l_timezone_rec.ATTRIBUTE5 := p_attribute5;
392 l_timezone_rec.ATTRIBUTE6 := p_attribute6;
393 l_timezone_rec.ATTRIBUTE7 := p_attribute7;
394 l_timezone_rec.ATTRIBUTE8 := p_attribute8;
395 l_timezone_rec.ATTRIBUTE9 := p_attribute9;
396 l_timezone_rec.ATTRIBUTE10 := p_attribute10;
397 l_timezone_rec.ATTRIBUTE11 := p_attribute11;
398 l_timezone_rec.ATTRIBUTE12 := p_attribute12;
399 l_timezone_rec.ATTRIBUTE13 := p_attribute13;
400 l_timezone_rec.ATTRIBUTE14 := p_attribute14;
401 l_timezone_rec.ATTRIBUTE15 := p_attribute15;
402 l_timezone_rec.CONTEXT := p_context;
403 l_timezone_rec.OBJECT_VERSION_NUMBER := p_object_version_number;
404 insert_row(
405 p_api_version,
406 p_init_msg_list,
407 p_validation_level,
408 p_commit,
409 x_return_status,
410 x_msg_count,
411 x_msg_data,
412 l_timezone_rec,
413 x_time_zone_id,
414 x_object_version_number
415 );
416 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
417 EXCEPTION
418 WHEN FND_API.G_EXC_ERROR THEN
419 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
420 (
421 l_api_name,
422 G_PKG_NAME,
423 'FND_API.G_RET_STS_ERROR',
424 x_msg_count,
425 x_msg_data,
426 '_Pvt'
427 );
428 APP_EXCEPTION.RAISE_EXCEPTION;
429 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
430 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
431 (
432 l_api_name,
433 G_PKG_NAME,
434 'FND_API.G_RET_STS_UNEXP_ERROR',
435 x_msg_count,
436 x_msg_data,
437 '_Pvt'
438 );
439 APP_EXCEPTION.RAISE_EXCEPTION;
440 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
441 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
442 (
443 l_api_name,
444 G_PKG_NAME,
445 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
446 x_msg_count,
447 x_msg_data,
448 '_Pvt'
449 );
450 APP_EXCEPTION.RAISE_EXCEPTION;
451 END insert_row;
452 Procedure lock_row
453 (
454 p_api_version IN NUMBER,
455 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
456 p_validation_level IN NUMBER,
457 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
458 x_return_status OUT VARCHAR2,
459 x_msg_count OUT NUMBER,
460 x_msg_data OUT VARCHAR2,
461 p_time_zone_id IN NUMBER,
462 p_object_version_number IN NUMBER) IS
463 E_Resource_Busy EXCEPTION;
464 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
465 CURSOR lock_csr IS
466 SELECT OBJECT_VERSION_NUMBER
467 FROM CS_TIME_ZONES
468 WHERE
469 TIME_ZONE_ID = p_time_zone_id AND
470 OBJECT_VERSION_NUMBER = p_object_version_number
471 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
472
473 CURSOR lchk_csr IS
474 SELECT OBJECT_VERSION_NUMBER
475 FROM CS_TIME_ZONES
476 WHERE
477 TIME_ZONE_ID = p_time_zone_id
478 ;
479 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
480 l_api_version CONSTANT NUMBER := 1;
481 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
482 l_object_version_number CS_TIME_ZONES.OBJECT_VERSION_NUMBER%TYPE;
483 lc_object_version_number CS_TIME_ZONES.OBJECT_VERSION_NUMBER%TYPE;
487 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
484 l_row_notfound BOOLEAN := FALSE;
485 lc_row_notfound BOOLEAN := FALSE;
486 BEGIN
488 G_PKG_NAME,
489 l_api_version,
490 p_api_version,
491 p_init_msg_list,
492 '_Pvt',
493 x_return_status);
494 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
497 RAISE FND_API.G_EXC_ERROR;
498 END IF;
499 BEGIN
500 OPEN lock_csr;
501 FETCH lock_csr INTO l_object_version_number;
502 l_row_notfound := lock_csr%NOTFOUND;
503 CLOSE lock_csr;
504 EXCEPTION
505 WHEN E_Resource_Busy THEN
506 IF (lock_csr%ISOPEN) THEN
507 CLOSE lock_csr;
508 END IF;
509 TAPI_DEV_KIT.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
510 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
511 END;
512
513 IF ( l_row_notfound ) THEN
514 OPEN lchk_csr;
515 FETCH lchk_csr INTO lc_object_version_number;
516 lc_row_notfound := lchk_csr%NOTFOUND;
517 CLOSE lchk_csr;
518 END IF;
519 IF (lc_row_notfound) THEN
520 TAPI_DEV_KIT.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
521 RAISE FND_API.G_EXC_ERROR;
522 ELSIF lc_object_version_number > p_object_version_number THEN
523 TAPI_DEV_KIT.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
524 RAISE FND_API.G_EXC_ERROR;
525 ELSIF lc_object_version_number <> p_object_version_number THEN
526 TAPI_DEV_KIT.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
527 RAISE FND_API.G_EXC_ERROR;
528 ELSIF lc_object_version_number = -1 THEN
529 TAPI_DEV_KIT.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
533 EXCEPTION
534 WHEN FND_API.G_EXC_ERROR THEN
535 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
536 (
537 l_api_name,
538 G_PKG_NAME,
539 'FND_API.G_RET_STS_ERROR',
540 x_msg_count,
541 x_msg_data,
542 '_Pvt'
543 );
544 APP_EXCEPTION.RAISE_EXCEPTION;
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
547 (
548 l_api_name,
549 G_PKG_NAME,
550 'FND_API.G_RET_STS_UNEXP_ERROR',
551 x_msg_count,
552 x_msg_data,
553 '_Pvt'
554 );
555 APP_EXCEPTION.RAISE_EXCEPTION;
556 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
557 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
558 (
559 l_api_name,
560 G_PKG_NAME,
561 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
562 x_msg_count,
563 x_msg_data,
564 '_Pvt'
565 );
566 APP_EXCEPTION.RAISE_EXCEPTION;
567 END lock_row;
568 Procedure update_row
569 (
570 p_api_version IN NUMBER,
571 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
572 p_validation_level IN NUMBER,
573 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
574 x_return_status OUT VARCHAR2,
575 x_msg_count OUT NUMBER,
576 x_msg_data OUT VARCHAR2,
577 p_timezone_val_rec IN TimeZone_Val_Rec_Type := G_MISS_TIMEZONE_VAL_REC,
578 x_object_version_number OUT NUMBER) IS
579 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
580 l_api_version CONSTANT NUMBER := 1;
581 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
582 l_timezone_rec TimeZone_Rec_Type;
583 l_def_timezone_rec TimeZone_Rec_Type;
584 FUNCTION populate_new_record (
585 p_timezone_rec IN TimeZone_Rec_Type,
586 x_timezone_rec OUT TimeZone_Rec_Type
587 ) RETURN VARCHAR2 IS
588 CURSOR cs_time_zones_pk_csr (p_time_zone_id IN NUMBER) IS
589 SELECT *
590 FROM Cs_Time_Zones
591 WHERE cs_time_zones.time_zone_id = p_time_zone_id;
592 l_cs_time_zones_pk cs_time_zones_pk_csr%ROWTYPE;
593 l_row_notfound BOOLEAN := TRUE;
594 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
595 BEGIN
596 x_timezone_rec := p_timezone_rec;
597 -- Get current database values
598 OPEN cs_time_zones_pk_csr (p_timezone_rec.time_zone_id);
599 FETCH cs_time_zones_pk_csr INTO l_cs_time_zones_pk;
600 l_row_notfound := cs_time_zones_pk_csr%NOTFOUND;
601 CLOSE cs_time_zones_pk_csr;
602 IF (l_row_notfound) THEN
603 l_return_status := FND_API.G_RET_STS_ERROR;
604 END IF;
605 IF (x_timezone_rec.time_zone_id = TAPI_DEV_KIT.G_MISS_NUM)
606 THEN
607 x_timezone_rec.time_zone_id := l_cs_time_zones_pk.time_zone_id;
608 END IF;
609 IF (x_timezone_rec.name = TAPI_DEV_KIT.G_MISS_CHAR)
610 THEN
611 x_timezone_rec.name := l_cs_time_zones_pk.name;
612 END IF;
613 IF (x_timezone_rec.description = TAPI_DEV_KIT.G_MISS_CHAR)
614 THEN
615 x_timezone_rec.description := l_cs_time_zones_pk.description;
616 END IF;
617 IF (x_timezone_rec.offset_indicator = TAPI_DEV_KIT.G_MISS_CHAR)
618 THEN
622 THEN
619 x_timezone_rec.offset_indicator := l_cs_time_zones_pk.offset_indicator;
620 END IF;
621 IF (x_timezone_rec.offset_time = TAPI_DEV_KIT.G_MISS_DATE)
623 x_timezone_rec.offset_time := l_cs_time_zones_pk.offset_time;
624 END IF;
625 IF (x_timezone_rec.last_update_date = TAPI_DEV_KIT.G_MISS_DATE)
626 THEN
627 x_timezone_rec.last_update_date := l_cs_time_zones_pk.last_update_date;
628 END IF;
629 IF (x_timezone_rec.last_updated_by = TAPI_DEV_KIT.G_MISS_NUM)
630 THEN
631 x_timezone_rec.last_updated_by := l_cs_time_zones_pk.last_updated_by;
632 END IF;
633 IF (x_timezone_rec.creation_date = TAPI_DEV_KIT.G_MISS_DATE)
634 THEN
635 x_timezone_rec.creation_date := l_cs_time_zones_pk.creation_date;
636 END IF;
637 IF (x_timezone_rec.created_by = TAPI_DEV_KIT.G_MISS_NUM)
638 THEN
639 x_timezone_rec.created_by := l_cs_time_zones_pk.created_by;
640 END IF;
641 IF (x_timezone_rec.last_update_login = TAPI_DEV_KIT.G_MISS_NUM)
642 THEN
643 x_timezone_rec.last_update_login := l_cs_time_zones_pk.last_update_login;
644 END IF;
645 IF (x_timezone_rec.start_date_active = TAPI_DEV_KIT.G_MISS_DATE)
646 THEN
647 x_timezone_rec.start_date_active := l_cs_time_zones_pk.start_date_active;
648 END IF;
649 IF (x_timezone_rec.end_date_active = TAPI_DEV_KIT.G_MISS_DATE)
650 THEN
651 x_timezone_rec.end_date_active := l_cs_time_zones_pk.end_date_active;
652 END IF;
653 IF (x_timezone_rec.attribute1 = TAPI_DEV_KIT.G_MISS_CHAR)
654 THEN
655 x_timezone_rec.attribute1 := l_cs_time_zones_pk.attribute1;
656 END IF;
657 IF (x_timezone_rec.attribute2 = TAPI_DEV_KIT.G_MISS_CHAR)
658 THEN
659 x_timezone_rec.attribute2 := l_cs_time_zones_pk.attribute2;
660 END IF;
661 IF (x_timezone_rec.attribute3 = TAPI_DEV_KIT.G_MISS_CHAR)
662 THEN
663 x_timezone_rec.attribute3 := l_cs_time_zones_pk.attribute3;
664 END IF;
665 IF (x_timezone_rec.attribute4 = TAPI_DEV_KIT.G_MISS_CHAR)
666 THEN
667 x_timezone_rec.attribute4 := l_cs_time_zones_pk.attribute4;
668 END IF;
669 IF (x_timezone_rec.attribute5 = TAPI_DEV_KIT.G_MISS_CHAR)
670 THEN
671 x_timezone_rec.attribute5 := l_cs_time_zones_pk.attribute5;
672 END IF;
673 IF (x_timezone_rec.attribute6 = TAPI_DEV_KIT.G_MISS_CHAR)
674 THEN
675 x_timezone_rec.attribute6 := l_cs_time_zones_pk.attribute6;
676 END IF;
677 IF (x_timezone_rec.attribute7 = TAPI_DEV_KIT.G_MISS_CHAR)
678 THEN
679 x_timezone_rec.attribute7 := l_cs_time_zones_pk.attribute7;
680 END IF;
681 IF (x_timezone_rec.attribute8 = TAPI_DEV_KIT.G_MISS_CHAR)
682 THEN
683 x_timezone_rec.attribute8 := l_cs_time_zones_pk.attribute8;
684 END IF;
685 IF (x_timezone_rec.attribute9 = TAPI_DEV_KIT.G_MISS_CHAR)
686 THEN
687 x_timezone_rec.attribute9 := l_cs_time_zones_pk.attribute9;
688 END IF;
689 IF (x_timezone_rec.attribute10 = TAPI_DEV_KIT.G_MISS_CHAR)
690 THEN
691 x_timezone_rec.attribute10 := l_cs_time_zones_pk.attribute10;
692 END IF;
693 IF (x_timezone_rec.attribute11 = TAPI_DEV_KIT.G_MISS_CHAR)
694 THEN
695 x_timezone_rec.attribute11 := l_cs_time_zones_pk.attribute11;
696 END IF;
697 IF (x_timezone_rec.attribute12 = TAPI_DEV_KIT.G_MISS_CHAR)
698 THEN
699 x_timezone_rec.attribute12 := l_cs_time_zones_pk.attribute12;
700 END IF;
701 IF (x_timezone_rec.attribute13 = TAPI_DEV_KIT.G_MISS_CHAR)
702 THEN
703 x_timezone_rec.attribute13 := l_cs_time_zones_pk.attribute13;
704 END IF;
705 IF (x_timezone_rec.attribute14 = TAPI_DEV_KIT.G_MISS_CHAR)
706 THEN
707 x_timezone_rec.attribute14 := l_cs_time_zones_pk.attribute14;
708 END IF;
709 IF (x_timezone_rec.attribute15 = TAPI_DEV_KIT.G_MISS_CHAR)
710 THEN
711 x_timezone_rec.attribute15 := l_cs_time_zones_pk.attribute15;
712 END IF;
713 IF (x_timezone_rec.context = TAPI_DEV_KIT.G_MISS_CHAR)
714 THEN
715 x_timezone_rec.context := l_cs_time_zones_pk.context;
716 END IF;
717 IF (x_timezone_rec.object_version_number = TAPI_DEV_KIT.G_MISS_NUM)
718 THEN
719 x_timezone_rec.object_version_number := l_cs_time_zones_pk.object_version_number;
720 END IF;
721 RETURN(l_return_status);
722 END populate_new_record;
723 BEGIN
724 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
725 G_PKG_NAME,
726 l_api_version,
727 p_api_version,
728 p_init_msg_list,
729 '_Pvt',
730 x_return_status);
731 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
732 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
733 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
734 RAISE FND_API.G_EXC_ERROR;
735 END IF;
736 migrate(p_timezone_val_rec, l_timezone_rec);
737 --- Defaulting item attributes
738 l_return_status := Default_Item_Attributes
739 (
740 l_timezone_rec, ---- IN
741 l_def_timezone_rec
742 );
743 --- If any errors happen abort API
744 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
747 RAISE FND_API.G_EXC_ERROR;
748 END IF;
752 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
749 l_return_status := populate_new_record(l_def_timezone_rec, l_def_timezone_rec);
750 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753 RAISE FND_API.G_EXC_ERROR;
754 END IF;
755 --- Validate all non-missing attributes (Item Level Validation)
756 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
757 l_return_status := Validate_Item_Attributes
758 (
759 l_def_timezone_rec ---- IN
760 );
761 --- If any errors happen abort API
762 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
764 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
765 RAISE FND_API.G_EXC_ERROR;
766 END IF;
767 END IF;
768 IF (p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
769 l_return_status := Validate_Item_Record(l_def_timezone_rec);
770 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
772 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775 END IF;
776 UPDATE CS_TIME_ZONES
777 SET
778 TIME_ZONE_ID = l_def_timezone_rec.time_zone_id ,
779 NAME = l_def_timezone_rec.name ,
780 DESCRIPTION = l_def_timezone_rec.description ,
781 OFFSET_INDICATOR = l_def_timezone_rec.offset_indicator ,
782 OFFSET_TIME = l_def_timezone_rec.offset_time ,
783 LAST_UPDATE_DATE = l_def_timezone_rec.last_update_date ,
784 LAST_UPDATED_BY = l_def_timezone_rec.last_updated_by ,
785 CREATION_DATE = l_def_timezone_rec.creation_date ,
786 CREATED_BY = l_def_timezone_rec.created_by ,
787 LAST_UPDATE_LOGIN = l_def_timezone_rec.last_update_login ,
788 START_DATE_ACTIVE = l_def_timezone_rec.start_date_active ,
789 END_DATE_ACTIVE = l_def_timezone_rec.end_date_active ,
790 ATTRIBUTE1 = l_def_timezone_rec.attribute1 ,
791 ATTRIBUTE2 = l_def_timezone_rec.attribute2 ,
792 ATTRIBUTE3 = l_def_timezone_rec.attribute3 ,
793 ATTRIBUTE4 = l_def_timezone_rec.attribute4 ,
794 ATTRIBUTE5 = l_def_timezone_rec.attribute5 ,
795 ATTRIBUTE6 = l_def_timezone_rec.attribute6 ,
796 ATTRIBUTE7 = l_def_timezone_rec.attribute7 ,
797 ATTRIBUTE8 = l_def_timezone_rec.attribute8 ,
798 ATTRIBUTE9 = l_def_timezone_rec.attribute9 ,
799 ATTRIBUTE10 = l_def_timezone_rec.attribute10 ,
800 ATTRIBUTE11 = l_def_timezone_rec.attribute11 ,
801 ATTRIBUTE12 = l_def_timezone_rec.attribute12 ,
802 ATTRIBUTE13 = l_def_timezone_rec.attribute13 ,
803 ATTRIBUTE14 = l_def_timezone_rec.attribute14 ,
804 ATTRIBUTE15 = l_def_timezone_rec.attribute15 ,
805 CONTEXT = l_def_timezone_rec.context ,
806 OBJECT_VERSION_NUMBER = l_def_timezone_rec.object_version_number
807 WHERE
808 TIME_ZONE_ID = l_def_timezone_rec.time_zone_id
809 ;
810 x_object_version_number := l_def_timezone_rec.OBJECT_VERSION_NUMBER;
811 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
812 EXCEPTION
813 WHEN FND_API.G_EXC_ERROR THEN
814 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
815 (
816 l_api_name,
817 G_PKG_NAME,
818 'FND_API.G_RET_STS_ERROR',
819 x_msg_count,
820 x_msg_data,
821 '_Pvt'
822 );
823 APP_EXCEPTION.RAISE_EXCEPTION;
824 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
825 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
826 (
827 l_api_name,
828 G_PKG_NAME,
829 'FND_API.G_RET_STS_UNEXP_ERROR',
830 x_msg_count,
831 x_msg_data,
832 '_Pvt'
833 );
834 APP_EXCEPTION.RAISE_EXCEPTION;
835 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
836 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
837 (
838 l_api_name,
839 G_PKG_NAME,
840 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
841 x_msg_count,
842 x_msg_data,
843 '_Pvt'
844 );
845 APP_EXCEPTION.RAISE_EXCEPTION;
846 END update_row;
847 Procedure update_row
848 (
849 p_api_version IN NUMBER,
850 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
851 p_validation_level IN NUMBER,
852 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
853 x_return_status OUT VARCHAR2,
854 x_msg_count OUT NUMBER,
855 x_msg_data OUT VARCHAR2,
856 p_time_zone_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
857 p_name IN CS_TIME_ZONES.NAME%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
858 p_description IN CS_TIME_ZONES.DESCRIPTION%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
859 p_offset_indicator IN CS_TIME_ZONES.OFFSET_INDICATOR%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
860 p_offset_time IN CS_TIME_ZONES.OFFSET_TIME%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
861 p_last_update_date IN CS_TIME_ZONES.LAST_UPDATE_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
862 p_last_updated_by IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
863 p_creation_date IN CS_TIME_ZONES.CREATION_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
864 p_created_by IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
865 p_last_update_login IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
866 p_start_date_active IN CS_TIME_ZONES.START_DATE_ACTIVE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
867 p_end_date_active IN CS_TIME_ZONES.END_DATE_ACTIVE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
871 p_attribute4 IN CS_TIME_ZONES.ATTRIBUTE4%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
868 p_attribute1 IN CS_TIME_ZONES.ATTRIBUTE1%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
869 p_attribute2 IN CS_TIME_ZONES.ATTRIBUTE2%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
870 p_attribute3 IN CS_TIME_ZONES.ATTRIBUTE3%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
872 p_attribute5 IN CS_TIME_ZONES.ATTRIBUTE5%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
873 p_attribute6 IN CS_TIME_ZONES.ATTRIBUTE6%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
874 p_attribute7 IN CS_TIME_ZONES.ATTRIBUTE7%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
875 p_attribute8 IN CS_TIME_ZONES.ATTRIBUTE8%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
876 p_attribute9 IN CS_TIME_ZONES.ATTRIBUTE9%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
877 p_attribute10 IN CS_TIME_ZONES.ATTRIBUTE10%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
878 p_attribute11 IN CS_TIME_ZONES.ATTRIBUTE11%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
879 p_attribute12 IN CS_TIME_ZONES.ATTRIBUTE12%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
880 p_attribute13 IN CS_TIME_ZONES.ATTRIBUTE13%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
881 p_attribute14 IN CS_TIME_ZONES.ATTRIBUTE14%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
882 p_attribute15 IN CS_TIME_ZONES.ATTRIBUTE15%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
883 p_context IN CS_TIME_ZONES.CONTEXT%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
884 p_object_version_number IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
885 x_object_version_number OUT NUMBER) IS
886 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
887 l_api_version CONSTANT NUMBER := 1;
888 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
889 l_timezone_rec TimeZone_Val_Rec_Type;
890 BEGIN
891 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
892 G_PKG_NAME,
893 l_api_version,
894 p_api_version,
895 p_init_msg_list,
896 '_Pvt',
897 x_return_status);
898 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
900 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
901 RAISE FND_API.G_EXC_ERROR;
902 END IF;
903 l_timezone_rec.TIME_ZONE_ID := p_time_zone_id;
904 l_timezone_rec.NAME := p_name;
905 l_timezone_rec.DESCRIPTION := p_description;
906 l_timezone_rec.OFFSET_INDICATOR := p_offset_indicator;
907 l_timezone_rec.OFFSET_TIME := p_offset_time;
908 l_timezone_rec.LAST_UPDATE_DATE := p_last_update_date;
909 l_timezone_rec.LAST_UPDATED_BY := p_last_updated_by;
910 l_timezone_rec.CREATION_DATE := p_creation_date;
911 l_timezone_rec.CREATED_BY := p_created_by;
912 l_timezone_rec.LAST_UPDATE_LOGIN := p_last_update_login;
913 l_timezone_rec.START_DATE_ACTIVE := p_start_date_active;
914 l_timezone_rec.END_DATE_ACTIVE := p_end_date_active;
915 l_timezone_rec.ATTRIBUTE1 := p_attribute1;
916 l_timezone_rec.ATTRIBUTE2 := p_attribute2;
917 l_timezone_rec.ATTRIBUTE3 := p_attribute3;
918 l_timezone_rec.ATTRIBUTE4 := p_attribute4;
919 l_timezone_rec.ATTRIBUTE5 := p_attribute5;
920 l_timezone_rec.ATTRIBUTE6 := p_attribute6;
921 l_timezone_rec.ATTRIBUTE7 := p_attribute7;
922 l_timezone_rec.ATTRIBUTE8 := p_attribute8;
923 l_timezone_rec.ATTRIBUTE9 := p_attribute9;
924 l_timezone_rec.ATTRIBUTE10 := p_attribute10;
925 l_timezone_rec.ATTRIBUTE11 := p_attribute11;
926 l_timezone_rec.ATTRIBUTE12 := p_attribute12;
927 l_timezone_rec.ATTRIBUTE13 := p_attribute13;
928 l_timezone_rec.ATTRIBUTE14 := p_attribute14;
929 l_timezone_rec.ATTRIBUTE15 := p_attribute15;
930 l_timezone_rec.CONTEXT := p_context;
931 l_timezone_rec.OBJECT_VERSION_NUMBER := p_object_version_number;
932 update_row(
933 p_api_version,
934 p_init_msg_list,
935 p_validation_level,
936 p_commit,
937 x_return_status,
938 x_msg_count,
939 x_msg_data,
940 l_timezone_rec,
941 x_object_version_number
942 );
943 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
944 EXCEPTION
945 WHEN FND_API.G_EXC_ERROR THEN
946 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
947 (
948 l_api_name,
949 G_PKG_NAME,
950 'FND_API.G_RET_STS_ERROR',
951 x_msg_count,
952 x_msg_data,
953 '_Pvt'
954 );
955 APP_EXCEPTION.RAISE_EXCEPTION;
956 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
957 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
958 (
959 l_api_name,
960 G_PKG_NAME,
961 'FND_API.G_RET_STS_UNEXP_ERROR',
962 x_msg_count,
963 x_msg_data,
964 '_Pvt'
965 );
966 APP_EXCEPTION.RAISE_EXCEPTION;
967 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
968 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
969 (
970 l_api_name,
971 G_PKG_NAME,
972 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
973 x_msg_count,
974 x_msg_data,
975 '_Pvt'
976 );
977 APP_EXCEPTION.RAISE_EXCEPTION;
978 END update_row;
979 Procedure delete_row
980 (
981 p_api_version IN NUMBER,
982 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
983 p_validation_level IN NUMBER,
984 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
988 p_time_zone_id IN NUMBER) IS
985 x_return_status OUT VARCHAR2,
986 x_msg_count OUT NUMBER,
987 x_msg_data OUT VARCHAR2,
989 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
990 l_api_version CONSTANT NUMBER := 1;
991 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
992 BEGIN
993 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
994 G_PKG_NAME,
995 l_api_version,
996 p_api_version,
997 p_init_msg_list,
998 '_Pvt',
999 x_return_status);
1000 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1002 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1003 RAISE FND_API.G_EXC_ERROR;
1004 END IF;
1005 DELETE FROM CS_TIME_ZONES
1006 WHERE
1007 TIME_ZONE_ID = p_time_zone_id
1008 ;
1009 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
1010 EXCEPTION
1011 WHEN FND_API.G_EXC_ERROR THEN
1012 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1013 (
1014 l_api_name,
1015 G_PKG_NAME,
1016 'FND_API.G_RET_STS_ERROR',
1017 x_msg_count,
1018 x_msg_data,
1019 '_Pvt'
1020 );
1021 APP_EXCEPTION.RAISE_EXCEPTION;
1022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1023 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1024 (
1025 l_api_name,
1026 G_PKG_NAME,
1027 'FND_API.G_RET_STS_UNEXP_ERROR',
1028 x_msg_count,
1029 x_msg_data,
1030 '_Pvt'
1031 );
1032 APP_EXCEPTION.RAISE_EXCEPTION;
1033 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
1034 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1035 (
1036 l_api_name,
1037 G_PKG_NAME,
1038 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
1039 x_msg_count,
1040 x_msg_data,
1041 '_Pvt'
1042 );
1043 APP_EXCEPTION.RAISE_EXCEPTION;
1044 END delete_row;
1045 PROCEDURE validate_row
1046 (
1047 p_api_version IN NUMBER,
1048 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
1049 p_validation_level IN NUMBER,
1050 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
1051 x_return_status OUT VARCHAR2,
1052 x_msg_count OUT NUMBER,
1053 x_msg_data OUT VARCHAR2,
1054 p_timezone_val_rec IN TimeZone_Val_Rec_Type := G_MISS_TIMEZONE_VAL_REC) IS
1055 l_api_name CONSTANT VARCHAR2(30) := 'validate_row';
1056 l_api_version CONSTANT NUMBER := 1;
1057 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1058 l_timezone_rec TimeZone_Rec_Type;
1059 l_def_timezone_rec TimeZone_Rec_Type;
1060 BEGIN
1061 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
1062 G_PKG_NAME,
1063 l_api_version,
1064 p_api_version,
1065 p_init_msg_list,
1066 '_Pvt',
1067 x_return_status);
1068 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1070 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1071 RAISE FND_API.G_EXC_ERROR;
1072 END IF;
1073 migrate(p_timezone_val_rec, l_timezone_rec);
1074 --- Validate all non-missing attributes (Item Level Validation)
1075 IF p_validation_level >= FND_API.G_VALID_LEVEL_FULL THEN
1076 l_return_status := Validate_Item_Attributes
1077 (
1078 l_timezone_rec ---- IN
1079 );
1080 --- If any errors happen abort API
1081 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1083 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1084 RAISE FND_API.G_EXC_ERROR;
1085 END IF;
1086 END IF;
1087 IF (p_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
1088 l_return_status := Validate_Item_Record(l_def_timezone_rec);
1089 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1091 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1092 RAISE FND_API.G_EXC_ERROR;
1093 END IF;
1094 END IF;
1095 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
1096 EXCEPTION
1097 WHEN FND_API.G_EXC_ERROR THEN
1098 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1099 (
1100 l_api_name,
1101 G_PKG_NAME,
1102 'FND_API.G_RET_STS_ERROR',
1103 x_msg_count,
1104 x_msg_data,
1105 '_Pvt'
1106 );
1107 APP_EXCEPTION.RAISE_EXCEPTION;
1108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1109 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1110 (
1111 l_api_name,
1112 G_PKG_NAME,
1113 'FND_API.G_RET_STS_UNEXP_ERROR',
1114 x_msg_count,
1115 x_msg_data,
1116 '_Pvt'
1117 );
1118 APP_EXCEPTION.RAISE_EXCEPTION;
1119 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
1120 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1121 (
1125 x_msg_count,
1122 l_api_name,
1123 G_PKG_NAME,
1124 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
1126 x_msg_data,
1127 '_Pvt'
1128 );
1129 APP_EXCEPTION.RAISE_EXCEPTION;
1130 END validate_row;
1131 PROCEDURE validate_row
1132 (
1133 p_api_version IN NUMBER,
1134 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
1135 p_validation_level IN NUMBER,
1136 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
1137 x_return_status OUT VARCHAR2,
1138 x_msg_count OUT NUMBER,
1139 x_msg_data OUT VARCHAR2,
1140 p_time_zone_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
1141 p_name IN CS_TIME_ZONES.NAME%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1142 p_description IN CS_TIME_ZONES.DESCRIPTION%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1143 p_offset_indicator IN CS_TIME_ZONES.OFFSET_INDICATOR%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1144 p_offset_time IN CS_TIME_ZONES.OFFSET_TIME%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
1145 p_last_update_date IN CS_TIME_ZONES.LAST_UPDATE_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
1146 p_last_updated_by IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
1147 p_creation_date IN CS_TIME_ZONES.CREATION_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
1148 p_created_by IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
1149 p_last_update_login IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
1150 p_start_date_active IN CS_TIME_ZONES.START_DATE_ACTIVE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
1151 p_end_date_active IN CS_TIME_ZONES.END_DATE_ACTIVE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
1152 p_attribute1 IN CS_TIME_ZONES.ATTRIBUTE1%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1153 p_attribute2 IN CS_TIME_ZONES.ATTRIBUTE2%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1154 p_attribute3 IN CS_TIME_ZONES.ATTRIBUTE3%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1155 p_attribute4 IN CS_TIME_ZONES.ATTRIBUTE4%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1156 p_attribute5 IN CS_TIME_ZONES.ATTRIBUTE5%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1157 p_attribute6 IN CS_TIME_ZONES.ATTRIBUTE6%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1158 p_attribute7 IN CS_TIME_ZONES.ATTRIBUTE7%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1159 p_attribute8 IN CS_TIME_ZONES.ATTRIBUTE8%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1160 p_attribute9 IN CS_TIME_ZONES.ATTRIBUTE9%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1161 p_attribute10 IN CS_TIME_ZONES.ATTRIBUTE10%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1162 p_attribute11 IN CS_TIME_ZONES.ATTRIBUTE11%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1163 p_attribute12 IN CS_TIME_ZONES.ATTRIBUTE12%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1164 p_attribute13 IN CS_TIME_ZONES.ATTRIBUTE13%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1165 p_attribute14 IN CS_TIME_ZONES.ATTRIBUTE14%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1166 p_attribute15 IN CS_TIME_ZONES.ATTRIBUTE15%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1167 p_context IN CS_TIME_ZONES.CONTEXT%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
1168 p_object_version_number IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM) IS
1169 l_api_name CONSTANT VARCHAR2(30) := 'validate_row';
1170 l_api_version CONSTANT NUMBER := 1;
1171 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1172 l_timezone_rec TimeZone_Val_Rec_Type;
1173 BEGIN
1174 l_return_status := TAPI_DEV_KIT.START_ACTIVITY(l_api_name,
1175 G_PKG_NAME,
1176 l_api_version,
1177 p_api_version,
1178 p_init_msg_list,
1179 '_Pvt',
1180 x_return_status);
1181 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1183 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1184 RAISE FND_API.G_EXC_ERROR;
1185 END IF;
1186 l_timezone_rec.TIME_ZONE_ID := p_time_zone_id;
1187 l_timezone_rec.NAME := p_name;
1188 l_timezone_rec.DESCRIPTION := p_description;
1189 l_timezone_rec.OFFSET_INDICATOR := p_offset_indicator;
1190 l_timezone_rec.OFFSET_TIME := p_offset_time;
1191 l_timezone_rec.LAST_UPDATE_DATE := p_last_update_date;
1192 l_timezone_rec.LAST_UPDATED_BY := p_last_updated_by;
1193 l_timezone_rec.CREATION_DATE := p_creation_date;
1194 l_timezone_rec.CREATED_BY := p_created_by;
1195 l_timezone_rec.LAST_UPDATE_LOGIN := p_last_update_login;
1196 l_timezone_rec.START_DATE_ACTIVE := p_start_date_active;
1197 l_timezone_rec.END_DATE_ACTIVE := p_end_date_active;
1198 l_timezone_rec.ATTRIBUTE1 := p_attribute1;
1199 l_timezone_rec.ATTRIBUTE2 := p_attribute2;
1200 l_timezone_rec.ATTRIBUTE3 := p_attribute3;
1201 l_timezone_rec.ATTRIBUTE4 := p_attribute4;
1202 l_timezone_rec.ATTRIBUTE5 := p_attribute5;
1203 l_timezone_rec.ATTRIBUTE6 := p_attribute6;
1204 l_timezone_rec.ATTRIBUTE7 := p_attribute7;
1205 l_timezone_rec.ATTRIBUTE8 := p_attribute8;
1206 l_timezone_rec.ATTRIBUTE9 := p_attribute9;
1207 l_timezone_rec.ATTRIBUTE10 := p_attribute10;
1208 l_timezone_rec.ATTRIBUTE11 := p_attribute11;
1209 l_timezone_rec.ATTRIBUTE12 := p_attribute12;
1210 l_timezone_rec.ATTRIBUTE13 := p_attribute13;
1211 l_timezone_rec.ATTRIBUTE14 := p_attribute14;
1212 l_timezone_rec.ATTRIBUTE15 := p_attribute15;
1213 l_timezone_rec.CONTEXT := p_context;
1214 l_timezone_rec.OBJECT_VERSION_NUMBER := p_object_version_number;
1215 validate_row(
1216 p_api_version,
1217 p_init_msg_list,
1218 p_validation_level,
1219 p_commit,
1223 l_timezone_rec
1220 x_return_status,
1221 x_msg_count,
1222 x_msg_data,
1224 );
1225 TAPI_DEV_KIT.END_ACTIVITY(p_commit, x_msg_count, x_msg_data);
1226 EXCEPTION
1227 WHEN FND_API.G_EXC_ERROR THEN
1228 x_return_status := TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1229 (
1230 l_api_name,
1231 G_PKG_NAME,
1232 'FND_API.G_RET_STS_ERROR',
1233 x_msg_count,
1234 x_msg_data,
1235 '_Pvt'
1236 );
1237 APP_EXCEPTION.RAISE_EXCEPTION;
1238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1239 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1240 (
1241 l_api_name,
1242 G_PKG_NAME,
1243 'FND_API.G_RET_STS_UNEXP_ERROR',
1244 x_msg_count,
1245 x_msg_data,
1246 '_Pvt'
1247 );
1248 APP_EXCEPTION.RAISE_EXCEPTION;
1249 WHEN TAPI_DEV_KIT.G_EXC_DUP_VAL_ON_INDEX THEN
1250 x_return_status :=TAPI_DEV_KIT.HANDLE_EXCEPTIONS
1251 (
1252 l_api_name,
1253 G_PKG_NAME,
1254 'TAPI_DEV_KIT.G_RET_STS_DUP_VAL_ON_INDEX',
1255 x_msg_count,
1256 x_msg_data,
1257 '_Pvt'
1258 );
1259 APP_EXCEPTION.RAISE_EXCEPTION;
1260 END validate_row;
1261 END CS_TIMEZONE_PVT;