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