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