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